Introduction
After creating a PDB on the primary in a Data Guard environment, the PDB gets also created on standby. However, the standby will still be missing a TEMP file. To create a TEMP file, I got a comment from a customer that they were switching over to standby to create the TEMP file, which is unnecessary. This led me to put the available options in this blog post.
Spoiler Alert! Oracle Database 23c creates the file automatically when the PDB gets opened.
The Environment
- Oracle Database version 19c running on Oracle Cloud using Base Database Service.
- Oracle Database version 23c running on Oracle Cloud using Base Database Service.
Oracle Database 19c
To create the TEMP file on standby, the standby must be open in read-only mode.
If you have an Active Data Guard option license, then it’s straightforward. Open the PDB in read-only mode and create the TEMP file:
SQL> alter session set container = PDB001;
SQL> alter tablespace temp add tempfile;
SQL> select name from v$tempfile;
Without the Active Data Guard option license, you can still open the standby in read-only mode. However, only while Redo Apply is stopped. So, stop Redo Apply, open the PDB in read-only mode, create the TEMP file, restart the database in MOUNT mode, and start Redo Apply again:
DGMGRL> edit database <standby_db_unique_name> set state = 'apply-off';
SQL> alter database open;
SQL> alter pluggable database PDB001 open;
SQL> alter session set container = PDB001;
SQL> alter tablespace temp add tempfile;
SQL> alter session set container = cdb$root;
SQL> shutdown immediate
SQL> startup mount
DGMGRL> edit database <standby_db_unique_name> set state = 'apply-on';
DGMGRL> show configuration
During this time, the primary is still protected by the standby, as the redo continues to be shipped to the standby host. It only doesn’t get applied until Redo Apply is started again. This might result in a (slightly) higher RTO if you need to switchover or failover during or right after this time, as the standby will need to apply the redo before opening. However, creating the TEMP file and restarting the database won’t take that much time. Just to be mentioned for completeness.
Oracle Database 23c
In Oracle Database 23c you don’t need to worry about the TEMP file on standby anymore. It automatically gets created when the PDB opens if:
- The standby uses Oracle Managed Files (OMF), i.e., the db_create_file_dest parameter is set to define the location for creating data files or temp files, and
- The standby_file_management parameter is set to AUTO on standby.
-- on standby
SQL> select value from v$parameter where name='db_create_file_dest';
VALUE
---------------------------------------
/u02/app/oracle/oradata/CDB23c_dc2/
SQL> select value from v$parameter where name='standby_file_management';
VALUE
---------------------------------------
AUTO
-- on primary
SQL> create pluggable database pdb23c admin user pdb23cadmin identified by VerySecretPW__2023;
SQL> alter pluggable database pdb23c open;
-- on standby
SQL> alter pluggable database pdb23c open; -- when becomes primary, or requires ADG or stop Redo Apply
SQL> alter session set container=pdb23c;
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/CDB23c_dc1/CDB23c_dc2/03C1CA724D8B292BE06353000A0AA39B/datafile/o1_mf_temp_lgkk99nz_.dbf
Conclusion
In Oracle Database 19c, you need to create the TEMP file on standby PDB while it’s open in read-only mode. Either have the Active Data Guard option license or stop Redo Apply during this operation to comply with licensing policies.
In Oracle Database 23c the TEMP file gets created automatically when the standby PDB gets open if the standby uses Oracle Managed Files and the standby_file_management parameter is set to AUTO on standby.
Further Reading
- Data Guard Physical Standby – Managing temporary tablespace tempfiles (Doc ID 1514588.1)
- A Simple Approach – Creating PDBs in a Data Guard environment with TDE enabled
- Creating PDBs in Data Guard Environments while using OCI Vault for TDE Key Management
- Hot Clone a remote PDB in Data Guard Environments using Transient no-standby PDBs
- How PDB Recovery Isolation in 21c makes your Life easier
Database Heartbeat 