Control Files and redo log files

Document directory

• 1. redo log files
• 2 conventional operations on online logs
• 3 common SQL statements
• 4. Differences between alter system switch logfile and alter system archive log current
• 5 Problems

Technorati label: Oracle, control file, redo log file

Control Files and redo log files

1. redo log files

Data File Types of log files: Online log files (also called online logs) and archived log files (historical backup of online log files)

Log running mode: Archive mode and non-archive mode (automatic archiving or not)

Log running process: Writing files depends on the lgwr background process.

Logs are used in an ordered loop, that is, when a group of log files is filled up, the next group of log files is repeatedly overwritten.

Purpose of redo log files: 1. Record data changes 2. Provide data recovery 3. Maintain database integrity.

1.1 log analysis or mining (logmnr)

1. Set Directory

Modify D:/Oracle/admin/Foxconn/pfile/init. ora

Url_file_dir =''

2. restartup

3. create directory file

Desc dbms_logmnr

Start D:/Oracle/ora92/rdbms/admin/catproc. SQL // you can update some data and find the current log group.

Exec dbms_logmnr_d.build ('foxdict. ora ', 'd:/Oracle/oradata/Foxconn/cdump ');

4. Add/Remove Log File

Exec dbms_logmnr.add_logfile/remove_logfile ('d:/Oracle/oradata/Foxconn/redo02.log ', dbms_logmnr.new );

Exec dbms_logmnr.add_logfile/remove_logfile ('d:/Oracle/oradata/Foxconn/redo03.log ', dbms_logmnr.new );

5. Start logmnr

Exec dbms_logmnr.start_logmnr (dictionaryname = 'd:/Oracle/admin/Fox/cdump/foxdict. ora ');

6. V $ logmnr_context (sqlredo, sqlundo)

2 conventional operations on online logs 2.1 view information about the current log

Sys @ ora11g> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim

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

1 1 10 209715200 1 Yes inactive 461938 09-Mar-09

2 1 11 209715200 1 no current 485885 09-Mar-09

3 1 9 209715200 1 Yes inactive 432636 04-Mar-09

Sys @ ora11g> Sele CT * from V $ logfile;

Group # status type member is _

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

1 online/Oracle/u02/oradata/ora11g/redo01.log No

2 online/Oracle/u02/oradata/ora11g/redo02.log No

3 online/Oracle/u02/oradata/ora11g/redo03.log No

2.2 Add a redo log Group

Sys @ ora11g> alter database add logfile Group 4 ('/Oracle/u02/oradata/ora11g/redo04_01.log', '/Oracle/u02/oradata/ora11g/redo04_02.log ') size 50 m;

Database altered.

Sys @ ora11g> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim

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

1 1 10 209715200 1 Yes inactive 461938 09-Mar-09

2 1 11 209715200 1 no current 485885 09-Mar-09

3 1 9 209715200 1 Yes inactive 432636 04-Mar-09

4 1 0 52428800 2 Yes unused 0

Sys @ ora11g> select * from V $ logfile;

Group # status type member is _

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

1 online/Oracle/u02/oradata/ora11g/redo01.log No

2 online/Oracle/u02/oradata/ora11g/redo02.log No

3 online/Oracle/u02/oradata/ora11g/redo03.log No

4 online/Oracle/u02/oradata/ora11g/redo04_01.log No

4 online/Oracle/u02/oradata/ora11g/redo04_02.log No

2.3 Add a log file

Sys @ ora11g> alter database add logfile Member

'/Oracle/u02/oradata/ora11g/redo01_02.log' to group 1,

'/Oracle/u02/oradata/ora11g/redo02_02.log' to group 2,

'/Oracle/u02/oradata/ora11g/redo03_02.log' to group 3;

Database altered.

Sys @ ora11g> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim

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

1 1 10 209715200 2 Yes inactive 461938 09-Mar-09

2 1 11 209715200 2 no current 485885 09-Mar-09

3 1 9 209715200 2 Yes inactive 432636 04-Mar-09

4 1 0 52428800 2 Yes unused 0

Sys @ ora11g> select * from V $ logfile order by 1;

Group # status type member is _

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

1 online/Oracle/u02/oradata/ora11g/redo01.log No

1 invalid online/Oracle/u02/oradata/ora11g/redo01_02.log No

2 invalid online/Oracle/u02/oradata/ora11g/redo02_02.log No

2 online/Oracle/u02/oradata/ora11g/redo02.log No

3 online/Oracle/u02/oradata/ora11g/redo03.log No

3 invalid online/Oracle/u02/oradata/ora11g/redo03_02.log No

4 online/Oracle/u02/oradata/ora11g/redo04_02.log No

4 online/Oracle/u02/oradata/ora11g/redo04_01.log No

8 rows selected.

2.4 rename a log Member

The new target must exist before the Member is renamed. The Oracle SQL command only points the internal pointer in the control file to the new log file.

2.4.1 shut down the database

Sys @ ora11g> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down.

