So we increased the temp tablespace by adding a 10G tempfile in the primary database.
SQL> select name from v$tempfile;
NAME
——————————————–
/u02/oracle/oradata/TEST/temp01.dbf
SQL> alter tablespace temp add tempfile ‘/u02/oracle/oradata/TEST/temp02.dbf’ size 10g;
Tablespace altered. ( IN PRODUCTION )
SQL> select name from v$tempfile;
NAME
———————————————
/u02/oracle/oradata/TEST/temp01.dbf
/u02/oracle/oradata/TEST/temp02.dbf
But the case is that in the standby database the new tempfile is not created.even the recovery is still active in standby database.
We switched a few archive logs in production database and waited till it applies on the standby database but still the tempfile is not created in the standby environment.
SQL> select name from v$tempfile;
NAME
———————————————–
/u02/oracle/oradata/TEST/temp01.dbf
On searching we found the following DOC id : 834174.1 ,
and we manually have to create the tempfiles in the standby database.
Now in standby database
sql > alter tablespace temp add tempfile ‘/u02/oracle/oradata/TEST/temp02.dbf’ size 10g;
and check
SQL> select name from v$tempfile;
NAME
———————————————
/u02/oracle/oradata/TEST/temp01.dbf
/u02/oracle/oradata/TEST/temp02.dbf
