How to Restore RMAN backup from one server to another server.

I remembered one time when I was assigned to restore RMAN backup files from PROD server located locally to a TEST server located abroad. I will present how I successfully accomplished my task in this blog.

Environment:
PROD Server
Operating System: RHEL 5.4
Database: Oracle Database 10.2.0.1

TEST Server:
Operating System: RHEL 5.4
Database: Oracle Database 10.2.0.4
(residing in a Oracle VM and the directories of all Oracle related files are the same with the PROD environment.)

Task: 
Restore the latest RMAN backup from PROD server to TEST server.

How To Do It:

1. Check the RMAN backup generated in the PROD server and validate if it is a good backup.

***The client has a backup generated using the following command:
RMAN> backup incremental level 0 database plus archivelog;
I reviewed the RMAN logs generated and took note of the location of the backup files generated by this backup. This will be the backupsets I will use to restore the TEST database.

Here is the log file of the generated RMAN backup:

[oracle@prod ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 15 17:11:16 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: PROD (DBID=2985905122)

RMAN> backup incremental level 0 database plus archivelog;


Starting backup at 10-NOV-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=130 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=481 recid=451 stamp=735001673
input archive log thread=1 sequence=482 recid=452 stamp=735057662
input archive log thread=1 sequence=483 recid=453 stamp=735087623
input archive log thread=1 sequence=484 recid=454 stamp=735088002
(truncated...)
input archive log thread=1 sequence=528 recid=498 stamp=736380023
input archive log thread=1 sequence=529 recid=499 stamp=736383622
input archive log thread=1 sequence=530 recid=500 stamp=736384052
channel ORA_DISK_1: starting piece 1 at 15-DEC-10
channel ORA_DISK_1: finished piece 1 at 15-DEC-10
piece handle=/u01/app/oracle/PROD/flash_recovery_area/20101115-jblvkqid_1_1 tag=TAG20101215T171139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=531 recid=501 stamp=736461049
input archive log thread=1 sequence=532 recid=502 stamp=736470025
input archive log thread=1 sequence=533 recid=503 stamp=736470414
(truncated...)
input archive log thread=1 sequence=581 recid=551 stamp=737766433
input archive log thread=1 sequence=582 recid=552 stamp=737831499
channel ORA_DISK_1: starting piece 1 at 15-DEC-10
channel ORA_DISK_1: finished piece 1 at 15-DEC-10
piece handle=/u01/app/oracle/PROD/flash_recovery_area/20101115-jclvkqmm_1_1 tag=TAG20101215T171139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
Finished backup at 10-NOV-10

Starting backup at 10-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/product/10.2.0/db_1/oradata/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/product/10.2.0/db_1/oradata/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/product/10.2.0/db_1/oradata/undotbs01.dbf
(truncated...)
input datafile fno=00004 name=/u01/app/oracle/product/10.2.0/db_1/oradata/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-NOV-10
channel ORA_DISK_1: finished piece 1 at 10-NOV-10
piece handle=/u01/app/oracle/PROD/flash_recovery_area/20101115-jdlvkqr9_1_1 tag=TAG20101215T171624 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
Finished backup at 10-NOV-10

Starting backup at 10-NOV-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=583 recid=553 stamp=737831930
channel ORA_DISK_1: starting piece 1 at 10-NOV-10
channel ORA_DISK_1: finished piece 1 at 10-NOV-10
piece handle=/u01/app/oracle/PROD/flash_recovery_area/20101115-jelvkqvr_1_1 tag=TAG20101215T171851 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 10-NOV-10

Starting Control File and SPFILE Autobackup at 15-DEC-10
piece handle=/u01/app/oracle/PROD/flash_recovery_area/autobackup/2010_11_15/o1_mf_s_737831933_6jk1zxsd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-10

RMAN> restore database validate;


(output truncated...)

channel ORA_DISK_1: restore complete, elapsed time: 00:06:42
Finished restore at 10-NOV-10


2. Copy all the backup pieces generated by the RMAN backup from the PROD server to the TEST server. 


I used SCP to transport these files.


example:
[oracle@prod ~]$ scp /RMAN/File1/here /RMAN/File2/here /RMAN/File3/here oracle@ip.add.of.dest:/path/to/the/FRA


Make sure that you will throw the backup file to the specific location it belongs, ie, the backupsets must be in backupsets directory, the control file autobackup must be in the autobackup, etc...


3. After transporting the files, you are now ready to restore and recover the TEST database server. On the TEST server, which is already shut down, do the following:


[oracle@test ~]$ rman target /



Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 10 15:48:10 2010


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


connected to target database (not started)


RMAN> startup nomount;


Oracle instance started


Total System Global Area     524288000 bytes


Fixed Size                     2022080 bytes
Variable Size                142607680 bytes
Database Buffers             373293056 bytes
Redo Buffers                   6365184 bytes


RMAN> restore controlfile from "/u01/app/oracle/TEST/autobackup/2010_12_15/o1_mf_s_737831933_6jk1zxsd_.bkp";


Starting restore at 10-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/product/10.2.0/db_1/oradata/control01.ctl
output filename=/u01/app/oracle/product/10.2.0/db_1/oradata/control02.ctl
output filename=/u01/app/oracle/product/10.2.0/db_1/oradata/control03.ctl
Finished restore at 10-NOV-10


RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1


RMAN> list archivelog all;



List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
453     1    483     A 09-NOV-10 /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_11_14/o1_mf_1_483_6fzyd6yy_.arc
454     1    484     A 09-NOV-10 /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_11_14/o1_mf_1_484_6fzyr25n_.arc
455     1    485     A 09-NOV-10 /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_11_15/o1_mf_1_485_6g24pbt3_.arc
456     1    486     A 10-NOV-10 /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_11_15/o1_mf_1_486_6g2lr9dl_.arc
(truncated...)

551     1    581     A 10-NOV-10 /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_12_14/o1_mf_1_581_6jh210sj_.arc
552     1    582     A 10-NOV-10 /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_12_15/o1_mf_1_582_6jk1lbhl_.arc
553     1    583     A 10-NOV-10 /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_12_15/o1_mf_1_583_6jk1ztvj_.arc



(I will recover until the last archive log I had in my backup so I get the details of the last archive log from this command, in this case, SEQ NO 583 of THREAD 1 is the latest archivelog. So I will use SEQ NO 583 +1, which is 584 and THREAD 1 in my RMAN run block.



RMAN> run {
2> set until sequence 584 thread 1;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }




executing command: SET until clause


Starting restore at 10-NOV-10
Starting implicit crosscheck backup at 10-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 92 objects
Finished implicit crosscheck backup at 10-NOV-10


Starting implicit crosscheck copy at 10-NOV-10
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 10-NOV-10


searching for all files in the recovery area
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/autobackup/2010_12_15/o1_mf_s_737831933_6jk1zxsd_.bkp



using channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/product/10.2.0/db_1/oradata/system01.dbf
restoring datafile 00002 to /u01/app/oracle/product/10.2.0/db_1/oradata/undotbs01.dbf
(truncated...)
restoring datafile 00012 to /u01/app/oracle/product/10.2.0/db_1/oradata/TKCS701
restoring datafile 00013 to /u01/app/oracle/product/10.2.0/db_1/oradata/TKCS801
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/backupsets/20101215-jdlvkqr9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/backupsets/20101215-jdlvkqr9_1_1 tag=TAG20101215T171624
channel ORA_DISK_1: restore complete, elapsed time: 00:06:42
Finished restore at 10-NOV-10


Starting recover at 10-NOV-10
using channel ORA_DISK_1


starting media recovery


archive log thread 1 sequence 583 is already on disk as file /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_12_15/o1_mf_1_583_6jk1ztvj_.arc
archive log filename=/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/archivelog/2010_12_15/o1_mf_1_583_6jk1ztvj_.arc thread=1 sequence=583
media recovery complete, elapsed time: 00:00:02
Finished recover at 10-NOV-10


database opened


RMAN>


And that's how I did the RMAN restoration. Until next blog. . . . .




Gilbert S Dela Cruz
Oracle Database Administrator
Oracle Database 11g Certified Associate
Registered Electronics Engineer
Member IECEP Philippines Inc.

Comments

  1. UnknownJanuary 24, 2011 at 7:27 AM

    Gilbert,
    Thanks a bunch for this info. Very helpful.

    Couple of questions though -

    1.what about the db name. Does it need to change anywhere? (to TEST)?
    2.POST CREATION: what about applying the archive log to TEST, archive logs generated on PROD. (ship and apply)

    ReplyDelete
  2. Gab S. Dela CruzJanuary 27, 2011 at 5:05 PM

    Hi Samuel,

    In my case, PROD's dbid and db_name are the same with TEST's. Before you mount the database, you can set the dbid of your TEST server to be identical with the PROD server. Now for the post creation, SCP the archivelogs from PROD to TEST and recover until the last seq and thread of the arcs that you transferred. BUT,,,make sure though that your test database, after restoration, SHOULD be in NOARCHIVELOG mode so that it will not generate arcs. If you would like to apply the arcs to the test server, that's the time you switch it to ARCHIVELOG mode. Well this is the easiest way of doing it..or the do the best way,,which is to clone your database using the steps I did here and configure it as a DATAGUARD. I will be posting the steps here soon. I am currently working on a project and i apologize for the delayed post.

    ReplyDelete
  3. UnknownJanuary 28, 2011 at 1:07 PM

    Hi Gab,
    Thanks for the reply. You're have actually got my question right (if possible can you forward me the steps/scripts?)

    I'll try to make it more clear.

    Our requirements are as follows, please let us know if this is possible and guide us in this -

    - We're NOT looking into - DATA GUARD or STREAMS or Advance replication or MViews or links or Triggers or GoldenGate.

    This needs to be manual process.
    Verion of Oracle 9.2.0.5.

    1. ONLINE backup (+ archive log + control file..) from production DB
    2. Copy Backup from PRODUCTION to new server.
    3. Use the backup (from PRODUCTION) to create new COPYDB on this new server.

    4. And eventually on a regular (daily/hourly) basis use the archive log of PRODUCTION
    to be applied (restored in) against COPYDB.

    Thanks.
    Sam.

    ReplyDelete

Post a Comment

Popular posts from this blog

RMAN Restoration to New Server with Different Directory and New Database Name

In this case, I have an existing production database with RMAN Level 0 backup: CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\flash_recovery_area\galaxy\backupsets\autobackup\SAMSUNG-ctl_%F'; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\flash_recovery_area\galaxy\backupsets\level0\level0-%d-%T-%U' MAXPIECESIZE 4G; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7'; SHOW ALL; REPORT SCHEMA; LIST BACKUP OF DATABASE; REPORT NEED BACKUP; REPORT UNRECOVERABLE; LIST EXPIRED BACKUP BY FILE; LIST ARCHIVELOG ALL; REPORT OBSOLETE; CROSSCHECK BACKUP DEVICE TYPE DISK; CROSSCHECK COPY OF ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK; DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK; RUN { sql 'alter system archive log current'; BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT; } LIST BACKUP
Read more

[Script] Tablespace Usage Alert

Hi, This is another variation of the previous scripts presented in this blog site. The building blocks of this script is the same as the Disk Space and ASM Diskgroup Space Monitoring scripts. This will alert you via email if a certain tablespace  reaches the set threshold. Let's share and make others love Oracle, too. For tablespace monitoring alert scripts. We have the following: Tablespace Monitoring Script You will notice that there is a pattern in the scripts creation presented in this blog.  Part 1: Report Generation : Here it is the tablespace report Part 2: Report output string process : Scripts are created to process further report into columns. Conditional statement was created to test the values in the report. Part 3: Check if the file generates alert : Email is sent if entities met the threshold. # # # # # #----- Start of Script -----###### #!/bin/bash  # Report tablespace usage  sqlplus "/ as sysdba" << EOF SET LINES 700 SET
Read more

[Script] ASM Diskgroup Space Usage Alert

Hi Again, Another important task that a DBA should do is to monitor the ASM diskgroup usage. We do not just add space to tablespaces, create more tablespaces or resize datafiles to increase storage without checking if there is space to our ASM diskgroup. Also, when we perform RMAN backups stored in ASM or Datapump Exports stored in ASM, we have to monitor and get alerts if we are running out of space. This is a simple monitoring script. I hope this could help you. Let's share and make others love Oracle, too. ASM Diskgroup  Space Monitoring ######--- Start of Script -----###### #!/bin/bash  # Report ASM Diskgroup space usage  #NOTES:  # 1. There is really a slash on the v$asm_diskgroup so Unix interpreter will treat dollar sign as a literal $ # 2. Set your tablespace threshold by changing the value of the AND clause  " >= 80" sqlplus "/ as sysdba" << EOF SET LINES 700 SET FEEDBACK OFF SET TRIMSPOOL ON SET ECHO OFF SPOOL /tmp/ASMs
Read more