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
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
No comments:
Post a Comment