Step by Step Guide on How to Create Logical Standby

郑全

Step by Step Guide on How to Create Logical Standby (文档 ID 738643.1)

转到底部

In this Document
Goal
Solution

Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 21-Sep-2012***
*** Reviewed for Relevance 16-Jul-2015 ***

Goal
Step by Step Guide on How to Create Logical Standby
Solution
Prerequisite
1 : Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database. See Appendix C of the dataguard documentation for a complete list of data type and storage type considerations.
2 : Ensure Table Rows in the Primary Database Can Be Uniquely Identified.
2.1 : Find Tables Without Unique Logical Identifier in the Primary Database.
Use following query to display a list of tables that SQL Apply may not be able to uniquely identify:
SQL> 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'
2.2 : If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. Use ALTER TABLE command to add a disabled primary-key RELY constraint.
The following example creates a disabled RELY constraint on a table named mytab, for which rows can be uniquely identified using the id and name columns:
SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
Creating a Logical Standby Database:
Step 1 Create a Physical Standby Database
Create a Physical Standby Database and make sure that there is no error in remote archiving to Standby from Primary Database.
Please refer following documentations for creating physical standby database:
For 10.2:
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
http://download.oracle.com/docs/ ... reate_ps.htm#i63561
For 11.1:
Oracle® Data Guard Concepts and Administration 11g Release 1 (11.1)
http://download.oracle.com/docs/ ... reate_ps.htm#i63561
Step 2 Make Sure that Physical Standby is in Sync with Primary Database
Use following query on Standby to check:
SQL>SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM  
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,  
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL  
WHERE  
ARCH.THREAD# = APPL.THREAD#  
ORDER BY 1;
There should not be any difference in Last Seq Received and Last Seq Applied on Physical Standby.
Step 3 Stop Redo Apply on the Physical Standby Database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 4 Set Parameters for Logical Standby in Primary
4.1. Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES)
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim1'
4.2. Set LOG_ARCHIVE_DEST_3 for logs which will received on Standby from Primary
LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prim1'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
Note: LOG_ARCHIVE_DEST_3 only takes effect when the primary database is transitioned to the logical standby role.
Step 5 Build a Dictionary in the Redo Data on Primary Database
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.
Step 6 Convert to a Logical Standby Database
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>;
For db_name, specify a database name to identify the new logical standby database. If you are using a spfile for standby, then command will update the db_name parameter otherwise it will issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.
Step 7 Create a New Password File for Logical Standby Database
$ORAPWD FILE=<filename> PASSWORD=<password> ENTRIES=<max_users>
This step is required in 10.2 only and should not be performed in 11g.
Step 8 Shutdown and Startup Logical Standby Database in Mount Stage
SQL> SHUTDOWN;
SQL> STARTUP MOUNT;
Step 9 Adjust Initialization Parameter on Logical Standby Database
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_2= 'SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1'
LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
Step 10 Open the Logical Standby Database
SQL> ALTER DATABASE OPEN RESETLOGS;
Step 11 Start Logical Apply on Standby
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;