|Oracle With Examples : Oracle Logical Standby Database|
-> Oracle Logical Standby Database
Oracle Logical Standby Database
How to create a Logical Standby database (step-by-step)
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:
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
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
SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
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.
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.
OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
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.
For more information about how to create a physical standby database click here.
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;
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;
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.
-> redo data generated by the logical standby database
DB_NAME, DB_UNIQUE_NAME must be changed as well with the new name from 6.;
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE OPEN RESETLOGS;
this is the first time the database is being opened, the database's
global name is adjusted automatically to match the new
Start applying redo data to the logical standby database:
ALTER DATABASE START LOGICAL STANDBY APPLY;
-> Oracle Logical Standby Database
Copyright (c) 2018 www.oracle-with-examples.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.