归档模式下用户管理的恢复测试

一、脱机情况下非系统数据文件恢复

非系统数据文件介质失效，在mount状态执行recover恢复。
1、创建测试表用于最后检验数据是否恢复

sqlplus admin/admin123
create table drop_dbf(id number) tablespace tbs;
insert into drop_dbf values(1);
commit;

SQL> select * from drop_dbf;

        ID
----------
         1
         
alter system switch logfile;
/
/
/

2、重命名tbs表空间的数据文件模拟介质失效

SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS';

   FILE_ID FILE_NAME
---------- -------------------------------------------
         6 /u01/app/oracle/oradata/orcl/tbs.dbf


SQL> !mv /u01/app/oracle/oradata/orcl/tbs.dbf /u01/app/oracle/oradata/orcl/tbs.old

3、关闭数据库

SQL> shutdown immediate;
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

4、使用备份的数据文件复制到原路径

[oracle@gs orcl]$ mv tbs.old tbs.dbf 

5、查看控制文件、数据文件SCN

SQL> startup mount;

控制文件scn：

SQL> select checkpoint_change# from v$datafile where file#=6;

CHECKPOINT_CHANGE#
------------------
           3063443

数据文件头部scn：

SQL> select file#,fuzzy,checkpoint_change# from v$datafile_header where file#=8;

     FILE# FUZZY     CHECKPOINT_CHANGE#
---------- --------- ------------------
         8 YES                  3063443

比较两者scn大小

6、通过应用日志恢复数据文件

SQL> recover tablespace tbs;
Media recovery complete.

7、打开数据库验证数据

SQL> alter database open;
SQL> select * from admin.drop_dbf;

        ID
----------
         1

二、联机状态下恢复非系统数据文件

在数据库启动状态，不停机，恢复数据文件。
1、对tbs表空间数据文件重命名（模拟介质失效）

SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS';

   FILE_ID FILE_NAME
---------- ------------------------------------------
         6 /u01/app/oracle/oradata/orcl/tbs.dbf
         
SQL> !mv /u01/app/oracle/oradata/orcl/tbs.dbf /u01/app/oracle/oradata/orcl/tbs.old

2、将tbs表空间数据文件脱机

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/tbs.dbf' offline;

Database altered.

3、从备份处恢复原数据文件

SQL> !mv /u01/app/oracle/oradata/orcl/tbs.old /u01/app/oracle/oradata/orcl/tbs.dbf

4、执行数据文件级的恢复操作

SQL> recover datafile '/u01/app/oracle/oradata/orcl/tbs.dbf';
Media recovery complete.

5、验证表数据

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/tbs.dbf' online;

Database altered.

SQL> select * from admin.drop_dbf;

        ID
----------
         1

三、控制文件介质恢复

3.1 恢复受损的多路复用控制文件

1、查看控制文件目录

SQL> show parameter control_files

NAME                                 TYPE       VALUE
------------------------            ---------- -------------
control_files                        string     /u01/app/oracle/oradata/orcl/control01.ctl, 
/u01/app/oracle/oradata/orcl/control02.ctl

2、删除其中一个控制文件

!rm -f /u01/app/oracle/oradata/orcl/control02.ctl 

3、关闭数据库

SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
已无法正常关闭数据库

强制关闭数据库

SQL> shutdown abort;
ORACLE instance shut down.

4、恢复容冗余控制文件

SQL> !cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl

5、打开数据库

SQL> startup

3.2 所有控制文件均丢失恢复

1、模拟所有控制文件丢失

[oracle@gs orcl]$ mv control01.ctl control01.old 
[oracle@gs orcl]$ mv control02.ctl control02.old

2、强制关闭数据库

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup;
ORA-00205: error in identifying control file, check alert log for more info
此时已无法正常打开数据库

3、使用备份恢复控制文件

[oracle@gs orcl]$ mv control01.old control01.ctl 
[oracle@gs orcl]$ mv control02.old control02.ctl 

4、启动到mount模式

SQL> startup mount;

5、此时用using backup controlfile子句执行恢复操作

SQL> recover database using backup controlfile;
ORA-00279: change 3089429 generated at 08/10/2018 14:20:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_17_983724050.dbf
ORA-00280: change 3089429 for thread 1 is in sequence #17


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

使用auto
auto
ORA-00308: cannot open archived log '/u01/arch/1_17_983724050.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/u01/arch/1_17_983724050.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
上述信息提示正常，序号17的日志还未归档，可能还在redo日志

查看日志组状态

SQL> select a.sequence#,a.status,a.first_change#,b.member from v$log a,v$logfile b where a.group#=b.group# order by a.sequence#;

 SEQUENCE#  STATUS   FIRST_CHANGE#   MEMBER
--------------------------------------------------
15 INACTIVE 3063440   /u01/app/oracle/oradata/orcl/redo03.log

16 INACTIVE 3063443   /u01/app/oracle/oradata/orcl/redo01.log

17 CURRENT 3083515    /u01/app/oracle/oradata/orcl/redo02.log

重新执行恢复
SQL> recover database using backup controlfile;
ORA-00279: change 3089429 generated at 08/10/2018 14:20:13 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_17_983724050.dbf
ORA-00280: change 3089429 for thread 1 is in sequence #17


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

输入当前日志文件
/u01/app/oracle/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
恢复完毕

6、打开数据库重置日志序列

SQL> alter database open resetlogs;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1