Tales From A Lazy Fat DBA

Standby Database (Data Guard) is slowly and steadily turning out to be my best topic/subject among others in High Availability. It attracts me to experiment to improve the HA features.

Alright, today I’ve fixed one of the problem i was facing from past few days and discovered that i was doing a stupid mistake and that i failed to catch too till the moment i rechecked some parameters in initialization file minute ago.

I tried to add/modify some data files and was not getting at the Standby End which resulted in several errors at Sb DB and lead to Standby Database crash. Otherwise i have to manual port log files from primary destination to the Standby party to maintain the sync between the two after datafiles addition.

Below were the error messages i was getting when tested the standby status:

SQL>  select message from v$dataguard_status;

MESSAGE
——————————————————————————–
ARC0: Archival stopped
ARC1: Archival stopped
Media Recovery Waiting for thread 1 seq# 197

Solution:
I’ve check and found Standby_File_Management was set to ‘MANUAL’
and both the db_file_name_convert and log_file_name_conver were different.

I’ve set all the three parameters to below provided values
Standby_File_Management=AUTO  (Both on Primary and Standby)
db_file_name_convert          ( I’ve left it unchanged on both sides – it was empty (default))
log_file_name_convert         ( I’ve left it unchanged on both sides – it was empty (default))

Snippets from my machine:
SQL> show parameter standby_file_management    (On both Primary and Standby)

NAME                                 TYPE        VALUE
———————————— ———– ——————————
standby_file_management              string      AUTO

SQL> show parameter db_file_name_convert

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_name_convert                 string

SQL> show parameter log_file_name_convert

NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_file_name_convert                string

1. Add a new tablespace to the primary database:

   SQL> CREATE TABLESPACE new_ts DATAFILE '/disk1/oracle/oradata/payroll/t_db2.dbf'
     2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

2. Archive the current online redo log file so the redo data will be transmitted to and applied on the standby database:

   SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. Verify the new datafile was added to the primary database:

   SQL> SELECT NAME FROM V$DATAFILE;
   NAME
   ----------------------------------------------------------------------
   /disk1/oracle/oradata/payroll/t_db1.dbf
   /disk1/oracle/oradata/payroll/t_db2.dbf

4. Verify the new datafile was added to the standby database:

   SQL> SELECT NAME FROM V$DATAFILE;
   NAME
   ----------------------------------------------------------------------
   /disk1/oracle/oradata/payroll/s2t_db1.dbf
   /disk1/oracle/oradata/payroll/s2t_db2.dbf