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