2.4.2 use operating system commands to rename or move log files

Ora11g @ rhel53/Oracle/u02/oradata/ora11g $ MV redo01.log redo01_01.log

Ora11g @ rhel53/Oracle/u02/oradata/ora11g $ MV redo02.log redo02_01.log

Ora11g @ rhel53/Oracle/u02/oradata/ora11g $ MV redo03.log redo03_01.log

2.4.3 start the database instance to the Mount state and rename the log file members in the control file.

Notconnected @> select * from V $ logfile order by 1, 4;

Group # status type member is _

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

1 online/Oracle/u02/oradata/ora11g/redo01.log No

2 online/Oracle/u02/oradata/ora11g/redo02.log No

3 online/Oracle/u02/oradata/ora11g/redo03.log No

4 online/Oracle/u02/oradata/ora11g/redo04_01.log No

4 online/Oracle/u02/oradata/ora11g/redo04_02.log No

1 online/Oracle/u02/oradata/ora11g/redo01_02.log No

2 online/Oracle/u02/oradata/ora11g/redo02_02.log No

3 online/Oracle/u02/oradata/ora11g/redo03_02.log No

8 rows selected.

Notconnected @> alter database rename File '/Oracle/u02/oradata/ora11g/redo01.log' to '/Oracle/u02/oradata/ora11g/redo01_01.log ';

Database altered.

Notconnected @> alter database rename File '/Oracle/u02/oradata/ora11g/redo02.log' to '/Oracle/u02/oradata/ora11g/redo02_01.log ';

Database altered.

Notconnected @> alter database rename File '/Oracle/u02/oradata/ora11g/redo03.log' to '/Oracle/u02/oradata/ora11g/redo03_01.log ';

Database altered.

2.4.4 Open Database, verification result

Notconnected @> alter database open;

Database altered.

Sys @ ora11g> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim

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

1 1 18 209715200 2 Yes inactive 486960 09-Mar-09

2 1 19 209715200 2 Yes inactive 486964 09-Mar-09

3 1 21 209715200 2 no current 486973 09-Mar-09

4 1 20 52428800 2 Yes inactive 486968 09-Mar-09

Sys @ ora11g> select * from V $ logfile order by 1, 4;

Group # status type member is _

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

1 online/Oracle/u02/oradata/ora11g/redo01_01.log No

1 online/Oracle/u02/oradata/ora11g/redo01_02.log No

2 online/Oracle/u02/oradata/ora11g/redo02_01.log No

2 online/Oracle/u02/oradata/ora11g/redo02_02.log No

3 online/Oracle/u02/oradata/ora11g/redo03_01.log No

3 online/Oracle/u02/oradata/ora11g/redo03_02.log No

4 online/Oracle/u02/oradata/ora11g/redo04_01.log No

4 online/Oracle/u02/oradata/ora11g/redo04_02.log No

8 rows selected.

2.4.5 do not forget to back up the control file

Sys @ ora11g> alter Database Backup controlfile to trace;

Database altered.

2.5 Delete an inactive redo log group member.

Sys @ ora11g> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim

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

1 1 18 209715200 2 Yes inactive 486960 09-Mar-09

2 1 19 209715200 2 Yes inactive 486964 09-Mar-09

3 1 21 209715200 2 no current 486973 09-Mar-09

4 1 20 52428800 2 Yes inactive 486968 09-Mar-09

Sys @ ora11g> alter database drop logfile member '/Oracle/u02/oradata/ora11g/redo04_02.log ';

Database altered.

Sys @ ora11g>! Ls-L/Oracle/u02/oradata/ora11g/redo04_02.log

-RW-r ----- 1 Oracle oinstall 52429312 Mar 9/Oracle/u02/oradata/ora11g/redo04_02.log

Sys @ ora11g>! Rm-F/Oracle/u02/oradata/ora11g/redo04_02.log

Sys @ ora11g> select * from V $ logfile order by 1, 4;

Group # status type member is _

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

1 online/Oracle/u02/oradata/ora11g/redo01_01.log No

1 online/Oracle/u02/oradata/ora11g/redo01_02.log No

2 online/Oracle/u02/oradata/ora11g/redo02_01.log No

2 online/Oracle/u02/oradata/ora11g/redo02_02.log No

3 online/Oracle/u02/oradata/ora11g/redo03_01.log No

3 online/Oracle/u02/oradata/ora11g/redo03_02.log No

4 online/Oracle/u02/oradata/ora11g/redo04_01.log No

7 rows selected.

2.6 delete an inactive redo log group.

Sys @ ora11g> alter database drop logfile group 4;

Database altered.

Sys @ ora11g>! Rm-F/Oracle/u02/oradata/ora11g/redo04_01.log

Sys @ ora11g> select * from V $ logfile;

Group # status type member is _

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

1 online/Oracle/u02/oradata/ora11g/redo01_01.log No

2 online/Oracle/u02/oradata/ora11g/redo02_01.log No

