Logical Standby 10g

Creating Logical Standby from Cold Backup

Create a Physical Standby Database

Ensure the physical standby database is caught up to the primary database.

Preparing the Primary Database Before creating the logical database, some tasks will have to be performed and the primary database will have to be prepared for the Data Guard setup.

The steps for preparing the Primary Database for Logical Standby Database creation are given below:

1. Check for unsupported Data type and Tables

It is important to identify unsupported database objects on the primary database before you create a logical standby database. This is because changes made to unsupported datatypes, table, sequences, or views on the primary database will not be propagated to the logical standby database. Moreover, no error message will be returned.

Unsupported Datatypes

BFILE

ROWID

UROWID

user-defined types

object types REFs

varrays

nested tables

XMLType

 

Unsupported Tables, Sequences, and Views

• Most schemas that ship with the Oracle database are skipped by SQL Apply
• Tables with unsupported datatypes
• Tables using table compression

To determine exactly which schemas will be skipped, query the DBA_LOGSTDBY_SKIP view.

Use the following query on the primary database to list the schema and table names of primary database tables that are not supported by logical standby databases:

SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED

ORDER BY OWNER, TABLE_NAME;

OWNER                          TABLE_NAME

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

OE                             CATEGORIES_TAB

OE                             CUSTOMERS

OE                             WAREHOUSES

PM                             ONLINE_MEDIA

PM                             PRINT_MEDIA

SH                             MVIEW$_EXCEPTIONS

To view the column name and data type for one of the tables listed in the previous query-

SELECT COLUMN_NAME, DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED

WHERE OWNER=<owner name> AND TABLE_NAME=<table name>;

SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED

WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

COLUMN_NAME                    DATA_TYPE

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

CUST_ADDRESS                   CUST_ADDRESS_TYP

PHONE_NUMBERS                  PHONE_LIST_TYP

CUST_GEO_LOCATION              SDO_GEOMETRY

Unsupported Schemas Dropped

DROP USER OR CASCADE;

DROP USER PM CASCADE;

DROP USER SH CASCADE;

If the primary database contains unsupported tables, log apply services automatically exclude these tables when applying redo data to the logical standby database.

If any of the critical tables in the primary database are unsupported then using a Physical Standby Database might be a better option.

By default, all SQL statements except those in the following list are applied to a logical standby database if they are executed on a primary database:

ALTER DATABASE

ALTER SESSION

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW LOG

ALTER SYSTEM

CREATE CONTROL FILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE PFILE FROM SPFILE

CREATE SCHEMA AUTHORIZATION

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG

CREATE SPFILE FROM PFILE

DROP DATABASE LINK

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW LOG

EXPLAIN

LOCK TABLE

SET CONSTRAINTS

SET ROLE

SET TRANSACTION

2. Ensure the table rows in the Primary Database can be Uniquely Identified.

Because the ROWIDs on a logical standby database might not be the same as the ROWIDs on the primary database, a different mechanism must be used to match the updated row on the primary database to its corresponding row on the logical standby database. You can use one of the following to match up the corresponding rows:

• Primary key
• Unique index

Oracle recommends that you add a primary key or a unique index to tables on the primary database, whenever appropriate and possible, to ensure SQL Apply can efficiently apply data updates to the logical standby database.

Query the DBA_LOGSTDBY_NOT_UNIQUE view to identify tables in the primary database that do not have a primary key or unique index with NOT NULL columns.

The following query displays a list of tables that SQL Apply might not be able to uniquely identify:

SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE

WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);

OWNER                          TABLE_NAME                     B

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

TSMSYS                         SRS$                           Y

SCOTT                          BONUS                          N

SCOTT                          SALGRADE                       N

HR                             COUNTRIES                      N

The value of the BAD_COLUMN column will be either Y or N, as described in the following list:

• Y

Indicates a table column is defined using an unbounded datatype, such as CLOB or BLOB. SQL Apply attempts to maintain these tables, but you must ensure the application provides uniqueness in bounded columns only. Note that if two rows in the table match except for rows in the LOB column, then the table cannot be maintained properly and SQL Apply will stop.

• N

Indicates the table contains enough column information to maintain the table in a logical standby database.

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. The 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 Corporation recommends that there should be 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.

3. Enable Supplemental Logging

Supplemental logging must be enabled on the primary database to support the logical standby database. Because Oracle only logs the columns that were modified, this is not always sufficient to uniquely identify the row that changed and additional (supplemental) information must be put into the redo log. The supplemental information that is added to the redo logs helps log apply services to correctly identify and maintain tables in the logical standby database.

To check whether supplemental logging is enabled:

SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP

--- ---

NO NO

The NO values indicate that supplemental logging is not enabled on the primary database.

To enable supplemental Logging:

On the primary database, issue the following statement to add primary key and unique index information to the archived redo log file:

