重庆思庄Oracle、Redhat认证学习论坛

标题: 创建oracle11g 逻辑备库 [打印本页]

作者: 郑全 时间: 2019-4-9 17:34
标题: 创建oracle11g 逻辑备库
本帖最后由郑全于 2019-4-9 17:35 编辑

环境：
主库：prod1
备库：sbdb1

数据库:11.2.0.3

1.创建物理备库
  此步骤略

2.停止物理备库日志应用
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3.修改主库支持逻辑备库
  alter system set LOG_ARCHIVE_DEST_2='LOCATION=/u01/app/oracle/standby VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=prod1';
  alter system set LOG_ARCHIVE_DEST_STATE_2=enable;

  alter system set UNDO_RETENTION=3600;
4.在redo中建立LogMiner字典，用于解释redo改变
  --在主库上执行
  execute DBMS_LOGSTDBY.BUILD;

5.转换物理备库到逻辑备库
--5.1 转换,数据库名称会变为新的名字 sbdb1(以前为prod1)
ALTER DATABASE RECOVER TO LOGICAL STANDBY sbdb1;

--5.2 启动备库到mount
shutdown immediate
startup mount;

--5.3 修改LOG_ARCHIVE_DEST_n接受主库的日志，以及逻辑备库自己产生的日志
  alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ONLINE_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=sbdb1';
  alter system set LOG_ARCHIVE_DEST_2='LOCATION=/u01/app/oracle/standby VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=sbdb1';


6.打开逻辑备用数据库

  ALTER DATABASE OPEN RESETLOGS;

  --启用sql apply
  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

7.验证逻辑备库正常运行
  --7.1 查看归档日志已经注册
  SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,
            DICT_BEGIN,DICT_END
      FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
  --7.2主库进行日志切换
  ALTER SYSTEM ARCHIVE LOG CURRENT;

  --7.3 再次执行7.1 的sql语句，看新的归档日志是否已经注册

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,
            DICT_BEGIN,DICT_END
      FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;


--7.4 检查重做数据是否被应用
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
      WHERE NAME = 'coordinator state';
--7.5 查看当前sql apply进程的状态
col status for a30
select type,status, STATUS_CODE from v$LOGSTDBY_PROCESS;

   TYPE                         STATUS                      STATUS_CODE
  ------------------------------ ------------------------------ -----------
  COORDINATOR                   ORA-16116: no work available       16116
  ANALYZER                      ORA-16116: no work available       16116
  APPLIER                      ORA-16116: no work available       16116
  APPLIER                      ORA-16116: no work available       16116
  APPLIER                      ORA-16116: no work available       16116
  APPLIER                      ORA-16116: no work available       16116
  APPLIER                      ORA-16116: no work available       16116
  READER                      ORA-16242: Processing log file    16242
                              (thread# 1, sequence# 19)

  BUILDER                      ORA-16116: no work available       16116

  TYPE                         STATUS                      STATUS_CODE
  ------------------------------ ------------------------------ -----------
  PREPARER                      ORA-16116: no work available       16116

--7.6 查看sql apply的进度
SELECT APPLIED_SCN, LATEST_SCN
   FROM V$LOGSTDBY_PROGRESS;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)