Oracle 11gR2 使用 rman 達到 database 異機還原的目標

2013-07-13 02:13

 

1. 環境介紹 

Source DB : SIPDB   

Destination DB : SIPDB2  

SID : orcl

 

 

2.進行 Source DB 備份作業

SIPDB 要開啟 Archive Log mode

 
[oracle@SIPDB ~]$ sqlplus / as sysdba
SQL> shutdown immediate;

SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

SQL> archive log list
Database log mode                   Archive Mode
Automatic archival                    Enabled
Archive destination                   USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence       6346
Next log sequence to archive     6348
Current log sequence                6348
 

 

在 SIPDB 建立 /data/backup 資料夾來存放備份檔案,並設定 oracle 為其 owner

 

[root@SIPDB /]# mkdir -p /data/backup/
 
[root@SIPDB /]# chown -R oracle.dba /data/backup/

 

編寫 rman 備份 script ,其內容如下

[oracle@SIPDB ~]$ cat backup_db.rman
run {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate channel c3 type disk;
 allocate channel c4 type disk;
 allocate channel c5 type disk;
 backup database format '/data/backup/%d_%U_%n_%T' ;
 backup archivelog all delete all input format '/data/backup/ARC_%U_%n_%T';
 backup current controlfile format '/data/backup/%d_control_%U_%n_%T.ctl';
     }
 
#########################################
 
%d       資料庫名稱
%u       一個八個字符的名稱代表備份集與創建時間
%n       資料庫名稱,向右填補到最大八個字符
%T       年月日格式(YYYYMMDD)
 
#########################################
 
用 oracle 身份執行 rman 備份
 
[oracle@SIPDB ~]$ rman target / nocatalog @backup_db.rman
 
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 13 02:25:20 2013
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ORCL (DBID=1333674623)
using target database control file instead of recovery catalog
 
RMAN> run {
2>  allocate channel c1 type disk;
3>  allocate channel c2 type disk;
4>  allocate channel c3 type disk;
5>  allocate channel c4 type disk;
6>  allocate channel c5 type disk;
7>  backup database format '/data/backup/%d_%U_%n_%T' ;
8>  backup archivelog all delete all input format '/data/backup/ARC_%U_%n_%T';
9>  backup current controlfile format '/data/backup/%d_control_%U_%n_%T.ctl';
10>      }
11>
allocated channel: c1
channel c1: SID=1160 device type=DISK
 
allocated channel: c2
channel c2: SID=20 device type=DISK
 
allocated channel: c3
channel c3: SID=396 device type=DISK
 
allocated channel: c4
channel c4: SID=776 device type=DISK
 
allocated channel: c5
channel c5: SID=1162 device type=DISK
 
Starting backup at 13-JUL-13
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/u01/app/oracle/oradata/orcl/undotbs01.dbf
channel c1: starting piece 1 at 13-JUL-13
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00004 name=/opt/u01/app/oracle/oradata/orcl/users01.dbf
channel c2: starting piece 1 at 13-JUL-13
channel c3: starting full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel c3: starting piece 1 at 13-JUL-13
channel c4: starting full datafile backup set
channel c4: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/u01/app/oracle/oradata/orcl/system01.dbf
channel c4: starting piece 1 at 13-JUL-13
channel c5: starting full datafile backup set
channel c5: specifying datafile(s) in backup set
input datafile file number=00005 name=/opt/u01/app/oracle/oradata/orcl/example01.dbf
channel c5: starting piece 1 at 13-JUL-13
channel c1: finished piece 1 at 13-JUL-13
piece handle=/data/backup/ORCL_2boejq8p_1_1_ORCLxxxx_20130713 tag=TAG20130713T022528 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:28
channel c5: finished piece 1 at 13-JUL-13
piece handle=/data/backup/ORCL_2foejq8q_1_1_ORCLxxxx_20130713 tag=TAG20130713T022528 comment=NONE
channel c5: backup set complete, elapsed time: 00:00:26
channel c3: finished piece 1 at 13-JUL-13
piece handle=/data/backup/ORCL_2doejq8p_1_1_ORCLxxxx_20130713 tag=TAG20130713T022528 comment=NONE
channel c3: backup set complete, elapsed time: 00:01:35
channel c4: finished piece 1 at 13-JUL-13
piece handle=/data/backup/ORCL_2eoejq8p_1_1_ORCLxxxx_20130713 tag=TAG20130713T022528 comment=NONE
channel c4: backup set complete, elapsed time: 00:01:35
channel c2: finished piece 1 at 13-JUL-13
piece handle=/data/backup/ORCL_2coejq8p_1_1_ORCLxxxx_20130713 tag=TAG20130713T022528 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:45
Finished backup at 13-JUL-13
 
