一、基于时间点的不完全恢复
步骤：
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
二、基于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
三、基于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'; 将该未命名的数据文件创建出来并重新命名为新表空间的名字，最后再进行恢复。
实验代码：略