Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the methods of Oracle archive log management". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the methods of Oracle archive log management".

In general, the default NOARCHIVELOG mode is selected when the database is installed. If you want to turn on ARCHIVELOG mode, you need to set initialization parameters for the archive log path.

The archive log path can be configured with one or more. The path can be a local path, such as a local file system, ASM disk group, or a remote path, such as a backup database. By setting multiple archive log paths, you can make the archive log redundant and ensure that the archive log is available even if one of the target disks is damaged.

When configuring parameters, the path of the archive log can be dynamically modified to take effect the next log switch.

This paper gives three commonly used archiving path setting schemes: single archiving path scheme, two-way archiving path scheme and multiple archiving path scheme.

Option 1: single archive path

If only a single archive directory is set, db_recovery_file_dest parameter is used by default and Fast Recovery Area is used to store archive data. You can also manually configure log_archive_dest initialization parameters, which can be configured as local file system and ASM disk group. The method of using the log_archive_dest_n parameter is discussed in the third scenario.

The specific operations are as follows:

-- if you use fast recovery area to store archive logs, you should note that there is a size limit in this area. It is recommended to adjust the size of SYS@cams > show parameter db_recovery_file_dest NAME TYPE VALUE---db_recovery_file_dest string / u01 / App/oracle/fast_recovery_ areadb_recovery_file_dest_size big integer 4182MSYS@cams > alter system set db_recovery_file_dest_size=10G System altered. SYS@cams > show parameter db_recovery_file_dest NAME TYPE VALUE---db_recovery_file_dest string / u01/app/oracle/fast_recovery _ areadb_recovery_file_dest_size big integer 10G Murray-if you use a local path to store archive logs You need to leave the db_recovery_file_dest parameter empty, and then set the log_archive_dest parameter. Here is an example of a local file system, where readers can test ASM disk groups on their own. SYS@cams > alter system set db_recovery_file_dest=''; System altered. SYS@cams > alter system set log_archive_dest='/u01/app/oracle/archive'; System altered.

You need to leave the LOG_ARCHIVE_DEST_n and DB_RECOVERY_FILE_DEST parameters empty to avoid the following problems:

SYS@cams > alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST';alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n orDB_RECOVERY_FILE_DEST

At this point, you can see the change in the trace log, and the default value of LOG_ARCHIVE_DEST_1 is automatically adjusted. Where, in the case of Fast Recovery Area archiving, the default value of LOG_ARCHIVE_DEST_1 is USE_DB_RECOVERY_FILE_DEST.

Tue Dec 18 17:48:24 2018ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;Tue Dec 18 17:52:13 2018Cleared LOG_ARCHIVE_DEST_1 parameter default valueUsing LOG_ARCHIVE_DEST_1 parameter default value as / u01/app/oracle/product/11.2.0/db_1/dbs/archALTER SYSTEM SET db_recovery_file_dest='' SCOPE=BOTH;Tue Dec 18 17:52:28 2018ALTER SYSTEM SET log_archive_dest='/u01/app/oracle/archive' SCOPE=BOTH -- first create a new archive path [oracle@XLJ181 trace] $mkdir-p / u01ActionActionActionArchive 1_25_994950965.dbf-- switch logs at this time, trigger writing archive log SYS@cams > alter system switch logfile;System altered.-- to view the newly generated archive log [oracle@XLJ181 trace] $ll / u01/app/oracle/archivetotal 11960 Mustang r-1 oracle oinstall 12244992 Dec 18 17:57 1_25_994950965.dbf

You can also see the LGWR switch operation in the trace log

Tue Dec 18 17:57:10 2018Thread 1 cannot allocate new log, sequence 26Private strand flush not complete Current log# 1 seq# 25 mem# 0: / u01/app/oracle/oradata/cams/redo01.logThread 1 advanced to log sequence 26 (LGWR switch) Current log# 2 seq# 26 mem# 0: / u01/app/oracle/oradata/cams/redo02.logTue Dec 18 17:57:10 2018Archived Log entry 20 added for thread 1 sequence 25 ID 0x9e3b45f3 dest 1: scenario 2: two archiving paths

If you want to set up two archive paths, the common solution is to configure the log_archive_dest and log_archive_duplex_dest parameters. The method of log_archive_dest_n parameters is discussed in the third scheme.

The specific operations are as follows:

-- create a new archive path [oracle@XLJ181 trace] $mkdir-p / u01 mkdir. Set the log_archive_duplex_dest parameter path and switch log trigger to write archive log SYS@cams > alter system set log_archive_duplex_dest='/u01/app/oracle/archive1'; System altered. SYS@cams > alter system switch logfile; System altered.-- looks at the newly generated archive log file, and it is obvious that the newly generated archive is the same size and file name. The two archive logs are redundant to each other. [oracle@XLJ181 trace] $ll / u01/app/oracle/archivetotal 15704 oracle oinstall r-1 oracle oinstall 12244992 Dec 18 17:57 1 "25" 994950965.dbfkashi r-1 oracle oinstall 3830272 Dec 18 18:30 1_26_994950965.dbf [oracle@XLJ181 trace] $ll / u01/app/oracle/archive1total 3744 Mustang r-1 oracle oinstall 3830272 Dec 18 18:30 1_26_994950965.dbf

Looking at the trace log, you can see the LGWR switch operation, and then write the archive in two directories.

Tue Dec 18 18:30:01 2018Thread 1 cannot allocate new log, sequence 27Private strand flush not complete Current log# 2 seq# 26 mem# 0: / u01/app/oracle/oradata/cams/redo02.logThread 1 advanced to log sequence 27 (LGWR switch) Current log# 3 seq# 27 mem# 0: / u01/app/oracle/oradata/cams/redo03.logTue Dec 18 18:30:02 2018Archived Log entry 21 added for thread 1 sequence 26 ID 0x9e3b45f3 dest 1:Archived Log entry 22 added for thread 1 sequence 26 ID 0x9e3b45f3 dest 2: option 3: multiple archiving path

