DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

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

#oracle #26ai #ai #programming

In Oracle Database 19c, when a tempfile is added on the primary database, it is not automatically created on the physical standby database. This is because Oracle does not generate redo for tempfile-related DDL operations (such as creating, adding, resizing, or dropping tempfiles).

Behavior in Oracle Database 19c

For example, when creating a temporary tablespace on the primary database(19c):

— 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
Enter fullscreen mode Exit fullscreen mode

On the standby database, although the tablespace metadata exists, the tempfile itself is not created:

— Data Guard

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

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

If we add 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
Enter fullscreen mode Exit fullscreen mode

Again, on the standby database:

— Data Guard

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

Automatic Tempfile Creation in Oracle AI Database 26ai

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

To enable this feature, the following parameters must be configured on the standby:

standby_file_management = AUTO
db_create_file_dest must be defined

— Data Guard

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

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

Now, when creating a temporary tablespace on the primary:

— 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
Enter fullscreen mode Exit fullscreen mode

The tempfile is automatically created on the standby database:

— Data Guard

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

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

Top comments (0)