3 online/Oracle/u02/oradata/ora11g/redo03_01.log No

1 online/Oracle/u02/oradata/ora11g/redo01_02.log No

2 online/Oracle/u02/oradata/ora11g/redo02_02.log No

3 online/Oracle/u02/oradata/ora11g/redo03_02.log No

6 rows selected.

2.7 force switch log

Sys @ ora11g> alter system switch logfile;

System altered.

Sys @ ora11g> alter system archive log current;

System altered.

Conclusion 2.8

1). log files are very important. When multiplexing and redo log files, you should save the members of a group on different disks.

2). Remember to back up the latest control file after maintenance of log files!

3). The above test is completed in the 11G environment and is applicable in the 10g environment.

3 common SQL statements 3.1 select * from V $ log

View the log group.

3.2 select * from V $ logfile

View log files.

3.3 archive log list

Query the Current Log File status.

3.4 alter system archive log all

Manually archive all log file groups.

3.5 alter system archive log current

Manually archive the active log file group.

Note1: Performs log switching on all instances in the database.

Note2: archive the current redo log file, whether it is automatically archived or not.

3.6 alter system archive log start

Change to automatic archiving.

3.7 alter system switch logfile

The switch logfile clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. when you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. to use this clause, your instance must have the database open.

Start writing a new log file group. Whether or not the current log file group is full, force log switching.

Note1: The current redo log file may not be archived. (If the file is automatically archived and opened, the existing redo log will be archived. If the file is not automatically archived, the current redo log will not be archived .)

Note2: Performs log switching on the current instance in a single-instance database or RAC.

3.8 alter system checkpoint

Specify checkpoint to explicitly force Oracle to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. you can specify this clause only when your instance has the database open. oracle does not return control to you until the checkpoint is complete.

Forces Oracle to perform a checkpoint once to ensure that all transaction changes committed are written to the disk data file.

3.9 select * from V $ archived_log

View archived log files.

4. Differences between alter system switch logfile and alter system archive log current

Alter system switch logfile performs log switching on the current instance in a single instance database or RAC. (You can also switch logs without archiving)

Alter system archive log current performs log switching on all instances in the database.

Explanation:

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

We all know that in an automatically archived database, the results of these two commands are almost the same. Let's take a look at Oracle's explanation:

Alter system switch logfile;

Switch logfile clause

The switch logfile clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. when you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. to use this clause, your instance must have the database open. alter system archive log current;

Current clause

Specify current to manually archive the current redo log file group of the specified thread, forcing a log switch. if you omit the thread parameter, then oracle archives all redo log file groups from all enabled threads, including logs previous to current logs. you can specify current only when the database is open. alter system archive log current noswitch;

Noswitch

Specify noswitch if you want to manually archive the current redo log file group without forcing a log switch. this setting is used primarily with standby databases to prevent data divergence when the primary database shuts down. divergence implies the possibility of data loss in case of primary database failure. you can use the noswitch clause only when your instance has the database mounted but not open. if the database is open, then this operation closes the database automatically. you must then manually shut down the database before you can reopen it

First, we need to understand two facts:

One is to trigger the arch process during the logfile switch, that is, to notify the archiving process that the log can be archived.

The other is that the ckpt process is triggered when logfile is switched. The ckpt process triggers the dbwr process. The dbwr process writes all the dirty buffer files protected in the previous redo in the buffer cache back to the disk. Then, the ckpt process records SCN to controlfile, and changes the redo log status from active to inactive. From this we can see that the inactive redo log is useless for the instance recovery. However, it may be useful for MEDA recovery, because the inactive redo log may have been archived or not archived. The checkpoint not complete, unable to allocate new redo log file error occurs because lgwr needs to overwrite a log file that has not been completed during the checkpoint process. lgwr will wait for dbwr to complete, this error occurs at the same time. There are several possible reasons for this error. One is that dbwr writing speed is too slow, and the other is that there is too much dirty data in the buffer cache. That is, transactions are too frequent, and the other may be caused by too few logs or too few log groups. Several operations used in Backup recovery:

Alter system checkpoint;

Alter system archive log all;

Alter system archive log current

Alter system switch logfile;

1: Alter system checkpoint;

Specify checkpoint to explicitly force Oracle to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. you can specify this clause only when your instance has the database open. oracle does not return control to you until the checkpoint is complete.

Forces Oracle to perform a checkpoint once to ensure that all transaction changes committed are written to the disk data file.

2: Alter system archive log all;

Manually archive all log file groups

3: Alter system archive log current

Manually archive the active log file group.

4: Alter system switch logfile;

The switch logfile clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. when you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. to use this clause, your instance must have the database open.

Start writing a new log file group. Whether or not the current log file group is full.

5. question 5.1 under what circumstances will Oracle archive?

1. When the automatic archiving setting is enabled, the archive will be performed on a regular basis.

How to schedule?

How to enable automatic archiving?

2. After the backup.

3. Shutdown