11 Replies Latest reply: Apr 10, 2013 6:14 AM by Sunny kichloo RSS

    Data Guard parameter

    Thunder2777
      HI There

      I have recently configure Data guard with Database 10g (10.2.0.4-64 bits) on Windows 2007 server.

      My Data Gurad Configuration show Success status with 2 databases on same (or local) location.

      My questions are

      1-When I query SHOW PARAMETER LOG ARCH

      DG_CONFIG(PRMDB)

      ONLY 1 (PRIMARY DATABASE IS DISPLAYED ONLY NOT 2 DTATABASES
      e.g. DG_CONFIG(PRMDB, STLDB)

      2- How to check the log applied interval or time (either transaction by transaction, timing etc)

      Kind Regards
      Thunder2777
        • 1. Re: Data Guard parameter
          Sunny kichloo
          Is any of log_archive_dest_ parameter in your system has Service name of Standby database.


          Execute below query on both the server to check archive differences


          Primary

          select max(sequence#) from v$archived_log;


          Standby

          select max(sequence#) from v$log_history;
          • 2. Re: Data Guard parameter
            mseberg
            Hello again;

            Based on your other question I'm concerned your setup has an issue. To rule out the listener and tsnname on both side can you run : ( and confirm results )
            From Primary
            
            connect sys/password@standby as sysdba
            
            From Standby
            
            connect sys/password@primary as sysdba
            Also can you post the INIT files from both the Primary and the Standby side? Between { code } top and bottom without the space for formating.

            Will review ASAP

            Best Regards

            mseberg
            • 3. Re: Data Guard parameter
              Thunder2777
              THANKS FOR QUICK RESPONSE.

              HERE IS THE OUTPUT WHEN I EXECUTED IT ON PRIMARY AND STANDBY DB



              select max(sequence#) from v$archived_log;

              MAX(SEQUENCE#)
              --------------
              52

              select max(sequence#) from v$log_history;

              MAX(SEQUENCE#)
              --------------
              52
              • 4. Re: Data Guard parameter
                Sunny kichloo
                Please share your init files as mentioned by mseberg in previous thread.

                So that we can check your configurations are correct
                • 5. Re: Data Guard parameter
                  Thunder2777
                  THANKS FOR REPLY


                  BOTH TNSNAMES.ORA AND LISTENER.ORA IN SAME DIRECTORY

                  C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

                  TNSNAMES.ORA

                  # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
                  # Generated by Oracle configuration tools.

                  PRMDB =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = CV-SERVER)(PORT = 1521))
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = PRMDB)
                  )
                  )

                  STLDB =
                  (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = CV-SERVER)(PORT = 1521))
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = STLDB)
                  )
                  )

                  LISTENER.ORA

                  # listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
                  # Generated by Oracle configuration tools.

                  SID_LIST_LISTENER =
                  (SID_LIST =
                  (SID_DESC =
                  (SID_NAME = PLSExtProc)
                  (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                  (PROGRAM = extproc)
                  )
                  (SID_DESC =
                  (SID_NAME = STLDB)
                  (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                  )
                  )

                  # PRIMARY DATABASE SPFILE

                  SPFILEPRMDB.ORA

                  prmdb.__db_cache_size=922746880
                  prmdb.__java_pool_size=16777216
                  prmdb.__large_pool_size=16777216
                  prmdb.__shared_pool_size=285212672
                  prmdb.__streams_pool_size=0
                  *.archive_lag_target=0
                  *.audit_file_dest='C:\oracle\product\10.2.0\admin\PRMDB\adump'
                  *.background_dump_dest='C:\oracle\product\10.2.0\admin\PRMDB\bdump'
                  *.compatible='10.2.0.3.0'
                  *.control_files='C:\oracle\product\10.2.0\oradata\PRMDB\control01.ctl','C:\oracle\product\10.2.0\oradata\PRMDB\control02.ctl','C:\oracle\product\10.2.0\oradata\PRMDB\control03.ctl'
                  *.core_dump_dest='C:\oracle\product\10.2.0\admin\PRMDB\cdump'
                  *.db_block_size=8192
                  *.db_domain=''
                  *.db_file_multiblock_read_count=16
                  *.db_name='PRMDB'
                  *.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
                  *.db_recovery_file_dest_size=2147483648
                  *.dg_broker_start=TRUE
                  *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRMDBXDB)'
                  *.job_queue_processes=10
                  *.log_archive_config='dg_config=(stldb)'
                  *.log_archive_dest_1='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=CV-SERVER)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stldb_XPT)(INSTANCE_NAME=stldb)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stldb" register net_timeout=180 valid_for=(online_logfile,primary_role)'
                  *.log_archive_dest_state_1='ENABLE'
                  prmdb.log_archive_format='ARC%S_%R.%T'
                  *.log_archive_max_processes=2
                  *.log_archive_min_succeed_dest=1
                  prmdb.log_archive_trace=0
                  *.open_cursors=300
                  *.pga_aggregate_target=417333248
                  *.processes=150
                  *.remote_login_passwordfile='EXCLUSIVE'
                  *.sga_target=1253048320
                  prmdb.standby_archive_dest=''
                  *.standby_file_management='MANUAL'
                  *.undo_management='AUTO'
                  *.undo_tablespace='UNDOTBS1'
                  *.user_dump_dest='C:\oracle\product\10.2.0\admin\PRMDB\udump'

                  # STANDBY DATABASE INITSTLDB
                  INITSTLDB.ORA

                  STLDB.__db_cache_size=922746880

                  STLDB.__java_pool_size=16777216
                  STLDB.__large_pool_size=16777216

                  STLDB.__shared_pool_size=285212672

                  STLDB.__streams_pool_size=0
                  *.audit_file_dest='C:\oracle\product\10.2.0\admin\STLDB\adump'

                  *.background_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\bdump'

                  *.compatible='10.2.0.3.0'

                  *.control_files='D:\ORACLE\ORADATA\STLDB\CONTROLFILE\control01.ctl'
                  *.core_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\cdump'

                  *.db_block_size=8192

                  *.db_domain=''

                  *.db_file_multiblock_read_count=16

                  *.db_name='PRMDB'

                  *.db_unique_name='STLDB'

                  *.db_recovery_file_dest='D:\oracle\FRA'

                  *.db_recovery_file_dest_size=2147483648

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

                  *.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
                  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                  DB_UNIQUE_NAME=STLDB'
                  *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
                  *.job_queue_processes=10

                  *.open_cursors=300

                  *.pga_aggregate_target=417333248

                  *.processes=150

                  *.remote_login_passwordfile='EXCLUSIVE'

                  *.sga_target=1253048320

                  *.undo_management='MANUAL'

                  *.undo_tablespace='UNDOTBS1'

                  *.user_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\udump'

                  THANKS
                  • 6. Re: Data Guard parameter
                    mseberg
                    Hello;

                    Reviewing will post as an edit here.

                    Data Guard parameter setup is incomplete. You have several issues.

                    Primary

                    log_archive_dest_2 = missing
                    log_archive_dest_state_2 = missing
                    standby_file_management = would set to AUTO
                    log_archive_config='dg_config=(stldb)' = set wrong expecting two enties not just strldb
                    FAL_SERVER= missing
                    FAL_CLIENT= missing

                    dg_broker_start=TRUE ( would set to FALSE until I get stable working system )

                    DB_UNIQUE_NAME = missing


                    Check my setup notes here :

                    http://www.visi.com/~mseberg/data_guard_on_oracle_10_step_by_step.html

                    Example
                    log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
                    
                    log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY' 
                    In the example above log_archive_dest_1 is going to the FRA of the Primary database. While log_archive_dest_2 is going to the Service for the remote, Standby database.


                    In this example both LOG_ARCHIVE_DEST_STATE_1 and LOG_ARCHIVE_DEST_STATE_2 are set to enable


                    For a simple Data Guard setup check these parameters :

                    FAL_SERVER
                    FAL_CLIENT
                    STANDBY_FILE_MANAGEMENT
                    DB_UNIQUE_NAME
                    DB_FILE_NAME_CONVERT
                    LOG_FILE_NAME_CONVERT
                    LOG_ARCHIVE_DEST_1
                    LOG_ARCHIVE_DEST_2
                    LOG_ARCHIVE_DEST_STATE_1
                    LOG_ARCHIVE_DEST_STATE_2
                    LOG_ARCHIVE_MAX_PROCESSES


                    Some of these you may not need for example DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT if the remote directory structure matches the Primary site.

                    The COMPATIBLE parameter need to be set the same on both databases. REMOTE_LOGIN_PASSWORDFILE is generally set to 'EXCLUSIVE'.

                    For Oracle 10 review document B14239-05 Chapter 3 Creating a Physical Standby Database


                    Best Regards

                    mseberg

                    Edited by: mseberg on Apr 9, 2013 8:06 AM

                    Edited by: mseberg on Apr 9, 2013 8:42 AM

                    Edited by: mseberg on Apr 9, 2013 8:53 AM
                    • 7. Re: Data Guard parameter
                      Thunder2777
                      Thanks MSEBERG

                      When i perform the the following changes in PRIMARY DB SPFILE

                      *.FAL_SERVER=STANDBY
                      *.FAL_CLIENT=PRIMARY
                      *.DB_FILE_NAME_CONVERT='STLDB','PRMDB'
                      *.LOG_FILE_NAME_CONVERT='STLDB','PRMDB'
                      *.log_archive_dest_1='LOCATION= C:\oracle\product\10.2.0\flash_recovery_area\PRMDB\ARCHIVELOG
                      VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRMDB'
                      *.log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC
                      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STLDB'
                      *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
                      *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
                      *.LOG_ARCHIVE_MAX_PROCESSES=30
                      PRIMARY.standby_archive_dest = 'C:\oracle\product\10.2.0\flash_recovery_area\PRMDB\ARCHIVELOG'

                      AND PERFORM

                      SHUTDOWN IMMEDIATE;
                      STARTUP;

                      THE RESULT: ORA-01078: failure in processing system parameters
                      LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITPRMDB.ORA'

                      WHAT TO DO??????
                      • 8. Re: Data Guard parameter
                        Sunny kichloo
                        Is your parameter file present at below mentioned locations

                        C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE
                        • 9. Re: Data Guard parameter
                          mseberg
                          Hello;

                          Would check what Sunny said. You can also start NOMOUNT from your PFILE and recreate the SPFILE from the pfile. Make sure you PFILE has no comments in it.

                          Recreate example ( Linux )

                          http://www.visi.com/~mseberg/general/fast_spfile_from_pfile.html

                          h2. Update

                          This does not appear to be correct :
                          PRIMARY.standby_archive_dest = 'C:\oracle\product\10.2.0\flash_recovery_area\PRMDB\ARCHIVELOG'
                          PRIMARY.standby_archive_dest syntax could cause your ORA-01078 LRM-00109. Would remove "PRIMARY."

                          Also double check your ORACLE_SID before startup

                          Best Regards

                          mseberg

                          Edited by: mseberg on Apr 10, 2013 7:14 AM
                          • 10. Re: Data Guard parameter
                            Thunder2777
                            THANKS SUNNY

                            C:\oracle\product\10.2.0\db_1\database\SPFILEPRMDB.ORA

                            WHERE PRMDB IS THE NAME OF PRIMARY DB.
                            • 11. Re: Data Guard parameter
                              Sunny kichloo
                              IF you have spfile why it is taking pfile as mentioned in your error

                              LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITPRMDB.ORA


                              Are you using normal startup command for starting database or something else??

                              Most import have you checked on the name of your spfile.Is there some spaces in the name of your files.