Log File Management

1) view log files

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) Add a redologgroup with only one member by default.

2.1 you can add logfile 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO04.LOG 'size 100 m without using the brackets sys @ CISCOSYS> alter database add logfile 'd; database altered.2.2 use parentheses sys @ CISCOSYS> alter database add logfile ('d: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO05.LOG ') size 100 m; database altered.2.3 add multiple 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 100 m; Database altered.2.3 Add 1 group, and specify the group number sys @ CISCOSYS> alter database add logfile group 9 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO09.LOG 'size 100 m; database altered.2.4 adding member to an existing group cannot specify the 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 add membersys @ CISCOSYS> alter database add logfile member 'd in an existing group: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO05-b.LOG 'to 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO05.LOG'; Database altered.

3. delete a group

3.1 delete 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 1 NO CURRENT2 0 1 YES UNUSED3 0 1 YES UNUSED4 0 1 YES UNUSED5 0 2 YES UNUSED6 0 2 YES UNUSED3.2 delete unused group membersys @ CISCOSYS> alter database drop logfile member 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO06-B.LOG '; Database altered. execute alter system switch logfile multiple times; test @ CISCOSYS> select group #, sequence #, members, archived, status from v $ log; GROUP # SEQUENCE # members arc status ---------- --- ---------------- 1 7 1 NO CURRENT2 2 2 1 YES ACTIVE3 3 3 1 YES ACTIVE4 4 1 YES ACTIVE5 5 5 2 YES ACTIVE6 6 1 YES ACTIVE6 rows selected. delete 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 2 1 YES INACTIVE3 3 3 1 YES INACTIVE4 4 1 YES INACTIVE5 5 5 2 YES INACTIVE6 6 1 YES INACTIVE6 rows selected. because the active log group cannot be deleted. so first place it in inactive3.3 if you accidentally delete the ACTIVE log 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;

This article is from the "simple" blog, please be sure to keep this source http://dba10g.blog.51cto.com/764602/1302850