Add Tempfile in Dataguard Environment

Temporary file addition is not handle automatically as data file added in Dataguard Environment.
You have to manually add the temp file in Standby database and managed it manually for sync both Primary and standby.
Parameter standby_file_management=AUTO has no impact on tempfile on Standby. It managed manually.

Add tempfile to an existing temporary tablespace in the Primary and Standby sites.

1. Primary: Add tempfile at temp tablespace.

alter tablespace temp add tempfile '+DATA' size 50M;


2. Primary: Check the files present in TEMP tablespace.

select name from v$tempfile;


3. Primary: Switch the logfile for replicate on DR site.

alter system switch logfile;


4. Standby: Check files present in TEMP tablespace.
Tempfile will NOT automatically be created in the physical standby

select name from v$tempfile;

Note: standby_file_management=AUTO has no impact on tempfile management.

5. You can add temp file in Standby:
A. Switchover
B. Standby is Readonly mode.

A. You can switchover the Primary to Standby database.
Add the new tempfile to the new primary site manually after the switch-over is complete.

alter tablespace temp add tempfile '+DATA' size 50M;

Note: it will maintain the same file structure at both primary and standby.

B. Tempfile is added to the Primary and the standby site is OPEN READ ONLY mode.
you can add the new temp file manually to the Standby if standby is in READONLY mode.

alter tablespace temp add tempfile '+DATA' size 20M;


Note: You cannot add temp file during database mount state. If you tried you got the following error:

SQL> alter tablespace TEMP add tempfile 'C:\ORacle18c\oradata\xe\temp02.dbf' size 200m;
alter tablespace TEMP add tempfile 'C:\ORacle18c\oradata\xe\temp02.dbf' size 200m
*
ERROR at line 1:
ORA-01109: database not open