Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 169,954
  • Archives

  • Categories

  • Subscribe

  • Advertisements

Data Guard – Adding and Resizing a datafile on the Primary database

Posted by FatDBA on October 13, 2012

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: