DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, October 17, 2016

Tempfiles in Standby database

In a active standby database we had a problem due to temp tablespace.

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

No comments: