Before the server process obtains the redo allocation latch for redo log buffer allocation, You need to sniff the redo log file to check whether there is sufficient space. If the space is insufficient, the sp will send a request for the switch log file and wait until the log file switch completion event is complete.
After a log request is sent, CKPT performs an incremental checkpoint event, while LGWR initiates a log change.
The specific process is as follows:
1) The LGWR process will find an available redo log file through the two-way linked list in the control file as the new current redo log.
The algorithm is as follows:
The log file is inactive and has been archived.
Prioritize unused Log File groups
2) flush the redo entries not written in the redo log buffer to the current online redo log file, the SCN of the last redo entries is recorded in the redo log header as the high SCN of the current log file, and the current online redo log file is closed.
3) conduct the second control file transaction and mark the closed redo log as ACTIVE (this is an incremental checkpoint event, the reason why it is identified as active, this is because the dirty buffer it protects may not be written to the data file. If it has all been written to the disk, it can be identified as inactive.) mark the new redo log as CURRENT, if the database is in archive mode, you need to record the old log group to the Control File Archive list record and notify ARCn to archive the log file.
4) LGWR opens all the members of the new log group and records the current log sequence # And the SCN (low scn) of the First redo block in the log file header)
5) LGWR modifies the flag in the SGA to generate new redo log information.
To sum up, log switching is an expensive operation. During the change period, all transactions to the database will be blocked. However, increasing the size of the redo log file is irrelevant to the amount of data lost. Reason:
1) redo entries is written in sequence, and one or more are written, which is the same for recovery.
2) Storage faults are certainly affected by all redo log files.
In ARCHIVE mode, direct path Loading records REDO. In non-archive log mode, direct path loading does not record REDO, But Oracle generates REDO because of system changes that require maintenance of segments, partitions, and tablespaces.
The redo log file is dumped to dump.
- 09:29:36 sys @ ORCL (^ ω ^) conn hr/hr
- Connected.
- 09:34:57 hr @ ORCL (^ ω ^)Create TableTestAs Select*FromDba_objectsWhere1 = 2;
- The table has been created.
- 09:35:41 hr @ ORCL (^ ω ^)SelectDbms_flashback.get_system_change_numberFromDual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 2804230
- 09:36:06 hr @ ORCL (^ ω ^)Select Group#, StatusFromV $ log;
- GROUP# STATUS
- ------------------------------------------
- 1CURRENT
- 2 INACTIVE
- 3 INACTIVE
- 09:36:26 hr @ ORCL (^ ω ^) col memberForA72
- 09:36:36 hr @ ORCL (^ ω ^)Select Group#, MemberFromV $ logfile;
- GROUP# MEMBER
- ----------------------------------------------------------------------------------
- 3 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ONLINELOG \ O1_MF_3_7TQZWZOY_.LOG
- 3 D: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORCL \ ONLINELOG \ O1_MF_3_7TQZ
- X11D_.LOG
- 2 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ONLINELOG \ O1_MF_2_7TQZWXO2_.LOG
- 2 D: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORCL \ ONLINELOG \ O1_MF_2_7TQZ
- WYPH_.LOG
- 1 D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ONLINELOG \ O1_MF_1_7TQZWVDD_.LOG
- 1 D: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ ORCL \ ONLINELOG \ O1_MF_1_7TQZ
- WWJ8_.LOG
- You have selected 6 rows.
- 09:37:24 hr @ ORCL (^ ω ^)SelectDbms_flashback.get_system_change_numberFromDual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 2804353
- 09:37:59 hr @ ORCL (^ ω ^)Insert/* + Append */IntoTestSelect*FromDba_objects;
- You have created 50453 rows.
- 09:39:15 hr @ ORCL (^ ω ^)Commit;
- Submitted.
- 09:39:21 hr @ ORCL (^ ω ^)SelectDbms_flashback.get_system_change_numberFromDual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 2804521
- 09:39:29 hr @ ORCL (^ ω ^)AlterSystem dump logfile'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ONLINELOG \ O1_MF_1_7TQZWVDD_.LOG'
- 09:40:25 2 scnMin2804353 scnMax2804521;
- The system has been changed.
- 09:40:57 hr @ ORCL (^ ω ^) conn/AsSysdba
- Connected.
- 09:41:09 sys @ ORCL (^ ω ^) archive log list
- Database Log mode archiving Mode
- Enable automatic archiving
- Archiving end point USE_DB_RECOVERY_FILE_DEST
- Oldest online log sequence 3
- Next archive log sequence 5
- Current Log sequence 5
- 09:41:19 sys @ ORCL (^ ω ^)AlterSystem dump logfile'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ONLINELOG \ O1_MF_1_7TQZWVDD_.LOG'
- 09:43:23 2 scnMin2804230 scnMax2804521;
- The system has been changed.
- 09:44:03 sys @ ORCL (^ ω ^) conn hr/hr
- Connected.
- 09:45:58 hr @ ORCL (^ ω ^)SelectDbms_flashback.get_system_change_numberFromDual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 2804801
- 09:46:07 hr @ ORCL (^ ω ^)Insert/* + APPEND */IntoTextSelect*FromDba_objects;
- Insert/* + APPEND */IntoTextSelect*FromDba_objects
- *
- Row 3 has an error:
- ORA-00942: Table or view does not exist
- 09:47:42 hr @ ORCL (^ ω ^)Insert/* + APPEND */IntoTestSelect*FromDba_objects;
- You have created 50453 rows.
- 09:47:53 hr @ ORCL (^ ω ^)Commit;
- Submitted.
- 09:47:58 hr @ ORCL (^ ω ^)SelectDbms_flashback.get_system_change_numberFromDual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 2805155
- 09:48:10 hr @ ORCL (^ ω ^)Select Group#, StatusFromV $ log;
- GROUP# STATUS
- ------------------------------------------
- 1CURRENT
- 2 INACTIVE
- 3 INACTIVE
- 09:48:50 hr @ ORCL (^ ω ^)AlterSystem dump logfile'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ONLINELOG \ O1_MF_1_7TQZWVDD_.LOG'
- 09:49:32 2 scnMin2804801 scnMax2805155;
