[20211018]奇怪的歸檔目的地.txt
[20211018]奇怪的歸檔目的地.txt
--//生產系統遇到一個奇怪的問題，因為磁碟滿了請求維護。
1.環境：
SYS@192.168.aaa.bbb:1521/xxxyyydg2> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SYS@192.168.aaa.bbb:1521/xxxyyydg2> @ dg/dg_dest
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR
------- -------------------- --------- ---------- --------------- ----------------------- -------------------- ---------------------------------------- ---------------- ------------- --------------- ------------ ------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyydg2 1 16864 0 0
2 LOG_ARCHIVE_DEST_2 VALID UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE xxxyyy 0 0 0 0
32 STANDBY_ARCHIVE_DEST VALID UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyydg2 1 16864 1 16864
DEST_ID DESTINATION STATUS ERROR TRANSMIT_MOD
------- ---------------------------------------- --------- ------------------------------ ------------
1 /u01/app/oracle/archivelog/xxxyyydg2 VALID SYNCHRONOUS
2 xxxyyy VALID ASYNCHRONOUS
3 USE_DB_RECOVERY_FILE_DEST VALID SYNCHRONOUS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
32 /u01/app/oracle/archivelog/xxxyyydg2 VALID SYNCHRONOUS
--//奇怪怎麼出現一個DEST_ID＝32的歸檔目的地,根本不存在log_archive_dest_32這個引數。
--//而且還多了一個DEST_ID＝3，DESTINATION=USE_DB_RECOVERY_FILE_DEST.
SYS@192.168.aaa.bbb:1521/xxxyyydg2> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ ---------- ----------------------------------------------------------------------------------------------------
log_archive_dest_3 string location="USE_DB_RECOVERY_FILE_DEST", valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)
log_archive_dest_30 string
log_archive_dest_31 string
--//發現多了一個log_archive_dest_3目的地，我前面維護時發現磁碟滿我已經取消了，我還生成了pfile，然後轉化為spfile檔案。
--//檢查alert日誌發現，又寫會回來的，什麼回事。難道其它監控軟體發現不存在自動寫入嗎？
--//alert.*:
Fri Oct 15 17:25:00 2021
Archived Log entry 923 added for thread 1 sequence 16848 ID 0xf090956 dest 1:
ALTER SYSTEM SET log_archive_dest_3='location="USE_DB_RECOVERY_FILE_DEST"',' valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='xxxyyydg2';
ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH SID='xxxyyydg2';
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='xxxyyydg2';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='xxxyyydg2';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (xxxyyydg2)
Fri Oct 15 17:25:01 2021
MRP0 started with pid=32, OS id=5908
MRP0: Background Managed Standby Recovery process started (xxxyyydg2)
--//這個時間是當時重啟沒有多久就出現。
--//檢查發現:
SYS@192.168.aaa.bbb:1521/xxxyyydg2> show parameter standby
NAME TYPE VALUE
------------------------------------ ---------- -----------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
--//standby_file_management=MANUAL.真心不知道我同事什麼安裝的，應該按照文件一步一步實施，這樣後手維護真不是人乾的事情。
DGMGRL> edit database xxxyyydg2 set PROPERTY StandbyFileManagement='AUTO';
Property "standbyfilemanagement" updated
--//注我已經使用DGMGRL管理，必須使用該軟體修改一些與dg相關引數，不然DGMGRL會報引數不一致，增加維護管理的麻煩。
SYS@192.168.aaa.bbb:1521/xxxyyydg2> @ dg/dg_dest
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR
------- -------------------- --------- ---------- --------------- ----------------------- -------------------- ---------------------------------------- ---------------- ------------- --------------- ------------ ------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyydg2 1 16864 0 0
2 LOG_ARCHIVE_DEST_2 VALID UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE xxxyyy 0 0 0 0
32 STANDBY_ARCHIVE_DEST VALID UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyydg2 1 16864 1 16864
DEST_ID DESTINATION STATUS ERROR TRANSMIT_MOD
------- ---------------------------------------- --------- ------------------------------ ------------
1 /u01/app/oracle/archivelog/xxxyyydg2 VALID SYNCHRONOUS
2 xxxyyy VALID ASYNCHRONOUS
32 /u01/app/oracle/archivelog/xxxyyydg2 VALID SYNCHRONOUS
--//檢查alert.*發現：
$ tail -f alert_xxxyyydg2.log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 16865 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/xxxyyy/std_redo04.log
Mon Oct 18 09:14:36 2021
Time drift detected. Please check VKTM trace file for more details.
Mon Oct 18 09:16:57 2021
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
Mon Oct 18 09:19:21 2021
NSV1 started with pid=48, OS id=12674
Mon Oct 18 09:25:58 2021
ALTER SYSTEM SET log_archive_dest_3='' SCOPE=BOTH SID='xxxyyydg2';
--//可以發現我一旦修改standby_file_management='AUTO'，log_archive_dest_3=''自動取消。
--//重啟dg觀察：
SYS@192.168.aaa.bbb:1521/xxxyyydg2> @ dg/dg_dest
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR
------- -------------------- --------- ---------- --------------- ----------------------- -------------------- ---------------------------------------- ---------------- ------------- --------------- ------------ ------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyydg2 1 16865 0 0
2 LOG_ARCHIVE_DEST_2 VALID UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE xxxyyy 0 0 0 0
32 STANDBY_ARCHIVE_DEST VALID UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyydg2 1 16865 1 16865
DEST_ID DESTINATION STATUS ERROR TRANSMIT_MOD
------- ---------------------------------------- --------- ------------------------------ ------------
1 /u01/app/oracle/archivelog/xxxyyydg2 VALID SYNCHRONOUS
2 xxxyyy VALID ASYNCHRONOUS
32 /u01/app/oracle/archivelog/xxxyyydg2 VALID SYNCHRONOUS
--//我看了另外一個dg：
SYS@192.168.31.7:1521/xxxyyydg> @ dg/dg_dest
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR
------- -------------------- --------- ---------- --------------- ----------------------- -------------------- ---------------------------------------- ---------------- ------------- --------------- ------------ ------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyy 1 16865 0 0
2 LOG_ARCHIVE_DEST_2 DEFERRED UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE xxxyyy 0 0 0 0
32 STANDBY_ARCHIVE_DEST VALID UNKNOWN UNKNOWN IDLE MAXIMUM PERFORMANCE /u01/app/oracle/archivelog/xxxyyy 1 16865 1 16865
DEST_ID DESTINATION STATUS ERROR TRANSMIT_MOD
------- ---------------------------------------- --------- ------------------------------ ------------
1 /u01/app/oracle/archivelog/xxxyyy VALID SYNCHRONOUS
2 xxxyyy DEFERRED ASYNCHRONOUS
32 /u01/app/oracle/archivelog/xxxyyy VALID SYNCHRONOUS
--//看來出現DEST_ID＝32是正常的,我有點過濾了。只不過不應該使用USE_DB_RECOVERY_FILE_DEST。
SYS@192.168.aaa.bbb:1521/xxxyyydg2> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ ---------- ---------------------------------------
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
--//現在應該正常了。
--//如果當時我使用我自己寫的檢查設定dg的相關引數，也許就不用走這樣的彎路了，浪費許多時間。
SYS@192.168.aaa.bbb:1521/xxxyyydg2> @ dg/dg_check
NAME TYPE VALUE SES_MOD SYS_MOD INS_MOD
------------------------- ---- ------------------------------------------------------------------------------------------------------------------------ ---------- ---------- -------
db_file_name_convert 2 /u01/app/oracle/oradata/xxxyyy, /u01/app/oracle/oradata/xxxyyy TRUE FALSE FALSE
db_name 2 xxxyyy FALSE FALSE FALSE
db_unique_name 2 xxxyyydg2 FALSE FALSE FALSE
fal_client 2 xxxyyydg2 FALSE IMMEDIATE TRUE
fal_server 2 xxxyyy, xxxyyydg FALSE IMMEDIATE TRUE
log_archive_config 2 dg_config=(xxxyyydg2,xxxyyy,xxxyyydg) FALSE IMMEDIATE TRUE
log_archive_dest_1 2 LOCATION=/u01/app/oracle/archivelog/xxxyyydg2 MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=xxxyyydg2 TRUE IMMEDIATE TRUE
log_archive_dest_2 2 SERVICE=xxxyyy LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=xxxyyy TRUE IMMEDIATE TRUE
log_archive_dest_3 2 TRUE IMMEDIATE TRUE
log_archive_dest_state_2 2 ENABLE TRUE IMMEDIATE TRUE
log_archive_dest_state_3 2 enable TRUE IMMEDIATE TRUE
log_archive_format 2 %t_%s_%r.dbf FALSE FALSE FALSE
log_file_name_convert 2 /u01/app/oracle/oradata/xxxyyy, /u01/app/oracle/oradata/xxxyyy FALSE FALSE FALSE
remote_login_passwordfile 2 EXCLUSIVE FALSE FALSE FALSE
standby_file_management 2 AUTO FALSE IMMEDIATE TRUE
15 rows selected.
--//附上指令碼：
$ cat dg/dg_dest.sql
column DESTINATION format a40
column DEST_NAME format a20
column ERROR format a30
column TYPE format a10
column DEST_ID format 999
SELECT DEST_ID, DEST_NAME, STATUS, TYPE, DATABASE_MODE, RECOVERY_MODE, PROTECTION_MODE, DESTINATION,
ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#, ERROR
FROM v$archive_dest_status
WHERE DESTINATION is not null;
--select dest_id,dest_name,status,database_mode, error from v$archive_dest_status where dest_id<=5;
select dest_id, destination, status, error, transmit_mode from v$archive_dest where destination is not null;
$ cat dg/dg_check.sql
COL name FOR a30
COL value FOR a120
COL ses_mod FOR a10
COL sys_mod FOR a10
COL ins_mod FOR a10
COL type FORMAT 99999
SELECT p.name
,p.TYPE
,p.VALUE
,p.isses_modifiable AS SES_MOD
,p.issys_modifiable AS SYS_MOD
,p.isinstance_modifiable AS INS_MOD
FROM v$parameter p
WHERE 1 = 1
AND name IN ('remote_login_passwordfile'
,'standby_file_management'
,'log_archive_dest_1'
,'log_archive_dest_state_2'
,'log_archive_dest_2'
,'log_archive_dest_state_3'
,'log_archive_dest_3'
,'log_archive_config'
,'db_file_name_convert'
,'log_file_name_convert'
,'db_name'
,'db_unique_name'
,'log_archive_format'
,'remote_login_passwordfile'
,'fal_server'
,'fal_client'
,'log_archive_config')
ORDER BY name;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2838001/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Backup And Recovery User's Guide-覆蓋歸檔日誌目的地GUIIDE
- RAC環境下節點歸檔目的地相互獨立的配置和備份要求
- [20211018]運維中關於history的問題.txt運維
- [20170217]歸檔收集器.txt
- [20230905]奇怪的語法.txt
- [20150529]使用bbed解決丟失的歸檔.txt
- [20161129]奇怪的轉儲命令.txt
- [20141013]奇怪的sql語句.txtSQL
- oracle 歸檔/非歸檔Oracle
- Oracle 歸檔與非歸檔模式的更改Oracle模式
- [20171208]強制刪除歸檔日誌檔案.txt
- [20190306]奇怪的查詢結果.txt
- [20220822]奇怪的ashtop輸出.txt
- [20221103]奇怪的mail資訊(整理版本).txtAI
- iOS 複雜物件的歸檔與反歸檔iOS物件
- 歸檔模式與非歸檔模式的切換模式
- Oracle歸檔模式和非歸檔模式的區別Oracle模式
- [20211210]優化遇到的奇怪問題.txt優化
- 歸檔
- [20141208]使用logminer看遠端歸檔檔案.txt
- oracle的歸檔模式Oracle模式
- Oracle 歸檔和非歸檔模式之間的切換Oracle模式
- 刪除歸檔，保留最近的5個歸檔日誌
- oracle 8i的歸檔與不歸檔切換Oracle
- Oracle歸檔模式和非歸檔模式Oracle模式
- 非歸檔模式改為歸檔模式模式
- [20170309]關於線上日誌與歸檔1.txt
- [20170309]關於線上日誌與歸檔2.txt
- [20170310]關於線上日誌與歸檔3.txt
- [20170310]關於線上日誌與歸檔4.txt
- [20231012]奇怪的執行時長.txt
- [20211111]奇怪的ashtop輸出.txt
- [20131121]奇怪的執行計劃變化.txt
- ORACLE資料檔名導致的奇怪問題Oracle
- 更改oracle10g的歸檔模式和歸檔路徑Oracle模式
- 歸檔日誌多歸檔路徑 duplex
- 更改ORACLE歸檔路徑及歸檔模式Oracle模式
- 改變歸檔模式，顯示歸檔資訊模式