Restore/Clone a Database from HP DP

I also have a similar post, which was using RMAN command from the target and using autonabcup controlfile. But this one is using "CONTROL FILE FROM DP MANAGED BACKUP"
We were planning to restore our production database to our test machine as a preparation of DR. Our current environment was as below:
- Source/Production Datbase on HPUX and Oracle 9.2
- HP Data Protector version 5.5
- RMAN Backup was taken with pool 1. FULL & INCREMENTAL BACKUP 2. Archivelog Daily Backup

1. Prepare the partitons of target machine similar to the Source/Production machine.
2. Make sure that all the needed media are on the tape library [or available an if mount request prompts you can change with the required tape]
3. If Partitions are not similar then create soft links considering space requirements.
4. Install Oracle Database SW [Same as production]
5. Configure DP client on the target machine
7. Prepare a pfile [initMYDB.ora] mentioning the locations of control files and archivelog files and other info line PGA, SGA size etc.
8. Execute below commands :

$ export ORACLE_SID=MYDB
$ rman target /
RMAN > startup nomount

9. Create listener serving the new database [MYDB]
10. Create a TNS enty for the new database [MYDB]
11. Create Password file for the the target database [MYDB]
12. Check that you can connect using password file:

$ sqlplus /nolog
SQL> conn sys/[password as in passwordfile]@MYDB as sysdba

13. From DP follow options to restore[to file system] controlfile from DP managed backup of control file:
                          > restore
                          > Oracle Server
                          > [source/production machine]
                          > click on [source/production DATABSE, in mycase it is MYDB]
                          > Select "Source" from the Tab Pane appeared on the right side
                          > From restore sction menu select "Perform RMAN Repository Restore"
                          > Select radio button "CONTROL FILE FROM DP MANAGED BACKUP"
                          > Select "Option" Tab
                          > From "Restore to client" menu select the target Machine
                          > Click the "Settings" Button and give username:sys password & service [in my case MYDB] then OK
                          > Cilck the "Device" tab and select your drive
                          > Enter the OS user & group whivh owns the oracle SW
                          > From the "Session ID" menu select the latest possible session id[or select as your need]
                          > CLick "Restore" button

Now, DP will restore the control file in the tmp directory of omni like below:                         
/var/opt/omni/tmp/ctrl_MYDB.dbf

14. Now restore the controlfile for the target DB as below:
$ export ORACLE_SID=MYDB
RMAN > run {
allocate channel 'dev0' type disk;
restore controlfile from '/var/opt/omni/tmp/ctrl_MYDB.dbf ';
release channel 'dev0';
}
Now, control file are restored to the locations specified in pfile of MYDB

15. now mount the database [as now we got control files]:

$ export ORACLE_SID=MYDB
RMAN > alter database mount;

16. From DP follow options to restore database from the full backup:
                          > restore
                          > Oracle Server
                          > [source/production machine]
                          > click on [source/production DATABSE, in mycase it is MYDB]
                          > Select "Source" from the Tab Pane appeared on the right side
                          > From restore sction menu select "Perform Restore"
                          > Check "DATABASE" option from the root [if you want to restore the full database]
                          > Select "Option" Tab
                          > From "Restore to client" menu select the target Machine
                          > Enter the OS user & group which owns the oracle SW                         
                          > Click the "Settings" Button and give username:sys password & service [in my case MYDB] then OK
                          > Let the "Restore Mode" to normal
                          > Set parallelism 4
                          > Slecet "Restore Until" option according to your need
                          > Cilck the "Device" tab and select your drive
                          > CLick "Restore" button

Now, DP will detech tapes from the library[or mount request will prompt] and restore all the datafiles to the location accrofing to the control file.

17. From DP follow options to recover database:
                          > restore
                          > Oracle Server
                          > [source/production machine]
                          > click on [source/production DATABSE, in mycase it is MYDB]
                          > Select "Source" from the Tab Pane appeared on the right side
                          > From restore sction menu select "Perform Recovery Only"
                          >  "DATABASE" option Check autometically [no need to do anything]
                          > Select "Option" Tab
                          > From "Restore to client" menu select the target Machine
                          > Enter the OS user & group which owns the oracle SW                         
                          > Click the "Settings" Button and give username:sys password & service [in my case MYDB] then OK
                          > Let the "Restore Mode" to normal
                          > Set parallelism 4
                          > Slecet "Recover Until" option according to your need
                          > Cilck the "Device" tab and select your drive
                          > I put "Open database after recover" unchecked
                          > CLick "Restore" button
                         
- DP will recever with incrimental backups first and then restore archivelogs to the location on archivelof in initMYDB.ora location and apply them
- if you have space constraints then move the related archive logs to another location after message like below on DP restore message:
archive log filename=/billing/mydb/arch/MYDB/MYDB_56595.arc thread=1 sequence=56595
archive log filename=/billing/mydb/arch/MYDB/MYDB_56596.arc thread=1 sequence=56596
archive log filename=/billing/mydb/arch/MYDB/MYDB_56597.arc thread=1 sequence=56597

18. Now open the database:

$ export ORACLE_SID=MYDB
RMAN> alter database open resetlogs;                       

