Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Create and configure logical Standby Database 10g PART II

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Posts
    120

    Create and configure logical Standby Database 10g PART II

    Continued From : Create-and-configure-logical-Standby-Database-10g-PART-I


    2. Create a New Password File for Logical Standby
    Because the conversion process changes the database name for the logical standby database (that was originally set with the DB_NAME initialization parameter), you must re-create the password file.

    [oracle@vmlinux2 ~]$ cd $ORACLE_HOME/dbs
    [oracle@vmlinux2 dbs]$ rm orapwstby
    [oracle@vmlinux2 dbs]$ orapwd file=orapwstby password=MySysPassword

    3. Adjust Initialization Parameters for the Logical Standby Database
    Modify the log archive destination initialization parameters for the logical standby database as follows:

    # ---[ Log Archive Destination Parameters (Logical Standby) ] --- #

    LOG_ARCHIVE_DEST_1=
    'location=use_db_recovery_file_dest valid_for=(online_logfiles,all_roles) db_unique_name=stby'

    LOG_ARCHIVE_DEST_2=
    'service=prod.info
    valid_for=(online_logfiles,primary_role) db_unique_name=prod'

    LOG_ARCHIVE_DEST_3=
    'location=/u04/oracle/oraarch/STBY valid_for=(standby_logfiles,standby_role) db_unique_name=stby'
    LOG_ARCHIVE_DEST_STATE_1='enable'
    LOG_ARCHIVE_DEST_STATE_2='enable'
    LOG_ARCHIVE_DEST_STATE_3='enable'
    To support role transition, modify the log archive destination initialization parameters for the primary database as well:
    # ---[ Log Archive Destination Parameters (Primary) ] --- #
    LOG_ARCHIVE_DEST_1=
    'location=use_db_recovery_file_dest valid_for=(online_logfiles,all_roles) db_unique_name=prod'
    LOG_ARCHIVE_DEST_2=
    'service=stby.info
    valid_for=(online_logfiles,primary_role) db_unique_name=stby'
    LOG_ARCHIVE_DEST_3=
    'location=/u04/oracle/oraarch/PROD valid_for=(standby_logfiles,standby_role) db_unique_name=prod'
    LOG_ARCHIVE_DEST_STATE_1='enable'
    LOG_ARCHIVE_DEST_STATE_2='enable'
    LOG_ARCHIVE_DEST_STATE_3='enable'

    The DB_FILE_NAME_CONVERT Initialization Parameter
    The DB_FILE_NAME_CONVERT initialization parameter is not honored once a physical standby database is converted to a logical standby database. This can be a problem, for example, when adding a non-OMF datafile to the primary database and the datafile paths are different between the primary and standby.

    4. Mount the Logical Standby Database
    On the logical standby database, shutdown the instance and issue the STARTUP MOUNT. Do not open the database; it should remain closed to user access until later in the creation process.

    SQL> shutdown;
    SQL> startup mount;

    5. Open the Logical Standby Database
    To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:

    SQL> alter database open resetlogs;

    6. Start SQL Apply
    Issue the following statement to start real-time apply on the logical standby database to immediately apply redo data from the standby redo log files to the logical standby database:

    SQL> alter database start logical standby apply immediate;
    After starting SQL Apply for the first time, certain actions are performed to prepare the LogMiner dictionary that will be used for all future operations.

    Start Remote Archiving
    With the SQL Apply engine started on the logical standby database, the next and final phase is to start shipping redo data to the standby from the primary.

    SQL> alter system set log_archive_dest_state_2=enable scope=both;

    To force the current redo logs to be archived immediately, use the following statement on the primary database:

    SQL> alter system archive log current;

    At this point, the standby database will continue to apply changes from archive redo logs being transferred from the primary.

    Verifying the Logical Standby Database

    Redo Transport
    From the primary database, perform a log switch and then verify the transmissions of the archive redo log file was successful:

    SQL> alter system switch logfile;

    SQL> select status, error from v$archive_dest where dest_id = 2;

    STATUS ERROR
    --------- ---------------------------------------------------------
    VALID
    If the transmission was successful, the status of the destination will be VALID as shown above. If for any reason the transmission was unsuccessful, the status will be INVALID and the full text of the error message will be populated in the ERROR column which can be used to investigate and correct the issue.

    SQL Apply Progress
    First, identify the current registered log for the logical standby database:

    [oracle@vmlinux2 ~]$ sqlplus sys/MySysPassword@stby as sysdba


    SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

    SQL> select sequence#, first_time, next_time, applied from dba_logstdby_log order by sequence#;

    From the primary database, archive the current log using the following SQL statement:

    SQL> alter system archive log current;

    Go back to the standby database and re-query the DBA_LOGSTDBY_LOG view to verify redo data was shipped, received, and applied:

    SQL> select sequence#, first_time, next_time, applied from dba_logstdby_log order by sequence#;

    Another good method used to determine the progress of SQL Apply is to query the DBA_LOGSTDBY_PROGRESS view.

    SQL> select applied_scn, newest_scn from dba_logstdby_progress;

    The APPLIED_SCN column lists the highest SCN that has been applied to the logical standby database while the NEWEST_SCN column lists the highest SCN that has been received from the primary. When these two SCN values are equal, we know that all available redo that has arrived from the primary has been applied to the standby

    Verify SQL Apply Engine
    To view the SQL Apply engine on the logical standby and the status of each process associated with SQL Apply, use the V$LOGSTDBY view.

    SQL> select type, high_scn, status, pid
    2 from v$logstdby
    3 order by type;

    TYPE HIGH_SCN STATUS PID
    ------------- ---------- ---------------------------------------------------------- --------
    ANALYZER 2165785 ORA-16117: processing 29087
    APPLIER 2164857 ORA-16116: no work available 29089
    APPLIER 2111208 ORA-16116: no work available 29095
    APPLIER 2111204 ORA-16116: no work available 29093
    APPLIER 2164689 ORA-16116: no work available 29091
    APPLIER ORA-16116: no work available 29097
    BUILDER 2165786 ORA-16116: no work available 29083
    COORDINATOR 2165848 ORA-16116: no work available 29143
    PREPARER 2165785 ORA-16116: no work available 29085
    READER 2165848 ORA-16240: Waiting for logfile (thread# 1, sequence# 3950) 29081
    It is useful to track the value of the HIGH_SCN column of V$LOGSTDBY view. As long as this value is increasing on subsequent queries of the view, it is an indicator that progress is being made and that changes are being applied by the SQL Apply engine.
    When any of the SQL Apply processes (above) show a status of no work available, it is considered to be idle and waiting on information from the primary database.
    Another useful resource for gathering information about the SQL Apply engine is the V$LOGSTDBY_STATS view, which provides state and status information.

    SQL> select * from v$logstdby_stats order by name;

    NAME VALUE
    -------------------------------- ----------
    CTAS txns delivered 84
    DDL txns delivered 433
    DML txns delivered 1667
    LCRs delivered to client 69484
    Log Miner session ID 1
    Recursive txns delivered 16394
    Rolled back txns seen 29
    apply delay 0
    bytes checkpointed 4050284
    bytes of redo processed 178564904
    bytes paged out 0
    bytes rolled back 0
    coordinator state IDLE
    coordinator uptime 123418
    maximum SGA for LCR cache 30
    maximum events recorded 100
    number of appliers 5
    number of preparers 1
    parallel servers in use 9
    preserve commit order TRUE
    realtime logmining Y
    record applied DDL N
    record skip DDL Y
    record skip errors Y
    record unsupported operations N
    seconds spent in checkpoint 0
    seconds spent in pageout 0
    seconds spent in rollback 0
    seconds system is idle 119103
    transaction consistency FULL
    transactions applied 246
    transactions ready 246
    txns delivered to client 18494

    You can also use the V$LOGSTDBY_STATS view to count the number of transactions applied or transactions read by SQL Apply to determine if transactions are being applied as fast as they are being read.

    Post-Creation Steps
    • Remove Fetch Archive Log (FAL) Parameters
    The FAL_SERVER and FAL_CLIENT initialization parameters are not honored once a physical standby database is converted to a logical standby database and can be safely removed.

    # ---[ Primary ] --- #
    SQL> alter system reset fal_client scope=spfile sid='*';
    SQL> alter system reset fal_server scope=spfile sid='*';
    SQL> shutdown immediate;
    SQL> startup;
    # ---[ Logical Standby ] --- #
    SQL> alter system reset fal_client scope=spfile sid='*';
    SQL> alter system reset fal_server scope=spfile sid='*';
    SQL> alter database stop logical standby apply;
    SQL> shutdown immediate;
    SQL> startup;
    SQL> alter database start logical standby apply immediate;
    Last edited by Hemant; 01-15-2012 at 09:55 PM.

+ Reply to Thread

Similar Threads

  1. Create and configure physical Standby Database 10g PART II
    By mdmasood in forum DataGuard Setup and Management
    Replies: 2
    Last Post: 11-25-2012, 09:34 PM
  2. Create single instance standby database for RAC Database
    By devesh_ocp in forum DataGuard Setup and Management
    Replies: 0
    Last Post: 04-26-2012, 11:27 AM
  3. Find Unsupported Tables to Create a LOGICAL STANDBY
    By Hemant in forum Database Scripts
    Replies: 0
    Last Post: 02-03-2012, 06:34 PM
  4. Create and configure physical Standby Database 10g PART I
    By mdmasood in forum DataGuard Setup and Management
    Replies: 2
    Last Post: 01-18-2012, 05:04 PM
  5. Create and configure logical Standby Database 10g PART I
    By mdmasood in forum DataGuard Setup and Management
    Replies: 1
    Last Post: 11-03-2011, 06:30 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics