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

1.确保数据库备份有效
确保数据库运行在归档模式


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


[oracle@oel u03]$ ./hot_bak.sh 




2.使用SCOTT创建测试表
[oracle@oel u03]$ sqlplus scott/tiger


SQL> create table test as select * from emp where deptno=30;


Table created.


SQL> select * from test;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30


6 rows selected.




3.检查数据库状态，并推进一下数据库的SCN，切换一些日志，模拟过了很长时间，数据库执行了很多操作
SQL> conn / as sysdba
Connected.
SQL> @/home/oracle/q_log


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


SQL> select current_Scn from v$database;


CURRENT_SCN
-----------
    2754587


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


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


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


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


SQL> alter system switch logfile;


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> @/home/oracle/q_log


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


SQL> @/home/oracle/q_log


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


SQL> select table_name,tablespace_name from dba_tables where table_name='TEST' and owner='SCOTT';


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS


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


FILE_NAME
---------------------------------------------------------------------------------------------
TABLESPACE_NAME                   FILE_ID
------------------------------ ----------
/u01/app/oracle/oradata/orcl/users01.dbf
USERS                                   4




4.模拟故障，删除USERS表空间对应的数据文件，使用SCOTT再次查询发现错误
SQL> !rm -rf /u01/app/oracle/oradata/orcl/users01.dbf


SQL> alter system flush buffer_cache;


System altered.


SQL> alter system flush shared_pool;


System altered.


SQL> conn scott/tiger
Connected.
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3




5.执行恢复
SQL> conn / as sysdba
Connected.
SQL> alter database datafile 4 offline;


Database altered.


SQL> !cp /u03/hot_bak/users01.dbf /u01/app/oracle/oradata/orcl/


检查当前控制文件中的检查点信息，以及各数据文件头的检查点信息
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                      2754635
/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                      2754456
/u01/app/oracle/oradata/orcl/example01.dbf                    2754635


无法对该文件正常的online
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'




针对 该文件单独进行恢复
SQL> recover datafile 4;
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.




再次检查控制文件及数据文件头中记录的检查点信息，发现USERS表空间对应的数据文件的检查点信息已经被更新
不再比其它数据文件旧了
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                      2754848
/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                      2754848
/u01/app/oracle/oradata/orcl/example01.dbf                    2754635


SQL> select current_scn from v$database;


CURRENT_SCN
-----------
    2755093




对该文件执行ONLINE操作
SQL> alter database datafile 4 online;


Database altered.




使用SCOTT进行验证，发现表没有丢，完全恢复
SQL> conn scott/tiger
Connected.
SQL> select * from test;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30


6 rows selected.