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

来源：这里教程网时间：2026-03-03 17:04:41 作者：

[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;

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推