逻辑备库不支持的用户、数据类型和语句

不支持的用户：可以通过select owner,name from DBA_LOGSTDBY_SKIP;来获得

不支持的数据类型：
BFILE
Collections (including VARRAYS and nested tables)
Encrypted columns
Multimedia data types (including Spatial, Image, and Context)
ROWID, UROWID
User-defined types
XMLType

不支持的语句：
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

查看表是否支持逻辑备库复制

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE

2   WHERE (OWNER, TABLE_NAME) NOT IN

3         (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)

4     AND BAD_COLUMN = 'Y';

OWNER                     TABLE_NAME

------------------------------ ------------------------------

TSMSYS                         SRS$

如果找到不合适的表，添加唯一标识Add a disabled primary-key RELY constraint.

SQL> ALTER TABLE testdglog ADD PRIMARY KEY (id, name) RELY DISABLE;

配置逻辑备库需要先配置DG物理备库，参考http://space.itpub.net/26543945/viewspace-748245

物理备库配置成功后
先将物理备库停掉 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

以下对主库配置

修改归档路径

修改主库参数文件(需要重起数据库才能生效)：

LOG_ARCHIVE_DEST_3=

'LOCATION=/opt/oracledata/arch/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=dglog26'

LOG_ARCHIVE_DEST_STATE_3=ENABLE

或执行(spfile启动)

SQL> alter system set log_archive_dest_3='location=/opt/oracledata/arch/

valid_for=(standby_logfiles,standby_role) db_unique_name=dglog26' scope=both;

System altered.

SQL> alter system set log_archive_dest_state_3=enable scope=both;

System altered.

创建logminer路径

设置logminer路径(如果创建可以跳过)

SQL> alter system set utl_file_dir='/opt/oracle/oradata/utl' scope=spfile;

System altered.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.

增加undo保留时间

SQL> ALTER SYSTEM SET undo_retention=3600 SCOPE=BOTH;

System altered.

转回从库

1．将物理备库转为逻辑备库

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY dglog;

ERROR at line 1:

ORA-16254: change db_name to DGLOG in the client-side parameter file (pfile)

这个不是错误，可以忽略，执行时间5-10分钟

2．重新创建密码文件

[oracle@std dbs]$

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=Dongzai1005 entries=5 force=y

3．重起数据库到mount状态

按照主库第2步操作，将service_name对应正确即可。重起到mount状态(动态修改不需要)

4．打开逻辑数据库

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

至此，逻辑备库就全部配置完了。

验证是否配置正确

建张表进行测试即可。

ITPub博客

配置DG逻辑备库

最新文章