- Now, database should be fully recovered and consistant and open
- Check alert log for error and resolve them

19. Create and adjust temp tablespace as per your need.

20. Now optionally do below:
     - change the database name
     - create a password file for the new name
     - Correct the TNS and listener for the new name
     - startup the databse
     - create spfile and reboot the DB
     - check the alertlog carefully


Some of the problems that may occur during above process are given below:

i. validation failed for archived log:

In alert log we may see below error:
recover database until time "to_date('10/26/11 00:00:00','MM/DD/YY HH24:MI:SS')";

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/27/2011 21:23:25
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 56617 scn 3098608773567 found to restore
...............
...............
RMAN-06025: no backup of log thread 1 seq 56605 scn 3098608377404 found to restore

Cause: Recover,while applying archivelog, failed due to space limitation or other cause

Solution:
1. Again try to recover database from DP after the resolving the associate problem.
         
2. manually copy archivelogs from the production or from other source and do as below:

2.a crosscheck archivelogs:
RMAN> crosscheck archivelog all;

while crosschecking if you error like below:
.......
.......
validation failed for archived log
archive log filename=/billing/mydb/arch/log_archive/MYDB_56618.arc recid=56167 stamp=765500466

2.b then, first check the current archivelog location of the target database and make sure that archivelogs names associated with the error exisrs there.
this occurs becaus the location of the archivelog of source and terget is not same [may occur when archivelogs are copied manually]
To resolve above we need no catalog archivelogs as below for all the archivelogs archivelogs gave error:

RMAN> catalog archivelog '/billing/mydb/arch/MYDB/MYDB_56618.arc';
...
...
RMAN> catalog archivelog '/billing/mydb/arch/MYDB/MYDB_56618.arc';

2.c then crosscheck again:
RMAN> crosscheck archivelog all;

Now, we if we get both success and fail for the same archivelog as below then it is ok, just ignore the error message:
validation succeeded for archived log
archive log filename=/billing/mydb/arch/MYDB/MYDB_56618.arc recid=56230 stamp=765667425
validation failed for archived log
archive log filename=/billing/mydb/arch/log_archive/MYDB_56618.arc recid=56167 stamp=765500466

2.d Now try to recover again:

RMAN> recover database until time "to_date('10/26/11 00:00:00','MM/DD/YY HH24:MI:SS')";

Starting recover at 27-OCT-11
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 56605 is already on disk as file /billing/mydb/arch/MYDB/MYDB_56605.arc
...
...
archive log thread 1 sequence 56618 is already on disk as file /billing/mydb/arch/MYDB/MYDB_56618.arc

archive log filename=/billing/mydb/arch/MYDB/MYDB_56605.arc thread=1 sequence=56605
....
....
archive log filename=/billing/mydb/arch/MYDB/MYDB_56619.arc thread=1 sequence=56619
media recovery complete
Finished recover at 27-OCT-11


2.e ignore nelow message on alertlog:

ORA-279 signalled during: alter database recover logfile '/billing/mydb/...'

2.f now to eliminate "both success and fail for the same archivelog" in "crosscheck archivelog all" execute below:

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

ii. At the time of "alter database open resetlogs", observe below:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/27/2011 21:32:57
ORA-01092: ORACLE instance terminated. Disconnection forced

1. ingnore below alertlog:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/billing/mydb/redo1/oradata/redo_02a.log'
ORA-27037: unable to obtain file status

2. but "ORA-01092: ORACLE instance terminated. Disconnection forced" is a issue, Please check the alert log:

ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

To resolve above error , startup mount do as below:

2.a
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
TBS_BILLIMAGES
TBS_ORDERHDR
.......
IDX_ORDERHDR
TEMP
TBS_SMALL
UNDOTBS  -- <<<<<<< So, we donot have tablespace 'UNDOTBS1', our undo tablesace name is 'UNDOTBS'

39 rows selected.

2.b Now, correct this as below:

SQL> show parameter undo;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management                      string                           AUTO
undo_retention                       integer                          21600
undo_suppress_errors                 boolean                          FALSE
undo_tablespace                      string                           UNDOTBS1
SQL>

SQL> shutdown immediate;

2.c [ optionally create a new one and ] edit initMYDB.ora and set "undo_tablespace=UNDOTBS" and startup using the pfile:

SQL> startup pfile='location of the pfile';
SQL> create spfile from pfile='location of the pfile';

thanks to below links:
http://repettas.wordpress.com/2008/11/01/restore-archive-logs-to-new-location-using-rman/
http://www.dbasupport.com/forums/showthread.php?t=53108
https://forums.oracle.com/forums/thread.jspa?threadID=636113

Install Oracle Workflow Server on 11gR2 Database

We have just installed Oracle Workflow Server on 11gR2 Database using below steps:

1. Go to $ORACLE_HOME\owb\wf\install

2. run below script:
Linux: $ ./wfinstall.csh
Windows: wfinstall.bat

3. insert values as below on the form displayed with the above command:
Install Option : Server Only
Workflow Account : owf_mgr
Workflow Password : Specify a password for owf_mgr
SYS Password : Enter SYS password for the database on which we are installing Oracle Workflow.
TNS Connect Descriptor : Descriotion of TNS of Target Database
[ (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME/IP)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))]
LDAP Parameters : [If needed]
Mailer Parameters : [If needed]
Tablespace : Default tablespace for worlflow server

