Online Log file Management

log file classification: redo log files

Archive log files

Warning log file

Trace log files

Redo_log_file file function:

1) Maintain data consistency

2) record the change operation in the database

Redo_log_friles in groups, a group can have more than one member, sequentially loop write, a database to start at least 2 log groups, each group has at least one member, Redolog work in a multi-work manner.

Log Switching: The process of switching from one group to the next.

1) Automatic switching

2) Manual switch

Planning Redo_log_file log:

Spread to different disks, disk IO is sufficient, and IO reads and writes faster.

Log size issue: To meet the database automatically switch log time interval around 15--20min.

1 ) query log file location:

Sql>select * from V$logfile;

group# STATUS TYPE MEMBER is_

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

1 STALE online/u01/oracle/oradata/rezin/redo01.log NO

2 STALE Online/u01/oracle/oradata/rezin/redo02.log NO

3 Online/u01/oracle/oradata/rezin/redo03.log NO

Sql> select * from V$log;

group# thread# sequence# BYTES members ARC STATUS

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

first_change# First_time

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

1 1 PNs 52428800 1 YES INACTIVE

642652 16-3? -15

2 1 52428800 1 YES INACTIVE

668961 16-3? -15

3 1 52428800 1 NO current

695725 17-3? -15

Log Status: status Common Values

Unused: Indicates that the online redo log filegroup has never been written, which is just adding the online redo log group status.

Current: Represents the online redo log file group that is currently in use.

Active: Indicates that the online redo log filegroup is active, but is not currently in use. Crash recovery requires this state, may be archived, or may not be archived.

Clearing: Represents an empty log that is being rebuilt after the ALTER DATABASE clear logfile is executed, and the status is unused after the log clears.

Manually switch logs:

Sql>alter system switch logfile;

To add a log group file:

Syntax: ALTER DATABASE add logfile [group N1] (' <dir> ', ' <dir> ',....) size n,[group N2] (' < Dir> ', ' <dir> ') size N,.........;

sql> ALTER DATABASE Add logfile Group 4 '/u01/oracle/oradata/orcl/redo04.log ' size 50m;

Database altered.

sql> ALTER DATABASE Add logfile '/u01/oracle/oradata/orcl/redo05.log ' size 50m;

Database altered.

Add a group to specify multiple members:

sql> ALTER DATABASE Add logfile

('/u01/oracle/oradata/orcl/redo07.log ', '/u01/oracle/oradata/orcl/redo08.log ') size 50m;

Database altered.

sql> ALTER DATABASE add logfile Group 7

('/u01/oracle/oradata/orcl/redo09.log ', '/u01/oracle/oradata/orcl/redo10.log ') size 50m;

Database altered.

One statement to add multiple log groups:

sql> ALTER DATABASE Add logfile Group 7 '/u01/oracle/oradata/orcl/redo11.log ' size 50m,group 8 '/u01/oracle/oradata/or Cl/redo12.log ' size 50m;

Database altered.

To add a log group member:

Sql>alter database Add member ' <dir1> ' to Group N, ' <dir1> ' to group N;

sql> ALTER DATABASE Add logfile member '/u01/oracle/oradata/orcl/redo13.log ' to group 8;

Database altered.

To add members to multiple groups:

sql> ALTER DATABASE Add logfile member '/u01/oracle/oradata/rezin/redo014.log ' to group 1, '/u01/oracle/oradata/rezin /redo015.log ' to group 2;

Invalid: Either the log file does not exist or it is a newly added member and the other is a used member.

Delete log group: ALTER DATABASE drop logfile group n1,group n2;

Log status of active, current state cannot be deleted, you need to manually switch the log until it can be deleted.

Sql>alter Database drop logfile Group 9;

Sql>alter database Drop logfile Group 5,group 6;

To remove a member directive: ALTER DATABASE drop logfile member ' <dir1> ', ' <dir2> ';

Sql>alter database Drop logfile member '/u01/oracle/oradata/orcl/redo07.log ';

Sql>alter Database Drop logfile Member

'/u01/oracle/oradata/orcl/redo01.log ', '/u01/oracle/oradata/orcl/redo02.log ';

Add: Delete the switch log.

Log Rename: Equivalent to change path, only current cannot be renamed.

Syntax: #cp <oldfile> <newfile>

Sql>alter database Rename file ' <oldfilename> ' to ' <newfilename> ';

Sql> Ho Cp/u01/oracle/oradata/orcl/redo11.log/u01/oracle/oradata/rezin/redo11.log

Sql> Ho Ls/u01/oracle/oradata/rezin/redo11.log

/u01/oracle/oradata/rezin/redo11.log

sql> ALTER DATABASE rename file '/u01/oracle/oradata/orcl/redo11.log ' to

'/u01/oracle/oradata/rezin/redo11.log ';

Online redo log file Rename: cannot be current , only Oracle in Mount and Open state to use.

Syntax: ALTER DATABASE rename file ' <oldfilename> ' to ' <newoldfile> ';

Status monitoring of the database:

Status: Inactive (outside Loop), Active,current (within loop)

Monitoring: Select ' Ho ls ' | | Member from V$logfile;

' Hols ' | | MEMBER

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

Ho Ls/u01/oracle/oradata/rezin/redo01.log

Ho Ls/u01/oracle/oradata/rezin/redo02.log

Ho Ls/u01/oracle/oradata/rezin/redo03.log

The query is not in:

Sql> Ho Ls/u01/oracle/oradata/rezin/redo01.log

/u01/oracle/oradata/rezin/redo01.log

Viewing disk space is not enough:

[Oracle] #dh-F

Log switching interval: Check whether the interval is between 15-20 minutes.

Sql> Select Group#,to_char (first_time, ' Yyyy-mm-dd hh24:mi:ss ') as First_time from V$log;

group# First_time

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

1 2015-03-17 08:54:07

2 2015-03-17 08:53:10

3 2015-03-17 08:53:22

4 2015-03-17 10:41:49

5 2015-03-17 15:07:24

6 2015-03-17 16:12:48

The log group must have multiple members, not multiple member groups, to be modified into multiple member groups.

Modify the log file size: Delete the existing log group and its physical files, and then re-establish the group and modify it to the appropriate size.

Online redo log exceptions (log inconsistency, loss, corruption) processing:

1) Log file inconsistency: Empty the inconsistent log group, and the Oracle database can be shut down to empty the log file at startup.

Sql> ALTER DATABASE clear logfile Group N; --->inactive can be emptied

Or

Sql> ALTER DATABASE clear unarchived logfile Group N; ---->active can be emptied

2) Missing log file:

Method One: Database consistency is closed, if the log file is lost can be restored by emptying the log file, the same steps.

Method Two: Database non-conforming shutdown

1) Add hidden parameters: The same control_file in the _allow_resetlog_corruption hidden parameters to solve the problem.

2) If the archive mode can be restored via the recover command: means of deception

Sql>recover database using Backup controlfile;

Sql>alter database open resetlogs;

3) Non-archive mode, hidden parameters Ignore database consistency, can play a big role, but does not support the use.

Online Log file Management