13 Replies Latest reply: Dec 9, 2013 3:57 AM by Mahir M. Quluzade RSS

    DG Physical Standby add new data file

      Hi all,

       

      11.2.0.1

       

      I am adding new filesystem for my Primary database tablespace TEMP and UNDO in "/u51/oradata/PROD/temp02.dbf"  and "/u51/oradata/PROD/undots02.dbf"

       

      Do I need to create its counterpart folder at my Standby database?

       

      Thanks,

      petra k

        • 1. Re: DG Physical Standby add new data file
          sb92075

          f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

           

          Hi all,

           

          11.2.0.1

           

          I am adding new filesystem for my Primary database tablespace TEMP and UNDO in "/u51/oradata/PROD/temp02.dbf"  and "/u51/oradata/PROD/undots02.dbf"

           

          Do I need to create its counterpart folder at my Standby database?

           

          Thanks,

          petra k

           

          yes

          • 2. Re: DG Physical Standby add new data file
            1031227

            I believe you need to create the FOLDER but not the FILE.

            Being a physical standby, all changes you do on Prod will be on STDBY in a few minutes(at most).

             

            If it was a logical, it might be different but i dont what situation i would use for a logical standby.

            • 3. Re: DG Physical Standby add new data file

              Thanks all,

               

              I searched google on how to add datafile to physical standby and primay, but I only see Version 8?

              Is this still applicable or with similar process  to 11g ?

               

              ORACLE - DBA Tips Corner

              • 4. Re: DG Physical Standby add new data file
                Hemant K Chitale

                Instead of google, why not search the official documentation ?

                 

                See the notes for "STANDBY_FILE_MANAGEMENT"  in the "Initialization Parameters" chapter of the Data Guard Concepts and Administration Guide at http://docs.oracle.com/cd/E11882_01/server.112/e41134/init_params.htm#i88512

                 

                and subsection 9.3.1 "Adding a Datafile or Creating a Tablespace" in the same guide at  http://docs.oracle.com/cd/E11882_01/server.112/e41134/manage_ps.htm#i1010428

                 

                However, as has been pointed out, the directory ("folder") must be present at the Standby server for Oracle to automatically add the datafile.

                 

                 

                Hemant K Chitale


                • 5. Re: DG Physical Standby add new data file
                  Anar Godjaev

                  HI ,

                   

                  Yes you are right.  ..  if you adding filesystem in you Primary database ,you  need to create counterpart folder in you Secondary location...

                   

                  Thank you

                  • 6. Re: DG Physical Standby add new data file
                    Karan Kukreja

                    Hi,

                     

                    Yes , the folder structure needs to be created , however we do not need to create the datafile manually. Its automatically done if we set the parameter STANDBY_FILE_MANAGEMENT to AUTO. If this is not set to AUTO , then we need to create the file ourselves.

                    Preferred option is to set it to AUTO.

                     

                    HTH

                    • 7. Re: DG Physical Standby add new data file
                      Mahir M. Quluzade

                      f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

                       

                      Hi all,

                       

                      11.2.0.1

                       

                      I am adding new filesystem for my Primary database tablespace TEMP and UNDO in "/u51/oradata/PROD/temp02.dbf"  and "/u51/oradata/PROD/undots02.dbf"

                       

                      Do I need to create its counterpart folder at my Standby database?

                       

                      Thanks,

                      petra k

                      Hi,

                       

                      As all say, it is best practice using STANDBY_FILE_MANAGEMENT  as AUTO.

                      It is rally useful. But in your case have two interesting nuance.

                       

                      When we using standby file management automatically.

                       

                      1. When you adding UNDO tablespace on primary database, then yes (when log applied) standby  database will create UNDO tablespace automatically,

                      and undo data file will create at standby datafiles folder (will use db_file_name_covert).
                      Here is nuance, when  you change UNDO tablespace of database, you must change UNDO_TABLESPACE parameter on primary  database,

                      but this change is not effective on standby database automatically, you  must change this parameter on standby manually.

                       

                      2. If  you create TEMP tablespace on  primary, then on standby database will create temporary tablespace, but without temp file.

                      Becuase  STANDBY_FILE_MANAGEMENT for only data files, not  redo logs  and tempfiles.

                      And when you change default temporary tablespace to new temp tablespace, it is will change on standby database automatically.

                       

                       

                      Regards

                      Mahir M. Quluzade

                      • 8. Re: DG Physical Standby add new data file

                        Thanks Hemant, Anar, Karan, Sb, 8080 and Mahir and all

                         

                         

                        By the way.... I am creating two(2) UNDOs and TEMPs, because  I want to switch alternately each other while I shrink the other TS. Because sometimes UNDO gets to big and so is TEMP.

                        Switching back and forth between 2 files will help manage the space.

                         

                        How will this affect the Standby DB?

                        • 10. Re: DG Physical Standby add new data file

                          Thanks Mahir,

                           

                          At Primary DB:

                           

                          SQL> show parameter UNDO

                           

                          NAME                                 TYPE        VALUE

                          ------------------------------------ ----------- ------------------------------

                          undo_management                      string      AUTO

                          undo_retention                       integer     900

                          undo_tablespace                      string      UNDOTBS1

                           

                           

                          At Standby DB:

                           

                          SQL> show parameter UNDO

                           

                          NAME                                 TYPE        VALUE

                          ------------------------------------ ----------- ------------------------------

                          undo_management                      string      AUTO

                          undo_retention                       integer     900

                          undo_tablespace                      string      UNDOTBS1

                           

                           

                          Note, that they have the same config parameter values:

                           

                          So when I got space problem when both UNDOTBS1 and TEMP is blowing up making the filesystem /u49  100% full

                          I want to create new UNDOTBS2 and TEMP2 on a new filesystem  /u50. I will also just create the folder both at primary and standby.

                           

                          Then I do this procedure:

                           

                          How to shrink UNDO tablespace?

                          ===============================

                           

                          The datafile for UNDO tablespace can’t be shrunk as we had issue where the datafile was set to unlimited and it kept on growing to fix the issue one can do the following steps.

                           

                          – UNDOTBS2 is new undo tablespace name

                          SQL> create undo tablespace UNDOTBS2 datafile ‘/u50/oradata/PROD/undotbs2.dbf’ size 1000m;

                           

                          – make the new tablespace to be the undo tablespace

                          SQL> alter system set undo_tablespace=undotbs2;

                           

                          – get the filename of the old undo tablespace which will be shrink so you can reclaim  the space

                          SQL> SELECT file_name FROM dba_data_files WHERE tablespace_name = ‘UNDOTBS1';

                           

                          FILE_NAME

                          ——————————————————————————–

                          /u49/oradata/PROD/undotbs1.dbf

                           

                           

                          SQL> alter database datafile '/u49/oradata/PROD/undotbs1.dbf' resize 500M;


                          (IT THIS ALLOWED)?

                           

                           

                          ==============================

                           

                           

                          HOW TO SHRINK TEMP

                          ------------------------------------

                           

                          SQL> CREATE TEMPORARY TABLESPACE temp2

                          2 TEMPFILE '/u50/oradata/PROD/temp2.dbf' SIZE 5M REUSE

                          3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

                          4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

                           

                          Tablespace created.

                           

                          SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

                           

                          Database altered.

                           

                          SQL> alter database datafile '/u49/oradata/PROD/temp.dbf' resize 500M;

                           

                          (IT THIS ALLOWED)?

                           

                           

                           

                          ##############

                           

                          Is this correct? I only do it on PRIMARY and not on STANDBY?

                           

                          I only create the folder-filesystem at STANDBY?

                           

                          THANKS

                          pK

                          • 11. Re: DG Physical Standby add new data file
                            Mahir M. Quluzade

                            f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

                             

                            Thanks Mahir,

                            You  are welcome!

                            At Primary DB:

                             

                            SQL> show parameter UNDO

                             

                            NAME                                 TYPE        VALUE

                            ------------------------------------ ----------- ------------------------------

                            undo_management                      string      AUTO

                            undo_retention                       integer     900

                            undo_tablespace                      string      UNDOTBS1

                             

                             

                            At Standby DB:

                             

                            SQL> show parameter UNDO

                             

                            NAME                                 TYPE        VALUE

                            ------------------------------------ ----------- ------------------------------

                            undo_management                      string      AUTO

                            undo_retention                       integer     900

                            undo_tablespace                      string      UNDOTBS1

                            if you want database use new UNDO tablespace, then you must change UNDO_TABLESPACE parameter to <NEW UNDO  TABLESPACE>

                            Note, that they have the same config parameter values:

                             

                            So when I got space problem when both UNDOTBS1 and TEMP is blowing up making the filesystem /u49  100% full

                            I want to create new UNDOTBS2 and TEMP2 on a new filesystem  /u50. I will also just create the folder both at primary and standby.

                             

                            Then I do this procedure:

                             

                            How to shrink UNDO tablespace?

                            ===============================

                             

                            The datafile for UNDO tablespace can’t be shrunk as we had issue where the datafile was set to unlimited and it kept on growing to fix the issue one can do the following steps.

                             

                            – UNDOTBS2 is new undo tablespace name

                            SQL> create undo tablespace UNDOTBS2 datafile ‘/u50/oradata/PROD/undotbs2.dbf’ size 1000m;

                             

                            – make the new tablespace to be the undo tablespace

                            SQL> alter system set undo_tablespace=undotbs2;

                             

                            – get the filename of the old undo tablespace which will be shrink so you can reclaim  the space

                            SQL> SELECT file_name FROM dba_data_files WHERE tablespace_name = ‘UNDOTBS1';

                             

                            FILE_NAME

                            ——————————————————————————–

                            /u49/oradata/PROD/undotbs1.dbf

                             

                             

                            SQL> alter database datafile '/u49/oradata/PROD/undotbs1.dbf' resize 500M;



                            Yes, it is also resize to 500M undo datafile on standby datbaase. (Must be STANDBY_FILE_MANAGEMENT=AUTO on standby database)

                             

                             

                             

                            ==============================

                             

                             

                            HOW TO SHRINK TEMP

                            ------------------------------------

                             

                            SQL> CREATE TEMPORARY TABLESPACE temp2

                            2 TEMPFILE '/u50/oradata/PROD/temp2.dbf' SIZE 5M REUSE

                            3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

                            4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

                             

                            Tablespace created.

                             

                            SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

                             

                            Database altered.

                             



                            There will create temporary tablespace TEMP2 and will set default temporary  tablespace TEMP2 on standby database, too.



                            SQL> alter database datafile '/u49/oradata/PROD/temp.dbf' resize 500M;

                             

                            It is not resize temporary  file on standby database. Becuase it is not created yet.

                             

                            Regards

                            Mahir M. Quluzade

                            • 12. Re: DG Physical Standby add new data file

                              Hi Mahir,

                               

                              Both primary and standby have this AUTO

                               

                              SQL> show parameter STANDBY

                               

                              NAME                                 TYPE        VALUE

                              ------------------------------------ ----------- ------------------------------

                              standby_archive_dest                 string      ?/dbs/arch

                              standby_file_management              string      AUTO

                               

                               

                              Thanks

                              • 13. Re: DG Physical Standby add new data file
                                Mahir M. Quluzade

                                It is best practice.

                                 

                                You  are welcome!

                                 

                                Regards

                                Mahir M. Quluzade