standby上增加tempfile报错ORA-00604,ORA-16000怎么办

这篇文章主要介绍standby上增加tempfile报错ORA-00604,ORA-16000怎么办，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！在DG中对primary增加tempfile不会同步到standby上，所以增加tempfile需要在standby上操作，但这次在standby上增加tempfile却报错ORA-00604 ORA-16000 (mount状态，MRP cancel报错依旧)。分析：这里ORA-00604通常是trigger在影响，因为时间紧迫没有做10046 trace，直接查询官方得到权威解释。原来是因为DB安装了OGG其中trigger GGS_DDL_TRIGGER_BEFORE（trigger主要作用是记录DDL操作）影响到增加tempfile操作。解决：在primary中disable triggerGGS_DDL_TRIGGER_BEFORE 后，再standby增加tempfile成功，之后再对primary中trigger enable官方文章：11g Standby Unable To Add Temp File (文档 ID 2168646.1)Oracle Database – Enterprise Edition – Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Oracle Database – Enterprise Edition – Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
This occurs in the PHYSICAL standby environment.

Alter tablespace saptmp add tempfile ‘+DATA’
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 1147
ORA-16000: database open for read-only accessSQL> select name, database_role, open_mode from v$database;NAME DATABASE_ROLE OPEN_MODE
——— —————- ——————–
TPS PHYSICAL STANDBY READ ONLYDatabase DDL trigger added in the Primary.This is due to the recent addition of the database DDL trigger added in the Primary. The changes are ransported to the Standby environment.
When trying to add temporary tablespace(Which is a DDL) in the Standby in READ ONLY mode,it will encounter misleading error message ORA-604 and ORA-16000First step get the output from the following SQL in the customer environment,make sure to compare the tempfiles on both PRIMARY and STANDBY environment.

(Note : In rare cases, there had been error messages reported but still the tempfilewas added at the database level).spool standby.out
set lines 200select name, database_role, open_mode from v$database;
select FILE_NAME,STATUS, round(BYTES/1048576) from dba_temp_files;
REM select dbms_metadata.get_ddl(‘TABLESPACE’, ‘SAPTMP’) from dual;select * from dba_triggers;spool offspool primary.out
set lines 200
select name, database_ro免费主机域名le, open_mode from v$database;
select FILE_NAME,STATUS, round(BYTES/1048576) from dba_temp_files;
REM select dbms_metadata.get_ddl(‘TABLESPACE’, ‘SAPTMP’) from dual;
select * from dba_triggers;
spool offSample outputselect name, database_role, open_mode from v$database;NAME DATABASE_ROLE OPEN_MODE
——— —————- ——————–
TP100 PRIMARY READ WRITESQL> select FILE_NAME,STATUS, round(BYTES/1048576) from dba_temp_files;
FILE_NAME STATUS ROUND(BYTES/1048576)
———————————– ————— ——————–
+DATA/tp100/tempfile/psaptemp.338.797514523 ONLINE 30720
+DATA/tp100/tempfile/psaptemp.12326.846224883 ONLINE 30720
+DATA/tp100/tempfile/psaptemp.15382.871981083 ONLINE 30720select dbms_metadata.get_ddl(‘TABLESPACE’, ‘SAPTMP’) from dual;DBMS_METADATA.GET_DDL(‘TABLESPACE’,’SAPTMP’)
—————————-免费主机域名——————————————–
CREATE TEMPORARY TABLESPACE “SAPTMP” TEMPFILE SIZE 32212254720 AUTOEXTDBA_TRIGGERS view will list the details of the trigger, for example
Trigger Type : DDL
Owner : sys
name : GGS_DDL_TRIGGER_BEFORE
status : Enabled
Before ddl on database trigger
After identifying the suspect trigger, go ahead and disable the trigger in the PRIMARYExample : ALTER TRIGGER sys.GGS_DDL_TRIGGER_BEFORE DISABLE;Wait for the changes to be propagated in the STANDBY environment. Now you wouldbe able to add the tempfile in the STANDBY using the “alter tablespace” SQL.Despite above approach, if the error persists then follow the Troubleshooting section for further analysis.Connect as sysdba

alter session set tracefile_identifier=’add_tempfile’;
alter session set events ‘10046 trace name context forever,level 12’
alter session set events ‘604 trace name ERRORSTACK level 3’;
alter session set events ‘16000 trace name errorstack level 3’;
— (Run the Alter tablespace command here)
— alter tablespace psaptemp add tempfile ‘+DATA’;
— Make sure to exit session
exit;Identify all the tracefiles in the trace directory.
ls -al *add_tempfile*Identify the 10046 trace and run tkprof on that tracefileCollect the following from customer for review.a. Upload all the raw trace files along with tkprof output file.b. Also upload the Alert.log with the error message.c. Output fromspool tempfile_info.out
select name, database_role, open_mode from v$database;
select file#,ts#, name,status,round(bytes/1048576),con_id from v$tempfile;
select FILE_NAME,STATUS, round(BYTES/1048576) from dba_temp_files;
REM REM select dbms_metadata.get_ddl(‘TABLESPACE’, ‘SAPTMP’) from dual;
select * from v#tempfile;
select * from dba_temp_files;
spool off以上是“standby上增加tempfile报错ORA-00604,ORA-16000怎么办”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注云技术行业资讯频道！

相关推荐: NOT IN之后的子查询为什么不能包含NULL值

本篇内容主要讲解“NOT IN之后的子查询为什么不能包含NULL值”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“NOT IN之后的子查询为什么不能包含NULL值”吧!如果空值可能是子查询结果的一部分，则不应与主查询…