If standby_file_management is set to AUTO, as soon as we add a datafile on primary database, same will be created automatically on standby database.
But will the same valid for tempfile?? Answer is NO.

Adding tempfiles to TEMP tablespaces in primary database, will not automatically create on standby database. Because no redo is generated, while adding tempfile. So DBA have to add the temp file manually.

EXAMPLE:

Add a tempfile on primary:

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773



ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 5G;


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773
+DATA/DB/TEMPFILE/temp.313.958108777

check tempfiles present in standby:

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773

We can see the tempfile didnt created in standby. So we have to create the same manually in standby.

CREATING TEMPFILE ON STANDBY:

Steps if active dataguard( i.e standby in read only mode):

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 5G;


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773
+DATA/DB/TEMPFILE/temp.313.958108777

Steps if standby database in mount stage:

--- cancel recovery:
recover managed standby database cancel;


-- Open database

alter database open readonly;

-- Add tempfile

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 5G;

restart the db in mount stage:

shutdown immediate;

startup mount;

---- Start the recovery process:

alter database recovery managed standby database disconnect from session;
SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/DB/TEMPFILE/temp.311.958108775
+DATA/DB/TEMPFILE/temp.310.958108773
+DATA/DB/TEMPFILE/temp.313.958108777
dataguardstandbytemp DATAGUARD


Related Topics

How to use expdp to export data from physical standby database
How to recreate physical standby controlfile
Flashback primary database in dataguard environment
How to disable enable log shipping in standby using dgmgrl
How to enable active dataguard in physical standby database
Standby redologs in oracle dataguard
How to setup dataguard broker configuration (DG broker) in 12c
Useful DGMGRL commands in oracle dataguard
How to add a tempfile in primary database in dataguard

You May Also Like

OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2
Install oracle enterprise manager cloud control 12c
Lock account automatically with INACTIVE_ACCOUNT_TIME
How to get tablespace quota details of an user in oracle
how to change archivelog destination in oracle
How to enable active dataguard in physical standby database
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
Apply database proactive bundle patch in RAC using manual process
Apply database patch from OEM Cloud control
Find the active transactions in oracle database

From This Website

How to generate AWR report in RAC
Flashback primary database in dataguard environment
Oswatcher tool for collecting server diagnostic information
ENABLE_PARALLEL_DML hint in oracle 12c
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
How to multiplex control file in standalone database
LREG Background Process in oracle
How to deinstall/cleanup standalone grid infrastructure
How to get the execution plan for a SQL between two AWR snapshots
shell script for file system alert