standby上增加tempfile報錯ORA

168次閱讀

共計(jì) 4477 個字符，預(yù)計(jì)需要花費(fèi) 12 分鐘才能閱讀完成。

這篇文章主要介紹 standby 上增加 tempfile 報錯 ORA-00604,ORA-16000 怎么辦，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

在 DG 中對 primary 增加 tempfile 不會同步到 standby 上，所以增加 tempfile 需要在 standby 上操作，但這次在 standby 上增加 tempfile 卻報錯 ORA-00604 ORA-16000 (mount 狀態(tài)，MRP cancel 報錯依舊)。

分析：

這里 ORA-00604 通常是 trigger 在影響，因?yàn)闀r間緊迫沒有做 10046 trace，直接查詢官方得到權(quán)威解釋。

原來是因?yàn)?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 怎么辦”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完