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
Two errors:
1. Steps if standby database in mount stage:
— Open database
alter database open read only;
2. Steps if standby database in mount stage:
alter database recover managed standby database disconnect from session;