Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 129,480
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

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 )

w

Connecting to %s

 
%d bloggers like this: