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

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