Oracle programming Art Study Notes (19)-Database Log Mode

 

The database can have two LOG modes: ARCHIVELOG mode and NOARCHIVELOG mode.

In NOARCHIVELOG mode, online redo logs are not archived after they are fully written, so they cannot be recovered after a fault occurs.

You can use the select log_mode from v $ database statement to query the current log mode, or use the archive LOG list statement with the sysdba permission.

For example:

Sys @ ORA11GR2> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Next log sequence to archive 20

Current log sequence 20

To change the LOG mode, shut down the database, start up mount, and then call the following statement to modify it:

Alter database noarchivelog/archivelog

Then alter database open to open the database.

 

You can change the log_archive_dest_1 parameter to change the archive log directory (the directory specified by the db_recovery_file_dest parameter in pfile/spfile is invalid). After 10 Gb, you can generate multiple copies of logs to different locations, therefore, you can specify multiple directories, for example:

Alter system set log_archive_dest_1 = 'location =/data/oracle/log1/archive_log ';

Alter system set log_archive_dest_2 = 'location =/data/oracle/log2/archive_log ';

To restore to the directory specified by db_recovery_file_dest, set the preceding log_archive_dest_n parameter to null ('').

 

You can use the alter system archive log current statement to forcibly archive logs;

You can use select name from v $ archived_log to view the path of the archive log file.

 

In NOARCHIVELOG mode, some operations, such as create table, do not generate REDO logs except for data dictionary modifications.

 

Databases running in ARCHIVELOG mode can be operated in NOLOGGING mode.

1) set NOLOGGING in SQL statements

For example, use NOLOGGING In the create table statement,

Create table t nologging as select * from all_objects;

In addition to the modification of the data dictionary, the REDO log is not generated, which is greatly reduced compared with the use of NOLOGGING. (In my test environment, I reduced from 6.56M to 143 K)

Note that NOLOGGING cannot avoid generating redo for all subsequent operations. Subsequent "normal" operations (such as INSERT, UPDATE, and DELETE) on the table T created above will still generate logs. Other special operations (for example, using SQL * Loader's direct path loading, or using INSERT/* + APPEND */syntax for direct path insertion) do not generate logs (unless the full log mode is enabled again by using the alter table t logging; Statement ).

 

2) set the NOLOGGING attribute on the segment (table or index)

For example, alter index xxx nologging/logging;

In this way, the redo log will not be generated when this index is rebuilt.

 

In an ARCHIVELOG database, if you use NOLOGGING properly, you can speed up many operations. However, exercise caution. After the operation, you must create a new baseline backup for the affected data files as soon as possible.

 

After Oracle9i Release 2, DBA may place the database in force logging mode. In this case, all operations are included in the log.

Query SELECT FORCE_LOGGING from v $ DATABASE to check whether the log mode is mandatory.

 

From NowOrNever