 |
 |
***************************************************
* Description: Procedure for Redo Log Multiplexing
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 14:56 PM EST, 01/12/2017
***************************************************
<1> Query current redo log group/member status:
|
|__ SQL> SELECT MEMBER,GROUP# FROM V$LOGFILE;
MEMBER GROUP#
---------------------------------------- ----------
/u03/redo/redo_13.rdo 10
/u03/redo/redo_43.rdo 40
/u03/redo/redo_23.rdo 20
/u04/redo/redo_11.rdo 10
/u04/redo/redo_12.rdo 10
/u04/redo/redo_21.rdo 20
/u04/redo/redo_22.rdo 20
/u04/redo/redo_41.rdo 40
/u04/redo/redo_42.rdo 40
<2> SQL for adding 4 groups redo log with 2 members within each:
|
|__ SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/oralog/redo1/redo_11.rdo', '/oralog/redo2/redo_12.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/oralog/redo1/redo_21.rdo', '/oralog/redo2/redo_22.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/oralog/redo1/redo_31.rdo', '/oralog/redo2/redo_32.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/oralog/redo1/redo_41.rdo', '/oralog/redo2/redo_42.rdo') SIZE 500M;
<3> Switch redo log file, and drop the group in "INACTIVE" status:
|
|__ SQL> ALTER SYSTEM SWITCH LOGFILE;
|
|__ SQL> ALTER SYSTEM CHECKPOINT;
|
|__ SQL> SELECT GROUP#,STATUS FROM V$LOG;
GROUP# STATUS
---------- ------------
1 INACTIVE ..... Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
2 ACTIVE ..... Log is active but not the current log. It is needed for crash recovery, and may be in use for block recovery.
Simply speaking, part of the information within this redo log has not been written into archive log yet.
3 CURRENT ..... Current redo log. This implies that the redo log is active. The redo log could be open or closed.
4 UNUSED ..... Online redo log has never been written to. This is the state of a redo log that was just added, or after a RESETLOGS,
when it is not the current redo log.
<4> Drop the redo log group:
|
|__ SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
SQL> ALTER DATABASE DROP LOGFILE GROUP 20;
SQL> ALTER DATABASE DROP LOGFILE GROUP 40;
<5> Reference:
|
|__ o. https://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo005.htm#ADMIN11324
Your Comments