ORACLE之不完全恢复：基于时间的恢复

ORACLE之不完全恢复：基于时间的恢复

• 使用备份控制文件，基于时间的恢复。

一、制造场景：创建数据

记录当前信息，插入一行数据（5），再观察时间和SCN号，再插入一行数据（6），再观察时间和SCN号，每次执行后都记录这些信息。commit

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
    2241126

SQL> select sysdate from dual;

SYSDATE
-------------------
2021-02-15 09:54:53

SQL> select * from hr.t1;

        ID
----------
         1
         2
         3
         4

SQL> insert into hr.t1 values(5);

1 row created.

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
    2241161

SQL> select sysdate from dual;

SYSDATE
-------------------
2021-02-15 09:56:27

SQL> commit;

Commit complete.

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
    2241189

SQL> select sysdate from dual;

SYSDATE
-------------------
2021-02-15 09:57:28

SQL> insert into hr.t1 values(6);

1 row created.

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
    2241199

SQL> select sysdate from dual;

SYSDATE
-------------------
2021-02-15 09:57:45

SQL> commit;

Commit complete.

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
    2241204

SQL> select sysdate from dual;

SYSDATE
-------------------
2021-02-15 09:57:52

SQL>

二、执行恢复：恢复到5的数据，6之前。

从上面记录的数据，我们确定SCN号为：2241189（插入5，然后commit的SCN号），此时的时间为：2021-02-15 09:57:28

1. shutdown abort

2. 将数据文件全部干掉
   

3. 然后从我们的备份中拷贝回来：
   

4. 启动数据库到mount状态，并执行恢复

   恢复命令：
   recover database unsing backup controlfile until time ‘2021-02-15 09:57:28’;

   SQL> startup mount;
   ORACLE instance started.
   
   Total System Global Area  838860800 bytes
   Fixed Size                  8626240 bytes
   Variable Size             348131264 bytes
   Database Buffers          473956352 bytes
   Redo Buffers                8146944 bytes
   Database mounted.
   SQL> recover database unsing backup controlfile until time '2021-02-15 09:57:28';
   ORA-00905: missing keyword
   
   
   SQL> recover database unsing backup controlfile until time '2021-02-15 09:57:28';
   ORA-00905: missing keyword
   
   
   SQL> recover database using backup controlfile until time '2021-02-15 09:57:28';
   ORA-00279: change 2239494 generated at 02/14/2021 04:40:36 needed for thread 1
   ORA-00289: suggestion : /u01/app/oracle/archive2/arch_orcl_1_3_1064464371.dbf
   ORA-00280: change 2239494 for thread 1 is in sequence #3
   
   
   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
   /u01/app/oracle/archive2/arch_orcl_1_3_1064464371.dbf
   ORA-00279: change 2240089 generated at 02/14/2021 04:55:35 needed for thread 1
   ORA-00289: suggestion : /u01/app/oracle/archive2/arch_orcl_1_4_1064464371.dbf
   ORA-00280: change 2240089 for thread 1 is in sequence #4
   ORA-00278: log file '/u01/app/oracle/archive2/arch_orcl_1_3_1064464371.dbf' no
   longer needed for this recovery
   
   
   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
   /u01/app/oracle/archive2/arch_orcl_1_4_1064464371.dbf
   Log applied.
   Media recovery complete.
   SQL>
   
   

5. 恢复完成打开数据库，查看，已经恢复完成

   SQL> alter database open resetlogs;
   
   Database altered.
   
   SQL> select * from hr.t1;
   
           ID
   ----------
            1
            2
            3
            4
            5
   
   SQL>