If you want to set up a multipath archive path, you need to use the log_archive_dest_n parameter, where n is an integer from 1 to 31, that is, you can specify 1 to 31 separate paths as the path to the archive log.

The value of the log_archive_dest_n parameter can be selected by selecting the LOCATION or SERVIDE keyword. "if you use the LOCATION keyword, you can select the local file system, ASM disk group, or the flashback recovery area represented by USE_DB_RECOVERY_FILE_DEST, and if you use the SERVICE keyword, you can select the network connection identifier of the standby library to connect to the backup database."

The specific operations are as follows:

-- create a local archive directory [oracle@XLJ181 admin] $mkdir-p / u01/app/oracle/archive2 [oracle@XLJ181 admin] $mkdir-p / u01Charger oracleandarchive3muri-configure the log_archive_dest_n parameter SYS@cams > alter system set log_archive_duplex_dest=''; System altered. SYS@cams > alter system set log_archive_dest=''; System altered. SYS@cams > alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'; System altered. SYS@cams > alter system set log_archive_dest_2='LOCATION=/u01/app/oracle/archive2'; System altered. SYS@cams > alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/archive3'; System altered.

You need to pay attention to the order of emptying in order to avoid the following errors

SYS@cams > alter system set log_archive_dest='';alter system set log_archive_dest=''*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-16022: LOG_ARCHIVE_DEST cannot be NULL because LOG_ARCHIVE_DUPLEX_DEST isnon-NULL

You need to leave the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters empty to avoid the following problems:

SYS@cams > alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST orLOG_ARCHIVE_DUPLEX_DEST-- switch log, trigger write archive log SYS@cams > alter system switch logfile; System altered.

Looking at the newly generated archive log file, it is clear that the newly generated archive is the same size and file name. The two archive logs are redundant to each other.

[oracle@XLJ181 admin] $ll / u01/app/oracle/archive2total 5440 Mustang r-1 oracle oinstall 4416512 Dec 18 19:09 1 "27" 994950965.dbfkosher r-1 oracle oinstall 1148416 Dec 18 19:23 1_28_994950965.dbf [oracle@XLJ181 admin] $ll / u01/app/oracle/archive3total 5440 Mustang r-1 oracle oinstall 4416512 Dec 18 19:09 1" 27" 994950965.dbfqr r-1 oracle Oinstall 1148416 Dec 18 19:23 1_28_994950965.dbf

Check the trace log and see an error message indicating that DB_RECOVERY_FILE_DEST has not set a value

Tue Dec 18 19:23:59 2018Errors in file / u01/app/oracle/diag/rdbms/cams/cams/trace/cams_arc3_29341.trc:ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not setErrors in file / u01/app/oracle/diag/rdbms/cams/cams/trace/cams_arc3_29341.trc:ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translatedORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not setArchived Log entry 25 added for thread 1 sequence 28 ID 0x9e3b45f3 dest 2:Archived Log entry 26 added for thread 1 sequence 28 ID 0x9e3b45f3 dest 3:

Configure values for the DB_RECOVERY_FILE_DEST parameter and switch logs

SYS@cams > alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'; System altered. SYS@cams > alter system switch logfile; System altered. SYS@cams > alter system switch logfile; System altered.

Looking at the trace log information, you can see that three archive paths were written at the same time.

Tue Dec 18 19:27:58 2018***WARNING: Files may exists in db_recovery_file_destthat are not known to the database. Use the RMAN commandCATALOG RECOVERY AREA tore-catalog any such files.If files cannot be cataloged, then manually delete themusing OS command.One of the following events caused this:1. A backup controlfile was restored.2. A standby controlfile was restored.3. The controlfile was re-created.4. Db_recovery_file_dest had previously been enabled and then disabled.***ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;Tue Dec 18 19:27:58 2018db_recovery_file_dest_size of 10240 MB is 5.63% used. This is auser-specified limit on the amount ofspace that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Tue Dec 18 19:28:01 2018Thread 1 cannot allocate new log Sequence 30Private strand flush not complete Current log# 2 seq# 29 mem# 0: / u01/app/oracle/oradata/cams/redo02.logThread 1 advanced to log sequence 30 (LGWR switch) Current log# 3 seq# 30 mem# 0: / u01/app/oracle/oradata/cams/redo03.logTue Dec 18 19:28:01 2018Archived Log entry 27 added for thread 1 sequence 29 ID 0x9e3b45f3 dest 2:Archived Log entry 28 added for thread 29 ID 0x9e3b45f3 dest 3:Tue Dec 29 19:33:38 2018Thread 1 cannot allocate new log Sequence 31Private strand flush not complete Current log# 3 seq# 30 mem# 0: / u01/app/oracle/oradata/cams/redo03.logThread 1 advanced to log sequence 31 (LGWR switch) Current log# 1 seq# 31 mem# 0: / u01/app/oracle/oradata/cams/redo01.logTue Dec 18 19:33:39 2018Expanded controlfile section 11 from 28 to 62 recordsRequested to grow by 34 records Added 2 blocks of recordsArchived Log entry 29 added for thread 1 sequence 30 ID 0x9e3b45f3 dest 1:Archived Log entry 30 added for thread 1 sequence 30 ID 0x9e3b45f3 dest 2:Archived Log entry 31 added for thread 1 sequence 30 ID 0x9e3b45f3 dest 3: thank you for reading, this is the content of "what are the methods of Oracle archive log management". After the study of this article, I believe you have a deeper understanding of the methods of Oracle archive log management. The specific use situation still needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.