ITPub博客

首页 > 数据库 > Oracle > RMAN恢复简单实验

RMAN恢复简单实验

原创 Oracle 作者:梓沐 时间:2016-03-25 13:35:12 0 删除 编辑
一、恢复初始化参数文件:
1、自动备份还原
1)关闭数据库并启动到nomount阶段
  1. SQL> shutdown immediate;
  2. ORA-01109: database not open
  3. Database dismounted.
  4. ORACLE instance shut down.

  5. SQL> startup nomount;
  6. ORACLE instance started.

  7. Total System Global Area 939495424 bytes
  8. Fixed Size 2258840 bytes
  9. Variable Size 599787624 bytes
  10. Database Buffers 331350016 bytes
  11. 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. Starting restore at 09-MAR-16
  3. using target database control file instead of recovery catalog
  4. allocated channel: ORA_DISK_1
  5. channel ORA_DISK_1: SID=10 device type=DISK

  6. RMAN-00571: ===========================================================
  7. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  8. RMAN-00571: ===========================================================
  9. RMAN-03002: failure of restore command at 03/09/2016 19:34:28
  10. 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. Starting restore at 09-MAR-16
  3. using channel ORA_DISK_1

  4. recovery area destination: /u01/oracle/fast_recovery_area
  5. database name (or database unique name) used for search: NEAL
  6. 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
  7. channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160309
  8. channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
  9. channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
  10. 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. BS Key Type LV Size Device Type Elapsed Time Completion Time
  3. ------- ---- -- ---------- ----------- ------------ ---------------
  4. 393 Full 9.73M DISK 00:00:01 09-MAR-16
  5.        BP Key: 393 Status: AVAILABLE Compressed: NO Tag: TAG20160309T141433
  6.        Piece Name: /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
  7.  SPFILE Included: Modification time: 09-MAR-16
  8.  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. Starting restore at 09-MAR-16
  3. allocated channel: ORA_DISK_1
  4. channel ORA_DISK_1: SID=237 device type=DISK

  5. channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
  6. channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
  7. 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. List of Backup Sets
  3. ===================
  4. BS Key Type LV Size Device Type Elapsed Time Completion Time
  5. ------- ---- -- ---------- ----------- ------------ ---------------
  6. 393 Full 9.73M DISK 00:00:01 09-MAR-16
  7.        BP Key: 393 Status: AVAILABLE Compressed: NO Tag: TAG20160309T141433
  8.        Piece Name: /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp
  9.  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. Starting restore at 09-MAR-16
  3. using target database control file instead of recovery catalog
  4. allocated channel: ORA_DISK_1
  5. channel ORA_DISK_1: SID=10 device type=DISK

  6. channel ORA_DISK_1: restoring control file
  7. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
  8. output file name=/u01/oracle/oradata/neal/control01.ctl
  9. output file name=/u01/oracle/fast_recovery_area/neal/control02.ctl
  10. Finished restore at 09-MAR-16
4)如果数据库处于mount阶段时,使用autobackup会报错
  1. RMAN> restore controlfile from autobackup;

  2. Starting restore at 09-MAR-16
  3. released channel: ORA_DISK_1
  4. Starting implicit crosscheck backup at 09-MAR-16
  5. allocated channel: ORA_DISK_1
  6. channel ORA_DISK_1: SID=10 device type=DISK
  7. Crosschecked 50 objects
  8. Finished implicit crosscheck backup at 09-MAR-16

  9. Starting implicit crosscheck copy at 09-MAR-16
  10. using channel ORA_DISK_1
  11. Crosschecked 2 objects
  12. Finished implicit crosscheck copy at 09-MAR-16

  13. searching for all files in the recovery area
  14. cataloging files...
  15. cataloging done

  16. List of Cataloged Files
  17. =======================
  18. File Name: /u01/oracle/fast_recovery_area/NEAL/autobackup/2016_03_09/o1_mf_s_906041673_cfzht9tn_.bkp

  19. using channel ORA_DISK_1

  20. RMAN-00571: ===========================================================
  21. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  22. RMAN-00571: ===========================================================
  23. RMAN-03002: failure of restore command at 03/09/2016 20:12:10
  24. 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. Starting restore at 09-MAR-16
  3. using channel ORA_DISK_1

  4. channel ORA_DISK_1: restoring control file
  5. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
  6. 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. RMAN> recover database;

  8. Starting recover at 09-MAR-16
  9. using channel ORA_DISK_1

  10. starting media recovery

  11. archived log for thread 1 with sequence 1 is already on disk as file /u01/oracle/oradata/neal/redo01.log
  12. archived log file name=/u01/oracle/oradata/neal/redo01.log thread=1 sequence=1
  13. media recovery complete, elapsed time: 00:00:00
  14. Finished recover at 09-MAR-16

  15. SQL> alter database open resetlogs;

  16. 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. using target database control file instead of recovery catalog
  3. RMAN-00571: ===========================================================
  4. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  5. RMAN-00571: ===========================================================
  6. RMAN-03002: failure of sql command at 03/09/2016 20:26:35
  7. RMAN-06403: could not obtain a fully authorized session
  8. ORA-01034: ORACLE not available
  9. ORA-27101: shared memory realm does not exist
  10. 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. using target database control file instead of recovery catalog
  3. sql statement: alter database datafile 12 offline
