RMAN异机恢复实验

最新推荐文章于 2022-06-15 15:24:52 发布

congkong9272

最新推荐文章于 2022-06-15 15:24:52 发布

阅读量125

点赞数

实验环境说明：
源端主机：192.168.1.11 数据库实例名：PROD1 数据库名称: PROD1 (归档模式打开 )
目标端主机：192.168.1.22 数据库实例名：TEST 数据库名称：TEST

源端数据库做备份：所有的数据文件，控制文件，参数文件

点击(此处)折叠或打开

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch2
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
#归档已开启
RMAN> backup as backupset database format '/home/oracle/full_%U.dbf';
Starting backup at 07-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-APR-17
channel ORA_DISK_1: finished piece 1 at 07-APR-17
piece handle=/home/oracle/full_07s13lfo_1_1.dbf tag=TAG20170407T153624 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-APR-17
channel ORA_DISK_1: finished piece 1 at 07-APR-17
piece handle=/home/oracle/full_08s13lg7_1_1.dbf tag=TAG20170407T153624 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-APR-17
#RMAN全库备份完毕
#/home/oracle/full_07s13lfo_1_1.dbf 是数据文件备份；/home/oracle/full_08s13lg7_1_1.dbf为参数文件和控制文件的备份

创建目录（与源端目录相同）并将源端数据库的RMAN备份文件拷贝到目标端
点击(此处)折叠或打开
1. [oracle@host02 ~]$ mkdir /home/oracle/abc
2. [oracle@host01 ~]$ scp /home/oracle/full_07s13lfo_1_1.dbf /home/oracle/full_08s13lg7_1_1.dbf 192.168.1.22:/home/oracle/
编辑目标端环境变量 SID:TEST
点击(此处)折叠或打开
1. export PATH
2. export ORACLE_BASE=/u01/app/oracle
3. export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
4. export ORACLE_SID=TEST
5. export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
配置目标库到nomount阶
1. 获取源库dbid
  点击(此处)折叠或打开
  1. [oracle@host01 ~]$ rman target /
  3. Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 15:34:45 2017
  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  7. connected to target database: PROD1 (DBID=2157889328)
2. 设置目标库dbid
  点击(此处)折叠或打开
  1. [oracle@host02 ~]$ rman target /
  3. Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 16:00:54 2017
  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  7. connected to target database (not started)
  9. RMAN> set dbid 2157889328
  11. executing command: SET DBID
3. 启动目标库至nomount
  点击(此处)折叠或打开
  1. RMAN> startup nomount;
  3. startup failed: ORA-01078: failure in processing system parameters
  4. LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initTEST.ora'
  6. starting Oracle instance without parameter file for retrieval of spfile
  7. Oracle instance started
  9. Total System Global Area 1068937216 bytes
  11. Fixed Size 2260088 bytes
  12. Variable Size 281019272 bytes
  13. Database Buffers 780140544 bytes
  14. Redo Buffers 5517312 bytes
4. 在目标库利用RMAN备份集恢复参数文件（initPROD1.ora)
  点击(此处)折叠或打开
  1. RMAN> restore spfile to pfile '?/dbs/initPROD1.ora' from '/home/oracle/full_08s13lg7_1_1.dbf';
  3. Starting restore at 07-APR-17
  4. using channel ORA_DISK_1
  6. channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/abc/full_08s13lg7_1_1.dbf
  7. channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
  8. Finished restore at 07-APR-17
5. 编辑参数文件initPROD1.ora 将PROD1替换为TEST，注意db_name仍为PROD1
  点击(此处)折叠或打开
  1. [oracle@host02 dbs]$ vi initPROD1.ora
  2. TEST.__db_cache_size=352321536
  3. TEST.__java_pool_size=4194304
  4. TEST.__large_pool_size=8388608
  5. TEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
  6. TEST.__pga_aggregate_target=335544320
  7. TEST.__sga_target=499122176
  8. TEST.__shared_io_pool_size=0
  9. TEST.__shared_pool_size=125829120
  10. TEST.__streams_pool_size=0
  11. *.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
  12. *.audit_trail='db'
  13. *.compatible='11.2.0.4.0'
  14. *.control_files='/u01/app/oracle/oradata/TEST/control01.ctl','/u01/app/oracle/oradata/TEST/control02.ctl'
  15. *.db_block_size=8192
  16. *.db_domain=''
  17. *.db_name='PROD1'
  18. *.diagnostic_dest='/u01/app/oracle'
  19. *.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
  20. *.log_archive_dest_1='location=/home/oracle/arch1'
  21. *.log_archive_dest_2='location=/home/oracle/arch2'
  22. *.memory_target=833617920
  23. *.open_cursors=300
  24. *.processes=150
  25. *.remote_login_passwordfile='EXCLUSIVE'
  26. *.undo_tablespace='UNDOTBS1'