4. Submit to start

5. After Successful installation a completion message will be displayed

Thanks to below links:
http://download.oracle.com/docs/cd/B28359_01/owb.111/b31280/install_opts05.htm#i1008852
http://forums.oracle.com/forums/thread.jspa?threadID=663891

User Trace

In order to trace all the actions by a specific user we can user below log on trigger:

CREATE OR REPLACE TRIGGER myuser_logon_trace
AFTER LOGON ON MYUSER.SCHEMA
BEGIN
execute immediate 'alter session set tracefile_identifier="MYUSER"';
DBMS_SESSION.SET_SQL_TRACE(TRUE);
END;
/

Now, we can find the output of the trace at "user_dump_dest" with "MYUSER" in the name.

How to clean listener log on oracle 9i and 10g

Let, our database name is LIST_MYDB and listener for this database is LIST_MYDB.

If needed doe below:
open "listener.ora" and put ADMIN_RESTRICTIONS_l[istener_name]=off and RELOAD [listnere_name]"
then proceed as below:

1. stop logging
$ lsnrctl
LSNRCTL> set CURRENT_LISTENER LIST_MYDB
Current Listener is LIST_MYDB

LSNRCTL> set Log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1533)))
LIST_MYDB parameter "log_status" set to OFF
The command completed successfully

2. move the current log
$ mv list_mydb.log mv list_mydb.log_old

3. start logging
$ lsnrctl
LSNRCTL> set CURRENT_LISTENER LIST_MYDB

LSNRCTL> set Log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1533)))
LIST_MYDB parameter "log_status" set to ON
The command completed successfully

LSNRCTL> exit

If forgot to stop logging [step 1] then logging may stop automatically.
In this case after completing step 2, do as step 1 and then step 3.

Now do below if needed:
open "listener.ora" and put ADMIN_RESTRICTIONS_l[istener_name]=on and RELOAD [listnere_name]"

RMAN Scripts to Create Backup Pieces to Multiple Disk LOcations and delete obsolete

In order to take backup of datafiles and archivelogs to more than one locations below scripts can be used:

run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data02/oradata/RMAN_BACKUP/LOC2/%F';
backup incremental level 0 database;
release channel disk1;
release channel disk2;

sql 'alter system archive log current';
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/LOG_%t_%s_%p_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/LOG_%t_%s_%p_%U';
backup archivelog all DELETE INPUT;
release channel disk1;
release channel disk2;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
}


We have faced below error while "delete obsolete":

RMAN> delete obsolete
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

Problem was, I was trying to delete backups, which are on both disk and tape, so we need to allocate proper channel tape/disk:

Tape:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete obsolete;
}

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}


Disk:

run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete obsolete;
}

run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}

Oracle Database Disaster Recovery using Disk Based RMAN Backup

Scenario:
- Database name is MYDB
- Production [source] machine is completely out of order and inaccessible
- Have scheduled RMAN Backup on remote storage disks
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- All the needed RMAN backup pieces have been identified
- Oracle SW on the target machine has been installed
- Below two mount points have been mounted on the target machine and on which RMAN backup pieces are kept:
/data01/oradata/RMAN_RESTORE/LOC1
/data02/oradata/RMAN_RESTORE/LOC2
- Latest AUTOBACKUPed CONTROLFILE has been identified [ c-[DBID]-[YYYYMMDD]-[2 digit HEX_SEQUENCE for the day],expl:c-2169732094-20110614-0d ] and kept on /data02/oradata/RMAN_RESTORE/LOC2
- Now have to restore and recover the MYDB database
- For all the steps below, please export ORACLE_HOME & ORACLE_SID properly and use oracle user

[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]

1. Restore the spfile:
$ export ORACLE_SID=MYDB
rman> startup nomount
rman> restore spfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';

- In above command "c-2169732094-20110614-0d" is the latest autobackuped controlfile.
- Spfile is now restored in the default location [$ORACLE_HOME/dbs/spfileMYDB].
- We may need to edit the spfile to create pfile file, because some some parameter may needed to be chaged an startup with pfile

rman> shutdown immediate
$ rman target /
rman> startup nomount [or startup nomount pfile='PFILE_LOCATION']


2. Restore controlfile and mount the database

- To avoid below error we need to create related directory locations or change control file location:
ORA-19504: failed to create file "/data01/oradata/MYDB/cntl/control02.ctl"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

rman> restore controlfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';

- On above, this is the same file from which spfile has been restored.

rman> startup mount [or force]

3. Check needed configuration

- Check latest scn as no redo log file available
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 44958649 CURRENT NO ---<<<< this is the target SCN
3 44958631 ACTIVE YES
2 44958342 ACTIVE YES

- optionally plan for change the datafile location
- use below command to find current datafile location then plan for new location:
rman> REPORT SCHEMA;
-we can use below set of commands to crosscheck whether we have all the needed backup pieces:
RMAN> list backup;
RMAN> list backup of database completed after [date];
RMAN> list backup of database completed between [start date] and [end date];

