12cR1 rac恢复到单机文件系统测试

本文对从12cR1 RAC到单机文件系统的还原做个简单的示例。
1、参数文件的修改

1. SQL> create pfile='/home/oracle/init.ora' from spfile;

通过上述命令转储pfile出来进行参数的修改。

2、将备份出的控制文件在单机上进行还原

1. RMAN> restore controlfile from '/recovert_area/tmp/controlfile_db_p5sb7rbl_1_1.bak;

3、还原数据库
将备份集放入recovery_area/bak/目录下，执行以下命令：

1. RMAN> catalog start with '/recovery_area/bak/';

这里的数据文件文件和临时文件的路径需要更改下，因为是还原到文件系统，这里给出示例脚本没有给出最终的数据文件名称，所以只供参考：

1. select 'set newname for tempfile '||file#|| ' to ''/ora12c/oradata/cdb/'';' from v$tempfile where con_id=1;
   
2. select 'set newname for tempfile '||file#|| ' to ''/ora12c/oradata/seed/'';' from v$tempfile where con_id=2;
   
3. select 'set newname for tempfile '||file#|| ' to ''/ora12c/oradata/pdb/pdb1/'';' from v$tempfile where con_id=3;
   
4. select 'set newname for datafile '||file#|| ' to ''/ora12c/oradata/cdb/'';' from v$datafile where con_id=1;
   
5. select 'set newname for datafile '||file#|| ' to ''/ora12c/oradata/seed/'';' from v$datafile where con_id=2;
   
6. select 'set newname for datafile '||file#|| ' to ''/ora12c/oradata/pdb/pdb1/'';' from v$datafile where con_id=3;

下面给出还原的脚本，供参考

1. run
   
2. {
   
3. set newname for datafile 1 to '/ora12c/oradata/cdb/system01.dbf';
   
4. set newname for datafile 3 to '/ora12c/oradata/cdb/sysaux01.dbf';
   
5. set newname for datafile 4 to '/ora12c/oradata/cdb/undotbs01.dbf';
   
6. set newname for datafile 6 to '/ora12c/oradata/cdb/users01.dbf';
   
7. set newname for datafile 8 to '/ora12c/oradata/cdb/undotbs02.dbf';
   
8. set newname for datafile 9 to '/ora12c/oradata/cdb/undotbs03.dbf';
   
9. set newname for datafile 5 to '/ora12c/oradata/seed/system01.dbf';
   
10. set newname for datafile 7 to '/ora12c/oradata/seed/sysaux01.dbf';
    
11. set newname for datafile 10 to '/ora12c/oradata/pdb/pdb1/clsprm_system.dbf';
    
12. set newname for datafile 11 to '/ora12c/oradata/pdb/pdb1/clsprm_sysaux.dbf';
    
13. set newname for datafile 12 to '/ora12c/oradata/pdb/pdb1/clsprm_users01.dbf';
    
14. set newname for datafile 14 to '/ora12c/oradata/pdb/pdb1/test2_01.dbf';
    
15. set newname for datafile 16 to '/ora12c/oradata/pdb/pdb1/test021.dbf';
    
16. set newname for tempfile 1 to '/ora12c/oradata/cdb/temp01.dbf';
    
17. set newname for tempfile 2 to '/ora12c/oradata/seed/temp01.dbf';
    
18. set newname for tempfile 3 to '/ora12c/oradata/pdb/pdb1/temp01.dbf';
    
19. restore database;
    
20. switch datafile all;
    
21. switch tempfile all;
    
22. }

4、恢复数据库
这里做不完全恢复。

RMAN> list backup of database;
省略部分。。。。。
 File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 40189418   06-AUG-17 /ora12c/oradata/cdb/system01.dbf
  3       Full 40189418   06-AUG-17 /ora12c/oradata/cdb/sysaux01.dbf
  4       Full 40189418   06-AUG-17 /ora12c/oradata/cdb/undotbs01.dbf
  6       Full 40189418   06-AUG-17 /ora12c/oradata/cdb/users01.dbf
省略部分。。。。。
==》通过上述部分可以看到Ckp SCN 为40189418、

RMAN>  list backup of archivelog all;
省略部分。。。
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    664     40186320   06-AUG-17 40188631   06-AUG-17
  1    665     40188631   06-AUG-17 40190128   06-AUG-17
  2    634     40186323   06-AUG-17 40188634   06-AUG-17
  2    635     40188634   06-AUG-17 40190122   06-AUG-17
  3    582     40186334   06-AUG-17 40188628   06-AUG-17
  3    583     40188628   06-AUG-17 40190125   06-AUG-17
省略部分。。。
==》通过对归档的Low SCN和Next SCN以及Ckp SCN 的分析，确认恢复到scn号为40190122。

以下给出恢复的脚本 ，供参考

1. run {
   
2. set archivelog destination to '/recovery_area/tmp/';
   
3. recover database until scn 40190122;
   
4. }

5、打开数据库

1. RMAN> alter database open resetlogs;

如果打不开，要求介质恢复，可以继续追加归档日志进行恢复。

6、删除多余的日志组和undo表空间
（1）清理日志组

1. SELECT b.member, a.group#,thread#, a.status
   
2. FROM v$log a, v$logfile b
   
3. WHERE a.group# = b.group#
   
4. ORDER BY a.thread#,b.group#;
   
5. 
   
6. MEMBER GROUP# THREAD# STATUS
   
7. ------------------------------ ---------- ---------- -------------
   
8. +CDB/cdb/redo01.log 1 1 CURRENT
   
9. +CDB/cdb/redo02.log 2 1 UNUSED
   
10. +CDB/cdb/redo03.log 3 2 INACTIVE
    
11. +CDB/cdb/redo04.log 4 2 UNUSED
    
12. +CDB/cdb/redo05.log 5 3 INACTIVE
    
13. +CDB/cdb/redo06.log 6 3 UNUSED 

==》这里删除组3，4，5，6

1. SQL> alter database disable thread 2;
   
2. Database altered.
   
3. SQL> alter database disable thread 3;
   
4. Database altered.
   
5. SQL> alter database drop logfile group 3;
   
6. Database altered.
   
7. SQL> alter database drop logfile group 4;
   
8. Database altered.
   
9. SQL> alter database drop logfile group 5;
   
10. Database altered.
    
11. SQL> alter database drop logfile group 6;
    
12. Database altered.

（2）清理unod表空间

1. SQL> select name from v$tablespace where name like 'UNDO%';
   
2. NAME
   
3. -------------------------
   
4. UNDOTBS1
   
5. UNDOTBS2
   
6. UNDOTBS3

==》通过show parameter undo_tablespace 命令确认要保留的undo,这里删除UNDOTBS2和UNDOTBS3表空间

1. SQL> drop tablespace UNDOTBS2 including contents and datafiles;
   
2. Tablespace dropped.
   
3. SQL> drop tablespace UNDOTBS3 including contents and datafiles;
   
4. Tablespace dropped.

end !

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

转载于:http://blog.itpub.net/25923810/viewspace-2143315/