前提已经成功搭建完成物理备库。

--====================================

备库中停止应用REDO

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

主库中

生成LogMiner字典信息：

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.

--==================================

把物理备库转换为逻辑备库的实验

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string JZQ
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY LGDG;

Database altered.

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
LOGICAL STANDBY

--=============================

修改tnsnames.ora

添加

LGDG =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.40.4)(PORT = 1521))
    )
    (CONNECT_DATA =
          (SERVER = DEDICATED)
      (SERVICE_NAME = LGDG)
    )
)
--================================

修改initJZQ.ora

*.log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=LGDG'
*.log_archive_dest_2=''
*.LOG_ARCHIVE_DEST_3='LOCATION=/u01/std VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=LGDG'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'

--===================================

SQL> alter database open resetlogs;

Database altered.

SQL> alter database start logical standby apply;

Database altered.

--===================================

SQL> alter database stop logical standby apply;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

--=======================================

主库中添加一条记录

SQL> insert into scott.dept values(4,'jj','jar');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

逻辑备库中查询

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
         4 jj             jar
        10 ACCOUNTING     zhejiang
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

---===========================================================

如果归档没有同步过来

可以查看 v$archive_dest 的错误提示

一般都是初始文件设置有误排查

如果要保留备库的归档，执行

exec dbms_logstdby.apply_set('LOG_AUTO_DELETE','FALSE');

详细设置可以参看备库的设置select * from dba_logstdby_parameters

ITPub博客

DG--创建逻辑备库设置维护

最新文章

ITPub博客

DG--创建逻辑备库 设置维护

最新文章

DG--创建逻辑备库设置维护