Wednesday, June 19, 2019

What to remember when adding temporary tablespaces on primary databases



Are temporary tablespaces automatically replicated to the standby databases?

No, they are not.
This is documentet in Oracle support note 834174.1 "Temporary Datafile created in Primary is missing in the Standby Database"

The setting of STANDBY_FILE_MANAGEMENT has no impact on temporary tablespaces and temporary files, because no redo is generated.

Unfortunately, this fact is not mentioned in the matrix listed in the documentation under the headline "Primary Database Changes That Require Manual Intervention at a Physical Standby"

From my own experience, here is what I did at a time when the primary database ran short on temporary space, and a new tablespace had to be created immediately:

Add temporary tablespace to the primary database:
CREATE  BIGFILE  TEMPORARY  TABLESPACE TMP 
TEMPFILE '/u02/oradata/proddb01/tmp.dbf' SIZE 1024M AUTOEXTEND ON NEXT 128M MAXSIZE 1T 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Check the situation on the standby database:
SQL> SELECT TABLESPACE_NAME , bigfile,status FROM DBA_TABLESPACES WHERE CONTENTS = 'TEMPORARY';

TABLESPACE_NAME                BIG STATUS
------------------------------ --- ---------
TEMP                           NO  ONLINE
TMP                            YES ONLINE

The temporary tablespace name is registred in the standby data dictionary, but there is no tempfile attached.

In order to add a tempfile to the standby database, it must be opened in READ ONLY mode.
Since my standby database is an active standby, it is already opened in READ ONLY mode:
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Add the tempfile:
alter tablespace tmp add tempfile '/u02/oradata/proddb01/tmp.dbf' size 1024M autoextend on next 128M maxsize 1T;

Tablespace altered.

Recheck the situation in the standby database:
SQL>  select ts#,name,status,enabled from v$tempfile;

       TS# NAME                                               STATUS  ENABLED
---------- -------------------------------------------------- ------- ----------
         3 /u02/oradata/proddb01/temp01.dbf                     ONLINE  READ WRITE
        31 /u02/oradata/proddb01/tmp.dbf                        ONLINE  READ WRITE

No comments:

Post a Comment