RMAN恢复简单实验

一、恢复初始化参数文件：
1、自动备份还原
1）关闭数据库并启动到nomount阶段

1. SQL> shutdown immediate;
   
2. ORA-01109: database not open
   
3. Database dismounted.
   
4. ORACLE instance shut down.
   
5. 
   
6. SQL> startup nomount;
   
7. ORACLE instance started.
   
8. 
   
9. Total System Global Area 939495424 bytes
   
10. Fixed Size 2258840 bytes
    
11. Variable Size 599787624 bytes
    
12. Database Buffers 331350016 bytes
    
13. Redo Buffers 6098944 byt

2）指定DBID

1. RMAN> set dbid=4033362200;
   
2. executing command: SET DBID

3）在数据库是使用spfile启动时，使用如下语句会报错，必须使用to来指定另外一个路径(使用pfile启动可解决如下错误)

1. RMAN> restore spfile from autobackup;
   
2. 
   
3. Starting restore at 09-MAR-16
   
4. using target database control file instead of recovery catalog
   
5. allocated channel: ORA_DISK_1
   
6. channel ORA_DISK_1: SID=10 device type=DISK
   
7. 
   
8. RMAN-00571: ===========================================================
   
9. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   
10. RMAN-00571: ===========================================================
    
11. RMAN-03002: failure of restore command at 03/09/2016 19:34:28
    
12. RMAN-06564: must use the TO clause when the instance is started with SPFILE

4）使用to关键词来指定恢复spfile路径

1. RMAN> restore spfile to '/u01/spfileneal.ora' from autobackup;
   
2. 
   
3. Starting restore at 09-MAR-16
   
4. using channel ORA_DISK_1
   
5. 
   
6. recovery area destination: /u01/oracle/fast_recovery_area
   
7. database name (or database unique name) used for search: NEAL
   
8. channel ORA_DISK_1: AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp found in the recovery area
   
9. channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160309
   
10. channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
    
11. channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
    
12. Finished restore at 09-MAR-16

5）发现还原成功

1. [oracle@cancer u01]$ ll
   
2. -rw-r----- 1 oracle oinstall 3584 Mar 9 19:39 spfileneal.ora

2、手动还原

1）数据库启动到mount阶段
RMAN> alter database mount;

2）列出含有spfile的备份集

1. RMAN> list backup of spfile;
   
2. 
   
3. BS Key Type LV Size Device Type Elapsed Time Completion Time
   
4. ------- ---- -- ---------- ----------- ------------ ---------------
   
5. 393 Full 9.73M DISK 00:00:01 09-MAR-16
   
6.        BP Key: 393 Status: AVAILABLE Compressed: NO Tag: TAG20160309T141433
   
7.        Piece Name: /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
   
8.  SPFILE Included: Modification time: 09-MAR-16
   
9.  SPFILE db_unique_name: NEAL

3）通过to来制定还原路径，通过from来指定使用那个备份集用于恢复

1. RMAN> restore spfile to '/u01/neal.ora' from '/u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp';
   
2. 
   
3. Starting restore at 09-MAR-16
   
4. allocated channel: ORA_DISK_1
   
5. channel ORA_DISK_1: SID=237 device type=DISK
   
6. 
   
7. channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
   
8. channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
   
9. Finished restore at 09-MAR-16

4）检查发现还原成功

1. [oracle@cancer u01]$ ll
   
2. -rw-r----- 1 oracle oinstall 3584 Mar 9 19:47 neal.ora

二、恢复控制文件

1)启动数据库到nomount阶段，并指定DBID

1. SQL> startup nomount;
   
2. RMAN> set dbid=4033362200;

2）列出含有控制文件的备份集

1. RMAN> list backup of controlfile;
   
2. 
   
3. List of Backup Sets
   
4. ===================
   
5. BS Key Type LV Size Device Type Elapsed Time Completion Time
   
6. ------- ---- -- ---------- ----------- ------------ ---------------
   
7. 393 Full 9.73M DISK 00:00:01 09-MAR-16
   
8.        BP Key: 393 Status: AVAILABLE Compressed: NO Tag: TAG20160309T141433
   
9.        Piece Name: /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
   
10.  Control File Included: Ckp SCN: 7176260 Ckp time: 09-MAR-16

3)在nomount状态不需要制定to的路径，故此不指定也可以恢复成功

1. RMAN> restore controlfile from '/u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp';
   
2. 
   
3. Starting restore at 09-MAR-16
   
4. using target database control file instead of recovery catalog
   
5. allocated channel: ORA_DISK_1
   
6. channel ORA_DISK_1: SID=10 device type=DISK
   
7. 
   
8. channel ORA_DISK_1: restoring control file
   
9. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
   
10. output file name=/u01/oracle/oradata/neal/control01.ctl
    
