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

 

The 10046 and errorstack should reveal precise details why it is failing to create the temp file.