This discussion is archived
8 Replies Latest reply: Nov 17, 2012 9:13 AM by CKPT RSS

Adding datafiles in dataguard environment

Meenakshy singh Newbie
Currently Being Moderated
Hi gurus,

I have one doubt regarding adding data file to a table space which is in data guard environment.

My oracle database is install in oracle 10gR2 on 64bit windows server.I have one primary database and physical DR database.
I am going to add data file in primary database.All my data files reside in the directory ' C:/oradata/data file/$Month_Name'

Now to add new data file i have to create new directory under C:/oradata/data file/ named November and add data file in the November directory.

1. Will addition of new folder named November will automatically reflect in DR database also. or do i need to go to DR and add the same directory and then add the data file in primary database.

2.When i am adding data file to primary database,will it effect to DR database?Or the DR database will automatically sync?

Please provide me step by step procedure.

can anyone provide me steps to follow up,so that both primary and DR will be in sync?

Thanks,
Meena

Edited by: 949632 on Nov 16, 2012 10:46 PM

Edited by: 949632 on Nov 16, 2012 10:47 PM
  • 1. Re: Adding datafiles in dataguard environment
    Think_dba Journeyer
    Currently Being Moderated
    1.You need to create directory at standby site

    2. Make standby_file_management to AUTO at primary site
  • 2. Re: Adding datafiles in dataguard environment
    LaserSoft Journeyer
    Currently Being Moderated
    Hi

    1. Adding new datafile in Production Database will automatically reflect the datafile in Standby Database.
    2. Data will move automatically from Primary database to standby database (If time permits make 'alter system switch logfile' in primary database)
    3. make sure that the parameter standby_file_management=AUTO in primary database.

    This works....

    Additional Steps :

    1. If you want to create new directory in standby database same as primary database then you have add the directory structure in db_file_name_convert parameter

    Thanks
    LaserSoft
  • 3. Re: Adding datafiles in dataguard environment
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi LaserSoft,

    I understand that datafile addition will automatically propagate to the DR but I am adding a folder on Primary first and putting the datafile in that directory.
    My worry is creation of directory on primary is something related to OS and datafile addition to oracle.So when i add the directory will it reflect in DR automatically.

    Thanks
    Meena
  • 4. Re: Adding datafiles in dataguard environment
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    Make sure that the parameter "standby_file_management" is set to AUTO in the standby pfile/spfile. Secondly, make sure that the parameter "db_file_name_convert" is also set to the correct path on the standby pfile/spfile. This is a static parameter, hence you need to bounce your standby database after you make changes.
  • 5. Re: Adding datafiles in dataguard environment
    LaserSoft Journeyer
    Currently Being Moderated
    Hi Meena

    Thanks for your reply.

    Please go through all the specified options :

    1. standby_file_management
    2. db_file_name_convert

    are important parameters and bounce the database

    Thanks
    LaserSoft
  • 6. Re: Adding datafiles in dataguard environment
    Meenakshy singh Newbie
    Currently Being Moderated
    Path of datafile in Primary Database: D:\oracle\P01\sapdata5*

    Some configurations :-

    DG_BROKER_START = false;*
    SQL> sho parameter db_file_name_convert*

    NAME                                 TYPE        VALUE*
    --------------------------------     ------*
    db_file_name_convert                 string*
    SQL> sho parameter standby_file_management*

    NAME                                 TYPE        VALUE*
    ------------------------------------ -----------*
    standby_file_management              string      AUTO*

    Here is the output of dba_data_files of primary database where datafiles are created.I want to create new directory named BTABD_11 in this location and add a new datafile named BTABD.DATA11

    SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='PSAPBTABD';

    TABLESPACE FILE_NAME
    ---------- --------------------------------------------------
    PSAPBTABD D:\ORACLE\P01\SAPDATA2\BTABD_7\BTABD.DATA7
    PSAPBTABD D:\ORACLE\P01\SAPDATA2\BTABD_6\BTABD.DATA6
    PSAPBTABD D:\ORACLE\P01\SAPDATA2\BTABD_5\BTABD.DATA5
    PSAPBTABD D:\ORACLE\P01\SAPDATA2\BTABD_4\BTABD.DATA4
    PSAPBTABD D:\ORACLE\P01\SAPDATA2\BTABD_3\BTABD.DATA3
    PSAPBTABD D:\ORACLE\P01\SAPDATA2\BTABD_2\BTABD.DATA2
    PSAPBTABD D:\ORACLE\P01\SAPDATA2\BTABD_1\BTABD.DATA1
    PSAPBTABD D:\ORACLE\P01\SAPDATA5\BTABD_8\BTABD.DATA8
    PSAPBTABD D:\ORACLE\P01\SAPDATA5\BTABD_9\BTABD.DATA9
    PSAPBTABD D:\ORACLE\P01\SAPDATA5\BTABD_10\BTABD.DATA10

    Thanks
    Meena
  • 7. Re: Adding datafiles in dataguard environment
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    The directory does not get created on the standby database if you create it on the primary database.
    Let me give you an example. Suppose the datafiles on the standby database are currently at location "D:\oracle\std\data_5" and now you need to create another directory called "data_11" as "D:\oracle\std\data_11", then on the standby database, set the parameter "db_file_name_convert" as shown below:
    db_file_name_convert='D:\oracle\P01\sapdata5\','D:\oracle\std\data_5\','D:\oracle\P01\BTABD_11','D:\oracle\std\data_11'
    Syntax of db_file_name_convert:
    db_file_name_convert='<file location on primary>','<file location on standby>','<file location on primary>','<file location on standby>'
    Note: db_file_name_convert parameter needs to be set on standby database and is a static parameter. Hence you need to bounce the standby database after setting it.

    Hope it helps !! Please consider closing the thread by providing appropriate points if you feel that your question has been answered.
  • 8. Re: Adding datafiles in dataguard environment
    CKPT Guru
    Currently Being Moderated
    Meena,

    This requirement can be achieved, But why you prefer to keep datafiles to be created in each month and what are the benefits of it?
    Usually data will be differentiated some times in months, but data files never as i see so far.

    If so you have to mention each month entry in standby database. If this is requirement plan for at least 6 months of folders and assign the same
    It can be problem if you perform switchover, So you have to mention even in primary database. Of course you do these changes in SPFILE/PFILE, In next bounce these parameters will be applied.

    Again, if you have each month in each folder based on that you have to create 12 folders. So it can be complex. If it is the requirement yes you can go ahead. Mentioning a link for that parameter settings, you can refer it.
    http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams048.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points