4. Register the calalog location [to identify the backup pieces from the disk locations]
- for each location where RMAN backup piece is kept please run " catalog start with" as below:

rman> catalog start with '/data01/oradata/RMAN_RESTORE/LOC1';
rman> catalog start with '/data02/oradata/RMAN_RESTORE/LOC2';


5. restore the database
- For similar location of the datafiles & tempfiles use below command:
rman> restore database;

- For changed locations [step 3.] of the datafiles & tempfiles use below command canbe used:
rman> run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
SET NEWNAME FOR TEMPFILE 1 TO '[new location for tempfile 1]';
allocate channel 'dev_0' type disk;
restore database;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}

6. Recover and open the database to the SCN identified at step 3:
rman> recover database until scn 44958649;
rman> alter database open resetlogs;

7. create a password file and give an entry in oratab file
$ orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=password entries=5
[ for windows:$ orapwd file=$ORACLE_HOME/database/orapwMYDB password=password entries=5 ]
$ vi /etc/oratab
MYDB:/oracle/orabase/product/11.2.0/dbhome_1:N


Relater links:
http://web.njit.edu/info/oracle/DOC/backup.102/b14191/rcmcatdb002.htm
http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ
http://repettas.wordpress.com/2008/11/01/rman-restore-and-recover-of-a-database-when-the-repository-and-spfileinitora-files-are-lost/

Disaster Recovery using HP Data Protector and Oracle RMAN

In our case we have used below components:
- HP Data Protector Version 6.2
- Source & Destination System: RHEL5.5
- Source & Destination Database: Oracle 11.2.0.1

Scenario:
- Our production [source] machine is completely out of order and inaccessible
- We have scheduled RMAN Backup [incremental] on TAPE which is configured with HPDP 6.2
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- Our Database name is MYDB and Backup Specifications names for this DB starts with MYDB_*
- Machine of Cell Manager is also completely damaged
- We have backup of HPDP iDB on tape
- So, currently we only have Tapes containing HPDP iDB latest backup & RMAN backup


So we need to do following:
1. Configure a new Cell Manager
2. Scan load tape information for the Tape containing DP iDB backup
3. import iDB from the backup on tape
4. Scan Tapes containing RMAN backup
5. Configure a new machine with same OS and Connectivity with new Cell Manager
6. Create the partitions locations as much as similar to the old machine as possible
7. Install Oracle SW only of same version [version means consideration 32bit and 64bit]
8. Create a LISTENER listening on default port
9. Install and configure HP DP 6.2 Client on the target DB machine [Install HP Data Protector 6.2 Client [and use in RMAN]]
10. Restore and Recover the Oracle Database named MYDB on new machine

Now we are going to discuss step 10 in details:

[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]

For all the below steps, make sure that desired ORACLE_HOME is exported correctly

a. Collection configuration information:

- identify the backup spec that contains latest backup in our case [NODE1_MYDB_INCR1]
- from the latest rman backup session of DP [internal database>sessions>properties>message] collect below to parameter:
dbid 4261461017;
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=MYDB,OB2BARLIST=NODE1_MYDB_INCR1)';

- identify and crosscheck needed media from session messages using lines like below:
Initializing new medium: "Default File_6"
Loading medium from slot [1709a8c054df607235202050011.fd] to device [Test_01_Writer0]

b. Restore spfile & controlfile backedup by "CONTROLFILE AUTOBACKUP" from Tape using below steps:

$ export ORACLE_SID=MYDB
$ rman target /
connected to target database (not started)
[ignore above, because we do not have any Database now]

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle1/orabase/product/11.2.0/dbhome_1/dbs/initMYDB.ora'
--ignore above, because we donot have s/pfile and DUMMY DB is needed now

RMAN>
set dbid 4261461017;
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=MYDB,OB2BARLIST=NODE1_MYDB_INCR1)';
restore spfile from autobackup;
restore controlfile from autobackup;
release channel 'dev_0';
}

allocated channel: dev_0
channel dev_0: SID=67 device type=SBT_TAPE
channel dev_0: Data Protector A.06.20/370

Starting restore at 12-JUN-11

channel dev_0: looking for AUTOBACKUP on day: 20110612
channel dev_0: AUTOBACKUP found: c-2169732094-20110612-01
channel dev_0: restoring spfile from AUTOBACKUP c-2169732094-20110612-01
channel dev_0: SPFILE restore from AUTOBACKUP complete
Finished restore at 12-JUN-11

Starting restore at 12-JUN-11

channel dev_0: looking for AUTOBACKUP on day: 20110612
channel dev_0: AUTOBACKUP found: c-2169732094-20110612-01
channel dev_0: restoring control file from AUTOBACKUP c-2169732094-20110612-01
channel dev_0: control file restore from AUTOBACKUP complete
output file name=/oracle1/orabase/product/11.2.0/dbhome_1/dbs/cntrlMYDB.dbf --<< control file restored location
Finished restore at 12-JUN-11

released channel: dev_0

[controlfile and spfile will be restored to the default location $ORACLE_HOME/dbs]

c. Then create a pfile by editing the restored spfile [change the location and other parameters]

