ODG TEMP Tablespace
Tempfiles in the Standby are only used when your Standby is opened in read-only mode. When you add tempfiles in the Primary database, new tempfiles are not added automatically on the Physical Standby database because no redo is generated. See 834174.1
ADD Temp TS to Physical Standby
Create temporary tablespace and temp file on PRIMARY. But only meta data without temp file will be created on STANDBY. File on STANDBY will be created after switch over or you can add it as in 'ADD Temp file to existing temp tablespace on physical standby' section below.
CREATE TEMPORARY TABLESPACE MYTEMPTS
TEMPFILE '/u01/oradata/MYSID/MYTEMPDF1.dbf'
SIZE 50M
AUTOEXTEND ON
NEXT 50M
MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1024K;
ADD Tempfile to TS on Physical Standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
ALTER TABLESPACE MYTEMPTS TEMPFILE '/u01/oradata/MYSID/MYTEMPDF2.dbf' SIZE 10M AUTOEXTEND ON;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ADD Tempfile to TS on Physical Standby (Active Data Guard)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER TABLESPACE MYTEMPTS TEMPFILE '/u01/oradata/MYSID/MYTEMPDF2.dbf' SIZE 10M AUTOEXTEND ON;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;