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

This article will explain in detail how to view and modify the oracle archive log path. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

1. Modify the format of the archive log

The default format is "% t_%s_%r.dbf". We try to change the format to "% t_%s_%r.arch", which is a static parameter that requires a database restart to take effect.

SQL > show parameter log_archive_format

NAME TYPE VALUE

-

Log_archive_format string t_%s_%r.dbf

SQL > alter system set log_archive_format='%t_%s_%r.arch' scope=spfile

System altered

two。 Modify the location of the archive log

1) use the "archive log list" command to check where the archive logs are stored by default.

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

2) the above result mentions the Archive destination of USE_DB_RECOVERY_FILE_DEST. In fact, we can remove "USE_" and use "DB_RECOVERY_FILE_DEST" to get a detailed explanation of the parameters in the database.

SQL > show parameter db_recovery_file_dest

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_

Area

Db_recovery_file_dest_size big integer 4182M

3) leave the db_recovery_file_dest parameter empty

SQL > alter system set db_recovery_file_dest =''scope=spfile

System altered.

4) set the log_archive_dest parameter to be empty, just in case

SQL > alter system set log_archive_dest =''scope=spfile

System altered.

5) the effective parameter we use here is log_archive_dest_1, and the modification method is as follows. Please pay attention to the grammar

Alter system set log_archive_dest_1 = 'location=/oradata/arch/ora10g' scope=spfile

Alter system set log_archive_dest_1='LOCATION=/oradata/arch/archivelogvalid_for= (all_logfiles,all_roles) db_unique_name=ocrl' scope=spfile

6) restart the database to make all the above modified parameters effective.

Sys@ora10g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sys@ora10g >

Sys@ora10g > startup

ORACLE instance started.

Total System Global Area 2.1475E+10 bytes

Fixed Size 2111160 bytes

Variable Size 2399144264 bytes

Database Buffers 1.9059E+10 bytes

Redo Buffers 14663680 bytes

Database mounted.

Database opened.

7) confirm that the modification is successful

(1) the first confirmation method: follow the "Archive destination" information using the "archive log list" command

Sys@ora10g > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / oracle/arch/ora10g

Oldest online log sequence 121

Next log sequence to archive 123

Current log sequence 123

(2) the second confirmation method: file it manually and confirm it through the v$archived_log view.

Sys@ora10g > alter system switch logfile

System altered.

Sys@ora10g > col NAME for A40

Sys@ora10g > alter session set nls_date_format = 'yyyy-mm-dd hh34:mi:ss'

Session altered.

Sys@ora10g > select recid, name, first_time from v$archived_log

RECID NAME FIRST_TIME

1 2009-06-06 01:38:39

2 2009-06-06 05:50:32

... This omits the previous archive log information.

132 / oracle/arch/ora10g/1_123_688786498.arch 2009-09-20 11:26:26

8) the above modification process can also be achieved by directly modifying the pfile file.

List the key parameters after modification:

$cat $ORACLE_HOME/dbs/initora10g.ora

Other parameters that you do not care about are omitted here

* .log_archive_format='%t_%s_%r.arch'

* .db_recovery_file_dest=''

* .log_archive_dest=''

* .log_archive_dest_1='location=/oracle/arch/ora10g'

3. After the introduction of the modification process, we will discuss some interesting phenomena together.

1) interesting phenomenon 1: the purpose of modifying the archive path can be achieved by using the log_archive_dest parameter without using the log_archive_dest_1 parameter mentioned above

Give a concise list of the modification process:

(1) modify the archive file format

Alter system set log_archive_format='%t_%s_%r.arch' scope=spfile

(2) leave db_recovery_file_dest empty

Alter system set db_recovery_file_dest =''scope=spfile

(3) leave log_archive_dest_1 empty

Alter system set log_archive_dest_1 =''scope=spfile

(4) enable log_archive_dest parameter

Alter system set log_archive_dest ='/ oracle/arch/ora10g' scope=spfile

(5) restart the database to make all the above parameters effective

Shutdown immediate

Startup

(6) of course, the above process can also be accomplished by modifying pfile.

The key parameters of the modified pfile are as follows:

* .log_archive_format='%t_%s_%r.arch'

* .db_recovery_file_dest=''

* .log_archive_dest='/oracle/arch/ora10g'

* .log_archive_dest_1=''

The above method can also achieve the purpose of modifying the archive path, but the parameter log_archive_dest is not recommended. In 10g, it is recommended to use the parameter log_archive_dest_n to complete the modification.

2) interesting phenomenon 2: if the parameters db_recovery_file_dest and log_archive_dest are set at the same time, archive logs will be generated in both directories.

(1) modify pfile. The experiment can be carried out with the following parameters

* .log_archive_format='%t_%s_%r.arch'

* .db_recovery_file_dest='/oracle/app/oracle/flash_recovery_area'

