- Define the DB_CREATE_ONLINE_LOG_DEST_n parameter :
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
- A group can be added with no file specification.
ALTER DATABASE ADD LOGFILE;
- Dropping a Group
ALTER DATABASE DROP LOGFILE GROUP 3;
Define the DB_CREATE_ONLINE_LOG_DEST_n parameter:
To create online redo log files to be managed by OMF, the DB_CREATE_ONLILNE_LOG_DEST_n parameter must be defined. The parameter must be set for each multiplexed copy identified by the n value.
In the example above two groups have been created with 2 members each. The names will be generated automatically (such as "ora_1_wo94n2xi.log") and displayed in the alertSID.log. The default size is 100 MB.
Adding Log File Group:
To create a new group of online redo log files, the DBA uses the ALTER DATABASE ADD LOGFILE command. The command has been modified so that the file specification is not necessary.
In the example above, To add a log file with 2 members, One in the location defined by the
DB_CREATE_ONLINE_LOG_DEST_1 and one in
DB _CREATE_ONLINE_LOG_DEST_2.
Unique filenames for the logfile members have generated automatically and displayed in the alertSID.log. the Default Size is 100MB.
Dropping a Group :
The above example drops the log file Group 3, and its operating system files associated with each OMF log file members in Group 3.
Archived Redo Log Files and OMF :
Archived redo log files cannot be OMF.
Obtaining Group and Member Information:
Information about a group and its members can be obtained by querying the following views.
- V$LOG
- V$LOGFILE
The following query returns information about the online redo logfile from the control file :
SQL> SELECT group#, sequence#, bytes, members, status FROM v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
-------- ---------- ---------- ---------- ----------------
1 403637 209715200 2 CURRENT
2 403634 209715200 2 INACTIVE
3 403635 209715200 2 INACTIVE
4 403636 209715200 2 INACTIVE
5 403632 209715200 2 INACTIVE
6 403633 209715200 2 INACTIVE
6 rows selected.
The following items are the most common values for the Status column :
- UNUSED: This status indicates that the online redo log file group has never been written to. This is the state of the online redo log file group that was just added.
- CURRENT: This status indicates the current online redo log file group. This implies that the online redo log file group is active.
- ACTIVE: This status indicates that the online redo log file group is active but it is not the current online redo log file group. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
- CLEARING: This status indicates that the log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE command. After the log is cleared, the status changes the UNUSED.
- CLEARING_CURRENT: This status indicates that the current log file is being cleared of a closed thread. The log can stay in this status id there is some failure in the switch, such as an input/output (I/O) error writing the new log header.
- INACTIVE: This status indicates that the online redo log file group is no longer needed for instance recovery. It may or may not be archived.
V$LOGFILE View :
To obtain the names of all the members of a group, query the V$LOGFILE view.
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
------- ------- ------- ----------------- ---------------
2 ONLINE /u01/oradata/ORCL/log02a.rdo NO
2 ONLINE /u01/oradata/ORCL/log02b.rdo NO
1 ONLINE /u01/oradata/ORCL/log01a.rdo NO
1 ONLINE /u01/oradata/ORCL/log01b.rdo NO
4 rows selected.
The value of the STATUS column could be one of the following.
- INVALID: This status indicates that the file is inaccessible.
- STALE: This status indicates that the contents of the file are incomplete.
- DELETED: This status indicates that the file is no longer used.
- Blank indicates that the file is in use.
Thank You !!
Very good
ReplyDeleteUseful
ReplyDelete