4 Replies Latest reply: Jan 8, 2014 4:49 AM by vk82 RSS

    Creation of Logical Standby Database Using RMAN ACTIVE DATABASE COMMAND

    vk82

      Hi All,

       

      I am in confusion how to create logical standby database from primary database using rman active database command.

       

      What i did:-

       

      Create primary database on machine 1 on RHEL 5 with Oracle 11gR2

       

      Create standby database on machine 2 on RHEL 5 With Oracle 11gR2 from primary using RMAN active database command

       

      Trying to create logical standby database on machine 3 on RHEL 5 with Oracle 11gR2 using RMAN active database command from primary.

       

       

      The point which confuse me is to start the logical standby in nomount mode on machine 3 with which pfile like i create the pfile for standby database do i need to create the pfile for logical standby db.

       

       

       

      I done the creation of logical standby database by converting physical standby to logical standby database

       

      I am following the below mentioned doc for the same:

       

      Creating a physical and a logical standby database in a DR environment | Chen Guang's Blog

       

      Kindly guide me how to work over the same or please provide me the steps of the same.

       

      Thanks in advance.

        • 1. Re: Creation of Logical Standby Database Using RMAN ACTIVE DATABASE COMMAND
          CKPT

          The point which confuse me is to start the logical standby in nomount mode on machine 3 with which pfile like i create the pfile for standby database do i need to create the pfile for logical standby db.

           

          You must start with PFILE or SPFILE in nomount before you perform duplicate, If you use SPFILE then in duplicate script you can add more configuration parameters such as LOG_FILE_NAME_CONVERT, SGA_MAX_SIZE/TARGET so on..

          • 2. Re: Creation of Logical Standby Database Using RMAN ACTIVE DATABASE COMMAND
            vk82

            Thanks for your reply

             

            I already started the logical standby database with pfile in nomount mode. And successfully completed the duplication of database. by mentioning the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameter.

             

            But i am not able to receive the logs on the above mentioned blog i run the sql command to check the logs but getting "no rows selected"

             

             

            My primary database pfile is:

             

            pc01prmy.__db_cache_size=83886080

            pc01prmy.__java_pool_size=12582912

            pc01prmy.__large_pool_size=4194304

            pc01prmy.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

            pc01prmy.__pga_aggregate_target=79691776

            pc01prmy.__sga_target=239075328

            pc01prmy.__shared_io_pool_size=0

            pc01prmy.__shared_pool_size=134217728

            pc01prmy.__streams_pool_size=0

            *.audit_file_dest='/u01/app/oracle/admin/pc01prmy/adump'

            *.audit_trail='db'

            *.compatible='11.1.0.0.0'

            *.control_files='/u01/app/oracle/oradata/PC01PRMY/controlfile/o1_mf_91g3mdtr_.ctl','/u01/app/oracle/flash_recovery_area/PC01PRMY/controlfile/o1_mf_91g3mf6v_.ctl'

            *.db_block_size=8192

            *.db_create_file_dest='/u01/app/oracle/oradata'

            *.db_domain=''

            *.db_file_name_convert='/u01/app/oracle/oradata/PC01SBY/datafile','/u01/app/oracle/oradata/PC01PRMY/datafile'

            *.db_name='pc01prmy'

            *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

            *.db_recovery_file_dest_size=2147483648

            *.diagnostic_dest='/u01/app/oracle'

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

            *.fal_client='PC01PRMY'

            *.fal_server='PC01SBY'

            *.log_archive_config='DG_CONFIG=(pc01prmy,pc01sby,pc01ls)'

            *.log_archive_dest_1='LOCATION=/u01/app/oracle/flash_recovery_area/PC01PRMY/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pc01prmy'

            *.log_archive_dest_2='SERVICE=pc01sby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pc01sby'

            *.log_archive_dest_3='SERVICE=pc01ls LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=pc01ls'

            *.log_archive_dest_state_1='ENABLE'

            *.log_archive_dest_state_2='DEFER'

            *.log_archive_dest_state_3='DEFER'

            *.log_archive_max_processes=30

            *.log_file_name_convert='/u01/app/oracle/oradata/PC01SBY/onlinelog','/u01/app/oracle/oradata/PC01PRMY/onlinelog'

            *.open_cursors=300

            *.pga_aggregate_target=78643200

            *.processes=150

            *.remote_login_passwordfile='EXCLUSIVE'

            *.sga_target=236978176

            *.undo_tablespace='UNDOTBS1'

             

             

            My logical standby pfile is:-

             

            pc01ls.__db_cache_size=92274688

            pc01ls.__java_pool_size=12582912

            pc01ls.__large_pool_size=4194304

            pc01ls.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

            pc01ls.__pga_aggregate_target=79691776

            pc01ls.__sga_target=239075328

            pc01ls.__shared_io_pool_size=0

            pc01ls.__shared_pool_size=125829120

            pc01ls.__streams_pool_size=0

            *.audit_file_dest='/u01/app/oracle/admin/pc01ls/adump'

            *.audit_trail='db'

            *.compatible='11.1.0.0.0'

            *.control_files='/u01/app/oracle/oradata/PC01LS/controlfile/o1_mf_91g3mdtr_.ctl','/u01/app/oracle/flash_recovery_area/PC01LS/controlfile/o1_mf_91g3mf6v_.ctl'

            *.db_block_size=8192

            *.db_create_file_dest='/u01/app/oracle/oradata'

            *.db_domain=''

            *.db_file_name_convert='/u01/app/oracle/oradata/PC01SBY/datafile','/u01/app/oracle/oradata/PC01PRMY/datafile'

            *.db_name='pc01prmy'

            *.db_unique_name='pc01ls'

            *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

            *.db_recovery_file_dest_size=2147483648

            *.diagnostic_dest='/u01/app/oracle'

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

            *.log_archive_config='DG_CONFIG=(pc01prmy,pc01sby,pc01ls)'

            *.log_archive_dest_1='LOCATION=/u01/app/oracle/flash_recovery_area/PC01PRMY/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pc01prmy'

            *.log_archive_dest_2='LOCATION=/u01/app/oracle/flash_recovery_area/PC01LS/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pc01ls'

            *.log_archive_dest_3='SERVICE=pc01ls LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=pc01ls'

            *.log_archive_dest_state_1='ENABLE'

            *.log_archive_dest_state_2='ENABLE'

            *.log_archive_max_processes=30

            *.log_file_name_convert='/u01/app/oracle/oradata/PC01SBY/onlinelog','/u01/app/oracle/oradata/PC01PRMY/onlinelog'

            *.open_cursors=300

            *.pga_aggregate_target=78643200

            *.processes=150

            *.remote_login_passwordfile='EXCLUSIVE'

            *.sga_target=236978176

            *.undo_tablespace='UNDOTBS1'

             

            Kindly advice over the same

            • 3. Re: Creation of Logical Standby Database Using RMAN ACTIVE DATABASE COMMAND
              CKPT

              Ok, So its time to troubleshoot.

              From primary get below queries outputs.

               

              set linesize 120

              select ds.dest_id id

              , ad.status

              , ds.database_mode db_mode

              , ad.archiver type

              , ds.recovery_mode

              , ds.protection_mode

              , ds.standby_logfile_count "SRLs"

              , ds.standby_logfile_active active

              , ds.archived_seq#

              from v$archive_dest_status ds

              , v$archive_dest ad

              where ds.dest_id = ad.dest_id

              and ad.status != 'INACTIVE'

              order by

              ds.dest_id;

               

              2)

               

              col severity for a15

              col message for a70

              col timestamp for a20

              select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;

               

              Change destination ID if its not configured in dest_2

               

              3) share the alert log from primary if you have any errors seen

               

               

              4) From standby also check errors after you start MRP if anything.

              • 4. Re: Creation of Logical Standby Database Using RMAN ACTIVE DATABASE COMMAND
                vk82

                physical standby database and start MRP (Managed Recovery Process):-

                 

                export ORACLE_SID=DB11R2LS

                sqlplus / as sysdba

                 

                idle> shutdown immediate;

                idl> starup

                Database opened.

                 

                sys@db11r2ls> recover managed standby database using current logfile disconnect;

                Media recovery complete.

                 

                [oracle@gchen-pc dbs]$ ps -ef |grep mrp

                oracle    8346     1  0 00:45 ?        00:00:01 ora_mrp0_DB11R2LS

                oracle    8370  5019  0 00:47 pts/2    00:00:00 grep mrp

                 

                Step 5.4) Make sure this physical standby is in Sync with primary databaseUse following query on Standby to check:

                 

                SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"

                FROM

                (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

                (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

                WHERE

                ARCH.THREAD# = APPL.THREAD#

                ORDER BY 1;

                 

                    Thread Last Sequence Received Last Sequence Applied

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

                         1                    187                   187

                 

                 

                I created physical standby on machine 2 before creating logical standby db on machine 3. Now i enable the LOG_ARCHIVE_DEST_STATE_2=ENABLE AND LOG_ARCHIVE_DEST_STATE_3=ENABLE. I check on physical standby archive logs are received and applied properly.

                 

                But how to check on logical standby database with above mentioned commands or need to do something else. OR SHALL I NEED TO CREATE PHYSICAL STANDBY DATABASE ON MACHINE 3 AND CONVERT IT TO LOGICAL STANDBY DATABASE RUNNING ON MACHINE 3

                 

                Ok i done. The problem is sorted out the reason why i am getting the above mentioned problem is due to some misconfiguration of parameter