Point to be considered when we add temp datafile in database when we have STANDBY Environment

Wneever we add tempfiles in the Primary database, new tempfiles will never added automatically in the Physical Standby database because no redo is generated.  (Oracle documentation  Doc ID 834174.1)
We have to manually create the added tempfile in the standby. For this we have to open the standby database in Read Only mode.

1- First we disable  the redo apply on the standby database to not activate ACTIVE DATAGUARD option if ever we do not have the license
DGMGRL> EDIT DATABASE 'ORACLE_DR' SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database verbose 'ORACLE_DR';
Database - ORACLE_DR
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       120 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF

DGMGRL> EDIT DATABASE 'ORACLE_DR' SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database verbose 'ORACLE_DR';
Database - ORACLE_DR
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       120 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
2- We open the standby database in read only mode
SQL> alter database open read only;

Database altered.

SQL> alter database open read only;

Database altered.

3- We manually add the missing tempfile in the standby database
SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------
/app/app/oracle/oradata/ORACLE/temp01.dbf

SQL> alter tablespace temp add tempfile '/app/app/oracle/oradata/ORACLE/temp02.dbf' size 30G;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------
/app/app/oracle/oradata/ORACLE/temp01.dbf
/app/app/oracle/oradata/ORACLE/temp02.dbf

SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------
/app/app/oracle/oradata/ORACLE/temp01.dbf

SQL> alter tablespace temp add tempfile '/app/app/oracle/oradata/ORACLE/temp02.dbf' size 30G;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------
/app/app/oracle/oradata/ORACLE/temp01.dbf
/app/app/oracle/oradata/ORACLE/temp02.dbf
4- We shut down the standby database and  mount the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL>

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Database mounted.

SQL>
5- We enable the redo apply

DGMGRL> EDIT DATABASE 'ORACLE_DR' SET STATE='APPLY-ON';
Succeeded.

DGMGRL> show database verbose 'ORACLE_DR';
Database - ORACLE_DR
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    ORACLE


DGMGRL> EDIT DATABASE 'ORACLE_DR' SET STATE='APPLY-ON';
Succeeded.

DGMGRL> show database verbose 'ORACLE_DR';
Database - ORACLE_DR

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    ORACLE
Conclusion
Corresponding tempfiles in the standby database need to be created whenever we add tempfile in Primary database.

Comments

Popular Posts