Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to add tempfile error ORA-00604,ORA-16000 to standby. The article is very detailed and has certain reference value. Interested friends must finish reading it!

Adding tempfile to primary in DG will not be synchronized to standby, so adding tempfile needs to be operated on standby, but this time adding tempfile to standby will result in an error ORA-00604 ORA-16000 (mount status, MRP cancel error is still the same).

Analysis:

Here ORA-00604 is usually influenced by trigger, because time is tight, we did not do 10046 trace, directly consult the official to get an authoritative explanation.

It turns out that DB installed OGG in which trigger GGS_DDL_TRIGGER_BEFORE (the main function of trigger is to record DDL operations) affects the increase of tempfile operations.

Resolve:

After disable trigger GGS_DDL_TRIGGER_BEFORE in primary, add tempfile to standby, and then trigger enable in primary

Official article:

11g Standby Unable To Add Temp File (document 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.