Oracle redo log and archive mode

Check whether the oracle database is in archive mode

Sql code

1. select name,log_mode from v$database;

2. NAME LOG_MODE

3. ------------------ ------------------------

4. QUERY             NOARCHIVELOG

5. Use the ARCHIVE LOG LIST command

6. Database log mode             No Archive Mode

7. Automatic archival             Disabled

8. Archive destination             /data/oracle/product/10.2.0/db_1//dbs/arch

9. Oldest online log sequence             739

10. Current log sequence             741

[sql] view plaincopy

1. select name,log_mode from v$database;

2. NAME             LOG_MODE

3. ------------------ ------------------------

4. QUERY             NOARCHIVELOG

5. Use the ARCHIVE LOG LIST command

6. Database log mode             No Archive Mode

7. Automatic archival             Disabled

8. Archive destination             /data/oracle/product/10.2.0/db_1//dbs/arch

9. Oldest online log sequence             739

10. Current log sequence             741

What is the Oracle archive mode?

　　 Oracle database has an online redo log. This log records changes made to the database, such as inserts, deletes, and updates data. These operations will be recorded in the online redo log. Generally, the database must have at least 2 online redo log groups. When an online redo log group is full, log switching occurs. At this time, online redo log group 2 becomes the currently used log. When online redo log group 2 is full, log switching occurs again , To write online redo log group 1, and so on repeatedly.

　　 If the database is in non-archive mode, online logs will be discarded when switching. In archive mode, when a log switch occurs, the switched log will be archived. For example, online redo log 1 is currently being used. When 1 is full, a log switch occurs and online redo log 2 is written. At this time, the contents of online redo log 1 will be copied to another specified directory. This directory is called the archive directory, and the copied files are called archived redo logs.

　　 Disaster recovery can only be carried out when the database is running in archive mode.

1. The difference between archive log mode and non-archive log mode

The non-archive mode can only be used for cold backup, and can only be used for full backup when restoring. The data from the most recent full backup to the system error period cannot be restored.

The archive mode can be used for hot backup, incremental backup, and partial recovery.

With ARCHIVE LOG LIST, you can check whether the archive mode or non-archive mode is the status of the period mode.

Configure the archive mode of the database

Change from non-archive mode to archive mode:

Sql code

1) SQL>SHUTDOWN NORMAL/IMMEDIATE;              first down the data

2) SQL>START MOUNT;              Start the database instance to mount state, but do not open

3) SQL>ALTER DATABASE ARCHIVELOG;              Set the database to archive log mode

4) SQL>ALTER DATABASE OPEN;              open the database

5) SQL>archive log list;              confirm that the database is now in archive log mode

6) SQL>archive log all;              archive the redo logs at this point in time

[sql] view plaincopy

1) SQL>SHUTDOWN NORMAL/IMMEDIATE;              first down the data

2) SQL>START MOUNT;              Start the database instance to mount state, but do not open

3) SQL>ALTER DATABASE ARCHIVELOG;              Set the database to archive log mode

4) SQL>ALTER DATABASE OPEN;              open the database

5) SQL>archive log list;              confirm that the database is now in archive log mode

6) SQL>archive log all;              archive the redo logs at this point in time

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

Sql code

SQL>alter tablespace users begin bacup;

[sql] view plaincopy

SQL>alter tablespace users begin bacup;

====================

Use if you stop the archive mode

alter database noarchivelog

========================

Change archive mode to non-archive mode:

Sql code

1)SQL>SHUTDOWN NORMAL/IMMEDIATE;

2)SQL>START MOUNT;

3)SQL>ALTER DATABASE NOARCHIVELOG;

4)SQL>ALTER DATABASE OPEN;

[sql] view plaincopy

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 the archive mode, the log file group is not allowed to be overwritten (overwritten).When the log file is full, if there is no manual archiving, the system will hang until the archive is completed.

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

Close and restart the archive log process during operation

SQL>ARCHIVE LOG STOP

SQL>ARCHIVE LOG START

4. Manual archiving: LOG_ARCHIVE_START=FALSE

Archive the current log file

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;

Archive the log file with serial number 052

SQL>ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;

Archive all log files

SQL>ALTER SYSTEM ARCHIVE LOG ALL;

Change the archive log target

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT TO'&PATH';

5. Conversion of archive mode and non-archive mode

The inverse process of step 4.

6. Configure multiple archiving processes

Q: When do I need to use multiple archiving processes?

A: If the archiving process consumes a lot of time, you can start multiple archiving processes. This is a dynamic parameter that can be dynamically modified with ALTER SYSTEM.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;

Up to 10 archiving processes can be specified in Oracle9i

Dynamic performance view related to the archiving process

v$bgprocess,v$archive_processes

7. Configure archive targets, multiple archive targets, remote archive targets, archive log format

Archive destination LOG_ARCHIVE_DEST_n

Local archive target:

SQL>LOG_ARCHIVE_DEST_1 = "LOCATION=D:ORACLEARCHIVEDLOG";

Remote archiving target:

SQL>LOG_ARCHIVE_DEST_2 = "SERVICE=STANDBY_DB1";

Mandatory archiving target, if there is an error, try again after 600 seconds:

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION=E:ORACLEARCHIVEDLOG MANDATORY REOPEN=600";

Optional archiving target, if an error occurs, give up archiving:

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = "LOCATION=E:ORACLEARCHIVEDLOG OPTIONAL";

Archive target status: close the archive target and open the archive target

Close archive target 1

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = DEFER

Open archive target 2

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE

Archive log format

LOG_ARCHIVE_FORMAT

8. Obtain archive log information

V$ARCHVIED_LOG

V$ARCHVIE_DEST

V$LOG_HISTORY

V$DATABASE

V$ARCHIVE_PROCESSES

ARCHIVE LOG LIST;