逻辑standby用的很少，特别是在11g，物理standby也可以只读打开并实时应用，物理standby也可以做滚动升级.不过，最近有一个用户需求，要建一个报表系统数据库，想到可以试试逻辑standby去实现，同步生产库的基础数据用户，并在逻辑standby上建BI相关的用户，用做数据统计。这样可以避免直接通过dblink或物化视图抓取数据对生产库的性能影响，又比利用goldengate实现同步在维护性上方便。
一.创建逻辑standby官方文档及注释:
Step by Step Guide on How to Create Logical Standby (文档 ID 738643.1)
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:
--创建逻辑standby
Step 1 Create a Physical Standby Database
--建逻辑standby,要先建一个物理standby,然后再进行转换
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/cd/B19306_01/server.102/b14239/create_ps.htm#i63561
For 11.1:
Oracle? Data Guard Concepts and Administration 11g Release 1 (11.1)
http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ps.htm#i63561
Step 2 Make Sure that Physical Standby is in Sync with Primary Database
--在物理standby上执行,查看跟主库的同步情况
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;
/*
Thread Last Sequence Received Last Sequence Applied
1 60 60
*/
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
--停止物理standby的redo应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 4 Set Parameters for Logical Standby in Primary
--设定主库的日志归档目录,LOG_ARCHIVE_DEST_3的设定是为了主库切换后使用
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
--在主库上生成logminer字典信息
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
--转换物理standby到逻辑standby,db_name是要指定一个新的逻辑standby 的db_name
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
--新建密码文件,这个在10g需要执行,11g原有的就可以
$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
--关闭逻辑standby并开户到mount状态
SQL> SHUTDOWN;
SQL> STARTUP MOUNT;
Step 9 Adjust Initialization Parameter on Logical Standby Database
--转换为逻辑standby后,修改原有的日志归档目录
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
--以resetlogs方式打开数据库
SQL> ALTER DATABASE OPEN RESETLOGS;
Step 11 Start Logical Apply on Standby
--开启sql apply,这里的immediate是要实时应用,需要有standby redo log支持,如果不需要实时应用,可不加immediate.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
二.逻辑standby创建完成后的一些设置
--在逻辑standby上将guard设置为standby,实现能在逻辑standby创建BI相关的用户
SQL>alter database guard standby;
--在逻辑standby上使用dbms_logstdby.skip跳过不需要同步的用户的表
SQL>alter database stop logical standby apply;
SQL>exec dbms_logstdby.skip(stmt=>'SCHEMA_DDL',SCHEMA_NAME=>'HR',OBJECT_NAME=>'%');
SQL>exec dbms_logstdby.skip(stmt=>'DML',SCHEMA_NAME=>'HR',OBJECT_NAME=>'%');
SQL>alter database start logical standby apply;
如果将来需要将跳过的表重新添加回来
SQL>alter database stop logical standby apply;
通过exec dbms_logstdby.unskip添加回来
通过dbms_logstdby.instantiate_table同步,这里需要建dblink
SQL>alter database start logical standby apply;
：