Automatic temp file creation on the standby - Oracle AI Database 26ai

Source: Dev.to

Behavior in Oracle Database 19c

When a tempfile is added on the primary database, it is not automatically created on the physical standby. Oracle does not generate redo for tempfile‑related DDL (create, add, resize, drop).

Primary

SQL> create temporary tablespace TEMP1404;
Tablespace created.

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
        15

SQL> select name from v$tempfile where ts#=15;
NAME
--------------------------------------------------------------------------------
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.807.1225812657

Data Guard (standby)

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
        15

SQL> select name from v$tempfile where ts#=15;
no rows selected

Adding another tempfile on the primary

Primary

SQL> alter tablespace TEMP1404 add tempfile size 100m;
Tablespace altered.

SQL> select name from v$tempfile where ts#=15;
NAME
--------------------------------------------------------------------------------
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.807.1225812657
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.808.1225812823

Data Guard (standby)

SQL> select name from v$tempfile where ts#=15;
no rows selected

---

Automatic Tempfile Creation in Oracle AI Database 26ai

Starting with Oracle AI Database 26ai, tempfile creation is automatically handled on the standby.

Enabling the feature on the standby

SQL> alter system set standby_file_management=AUTO;
System altered.

SQL> alter system set db_create_file_dest='/oracle/base/oradata';
System altered.

Creating a temporary tablespace on the primary

SQL> create temporary tablespace TEMP1404;
Tablespace created.

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
         5

SQL> select name from v$tempfile where ts#=5;
NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/DB2/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wdt7_.tmp

Result on the standby

SQL> select name from v$tempfile where ts#=5;
NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/DG1/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wbyx_.dbf

This enhancement removes the need for manual synchronization of tempfile configurations between primary and standby databases, simplifying Data Guard administration.