How To Add A Tempfile In Primary Database In Dataguard

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.

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
+DATA/DB/TEMPFILE/temp.511.865008665
+DATA/DB/TEMPFILE/temp.510.958108663

ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DATA’ size 3G;

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
+DATA/DB/TEMPFILE/temp.511.865008665
+DATA/DB/TEMPFILE/temp.510.958108663
+DATA/DB/TEMPFILE/temp.512.958108888

check tempfiles present in standby:

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
+DATA/DB/TEMPFILE/temp.511.865008665
+DATA/DB/TEMPFILE/temp.510.958108663

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 3G;

SQL> select file_name from dba_temp_files;

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 3G;

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;

Rameshwaran T