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

Editor to share with you how to view oracle archive log path 1, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

View oracle archive log path

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.

Sys@ora10g > show parameter log_archive_format

NAME TYPE VALUE

Log_archive_format string t_%s_%r.dbf

Sys@ora10g > 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.

Sys@ora10g > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 98

Next log sequence to archive 100

Current log sequence 100

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.

Sys@ora10g > show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE

Db_recovery_file_dest string / oracle/app/oracle/flash_recovery_area

3) leave the db_recovery_file_dest parameter empty

Alter system set db_recovery_file_dest =''scope=spfile

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

Alter system set log_archive_dest =''scope=spfile

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=/oracle/arch/ora10g' 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

The above is all the contents of the article "how to View oracle Archive Log path 1". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.