建立逻辑standby

1、查看主数据库中有无不支持的字段

SQL> select * from DBA_LOGSTDBY_UNSUPPORTED;

 

2、下面的语句，standby端将不在执行

ALTER DATABASE
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW LOG
ALTER SESSION
ALTER SYSTEM
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
CREATE SCHEMA AUTHORIZATION
CREATE SPFILE FROM PFILE
DROP DATABASE LINK
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW LOG
EXPLAIN
LOCK TABLE
SET CONSTRAINTS
SET ROLE
SET TRANSACTION

 

3、确定在主数据库上，补充日志是否被启用，可以查询v$database

SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;

 

4、找出不被支持的表

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';

 

5、创建rely 的主键约束

SQL> alter table t1 add primary key (id) rely disable; ------确定是唯一

 

6、取消standby的redo应用

SQL>alter database recover managed standby database cancel;

 

7、主库完整数据字典，并传到备库

SQL> execute dbms_logstdby.build;

 

 8、备库执行转换命令

SQL> alter database recover to logical standby rac3;
alter database recover to logical standby rac3
*
ERROR at line 1:
ORA-19953: database should not be open   --------------此时报错正常

 

9、关闭数据库并启动到mount状态

SQL> shutdown immediate

SQL> startup mount
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
LOGICAL STANDBY   ---------------已经转化过来了

 

10、再次执行转化

SQL> alter database recover to logical standby rac3;

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      RAC3

 

11、重构备库密码文件

orapwd file=/home/oracle/product/10.2.0/db_1/dbs/orapwrac3 password=oracle entries=5;

 

12、查看备库归档路径和接受路径（要不同）

SQL> show parameter standby_ar

SQL> show parameter   log_archive

 

13、创建备库log

SQL> alter database add standby logfile group 4 ('/home/oracle/oradata/rac1/standbyrd01.log') size 20M;

SQL> alter database add standby logfile group 4 ('/home/oracle/oradata/rac1/standbyrd01.log') size 20M;

SQL> alter database add standby logfile group 6 ('/home/oracle/oradata/rac1/standbyrd03.log') size 20M;

SQL> select member from v$logfile;

 

14、打开数据库

SQL> alter database open resetlogs;

 

15、应用逻辑备库

SQL> alter database start logical standby apply immediate;

 

 16、验证时，必须用普通用户