--查看数据库状态 SQL> select open_mode from v$database;
OPEN_MODE ---------- READ WRITE
--创建测试表 SQL> create table zlm.test2 as select object_id id,object_name name from dba_objects where rownum<=3;
Table created.
SQL> set lin 130 pages 130
SQL> col name for a15 SQL> select * from zlm.test2;
ID NAME ---------- --------------- 20 ICOL$ 44 I_USER1 28 CON$
--切换日志使online日志归档 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 0 52428800 1 YES UNUSED 0 2 1 0 52428800 1 YES UNUSED 0 3 1 1 52428800 1 NO CURRENT 1000917 19-914
由于之前已经恢复过一次，是用open resetlogs打开数据库的，因此这里sequence为1，
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 2 52428800 1 NO CURRENT 1001252 19-914 2 1 0 52428800 1 YES UNUSED 0 3 1 1 52428800 1 NO ACTIVE 1000917 19-914
注意：使用alter system switch logfile与使用alter system archive log current;是有区别的，前者只是对日志做一个切换，而并非进行归档，可以看到，切换一次归档后，3号日志文件组文件状态为"ACTIVE"，归档状态为"NO"
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 2 52428800 1 YES ACTIVE 1001252 19-914 2 1 3 52428800 1 NO CURRENT 1001255 19-914 3 1 1 52428800 1 YES ACTIVE 1000917 19-914
注意：第2次切换日志后，3号日志组文件才进行了归档，可以看到ARCHIVED列的值为"YES",但STATUS列依然是ACTIVE,可以对比一下用alter system archive log current;来归档日志后，STATUS列的值应该为"INACTIVE"
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 2 52428800 1 YES ACTIVE 1001252 19-914 2 1 3 52428800 1 YES ACTIVE 1001255 19-914 3 1 4 52428800 1 NO CURRENT 1001283 19-914
第3次切换归档后，当前日志组又变成3，注意此时online日志仍然是未归档状态
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 5 52428800 1 NO CURRENT 1001295 19-914 2 1 3 52428800 1 YES ACTIVE 1001255 19-914 3 1 4 52428800 1 YES ACTIVE 1001283 19-914
第4次切换日志，使online日志刷新到归档日志
SQL> exit Disconnected from
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@ora10g ora10g]$ pwd /u01/app/oracle/oradata/ora10g [oracle@ora10g ora10g]$ ll -lrth total 1.4G -rw-r----- 1 oracle oinstall 51M Sep 19 15:04 temp01.dbf -rw-r----- 1 oracle oinstall 51M Sep 19 15:11 redo02.log -rw-r----- 1 oracle oinstall 51M Sep 19 15:11 zlm01.dbf -rw-r----- 1 oracle oinstall 31M Sep 19 15:11 users01.dbf -rw-r----- 1 oracle oinstall 166M Sep 19 15:11 undotbs01.dbf -rw-r----- 1 oracle oinstall 561M Sep 19 15:11 system01.dbf -rw-r----- 1 oracle oinstall 271M Sep 19 15:11 sysaux01.dbf -rw-r----- 1 oracle oinstall 51M Sep 19 15:11 redo03.log -rw-r----- 1 oracle oinstall 101M Sep 19 15:11 example01.dbf -rw-r----- 1 oracle oinstall 51M Sep 19 15:13 redo01.log -rw-r- |