standby上增加tempfile报错ORA

共计 4477 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章主要介绍 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 trigger GGS_DDL_TRIGGER_BEFORE 后，再 standby 增加 tempfile 成功，之后再对 primary 中 trigger enable

官方文章：

11g Standby Unable To Add Temp File (文档 ID 2168646.1)

APPLIES TO:

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.

SYMPTOMS

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 access

SQL select name, database_role, open_mode from v$database;

NAME DATABASE_ROLE OPEN_MODE
——— —————- ——————–
TPS    PHYSICAL STANDBY READ ONLY

CHANGES

Database DDL trigger added in the Primary.

CAUSE

 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-16000

SOLUTION

First 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 tempfile was added at the database level).

Data Collection

spool standby.out

set lines 200

select name, database_role, open_mode from v$database;
select FILE_NAME,STATUS, round(BYTES/1048576) from dba_temp_files;
REM Substitute SAPTMP with appropriate Tablespace name
select dbms_metadata.get_ddl(TABLESPACE , SAPTMP) from dual;

select * from dba_triggers;

spool off

spool primary.out
set lines 200
select name, database_role, open_mode from v$database;
select FILE_NAME,STATUS, round(BYTES/1048576) from dba_temp_files;
REM Substitute SAPTMP with appropriate Tablespace name
select dbms_metadata.get_ddl(TABLESPACE , SAPTMP) from dual;
select * from dba_triggers;
spool off

Sample output

select name, database_role, open_mode from v$database;

NAME DATABASE_ROLE OPEN_MODE
——— —————- ——————–
TP100 PRIMARY READ WRITE

SQL 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 30720

select dbms_metadata.get_ddl(TABLESPACE , SAPTMP) from dual;

DBMS_METADATA.GET_DDL(TABLESPACE , SAPTMP)
————————————————————————
CREATE TEMPORARY TABLESPACE SAPTMP TEMPFILE SIZE 32212254720 AUTOEXT

DBA_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

Solution

After identifying the suspect trigger, go ahead and disable the trigger in the PRIMARY

Example : ALTER TRIGGER sys.GGS_DDL_TRIGGER_BEFORE DISABLE;

Wait for the changes to be propagated in the STANDBY environment. Now you would be 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.

Troubleshooting

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 tracefile

Collect 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 from

spool 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 Substitute SAPTMP with appropriate Tablespace name
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 怎么办”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！