Oralce redo log)

1.Introduction

Oracle introduces redo logs for database restoration.

Oracle-related processes: redo the log write process (lgwr ).

Redo log properties: Online log files, which must be managed when the Oracle server is running.

Related Data Dictionary: V $ log; V $ logfile.

Operator permission: You have sys or system user permissions.

Redo log files:

Data File Types of log files: Online log files (also called online logs) and archived log files (historical backups of online log files ),Each redo log group contains one or more redo log files. Each force must have at least two redo log groups.

Log running mode: Archive mode and non-archive mode (automatic archiving or not)

Log running process: Writing files depends on the lgwr background process.

Logs are used in an ordered loop, that is, when a group of log files is filled up, the next group of log files is repeatedly overwritten.

Purpose of redo log files: 1. Record data changes 2. Provide data recovery 3. Maintain database integrity.

2.What isOracleArchive mode?

The Oracle database has a redo log of the connected machine. This log records changes made to the database, such as insertion, deletion, and data update. All these operations are recorded in the online redo log. Generally, a database must have at least two online redo log groups. When an online redo log group is full, log switching occurs. In this case, online redo log group 2 becomes the currently used log and distributes these files on different physical disks, when online redo log group 2 is full, log switching occurs again. Write online redo log group 1, and then repeat it.

Online redo logs are currently used. If the database is in non-archive mode, online logs are discarded during switchover. in archive mode, when a log switch occurs, the switched logs are archived. For example, 1. When 1 is full, log switching occurs and online redo log 2 is started. At this time, the content of online redo log 1 is copied to another specified directory. This directory is called the archive directory, and the copied file is called the archive redo log. You can perform catastrophic recovery only when the database is running in archive mode.

The difference between the archive log mode and the non-archive log mode: the non-archive mode only supports cold backup, and only supports full backup during recovery. data during the last full backup to system error cannot be recovered. archive mode supports hot backup, Incremental backup, and partial recovery. you can use archive log list to check whether the archive mode is not the archive mode when the archive mode is in the status.

3.How redo logs work

Background ProcessLgwrWrites the log entries in the redo log buffer to the redo log file.. When a user commits a transaction, lgwr writes the redo entry of the transaction to the redo log file and assigns it an identifier (System Change number SCN). SCN is used to identify the redo log entry of the transaction. The transaction commit is completed only when the "redo log entry" of the transaction is successfully written into the redo log file on the disk.

In the following cases, lgwr will automatically write the log entries in the redo log buffer to the redo log file:

"Every 3 seconds

A user submits a transaction

Log buffer is filled with 1/3

Log buffer will have 1 MB of Log Data

Check Point checkpoint

Check Point occurs in the following situations:
A. Each log Switch
B. When the instance is disabled using the normal, transactional, and immediate options
C. Set the initialization parameter fast_start_mttr_target to force it to happen.
D. The database administrator manually sets alter system checkpoint, alter tablespace, and datafile offline.
E. Use the alter tablespace [offline normal | read only | beginbackup] statement to cause a checkpoint for the specified data file.
Note: If the log_checkpoints_to_alert parameter is set to true, the checkpoint information is recorded in the alert_sid.log file.

4.Redo log status

Current indicates the redo log group being written by lgwr.

Active indicates that the changes recorded in the redo log group have not been written to the data file, and the active redo log group must be used for instance recovery;

Inactive indicates that the redo log group in inactive state is not required for instance recovery;

Unused indicates that the redo log group is created and not used yet;

5.Instance recovery