6. 更改参数文件名字为initTEST.ora 并且通过pfile创建spfile
  点击(此处)折叠或打开
  1. [oracle@host02 ~]$ cd $ORACLE_HOME/dbs
  2. [oracle@host02 dbs]$ ls
  3. hc_TEST.dat init.ora initPROD1.ora
  4. [oracle@host02 dbs]$ mv initPROD1.ora initTEST.ora
  5. [oracle@host02 dbs]$ sqlplus / as sysdba
  7. SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 16:29:35 2017
  9. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  12. Connected to:
  13. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  14. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  16. SQL> create spfile from pfile;
  18. File created.
  20. SQL> !ls spfileTEST.ora
  21. spfileTEST.ora
7. 创建相应文件目录并通过RMAN恢复控制文件
  点击(此处)折叠或打开
  1. [oracle@host02 ~]$ mkdir -p /u01/app/oracle/admin/TEST/adump
  3. RMAN> restore controlfile from '/home/oracle/full_08s13lg7_1_1.dbf';
  5. Starting restore at 07-APR-17
  6. using target database control file instead of recovery catalog
  7. allocated channel: ORA_DISK_1
  8. channel ORA_DISK_1: SID=19 device type=DISK
  10. channel ORA_DISK_1: restoring control file
  11. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
  12. output file name=/u01/app/oracle/oradata/TEST/control01.ctl
  13. output file name=/u01/app/oracle/oradata/TEST/control02.ctl
  14. Finished restore at 07-APR-17
8. 将数据库启动到mount
  点击(此处)折叠或打开
  1. RMAN> alter database mount;
  3. database mounted
  4. released channel: ORA_DISK_1
配置目标库到mount阶段
1. 首先用set newname更改所有数据文件的位置为目标库的位置
  点击(此处)折叠或打开
  1. RMAN> run{
  2. 2> set newname for datafile 1 to '/u01/app/oracle/oradata/TEST/system01.dbf';
  3. 3> set newname for datafile 2 to '/u01/app/oracle/oradata/TEST/sysaux01.dbf';
  4. 4> set newname for datafile 3 to '/u01/app/oracle/oradata/TEST/undotbs01.dbf';
  5. 5> set newname for datafile 4 to '/u01/app/oracle/oradata/TEST/users01.dbf';
  6. 6> set newname for datafile 5 to '/u01/app/oracle/oradata/TEST/example01.dbf';
  7. 7> RESTORE DATABASE ;
  8. 8> SWITCH DATAFILE ALL;
  9. 9> RECOVER DATABASE;
  10. 10> }
  12. executing command: SET NEWNAME
  14. executing command: SET NEWNAME
  16. executing command: SET NEWNAME
  18. executing command: SET NEWNAME
  20. executing command: SET NEWNAME
  22. Starting restore at 07-APR-17
  23. using channel ORA_DISK_1
  25. channel ORA_DISK_1: starting datafile backup set restore
  26. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  27. channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/TEST/system01.dbf
  28. channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/TEST/sysaux01.dbf
  29. channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/TEST/undotbs01.dbf
  30. channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/TEST/users01.dbf
  31. channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TEST/example01.dbf
  32. channel ORA_DISK_1: reading from backup piece /home/oracle/full_07s13lfo_1_1.dbf
  33. channel ORA_DISK_1: piece handle=/home/oracle/full_07s13lfo_1_1.dbf tag=TAG20170407T153624
  34. channel ORA_DISK_1: restored backup piece 1
  35. channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
  36. Finished restore at 07-APR-17
  38. datafile 1 switched to datafile copy
  39. input datafile copy RECID=7 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/system01.dbf
  40. datafile 2 switched to datafile copy
  41. input datafile copy RECID=8 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
  42. datafile 3 switched to datafile copy
  43. input datafile copy RECID=9 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
  44. datafile 4 switched to datafile copy
  45. input datafile copy RECID=10 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/users01.dbf
  46. datafile 5 switched to datafile copy
  47. input datafile copy RECID=11 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/example01.dbf
  49. Starting recover at 07-APR-17
  50. using channel ORA_DISK_1
  52. starting media recovery
  54. unable to find archived log
  55. archived log thread=1 sequence=6
  56. RMAN-00571: ===========================================================
  57. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  58. RMAN-00571: ===========================================================
  59. RMAN-03002: failure of recover command at 04/07/2017 16:53:21
  60. RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 1010272
