测试平台信息:
Oracle:11gR2
操作系统:Redhat 5.5
Target DB:dave
几点说明:
(1)RMAN 异机恢复的时候,db_name必须相同。 如果说要想改成其他的实例名,可以在恢复成功后,用nid 命令修改。 实例名的信息会记录到控制文件里,所以如果在恢复的时候,如果实例名不一致,恢复的时候会报错。
(2)如果恢复的路径和源库不一致,就需要在restore时用set 命令指定新位置。 并且使用switch datafile all将信息更新的到控制文件。
在做duplicate的时候,RMAN 会自动根据pfile中的log_file_name_convert和db_file_name_convert来进行set 的转换。 手工restore时,只能只只能使用set 命令。
(3)异机恢复对相同目录和不同目录都做了说明。
(4)最后测试了NID 修改DBID 和DBNAME.
一. Target 库准备工作:
1. 查询DBID
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
DAVE 808234286
2. 备份DB
关于RMAN 的shell 备份脚本,参考:
Nocatalog 下的RMAN 增量备份 shell脚本
http://blog.csdn.net/tianlesoftware/archive/2011/01/26/6164931.aspx
在这里我用0级和1级备份了下DB。
[oracle@qs-dmm-rh1 backup]$ ls
arch_0pm6qt8q_1_1_20110309 dave_lev0_0jm6qt77_1_1_20110309 dave_lev1_0um6qtcq_1_1_20110309
arch_0qm6qt8q_1_1_20110309 dave_lev0_0km6qt77_1_1_20110309 dave_lev1_0vm6qtcq_1_1_20110309
arch_13m6qtda_1_1_20110309 dave_lev0_0lm6qt77_1_1_20110309 dave_lev1_11m6qtd7_1_1_20110309
arch_14m6qtda_1_1_20110309 dave_lev0_0nm6qt7c_1_1_20110309 dave_spfile_16m6qtde_1_1_20110309
ctl_file_15m6qtdc_1_1_20110309 dave_lev1_0tm6qtcq_1_1_20110309
二. Auxiliary库准备工作:
1. 创建口令文件
[oracle@qs-dmm-rh2 trace]$ orapwd file=?/dbs/orapwdave password=oracle
2. 创建相关的目录
[oracle@qs-dmm-rh2 u01]$ mkdir oradata
[oracle@qs-dmm-rh2 oradata]$ pwd
/u01/oradata
3. 创建初始化参数
将Target 库的pfile 文件copy过来。
也可以使用RMAN 从我们Target库的备份集中恢复,因为我们之前备份过spfile。不过使用RMAN, DB要先启动到nomout 状态。 这个可以用默认的init.ora 来启动。
[oracle@qs-dmm-rh2 backup]$ export ORACLE_SID=dave
[oracle@qs-dmm-rh2 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 11 15:11:31 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DAVE (not mounted)
RMAN> restore spfile to pfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdave2.ora' from '/u01/backup/dave_spfile_16m6qtde_1_1_20110309';
Starting restore at 11-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/dave_spfile_16m6qtde_1_1_20110309
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 11-MAR-11
如果修改数据文件保存的位置,那么要修改控制文件的相关的参数:
*.control_files='/u01/oradata/control01.ctl','/u01/oradata/control02.ctl','/u01/oradata/control03.ctl'
*.db_name='dave'
4. 将用pfile将Auxiliary库启动到nomout 状态
SQL> startup nomount pfile=?/dbs/initdave.ora
5. 恢复控制文件
[oracle@qs-dmm-rh2 dbs]$ export ORACLE_SID=dave
[oracle@qs-dmm-rh2 dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 11 15:25:55 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DAVE (not mounted)
RMAN> restore controlfile from '/u01/backup/ctl_file_15m6qtdc_1_1_20110309';
Starting restore at 11-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/control01.ctl
output file name=/u01/oradata/control02.ctl
output file name=/u01/oradata/control03.ctl
Finished restore at 11-MAR-11
restore 的时候需要控制文件,控制文件恢复的位置,是我们在pfile中的control_files参数控制的。
6. 将DB 启动到mout状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
7. restore 数据库
7.1 恢复目录不同的情况:
因为我们的路径不同,所以我们需要使用set 命令转换一下路径。
到Target 库查询一下:
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- -------------------------------------------
4 /u01/app/oracle/oradata/dave/users01.dbf
3 /u01/app/oracle/oradata/dave/undotbs01.dbf
2 /u01/app/oracle/oradata/dave/sysaux01.dbf
1 /u01/app/oracle/oradata/dave/system01.dbf
SQL> select file_id,file_name from dba_temp_files;
FILE_ID FILE_NAME
---------- ------------------------------------------
1 /u01/app/oracle/oradata/dave/temp01.dbf
注意,restore 的时候不会对temp 表空间进行restore。所以等restore 之后,我们需要手工创建temp表空间。
RMAN> run
2> {
set newname for datafile 1 to "/u01/oradata/system01.dbf";
3> 4> set newname for datafile 2 to "/u01/oradata/sysaux01.dbf";
5> set newname for datafile 3 to "/u01/oradata/undotbs01.dbf";
6> set newname for datafile 4 to "/u01/oradata/users01.dbf";
7> restore database;
8> switch datafile all;
9> }
对switch datafile all的说明:
--对于nocatalog 模式下,rman备份的信息是保存在控制文件里的,包括文件的路径信息。 这里的switch datafile all的作用,就是更新控制文件里的信息。
executing command: SET NEWNAME
released channel: ORA_DISK_1
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
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 /u01/oradata/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev0_0lm6qt77_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev0_0lm6qt77_1_1_20110309 tag=DAVE_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00004 to /u01/oradata/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev0_0nm6qt7c_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev0_0nm6qt7c_1_1_20110309 tag=DAVE_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00002 to /u01/oradata/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev0_0km6qt77_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev0_0km6qt77_1_1_20110309 tag=DAVE_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 /u01/oradata/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev0_0jm6qt77_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev0_0jm6qt77_1_1_20110309 tag=DAVE_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 11-MAR-11
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=745522150 file name=/u01/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=745522150 file name=/u01/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=745522150 file name=/u01/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=745522150 file name=/u01/oradata/users01.dbf
7.2 恢复目录相同
这种情况比较简单,直接:
RMAN> restore database;
8. recover DB
RMAN> recover database;
Starting recover at 11-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev1_0tm6qtcq_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev1_0tm6qtcq_1_1_20110309 tag=DAVE_LEV1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u01/oradata/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev1_0um6qtcq_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev1_0um6qtcq_1_1_20110309 tag=DAVE_LEV1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/oradata/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev1_0vm6qtcq_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev1_0vm6qtcq_1_1_20110309 tag=DAVE_LEV1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/oradata/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/dave_lev1_11m6qtd7_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/dave_lev1_11m6qtd7_1_1_20110309 tag=DAVE_LEV1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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=7
channel ORA_DISK_1: reading from backup piece /u01/backup/arch_13m6qtda_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/arch_13m6qtda_1_1_20110309 tag=ARC_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_745352047.dbf thread=1 sequence=7
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /u01/backup/arch_14m6qtda_1_1_20110309
channel ORA_DISK_1: piece handle=/u01/backup/arch_14m6qtda_1_1_20110309 tag=ARC_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_8_745352047.dbf thread=1 sequence=8
unable to find archived log
archived log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/11/2011 17:32:00
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 823627
RMAN>
之后会报一个错误:
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 823627
这里是提醒恢复到一个未知的scn号。在alter database mount之后,通过set until scn或者set until time命令设置恢复到的scn号或时间。就可以避免这个错误。
9. 用open resetlogs 打开数据库
SQL> alter database open resetlogs;
Database altered.
我测试的平台是11gR2的版本,在open resetlogs 之后,自动在原来默认的路径创建了temp 表空间和3组redo 文件。
如果恢复目录和原来相同,就不用修改。
如果目录不同,我们就需要把这些文件移到我们现在的data目录。
源目录:
[oracle@qs-dmm-rh2 dave]$ pwd
/u01/app/oracle/oradata/dave
[oracle@qs-dmm-rh2 dave]$ ls
redo01.log redo02.log redo03.log temp01.dbf
现在的目录:
[oracle@qs-dmm-rh2 dave]$ cd /u01/oradata/
[oracle@qs-dmm-rh2 oradata]$ ls
control01.ctl control02.ctl control03.ctl sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
(1)处理online redo log
SQL> select group#,bytes/1024/1024||'M',status from v$log;
GROUP# BYTES/1024/1024||'M' STATUS
---------- ----------------------------------------- ----------------
1 50M INACTIVE
2 50M CURRENT
3 50M UNUSED
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------------------------------
3 /u01/app/oracle/oradata/dave/redo03.log
2 /u01/app/oracle/oradata/dave/redo02.log
1 /u01/app/oracle/oradata/dave/redo01.log
oracle 至少有2组redo log。 所以我们可以将已经完成归档的redo drop掉, 重新创建。
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('/u01/oradata/redo03.log') size 50m;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,bytes/1024/1024||'M',status from v$log;
GROUP# BYTES/1024/1024||'M' STATUS
---------- ----------------------------------------- ----------------
1 50M INACTIVE
2 50M ACTIVE
3 50M CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 ('/u01/oradata/redo01.log') size 50m;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 ('/u01/oradata/redo02.log') size 50m;
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------------------------------
3 /u01/oradata/redo03.log
2 /u01/oradata/redo02.log
1 /u01/oradata/redo01.log
(2)处理temp 临时表空间
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dave/temp01.dbf
--表空间offline
SQL> alter database tempfile '/u01/app/oracle/oradata/dave/temp01.dbf' offline;
Database altered.
--在OS 级别移动temp 的数据文件
SQL> !mv /u01/app/oracle/oradata/dave/temp01.dbf /u01/oradata/temp01.dbf
--修改控制文件中temp文件的信息
SQL> alter database rename file '/u01/app/oracle/oradata/dave/temp01.dbf' to '/u01/oradata/temp01.dbf';
Database altered.
--temp 表空间online
SQL> alter database tempfile '/u01/oradata/temp01.dbf' online;
Database altered.
--验证
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/temp01.dbf
10. NID 修改DBID和DBNAME
先查看,这个和我们之前的一样
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
DAVE 808234286
几点说明:
(1)在修改DBID期间仍然可能会遇到不可恢复的错误。所以修改之前备份数据库,特使是控制文件。因为nid 会修改控制文件中的信息。
(2)需要将DB 启动到mount 状态才能修改。
SQL> shutdown immediate
SQL> startup mount;
在执行NID 命令之前:一定要关闭所有的session连接。
nid 命令到执行的最后会关闭数据库,如果有session 连接,就会阻止这个操作,修改dbid就会被挂死。如果中断这个操作。修改就会失败,数据库就不能mount。需要恢复。
(1)只改DBID, 不改db_name
[oracle@qs-dmm-rh2 trace]$ nid target=sys/oracle
DBNEWID: Release 11.2.0.1.0 - Production on Fri Mar 11 18:36:33 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database DAVE (DBID=808234286)
Connected to server version 11.2.0
Control Files in database:
/u01/oradata/control01.ctl
/u01/oradata/control02.ctl
/u01/oradata/control03.ctl
Change database ID of database DAVE? (Y/[N]) => y
Proceeding with operation
Changing database ID from 808234286 to 808418162
Control File /u01/oradata/control01.ctl - modified
Control File /u01/oradata/control02.ctl - modified
Control File /u01/oradata/control03.ctl - modified
Datafile /u01/app/oracle/oradata/dave/system01.db - dbid changed
Datafile /u01/app/oracle/oradata/dave/sysaux01.db - dbid changed
Datafile /u01/app/oracle/oradata/dave/undotbs01.db - dbid changed
Datafile /u01/app/oracle/oradata/dave/users01.db - dbid changed
Datafile /u01/app/oracle/oradata/dave/temp01.db - dbid changed
Control File /u01/oradata/control01.ctl - dbid changed
Control File /u01/oradata/control02.ctl - dbid changed
Control File /u01/oradata/control03.ctl - dbid changed
Instance shut down
Database ID for database DAVE changed to 808418162.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
重启打开数据库:
[oracle@qs-dmm-rh2 ~]$ export ORACLE_SID=dave
[oracle@qs-dmm-rh2 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 11 18:37:57 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 196681728 bytes
Fixed Size 1335444 bytes
Variable Size 83890028 bytes
Database Buffers 109051904 bytes
Redo Buffers 2404352 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
DAVE 808418162
SQL>
(2)修改DBID 和DB_NAME
注意一点,修改DB_NAME 之前,要将spfile 创建成pfile,因为修改dbname之后,原来的参数文件就没用了。所以要保证最新的参数。 还有修改DB_NAME的值为最新值。 修改完之后,然后用这个新参数启动DB.
[oracle@qs-dmm-rh2 trace]$ nid target=sys/oracle dbname=anqing
DBNEWID: Release 11.2.0.1.0 - Production on Fri Mar 11 18:41:01 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database DAVE (DBID=808418162)
Connected to server version 11.2.0
Control Files in database:
/u01/oradata/control01.ctl
/u01/oradata/control02.ctl
/u01/oradata/control03.ctl
Change database ID and database name DAVE to DBA? (Y/[N]) => y
Proceeding with operation
Changing database ID from 808418162 to 1204743549
Changing database name from DAVE to DBA
Control File /u01/oradata/control01.ctl - modified
Control File /u01/oradata/control02.ctl - modified
Control File /u01/oradata/control03.ctl - modified
Datafile /u01/app/oracle/oradata/dave/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dave/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dave/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dave/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/dave/temp01.db - dbid changed, wrote new name
Control File /u01/oradata/control01.ctl - dbid changed, wrote new name
Control File /u01/oradata/control02.ctl - dbid changed, wrote new name
Control File /u01/oradata/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to DBA.
Modify parameter file and generate a new password file before restarting.
Database ID for database DBA changed to 1204743549.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
验证:
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
ANQING 676458969