ITPub博客

首页 > 数据库 > Oracle > [20211018]奇怪的归档目的地.txt

[20211018]奇怪的归档目的地.txt

原创 Oracle 作者:lfree 时间:2021-10-19 08:58:23 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3135
  • 访问量
    6840130