RMAN恢复简单实验

一、恢复初始化参数文件：

1、自动备份还原

1）关闭数据库并启动到nomount阶段

SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area  939495424 bytesFixed Size            2258840 bytesVariable Size          599787624 bytesDatabase Buffers      331350016 bytesRedo Buffers            6098944 bytes

2）指定DBID

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

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

RMAN> restore spfile from autobackup;Starting restore at 09-MAR-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 device type=DISKRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 03/09/2016 19:34:28RMAN-06564: must use the TO clause when the instance is started with SPFILE

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

RMAN> restore spfile to '/u01/spfileneal.ora' from autobackup;Starting restore at 09-MAR-16using channel ORA_DISK_1recovery area destination: /u01/oracle/fast_recovery_areadatabase name (or database unique name) used for search: NEALchannel ORA_DISK_1: AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp found in the recovery areachannel ORA_DISK_1: looking for AUTOBACKUP on day: 20160309channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkpchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 09-MAR-16

5）发现还原成功

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

2、手动还原

1）数据库启动到mount阶段

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

2）列出含有spfile的备份集

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

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

RMAN> restore spfile to '/u01/neal.ora' from '/u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp';Starting restore at 09-MAR-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=237 device type=DISKchannel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkpchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 09-MAR-16

4）检查发现还原成功

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

二、恢复控制文件

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

SQL> startup nomount;RMAN> set dbid=4033362200;

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

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

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

RMAN> restore controlfile from '/u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp';Starting restore at 09-MAR-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/oracle/oradata/neal/control01.ctloutput file name=/u01/oracle/fast_recovery_area/neal/control02.ctlFinished restore at 09-MAR-16

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

RMAN> restore controlfile from autobackup;Starting restore at 09-MAR-16released channel: ORA_DISK_1Starting implicit crosscheck backup at 09-MAR-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 device type=DISKCrosschecked 50 objectsFinished implicit crosscheck backup at 09-MAR-16Starting implicit crosscheck copy at 09-MAR-16using channel ORA_DISK_1Crosschecked 2 objectsFinished implicit crosscheck copy at 09-MAR-16searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkpusing channel ORA_DISK_1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 03/09/2016 20:12:10RMAN-06496: must use the TO clause when the database is mounted or open

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

RMAN> restore controlfile to '/u01/a.ctl' from '/u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp';Starting restore at 09-MAR-16using channel ORA_DISK_1channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 09-MAR-16[oracle@cancer u01]$ ll-rw-r-----  1 oracle oinstall 10141696 Mar  9 20:13 a.ctl

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

SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: '/u01/oracle/oradata/neal/system01.dbf'RMAN> recover database;Starting recover at 09-MAR-16using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 1 is already on disk as file /u01/oracle/oradata/neal/redo01.logarchived log file name=/u01/oracle/oradata/neal/redo01.log thread=1 sequence=1media recovery complete, elapsed time: 00:00:00Finished recover at 09-MAR-16SQL> alter database open resetlogs;Database altered.

三、恢复数据文件
1）将某个dbf文件mv或者rm掉
[oracle@cancer neal]$ mv sales_2009_2.dbf sales_2009_2.dbf.bak

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

RMAN> sql 'alter database datafile 12 offline';using target database control file instead of recovery catalogRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of sql command at 03/09/2016 20:26:35RMAN-06403: could not obtain a fully authorized sessionORA-01034: ORACLE not availableORA-27101: shared memory realm does not existLinux-x86_64 Error: 2: No such file or directory

3）启动数据库到mount阶段

SQL> startup mount;ORACLE instance started.

4）将数据文件offline

RMAN> sql 'alter database datafile 12 offline';using target database control file instead of recovery catalogsql statement: alter database datafile 12 offline

5）还原restore数据文件

RMAN> restore datafile 12;Starting restore at 09-MAR-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00012 to /u01/oracle/oradata/neal/sales_2009_2.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/backup/neal_lv0_dgr02ri7_1_1_20160309channel ORA_DISK_1: piece handle=/home/oracle/backup/neal_lv0_dgr02ri7_1_1_20160309 tag=NEAL_LV0channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 09-MAR-16

6）恢复recover数据文件

RMAN> recover datafile 12;Starting recover at 09-MAR-16using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 09-MAR-16

7）将数据文件online

RMAN> sql 'alter database datafile 12 online';sql statement: alter database datafile 12 online

8）打开数据库成功

SQL> alter database open;Database altered.

9）发现恢复成功

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

四、恢复表空间

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

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

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

[oracle@cancer neal]$ rm test01.d

3）将表空间offline掉，报错

RMAN> sql 'alter tablespace test offline';using target database control file instead of recovery catalogsql statement: alter tablespace test offlineRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03009: failure of sql command on default channel at 03/09/2016 21:27:45RMAN-11003: failure during parse/execution of SQL statement: alter tablespace test offlineORA-01116: error in opening database file 16ORA-01110: data file 16: '/u01/oracle/oradata/neal/test01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

4）加上immediate关键字

RMAN> sql 'alter tablespace test offline immediate';sql statement: alter tablespace test offline immediate

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

RMAN> restore tablespace test;RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"RMAN-01007: at line 1 column 20 file: standard inputRMAN> restore tablespace 'TEST';Starting restore at 09-MAR-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=255 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00016 to /u01/oracle/oradata/neal/test01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/backup/neal_lv0_e4r02vfk_1_1_20160309channel ORA_DISK_1: piece handle=/home/oracle/backup/neal_lv0_e4r02vfk_1_1_20160309 tag=NEAL_LV0channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 09-MAR-16RMAN> recover tablespace 'TEST';Starting recover at 09-MAR-16using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 09-MAR-16

6）将表空间重新online

RMAN> sql 'alter tablespace test online';sql statement: alter tablespace test online

五、三种不完全恢复方式

1）基于时间点

run{ set until time "to_date('2016-03-10 12:00:00','yyyy-mm-dd hh24:mi:ss')"; restore database; recover database; alter database open resetlogs;}

2）基于SCN

startup mount;restore database until scn 10000;recover database until scn 10000;alter database open resetlogs;

3）基于日志序列

startup mount;restore database until sequence 100 thread 1;recover database until sequence 100 thread 1;alter database open resetlogs;