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;

Bibliography