RMAN 备份异机恢复 并创建新DBID

测试平台信息：

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

---------------------------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

网上资源： http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823

聊天 群：40132017

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请