Table of Contents
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)