Chapter 4 redo log
1. basic concepts
1. Redo Thread
In OPS (Oracle Parallel Server), each instance has its own redo log file.
Reduction threads and examples are one or one.
2. SCN (System Change Number)
The SCN is the unique identification number for identifying the change of the database, and its value is in order.
3. Redo entry
4. Log serial number
The log serial number is the use identification number of the redo log. When log switching, the log sequence will be automatically incremented and the information is written to the control file.
When the ResetLogs operation is performed, the log sequence number is automatically reset; when the database is in the ArchiveLog mode, the system will use the log serial number as part of the archived log name; when the database is restored, the system is quoting the archive by log sequence number. Logs and redo logs.
2. Naturalization
1. Diversified heavy log
Diversified redo logs, mirror log members, by placing different log members of the same log group to different disks, avoiding disk corruption causes the instance to terminate.
The number of members of the different log groups should be maintained.
2. Reasonable setting redo log size
The log size must be reasonably set, avoiding log switching frequently increases the number of checkpoints and reducing system performance. (20 ~ 30min switching is appropriate, too big, may result in too long instance recovery time)
The same day group member size must be the same.
3. Select the appropriate log group
Select the minimum number of log groups that do not hinder the LGWR process.
When the warning file and the LGWR trace file appear:
You should add the log group when Checkpoint Not Complete or Redo Log Group NOT ARCHIVed.
3. Log switching
When the log group is full, the Oracle Server will automatically perform log switching; it can also be enforced by ALTER System Switch logfile.
4. checking point
When launching Oracle Server, the background process SMON will always check the consistency of the control file, data file, and redo log files (ie, the SCN value of three files is the same).
a. When the data file, the control file, the current SCN value of the redo log file is exactly the same, and the system will open the data file and the redo log.
b. If the current SCN value of the control file and the data file is completely consistent, it is less than the current SCN value of the redo log, and instance recovery is required (for example, ShutDown Abort; suddenly power off, etc.)
c. If the current SCN value of the control file and the data file does not match, it indicates that the data file or control file is damaged, and the media is required to recover the damaged physical file.
5. Increase heavy log
1. Increase log group
When the warning file has CheckPoint Not Complete, you should add a log group. The purpose of increasing the log group is to ensure that the DBWR process and the ARCH process do not hinder the work of the LGWR process, thereby increasing system performance.
Alter Database Add logfile
('E: /oracle/oradata/lgtest/newredo01.log', 'f: /oracle/oradata/lgtest/newredo01.log') size 30m;
2. Add a log member
Increase the log members, the diversified heavy log, avoiding the system that the system cannot run normally after a member of a log group is damaged.
ALTER DATABASE Add logfile Member
'F: /oracle/oradata/lgtest/newredo02.log' 'to group 2;
6. Change redo log position or name
1. Copy redo logs to target position Copy E: /oracle/oradata/lgtest/newredo01.log f: / newredo01.log
2. Change the redo log pointer recorded by the control file
Alter Database Rename File 'E: / ORACLE/Ordata/lgtest/newredo01.log'
To 'f: / newredo01.log';
But you can't change the log group members currently being used.
7. Delete redo log
1. Delete log members
ALTER DATABASE DROP Logfile Member 'f: /oracle/oradata/lgtest/newredo01.log'
Can't delete the only member of the log group;
When the database is in ArchiveLog mode, make sure that the log member is in group already archived;
Can't delete log members of the current log group
2. Delete log group
Alter Database Drop Logfile Group 3;
3. Clear redo log
Alter Database Clear UnarchiveD logfile group 2;
8. Related scripts
1. Get heavy thread information
SELECT THREAD #, Groups, Current_Group #, Sequence # from v $ thread
2. Get log group information or get SCN information for log groups
SELECT * FROM V $ log
3. Get heavy log file name
SELECT * FROM V $ logfile
4. Get the checkpoint information recorded in the control file
Select Checkpoint_change #, Name from V $ DataFile
5. Get the checkpoint information recorded in the data file
Select Checkpoint_change #, Name from V $ DataFile_Header
Chapter 5 Archive Log
The archive log is a backup of the redo log, and the purpose of using the archive log is to achieve media recovery.
1. Log operation mode
1. NOARCHIVELOG (non-archive mode)
Do not save redo logs.
Physical backups cannot be performed in Open states; to perform full database backup regularly; only databases can be restored to the last full backup point.
2. ArchiveLog
When log switching, the ARCH process copies the contents of the redo log to the archive log.
New transaction changes cannot be covered by older transactions before archiving redo logs.
Physical backups can be performed in the OPEN state.
You can restore the database to the status of failure.
It can be synchronized with the primary database with the backup database.
2. Open archive
1. Change log mode of operation
Shutdown immediate;
Startup mount;
Alter Database ArchiveLog;
ALTER DATABASE OPEN
After modifying the log operation mode, you must re-back up the database.
2. Archive reform log
1. Manual archive
Alter System Archive Log ALL;
2. Automatic archive
Setting initialization parameters: log_archive_start = true
Shutdown immediate;
Startup pfile =.
3. Check log operating mode
Archive log list;
3. Specify archive position
1. Use log_archive_dest
1. Set an archive directory
LOG_ARCHIVE_DEST log_archive_duplex_dest
2. Setting Archive Log Format 2. Use log_archive_dest_n
1. Specify archive position
LOG_ARCHIVE_DEST_1 = 'location = f: / arc1'
Log_archive_dest_3 = 'service = standby'
2. Set other initialization parameters
Log_archive_Dest_State_1 = Enable
LOG_ARCHIVE_DEST_STATE_3 = Defer
4. Related scripts
1. Display log operation mode
SELECT log_mode from v $ database
2. Display archive log information
Select Name, Sequence #, First_Change # from v $ archived_log
3. Display archived log location
Select Destination from V $ Arvhive_Dest
4. Display log history information
SELECT * from V $ loghist;
