oracle歸檔路徑剖析及ORA-02097、ORA-16018的解決辦法
在預設情況下，資料庫日誌模式切換到歸檔模式後，歸檔日誌的儲存路徑並沒有配置，oracle預設使用一個“快速恢復區”的儲存位置,可以透過archive log list檢視
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 18
Current log sequence 18
首先，oracle可以透過下列一組初始化引數指定兩個歸檔路徑
log_archive_dest
log_archive_duplex_dest
例如：
sql>alter system set log_archive_dest='/u01/arch';
sql>alter system set log_archive_duplex_dest='/u01/arch2';
除此之外，oracle還提供了另外一組初始化引數設定log_archive_dest_n(n=1~31)，資料庫歸檔時，可以最多產生31個相同的歸檔日誌檔案，保證這些歸檔日誌檔案的安全，不同的是，這組引數與上組不能同時使用，log_archive_dest_n指定的儲存位置既可以是本地目錄，也可以是一個ASM磁碟組，還可以是另一個資料庫伺服器，本地目錄的格式為"location=本地目錄",ASM磁碟組的指定格式為location=+磁碟組名稱",例如location=+DATA(DATA是ASM磁碟組的名稱），另外一個資料庫伺服器的指定格式為"service="服務名稱"例如：
log_archive_dest_1="location=/u01/arch"
log_archive_dest_2="location=/u01/arch2"
log_archive_dest_3="service=backup"
另外oracle提供31個對應的初始化引數log_archive_dest_state_n 用於分別指定31個歸檔路徑的狀態，如果這些引數的值為enable，表明管理員希望資料庫使用其位置，如果引數值為defer，則表明暫時不使用對應的歸檔位置。歸檔日誌檔案的名字不能重複，在命名中必須同時使用三個變數，即%S,%T,%R（%S代表重做日誌組的sqluence的編號，總共10位，從左邊補零；%T，代表重做日誌組的thread編號，總共4位，%R代表資料庫最近一次以resetlogs方式開啟的時間戳，總共10位，左邊補0,如果是小寫則不補0）
例如：
sql>alter system set log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
下面是更改log_archive_dest時報ORA-02097 ORA-16018錯誤的過程及解決辦法：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 18
Current log sequence 18
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SQL> alter system set log_archive_dest="/u01oracle/arch";
alter system set log_archive_dest="/u01oracle/arch"
*
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
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/app/oracle/product
/10.2.0/dbhome_1/dbs/spfilepro
d.ora
SQL> create pfile='/u01/init.ora' from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@XXXX u01]$ vi /u01/init.ora
[oracle@XXXX u01]$ cat /u01/init.ora
prod.__db_cache_size=188743680
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=79691776
prod.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/app/oracle/admin/prod/adump'
*.background_dump_dest='/u01/oracle/app/oracle/admin/prod/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/app/oracle/oradata/prod/control01.ctl','/u01/oracle/app/oracle/oradata/prod/control02.ctl','/u01/oracle/app/oracle/oradata/prod/control03.ctl'
*.core_dump_dest='/u01/oracle/app/oracle/admin/prod/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
#*.db_recovery_file_dest='/u01/oracle/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=279969792
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/app/oracle/admin/prod/udump'
[oracle@XXXX u01]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 25 08:19:16 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/init.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020192 bytes
Variable Size 88083616 bytes
Database Buffers 188743680 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
SQL> alter system set log_archive_dest='/u01/arch';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 14
Next log sequence to archive 18
Current log sequence 18
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string /u01/arch
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 18
Current log sequence 18
首先，oracle可以透過下列一組初始化引數指定兩個歸檔路徑
log_archive_dest
log_archive_duplex_dest
例如：
sql>alter system set log_archive_dest='/u01/arch';
sql>alter system set log_archive_duplex_dest='/u01/arch2';
除此之外，oracle還提供了另外一組初始化引數設定log_archive_dest_n(n=1~31)，資料庫歸檔時，可以最多產生31個相同的歸檔日誌檔案，保證這些歸檔日誌檔案的安全，不同的是，這組引數與上組不能同時使用，log_archive_dest_n指定的儲存位置既可以是本地目錄，也可以是一個ASM磁碟組，還可以是另一個資料庫伺服器，本地目錄的格式為"location=本地目錄",ASM磁碟組的指定格式為location=+磁碟組名稱",例如location=+DATA(DATA是ASM磁碟組的名稱），另外一個資料庫伺服器的指定格式為"service="服務名稱"例如：
log_archive_dest_1="location=/u01/arch"
log_archive_dest_2="location=/u01/arch2"
log_archive_dest_3="service=backup"
另外oracle提供31個對應的初始化引數log_archive_dest_state_n 用於分別指定31個歸檔路徑的狀態，如果這些引數的值為enable，表明管理員希望資料庫使用其位置，如果引數值為defer，則表明暫時不使用對應的歸檔位置。歸檔日誌檔案的名字不能重複，在命名中必須同時使用三個變數，即%S,%T,%R（%S代表重做日誌組的sqluence的編號，總共10位，從左邊補零；%T，代表重做日誌組的thread編號，總共4位，%R代表資料庫最近一次以resetlogs方式開啟的時間戳，總共10位，左邊補0,如果是小寫則不補0）
例如：
sql>alter system set log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
下面是更改log_archive_dest時報ORA-02097 ORA-16018錯誤的過程及解決辦法：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 18
Current log sequence 18
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SQL> alter system set log_archive_dest="/u01oracle/arch";
alter system set log_archive_dest="/u01oracle/arch"
*
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
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/app/oracle/product
/10.2.0/dbhome_1/dbs/spfilepro
d.ora
SQL> create pfile='/u01/init.ora' from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@XXXX u01]$ vi /u01/init.ora
[oracle@XXXX u01]$ cat /u01/init.ora
prod.__db_cache_size=188743680
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=79691776
prod.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/app/oracle/admin/prod/adump'
*.background_dump_dest='/u01/oracle/app/oracle/admin/prod/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/app/oracle/oradata/prod/control01.ctl','/u01/oracle/app/oracle/oradata/prod/control02.ctl','/u01/oracle/app/oracle/oradata/prod/control03.ctl'
*.core_dump_dest='/u01/oracle/app/oracle/admin/prod/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
#*.db_recovery_file_dest='/u01/oracle/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=279969792
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/app/oracle/admin/prod/udump'
[oracle@XXXX u01]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 25 08:19:16 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/init.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020192 bytes
Variable Size 88083616 bytes
Database Buffers 188743680 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
SQL> alter system set log_archive_dest='/u01/arch';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 14
Next log sequence to archive 18
Current log sequence 18
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string /u01/arch
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29618264/viewspace-2139879/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 更改ORACLE歸檔路徑及歸檔模式Oracle模式
- XML檔案中url路徑中&失效解決辦法XML
- Oracle資料庫頻繁歸檔問題的解決辦法Oracle資料庫
- rac中文名檔名新增到本地路徑解決辦法(ORA-01157)-非歸檔
- 更改oracle10g的歸檔模式和歸檔路徑Oracle模式
- Oracle表碎片起因及解決辦法Oracle
- oracle單機改變歸檔路徑Oracle
- oracle資料庫更改歸檔路徑Oracle資料庫
- [Archive]更改ORACLE預設歸檔路徑HiveOracle
- Oracle statspack無法收集快照，及解決辦法Oracle
- Oracle資料庫的歸檔日誌寫滿磁碟空間解決辦法Oracle資料庫
- 非歸檔資料檔案誤刪除解決辦法
- oracle壞塊問題及解決辦法Oracle
- oracle歸檔日誌儲存路徑的設定Oracle
- chrome上傳圖片 路徑為c:/fakepath的解決辦法Chrome
- 歸檔日誌多歸檔路徑 duplex
- Inet控制元件中上載的路徑、檔名中出現空格的解決辦法 (轉)控制元件
- dataguard歸檔路徑的問題
- 改變歸檔檔案路徑
- Oracle資料庫的閃回恢復區及多歸檔路徑的設定Oracle資料庫
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- 修改歸檔日誌路徑
- 更改archive log 歸檔路徑和歸檔檔名稱Hive
- 修改db2的歸檔路徑DB2
- 【DataBase】：使用Oracle遇到的幾個問題及解決辦法DatabaseOracle
- 檔案無法粉碎解決辦法
- 丟失Oracle中資料檔案Ora時的解決辦法Oracle
- oracle imp過慢的解決辦法Oracle
- 表碎片起因及解決辦法
- Oracle ORA-27101錯誤及解決辦法Oracle
- 微信域名被封的原因及解決辦法
- 【archive_dest】歸檔的路徑問題Hive
- 未能找到路徑“in oslyncsc.exe”的一部分 的解決辦法
- jvm：jmap無法dump檔案的解決辦法JVM
- 登錄檔無法開啟的解決辦法
- 關於Android程式設計檔案路徑中含有中文字元的問題解決辦法Android程式設計字元
- 什麼是網路擁塞及解決辦法簡介
- 表碎片起因及解決辦法(zt)