2. 提示报错，按SCN号恢复数据库
  点击(此处)折叠或打开
  1. RMAN> run{
  2. 2> set until SCN 1010272;
  3. 3> restore database;
  4. 4> switch datafile all;
  5. 5> recover database;
  6. 6> }
  8. executing command: SET until clause
  10. Starting restore at 07-APR-17
  11. using channel ORA_DISK_1
  13. skipping datafile 1; already restored to file /u01/app/oracle/oradata/TEST/system01.dbf
  14. skipping datafile 2; already restored to file /u01/app/oracle/oradata/TEST/sysaux01.dbf
  15. skipping datafile 3; already restored to file /u01/app/oracle/oradata/TEST/undotbs01.dbf
  16. skipping datafile 4; already restored to file /u01/app/oracle/oradata/TEST/users01.dbf
  17. skipping datafile 5; already restored to file /u01/app/oracle/oradata/TEST/example01.dbf
  18. restore not done; all files read only, offline, or already restored
  19. Finished restore at 07-APR-17
  22. Starting recover at 07-APR-17
  23. using channel ORA_DISK_1
  25. starting media recovery
  26. media recovery complete, elapsed time: 00:00:00
  28. Finished recover at 07-APR-17
3. 以resetlogs方式打开数据库
  点击(此处)折叠或打开
  1. RMAN> alter database open resetlogs;
  3. RMAN-00571: ===========================================================
  4. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  5. RMAN-00571: ===========================================================
  6. RMAN-03002: failure of alter db command at 04/07/2017 17:24:09
  7. ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/PROD1/redo01.log'
  8. ORA-27040: file create error, unable to create file
  9. Linux-x86_64 Error: 2: No such file or directory
  10. Additional information: 1
4. 提示报错（这是因为控制文件记录的还是源端日志信息）改为正确路径
  点击(此处)折叠或打开
  1. RMAN> run{
  2. 2> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo01.log'' TO ''/u01/app/oracle/oradata/TEST/redo01.log'' ";
  3. 3> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo02.log'' TO ''/u01/app/oracle/oradata/TEST/redo02.log'' ";
  4. 4> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo03.log'' TO ''/u01/app/oracle/oradata/TEST/redo03.log'' ";
  5. 5> }
  7. sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo01.log'' TO ''/u01/app/oracle/oradata/TEST/redo01.log''
  9. sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo02.log'' TO ''/u01/app/oracle/oradata/TEST/redo02.log''
  11. sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo03.log'' TO ''/u01/app/oracle/oradata/TEST/redo03.log''
5. 再次resetlogs开库
  点击(此处)折叠或打开
  1. RMAN> alter database open resetlogs;
  3. RMAN-00571: ===========================================================
  4. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  5. RMAN-00571: ===========================================================
  6. RMAN-03002: failure of alter db command at 04/07/2017 17:29:46
  7. ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
  8. ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TEST/redo03.log'
6. 根据提示将日志组clear ，开库
  点击(此处)折叠或打开
  1. RMAN> sql "alter database clear logfile group 3";
  3. sql statement: alter database clear logfile group 3
  5. RMAN> alter database open resetlogs;
  7. database opened
配置目标库到open阶段
rename临时表空间
点击(此处)折叠或打开
1. SQL> select file_name from dba_temp_files;
2. select file_name from dba_temp_files
3. *
4. ERROR at line 1:
5. ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
6. ORA-01110: data file 201: '/u01/app/oracle/oradata/PROD1/temp01.dbf'
9. SQL> shutdown immediate
10. Database closed.
11. Database dismounted.
12. ORACLE instance shut down.
13. SQL> startup mount;
14. ORACLE instance started.
16. Total System Global Area 830930944 bytes
17. Fixed Size 2257800 bytes
18. Variable Size 536874104 bytes
19. Database Buffers 289406976 bytes
20. Redo Buffers 2392064 bytes
21. Database mounted.
22. SQL> alter database rename file '/u01/app/oracle/oradata/PROD1/temp01.dbf' to '/u01/app/oracle/oradata/TEST/temp01.dbf';
24. Database altered.
26. SQL> alter database open;
28. Database altered.
30. SQL> select file_name from dba_temp_files;
32. FILE_NAME
33. --------------------------------------------------------------------------------
34. /u01/app/oracle/oradata/TEST/temp01.dbf
36. SQL> !ls
37. adump control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
38. control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

更改数据库名字及DBID

