Oracle archiving Mode

Directory

Check whether the oracle database is in archive Mode

What is Oracle archive mode?

Configure the database archive Mode

Check whether the oracle database is in archive Mode

[1]

1. select name, log_mode from v $ database;

NAME LOG_MODE

------------------------------------------

QUERY NOARCHIVELOG

2. Use the archive log list Command

Database log mode No Archive Mode

Automatic archival Disabled

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

Oldest online log sequence 739

Current log sequence 741

What is Oracle archive 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. 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. Write online redo log group 1, and then repeat it.

If the database is in non-archive mode, online logs are discarded during Switching. In archive mode, logs are archived When switching. For example, currently online redo log 1 is used. When 1 is full, log switching occurs and online redo log 2 is started, at this time, the content of online redo log 1 will be 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.

1. Differences between the archive log mode and the non-archive log Mode

In non-archive mode, only cold backup can be performed, and only full backup can be performed 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 current mode is ARCHIVE or non-ARCHIVE.

Configure the database archive Mode

1. Change non-archive mode to archive mode:

1) SQL> conn/as sysdba (connect to the database as DBA)

2) SQL> shutdown immediate; (close the database immediately)

3) SQL> startup mount (start the instance and load the database, but do not open it)

4) SQL> alter database archivelog; (change the database to archive Mode)

5) SQL> alter database open; (open the database)

6) SQL> alter system archive log start; (enable automatic archiving)

7) SQL> exit (exit)

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!

2. Change the archive mode to non-archive mode:

1) SQL> SHUTDOWN NORMAL/IMMEDIATE;

2) SQL> STARTUP 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 SYSTEM ARCHIVE LOG CURRENT;

Archive log files numbered 052

SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;

Archive all log files

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

Change the log archiving target

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

5. Archive mode and non-archive Mode Conversion

The inverse process in step 1.

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 and can be modified dynamically using 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 archiving targets, multiple archiving targets, remote archiving targets, and archiving log formats

Archive target LOG_ARCHIVE_DEST_n

Local archiving target:

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

Target of remote Archiving:

SQL> LOG_ARCHIVE_DEST_2 = "SERVICE = STANDBY_DB1 ";

Force archiving target. If an error occurs, retry in 600 seconds:

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

Optional archiving target. If an error occurs, discard the archive:

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

Archiving target status: Close archiving target and open archiving 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

Archived log format

LOG_ARCHIVE_FORMAT

8. Obtain archived log information

V $ ARCHIVED_LOG

V $ ARCHIVE_DEST

V $ LOG_HISTORY

V $ DATABASE

V $ ARCHIVE_PROCESSES

Archive log list;