* .log_archive_dest='/oracle/arch/ora10g'

* .log_archive_dest_1=''

(2) restart the database using pfile

Sys@ora10g > shutdown immediate

Sys@ora10g > startup pfile ='/ oracle/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora'

(3) the "interesting" phenomenon appeared.

Sys@ora10g > alter system switch logfile

Sys@ora10g > alter system switch logfile

Sys@ora10g > col NAME for A40

Sys@ora10g > alter session set nls_date_format = 'yyyy-mm-dd hh34:mi:ss'

Sys@ora10g > select recid, name, first_time from v$archived_log

RECID NAME FIRST_TIME

/ oracle/arch/ora10g/1_128_688786498.arch 2009-09-20 12:09:29

/ oracle/arch/ora10g/1_129_688786498.arch 2009-09-20 12:09:29

139 / oracle/arch/ora10g/1_130_688786498.arch 2009-09-20 12:09:30

/ oracle/app/oracle/flash_recovery_area/O 2009-09-20 12:09:30

RA10G/archivelog/2009_09_20/o1_mf_1_130_

5ccccmw6_.arc

141 / oracle/arch/ora10g/1_131_688786498.arch 2009-09-20 12:20:03

/ oracle/app/oracle/flash_recovery_area/O 2009-09-20 12:20:03

RA10G/archivelog/2009_09_20/o1_mf_1_131_

5cccd5kt_.arc

(4) conclusion

Both directories generate archive logs with the same content

The format of the archive files generated under the / oracle/app/oracle/flash_recovery_area directory is not affected by the log_archive_format parameter, that is, the parameter of log_archive_format is only valid for log_archive_dest and log_archive_dest_n.

3) interesting phenomenon 3: when db_recovery_file_dest and log_archive_dest_n are set at the same time, only the directory of log_archive_dest_n generates logs

(1) modify pfile. The experiment can be carried out with the following parameters

* .log_archive_format='%t_%s_%r.arch'

* .db_recovery_file_dest='/oracle/app/oracle/flash_recovery_area'

* .log_archive_dest=''

* .log_archive_dest_1='location=/oracle/arch/ora10g'

(2) restart the database using pfile

Sys@ora10g > shutdown immediate

Sys@ora10g > startup pfile ='/ oracle/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora'

(3) the experimental results are as follows.

Sys@ora10g > alter system switch logfile

Sys@ora10g > alter system switch logfile

Sys@ora10g > col NAME for A40

Sys@ora10g > alter session set nls_date_format = 'yyyy-mm-dd hh34:mi:ss'

Sys@ora10g > select recid, name, first_time from v$archived_log

RECID NAME FIRST_TIME

/ oracle/arch/ora10g/1_129_688786498.arch 2009-09-20 12:09:29

139 / oracle/arch/ora10g/1_130_688786498.arch 2009-09-20 12:09:30

/ oracle/app/oracle/flash_recovery_area/O 2009-09-20 12:09:30

RA10G/archivelog/2009_09_20/o1_mf_1_130_

5ccccmw6_.arc

141 / oracle/arch/ora10g/1_131_688786498.arch 2009-09-20 12:20:03

/ oracle/app/oracle/flash_recovery_area/O 2009-09-20 12:20:03

RA10G/archivelog/2009_09_20/o1_mf_1_131_

5cccd5kt_.arc

/ oracle/arch/ora10g/1_132_688786498.arch 2009-09-20 12:20:21

/ oracle/arch/ora10g/1_133_688786498.arch 2009-09-20 12:32:58

(4) conclusion

Compared with the conclusion of "interesting phenomenon 2", we can see that only the archive path set by the log_archive_dest_1 parameter takes effect.

3) interesting phenomenon 4: log_archive_dest and log_archive_dest_1 parameters cannot be set at the same time

(1) to modify pfile, the experiment can be carried out with the following parameters. Whether the db_recovery_file_dest parameter is set or not will not affect our experimental results.

* .log_archive_format='%t_%s_%r.arch'

* .db_recovery_file_dest='/oracle/app/oracle/flash_recovery_area'

* .log_archive_dest='/oracle/arch/ora10g'

* .log_archive_dest_1='location=/oracle/arch/ora10g'

(2) an error will be reported when restarting the database using pfile, which cannot be set in this way.

Sys@ora10g > shutdown immediate

Sys@ora10g > startup pfile ='/ oracle/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora'

ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

(4) conclusion

The log_archive_dest and log_archive_dest_1 parameters cannot be set at the same time, that is, if the log_archive_dest_1 parameter is enabled, it can no longer be set repeatedly with the log_archive_dest parameter.

Through this experiment, we can draw a conclusion: the log_archive_dest parameter exists only for backward compatibility, so if you need to modify the archive generation path in the post-9i version of Oracle, it is recommended that you use the log_archive_dest_n parameter.

This is the end of the article on "how to view and modify the oracle archive log path". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.