Starting backup at 13-JUL-13
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6347 RECID=83 STAMP=820636098
channel c1: starting piece 1 at 13-JUL-13
channel c1: finished piece 1 at 13-JUL-13
piece handle=/data/backup/ARC_2goejqe3_1_1_ORCLxxxx_20130713 tag=TAG20130713T022819 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/opt/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_13/o1_mf_1_6347_8y0lp21f_.arc RECID=83 STAMP=820636098
Finished backup at 13-JUL-13
 
Starting backup at 13-JUL-13
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 13-JUL-13
channel c1: finished piece 1 at 13-JUL-13
piece handle=/data/backup/ORCL_control_2hoejqe6_1_1_ORCLxxxx_20130713.ctl tag=TAG20130713T022821 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUL-13
 
Starting Control File and SPFILE Autobackup at 13-JUL-13
piece handle=/opt/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2013_07_13/o1_mf_s_820636105_8y0lp9pq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-JUL-13
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
 
Recovery Manager complete.
 
 
這時備份檔已經存在 /data/backup/ 了
 
[oracle@SIPDB ~]$ ls -al /data/backup/
total 3767716
drwxr-xr-x 2 oracle dba            4096 Jul 13 02:28 .
drwxr-xr-x 3 root   root           4096 Jul 13 02:18 ..
-rw-r----- 1 oracle oinstall    5801984 Jul 13 02:28 ARC_2goejqe3_1_1_ORCLxxxx_20130713
-rw-r----- 1 oracle oinstall    5955584 Jul 13 02:25 ORCL_2boejq8p_1_1_ORCLxxxx_20130713
-rw-r----- 1 oracle oinstall 2340970496 Jul 13 02:28 ORCL_2coejq8p_1_1_ORCLxxxx_20130713
-rw-r----- 1 oracle oinstall  760242176 Jul 13 02:26 ORCL_2doejq8p_1_1_ORCLxxxx_20130713
-rw-r----- 1 oracle oinstall  658243584 Jul 13 02:26 ORCL_2eoejq8p_1_1_ORCLxxxx_20130713
-rw-r----- 1 oracle oinstall   72982528 Jul 13 02:25 ORCL_2foejq8q_1_1_ORCLxxxx_20130713
-rw-r----- 1 oracle oinstall   10125312 Jul 13 02:28 ORCL_control_2hoejqe6_1_1_ORCLxxxx_20130713.ctl
 
 

 

3. 進行 Destination DB 還原作業

先在 Destination DB 主機上安裝 Oracle 11gR2,但不用建立資料庫
 
把 Source DB 的 /data/backup 中的檔案複製到 Destination DB
[oracle@SIPDB ~]$ scp -r /data/backup/ SIPDB2:/data/backup/
ORCL_2eoejq8p_1_1_ORCLxxxx_20130713                                       100%  628MB  25.1MB/s   00:25
ARC_2goejqe3_1_1_ORCLxxxx_20130713                                         100% 5666KB   5.5MB/s   00:00
ORCL_2boejq8p_1_1_ORCLxxxx_20130713                                       100% 5816KB   5.7MB/s   00:00
ORCL_2coejq8p_1_1_ORCLxxxx_20130713                                       100% 2233MB  25.1MB/s   01:29
ORCL_2foejq8q_1_1_ORCLxxxx_20130713                                        100%   70MB  23.2MB/s   00:03
ORCL_2doejq8p_1_1_ORCLxxxx_20130713                                       100%  725MB  25.9MB/s   00:28
ORCL_control_2hoejqe6_1_1_ORCLxxxx_20130713.ctl                       100% 9888KB   9.7MB/s   00:01
 
 

複製 SIPDB  spfileorcl.ora、orapworcl 到 SIPDB2 $ORACLE_HOME/$ORACLE_HOME/dbs/ 下

[oracle@SIPDB dbs]$ scp spfileorcl.ora SIPDB2:/opt/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora           100% 3584     3.5KB/s   00:00
[oracle@SIPDB dbs]$ scp orapworcl SIPDB2:/opt/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl                 
100% 1536     1.5KB/s   00:00
 

 

複製 SIPDB  listener.ora、tnsnames.ora 到 SIPDB2 $ORACLE_HOME/$ORACLE_HOME/network/admin/ 下

[oracle@SIPDB admin]$ scp listener.ora SIPDB2:/opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora                                                         100%  378     0.4KB/s   00:00
[oracle@SIPDB admin]$ scp tnsnames.ora SIPDB2:/opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora      
 
#########################################
 
HOST 要改成自己的Hostname  (HOST = SIPDB2)
 
#########################################

 

4. 進行 Destination DB 還原作業

    

在 SIPDB2 中建立 $ORACLE_BASE/flash_recovery_area 目錄

[oracle@SIPDB2 ~]$ mkdir $ORACLE_BASE/flash_recovery_area
 
