記錄檔的管理

1)查看記錄檔

sys@CISCOSYS> col member format a60sys@CISCOSYS> select group#,member,type from v$logfile;GROUP# MEMBER                                                       TYPE---------- ------------------------------------------------------------ -------3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG             ONLINE2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG             ONLINE1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG             ONLINE

2）添加一個redologgroup，預設只有一個member

2.1 可以不使用括弧sys@CISCOSYS> alter database add logfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG' size 100m;Database altered.2.2 使用括弧sys@CISCOSYS> alter database add logfile ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO05.LOG') size 100m;Database altered.2.3 一次性添加多個memberCISCOSYS> alter database add logfile ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO06-a.LOG','D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO06-b.LOG') size 100m;Database altered.2.3 新增1個group ，並指定group號sys@CISCOSYS> alter database  add logfile group 9 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO09.LOG' size 100m;Database altered.2.4 給一個現有的group 添加member不能指定size)sys@CISCOSYS> alter database  add logfile member 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO09-b.LOG' to group 9;Database altered.2.5 在一個現有group ，添加membersys@CISCOSYS> alter database  add logfile member 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO05-b.LOG' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO05.LOG';Database altered.

3刪除group

3.1 刪除UNUSED的groupsys@CISCOSYS> alter database drop logfile group 9;Database altered.sys@CISCOSYS> select group#,sequence#,members,archived,status from  v$log;GROUP#  SEQUENCE#    MEMBERS ARC STATUS---------- ---------- ---------- --- ----------------1          1          1 NO  CURRENT2          0          1 YES UNUSED3          0          1 YES UNUSED4          0          1 YES UNUSED5          0          2 YES UNUSED6          0          2 YES UNUSED3.2 刪除unused group membersys@CISCOSYS> alter database drop logfile member 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO06-B.LOG';Database altered.多次執行alter system switch logfile;test@CISCOSYS> select group#,sequence#,members,archived,status from  v$log;GROUP#  SEQUENCE#    MEMBERS ARC STATUS---------- ---------- ---------- --- ----------------1          7          1 NO  CURRENT2          2          1 YES ACTIVE3          3          1 YES ACTIVE4          4          1 YES ACTIVE5          5          2 YES ACTIVE6          6          1 YES ACTIVE6 rows selected.刪除ACTIVE的grouptest@CISCOSYS> alter database drop logfile group 6;alter database drop logfile group 6*ERROR at line 1:ORA-01624: log 6 needed for crash recovery of instance orcl (thread 1)ORA-00312: online log 6 thread 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO06-A.LOG'test@CISCOSYS> alter system checkpoint;System altered.test@CISCOSYS> select group#,sequence#,members,archived,status from  v$log;GROUP#  SEQUENCE#    MEMBERS ARC STATUS---------- ---------- ---------- --- ----------------1          7          1 NO  CURRENT2          2          1 YES INACTIVE3          3          1 YES INACTIVE4          4          1 YES INACTIVE5          5          2 YES INACTIVE6          6          1 YES INACTIVE6 rows selected.由於active的 log group 不能刪除.所以 要先將其置於inactive3.3 如果不小心刪除了 ACTIVE的日誌sys@CISCOSYS> alter database open2  ;alter database open*ERROR at line 1:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'sys@CISCOSYS> alter database clear  unarchived logfile group 1;Database altered.sys@CISCOSYS> alter database open2  ;

selectcheckpoint_change#fromv$database;
selectname,checkpoint_change#fromv$datafile_header;
selectname,checkpoint_change#fromv$datafile;
selectname,last_change#fromv$datafile;

本文出自 “簡單” 部落格，請務必保留此出處http://dba10g.blog.51cto.com/764602/1302850