5)还原restore数据文件
  1. RMAN> restore datafile 12;

  2. Starting restore at 09-MAR-16
  3. using channel ORA_DISK_1

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

  2. Starting recover at 09-MAR-16
  3. using channel ORA_DISK_1

  4. starting media recovery
  5. media recovery complete, elapsed time: 00:00:00

  6. Finished recover at 09-MAR-16
7)将数据文件online
  1. RMAN> sql 'alter database datafile 12 online';

  2. sql statement: alter database datafile 12 online
8)打开数据库成功
SQL> alter database open;

9)发现恢复成功
  1. [oracle@cancer neal]$ ll

  2. -rw-r----- 1 oracle oinstall 52436992 Mar 9 20:28 sales_2009_2.dbf
  3. -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. Tablespace created.
2)删除表空间对应的数据文件

[oracle@cancer neal]$ rm test01.d

3)将表空间offline掉,报错
  1. RMAN> sql 'alter tablespace test offline';

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

  2. sql statement: alter tablespace test offline immediate
5)这里报错是因为test是关键字,加上''并大写可以解决
  1. RMAN> restore tablespace test;

  2. RMAN-00571: ===========================================================
  3. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  4. RMAN-00571: ===========================================================
  5. RMAN-00558: error encountered while parsing input commands
  6. RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"
  7. RMAN-01007: at line 1 column 20 file: standard input

  8. RMAN> restore tablespace 'TEST';

  9. Starting restore at 09-MAR-16
  10. allocated channel: ORA_DISK_1
  11. channel ORA_DISK_1: SID=255 device type=DISK

  12. channel ORA_DISK_1: starting datafile backup set restore
  13. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  14. channel ORA_DISK_1: restoring datafile 00016 to /u01/oracle/oradata/neal/test01.dbf
  15. channel ORA_DISK_1: reading from backup piece /home/oracle/backup/neal_lv0_e4r02vfk_1_1_20160309
  16. channel ORA_DISK_1: piece handle=/home/oracle/backup/neal_lv0_e4r02vfk_1_1_20160309 tag=NEAL_LV0
  17. channel ORA_DISK_1: restored backup piece 1
  18. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
  19. Finished restore at 09-MAR-16

  20. RMAN> recover tablespace 'TEST';

  21. Starting recover at 09-MAR-16
  22. using channel ORA_DISK_1

  23. starting media recovery
  24. media recovery complete, elapsed time: 00:00:00

  25. Finished recover at 09-MAR-16
6)将表空间重新online
  1. RMAN> sql 'alter tablespace test online';

  2. sql statement: alter tablespace test online

五、三种不完全恢复方式

  1. 1)基于时间点

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

  8. 2)基于SCN

  9. startup mount;
  10. restore database until scn 10000;
  11. recover database until scn 10000;
  12. alter database open resetlogs;

  13. 3)基于日志序列

  14. startup mount;
  15. restore database until sequence 100 thread 1;
  16. recover database until sequence 100 thread 1;
  17. alter database open resetlogs;

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

请登录后发表评论 登录
全部评论
擅长PLS/QL开发,SQL调优和改写,数据库设计

注册时间:2014-08-18

  • 博文量
    161
  • 访问量
    1086027