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

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

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

    Create and configure logical Standby Database 10g PART I

    Logical Standby Database
    Before implementing a logical standby database, you should first examine your application to determine if logical standby has support to maintain all of the data types and tables.

    Internal Schema Support
    Some schemas that ship with the Oracle database are automatically skipped by SQL Apply. To determine exactly which schemas will be skipped, query the DBA_LOGSTDBY_SKIP view.

    SQL> select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA' order by owner;

    Determine Support for Data Types and Storage Attributes for Tables
    Run the following query on the primary database to list all tables that contain unsupported data types:
    SQL> select distinct owner, table_name
    2 from dba_logstdby_unsupported
    3 order by owner, table_name;

    Any tables returned from the above query are considered unsupported by logical standby database.

    If the structure of the table is supported but certain columns in the table have unsupported data types, the ATTRIBUTE column will be NULL.

    SQL> select owner, table_name, column_name, data_type, attributes
    2 from dba_logstdby_unsupported
    3 where owner = 'SCOTT'
    4 order by owner, table_name, column_name;

    Data Type Considerations
    Supported Data Types
    • BINARY_DOUBLE
    • BINARY_FLOAT
    • BLOB
    • CHAR
    • CLOB and NCLOB
    • DATE
    • INTERVAL YEAR TO MONTH
    • INTERVAL DAY TO SECOND
    • LONG
    • LONG RAW
    • NCHAR
    • NUMBER
    • NVARCHAR2
    • RAW
    • TIMESTAMP
    • TIMESTAMP WITH LOCAL TIMEZONE
    • TIMESTAMP WITH TIMEZONE
    VARCHAR2 and VARCHAR
    Unsupported Data Types
    • BFILE
    • Collections (including VARRAYS and nested tables)
    • Encrypted columns
    • Multimedia data types (including Spatial, Image, and Context)
    • ROWID, UROWID
    • User-defined types
    XMLType
    Storage Type Considerations
    Supported Storage Types
    Cluster tables (including index clusters and heap clusters)
    Index-organized tables (partitioned and non-partitioned, including overflow segments)
    Heap-organized tables (partitioned and non-partitioned
    Unsupported Storage Type
    Logical standby databases do not support the segment compression storage type.
    PL/SQL Supplied Packages Considerations
    Supported PL/SQL Supplied Packages
    Examples of such packages are DBMS_OUTPUT, DBMS_RANDOM, DBMS_PIPE, DBMS_DESCRIBE, DBMS_OBFUSCATION_TOOLKIT, DBMS_TRACE, and DBMS_METADATA.
    Oracle PL/SQL supplied packages that do not modify system metadata but may modify user data are supported by SQL Apply, as long as the modified data belongs to the list supported data types. Examples of such packages are DBMS_LOB, DBMS_SQL, and DBMS_TRANSACTION.
    Unsupported PL/SQL Supplied Packages
    Examples of such packages are DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, DBMS_SCHEDULER, and DBMS_AQ.

    Skipped SQL Statements on a Logical Standby Database
    By default, the following SQL statements are automatically skipped by SQL Apply:

    ALTER DATABASE
    ALTER MATERIALIZED VIEW
    ALTER MATERIALIZED VIEW LOG
    ALTER SESSION
    ALTER SYSTEM
    CREATE CONTROL FILE
    CREATE DATABASE
    CREATE DATABASE LINK
    CREATE PFILE FROM SPFILE
    CREATE MATERIALIZED VIEW
    CREATE MATERIALIZED VIEW LOG
    CREATE SCHEMA AUTHORIZATION
    CREATE SPFILE FROM PFILE
    DROP DATABASE LINK
    DROP MATERIALIZED VIEW
    DROP MATERIALIZED VIEW LOG
    EXPLAIN
    LOCK TABLE
    SET CONSTRAINTS
    SET ROLE
    SET TRANSACTION
    All other SQL statements executed on the primary database are applied to the logical standby database.
    Ensure Table Rows in the Primary Database Can Be Uniquely Identified
    Oracle recommends that you add a primary key or a non-null unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.
    Perform the following steps to ensure SQL Apply can uniquely identify rows of each table being replicated in the logical standby database.
    Step 1 — Find tables without unique logical identifier in the primary database
    Query the DBA_LOGSTDBY_NOT_UNIQUE view to display a list of tables that SQL Apply may not be able to uniquely identify. For example:

    SQL> select owner, table_name
    2 from dba_logstdby_not_unique
    3 where (owner, table_name) not in (
    4 select distinct owner, table_name
    5 from dba_logstdby_unsupported)
    6 order by owner, table_name;


    Step 2 — Add a disabled primary-key RELY constraint
    To create a disabled RELY constraint on a primary database table, use the ALTER TABLE statement with a RELY DISABLE clause.

    SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
    Create the Logical Standby Database
    After verifying that the logical standby database can be used to support your given application, use the steps outlined in this section to create the logical standby.
    1. Create a Physical Standby Database
    The first step in creating a logical standby using Oracle Database 10g or higher, is to create an initial physical standby database.

    This has been posted in the forum in two parts.

    Stop Redo Apply on the Physical Standby Database
    Before converting to a logical standby database, stop Redo Apply on the physical standby database. Stopping Redo Apply is necessary to avoid applying changes past the redo that contains the LogMiner dictionary.
    To stop Redo Apply, issue the following statement on the physical standby database.

    SQL> alter database recover managed standby database cancel;

    Database altered.

    Build a Dictionary in the Redo Data
    A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo.
    To build the LogMiner dictionary, issue the following statement from the primary database:

    SQL> execute dbms_logstdby.build;

    PL/SQL procedure successfully completed.
    As part of building LogMiner Multiversioned Data Dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns.
    Verify Supplemental Logging is Enable on the Primary and Standby
    Primary Database
    Supplemental logging should already be enabled on the primary database as a result of building the LogMiner Dictionary.

    SQL> select
    2 supplemental_log_data_pk as "PK Logging"
    3 , supplemental_log_data_ui as "UI Logging"
    4 from v$database;

    PK Logging UI Logging
    ----------- -----------
    YES YES
    Standby Database
    In order to support role transition, supplemental logging should be enabled on the new physical standby database.

    SQL> select
    2 supplemental_log_data_pk as "PK Logging"
    3 , supplemental_log_data_ui as "UI Logging"
    4 from v$database;

    PK Logging UI Logging
    ----------- -----------
    NO NO
    If either column in the above query report NO, supplemental logging must be enabled by issuing the following statement on both the primary and standby database:

    SQL> alter database add supplemental log data (primary key, unique index) columns;

    Database altered.
    After executing the statement above, re-query V$DATABASE to ensure that SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI report a value of YES.

    SQL> select
    2 supplemental_log_data_pk as "PK Logging"
    3 , supplemental_log_data_ui as "UI Logging"
    4 from v$database;

    PK Logging UI Logging
    ----------- -----------
    YES YES
    1. Convert Physical Standby Database to a Logical Standby Database
    The following sections describe how to prepare the physical standby database to transition to a logical standby database.
    To continue applying redo data to the physical standby database until it is ready to convert to a logical standby database, issue the following SQL statement on the standby:
    SQL> alter database recover to logical standby stby;

    Database altered.

    The ALTER DATABASE statement above waits, applying redo data to the standby until the LogMiner dictionary is found in the log files. If the ALTER DATABASE statement does hang while waiting for the archived redo log containing the LogMiner dictionary, you may have to manually register the missing logs to the standby using:

    SQL> alter database register logfile '<name_of_log_file>';
    Continue registering any missing logs to the standby until you receive the error "ORA-01507: database not mounted". This indicates that the standby has received and applied the LogMiner dictionary and the ALTER DATABASE statement that was waiting, should now be complete.
    If a dictionary build was not successfully performed on the primary database, this command will never complete. You can cancel the SQL statement by issuing the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL statement from another SQL session.

    Continued at : Create-and-configure-logical-Standby-Database-10g-PART-II
    Last edited by Hemant; 11-03-2011 at 06:29 PM.

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    Very Helpful Post to create Logical Standby.
    Thanks Masood

+ 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 II
    By mdmasood in forum DataGuard Setup and Management
    Replies: 0
    Last Post: 11-03-2011, 05:06 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