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

    How to check dataguard

      Hi all,

       

      11.2.0.3

       

      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?

       

       

      Thanks,

      pK

        • 1. Re: How to check dataguard
          Krishna-Oracle

          SQL> select database_role from v$database;

          DATABASE_ROLE

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

          PRIMARY

           

          Role can be either one of the below

          LOGICAL STANDBY,PHYSICAL STANDBY,PRIMARY

           

          Also

          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.

             

            @primary

            SQL> alter system switch logfile;

             

            @standby

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

             

            Thanks,

            pK

            • 3. Re: How to check dataguard
              Krishna-Oracle

              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

                graci!

                • 5. Re: How to check dataguard

                  Hi All,

                   

                  11.2.0.3

                  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

                   

                   

                  Thanks,

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

                        That's not possible for previous dba

                        DB_NAME

                        • 9. Re: How to check dataguard
                          user8914886

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

                          Simply check following parameters,

                          LOG_ARCHIVE_CONFIG

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

                          LOG_ARCHIVE_DEST_2_state = enable

                           

                          Thanks

                          • 10. Re: How to check dataguard
                            yasinyazici

                            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;

                             

                            PROCESS   STATUS        SEQUENCE# DELAY_MINS ACTIVE_AGENTS CLIENT_P

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

                            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