將 DB 開到 nomount mode
[oracle@SIPDB2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 13 03:05:37 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area    3340451840 bytes
Fixed Size                          2217952 bytes
Variable Size                      2516584480 bytes
Database Buffers                805306368 bytes
Redo Buffers                      16343040 bytes
 
 
建立 $ORACLE_BASE/oradata/orcl/  目錄
[oracle@SIPDB2 ~]$ mkdir -p $ORACLE_BASE/oradata/orcl/
 
建立 $ORACLE_BASE/flash_recovery_area/orcl/  目錄
[oracle@SIPDB2 ~]$ mkdir -p $ORACLE_BASE/flash_recovery_area/orcl/
 
用 rman 回覆 control file
[oracle@SIPDB2 ~]$ rman target / nocatalog
 
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 13 03:15:01 2013
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ORCL (not mounted)
using target database control file instead of recovery catalog
 
RMAN> SET DBID=1333674623
 
executing command: SET DBID
 
RMAN> restore controlfile from '/data/backup/ORCL_control_29oejj73_1_1_ORCLxxxx_20130713.ctl';
 
Starting restore at 13-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK
 
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/opt/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 13-JUL-13
 
 
將 DB 開到 mount mode
RMAN> alter database mount;
 
database mounted
released channel: ORA_DISK_1
 
 
啟動 listener
[oracle@SIPDB2 ~]$ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-JUL-2013 03:18:38
 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
Starting /opt/u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/u01/app/oracle/diag/tnslsnr/SIPDB2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                13-JUL-2013 03:18:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/u01/app/oracle/diag/tnslsnr/SIPDB2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully
 
在 rman 下執行 restore database;
RMAN> restore database;
 
Starting restore at 13-JUL-13
Starting implicit crosscheck backup at 13-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=767 device type=DISK
Crosschecked 6 objects
Crosschecked 52 objects
Finished implicit crosscheck backup at 13-JUL-13
 
Starting implicit crosscheck copy at 13-JUL-13
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 13-JUL-13
 
searching for all files in the recovery area
cataloging files...
no files cataloged
 
using channel ORA_DISK_1
using channel ORA_DISK_2
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /opt/u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /data/backup/ORCL_23oejj41_1_1_ORCLxxxx_20130713
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00005 to /opt/u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_2: reading from backup piece /data/backup/ORCL_27oejj42_1_1_ORCLxxxx_20130713
channel ORA_DISK_2: piece handle=/data/backup/ORCL_27oejj42_1_1_ORCLxxxx_20130713 tag=TAG20130713T002329
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00002 to /opt/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_2: reading from backup piece /data/backup/ORCL_25oejj42_1_1_ORCLxxxx_20130713
channel ORA_DISK_1: piece handle=/data/backup/ORCL_23oejj41_1_1_ORCLxxxx_20130713 tag=TAG20130713T002329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:23
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /data/backup/ORCL_26oejj42_1_1_ORCLxxxx_20130713
channel ORA_DISK_2: piece handle=/data/backup/ORCL_25oejj42_1_1_ORCLxxxx_20130713 tag=TAG20130713T002329
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:40
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00004 to /opt/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_2: reading from backup piece /data/backup/ORCL_24oejj42_1_1_ORCLxxxx_20130713
channel ORA_DISK_1: piece handle=/data/backup/ORCL_26oejj42_1_1_ORCLxxxx_20130713 tag=TAG20130713T002329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:51
channel ORA_DISK_2: piece handle=/data/backup/ORCL_24oejj42_1_1_ORCLxxxx_20130713 tag=TAG20130713T002329
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:35
Finished restore at 13-JUL-13
 
 
在 rman 下執行 recover database;
RMAN> recover database;
 
Starting recover at 13-JUL-13
using channel ORA_DISK_1
using channel ORA_DISK_2
 
starting media recovery
 
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6346
channel ORA_DISK_1: reading from backup piece /data/backup/ARC_28oejj71_1_1_ORCLxxxx_20130713
channel ORA_DISK_1: piece handle=/data/backup/ARC_28oejj71_1_1_ORCLxxxx_20130713 tag=TAG20130713T002505
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_13/o1_mf_1_6346_8y0pmht5_.arc thread=1 sequence=6346
channel default: deleting archived log(s)
archived log file name=/opt/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_07_13/o1_mf_1_6346_8y0pmht5_.arc RECID=83 STAMP=820640111
unable to find archived log
archived log thread=1 sequence=6347
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/13/2013 03:35:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6347 and starting SCN of 150484413
 
 
在 rman 下執行 alter database open RESETLOGS;
RMAN> alter database open RESETLOGS;
 
database opened
 
 

 

5. 比對 Source DB 和 Destination DB 的資料

 

[oracle@SIPDB oracle]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 13 03:54:53 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select count(endtime) from voip.cdr;
 
COUNT(ENDTIME)
--------------
       7200370
 
 
 
[oracle@SIPDB2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 13 03:55:25 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select count(endtime) from voip.cdr;
 
COUNT(ENDTIME)
--------------
       7200370