归档模式下-丢失关键数据文件

保是归档模式
检查数据状态
SQL> conn / as sysdBA
Connected.
SQL> select current_Scn from v$database;


CURRENT_SCN
-----------
    2756129


SQL> @/home/oracle/q_log


GROUP# SEQUENCE# FILENAME                                 FILESTA GROUPSTAT  ARC
------ --------- ---------------------------------------- ------- ---------- ---
     1         4 /u01/app/oracle/oradata/orcl/redo01.log          INACTIVE   YES
     2         5 /u01/app/oracle/oradata/orcl/redo02.log          CURRENT    NO
     3         3 /u01/app/oracle/oradata/orcl/redo03.log          INACTIVE   YES


SQL> select name,checkpoint_change# from v$datafile;


NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf                     2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf                     2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf                    2754635
/u01/app/oracle/oradata/orcl/users01.dbf                      2755118
/u01/app/oracle/oradata/orcl/example01.dbf                    2754635


SQL> select name,checkpoint_change# from v$datafile_header;


NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf                     2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf                     2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf                    2754635
/u01/app/oracle/oradata/orcl/users01.dbf                      2755118
/u01/app/oracle/oradata/orcl/example01.dbf                    2754635




2.模拟故障，发现报错
SQL> !rm -rf /u01/app/oracle/oradata/orcl/undotbs01.dbf


ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3




3.执行恢复


停止数据并重启到MOUNT状态
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  548216832 bytes
Fixed Size                  1337804 bytes
Variable Size             377488948 bytes
Database Buffers          163577856 bytes
Redo Buffers                5812224 bytes
Database mounted.


使用备份的文件，把丢失的UNDO文件复制回来
[oracle@oel orcl]$ cp /u03/hot_bak/undotbs01.dbf /u01/app/oracle/oradata/orcl/




检查一下控制文件及数据文件头中，检查点的信息，发现undotbs01.dbf这个文件的文件头中记录的检查信息比其它文件旧
需要恢复
SQL> select name,checkpoint_change# from v$datafile;


NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf                     2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf                     2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf                    2754635
/u01/app/oracle/oradata/orcl/users01.dbf                      2755118
/u01/app/oracle/oradata/orcl/example01.dbf                    2754635


SQL> select name,checkpoint_change# from v$datafile_header;


NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf                     2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf                     2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf                    2754456
/u01/app/oracle/oradata/orcl/users01.dbf                      2755118
/u01/app/oracle/oradata/orcl/example01.dbf                    2754635




单独对该文件进行恢复
SQL> recover datafile 3;
ORA-00279: change 2754456 generated at 05/20/2016 09:40:33 needed for thread 1
ORA-00289: suggestion : /u02/arch/1_1_912326240.dbf
ORA-00280: change 2754456 for thread 1 is in sequence #1




Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2754614 generated at 05/20/2016 09:43:58 needed for thread 1
ORA-00289: suggestion : /u02/arch/1_2_912326240.dbf
ORA-00280: change 2754614 for thread 1 is in sequence #2




Log applied.
Media recovery complete.




打开数据库
SQL> alter database open;


Database altered.




再次检查数据库状态
SQL> select name,checkpoint_change# from v$datafile;


NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf                     2776233
/u01/app/oracle/oradata/orcl/sysaux01.dbf                     2776233
/u01/app/oracle/oradata/orcl/undotbs01.dbf                    2776233
/u01/app/oracle/oradata/orcl/users01.dbf                      2776233
/u01/app/oracle/oradata/orcl/example01.dbf                    2776233


SQL> select name,checkpoint_change# from v$datafile_header;


NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf                     2776233
/u01/app/oracle/oradata/orcl/sysaux01.dbf                     2776233
/u01/app/oracle/oradata/orcl/undotbs01.dbf                    2776233
/u01/app/oracle/oradata/orcl/users01.dbf                      2776233
/u01/app/oracle/oradata/orcl/example01.dbf                    2776233