oracle日志维护

 1.adding online redo log file groups

   没添加日志组之前，redo日志组状态

    SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

---------- ------- ------- -------------------------------------------------- ---

         1 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO

         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO

         2 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO

 

  下面使用命令创建一个redo日志组

  SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log','/u01/app/oracle/oradata/orcl/redo04b.log') size 50m; 

Database altered.

   如果系统已经存在日志，则可以使用reuse;

  SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log','/u01/app/oracle/oradata/orcl/redo04b.log') size 50m reuse;

Database altered  

 

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

---------- ------- ------- -------------------------------------------------- ---

         1 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO

         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO

         2 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO

         4         ONLINE  /u01/app/oracle/oradata/orcl/redo04.log            NO

         4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log           NO

 

2.adding onlie redo log file members

  给日志组添加成员，添加日志组成员时，不用指定大小，oracle会自动指定大小

  SQL>  alter database add logfile member '/u01/app/oracle/oradata/orcl/redo04c.log' to group 4;

        Database altered.

  如果日志文件存在，则可以重用

  SQL> alter database add logfile member '/u01/app/oracle/oradata/orcl/redo04c.log' reuse to group 4;

        Database altered.

  添加完之后，查询日志状态

   SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

---------- ------- ------- -------------------------------------------------- ---

         1 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO

         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO

         2 STALE   ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO

         4         ONLINE  /u01/app/oracle/oradata/orcl/redo04.log            NO

         4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log           NO

 

3.dropping online redo log file groups

  使用下面命令将整个日志组删除

  SQL> alter database drop logfile group 4;

    Database altered.

  删除之后，查询日志状态

   SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

---------- ------- ------- -------------------------------------------------- ---

         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO

         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO

         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO

 

4.dropping online redo log file members

  使用下面命令删除日志组中一个成员，当日志组只有一个成员时，不能删除日志组成员

  SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo04c.log';

       Database altered.

  下面查询日志组状态

  SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

---------- ------- ------- -------------------------------------------------- ---

         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO

         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO

         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO

         4         ONLINE  /u01/app/oracle/oradata/orcl/redo04.log            NO

         4         ONLINE  /u01/app/oracle/oradata/orcl/redo04b.log           NO

 

5.relocating or renaming online redo log files

  如果要给日志组文件换一个位置，则需要依照下面步骤执行

  （1）shutdown database

   (2) copy the online redo log files to the new location;

   (3) start database to mount;

   (4)execute the command

 SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo04.log' to '/u02/app/oracle/oradata/orcl/redo04.log';

   (5)open database

 

6.Clearing Online Redo Log Files

  An online redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize the online redo log file without shutting down the database.

This command can overcome two situations where dropping online redo log files is not possible:

If there are only two log groups

The corrupt online redo log file belongs to the current group 

If the corrupt online redo log file has not been archived, use the UNARCHIVED keyword in the command to clear the corrupted online redo log files and avoids archiving them. The cleared online redo log files are available for use even though they were not archived.

If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. Oracle writes a message in the alert log describing the backups from which you cannot recover.

 

SQL> alter database clear logfile group 4;

SQL> alter database clear unarchived logfile group 4;