[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2838001/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
