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)