8 Replies Latest reply: Nov 17, 2012 5:13 PM by CKPT RSS

    Adding datafiles in dataguard environment

    Meenakshy singh
      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?


      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
          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

            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

            • 3. Re: Adding datafiles in dataguard environment
              Meenakshy singh
              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.

              • 4. Re: Adding datafiles in dataguard environment
                Shivananda Rao

                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
                  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

                  • 6. Re: Adding datafiles in dataguard environment
                    Meenakshy singh
                    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
                    ---------- --------------------------------------------------

                    • 7. Re: Adding datafiles in dataguard environment
                      Shivananda Rao

                      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:
                      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

                        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.