oracle 归档路径 fra,【归档日志路径】之参数 log_archive_dest、LOG_ARCHIVE_DEST_n及快速恢复区路径关系...

更改归档日志路径

1.先查看归档路径

SYS@ORA11GR2>archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence13

Next log sequence to archive15

Current log sequence15

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter log_archive

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_configstring

log_archive_deststring

log_archive_dest_1string

log_archive_dest_10string

log_archive_dest_11string

log_archive_dest_12string

log_archive_dest_13string

log_archive_dest_14string

log_archive_dest_15string

log_archive_dest_16string

log_archive_dest_17string

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_18string

log_archive_dest_19string

log_archive_dest_2string

log_archive_dest_20string

log_archive_dest_21string

log_archive_dest_22string

log_archive_dest_23string

log_archive_dest_24string

log_archive_dest_25string

log_archive_dest_26string

log_archive_dest_27string

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_28string

log_archive_dest_29string

log_archive_dest_3string

log_archive_dest_30string

log_archive_dest_31string

log_archive_dest_4string

log_archive_dest_5string

log_archive_dest_6string

log_archive_dest_7string

log_archive_dest_8string

log_archive_dest_9string

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_state_1stringenable

log_archive_dest_state_10stringenable

log_archive_dest_state_11stringenable

log_archive_dest_state_12stringenable

log_archive_dest_state_13stringenable

log_archive_dest_state_14stringenable

log_archive_dest_state_15stringenable

log_archive_dest_state_16stringenable

log_archive_dest_state_17stringenable

log_archive_dest_state_18stringenable

log_archive_dest_state_19stringenable

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_state_2stringenable

log_archive_dest_state_20stringenable

log_archive_dest_state_21stringenable

log_archive_dest_state_22stringenable

log_archive_dest_state_23stringenable

log_archive_dest_state_24stringenable

log_archive_dest_state_25stringenable

log_archive_dest_state_26stringenable

log_archive_dest_state_27stringenable

log_archive_dest_state_28stringenable

log_archive_dest_state_29stringenable

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_state_3stringenable

log_archive_dest_state_30stringenable

log_archive_dest_state_31stringenable

log_archive_dest_state_4stringenable

log_archive_dest_state_5stringenable

log_archive_dest_state_6stringenable

log_archive_dest_state_7stringenable

log_archive_dest_state_8stringenable

log_archive_dest_state_9stringenable

log_archive_duplex_deststring

log_archive_formatstring%t_%s_%r.dbf

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_local_firstbooleanTRUE

log_archive_max_processesinteger4

log_archive_min_succeed_destinteger1

log_archive_startbooleanFALSE

log_archive_traceinteger0

SYS@ORA11GR2>

2.修改归档路径：

SYS@ORA11GR2>alter system set

log_archive_dest_1='LOCATION=/home/oracle';

System altered.

SYS@ORA11GR2>alter system set

log_archive_dest_10='LOCATION=/u01/app/oracle';

System altered.

SYS@ORA11GR2>alter system set

log_archive_dest_2='LOCATION=/u01/app';

System altered.

SYS@ORA11GR2>alter system set

log_archive_dest_3='LOCATION=/u01/app';

alter system set

log_archive_dest_3='LOCATION=/u01/app'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified

because specified value is invalid

ORA-16033: parameter LOG_ARCHIVE_DEST_3 destination cannot be the same

as parameter

LOG_ARCHIVE_DEST_2 destination

SYS@ORA11GR2>alter system set

log_archive_dest_3='LOCATION=/u01/app/FRA';

System altered.

SYS@ORA11GR2>alter system set

log_archive_min_succeed_dest=2;

System altered.

SYS@ORA11GR2>

3.再次查看归档路径：

SYS@ORA11GR2>archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive destination/u01/app/oracle

Oldest online log sequence13

Next log sequence to archive15

Current log sequence15

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter log_archive

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_configstring

log_archive_deststring

log_archive_dest_1stringLOCATION=/home/oracle

log_archive_dest_10stringLOCATION=/u01/app/oracle

log_archive_dest_11string

log_archive_dest_12string

log_archive_dest_13string

log_archive_dest_14string

log_archive_dest_15string

log_archive_dest_16string

log_archive_dest_17string

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_18string

log_archive_dest_19string

log_archive_dest_2stringLOCATION=/u01/app

log_archive_dest_20string

log_archive_dest_21string

log_archive_dest_22string

log_archive_dest_23string

log_archive_dest_24string

log_archive_dest_25string

log_archive_dest_26string

log_archive_dest_27string

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_28string

log_archive_dest_29string

log_archive_dest_3stringLOCATION=/u01/app/FRA

log_archive_dest_30string

log_archive_dest_31string

log_archive_dest_4string

log_archive_dest_5string

log_archive_dest_6string

log_archive_dest_7string

log_archive_dest_8string

log_archive_dest_9string

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_state_1stringenable

log_archive_dest_state_10stringenable

log_archive_dest_state_11stringenable

log_archive_dest_state_12stringenable

log_archive_dest_state_13stringenable

log_archive_dest_state_14stringenable

log_archive_dest_state_15stringenable

log_archive_dest_state_16stringenable

log_archive_dest_state_17stringenable

log_archive_dest_state_18stringenable

log_archive_dest_state_19stringenable

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_state_2stringenable

log_archive_dest_state_20stringenable

log_archive_dest_state_21stringenable

log_archive_dest_state_22stringenable

log_archive_dest_state_23stringenable

log_archive_dest_state_24stringenable

log_archive_dest_state_25stringenable

log_archive_dest_state_26stringenable