d. shutdown the Database and start the database using the newly created pfile:

$ export ORACLE_SID=MYDB
$ sqlplus / as sysdba
SQL> shutdown immediate

SQL> startup nomount pfile='[location of the newly created pfile]'

e. Change the locations for controlfile or copy controlfiles to the desired location mentioned in the in pfile and mount the Database
[or we could create the pfile with the modified "control_files" parameter]

SQL> show parameter control_files

control_files string /system1/oradata/MYDB/cntl/c
ontrol01.ctl, /data01/oradata/
MYDB/cntl/control02.ctl

SQL>! cp $ORACLE_HOME/dbs/cntrlMYDB.dbf /system1/oradata/MYDB/cntl/control01.ctl
SQL>! cp $ORACLE_HOME/dbs/cntrlMYDB.dbf /data01/oradata/MYDB/cntl/control02.ctl

SQL> alter database mount


f. after mount we can restore it from DP or using rman as below,
"tape identification and loading unloading will be handled by DP, please monitor session on DP"

$ export ORACLE_SID=MYDB
$ target /

RMAN> run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore database;
}
[Here only SBT_LIBRARY parms is needed, as rest of the info is now in restored control files.]

If you find error like below then try to create same locations or restore to new location:
ORA-19504: failed to create file "......."
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

Use below command to identify missing datafiles and original locations:
RMAN> VALIDATE DATABASE;
RMAN> REPORT SCHEMA;

if same location is create then:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore datafile 15;
release channel 'dev_0';
}

if new location is needed then:
run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore datafile 1;
restore datafile 5;
restore datafile 15;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}

g. Collect the latest SCN [as redo logs are also lost] which will be user to recover.
This will eliminate the porbability of RMAN-06054 & RMAN-06556

$ export ORACLE_SID=MYDB
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
2 968291 CURRENT NO ---<<< target SCN, SCN just after the latest archived redolog
1 968224 ACTIVE YES
3 963378 INACTIVE YES


i. Recover Database to the latest SCN

$ export ORACLE_SID=MYDB
$ rman target /
RMAN> run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
recover database until scn 968291;
}
[Here only SBT_LIBRARY parms is needed, as rest of the info is now in restored control files.]


j. Open database using "resetlogs" option [as redo logs are lost]

RMAN> alter database open resetlogs;

k. now create password file to enable remore sysdba login

$ export ORACLE_SID=MYDB
$ orapwd file=$ORACLE_HOME/dbs/orapw password=oracle entries=5

l. create spfile and restart database

$ export ORACLE_SID=MYDB
$ sqlplus / as sysdba

SQL> create spfile from pfile='[location of the newly created pfile]'
SQL> shutdown immediate
SQL> startup

m. Check alert log for error and take a level 0 backup using DP

n. optionally enter a new entry for the database in "/etc/oratab", needed for configuration [dbca]


If there is a 32bit and 64bit difference between source and destination database, we may encounter below error on step "10.j" :
--------------------------------------------------------------------------------------
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56327]
ORA-06553: PLS-801: internal error [56327]
--------------------------------------------------------------------------------------
Problem is that, backup originally was done from Oracle 64bit, but restored to Oracle 32bit.
Please use "MOS NOTE [ID 62290.1] — Changing between 32-bit and 64-bit Word Sizes" to resolve this issue.

Related Links:
http://www.orafusion.com/art_rman3.htm
http://yurijs.wordpress.com/2010/01/27/backup-issue/
ttp://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmcomre.htm

For HPDP Related Config:
http://razib2020.blogspot.com/2010/07/installation-of-dp-cell-manager-purging.html

Install HP Data Protecctor 6.2 Client [and use in RMAN]

We have followed below steps while installing HP Data Protecctor 6.2 Client:

For windows:

1. Add hostname and IP of target machine [client] & DP Cell Manager in hosts file of the target machine like below:
on windows C:\Windows\System32\drivers\etc\hosts
on linux /etc/hosts

192.168.81.209 DP_CLIENT_HOSTNAME.domain.com DP_CLIENT_HOSTNAME
192.168.9.23 DP_CELL_MANAGER.domain.com DP_CELL_MANAGER

2. unzip the HPDP sw dump
on windows change the locaion to: ESD_HP_DP_6.2_for_Windows_and_Media_Operation_TD586_15000_01\TD586-15000-01\WINDOWS_OTHER\i386

- Double Click setup --<<< use "run as administrator" or install using Administrator user
- select installation type: cleint
- Enter: Cell manager hostname [as entered in the hosts file] and "check response"
- From component list select: Disk Agent, Media Agent & oracle integration [under Integration]
- start
- Finish
Now locate the "orasbt.dll" which is needed in RMAN command.
So, RMAN Command on window may look as below:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=D:\OmniBack\bin\orasbt.dll';
restore database;
}


For Linux/Unix:

We need HPUX distribution of HPDP to install on both HPUX and linux.
Run below set of commands as "root":

$ tar -zxvf ESD_HP_DP_6.2_for_HP_UX_PA_TD586_15001_01.tar.gz
$ cd TD586-15001-01/LOCAL_INSTALL/
$ ./omnisetup.sh -server DP_CELL_MANAGER.domain.com -install da,ma,oracle8
# here as DP_CELL_MANAGER.domain.com is entered in the hosts file


