6 Replies Latest reply: Jun 5, 2014 2:43 AM by yasinyazici RSS

    document on creation of Data guard for 11.2.0.4 DB ( from RAC (primary) to single node (standby).

    User519903

      Hi,

       

      Could someone please send me a document on creation of Data guard for 11.2.0.4 DB ( from RAC (primary) to single node (standby).

       

      Thank you very much

        • 3. Re: document on creation of Data guard for 11.2.0.4 DB ( from RAC (primary) to single node (standby).
          Srini Chavali-Oracle

          Moved to the data guard forum - pl post future questions in an appropriate forum

          • 4. Re: document on creation of Data guard for 11.2.0.4 DB ( from RAC (primary) to single node (standby).
            User519903

            Thank you very much for the suggestion.

             

            Inadvertently, I posted the query in a wrong forum.

             

            Thank.

            • 5. Re: document on creation of Data guard for 11.2.0.4 DB ( from RAC (primary) to single node (standby).
              BPeaslandDBA

              Creating a Physical Standby Database

               

              The above contains the steps to create a single-instance physical standby. The fact that the primary is RAC has almost no bearing on the process. The only thing I see that would be different is if you use the primary's SPFILE to create one for the standby. You will want to remove the cluster-specific items from the file before using the in standby.

               

              HTH,

              Brian

              • 6. Re: document on creation of Data guard for 11.2.0.4 DB ( from RAC (primary) to single node (standby).
                yasinyazici

                Hi

                I installed the Dataguard before as you mentioned in the your question.I paste steps of installation here because of I can not attached file.

                 

                **************************Dataguard installation(Primary RAC, Standby Single İnstance)*******************

                 

                1) we need to add standby redo log file.we did this in the primary site on the any instance.

                http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#SBYDB00426

                 

                ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

                GROUP 11 SIZE 50M,

                GROUP 12 SIZE 50M,

                GROUP 13 size 50M;

                 

                ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

                GROUP 8 SIZE 50M,

                GROUP 9 SIZE 50M,

                GROUp 10 size 50M;

                 

                 

                2) we create dump directory in the standby site.

                 

                Dump file için aşagıdaki directory oluşturuldu.

                 

                mkdir -p $ORACLE_BASE/admin/ODMTEST/adump

                 

                3)We modify pfile for standby database and we use pfile so we must add following parmeter to pfile to take affect after restart database again.

                we open instance 1 so we must comment line which belongs to instance 2

                 

                log_archive_config='dg_config=(ODMTESTSTDBY,ODMTEST)' scope=both  --> (standbydatabase_name,database_unique_name)

                log_archive_dest_2='service=ODMTESTSTDBY LGWR async valid_for=(online_logfile,primary_role) db_unique_name=ODMTEST' scope=both;

                standby_file_management=AUTO scope=both;

                fal_server=ODMTEST scope=both;

                 

                NOTE:I want to inform you about important point. you should note that cluster_database parameter. İf standby database is single instance.

                This parameter should be false in addtion to this ı used ASM.

                *************************************************Standby pfile.ora

                 

                /oracle/ora11g> vi dbs/initODMTEST1.ora

                *.audit_file_dest='/oracle/admin/ODMTEST/adump'

                *.audit_trail='db'

                *.cluster_database=false

                *.compatible='11.2.0.4.0'

                *.control_files='+DG_RMAN

                *.db_block_size=8192

                *.db_create_file_dest='+DG_RMAN'

                *.db_domain=''

                *.db_name='ODMTEST'

                *.db_recovery_file_dest='+DG_RMAN'

                *.db_recovery_file_dest_size=53687091200

                *.diagnostic_dest='/oracle'

                *.dispatchers='(PROTOCOL=TCP) (SERVICE=ODMTESTXDB)'

                ODMTEST1.instance_number=1

                #ODMTEST2.instance_number=2

                *.open_cursors=300

                *.pga_aggregate_target=1248854016

                *.processes=150

                *.remote_login_passwordfile='exclusive'

                *.sga_target=3746562048

                *.standby_file_management='AUTO'

                ODMTEST1.thread=1

                #ODMTEST2.thread=2

                ODMTEST1.undo_tablespace='UNDOTBS1'

                #ODMTEST2.undo_tablespace='UNDOTBS2'

                 

                *.db_unique_name=ODMTESTSTDBY

                *.log_archive_config='dg_config=(ODMTESTSTDBY,ODMTEST)'

                *.log_archive_dest_2='service=ODMTEST async valid_for=(online_logfile,primary_role) db_unique_name=ODMTEST'

                *.log_archive_dest_state_2='ENABLE'

                *.fal_server=ODMTEST

                ~

                4)We move password file from  one node of RAC that which instance you want open

                 

                5) configure tnsnames.

                 

                ####Primary site tnsnames entry. add this  to tnsnames.ora file of RAC nodes

                ODMTESTSTDBY =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.3)(PORT = 1521))

                    (CONNECT_DATA =

                      (SERVER = DEDICATED)

                      (SERVICE_NAME = ODMTESTSTDBY)

                    )

                  )

                 

                 

                 

                ######Standby database tnsnames.ora file

                 

                ODMTESTSTDBY =

                  (DESCRIPTION =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.3)(PORT = 1521))

                    (CONNECT_DATA =

                      (SERVER = DEDICATED)

                      (SERVICE_NAME = ODMTESTSTDBY)

                    )

                  )

                 

                 

                ODMTEST =

                  (DESCRIPTION =

                    (FAILOVER=ON)

                    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))

                    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.2)(PORT = 1521))

                    (CONNECT_DATA =

                      (SERVER = DEDICATED)

                      (SERVICE_NAME = ODMTEST)

                    )

                  )

                 

                                   

                6)we need to set  some parameter in the primary site. we must do this in the both node.

                 

                SQL>alter system set log_archive_config='dg_config=(ODMTEST,ODMTESTSTDBY)' scope=memory  --> (standbydatabase_name,database_unique_name)

                 

                SQL>alter system set log_archive_dest_2='service=ODMTESTSTDBY LGWR async valid_for=(online_logfile,primary_role) db_unique_name=ODMTESTSTDBY' scope=memory;

                 

                SQL>alter system set standby_file_management=AUTO scope=memory;

                 

                SQL>alter system set fal_server=ODMTESTSTDBY scope=memory;

                 

                ####we add following entry pfile in the nodes of RAC.

                log_archive_config='dg_config=(ODMTEST,ODMTESTSTDBY)'

                log_archive_dest_2='service=ODMTESTSTDBY LGWR async valid_for=(online_logfile,primary_role) db_unique_name=ODMTESTSTDBY' scope=memory

                standby_file_management=AUTO scope=memory

                fal_server=ODMTESTSTDBY scope=memory

                 

                7) we create controlfile for standby database from primary site and  take full backup.

                we move this file to standby site.

                 

                RMAN>backup current controlfile for standby format '/tmp/standbycontrol.ctl';

                RMAN>backup database;

                 

                 

                8)we start instance nomount mode with above pfile and restore controlfile

                 

                RMAN>restore controlfile  from '/tmp/standbycontrol.ctl';

                 

                9) it hase taken database mount mode

                 

                SQL>alter database mount standby database;

                 

                 

                10) we will restore database.

                 

                /oracle/ora11g> rman target /

                 

                Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 14 15:33:26 2014

                 

                Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

                 

                connected to target database: ODMTEST (DBID=1978656170, not open)

                 

                RMAN> catalog start with '/oracle/backup/';

                 

                Starting implicit crosscheck backup at 14-JAN-14

                using target database control file instead of recovery catalog

                allocated channel: ORA_DISK_1

                channel ORA_DISK_1: SID=131 device type=DISK

                Finished implicit crosscheck backup at 14-JAN-14

                 

                Starting implicit crosscheck copy at 14-JAN-14

                using channel ORA_DISK_1

                Finished implicit crosscheck copy at 14-JAN-14

                 

                searching for all files in the recovery area

                cataloging files...

                no files cataloged

                 

                searching for all files that match the pattern /oracle/backup/

                 

                List of Files Unknown to the Database

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

                File Name: /oracle/backup/ODMTESTARC_08ou29mj_1_1.bkp

                File Name: /oracle/backup/ODMTESTARC_05ou29m7_1_1.bkp

                File Name: /oracle/backup/ODMTEST_07ou29mg_1_1.bkp

                File Name: /oracle/backup/ODMTEST_06ou29m9_1_1.bkp

                 

                Do you really want to catalog the above files (enter YES or NO)? yes

                cataloging files...

                cataloging done

                 

                List of Cataloged Files

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

                File Name: /oracle/backup/ODMTESTARC_08ou29mj_1_1.bkp

                File Name: /oracle/backup/ODMTESTARC_05ou29m7_1_1.bkp

                File Name: /oracle/backup/ODMTEST_07ou29mg_1_1.bkpG

                File Name: /oracle/backup/ODMTEST_06ou29m9_1_1.bkp

                 

                RMAN>

                 

                RMAN> restore database;

                 

                Starting restore at 14-JAN-14

                using channel ORA_DISK_1

                 

                channel ORA_DISK_1: starting datafile backup set restore

                channel ORA_DISK_1: specifying datafile(s) to restore from backup set

                channel ORA_DISK_1: restoring datafile 00001 to +DG_RMAN/odmtest/datafile/system.328.836736205

                channel ORA_DISK_1: restoring datafile 00002 to +DG_RMAN/odmtest/datafile/sysaux.469.836736207

                channel ORA_DISK_1: restoring datafile 00003 to +DG_RMAN/odmtest/datafile/undotbs1.324.836736207

                channel ORA_DISK_1: restoring datafile 00004 to +DG_RMAN/odmtest/datafile/users.323.836736207

                channel ORA_DISK_1: restoring datafile 00005 to +DG_RMAN/odmtest/datafile/undotbs2.271.836736315

                channel ORA_DISK_1: reading from backup piece /oracle/backup/ODMTEST_06ou29m9_1_1.bkp

                channel ORA_DISK_1: piece handle=/oracle/backup/ODMTEST_06ou29m9_1_1.bkp tag=TAG20140114T151832

                channel ORA_DISK_1: restored backup piece 1

                channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

                Finished restore at 14-JAN-14

                 

                11) we set following parameter in the primary site to log shipping from primary to standby.in both nodes

                 

                alter system set log_archive_dest_state_2=enable;

                 

                12)After database restored successfully we start redo apply as following.

                 

                SQL>alter database recover managed standby database  disconnect;

                                   

                                   

                ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

                 

                we check redo gap between primary and standby

                 

                primary site

                SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

                 

                standby site

                SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#

                 

                and we can check standby process with following

                 

                SQL>select process,status,thread#,sequence#,delay_mins,active_agents,client_process from v$managed_standby;

                                          

                                      

                I hope this help you

                yasin