ARCHIVE LOG LIST

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

Switch to a new log file to ensure that the redo logs do not contain both supplemental log data and nonsupplemental log data.

ALTER SYSTEM ARCHIVE LOG CURRENT;

Verify Supplemental Logging

SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP

--- ----

YES YES

Note:

If you enable supplemental logging on a primary database in a Data Guard configuration that also contains physical standby databases, then you must issue the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement on each physical standby database to ensure future switchovers work correctly.

4. Prepare the Primary Database for Role Transitions

log_archive_dest_1='LOCATION=C:\oracle\product\10.2.0\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

log_archive_dest_2='SERVICE=pune  LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pune'

log_archive_dest_3='SERVICE=delhi LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=delhi'

#  This is valid only when orcl is running in the standby role. Archives redo data received from the
#  primary database to this local archived redo log files location.

LOG_ARCHIVE_DEST_4='LOCATION=C:\oracle\product\10.2.0\logical_standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='ENABLE'

log_archive_dest_state_3='ENABLE'

log_archive_dest_state_4='ENABLE'

undo_retention=3600

5. Prepare Logical Standby Database for Role Transition

Ensure Supplemental Logging Is Enabled

Enabling supplemental logging on the logical standby database now rather than later is beneficial to prepare the database for future role transitions.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

Prepare an Initialization Parameter File for the Logical Standby Database

# Archived redo log files that store redo data generated by the logical standby database either in primary or standby.

log_archive_dest_1='LOCATION=D:\pune\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pune'

log_archive_dest_2='SERVICE=orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

log_archive_dest_3='SERVICE=delhi LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=delhi'

# Directs archival of redo data received from the primary database

LOG_ARCHIVE_DEST_4='LOCATION=D:\pune\logical_standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pune'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='ENABLE'

log_archive_dest_state_3='ENABLE'

log_archive_dest_state_4='ENABLE'

parallel_max_servers=9

undo_retention=3600

Shut Down Your Physical Standby Database Pune

SHUTDOWN IMMEDIATE

6. Create a Control File for the Logical Standby Database on the primary database.

ALTER DATABASE OPEN;

If a switchover operation between the primary database and a logical standby database is expected in future, an alternate tablespace in the primary database should be created and the logical standby system tables are moved to that separate tablespace.

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');

ALTER SYSTEM ENABLE RESTRICTED SESSION;

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.

EXECUTE DBMS_LOGSTDBY.BUILD;

ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'D:\pune\control02.ctl';

ALTER SYSTEM DISABLE RESTRICTED SESSION;

If you have created control file at some other location then copy it  to the logical standby system.

And remove the previous physical standby control files.

7. On Logical Standby database.

SHU IMMEDIATE

Remove the previous physical standby control files and rename the latest logical controlfile.

STARTUP MOUNT

 

SELECT * FROM V$LOGFILE;

IF IT SHOWS PRIMARY LOCATION PATH THEN DROP IT.

ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 8;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 9;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 10;

SELECT * FROM V$LOGFILE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6  SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7  SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8  SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9  SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 SIZE 50M;

ALTER SYSTEM REGISTER;

You can run Redo Apply on the 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.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

ALTER DATABASE OPEN;

Start SQL Apply.

Issue the following statement to begin applying redo data to the logical standby database.

ALTER DATABASE START LOGICAL STANDBY APPLY;

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

select open_mode, database_role, switchover_status from  v$database;

alter system register;

Verify the Logical Standby Database Is Performing Properly

SELECT sequence#, applied, file_name FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

If log is not shipped to logical standby location, restart the primary database.

Verify redo data is being applied correctly.

On the logical standby database, query the V$LOGSTDBY_STATS view to verify redo data is being applied correctly.

SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

NAME                          VALUE

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

coordinator state             INITIALIZING

When the coordinator process is INITIALIZING, log apply services are preparing to begin SQL Apply, but data from the archived redo log files is not being applied to the logical standby database, it takes time so wait. If it shows IDLE , then log has been applied.

SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

NAME                      VALUE

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

coordinator state         IDLE

View the V$LOGSTDBY view to see current SQL Apply activity.

On the logical standby database, query the V$LOGSTDBY view to see a current snapshot of SQL Apply activity. A text message describing the current activity of each process involved in reading and applying changes is displayed.

SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

TYPE           HIGH_SCN STATUS

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

COORDINATOR             ORA-16115: loading Log Miner dictionary data

READER                  ORA-16127: stalled waiting for additional transactons to be
                                   applied

BUILDER                 ORA-16117: processing

PREPARER                ORA-16116: no work available

On shutdown of logical standby database, each you have restart SQL apply.

ALTER DATABASE START LOGICAL STANDBY APPLY;

END

If You Want to configure Logical standby on oracle 11g version click here .