11. output file name=/u01/oracle/fast_recovery_area/neal/control02.ctl
    
12. Finished restore at 09-MAR-16

4)如果数据库处于mount阶段时，使用autobackup会报错

1. RMAN> restore controlfile from autobackup;
   
2. 
   
3. Starting restore at 09-MAR-16
   
4. released channel: ORA_DISK_1
   
5. Starting implicit crosscheck backup at 09-MAR-16
   
6. allocated channel: ORA_DISK_1
   
7. channel ORA_DISK_1: SID=10 device type=DISK
   
8. Crosschecked 50 objects
   
9. Finished implicit crosscheck backup at 09-MAR-16
   
10. 
    
11. Starting implicit crosscheck copy at 09-MAR-16
    
12. using channel ORA_DISK_1
    
13. Crosschecked 2 objects
    
14. Finished implicit crosscheck copy at 09-MAR-16
    
15. 
    
16. searching for all files in the recovery area
    
17. cataloging files...
    
18. cataloging done
    
19. 
    
20. List of Cataloged Files
    
21. =======================
    
22. File Name: /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
    
23. 
    
24. using channel ORA_DISK_1
    
25. 
    
26. RMAN-00571: ===========================================================
    
27. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    
28. RMAN-00571: ===========================================================
    
29. RMAN-03002: failure of restore command at 03/09/2016 20:12:10
    
30. RMAN-06496: must use the TO clause when the database is mounted or open

5）因此在mount阶段恢复控制文件时就需制定路径

1. RMAN> restore controlfile to '/u01/a.ctl' from '/u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp';
   
2. 
   
3. Starting restore at 09-MAR-16
   
4. using channel ORA_DISK_1
   
5. 
   
6. channel ORA_DISK_1: restoring control file
   
7. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
   
8. Finished restore at 09-MAR-16

1. [oracle@cancer u01]$ ll
   
2. -rw-r----- 1 oracle oinstall 10141696 Mar 9 20:13 a.ctl

6）恢复控制文件后，需recover database,再打开数据库必须以resetlogs方式

1. SQL> alter database open resetlogs;
   
2. alter database open resetlogs
   
3. *
   
4. ERROR at line 1:
   
5. ORA-01152: file 1 was not restored from a sufficiently old backup
   
6. ORA-01110: data file 1: '/u01/oracle/oradata/neal/system01.dbf'
   
7. 
   
8. RMAN> recover database;
   
9. 
   
10. Starting recover at 09-MAR-16
    
11. using channel ORA_DISK_1
    
12. 
    
13. starting media recovery
    
14. 
    
15. archived log for thread 1 with sequence 1 is already on disk as file /u01/oracle/oradata/neal/redo01.log
    
16. archived log file name=/u01/oracle/oradata/neal/redo01.log thread=1 sequence=1
    
17. media recovery complete, elapsed time: 00:00:00
    
18. Finished recover at 09-MAR-16
    
19. 
    
20. SQL> alter database open resetlogs;
    
21. 
    
22. Database altered.

三、恢复数据文件

1）将某个dbf文件mv或者rm掉

1. [oracle@cancer neal]$ mv sales_2009_2.dbf sales_2009_2.dbf.bak

2）这里报错是因为数据库已经异常关闭

1. RMAN> sql 'alter database datafile 12 offline';
   
2. 
   
3. using target database control file instead of recovery catalog
   
4. RMAN-00571: ===========================================================
   
5. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   
6. RMAN-00571: ===========================================================
   
7. RMAN-03002: failure of sql command at 03/09/2016 20:26:35
   
8. RMAN-06403: could not obtain a fully authorized session
   
9. ORA-01034: ORACLE not available
   
10. ORA-27101: shared memory realm does not exist
    
11. Linux-x86_64 Error: 2: No such file or directory

3）启动数据库到mount阶段
SQL> startup mount;

4）将数据文件offline

1. RMAN> sql 'alter database datafile 12 offline';
   
2. 
   
3. using target database control file instead of recovery catalog
   
4. sql statement: alter database datafile 12 offline

5）还原restore数据文件

1. RMAN> restore datafile 12;
   
2. 
   
3. Starting restore at 09-MAR-16
   
4. using channel ORA_DISK_1
   
5. 
   
6. channel ORA_DISK_1: starting datafile backup set restore
   
7. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
   
8. channel ORA_DISK_1: restoring datafile 00012 to /u01/oracle/oradata/neal/sales_2009_2.dbf
   
9. channel ORA_DISK_1: reading from backup piece /home/oracle/backup/neal_lv0_dgr02ri7_1_1_20160309
   
10. channel ORA_DISK_1: piece handle=/home/oracle/backup/neal_lv0_dgr02ri7_1_1_20160309 tag=NEAL_LV0
    
