.JPG)
So you can consider this post as :-
Backup and Recovery : Concepts Part 1
REDO LOGFILE
-----------------
The Oracle server maintains the redo Oracle log files to minimize the loss of data in the Database in case of an uncontrolled shutdown.
Online redo Oracle log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.
For example, if you change a employee name in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the rollback segment data block, and the transaction table of the rollback segments.
Two views play an important part here namely , v$log and v$logfile.
v$logfile can be used to find the path of the redo log or their type (online/standby(used for standby database))
For more information on standby redo logs :-
http://www.pythian.com/news/581/oracle-standby-redo-logs/
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
--------------------------------------------------------------------------------
3 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo03.log
2 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo02.log
1 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo01.log
v$log can be used to find out if your redo logs are archived or not with archived column . And also to check the status of the the log files . There are different log status , few of the important ones are:-
1. UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
2. CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
3. ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
4. INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
5. INVALIDATED - Archived the current redo log without a log switch.
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 7 1 NO CURRENT
2 5 1 NO INACTIVE
3 6 1 NO INACTIVE
Also to check which archivelog/no archivelog mode , we can use
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
-------------------------------------------------------------------------------------
CHECKPOINT
----------------
A checkpoint performs the following three operations:
1. Every dirty block in the buffer cache is written to the data files. That is, it(DBWR) synchronizes the datablocks in the buffer cache with the datafiles on disk.
2. The latest SCN is written (updated) into the datafile header.
3. The latest SCN is also written to the controlfiles.
Every modification in the database (for example, an update) is given an unique system change number SCN, which is, therefore, an ever increasing integer. The latest SCN is immediately recorded in the controlfile. The modifications are logged in the online redo log by the log writer LGWR, but not necessarily in the datafiles.
v$datafile_header can be used to find out the checkpoint number in the datafiles .
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;
FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 27-AUG-10 829278
2 27-AUG-10 829278
3 27-AUG-10 829278
4 27-AUG-10 829278
5 27-AUG-10 829278
Latest checkpoint number can be found with the help of v$database
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
829278
If the control file is a backup file , checkpoint number in the file can be found with :-
SQL> select CONTROLFILE_CHANGE# from v$database;
CONTROLFILE_CHANGE#
-------------------
830797
-------------------------------------------------------------------------------------
REDO Log and Checkpoint
---------------------------
Now , "alter system switch logfile " forces a redo log switch to happen . A log switch also occurs when one online redo log has been filled and the next online redo log is going to be filled.
A log switch always triggers a checkpoint (that's what is written at maximum places) but if you see this carefully .
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;
FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 27-AUG-10 831582
2 27-AUG-10 831582
3 27-AUG-10 831582
4 27-AUG-10 831582
5 27-AUG-10 831582
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
831582
SQL> SQL> SELECT group#, sequence#, members, archived, status FROM v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO INACTIVE
2 14 1 NO CURRENT
3 12 1 NO INACTIVE
SQL> alter system switch logfile;
System altered.
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO INACTIVE
2 14 1 NO ACTIVE
3 15 1 NO CURRENT
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
831582
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;
FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
1 27-AUG-10 831582
2 27-AUG-10 831582
3 27-AUG-10 831582
4 27-AUG-10 831582
5 27-AUG-10 831582
There is no change in the checkpoint . Checkpointing in case of log switch happens if there are dirty buffers to be written to the datafile or in other words , SCN in the datafile header and in the control file don't match . Here since they were same , no checkpointing occurred even though log was switched.
Also , if we do a checkpointing , log switch doesn't happen but triggering a checkpoint brings the log files in "INACTIVE" state since they are no longer needed in instance recovery (may be needed in the media recovery) ( as it is written on the datafiles).
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO CURRENT
2 11 1 NO INACTIVE
3 12 1 NO INACTIVE
SQL> alter system switch logfile;
System altered.
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO ACTIVE
2 14 1 NO CURRENT
3 12 1 NO INACTIVE
SQL> alter system checkpoint;
System altered.
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 1 NO INACTIVE
2 14 1 NO CURRENT
3 12 1 NO INACTIVE
-------------------------------------------------------------------------------------