Now locate the "libob2oracle8.so" [for x64 machine libob2oracle8_64bit.so] which is needed in RMAN command:
So, RMAN Command on window may look as below:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8.so';
restore database;
}

Thank to :http://saifur-shaon.blogspot.com/2011/01/dml-over-dblink-ora-01031.html

Install Oracle Apex 4 on Oracle Database 11.2

Our target was to Install Apex with below details:
Apex version: 4.0.2
Install Option: Full Development, we need this installation for development
HTTP Server: "Application Express Listener" "Standalone Mode", want to avoid installation of extra application server
Database: Oracle 11.2 Enterprise
Java: jdk1.6.0_10 [for apex listener]

Below is the prepared action plan I have prepared according to the docs provided with Apex:

1. Requirements Check list:
1.1 DB: >= 10.2.0.3
1.2 SHARED_POOL_SIZE='100M'
1.3 Disk Space Requirement
- Oracle Application Express software files on the file system: 1 GB if using full download (apex_4.0.2.zip).
- Oracle Application Express tablespace: 185 MB
- SYSTEM tablespace: 100 MB
- Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB
1.4 Oracle XML DB Requirement
1.5 Oracle Text Requirement
1.6 HTTP Server Requirements
- We will go for Oracle "Application Express Listener" because:
a. seperation from DB [Embaded PL/SQL gateway, runs in the database as part of the XML DB HTTP Protocol Listener]
b. no need of extra plagin [Oracle HTTP Server, uses the mod_plsql plug-in to communicate with the Oracle Application Express engine within the Oracle database]