If the database suddenly loses power, changes to the database are not actually written into the database file, but Oracle ensures that changes to the database have been recorded in the redo log file. If the routine fails, it is similar to executing the shutdown abort command to shut down the database. After the hardware problem and power failure are solved, the DBA only needs to execute the startup command to start the routine, you do not need to execute any recovery command.When executedStartupCommand to start the routine and load the database, the background processSMONRoutine recovery is automatically executed to synchronize all database files (data files, control files, and redo logs. Instance recovery automatically applies changes recorded in the redo log file to the data file, so there will be no data loss.

 

6.Redo log group operations

1.1 Database Log Archiving

1. Check whether the Oracle database is in archive mode:

SQL> select name, log_mode from V $ database;

Name log_mode
---------------------
Ptldb noarchivelog

2. View redo log groups in the database

SQL> select * from V $ log;

Group # thread # sequence # bytes membersarc status first_change # first_time

1 1 1558 315392000 1 No inactive 459785223 20-jul14

2 1 1559 315392000 1 no current 460781485 21-jul14

3 1 1557 315392000 1 No inactive 458700182 19-jul-14

3. View redo log files in the database

Know the redo log files and the group of each redo log file.

SQL> select * from V $ logfile;

Group # status type member is _

3 online/opt/Oracle/oradata/ptldb/redo03.log No

2 online/opt/Oracle/oradata/ptldb/redo02.log No

1 online/opt/Oracle/oradata/ptldb/redo01.log No

4. Create a redo log Group

Alter database add logfile Group 4 ('/dev/rlv_vgdb9_data22', '/dev/rlv_vgdb9_data23', '/dev/rlv_vgdb9_data24') size8191m;
-- In this way, a log group is added at most, and each of the three members in the log group has a size of MB.

5. Add a member to a log group.
Alterdatabase add logfile member '/dev/rlv_vgdb9_data25' to group 4;
-- Note: you do not need to specify the size of the log group members.
6. delete a log group.
Alterdatabase drop logfile group 4;
-- Note: The current log group cannot be deleted, the active log group cannot be deleted, and the log group without archiving cannot be deleted.
7. delete a member of the log group.
Alterdatabase drop logfile member '/dev/rlv_vgdb9_data25 ';
-- Note: Each group must retain at least one member;

8. Change the status of the redo log group.

Alter system switchlogfile;

Changes from active to inactive

Alter system checkpoint;

 

Configure the database archive Mode

Change non-archive mode to archive mode:

1) SQL> shutdown normal/immediate;

2) SQL> Start Mount;

3) SQL> alter database archivelog;

4) SQL> alter database open;

5) SQL> make a full backup because the backup logs generated in non-archive log mode are no longer available in archive mode. This step is not very important!

 

Change archive mode to non-archive mode:

1) SQL> shutdown normal/immediate;

2) SQL> Start Mount;

3) SQL> alter database noarchivelog;

4) SQL> alter database open;

 

 

3. Enable automatic archiving: log_archive_start = true

In archive mode, the log file group cannot be overwritten. When the log file is full, if no manual archiving is performed, the system suspends until the archiving is complete.

At this time, you can only read but not write.

Close and restart the archiving log process during running

SQL> archive log stop

SQL> archive log start

 

4. manual archiving: log_archive_start = false

Archive Current Log File

SQL> alter systemarchive log current;

Archive log files numbered 052

SQL> alter systemarchive log sequence 052;

Archive all log files

SQL> alter systemarchive log all;

Change the log archiving target

SQL> alter systemarchive log current to '& path ';

 

1.2 log Switching

Alter system switch logfile;
-- Log switching means to stop writing to the current group and write to a new group. The system can automatically or manually complete the change.
-- When a switch logfile occurs, the system completes the checkpoint operation in the background.
-- Manually switch the log Group
-- Log Group A: currently being written.
-- Log group B: writable.
-- Checkpoint ensures the consistency of the control file, data file header, and log file header SCN, which is an important mechanism for the database to maintain data integrity.
-- After switching the log group manually, Oracle starts to write logs to log group B and performs a checkpoint to write the dirtybuffer corresponding to some logs in log group A that have not passed the checkpoint from the buffer cache.
-- Data file. After checkpoing is completed
-- Log Group A: writable.
-- Log group B: currently being written.
-- In addition, alter tablespace userbegin backup, drop table, and truncate table all cause checkpoint operations.
-- In addition to the above manual and automatic checkpoint, you can also control the checkpoint through some parameters.
-- Fast_start_mttr_target (MTTR: Time Interval for data recovery)
-- Checkpoints is used for synchronization. The higher the synchronization frequency, the shorter the recovery time required when a system error occurs.
-- Synchronization-related parameters: fast_start_mttr_target (the database uses it to change the next three parameters), fast_start_io_target, log_checkpoint_interval, log_checkpoint_timeout