Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1126099
  • 博文数量: 276
  • 博客积分: 10077
  • 博客等级: 上将
  • 技术积分: 2513
  • 用 户 组: 普通用户
  • 注册时间: 2007-08-24 20:31
文章分类

全部博文(276)

文章存档

2020年(1)

2015年(5)

2012年(2)

2011年(6)

2010年(7)

2009年(224)

2008年(31)

我的朋友

分类: Oracle

2009-07-03 13:53:39

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.

Step 2 Make Sure that Physical Standby is in Sync with Primary Database

Use following query on Standby to check:

SQL>SELECT MAX(AL.SEQUENCE#) "LAST SEQ RECEIVED", MAX(LH.SEQUENCE#) "LAST SEQ APPLIED" FROM V$ARCHIVED_LOG AL, V$LOG_HISTORY LH;

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 ;

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= PASSWORD= ENTRIES=

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;
阅读(1027) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~