ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 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.

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

  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;

  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

  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;

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

  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;

  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';

  3. Starting restore at 09-MAR-16
  4. allocated channel: ORA_DISK_1
  5. channel ORA_DISK_1: SID=237 device type=DISK

  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;

  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';

  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

  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;

  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

  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

  16. searching for all files in the recovery area
  17. cataloging files...
  18. cataloging done

  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

  24. using channel ORA_DISK_1

  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';

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

  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'

  8. RMAN> recover database;

  10. Starting recover at 09-MAR-16
  11. using channel ORA_DISK_1

  13. starting media recovery

  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

  20. SQL> alter database open resetlogs;

  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';

  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';

  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;

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

  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;

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

  6. starting media recovery
  7. media recovery complete, elapsed time: 00:00:00

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

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

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

  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;

  3. Tablespace created.
2）删除表空间对应的数据文件

[oracle@cancer neal]$ rm test01.d

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

  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';

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

  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

  10. RMAN> restore tablespace 'TEST';

  12. Starting restore at 09-MAR-16
  13. allocated channel: ORA_DISK_1
  14. channel ORA_DISK_1: SID=255 device type=DISK

  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

  25. RMAN> recover tablespace 'TEST';

  27. Starting recover at 09-MAR-16
  28. using channel ORA_DISK_1

  30. starting media recovery
  31. media recovery complete, elapsed time: 00:00:00

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

  3. sql statement: alter tablespace test online

五、三种不完全恢复方式
  1. 1）基于时间点

  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. }

  10. 2）基于SCN

  12. startup mount;
  13. restore database until scn 10000;
  14. recover database until scn 10000;
  15. alter database open resetlogs;

  17. 3）基于日志序列

  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博客 ” ，链接：https://blog.itpub.net/29812844/viewspace-2063673/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Centos6.5安装Oracle11.2.0.4 RAC(完整版)
下一篇： RMAN重新注册备份集
请登录后发表评论 登录
全部评论
梓沐
专家姓名：孙伟
擅长PLS/QL开发，SQL调优和改写，数据库设计

注册时间：2014-08-18

  • 博文量
    161
  • 访问量
    1204437

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593