[20211018]奇怪的歸檔目的地.txt

lfree 發表於 2021-10-19

[20211018]奇怪的歸檔目的地.txt

--//生產系統遇到一個奇怪的問題,因為磁碟滿了請求維護。

1.環境:
[email protected]: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

[email protected]: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.

[email protected]: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)
--//這個時間是當時重啟沒有多久就出現。

--//檢查發現:
[email protected]: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會報引數不一致,增加維護管理的麻煩。

[email protected]: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觀察:
[email protected]: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:
[email protected]: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。

[email protected]: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的相關引數,也許就不用走這樣的彎路了,浪費許多時間。

[email protected]: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/,如需轉載,請註明出處,否則將追究法律責任。