This article comes from the "Net Administrator World" 2002, the fault diagnosis section in order to prevent the fault or loss of the database log file itself, Oracle strongly recommends using the mirrored redo log, if the LGWR trace file or the message table in the Alert file LGWR, It is often necessary to add groups because the checkpoint has not been completed or the group is not archived.
Add online heavy log group
Syntax: ALTER DATABASE [Database] add logfile [group integer] Filespec [, [group integer] filespec] ...] You can specify the member name and location by file instructions. You can select each redo log file group Group parameter value. If this parameter is omitted, the Oracle server will automatically generate its value.
Add a redo log member
Add a new member to the existing log file group to add a new member ALTER DATABASE [Database] add logfile ['filename' [reuse]] ... to {} using the ALTER DATABASE Add Logfile Member command. Group integer | ('filename "[,' filename '] ...)}]
Relocate or rename online redo log files
Syntax: ALTER DATABASE [Database] RENAME File 'FileName' [, 'filename'] ... to 'filename' [, 'filename "] ... Note: This command is only the information that modifies the log file in the control file. There is no real moving log file location, so you need to manually use the location of the operating system command to move the log file.
Delete unwanted log file groups
Syntax: ALTER DATABASE [Database] Drop logfile {group integer | ('filename' [, 'filename'] ...)} [, {group integer | ('filename "[,' filename '] ...)}] ... Note: A routine requires at least two sets of online recruit log files; you cannot delete the active group or the current group; if the database is running in ArchiveLog mode and the log file group is not filed, the group cannot be deleted; Do not delete the operating system file when you do a log group, so you need to delete the log file for manual operating system commands.
Delete log files
When a online redo log member status is invalid, if you want to discard one or more online log members, use the command: ALTER DATABASE [Database] Drop Logfile Member 'FileName' [, 'filename'] ... Note: Can't The last valid member in the delete group cannot delete the current group, so you must force the log file before deleting the member. If the database is running in ArchiveLog mode and the member belongs to the log file group file, it is not possible to delete the member in deleting online The operating system file is not deleted when he reforms a log member, so it is also necessary to manually use the operating system command to complete the actual deletion of files.
Clear online log group
If all members of a online log file group have been destroyed, you can solve the problem by reinitializing these log files. Syntax: ALTER DATABASE [Database] clear [unarchived] logfile {group integer | ('filename "(' filename '] ...)} [{group integer | (' filename" [, 'filename "... )}] ... Note: Whether it online does not archive if the log file is archived, it can be cleared. However, when it does not archive, you must contain keyword unarchived. This will make the backup unavailable if you need this online redo log file. Query V $ log to get information about online redo log file group
The following item is a common value for the Status column in the V $ log view: Unused indicates that the online redo log group has never written, which is the status of the online redo log file that just added. Current indicates that the current online redo log group, which means that the online redo log group is active. Active indicates that the online redo log group is active, but it is not the current online redo log group, and the crash recovery requires that it may be used for block recovery, which may not archive. Clearing indicates that the log is rebuilt to an empty log after the Alter Database Clear logfile command, and the status is changed to unused after the log clearance. Clearing_current indicates that the shutdown thread in the current log file is being cleared. If some fault occurs, if some fault occurs, if the I / O error when writing a new log title, the log can stay in this state. Inactive indicates that routine recovery no longer needs to do log groups online, it may archive may not archive.
Query V $ logfile Get log member information
The following item is the common value of the status column in the V $ logfile view: Invalid indicates that the file is not accessible. Stale indicates that the file content is not complete, such as being added a log file member. DELETED indicates that the file is no longer used. Blank indicates that the file is being used.
Common fault handling
If LGWR is at least accessible to a member, the write to which accessible to the member will be performed as usual, and the LGWR ignores unavailable members within the group. If the group is inactive, that is, the checkpoint has been completed, then discard and add a new online log member to solve the problem, otherwise, if the group is the current active log group, you must first force the log switching. SQL> Select Group #, Sequence #, Bytes, Members, Status 2 from v $ log; group # sequence # bytes Members Status ----------------------- ----------- 1 411 1048576 2 inactive 2 412 1048576 2 inactive 3 413 1048576 2 Inactive 5 414 1048576 2 Currentsql> Select * from v $ logfile; group # status member ------- --------------------------. . . . . . (Other log files). . . . . . . . . 5 INVALID E: /Oracle1/ora81/oradata/test/redo07.log 5 E: /oracle1/ora81/oradata/test/redo08.log8 rows selectededsql> But this time the data can also ignore this corrupted file and normal use. To fix this file, we need to do: SQL> Shutdownsql> Hoste: /> Copy E: /Oracle1/ora81/oradata/test/redo08.log e: /oracle1/ora81/oradata/test/redo07.log rewrite E: / ORACLE1 / ORA81 / ORADATA / TEST / REDO07.LOG? (Yes / No / ALL): YES has copied 1 file. The above steps need to be noted that if the location or file name of the new log file needs to change (eg, the media failure), rename the log file of this change position or name after the Startup Mount, then Open the data again, see "Repositioning or renameting the online redo log file". E: /> EXITSQL> Select * from v $ logfile; group # status member --------------------------------- . . . . . . (Other log files). . . . . . . . . 5 Unknown E: /oracle1/ora81/oradata/test/redo07.log 5 Stale E: /oracle1/ora81/oradata/test/redo08.log8 rows selected we see the status of the log file is unknown; indicating that it is added The status of the log file is connected online. Wait until the database is using the file or manual command: ALTER system switch logfile After enforcing the database, the status of the file is displayed as blank, ie, normal state. Note: After restoring the database, immediately close the data (Normal), and perform a cold backup. The relevant part of the log file corruption and repair will be recorded in the alert.log file.
If the LGWR is unable to access all members of the next group or the log file when the LGWR is not accessible, the instance is turned off. If you are inactive, then discard and add a new log group to resolve the problem; if activity, the database may need to recover from the online redo log file residue. In order to simulate a member damage in the log group, we open the text editor, and destroy the E: / test / redo07.log file, then we see: SQL> ALTER SYSTEM SWITCH LOGFILE; ALTER System Switch logfileora-00313: Unable to open a member SQL> Startup Mountsql> ALTER DATABASE2 DROP logfile group 5; the database has been changed. SQL> ALTER DATABASE OPEN; database has changed. Now everything is normal, completely shut down the data (Normal) and perform a cold backup. The relevant part of the log file corruption and repair will be recorded in the alert.log file. In archiving mode, we need to recover the database with ALTER DATABASE CLOGFILE GROUP: Now there is only one member in Group 4, and not current, we damage this group (f: /oracle1/ora81/oradata/test/redo07.log), Then, after Switch to this corrupted group, the database will hang, or if it will crash, then we may receive the following error: SQL> ALTABASE CLOGFILE 2 'f: / Oracle1 / Ora81 / ORADATA / TEST / Redo07.log '; Alter Database Clear Logfile * Error is located in Chapter 1: ORA-12571: TNS: Packet Writer FailureSQL> Conn Intenral @ TEST Please enter the password: error: ora-01092: Oracle Instance Terminated. Disconnection ForcedSql> At this time, We can quit SQLPLUS and then recover: SQL> EXITE: /> SQLPLUS INTERNAL comes in, then SHUTDOWN ABORT Database, then, then Startup Mount: SQL> Shutdown Aborts QL> Startup Mount You can see that the database does not have Crash. Now we clear group4, first use ALTER DATABASE CLEAR LOGFILE Group 4; (if you don't do it, use the ALTER DATABASE CLEAR UNARCHIVED LOGFILE Group 4;): SQL> ALTABASE CLOGFILE Group 4; Database has changed. SQL> ALTER DATABASE OPEN; database has changed. SQL> Ok, the database has resumed that if all members who are writing the current group, LGWR suddenly cannot access these members, then the database routine is turned off, in which case the database may need to be carried out from the online log file residue Medium recovery. Determine Group4 is CURRENT, now destroying Group4.
