By Mouhamadou Diaw

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
1
2
3
4
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
1
2
3
4
5
6
7
8
9
10
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
1
2
3
4
5
6
7
8
9
10
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












1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19






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












1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22


23






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












1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22


23


24






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)












1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19






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












1


2


3


4


5


6


7


8


9


10






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












1


2


3


4


5


6


7


8


9


10


11


12


13


14


15






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


*************************************


oracle@rac12e:/home/oracle/ [HRVA]














3- We manually add the missing tempfile in the standby database












1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16






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












1


2


3


4


5


6


7


8


9


10


11


12


13


14






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












1


2


3


4


5


6


7


8


9


10


11


12






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.





 Post Views: 1,991










Thumbnail [60x60]


by

Oracle Team