备份与恢复（2）--- 不完全恢复实验

一、基于时间点的不完全恢复

　　步骤：

　　　　1. 创建一个测试表空间

　　　　2. 备份数据库（冷备）

　　　　3.创建一张测试表并绑定到该表空间，插入数据并提交

　　　　4. 提前获取时间点（实际工作中可以通过日志挖掘）

　　　　5. 以purge的方式删除测试表

　　　　6. 切换日志，执行完全检查点

　　　　7. 删除该表空间的数据文件（模拟介质损坏）

　　　　8. 进行基于时间点不完全恢复

　　实验代码：

  1 1. 创建表空间
  2 SQL> create tablespace hehe datafile '/u01/app/oracle/oradata/OCP12C/hehe01.dbf' size 5m;
  3 
  4 Tablespace created.
  5 2. 备份数据库
  6 [oracle@oracle12c ~]$ mkdir /u01/app/oracle/backup/cdb
  7 [oracle@oracle12c ~]$ mkdir /u01/app/oracle/backup/erp
  8 [oracle@oracle12c ~]$ cd /u01/app/oracle/backup/cdb
  9 [oracle@oracle12c cdb]$ cp /u01/app/oracle/oradata/OCP12C/*.dbf .[oracle@oracle12c erp]$ cp /u01/app/oracle/oradata/OCP12C/ERP/*.dbf .
 10 3. 创建测试表，绑定新创建的表空间，并插入数据提交
 11 SQL> create table heheda(id number) tablespace hehe;
 12 
 13 Table created.
 14 
 15 SQL> insert into heheda values(1);
 16 
 17 1 row created.
 18 
 19 SQL> insert into heheda values(2);
 20 
 21 1 row created.
 22 
 23 SQL> commit;
 24 
 25 Commit complete.
 26 4. 获取时间点
 27 SQL> select sysdate from dual;
 28 
 29 SYSDATE
 30 -------------------
 31 2021-01-17 13:21:10
 32 5. 删除表
 33 SQL> drop table heheda;
 34 
 35 Table dropped.
 36 
 37 SQL> purge recyclebin;
 38 
 39 Recyclebin purged.
 40 6. 切换日志，执行完全检查点
 41 SQL> alter system switch logfile;
 42 
 43 System altered.
 44 
 45 SQL> alter system checkpoint;
 46 
 47 System altered.
 48 7. 删除表空间数据文件
 49 SQL> !rm -rf /u01/app/oracle/oradata/OCP12C/hehe01.dbf;
 50 8.执行不完全恢复
 51 SQL> !cp /u01/app/oracle/backup/cdb/*.dbf /u01/app/oracle/oradata/OCP12C/
 52 SQL> startup force
 53 ORACLE instance started.
 54 
 55 Total System Global Area 1660944384 bytes
 56 Fixed Size            8793448 bytes
 57 Variable Size          939524760 bytes
 58 Database Buffers      704643072 bytes
 59 Redo Buffers            7983104 bytes
 60 Database mounted.
 61 ORA-01113: file 1 needs media recovery
 62 ORA-01110: data file 1: '/u01/app/oracle/oradata/OCP12C/system01.dbf'
 63 SQL> recover database until time '2021-01-17 13:21:10';
 64 ORA-00279: change 4237780 generated at 12/14/2020 12:08:55 needed for thread 1
 65 ORA-00289: suggestion : /u01/app/oracle/arch/1_70_1056413938.dbf
 66 ORA-00280: change 4237780 for thread 1 is in sequence #70
 67 
 68 
 69 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 70 /u01/app/oracle/arch/1_70_1056413938.dbf
 71 ORA-00279: change 4308810 generated at 01/17/2021 12:50:02 needed for thread 1
 72 ORA-00289: suggestion : /u01/app/oracle/arch/1_71_1056413938.dbf
 73 ORA-00280: change 4308810 for thread 1 is in sequence #71
 74 
 75 
 76 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 77 /u01/app/oracle/arch/1_71_1056413938.dbf
 78 Log applied.
 79 Media recovery complete.
 80 SQL> exit
 81 Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 82 [oracle@oracle12c ~]$ sqlplus / as sysdba
 83 
 84 SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 17 13:29:29 2021
 85 
 86 Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 87 
 88 Connected to an idle instance.
 89 
 90 SQL> startup
 91 ORACLE instance started.
 92 
 93 Total System Global Area 1660944384 bytes
 94 Fixed Size            8793448 bytes
 95 Variable Size          939524760 bytes
 96 Database Buffers      704643072 bytes
 97 Redo Buffers            7983104 bytes
 98 Database mounted.
 99 Database opened.
100 SQL> select * from heheda;
101 
102     ID
103 ----------
104      1
105      2

View Code

二、基于BACKUPCONTROLFILE的不完全恢复（控制文件包含新创建表空间）

　　步骤：

　　　　1. 备份数据文件

　　　　2. 创建新的表空间，并创建测试表绑定该表空间，插入数据

　　　　3. 备份控制文件

　　　　4. 删除新创建的表空间数据文件

　　　　5. 执行完全检查点（正常情况下会报错，隐式断开）

　　　　6. 进行不完全恢复

　　　　　　1. 将备份的数据文件还原

　　　　　　2. 备份的控制文件还原

　　　　　　3. SQL> recover database using backup controlfile

　　实验代码：

  1 /***** 创建新的表空间并创建测试表绑定到该表空间，插入数据*****/
  2 SQL> create tablespace memeda datafile '/u01/app/oracle/oradata/OCP12C/memeda01.dbf' size 5m;
  3 SQL> create table memeda(id number) tablespace memeda;
  4 
  5 Table created.
  6 
  7 SQL> insert into memeda values(1);
  8 
  9 1 row created.
 10 
 11 SQL> insert into memeda values(2);
 12 
 13 1 row created.
 14 
 15 SQL> commit;
 16 
 17 Commit complete.
 18 /***** 备份控制文件 *****/
 19 SQL> alter database backup controlfile to '/u01/app/oracle/backup/cdb/cont.bak';
 20 
 21 Database altered.
 22 /***** 切换日志 *****/
 23 SQL> alter system switch logfile;
 24 
 25 System altered.
 26 /***** 删除新创建的表空间数据文件 *****/
 27 SQL> !rm -rf /u01/app/oracle/oradata/OCP12C/memeda01.dbf;
 28 /***** 执行完全检查点*****/
 29 SQL> alter system checkpoint;
 30 alter system checkpoint
 31 *
 32 ERROR at line 1:
 33 ORA-03113: end-of-file on communication channel
 34 Process ID: 13495
 35 Session ID: 1 Serial number: 38548
 36 
 37 
 38 SQL> exit
 39 /***** 还原备份文件 *****/
 40 [oracle@oracle12c ~]$ cp /u01/app/oracle/backup/cdb/*.dbf /u01/app/oracle/oradata/OCP12C/
 41 [oracle@oracle12c ~]$ cp /u01/app/oracle/backup/cdb/cont.bak /u01/app/oracle/oradata/OCP12C/control01.ctl
 42 [oracle@oracle12c ~]$ cp /u01/app/oracle/backup/cdb/cont.bak /u01/app/oracle/oradata/OCP12C/control02.ctl
 43 [oracle@oracle12c ~]$ cp /u01/app/oracle/backup/cdb/cont.bak /u01/app/oracle/oradata/OCP12C/control03.ctl
 44 SQL> startup
 45 ORACLE instance started.
 46 
 47 Total System Global Area 1660944384 bytes
 48 Fixed Size            8793448 bytes
 49 Variable Size          939524760 bytes
 50 Database Buffers      704643072 bytes
 51 Redo Buffers            7983104 bytes
 52 Database mounted.
 53 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 54 /***** 开始恢复 *****/ /***** 由于备份文件中没有新的表空间数据文件，但是备份的控制文件中记录了创建新表空间的动作，所以此处报错*****/
 55 SQL> recover database using backup controlfile;
 56 ORA-00283: recovery session canceled due to errors
 57 ORA-01110: data file 17: '/u01/app/oracle/oradata/OCP12C/memeda01.dbf'
 58 ORA-01157: cannot identify/lock data file 17 - see DBWR trace file
 59 ORA-01110: data file 17: '/u01/app/oracle/oradata/OCP12C/memeda01.dbf'
 60 
 61 /***** 将新表空间数据文件创建出来 *****/
 62 SQL> alter database create datafile '/u01/app/oracle/oradata/OCP12C/memeda01.dbf';
 63 
 64 Database altered.
 65 /***** 再次进行恢复 *****/
 66 SQL> recover database using backup controlfile;
 67 ORA-00279: change 4726030 generated at 01/17/2021 15:06:12 needed for thread 1
 68 ORA-00289: suggestion : /u01/app/oracle/arch/1_3_1062077271.dbf
 69 ORA-00280: change 4726030 for thread 1 is in sequence #3
 70 
 71 
 72 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 73 auto
 74 ORA-00279: change 4827481 generated at 01/17/2021 15:21:00 needed for thread 1
 75 ORA-00289: suggestion : /u01/app/oracle/arch/1_4_1062077271.dbf
 76 ORA-00280: change 4827481 for thread 1 is in sequence #4
 77 ORA-00278: log file '/u01/app/oracle/arch/1_3_1062077271.dbf' no longer needed for this recovery
 78 
 79 
 80 ORA-00279: change 4829618 generated at 01/17/2021 15:34:45 needed for thread 1
 81 ORA-00289: suggestion : /u01/app/oracle/arch/1_5_1062077271.dbf
 82 ORA-00280: change 4829618 for thread 1 is in sequence #5
 83 ORA-00278: log file '/u01/app/oracle/arch/1_4_1062077271.dbf' no longer needed for this recovery
 84 
 85 /***** 此处的报错是因为5号归档日志不存在，有一部分日志存在于在线日志文件中 *****/
 86 ORA-00308: cannot open archived log '/u01/app/oracle/arch/1_5_1062077271.dbf'
 87 ORA-27037: unable to obtain file status
 88 Linux-x86_64 Error: 2: No such file or directory
 89 Additional information: 7
 90 SQL> select group#,sequence#, status from v$log;
 91 
 92     GROUP#  SEQUENCE# STATUS
 93 ---------- ---------- --------------------------------
 94      1        4 CURRENT
 95      3        3 INACTIVE
 96      2        2 INACTIVE
 97 SQL> recover database using backup controlfile;
 98 ORA-00279: change 4829618 generated at 01/17/2021 15:34:45 needed for thread 1
 99 ORA-00289: suggestion : /u01/app/oracle/arch/1_5_1062077271.dbf