2. Install appex:
2.1 Download Oracle Appex and upzip to the desired location[http://www.oracle.com/technology/products/database/application_express/download.html]
2.2 Remove the password complexity from the default profile of DB
2.3 Create tablespace for appex [TBS_APEX]
2.4 for full development installation go to unzipped folder of appex and use below:
SQL> @apexins tablespace_apex tablespace_files tablespace_temp images
tablespace_apex: is the name of the tablespace for the Oracle Application Express application user.
tablespace_files: is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp: is the name of the temporary tablespace.
images: is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/
for our case we used below:
SQL> conn / as sysdba
SQL> @apexins TBS_APEX TBS_APEX TEMP /i/
When Oracle Application Express installs it creates three new database accounts:
APEX_040000 - The account that owns the Oracle Application Express schema and metadata.
FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.
2.5 Change the Password for the ADMIN Account [When prompted enter a password for the ADMIN account]
SQL> conn / as sysdba
SQL> @apxchpwd
2.6 unlock APEX_PUBLIC_USER and change its password
SQL> conn / as sysdba
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password

3. install appex listener:
3.1 Download Oracle Application Express Listener and unzip to the desired location [http://www.oracle.com/technology/products/database/application_express/html/apex_listener_download.html]
3.2 for installation we choose "Standalone Mode" as we donot need any exra components
3.4 start appex listener[we are using jdk1.6.0_10, failed to run using jre 1.3 & 1.5]:
> "C:\Program Files\Java\jdk1.6.0_10\bin\java" –jar -Dapex.images=[apex home]/images [appex listener home]\apex.war
-When prompted, specify the location of the folder containing the static resources used by Oracle Application Express
we used the images location created in the unziped apex folder:..../apex/images
- when prompt for username of "APEX Listener Administrator" we kept the default:[adminlistener] and gave the password [prompt only for the first run]
- when prompt for username of "APEX Listener Manager" we kept the default:[managerlistener] and gave the password [prompt only for the first run]
then go to "http://[ip or hostname]:[port, default is 8080]/apex/listenerConfigure" and configure APEX Listener:just give the connection details and apply
for more configuration refer to docs comes with APEX Listener
3.5 Enable Network Services in Oracle Database 11g:
- Failing to grant these privileges results in issues with:
a. Sending outbound mail in Oracle Application Express.Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.
b. Using Web services in Oracle Application Express.
c. PDF/report printing.
d. Searching for content in online Help (that is, using the Find link).
3.6 Granting Connect Privileges

The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user[as sysdba]:
-----------------------BEGIN SCRIPT-------------------------------------
DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
-----------------------------END SCRIPT----------------------------------
3.7 Enable Indexing of Online Help in Oracle Database 11gR2 and Higher:

- Run the following command:
SQL > conn / as sysdba
SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

This returns either NULL or the database role which is granted the ability to use an Oracle Text URL datastore.
- If no value is returned by above, then create a new database role as shown in the following example:
SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;
SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;

- use the Oracle Text API to grant permission database role with the following statement:
SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');

4. JOB_QUEUE_PROCESSES:
4.1 JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs.
4.2 In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs
4.3 use below to check and set value for job_queue_processes:
SQL> SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = [number];

5. Creating a Workspace Manually:
5.1 go to "http://hostname:port/apex/apex_admin" and login using "admin" [if will prompt for passwrod change, change it according to the rule]
5.2 Manage Workspaces > Create Workspace
- enter a workspace name and description and click Next >
- For Re-use existing schema, select No
Neter a schema name & password and quota
Click Next >
- Enter the Workspace administrator information and click Next >
- Create Workspace

6. Creating Oracle Application Express Users:6.1 go to "http://hostname:port/apex/apex_admin" and login using "admin"
6.2 create user > enter necessary info > create

7. Logging in to Your Workspace:
7.1 goto "http://hostname:port/apex"
7.2 enter the following:
Workspace field - Enter the name of your workspace
Username field - Enter your user name
Password field - Enter your case-sensitive password
7.3 login

ORA-00020

Last week we confronted ORA-00020. One of our development team complained that, whenever they were trying to connect to the DB (using JDBC) they were getting "ORA-12537: TNS: connection closed".
At the beginning we thought that it should be a problem related to network connectivity.
At first, we looked at the alert log and found several "ORA-00020", then tried to connect to the DB locally "as sysdba" got the error:

SQL> conn / as sysdba
ERROR:
ORA-00020: maximum number of processes (%s) exceeded

At this point oracle had reached to maximum limit for processes and was unable to create a new server process to serve a new session. But this error [ORA-00020] is visible only form a local connection [bypassing listener] like above and using connections via listener may show TNS error like "ORA-12537".
We also, have to remember that this is not an ORA-00018 [maximum session] error and there is no one to one relationship between number of session and number of process. But opening too many sessions from an application may be one of the causes of "ORA-00020".
In order to solve the issue we followed below steps:

- If there is any existing connection with privilege to kill oracle session then use it to kill some sessions

- If above is not true then follow below:

1. from OS use below commane to find oracle processes serving oracle DB sessions:
$ ps -ef|grep "oracle[DB_SID] (LOCAL=NO)" [ example "oracleORCL (LOCAL=NO)" for SID with ORCL ]

2. $kill - 9 some_process_id_from_above to reduce number of oracle processes to below limit, so that a new connection can be made.

3. Immediately try "conn / as sysdba"

4. Find the application/user, which may induced the problem by opening too many connections using below query:
"select count(*),username,program from v$session group by username,program"

5. Then do necessary action [ like kill session from DB,increase value parameter "process" etc]

In our case, we have found one application opening too many connections.

For more information please below:

MOS NOTE ID: 825045.1, ID 316916.1
http://www.experts-exchange.com/Database/Oracle/Q_20015348.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:4026792891308559::::P11_QUESTION_ID:5671284058977

Using Partition Exchange in Datawarehouse Environment

Our business needs:
- current data (3 months) should be on faster filesystem [RAID 1+0]
- older than 2 months should be on slower filesystem RAID 5
- older than 1 year data should be taken to Tape

So we have planed as below:
1. Current data should be kept on a Partitioned table [daily ranged partition]
So, that - each day partition will not going to contain garbage data[other day data]
- ETL processing and application design becomes simple
- tables can be managed easily.
2. Daily partition should be created on a seperate tablespace [or 10 day data will be kept on single tablespace]
3. Older partitions will be converted to stand alone tables and converted to TTS along with their tablespaces
4. Older partitions will be dropped from the current partitioned table
5. Newly created TTS with converted standalone tables will be removed from the PROD DB and moved to the slower filesystem
6. Finally these standalone tables will be reattached to the ARCHIVE database [as standalone table in partitioned view or as a partition of a table]

We have several indexes on the current partitioned table and we wanted to check the scrnerios specially related to converting a partition to a table.
So, we went through below steps:
1. create a date ranged partition
CREATE TABLE part_tab (id NUMBER, names varchar2(10) ,dates DATE)
PARTITION BY RANGE (dates)
(PARTITION PART_01_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-02', 'YYYY-MM-DD')),
PARTITION PART_02_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-03', 'YYYY-MM-DD')),
PARTITION PART_03_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-04', 'YYYY-MM-DD')),
PARTITION PART_04_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-05', 'YYYY-MM-DD')),
PARTITION PART_05_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-06', 'YYYY-MM-DD')),
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE)
);
-- create two local indexes (one unique and other normal)
create unique index idx_part_tab_unq on part_tab(id,dates) local;
create index idx_part_tab_names on part_tab(names) local;
-- create one global index
create index idx_part_tab_id on part_tab(id);

2. check the status of the patition table and its indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');

3. inset sample data
insert into part_tab values(1,'aa','1-Jun-2011');
insert into part_tab values(2,'bb','1-Jun-2011');
insert into part_tab values(3,'cc','2-Jun-2011');
insert into part_tab values(4,'dd','2-Jun-2011');
insert into part_tab values(5,'ee','3-Jun-2011');
insert into part_tab values(6,'ff','3-Jun-2011');
insert into part_tab values(7,'gg','4-Jun-2011');
insert into part_tab values(8,'hh','4-Jun-2011');
insert into part_tab values(9,'ii','5-Jun-2011');
insert into part_tab values(10,'jj','5-Jun-2011');
commit;

--check data , there should be 2 rows in each partition
select * from part_tab partition(PART_01_JUN_2011);
select * from part_tab partition(PART_02_JUN_2011);
select * from part_tab partition(PART_03_JUN_2011);
select * from part_tab partition(PART_04_JUN_2011);
select * from part_tab partition(PART_05_JUN_2011);

4. Now create the target non partitioned table with same structure and indexes [similar indexes like all local indexes, no global indexes and no extra indexes]
create table TAB_01_JUN_2011 as select * from part_tab partition(PART_01_JUN_2011) where 1=2;
create unique index idx_TAB_01_JUN_2011_unq on TAB_01_JUN_2011(id,dates);
create index idx_TAB_01_JUN_2011_names on TAB_01_JUN_2011(names);

5. check target tables indexes
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');

6. check target tables data [should be empty]
select * from TAB_01_JUN_2011;

7. now exchange the partition

ALTER TABLE part_tab
EXCHANGE PARTITION PART_01_JUN_2011 WITH TABLE TAB_01_JUN_2011
INCLUDING INDEXES
WITHOUT VALIDATION
update global indexes;

8. Now check the source partition[should be empty now]
select * from part_tab partition(PART_01_JUN_2011);

9. now check the target table [should have all the rows of the source partition]
select * from TAB_01_JUN_2011;


10. check whether indexes are ok on both
insert into TAB_01_JUN_2011 select * from TAB_01_JUN_2011; --ORA-00001, means unique index is ok
insert into part_tab select * from part_tab;--ORA-00001, means unique index is ok

select * from part_tab where names='cc';--no error and plan show that it is using index
select * from TAB_01_JUN_2011 where names='aa';--no error and plan show that it is using index
select * from part_tab where id=8;--no error and plan show that it is using index

11. check the status of partitioned table & indexes and target table and indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select partition_name from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');

12. alter table part_tab drop partition PART_01_JUN_2011 update global indexes;
select test_num_rows from dual;

13. now check whether any proceudre,function or other objects becomes invalid
--if soem object becomes invalid, create necessary scripts to recompile just it after partition exchange;
select object_name,object_type,status from user_objects where status<>'VALID';

14. Tables/partitons older than one year on the ARCHIVE DB may again converted to standalone tables and TTS and then table the whole TTS set to tape.

From above we have found that:
- Global index on the source partitioned table becomes invalid if we donot user update global indexes
- Objects(finctions & procedures) referencing to the target partitioned table becomes invalid

So, at the end we decided to go according to the plan with below conditions:
- There should not be any global index and "update global indexes" is a time consuming operation for a large table
- There should not be any object which directly references to partitions current partitioned tables

UNDO Tablespace Usage

I use below query to find UNDO tablespace usage:

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;

Temporary Tablespace Usage

I use below query to find temporary tablespace usage:


select b.tablespace_name,
b.SizeMB,
nvl(a.UsageMB, 0) as UsageMB,
b.SizeMB - nvl(a.UsageMB, 0) as FreeMB
from (select sum(BLOCKS) * b.VALUE / 1024 / 1024 as UsageMB,
tablespace as tablespace_name
from v$tempseg_usage a, v$parameter b
where b.name = 'db_block_size'
group by TABLESPACE, b.VALUE) a,
(select sum(bytes) / 1024 / 1024 as SizeMB, tablespace_name
from dba_temp_files
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name;

Oracle Central Inventory is corrupted [10.2]

We had a linux machine having oracle client 10.2.0.1 installed on it and we were preparing to install a patch for "sqlplus".
To check "opatch" we executed "opatch lsinventory" and found below output:

$ opatch lsinventory

LsInventory Session failed: OPatch failed to locate Central Inventory.
Possible causes are:
The Central Inventory is corrupted
The oraInst.loc file specified is not valid.

OPatch failed with error code 72

According to the output "Oracle Inventory" is corrupted.
Then we check the inventory location form file "/oracle/product/10.2.0/client_1/oraInst.loc" which was "/oracle/base/oraInventory".
Then we executed below set of commands [under oracle user] to repaier "Oracle Inventory":

$ mv /oracle/base/oraInventory /oracle/base/oraInventory_old
$ cd /oracle/product/10.2.0/client_1/oui/bin #location of OUI
$ ./runInstaller -silent -invPtrLoc "/oracle/product/10.2.0/client_1/oraInst.loc" -attachHome ORACLE_HOME="/oracle/product/10.2.0/client_1" ORACLE_HOME_NAME="Ora10gHome"

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
'AttachHome' was successful.

Check with opatch:
$ opatch lsinventory

Now we had "OPatch succeeded."
Thanks to http://www.oraclemasters.in/?p=412

sqlplus hangs [10.2.0.1]

On a Redhat Linux 4.3 32bit machine, we just installed oracle client 10.2.0.1.
While we were trying to use sqlplus as below, it hanged:

$ sqlplus /nolog

Not only this command all the commands with "sqlplus" were also hanged.
But tnsping was working fine.

Solution:
This is a bug [MOS Note ID 338461.1].
When the machine uptime hits 248 days, this bug hits.
In our case up time was 406 days.

And there are two solution restart the machine or apply patch 4612267.
In our case we applied the patch and just after patch installation, "sqlplus" started working fine.

Determine Number of Log Switch for Oracle Database

A very common query, which can be found from many sources. I am keeping this for my personal reference:


SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
;