Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database. This is done on the primary database.

Logical standby databases do not support the following data types:

BFILE
ROWID, UROWID
User-defined types
Collections (including VARRAYS and nested tables)
XML type
Encrypted columns
Multimedia data types (including Spatial, Image, and Context)

For more information about this ( for Oracle 10.2) click here.

To find which tables cannot be duplicated on the logical database run the following select:

SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
ORDER BY OWNER,TABLE_NAME;

Oracle Logical Standby Database

To view the column names and data types which are not supported for one of the tables listed in the previous query, run the following select:

SQL> col DATA_TYPE format a20

SQL> col COLUMN_NAME format a20

SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

Oracle Logical Standby Database

2. Ensure Table Rows in the Primary Database Can Be Uniquely Identified

The physical organization in a logical standby database is different from that of the primary database, even though the logical standby database is created from a backup copy of the primary database. Thus, ROWIDs contained in the redo records generated by the primary database cannot be used to identify the corresponding row in the logical standby database.

Oracle uses primary-key or unique-key supplemental logging to logically identify a modified row in the logical standby database. When database-wide primary-key and unique-key supplemental logging is enabled, each UPDATE statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database.

If a table has a primary key defined, then the primary key is logged as part of the UPDATE statement to identify the modified row.
In the absence of a primary key, the shortest non-null unique key is logged as part of the UPDATE statement to identify the modified row.
In the absence of both a primary key and a non-null unique key, all columns of bounded size are logged as part of the UPDATE statement to identify the modified row. In other words, all columns except those with the following types are logged: LONG, LOB, LONG ROW, object type, and collections.

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.

Find tables without unique logical identifier in the primary database:

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME

FROM DBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = 'Y';

If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This avoids the overhead of maintaining a primary key on the primary database.

SQL> ALTER TABLE TableA ADD PRIMARY KEY (id, name) RELY DISABLE;

When you specify the RELY constraint, the system will assume that rows are unique. Because you are telling the system to rely on the information, but are not validating it on every modification done to the table, you must be careful to select columns for the disabled RELY constraint that will uniquely identify each row in the table. If such uniqueness is not present, then SQL Apply will not correctly maintain the table.

To improve the performance of SQL Apply, add an index to the columns that uniquely identify the row on the logical standby database. Failure to do so results in full table scans during UPDATE or DELETE statements carried out on the table by SQL Apply.

3. Create a Physical Standby Database

For more information about how to create a physical standby database click here.

4. Stop Redo Apply on the Physical Standby Database

You can run Redo Apply on the new physical standby database for any length of time before converting it to a logical standby database. However, before converting to a logical standby database, stop Redo Apply on the physical standby database.

NOTE: If the database is a RAC database comprised of multiple instances, then you must first reduce the number of instances to one.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5. Build a Dictionary in the Redo Data

A Log Miner dictionary must be built into the redo data so that the Log Miner component of SQL Apply can properly interpret changes it sees in the redo.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

6. Convert Physical Standby Database to a Logical Standby Database

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <NewDatabaseName>; (in mount state:

the control file will be modified with the new database name)

The redo logs will be applied until the Log Miner dictionary is found in the log files. That could take several minutes. If a dictionary build is 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.

7. Create a New Password File

$ orapwd file=orapw<NewSID> password=password entries= n

This is needed because the conversion process changes the database name.

8. Adjust Initialization Parameters for the Logical Standby Database

LOG_ARCHIVE_DEST_1='LOCATION=/arch1/standbyDB/
                VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
                DB_UNIQUE_NAME=standbyDB'
LOG_ARCHIVE_DEST_2='SERVICE=primaryDB LGWR ASYNC
               VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
               DB_UNIQUE_NAME=primaryDB'
LOG_ARCHIVE_DEST_3='LOCATION=/arch2/standbyDB/
               VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
               DB_UNIQUE_NAME=standbyDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE

LOG_ARCHIVE_DEST_STATE_1 -> redo data generated by the logical standby database
LOG_ARCHIVE_DEST_STATE_2 -> is ignored; LOG_ARCHIVE_DEST_2 is valid only when standbyDB is running in the

primary role.
LOG_ARCHIVE_DEST_STATE_3 -> data received from the primary database

DB_NAME, DB_UNIQUE_NAME must be changed as well with the new name from 6.;

9. Open the Logical Standby Database

SQL> shutdown immediate;

SQL> startup mount;

SQL> ALTER DATABASE OPEN RESETLOGS;

Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new DB_NAME initialization parameter.

Start applying redo data to the logical standby database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Oracle Logical Standby Database

In-Oracle.com -> Oracle DBA -> Oracle Replication

-> Oracle Logical Standby Database

Oracle Applications EBS 11i, R12 Oracle Database Data Warehouse & BI Oracle Middleware PL/SQL Linux ...

Find related information on Google:

Custom Search

Copyright (c) 2011-2012 www.in-oracle.com | Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.