34
2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf ONLINE 552134
3 /u01/app/oracle/oradata/ora10g/sysaux01.dbfONLINE 552134
4 /u01/app/oracle/oradata/ora10g/users01.dbfONLINE 552134
5 /u01/app/oracle/oradata/ora10g/example01.dbf ONLINE 552134
6 /u01/app/oracle/oradata/ora10g/zlm01.dbfONLINE 552134
7 /u01/app/oracle/oradata/ora10g/zlm02.dbfONLINE 552134
8 /u01/app/oracle/oradata/ora10g/zlm_test01.dbf ONLINE 552134
执行了检查点以后，脏数据刷到磁盘数据文件，数据库全部数据文件的检查点SCN都会保持一致
--当前日志情况
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 7 52428800 1 YES INACTIVE 551038 05-9? -14
2 1 8 52428800 1NO CURRENT 551097 05-9? -14
3 1 6 52428800 1 YES INACTIVE 550653 05-9? -14
--把8号数据文件OFFLINE
SQL> alter database datafile 8 offline; --只有归档模式可以用
alter database datafile 8 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database datafile 8 offline drop; --非归档要用offline drop，注意，不是真正的删除物理数据文件
Database altered.
SQL> alter database datafile 8 online; --offline drop以后，需要recover以后才能online
alter database datafile 8 online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/ora10g/zlm_test01.dbf'
SQL> recover datafile 8
Media recovery complete.
SQL> alter database datafile 8 online;
Database altered.
由于此时在线日志并没有被覆盖，可以进行recover操作，然后对8号数据文件进行online
二、在线日志被覆盖的场景
--把8号数据文件重新OFFLINE
SQL> alter database datafile 8 offline drop;
Database altered.
--查看在线日志当前状态
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 7 52428800 1 YES INACTIVE 551038 05-9? -14
2 1 8 52428800 1NO CURRENT 551097 05-9? -14
3 1 6 52428800 1 YES INACTIVE 550653 05-9? -14
--切换3次日志，把当前日志内容覆盖
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 7 52428800 1 YES INACTIVE 551038 05-9? -14
2 1 8 52428800 1 NO ACTIVE 551097 05-9? -14
3 1 9 52428800 1NO CURRENT 552891 05-9? -14
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 10 52428800 1NO CURRENT 552899 05-9? -14
2 1 8 52428800 1 NO ACTIVE 551097 05-9? -14
3 1 9 52428800 1 NO ACTIVE 552891 05-9? -14
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 10 52428800 1 NO ACTIVE 552899 05-9? -14
2 1 11 52428800 1NO CURRENT 552911 05-9? -14
3 1 9 52428800 1 NO ACTIVE 552891 05-9? -14
?
当前日志的检查点SCN从551097变为552911，也就是说，第3次切换日志以后，current日志被覆盖了
--对8号数据文件online
SQL> alter database datafile 8 online;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/or