Infrastructure at your Service

Mouhamadou Diaw

Dataguard Environment and Database Tempfiles

Last day I found a curious message in the backup alert log in a dataguard environment.
The message was related to an issue with the resync catalog and was something like

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 02/18/2016 17:48:42
ORA-00001: unique constraint (RMAN.TS_P2) violated
After few researches, it seems that this error is due to a mismatch of tempfiles between Primary and Standby.
Some tempfiles were missing in the standby database.

How is it be possible as we have the parameter StandbyFileManagement set  to auto on both sides

Primary database
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 HRVA_SITE1

SQL> show parameter standby_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
standby_file_management 	     string	 AUTO
SQL>
Standby database
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 HRVA_SITE2

SQL> show parameter standby_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
standby_file_management 	     string	 AUTO
SQL>

Let’s do some tests to better understand how oracle manages datafiles and tempfiles in dataguard environment

 First we add a datafile to the primary database

SQL> show parameter db_uni

NAME				     TYPE	 VALUE
------------------------------------ ----------- --------------------
db_unique_name			     string	 HRVA_SITE1

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/HRVA/users02.dbf' size 5M;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------
/u01/app/oracle/oradata/HRVA/system01.dbf
/u01/app/oracle/oradata/HRVA/sysaux01.dbf
/u01/app/oracle/oradata/HRVA/undotbs01.dbf
/u01/app/oracle/oradata/HRVA/users01.dbf
/u01/app/oracle/oradata/HRVA/users02.dbf
Now let's verify that the new datafile is present in the standby database
SQL> show parameter db_uni

NAME				     TYPE	 VALUE
------------------------------------ ----------- -----------------------
db_unique_name			     string	 HRVA_SITE2

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HRVA/system01.dbf
/u01/app/oracle/oradata/HRVA/sysaux01.dbf
/u01/app/oracle/oradata/HRVA/undotbs01.dbf
/u01/app/oracle/oradata/HRVA/users01.dbf
/u01/app/oracle/oradata/HRVA/users02.dbf

SQL>

This confirms that the new datafile was replicated as expected.

Now let’s add a tempfile to the primary database

SQL> show parameter db_uni

NAME				     TYPE	 VALUE
------------------------------------ ----------- --------------------------
db_unique_name			     string	 HRVA_SITE1

SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------
/u01/app/oracle/oradata/HRVA/temp01.dbf

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/HRVA/temp02.dbf' size 10M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------
/u01/app/oracle/oradata/HRVA/temp01.dbf
/u01/app/oracle/oradata/HRVA/temp02.dbf

SQL>

Connecting to the standby database, we can see that the new tempfile was not replicated (although the standby_file_management is set to auto)

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> show parameter db_uni

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 HRVA_SITE2

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HRVA/temp01.dbf

SQL>

 In fact when you add tempfiles in the Primary database, new tempfiles are not 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 'HRVA_SITE2' SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database verbose 'HRVA_SITE2';
Database - HRVA_SITE2
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       46 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.

********* dbi services Ltd. *********
STATUS                 : OPEN
DB_UNIQUE_NAME         : HRVA_SITE2
OPEN_MODE              : READ ONLY
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PHYSICAL STANDBY
FLASHBACK_ON           : YES
FORCE_LOGGING          : YES
VERSION                : 11.2.0.4.0
*************************************
[email protected]:/home/oracle/ [HRVA]
3- We manually add the missing tempfile in the standby database
SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------
/u01/app/oracle/oradata/HRVA/temp01.dbf

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/HRVA/temp02.dbf' size 10M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
---------------------------------------------------------------------------
/u01/app/oracle/oradata/HRVA/temp01.dbf
/u01/app/oracle/oradata/HRVA/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.
Total System Global Area  417546240 bytes
Fixed Size		    2253824 bytes
Variable Size		  331353088 bytes
Database Buffers	   79691776 bytes
Redo Buffers		    4247552 bytes
Database mounted.
SQL>
5- We enable the redo apply
DGMGRL> EDIT DATABASE 'HRVA_SITE2' SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database verbose 'HRVA_SITE2';
Database - HRVA_SITE2
  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):
    HRVA

Conclusion

In a dataguard environment whenever we add tempfiles or temporary tablespaces, we have to create manually the corresponding tempfiles in the standby database.


 

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant