11 Replies Latest reply: May 21, 2014 11:52 PM by petra-K RSS

    How to check dataguard

      Hi all,



      Supposing you are a new hire dba in a company, and you are logged in to a PROD database.

      How do you check if the PROD has a dataguard standby database configured or enabled?





        • 1. Re: How to check dataguard

          SQL> select database_role from v$database;





          Role can be either one of the below




          SQL> show parameter log_archive_config

          • 2. Re: How to check dataguard

            Thanks Krish,


            Yesterday I was able to complete the datagaurd standby db setup. I verified the physical standby and it was ok applying logs.



            SQL> alter system switch logfile;



            SQL> select sequence#, first_time, applied from v$archived_log order by sequence#;


            I have seen that logfiles are transported and applied.


            But today when I check our standby, and it was down

            I tried to start it up but I got errors:

            sqlplus / as sysdba


            SQL*Plus: Release Production on Thu May 15 12:50:51 2014

            Copyright (c) 1982, 2011, Oracle.  All rights reserved.


            Connected to an idle instance.


            SQL> startup nomount;

            ORACLE instance started.


            Total System Global Area  521936896 bytes

            Fixed Size                  2223080 bytes

            Variable Size             318768152 bytes

            Database Buffers          197132288 bytes

            Redo Buffers                3813376 bytes

            SQL> alter database mount standby database;

            alter database mount standby database


            ERROR at line 1:

            ORA-00214: control file '/u21/ORACLE/ORADATA/BNCTUAT2/control01.ctl' version

            17143 inconsistent with file '/u21/fast_recovery_area/BNCTUAT2/control02.ctl'

            version 17141



            Why are my controlfiles unsync? Which one should I retain? Why is the dataguard setup miss to update the other?




            • 3. Re: How to check dataguard

              standby control file can be recreated

              1) Shutdown the Standby Database:

              2)Connect to the Primary Database as SYS:

              3)Create a new Physical Standby Controlfile from the Primary:

              SQL> alter database create standby controlfile as '<File-Specification>';

              4) Copy the new created Standby Controlfile to the Standby server and replace the current Standby Controlfile with the new created one.

              5) Mount standby database (You may need to check init parameter for control file)

              SQL> startup mount



              6) If the File-Structure is different between Primary and Standby Database,  you could either rename the File-Location(s) in the new Standby Controlfile:

              SQL> alter database rename file '<old Filespecification or file#>' to '<new Filespecification>';

              or set db_file_name_convert or/and log_file_name_convert init parameters in the standby database.

              • 4. Re: How to check dataguard


                • 5. Re: How to check dataguard

                  Hi All,



                  How can I resolve long dbnames for standby database?


                  SQL> startup nomount pfile=initBNCTUATDR.ora

                  ORA-01127: database name 'BNCTUATDR' exceeds size limit of 8 characters




                  • 6. Re: How to check dataguard
                    Sunny kichloo

                    Error you are getting is obvious why you are using long name shorten the name and it will work.

                    • 7. Re: How to check dataguard

                      Well , I think I see one here in the old setup done by the previous dba 9 char dbname. Let me check again.

                      • 8. Re: How to check dataguard

                        That's not possible for previous dba


                        • 9. Re: How to check dataguard

                          You can check alert log ... see the log flowing into standby database.

                          Simply check following parameters,


                          LOG_ARCHIVE_DEST_2 [If service name there - then good chance standby is present]

                          LOG_ARCHIVE_DEST_2_state = enable



                          • 10. Re: How to check dataguard

                            You can use below query in the primary side. if you see lns process dataguard has used in system. İf Dataguard not installed in the system query did not return any row


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



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

                            ARCH      CLOSING            9286          0             0 ARCH

                            ARCH      CLOSING            9284          0             0 ARCH

                            ARCH      CLOSING            9239          0             0 ARCH

                            ARCH      CLOSING            9285          0             0 ARCH

                            LNS       WRITING            9287          0             0 LNS

                            • 11. Re: How to check dataguard

                              I thank you all