Database archiving mode

Background: After the new speech single function on-line, the production environment before the scheduled task to produce the word order, resulting in the database has 2000多万条 record waiting to generate a statement, scheduled tasks are configured correctly, because the environment to open the database archiving mode, While the process of generating a single word is constantly in the update and delete behavior of the data in the track table, resulting in a large number of archived logs, disk space must be monitored in real time.

Oracle database archiving mode is generally in development, test environment is not set by default, mainly to save disk space, in the production environment, set the log mode and automatic archiving is mainly to ensure the system security, restore data.

(i) The automatic archiving of Oracle database for logs requires two conditions:

1. Is the setting of the database log mode (can be Archive mode and no Archive mode)

2. Is the auto-archive mode setting (Automatic archival, enabled and disabled)

(ii) View the current log mode and archiving mode settings for Oracle data

Use the archive log List command to view

1. Run the view results in the log Auto-archive mode

Database Log Mode Archive mode
Automatic Archival Enabled
Archive destination/backup/archivelog
Oldest online log sequence 2131
Next Log sequence to Archive 2133
Current Log sequence 2133

2. The view results of the database log Auto-archive mode are not started

Database log mode No Archive mode
Automatic Archival Disabled
Archive Destination/u01/app/oracle/product/8.1.7/dbs/arch
Oldest online log sequence 194
Current log sequence 196

(iii) Setting of the database logging mode

When you create a database, you can specify the log mode of the database in the CreateDatabase statement, which defaults to the Noarchivelog mode, which increases the creation time by about 20% if you specify archive mode when you create the database. When you start instance later, it's usually only a few seconds.

The steps and actions of the log mode setting switch (Archive mode and no Archive mode) are as follows:

1. Close the DB instance

sql> shutdown immediate;

2. Backing Up the database

This backup is used with subsequent logs for future disaster recovery (it is important to change to archive log mode, without this database backup, only log files cannot be recovered from that point in time)

3. Start the DB instance to mount state, but do not open it.

sql> startup Mount;

4. Switch the database log mode.

sql> ALTER DATABASE Archivelog;( set up the DB to archive log mode )

sql> alter Databasenoarchivelog;( Set Database to non-archived log mode )

5. Open the database.

sql> ALTER DATABASE open;

6. Confirm that the database is now in archive log mode.

Sql> Archive log list;

7. Archive the redo logs at this point in time

Sql> archive Logall;

8. Verify that the newly generated log file is under the appropriate archive directory.

(iv) Setting up the automatic database archiving mode

In this mode, the database launches an arch process that is specifically responsible for writing the redo logs to the appropriate directory of the system's archiving device. Set parameters in the database's parameter file (typically in the $oracle_home/dbs/init*.ora file):

log_archive_start=log_archive_dest= log_archive_format=

Log_archive_start: Set to True if automatic archiving is required, or false if required for non-automatic archiving

Log_archive_dest: This parameter sets the path for the Archivelogs archive to be stored.

Log_archive_format: This parameter sets the naming format for Archivelogs. For example, if you set the format to: Arch%s.arc log file will be: Arch1.arc, Arch2.arc, Arch3.arc

These parameters are set to take effect only before the DB instance is started, and if you set it in the database run, you must restart the database for it to take effect. If the database is running and cannot be immediately re-started, to set it to auto-archive mode, do the following:

sql> ALTER SYSTEM ARCHIVE LOG START;

To set it to non-auto-archive mode (cancel Auto-archive):

sql> ALTER SYSTEM ARCHIVE LOG STOP;

However, if the database is re-started, the result of the modification to the statement is invalidated, and the auto-archive setting is set according to the value of Log_archive_start in the system parameter file.

(v) Several setup situations:

1. Database log mode Archive Mode,automaticarchival Enabled This is the Oracle database logging and archiving mode setting in most production environments, in which case A regular backup of the database (with hot spare and cold) and archived log backups effectively restores the database to the full point in time of the archived log.
2. Database log mode Archive Mode,automaticarchival Disabled In this case, the databases are not automatically archived and need to be archived manually. If all the online logs are full, and there is no timely manual archiving, because LGWR no available online log writable, the database will be hanging here, only after manual archiving, there is an available online log to continue. This situation should be avoided in the production environment. The manual archive operation is as follows:

sql> ALTER SYSTEM ARCHIVE LOG all;

The database will file the online logs for processing

1. Database log mode NO Archive mode,automaticarchival Enabled In some cases, the DBA has only set log_archive_start=true in the database parameters file. Then after the database is up and looking at the Arch archive process has been up, but although Oracle has made several log switches, but still no archived logs, this is the case, if the database is not in Arvhivelog mode, Redolog will not be archived.
2. Database log mode NO Archive mode,automaticarchival Disabled This setting is the default setting for the Oracle database that you just installed, and most of the development environment. That is, the archive is not filed.

Archive log cleanup (not manually deleted if Rmna backup is used)

Archived logs can be cleaned up without causing the database to be problematic, but the general history is retained for nearly 1 hours. First, manually delete the archive log files second, using Rman to delete the database records of the archive list information

1. To enter Rman in this database, enter "Rman target/" in the command line mode, enter Rman, check the prompt to confirm if the connection is the library?

Oracle ~# reman/target

1. To view the status of an archived log file:

rman> list Archivelog all;

1. Delete log files from the operating system (manually removed to the operating system level, or it can be the first step;)
2. Update the archived log information;

Rman>crosscheck Archivelog all;
rman> Delete Expired Archivelog all; (Type "yes" when confirming)
Rman> Exit

Database archiving mode