11. channel ORA_DISK_1: restored backup piece 1
    
12. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    
13. Finished restore at 09-MAR-16

6）恢复recover数据文件

1. RMAN> recover datafile 12;
   
2. 
   
3. Starting recover at 09-MAR-16
   
4. using channel ORA_DISK_1
   
5. 
   
6. starting media recovery
   
7. media recovery complete, elapsed time: 00:00:00
   
8. 
   
9. Finished recover at 09-MAR-16

7）将数据文件online

1. RMAN> sql 'alter database datafile 12 online';
   
2. 
   
3. sql statement: alter database datafile 12 online

8）打开数据库成功
SQL> alter database open;

9）发现恢复成功

1. [oracle@cancer neal]$ ll
   
2. 
   
3. -rw-r----- 1 oracle oinstall 52436992 Mar 9 20:28 sales_2009_2.dbf
   
4. -rw-r----- 1 oracle oinstall 52436992 Mar 9 20:17 sales_2009_2.dbf.bak

四、恢复表空间

1）创建用于测试的表空间

1. SQL> create tablespace test datafile '/u01/oracle/oradata/neal/test01.dbf' size 10m;
   
2. 
   
3. Tablespace created.

2）删除表空间对应的数据文件

[oracle@cancer neal]$ rm test01.d

3）将表空间offline掉，报错

1. RMAN> sql 'alter tablespace test offline';
   
2. 
   
3. using target database control file instead of recovery catalog
   
4. sql statement: alter tablespace test offline
   
5. RMAN-00571: ===========================================================
   
6. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   
7. RMAN-00571: ===========================================================
   
8. RMAN-03009: failure of sql command on default channel at 03/09/2016 21:27:45
   
9. RMAN-11003: failure during parse/execution of SQL statement: alter tablespace test offline
   
10. ORA-01116: error in opening database file 16
    
11. ORA-01110: data file 16: '/u01/oracle/oradata/neal/test01.dbf'
    
12. ORA-27041: unable to open file
    
13. Linux-x86_64 Error: 2: No such file or directory
    
14. Additional information: 3

4）加上immediate关键字

1. RMAN> sql 'alter tablespace test offline immediate';
   
2. 
   
3. sql statement: alter tablespace test offline immediate

5）这里报错是因为test是关键字，加上''并大写可以解决

1. RMAN> restore tablespace test;
   
2. 
   
3. RMAN-00571: ===========================================================
   
4. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   
5. RMAN-00571: ===========================================================
   
6. RMAN-00558: error encountered while parsing input commands
   
7. RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"
   
8. RMAN-01007: at line 1 column 20 file: standard input
   
9. 
   
10. RMAN> restore tablespace 'TEST';
    
11. 
    
12. Starting restore at 09-MAR-16
    
13. allocated channel: ORA_DISK_1
    
14. channel ORA_DISK_1: SID=255 device type=DISK
    
15. 
    
16. channel ORA_DISK_1: starting datafile backup set restore
    
17. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    
18. channel ORA_DISK_1: restoring datafile 00016 to /u01/oracle/oradata/neal/test01.dbf
    
19. channel ORA_DISK_1: reading from backup piece /home/oracle/backup/neal_lv0_e4r02vfk_1_1_20160309
    
20. channel ORA_DISK_1: piece handle=/home/oracle/backup/neal_lv0_e4r02vfk_1_1_20160309 tag=NEAL_LV0
    
21. channel ORA_DISK_1: restored backup piece 1
    
22. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    
23. Finished restore at 09-MAR-16
    
24. 
    
25. RMAN> recover tablespace 'TEST';
    
26. 
    
27. Starting recover at 09-MAR-16
    
28. using channel ORA_DISK_1
    
29. 
    
30. starting media recovery
    
31. media recovery complete, elapsed time: 00:00:00
    
32. 
    
33. Finished recover at 09-MAR-16

6）将表空间重新online

1. RMAN> sql 'alter tablespace test online';
   
2. 
   
3. sql statement: alter tablespace test online

五、三种不完全恢复方式

1. 1）基于时间点
   
2. 
   
3. run{
   
4. set until time "to_date('2016-03-10 12:00:00','yyyy-mm-dd hh24:mi:ss')";
   
5. restore database;
   
6. recover database;
   
7. alter database open resetlogs;
   
8. }
   
9. 
   
10. 2）基于SCN
    
11. 
    
12. startup mount;
    
13. restore database until scn 10000;
    
14. recover database until scn 10000;
    
15. alter database open resetlogs;
    
16. 
    
17. 3）基于日志序列
    
18. 
    
19. startup mount;
    
20. restore database until sequence 100 thread 1;
    
21. recover database until sequence 100 thread 1;
    
22. alter database open resetlogs;

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

转载于:http://blog.itpub.net/29812844/viewspace-2063673/