Friday, May 8, 2015

Adding Temp File on Standby

Creating a temporary tablespace on the primary of a physical data guard configuration only adds the tablespace entry on the standby, it doesn't create a temp file associated with the temporary tablespace.
Adding new temporary tablespace on primary.
create temporary tablespace temp2 tempfile '+data(tempfile)' SIZE 10M AUTOEXTEND ON NEXT 1M;
on standby the tablespace is listed in v$tablespace
SQL> select name from v$tablespace;

       TS# NAME
---------- -----------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         3 TEMP
         4 USERS
         5 TEMP2
But there's no temp file associated with this tablespce
select ts#,name from v$tempfile;

       TS# NAME
---------- --------------------------------------------------
         3 +DG_DATA/ent11g2s/tempfile/temp.267.787771139
To add a temp file stop the log apply on standby and open the db in read only mode.
SQL> alter database open read only;

Database altered.

SQL>  alter tablespace temp2 add tempfile '+DG_DATA' size 10m;

Tablespace altered.

select ts#,name from v$tempfile;

       TS# NAME
---------- ---------------------------------------------_
         3 +DG_DATA/ent11g2s/tempfile/temp.267.787771139
         5 +DG_DATA/ent11g2s/tempfile/temp2.260.877620677


If the temporary tablesapce is dropped on the primary then both tablespace and temp file is dropped on the standby.
SQL> select name from v$tempfile;

NAME
----------------------------------------------
+DG_DATA/ent11g2s/tempfile/temp.267.787771139

SQL>  select name from v$tablespace;

NAME
-----------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
On alert log
Recovery deleting tempfile #2:'+DG_DATA/ent11g2s/tempfile/temp2.260.877620677'
Recovery dropped temporary tablespace 'TEMP2'
Useful Metalink Notes
DDL Operations (Alter and Drop) on Temporary Tablespace Hang [ID 1911167.1]
Drop Temp Tablespace Command Hangs [ID 214371.1]
Drop Of A Huge LMT Temporary Tablespace Hangs [ID 1070665.1]