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

Behavior in Oracle Database 19c ## Automatic Tempfile Creation in Oracle AI Database 26ai 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). For example, when creating a temporary tablespace on the primary database(19c): On the standby database, although the tablespace metadata exists, the tempfile itself is not created: If we add another tempfile on the primary: Again, on the standby database: 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 Now, when creating a temporary tablespace on the primary: The tempfile is automatically created on the standby database: This enhancement eliminates the need for manual synchronization of tempfile configurations between primary and standby databases, simplifying Data Guard administration. Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: SQL> select ts# from v$tablespace where NAME='TEMP1404'; TS# ---------- 15 SQL> select name from v$tempfile where ts#=15; no rows selected COMMAND_BLOCK: SQL> select ts# from v$tablespace where NAME='TEMP1404'; TS# ---------- 15 SQL> select name from v$tempfile where ts#=15; no rows selected COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: SQL> select name from v$tempfile where ts#=15; no rows selected Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: SQL> select name from v$tempfile where ts#=15; no rows selected COMMAND_BLOCK: SQL> select name from v$tempfile where ts#=15; no rows selected COMMAND_BLOCK: 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 COMMAND_BLOCK: SQL> alter system set standby_file_management=AUTO; System altered. SQL> alter system set db_create_file_dest='/oracle/base/oradata'; System altered. COMMAND_BLOCK: SQL> alter system set standby_file_management=AUTO; System altered. SQL> alter system set db_create_file_dest='/oracle/base/oradata'; System altered. COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: 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 COMMAND_BLOCK: SQL> select name from v$tempfile where ts#=5; NAME -------------------------------------------------------------------------------- /oracle/base/oradata/DG1/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wbyx_.dbf COMMAND_BLOCK: SQL> select name from v$tempfile where ts#=5; NAME -------------------------------------------------------------------------------- /oracle/base/oradata/DG1/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wbyx_.dbf