数据库启动mount
点击(此处)折叠或打开
1. SQL> shutdown immediate
2. Database closed.
3. Database dismounted.
4. ORACLE instance shut down.
5. SQL> startup mount;
6. ORACLE instance started.
8. Total System Global Area 830930944 bytes
9. Fixed Size 2257800 bytes
10. Variable Size 536874104 bytes
11. Database Buffers 289406976 bytes
12. Redo Buffers 2392064 bytes
13. Database mounted.
nid target=sys/oracle dbname=newname
点击(此处)折叠或打开
1. [oracle@host02 abc]$ nid target=sys/oracle dbname=TEST
3. DBNEWID: Release 11.2.0.4.0 - Production on Fri Apr 7 18:20:33 2017
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
7. Connected to database PROD1 (DBID=2157889328)
9. Connected to server version 11.2.0
11. Control Files in database:
12. /u01/app/oracle/oradata/TEST/control01.ctl
13. /u01/app/oracle/oradata/TEST/control02.ctl
15. Change database ID and database name PROD1 to TEST? (Y/[N]) => Y
17. Proceeding with operation
18. Changing database ID from 2157889328 to 2238816753
19. Changing database name from PROD1 to TEST
20. Control File /u01/app/oracle/oradata/TEST/control01.ctl - modified
21. Control File /u01/app/oracle/oradata/TEST/control02.ctl - modified
22. Datafile /u01/app/oracle/oradata/TEST/system01.db - dbid changed, wrote new name
23. Datafile /u01/app/oracle/oradata/TEST/sysaux01.db - dbid changed, wrote new name
24. Datafile /u01/app/oracle/oradata/TEST/undotbs01.db - dbid changed, wrote new name
25. Datafile /u01/app/oracle/oradata/TEST/users01.db - dbid changed, wrote new name
26. Datafile /u01/app/oracle/oradata/TEST/example01.db - dbid changed, wrote new name
27. Datafile /u01/app/oracle/oradata/TEST/temp01.db - dbid changed, wrote new name
28. Control File /u01/app/oracle/oradata/TEST/control01.ctl - dbid changed, wrote new name
29. Control File /u01/app/oracle/oradata/TEST/control02.ctl - dbid changed, wrote new name
30. Instance shut down
32. Database name changed to TEST.
33. Modify parameter file and generate a new password file before restarting.
34. Database ID for database TEST changed to 2238816753.
35. All previous backups and archived redo logs for this database are unusable.
36. Database has been shutdown, open database with RESETLOGS option.
37. Succesfully changed database name and ID.
38. DBNEWID - Completed succesfully.
通过spfile生成pfile，修改pfile的dbname为新的名字
点击(此处)折叠或打开
1. SQL> create pfile from spfile;
3. File created.
5. [oracle@host02 abc]$ cd $ORACLE_HOME/dbs
6. [oracle@host02 dbs]$ ls
7. hc_TEST.dat init.ora initTEST.ora lkPROD1 spfileTEST.ora
8. [oracle@host02 dbs]$ vi initTEST.ora
9. TEST.__db_cache_size=289406976
10. TEST.__java_pool_size=4194304
11. TEST.__large_pool_size=71303168
12. TEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
13. TEST.__pga_aggregate_target=335544320
14. TEST.__sga_target=499122176
15. TEST.__shared_io_pool_size=0
16. TEST.__shared_pool_size=125829120
17. TEST.__streams_pool_size=0
18. *.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
19. *.audit_trail='db'
20. *.compatible='11.2.0.4.0'
21. *.control_files='/u01/app/oracle/oradata/TEST/control01.ctl','/u01/app/oracle/oradata/TEST/control02.ctl'#Restore Controlfile
22. *.db_block_size=8192
23. *.db_domain=''
24. *.db_name='TEST'
25. *.diagnostic_dest='/u01/app/oracle'
26. *.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
27. *.log_archive_dest_1='location=/home/oracle/arch1'
28. *.log_archive_dest_2='location=/home/oracle/arch2'
29. *.memory_target=833617920
30. *.open_cursors=300
31. *.processes=150
32. *.remote_login_passwordfile='EXCLUSIVE'
33. *.undo_tablespace='UNDOTBS1'
通过pfile生成spfile
点击(此处)折叠或打开
1. SQL> create spfile from pfile;
3. File created.
启动，resetlogs
点击(此处)折叠或打开
1. SQL> alter database open resetlogs;
3. Database altered.
验证
点击(此处)折叠或打开
1. [oracle@host02 dbs]$ rman target /
3. Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 18:31:05 2017
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
7. connected to target database: TEST (DBID=2238816753)
点击(此处)折叠或打开
1. [oracle@host01 ~]$ rman target /
3. Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 18:31:01 2017
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
7. connected to target database: PROD1 (DBID=2157889328)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31405405/viewspace-2136824/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31405405/viewspace-2136824/

congkong9272

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
RMAN异机恢复实验

实验环境说明：源端主机：192.168.1.11数据库实例名：PROD1 数据库名称: PROD1 (归档模式打开 )目标端主机：192.168.1.22 数据库实例名：TEST 数据库名称：TES...
复制链接

扫一扫