100 ORA-00280: change 4829618 for thread 1 is in sequence #5
101 
102 
103 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
104 /u01/app/oracle/oradata/OCP12C/redo02b.log  --- 在线日志文件
105 Log applied.
106 Media recovery complete.
107 SQL> alter database open resetlogs;
108 
109 Database altered.
110 
111 SQL> select * from memeda;
112 
113     ID
114 ----------
115      1
116      2

View Code

三、基于BACKUPCONTROLFILE的不完全恢复（控制文件不包含新创建表空间）

　　步骤：

　　与第二个实验基本一致，只是在恢复时，会将日志文件中记录的动作写入到控制文件，此时会出现 ORA-01244: unnamed datafile(s) added to control file by media recovery 报错，通过命令SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00012' as '/u01/app/oracle/oradata/PROD1/newtest02.dbf'; 将该未命名的数据文件创建出来并重新命名为新表空间的名字，最后再进行恢复。

　　实验代码：略

相关阅读:
关于DISCUZ!NT发布的问题整理！
javascript 文字滚动显示[zhuan ]
Bot Framework Emulator应用与调试
 Bot Framework的简单实现
 命名空间在扩展方法中的妙用
 Error:Execution failed for task ':app:transformClassesWithDexForDebug"
C#中WebApi接口传参不再困惑：传参详解
 贝塞尔曲线
 从枚举值获取对应的文本描述
 常用正则表达式收集
原文地址：https://www.cnblogs.com/eniniemand/p/14289257.html