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.
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
Post a Comment