一、用rman迁移数据库,恢复到指定的日志序列号 sequence#
1、 rman 备份源端数据库过程
[oracle@haozg backup]$
[oracle@haozg backup]$ rman target /
[oracle@haozg backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 14 18:29:45 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4179426506)
RMAN> run{
allocate channel c1 type disk maxpiecesize=500m;
backup current controlfile format '/u01/app/flash_recovery_area/backup/ctl_%d_%s';
backup full database format '/u01/app/flash_recovery_area/backup/db_%d_%s_%p_%t';
sql 'alter system archive log current';
release channel c1;
}2> 3> 4> 5> 6> 7>
allocate channel c1 type disk maxpiecesize=500m;
backup current controlfile format '/u01/app/flash_recovery_area/backup/ctl_%d_%s';
backup full database format '/u01/app/flash_recovery_area/backup/db_%d_%s_%p_%t';
sql 'alter system archive log current';
release channel c1;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=35 device type=DISK
allocated channel: c1
channel c1: SID=35 device type=DISK
Starting backup at 14-MAY-12
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 14-MAY-12
channel c1: finished piece 1 at 14-MAY-12
piece handle=/u01/app/flash_recovery_area/backup/ctl_ORA11G_1 tag=TAG20120514T182951 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-MAY-12
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 14-MAY-12
channel c1: finished piece 1 at 14-MAY-12
piece handle=/u01/app/flash_recovery_area/backup/ctl_ORA11G_1 tag=TAG20120514T182951 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-MAY-12
Starting backup at 14-MAY-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oradata/system01.dbf
input datafile file number=00004 name=/u01/app/oradata/user01.dbf
input datafile file number=00002 name=/u01/app/oradata/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oradata/undo01.dbf
channel c1: starting piece 1 at 14-MAY-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oradata/system01.dbf
input datafile file number=00004 name=/u01/app/oradata/user01.dbf
input datafile file number=00002 name=/u01/app/oradata/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oradata/undo01.dbf
channel c1: starting piece 1 at 14-MAY-12
channel c1: finished piece 1 at 14-MAY-12
piece handle=/u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593 tag=TAG20120514T182953 comment=NONE
channel c1: backup set complete, elapsed time: 00:04:05
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 14-MAY-12
channel c1: finished piece 1 at 14-MAY-12
piece handle=/u01/app/flash_recovery_area/backup/db_ORA11G_3_1_783282839 tag=TAG20120514T182953 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-MAY-12
piece handle=/u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593 tag=TAG20120514T182953 comment=NONE
channel c1: backup set complete, elapsed time: 00:04:05
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 14-MAY-12
channel c1: finished piece 1 at 14-MAY-12
piece handle=/u01/app/flash_recovery_area/backup/db_ORA11G_3_1_783282839 tag=TAG20120514T182953 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-MAY-12
sql statement: alter system archive log current
released channel: c1
RMAN>
RMAN>
2、把spfile文件和rman备份集复制到目标端的对应目录下,然后在目标端启动实例。过程如下:
[oracle@haozg dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 16 13:48:47 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomout;
SP2-0714: invalid combination of STARTUP options
SQL> startup nomount
ORACLE instance started.
SP2-0714: invalid combination of STARTUP options
SQL> startup nomount
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL>
SQL>
SQL> create pfile from spfile;
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL>
SQL>
SQL> create pfile from spfile;
File created.
SQL> exi
SP2-0042: unknown command "exi" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@haozg dbs]$ ls
hc_ora11g.dat init.ora.bak peshm_ora11g_0
initora11g.ora lkORA11G spfileora11g.ora
[oracle@haozg dbs]$
SP2-0042: unknown command "exi" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@haozg dbs]$ ls
hc_ora11g.dat init.ora.bak peshm_ora11g_0
initora11g.ora lkORA11G spfileora11g.ora
[oracle@haozg dbs]$
3、查看目标端数据库的dbid
[oracle@haozg backup]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 14 19:24:13 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select dbid from v$database;
DBID
----------
4179426506
----------
4179426506
4、rman到目标数据库,指定dbid
[oracle@haozg dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 16 13:57:22 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (not mounted)
RMAN> set dbid=4179426506
executing command: SET DBID
RMAN>
5、在目标端,从指定备份集中恢复控制文件
RMAN> restore controlfile from '/u01/app/flash_recovery_area/backup/ctl_ORA11G_1';
Starting restore at 16-MAY-12
using channel ORA_DISK_1
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oradata/control1.ctl
output file name=/u01/app/oradata/control2.ctl
Finished restore at 16-MAY-12
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oradata/control1.ctl
output file name=/u01/app/oradata/control2.ctl
Finished restore at 16-MAY-12
RMAN>
----查看恢复的控制文件
[oracle@haozg oradata]$ ls -al
total 19048
drwxr-xr-x. 2 oracle oinstall 4096 May 16 14:09 .
drwxrwxr-x. 9 oracle oinstall 4096 May 16 09:44 ..
-rw-r-----. 1 oracle oinstall 9748480 May 16 14:11 control1.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 16 14:11 control2.ctl
[oracle@haozg oradata]$ ls -al
total 19048
drwxr-xr-x. 2 oracle oinstall 4096 May 16 14:09 .
drwxrwxr-x. 9 oracle oinstall 4096 May 16 09:44 ..
-rw-r-----. 1 oracle oinstall 9748480 May 16 14:11 control1.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 16 14:11 control2.ctl
6、启动数据库到muout状态
RMAN> startup mount;
database is already started
database mounted
released channel: ORA_DISK_1
database mounted
released channel: ORA_DISK_1
RMAN>
7、转储数据文件
RMAN> restore database;
RMAN> restore database;
Starting restore at 16-MAY-12
Starting implicit crosscheck backup at 16-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Finished implicit crosscheck backup at 16-MAY-12
Starting implicit crosscheck backup at 16-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Finished implicit crosscheck backup at 16-MAY-12
Starting implicit crosscheck copy at 16-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-MAY-12
searching for all files in the recovery area
cataloging files...
no files cataloged
cataloging files...
no files cataloged
using channel ORA_DISK_1
creating datafile file number=1 name=/u01/app/oradata/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/16/2012 14:15:21
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/app/oradata/system01.dbf'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/16/2012 14:15:21
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/app/oradata/system01.dbf'
RMAN>
出现上面的错误,处理方法如下:
RMAN> catalog start with '/u01/app/flash_recovery_area/backup';
searching for all files that match the pattern /u01/app/flash_recovery_area/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_3_1_783282839
File Name: /u01/app/flash_recovery_area/backup/ctl_ORA11G_1
=====================================
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_3_1_783282839
File Name: /u01/app/flash_recovery_area/backup/ctl_ORA11G_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_3_1_783282839
File Name: /u01/app/flash_recovery_area/backup/ctl_ORA11G_1
=======================
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593
File Name: /u01/app/flash_recovery_area/backup/db_ORA11G_3_1_783282839
File Name: /u01/app/flash_recovery_area/backup/ctl_ORA11G_1
RMAN>
继续转储数据文件
RMAN> restore database;
Starting restore at 16-MAY-12
using channel ORA_DISK_1
using channel ORA_DISK_1
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/app/oradata/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oradata/undo01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oradata/user01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593
channel ORA_DISK_1: piece handle=/u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593 tag=TAG20120514T182953
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 16-MAY-12
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oradata/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oradata/undo01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oradata/user01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593
channel ORA_DISK_1: piece handle=/u01/app/flash_recovery_area/backup/db_ORA11G_2_1_783282593 tag=TAG20120514T182953
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 16-MAY-12
RMAN>
8、恢复数据库,恢复到指定的日志sequence#,做不完全恢复。
在源端进行日志切换 alter system switch logfile; 把生成的归档日志拷贝到目标端的对应目录下,我把sequenc#为16、17、18的
归档日志都拷贝到了目标端。
归档日志都拷贝到了目标端。
recover database until sequence 15 thread 1;
datafile 1 must be restored from backup older than SCN 221707
datafile 1 must be restored from backup older than SCN 221707
recover database until sequence 16 thread 1;
RMAN-06556: datafile 1 must be restored from backup older than SCN 248060
RMAN-06556: datafile 1 must be restored from backup older than SCN 248060
recover database until sequence 17 thread 1;
提示找不到sequence# 为16的归档日志。
原因是我用系统默认的归档路径存储归档日志,生成的归档日志文件名是系统自动命名的,和参数
log_archive_format string %t_%s_%r.dbf
的格式不一样,在用归档日志恢复的时候要按照参数指定的格式寻找对应的归档日志,但是这个格式和系统自动生成的归档日志格式
不一样导致找不到归档日志文件。
所以在rman做备份恢复的时候要指定归档路径log_archive_dest_1,可以避免上面的错误发生。
log_archive_format string %t_%s_%r.dbf
的格式不一样,在用归档日志恢复的时候要按照参数指定的格式寻找对应的归档日志,但是这个格式和系统自动生成的归档日志格式
不一样导致找不到归档日志文件。
所以在rman做备份恢复的时候要指定归档路径log_archive_dest_1,可以避免上面的错误发生。
但是如果遇到上面的问题处理方法如下:执行下面的命令:
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc';
过程如下:
RMAN> catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc';
cataloged archived log
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc RECID=2 STAMP=783444095
RMAN> catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc';
catalog archivelog '/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc';
cataloged archived log
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc RECID=2 STAMP=783444095
RMAN>
cataloged archived log
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc RECID=3 STAMP=783444096
cataloged archived log
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc RECID=3 STAMP=783444096
RMAN>
cataloged archived log
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc RECID=4 STAMP=783444097
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc RECID=4 STAMP=783444097
RMAN>
RMAN>
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 16 A 14-MAY-12
Name: /u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc
------- ---- ------- - ---------
2 1 16 A 14-MAY-12
Name: /u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc
3 1 17 A 14-MAY-12
Name: /u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc
Name: /u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_17_7v1xt9f9_.arc
4 1 18 A 14-MAY-12
Name: /u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc
Name: /u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_18_7v1ysry3_.arc
RMAN> recover database until sequence 17 thread 1;
Starting recover at 16-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
starting media recovery
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc thread=1 sequence=16
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-MAY-12
archived log file name=/u01/app/flash_recovery_area/ORA11G/archivelog/2012_05_14/o1_mf_1_16_7v1qwz45_.arc thread=1 sequence=16
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-MAY-12
RMAN>
9、以resetlogs方式打开数据库
RMAN> alter database open resetlogs;
database opened
RMAN>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23062014/viewspace-730696/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23062014/viewspace-730696/