這篇文章主要講解了“Oracle歸檔日志管理的方法有哪些”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“Oracle歸檔日志管理的方法有哪些”吧！
創(chuàng)新互聯(lián)建站主要從事成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)桓臺(tái),10多年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來(lái)電咨詢建站服務(wù):13518219792一般情況下，數(shù)據(jù)庫(kù)安裝時(shí)選擇的是默認(rèn)NOARCHIVELOG模式。如果要開(kāi)啟ARCHIVELOG模式，則需要為歸檔日志路徑設(shè)置初始化參數(shù)。
歸檔日志路徑可以配置一個(gè)也可以配置多個(gè)。路徑可以是本地路徑，如本地文件系統(tǒng)，ASM磁盤(pán)組；也可以是遠(yuǎn)程路徑，如備份數(shù)據(jù)庫(kù)。設(shè)置多個(gè)歸檔日志路徑，可以對(duì)歸檔日志做冗余，即使有一個(gè)目標(biāo)盤(pán)損壞，也可以保證歸檔日志是可用的。
在配置參數(shù)時(shí)，歸檔日志的路徑可以動(dòng)態(tài)修改，在下一次日志切換的時(shí)候生效。
這里給出3種常用的歸檔路徑的設(shè)置方案：?jiǎn)我粴w檔路徑方案、兩路歸檔路徑方案和多路歸檔路徑的方案。
如果只設(shè)置單一的歸檔目錄，默認(rèn)使用db_recovery_file_dest參數(shù)，使用Fast Recovery Area存放歸檔數(shù)據(jù)；也可手工配置log_archive_dest初始化參數(shù)，可以配置為本地文件系統(tǒng)，ASM磁盤(pán)組。使用log_archive_dest_n參數(shù)的方法在第三種方案進(jìn)行討論。
具體操作如下：
--如果使用fast recovery area存放歸檔日志，需要注意該區(qū)域有大小限制，建議將大小調(diào)大 SYS@cams> 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 SYS@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_ area db_recovery_file_dest_size big integer 10G
--如果使用本地路徑存放歸檔日志，需要將db_recovery_file_dest參數(shù)置空，然后設(shè)置log_archive_dest參數(shù)。這里給出的是本地文件系統(tǒng)例子，讀者可自行測(cè)試ASM磁盤(pán)組。 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.
需要將LOG_ARCHIVE_DEST_n和DB_RECOVERY_FILE_DEST參數(shù)置空，以免出現(xiàn)如下問(wèn)題：
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 invalid ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST
此時(shí)，可以看到trace日志的變化，LOG_ARCHIVE_DEST_1的默認(rèn)值自動(dòng)調(diào)整過(guò)程。其中，在使用Fast Recovery Area歸檔的情況下，LOG_ARCHIVE_DEST_1的默認(rèn)值為USE_DB_RECOVERY_FILE_DEST。
Tue Dec 18 17:48:24 2018 ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH; Tue Dec 18 17:52:13 2018 Cleared LOG_ARCHIVE_DEST_1 parameter default value Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0/db_1/dbs/arch ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=BOTH; Tue Dec 18 17:52:28 2018 ALTER SYSTEM SET log_archive_dest='/u01/app/oracle/archive' SCOPE=BOTH;
--首先創(chuàng)建新的歸檔路徑 [oracle@XLJ181 trace]$ mkdir -p /u01/app/oracle/archive
--此時(shí)對(duì)日志進(jìn)行切換，觸發(fā)寫(xiě)歸檔日志 SYS@cams> alter system switch logfile; System altered.
--查看新生成的歸檔日志 [oracle@XLJ181 trace]$ ll /u01/app/oracle/archive total 11960 -rw-r----- 1 oracle oinstall 12244992 Dec 18 17:57 1_25_994950965.dbf
trace日志中也可以看到LGWR switch操作
Tue Dec 18 17:57:10 2018 Thread 1 cannot allocate new log, sequence 26 Private strand flush not complete Current log# 1 seq# 25 mem# 0: /u01/app/oracle/oradata/cams/redo01.log Thread 1 advanced to log sequence 26 (LGWR switch) Current log# 2 seq# 26 mem# 0: /u01/app/oracle/oradata/cams/redo02.log Tue Dec 18 17:57:10 2018 Archived Log entry 20 added for thread 1 sequence 25 ID 0x9e3b45f3 dest 1:
如果要設(shè)置兩路歸檔路徑，常用的方案是配置log_archive_dest和log_archive_duplex_dest參數(shù)。用log_archive_dest_n參數(shù)的方法在第三種方案進(jìn)行討論。
具體操作如下：
--創(chuàng)建新的歸檔路徑 [oracle@XLJ181 trace]$ mkdir -p /u01/app/oracle/archive1
--設(shè)置log_archive_duplex_dest參數(shù)路徑并切換日志觸發(fā)寫(xiě)歸檔日志 SYS@cams> alter system set log_archive_duplex_dest='/u01/app/oracle/archive1'; System altered. SYS@cams> alter system switch logfile; System altered.
--查看新生成的歸檔日志文件，顯然新生成的歸檔大小和文件名一致。兩份歸檔日志互為冗余。 [oracle@XLJ181 trace]$ ll /u01/app/oracle/archive total 15704 -rw-r----- 1 oracle oinstall 12244992 Dec 18 17:57 1_25_994950965.dbf -rw-r----- 1 oracle oinstall 3830272 Dec 18 18:30 1_26_994950965.dbf [oracle@XLJ181 trace]$ ll /u01/app/oracle/archive1 total 3744 -rw-r----- 1 oracle oinstall 3830272 Dec 18 18:30 1_26_994950965.dbf
查看trace日志，可以看到LGWR switch操作，然后分別在兩個(gè)目錄下寫(xiě)歸檔。
Tue Dec 18 18:30:01 2018 Thread 1 cannot allocate new log, sequence 27 Private strand flush not complete Current log# 2 seq# 26 mem# 0: /u01/app/oracle/oradata/cams/redo02.log Thread 1 advanced to log sequence 27 (LGWR switch) Current log# 3 seq# 27 mem# 0: /u01/app/oracle/oradata/cams/redo03.log Tue Dec 18 18:30:02 2018 Archived 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:
如果要設(shè)置多路歸檔路徑，需要使用log_archive_dest_n參數(shù)，其中，n的取值為1到31的整數(shù)，也就是說(shuō)，可以指定1到31個(gè)獨(dú)立的路徑作為歸檔日志的路徑。
log_archive_dest_n參數(shù)的取值可以選擇LOCATION或SERVIDE關(guān)鍵字。如果使用LOCATION關(guān)鍵字，可以選擇本地文件系統(tǒng)、ASM磁盤(pán)組或USE_DB_RECOVERY_FILE_DEST代表的閃回恢復(fù)區(qū)；如果使用SERVICE關(guān)鍵字，可以選擇備庫(kù)的網(wǎng)絡(luò)連接標(biāo)識(shí)符連接備份數(shù)據(jù)庫(kù)。
具體操作如下：
--創(chuàng)建本地歸檔目錄 [oracle@XLJ181 admin]$ mkdir -p /u01/app/oracle/archive2 [oracle@XLJ181 admin]$ mkdir -p /u01/app/oracle/archive3
--配置log_archive_dest_n參數(shù) 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.
需要注意置空的順序，以免出現(xiàn)如下錯(cuò)誤
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 invalid ORA-16022: LOG_ARCHIVE_DEST cannot be NULL because LOG_ARCHIVE_DUPLEX_DEST is non-NULL
需要將LOG_ARCHIVE_DEST和LOG_ARCHIVE_DUPLEX_DEST參數(shù)置空，以免出現(xiàn)如下問(wèn)題：
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 invalid ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
--切換日志，觸發(fā)寫(xiě)歸檔日志 SYS@cams> alter system switch logfile; System altered.
查看新生成的歸檔日志文件，顯然新生成的歸檔大小和文件名一致。兩份歸檔日志互為冗余。
[oracle@XLJ181 admin]$ ll /u01/app/oracle/archive2 total 5440 -rw-r----- 1 oracle oinstall 4416512 Dec 18 19:09 1_27_994950965.dbf -rw-r----- 1 oracle oinstall 1148416 Dec 18 19:23 1_28_994950965.dbf [oracle@XLJ181 admin]$ ll /u01/app/oracle/archive3 total 5440 -rw-r----- 1 oracle oinstall 4416512 Dec 18 19:09 1_27_994950965.dbf -rw-r----- 1 oracle oinstall 1148416 Dec 18 19:23 1_28_994950965.dbf
查看trace日志，看到錯(cuò)誤提示，提示DB_RECOVERY_FILE_DEST未設(shè)置值
Tue Dec 18 19:23:59 2018 Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_arc3_29341.trc: ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not set Errors 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 translated ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not set Archived 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:
為DB_RECOVERY_FILE_DEST參數(shù)配置數(shù)值，切換日志
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.
查看trace日志信息，最后可看到同時(shí)寫(xiě)入了3個(gè)歸檔路徑
Tue Dec 18 19:27:58 2018 ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using 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 2018 db_recovery_file_dest_size of 10240 MB is 5.63% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Tue Dec 18 19:28:01 2018 Thread 1 cannot allocate new log, sequence 30 Private strand flush not complete Current log# 2 seq# 29 mem# 0: /u01/app/oracle/oradata/cams/redo02.log Thread 1 advanced to log sequence 30 (LGWR switch) Current log# 3 seq# 30 mem# 0: /u01/app/oracle/oradata/cams/redo03.log Tue Dec 18 19:28:01 2018 Archived Log entry 27 added for thread 1 sequence 29 ID 0x9e3b45f3 dest 2: Archived Log entry 28 added for thread 1 sequence 29 ID 0x9e3b45f3 dest 3: Tue Dec 18 19:33:38 2018 Thread 1 cannot allocate new log, sequence 31 Private strand flush not complete Current log# 3 seq# 30 mem# 0: /u01/app/oracle/oradata/cams/redo03.log Thread 1 advanced to log sequence 31 (LGWR switch) Current log# 1 seq# 31 mem# 0: /u01/app/oracle/oradata/cams/redo01.log Tue Dec 18 19:33:39 2018 Expanded controlfile section 11 from 28 to 62 records Requested to grow by 34 records; added 2 blocks of records Archived 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:
感謝各位的閱讀，以上就是“Oracle歸檔日志管理的方法有哪些”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)Oracle歸檔日志管理的方法有哪些這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
重慶分公司
重慶分公司
