Multiplexing ONLINE REDO to FRA

Mutliplexing  REDO LOGFILE within FRA

The following statements can create online redologs in the Flash Recovery Area:
  - CREATE DATABASE
  - ALTER DATABASE ADD LOGFILE
  - ALTER DATABASE ADD STANDBY LOGFILE
  - ALTER DATABASE OPEN RESETLOGS
 
 If the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST parameters are specified, 
 and if the DB_CREATE_ONLINE_LOG_DEST_n parameter is not specified, an online 
 redolog file member is created in both DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST 
 up to MAXLOGMEMBERS. The file in DB_CREATE_FILE_DEST is the first member. 

SQL>  show parameter db_reco
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string     +FRA2
db_recovery_file_dest_size         big integer 40G
SQL> show parameter db_creat 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest             string     +DATA
db_create_online_log_dest_1         string
db_create_online_log_dest_2         string
db_create_online_log_dest_3         string
db_create_online_log_dest_4         string
db_create_online_log_dest_5         string
SQL>  ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1  size 50m;
Database altered.

SQL> select l.thread#, group#, sequence#, l.status,    member,type,  IS_RECOVERY_DEST_FILE is_rdf from v$logfile inner join v$log l
          using (group#)   order by  l.thread#, group#;
   THREAD#     GROUP#  SEQUENCE# STATUS       MEMBER                                         TYPE    IS_RDF
---------- ---------- ---------- ---------------- ------------------------------------------ ------- ------
     1        1           0 UNUSED       +FRA2/grac4/onlinelog/group_1.285.852650241         ONLINE  YES
     1        1           0 UNUSED       +DATA/grac4/onlinelog/group_1.274.852650227         ONLINE  NO
     1       11          27 INACTIVE      +DATA/grac4/onlinelog/group_11.271.852485683       ONLINE  NO
     1       12          26 INACTIVE      +DATA/grac4/onlinelog/group_12.272.852485689       ONLINE  NO
--> IS_RECOVERY_DEST_FILE is set  to YES

but following command won't work : 
SQL>  ALTER DATABASE ADD LOGFILE GROUP  2  ( '+FRA2', '+DATA') SIZE 50 m;
SQL> select l.thread#, group#, sequence#, l.status,    member,type,  IS_RECOVERY_DEST_FILE is_rdf from v$logfile inner join v$log l
          using (group#)   order by  l.thread#, group#;

   THREAD#     GROUP#  SEQUENCE# STATUS       MEMBER                         TYPE    IS_RDF
---------- ---------- ---------- ---------------- -------------------------------------------------- ------- ------
     1        1           0 UNUSED       +FRA2/grac4/onlinelog/group_1.285.852650241         ONLINE  YES
     1        1           0 UNUSED       +DATA/grac4/onlinelog/group_1.274.852650227         ONLINE  NO
     1        2           0 UNUSED       +DATA/grac4/onlinelog/group_2.273.852650445         ONLINE  NO
     1        2           0 UNUSED       +FRA2/grac4/onlinelog/group_2.298.852650439         ONLINE  NO
--> IS_RECOVERY_DEST_FILE is set  to NO

Dropping a REDO log

 
Current status 
   THREAD#     GROUP#  SEQUENCE# STATUS       MEMBER                         TYPE    IS_RDF
---------- ---------- ---------- ---------------- -------------------------------------------------- ------- ------
     1        1          25 INACTIVE      +FRA2/grac4/onlinelog/group_1.278.852626513         ONLINE  NO
     1        1          25 INACTIVE      +DATA/grac4/onlinelog/group_1.273.852581633         ONLINE  NO
     1        2          28 CURRENT      +DATA/grac4/onlinelog/group_2.274.852628309         ONLINE  NO
     1        2          28 CURRENT      +FRA2/grac4/onlinelog/group_2.285.852628307         ONLINE  NO
     1       11          27 INACTIVE      +DATA/grac4/onlinelog/group_11.271.852485683         ONLINE  NO
     1       12          26 INACTIVE      +DATA/grac4/onlinelog/group_12.272.852485689         ONLINE  NO
..
SQL>  ALTER DATABASE DROP LOGFILE  GROUP 1;
Database altered.

SQL>  ALTER DATABASE DROP LOGFILE  GROUP 2;
 ALTER DATABASE DROP LOGFILE  GROUP 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance grac41 (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '+FRA2/grac4/onlinelog/group_2.285.852628307'
ORA-00312: online log 2 thread 1: '+DATA/grac4/onlinelog/group_2.274.852628309'
--> Group 2 is in CURRENT status 

SQL> alter system switch logfile;
System altered.
SQL>  ALTER DATABASE DROP LOGFILE  GROUP 2;
 ALTER DATABASE DROP LOGFILE  GROUP 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance grac41 (thread 1)
ORA-00312: online log 2 thread 1: '+FRA2/grac4/onlinelog/group_2.285.852628307'
ORA-00312: online log 2 thread 1: '+DATA/grac4/onlinelog/group_2.274.852628309'

SQL>  alter system  checkpoint global;
System altered.

SQL> ALTER DATABASE DROP LOGFILE  GROUP 2;
Database altered.

 

Reference

  • Configuring file creation in Flash recovery area and order of Precedence (Doc ID 305810.1)

Leave a Reply

Your email address will not be published. Required fields are marked *