数据文件offline，未归档的非活动在线日志或当前在线日志损坏，在线修复(实验系列)

实验步骤：

01)            SQL> ALTER SYSTEM ARCHIVELOG STOP;

02)    SQL> ARCHIVE LOG LIST
数据库日志模式            存档模式
自动存档             禁用
存档终点            e:\oracle\oradata\newdb\archive
最早的概要日志序列     1
下一个存档日志序列   3
当前日志序列           3

03)    SQL> DROP TABLE T1;
表已丢弃。
15:13:10 SQL> create table t1 (scn int) tablespace users;
表已创建。
15:56:25 SQL> insert into t1
15:56:36   2    select dbms_flashback.get_system_change_number
15:56:36   3    from (select rownum from dba_objects where rownum <= 1000a,
15:56:36   4           (select rownum from dba_objects where rownum <= 1000) b;
已创建1000000行。

04)    15:58:08 SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

  GROUP#            BYTES ARC    FIRST_CHANGE#        SEQUENCE# STATUS                 ---------------- ---------------- --- ---------------- ---------------- ----------------                                  1         10485760 NO           1155101                5 CURRENT                                          2         10485760 NO           1152594                3 INACTIVE                                          3         10485760 NO           1154972                4 ACTIVE                                       已选择3行。

15:58:08 SQL> select min(scn) ,max(scn) from t1;

  MIN(SCN)         MAX(SCN)       
             ---------------- ----------------
                 1154965         1155128 

16:01:55 SQL> commit;
提交完成。

05)    SQL> ALTER DATABASE DATAFILE 'F:\ORADATA\USERS01.DBF' OFFLINE;（模拟数据文件损坏）

06)    16:02:16 SQL> select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
SUBSTR(NAME,1,40)                     REC FUZ CHECKPOINT_CHANGE#                          ------------------------------------- --- --- ------------------                                                           D:\ORADATA\NEWDB\SYSTEM01.DBF         NO  YES            1154972                        D:\ORADATA\NEWDB\UNDOTBS01.DBF        NO  YES            1154972                       C:\ORADATA\NEWDB\CWMLITE01.DBF        NO  YES            1154972                       D:\ORADATA\NEWDB\DRSYS01.DBF          NO  YES            1154972                           D:\ORADATA\NEWDB\EXAMPLE01.DBF        NO  YES            1154972                       D:\ORADATA\NEWDB\INDX01.DBF           NO  YES            1154972                            D:\ORADATA\NEWDB\ODM01.DBF            NO  YES            1154972                           D:\ORADATA\NEWDB\TOOLS01.DBF          NO  YES            1154972                           D:\ORADATA\NEWDB\USERS01.DBF                             0                                                D:\ORADATA\NEWDB\XDB01.DBF            NO  YES            1154972                             D:\ORADATA\NEWDB\USERS02.DBF          NO  YES            1154972                           
已选择11行。

07)    16:02:16 SQL> select GROUP#,substr(member,1,60) from v$logfile;

   GROUP# SUBSTR(MEMBER,1,60)                                                                                  ---------------- ----------------------------------------------
               3 D:\REDO03.LOG                                                                                                            2 D:\REDO02.LOG                                                                                                           1 D:\REDO01.LOG  

08)    16:02:50  SQL> select min(scn) ,max(scn) from t1;

        MAX(SCN)         MIN(SCN)
---------------- ----------------
         1154965         1155128 

09)    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT
*
ERROR 位于第 1 行:
ORA-16038: 日志 2 序列号 3 无法归档
ORA-00313: 无法打开日志组  (线程 ) 的成员
ORA-00312: 联机日志 2 线程 1: 'D:\REDO02.LOG'

10)    SQL> alter database clear logfile 'D:\REDO02.LOG';
alter database clear logfile 'D:\REDO02.LOG'
*
ERROR 位于第 1 行:
ORA-00350: 日志 2 (线程 1) 中需要归档
ORA-00312: 联机日志 2 线程 1: 'D:\REDO02.LOG'

11)    REM -- 未归档的日志文件不允许归档
SQL> alter database clear unarchived logfile 'D:\REDO02.LOG' UNRECOVERABLE DATAFILE;

12)    16:24:16 SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

         GROUP#            BYTES ARC    FIRST_CHANGE#        SEQUENCE# STATUS         ---------------- ---------------- --- ---------------- ---------------- ----------------                                    1         10485760 NO           1155101                5 ACTIVE                                              2         10485760 NO           1159843                6 CURRENT                                         3         10485760 NO           1154972                4 INACTIVE                                        已选择3行。

13)    恢复完成