ORA-16000 Dropping or Creating Temporary Tablespace In Physical Standby Database

Symptomps

ORA-16000 Dropping or Creating Temporary Tablespace In Physical Standby Database

Cause

The follwing example demostrates the issue standby database is open read only mode:


sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY


Drop temporary tablespace

SQL> drop tablespace Temp;
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

Create temporary tablespace:

SQL> create temporary tablespace temp  tempfile '/u01/app/oracle/oradata/gpprod/tmp.dbf' size 200m reuse extent management local uniform size 1048576;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access

Soutions

It's not possible to drop or create temporary tablespace in a physical standbu database.

A Physical stanby database allows ddl on tempfiles only, because no redo is renerated for temp files