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