log_archive_dest_state_27stringenable

log_archive_dest_state_28stringenable

log_archive_dest_state_29stringenable

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_dest_state_3stringenable

log_archive_dest_state_30stringenable

log_archive_dest_state_31stringenable

log_archive_dest_state_4stringenable

log_archive_dest_state_5stringenable

log_archive_dest_state_6stringenable

log_archive_dest_state_7stringenable

log_archive_dest_state_8stringenable

log_archive_dest_state_9stringenable

log_archive_duplex_deststring

log_archive_formatstring%t_%s_%r.dbf

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_local_firstbooleanTRUE

log_archive_max_processesinteger4

log_archive_min_succeed_destinteger2

log_archive_startbooleanFALSE

log_archive_traceinteger0

SYS@ORA11GR2>

4.更改log_archive_dest值

SYS@ORA11GR2>alter system set

log_archive_dest='/u01/app/oracle/ORA11GR2';

alter system set

log_archive_dest='/u01/app/oracle/ORA11GR2'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified

because specified value is invalid

ORA-16018: cannot use LOG_ARCHIVE_DEST withLOG_ARCHIVE_DEST_nor

DB_RECOVERY_FILE_DEST

log_archive_dest和log_archive_dest_n这个参数不能同时设置。

SYS@ORA11GR2>

5.解决：

SYS@ORA11GR2>alter system set

log_archive_dest_3='';

System altered.

SYS@ORA11GR2>alter system set log_archive_dest_2='';

System altered.

SYS@ORA11GR2>alter system set

log_archive_dest_1='';

alter system set log_archive_dest_1=''

ERROR at line 1:

ORA-02097: parameter cannot be modified

because specified value is invalid

ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations

than LOG_ARCHIVE_MIN_SUCCEED_DEST

requires

SYS@ORA11GR2>alter system set log_archive_min_succeed_dest=1;

System altered.

SYS@ORA11GR2>alter system set

log_archive_dest_10='';

System altered.

SYS@ORA11GR2>alter system set log_archive_dest_1='';

System altered.

SYS@ORA11GR2>

6.再次查看归档路径及快速恢复区：

SYS@ORA11GR2>archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive

destination?/dbs/arch

Oldest online log sequence13

Next log sequence to archive15

Current log sequence15

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter recover

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

db_recovery_file_deststring/u01/app/FRA

db_recovery_file_dest_sizebig integer 3G

db_unrecoverable_scn_trackingbooleanTRUE

recovery_parallelisminteger0

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter log_archive

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

log_archive_configstring

log_archive_deststring

log_archive_dest_1string

log_archive_dest_10string

log_archive_dest_11string

SYS@ORA11GR2>alter system set

log_archive_dest='/u01/app/FRA';

alter system set

log_archive_dest='/u01/app/FRA'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified

because specified value is invalid

ORA-16018:

cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST

7.解决：

SYS@ORA11GR2>show parameter recover

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

db_recovery_file_deststring/u01/app/FRA/

db_recovery_file_dest_sizebig integer 3G

db_unrecoverable_scn_trackingbooleanTRUE

recovery_parallelisminteger0

SYS@ORA11GR2>alter system

set db_recovery_file_dest='';

System altered.

SYS@ORA11GR2>show parameter recover

NAMETYPEVALUE

------------------------------------ -----------

------------------------------

db_recovery_file_deststring

db_recovery_file_dest_sizebig integer3G

db_unrecoverable_scn_trackingbooleanTRUE

recovery_parallelisminteger0

SYS@ORA11GR2>

SYS@ORA11GR2>archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive destination/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence13

Next log sequence to archive15

Current log sequence15

SYS@ORA11GR2>

再次设置快速恢复区路径：

SYS@ORA11GR2>alter system set

db_recovery_file_dest='/u01/app/FRA';

System altered.

SYS@ORA11GR2>archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence13

Next log sequence to archive15

Current log sequence15

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter recover

NAMETYPEVALUE

------------------------------------

----------- ------------------------------

db_recovery_file_deststring/u01/app/FRA

db_recovery_file_dest_sizebig integer3G

db_unrecoverable_scn_trackingbooleanTRUE

recovery_parallelisminteger0

SYS@ORA11GR2>

SYS@ORA11GR2>alter

system set log_archive_dest='/u01/app/FRA';

alter

system set log_archive_dest='/u01/app/FRA'

*

ERROR

at line 1:

ORA-02097:

parameter cannot be modified because specified value is invalid

ORA-16018: cannot use

LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST

结论：

1.指定了db_recovery_file_dest快速恢复区作为归档路径，则不能使用LOG_ARCHIVE_DEST，不兼容；

而db_recovery_file_dest和log_archive_dest_n兼容，且未指定log_archive_dest_n参数时归档默认指向db_recovery_file_dest快速恢复区，而当log_archive_dest_n参数设定值时则归档目录指定为log_archive_dest_n参数所指向的目录。

2.log_archive_dest参数：使用log_archive_dest参数最多可设置2个归档路径，通过log_archive_dest设置一个主归档路径，通过LOG_ARCHIVE_DUPLEX_DEST参数设置一个从归档路径。所有的路径必须是本地的

3.LOG_ARCHIVE_DEST_n,LOG_ARCHIVE_DEST_n参数可以设置最多10个不同的归档路径，通过设置关键词location或service，该参数指向的路径可以是本地或远程的。

4.可见log_archive_dest和LOG_ARCHIVE_DEST_n这两个参数都可以设置归档路径，不同的是后者可以设置远程归档到standby端，而前者只能归档到本地，且最多同时归档到2个路径下