直接搭建逻辑备库oracle,oracle11g逻辑备库搭建

oracle11g逻辑备库搭建

oracle11g逻辑备库搭建

前提确保主键上的每一行都能唯一标识，备库与主库的物理布局都不一致，不能使用rowid去标识行。

所以表上有主键或唯一约束的表好标记唯一，但是如果表上没有主键没有唯一索引，那么所有的列会被记录到update语句中去标识行。

使用下面的语句去查看主库上哪些表没有唯一标识符：

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

如果应用确定表上的行都是唯一的，那么你可以创建一个rely主键约束。

alter table mytab add primary key(id,name) rely disable;

当你指定了rely约束，系统认为表上的行是唯一的，但每次记录修改的时候不会去检查它，如果这样的唯一约束没有被设置，那么sql apply可能不能正常的维护表。为了维护sql apply的性能，需要在逻辑备库的表上能唯一标识唯一值的字段上添加唯一索引，如果没有索引，那么就是导致全表扫描。性能会下降的厉害。

创建逻辑备库的步骤：

1先搭建物理备库

2停止物理备库上的redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3让主库准备做角色转换，之前搭建物理备库的时候，主库的角色转换准备是为了转换成物理备库，但是为了主库能转换成逻辑备库，需要修改参数：

LOG_ARCHIVE_DEST_1=

‘LOCATION=/arch1/chicago/

VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=chicago’

LOG_ARCHIVE_DEST_3=

‘LOCATION=/arch2/chicago/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=chicago’

LOG_ARCHIVE_DEST_STATE_3=ENABLE

在redo数据中创建字典

EXECUTE DBMS_LOGSTDBY.BUILD;

alter system archive log current;

3转换成逻辑备库，为了在转换成逻辑备库之前继续应用日志，使用下面的命令

alter database recover to logical standby db_name; –此处的db_name是新逻辑备库的新名称，可以与之前的db_name不一致。

此处数据库在open的时候执行这个命令会遇到：

ORA-19953: database should not be open

关闭数据库实例，打开到mount状态，再次执行卡到了那里，alert中没有报错，一直等待了很长时间才执行完成

看到alert日志做了下面的操作：

Media Recovery Log /data/arch2/1_2535_941971784.dbf

Incomplete Recovery applied until change 7316207 time 06/22/2017 14:31:08

Thu Jun 22 14:31:54 2017

Media Recovery Complete (dbadb)

Killing 3 processes with pids 2593,2595,2597 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 2427

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 7316207

Resetting resetlogs activation ID 3394325723 (0xca514cdb)

Online log /data/oracle/app/oradata/dbadb/redo01.log: Thread 1 Group 1 was previously cleared

Online log /data/oracle/app/oradata/dbadb/redo02.log: Thread 1 Group 2 was previously cleared

Online log /data/oracle/app/oradata/dbadb/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 7316205

Thu Jun 22 14:31:56 2017

Setting recovery target incarnation to 3

RECOVER TO LOGICAL STANDBY: Complete - Database shutdown required after NID finishes

* DBNEWID utility started *

DBID will be changed from 3394338758 to new DBID of 3399704284 for database DBADB

DBNAME will be changed from DBADB to new DBNAME of DBADB

Starting datafile conversion

Datafile conversion complete

Database name changed to DBADB.

Modify parameter file and generate a new password file before restarting.

Database ID for database DBADB changed to 3399704284.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open with RESETLOGS option.

Succesfully changed database name and ID.

* DBNEWID utility finished succesfully *

Shutting down archive processes

Archiving is disabled

Thu Jun 22 14:31:56 2017

ARCH shutting down

ARC3: Archival stoppedThu Jun 22 14:31:56 2017

ARCH shutting down

Thu Jun 22 14:31:56 2017

ARCH shutting down

ARC2: Relinquishing active heartbeat ARCH role

Thu Jun 22 14:31:56 2017

ARCH shutting down

ARC2: Archival stopped

ARC0: Archival stopped

ARC1: Archival stopped

Completed: alter database recover to logical standby dbadb

destination database instance is ‘started’ not ‘mounted’

修改了dbid，dbname

调整逻辑备库的参数，逻辑备库

SQL> SHUTDOWN;

SQL> STARTUP MOUNT;

需要修改 LOG_ARCHIVE_DEST_n参数，不像物理备库，逻辑备库在打开的时候，会产生redo有不同类型的日志文件，在线redo文件，归档文件，standby日志文件等，最佳实践是指定不同的位置上。

下面是个示例：

LOG_ARCHIVE_DEST_1=

‘LOCATION=/arch1/boston/

VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=boston’

LOG_ARCHIVE_DEST_2=

‘SERVICE=chicago ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=chicago’

LOG_ARCHIVE_DEST_3=

‘LOCATION=/arch2/boston/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=boston’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

4打开逻辑备库

SQL> ALTER DATABASE OPEN RESETLOGS;

使用下面的命令来应用redo 数据

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

sql应用的过程

oracle11g逻辑备库搭建相关教程