ADG 环境 物理备库to逻辑备库
--在创建逻辑备库之前，必须得先创建物理备库
步骤1：停快速故障轉移功能
DGMGRL>stop observer
DGMGRL>disable fast_start failover;
DGMGRL> show configuration;
步骤2：使用以下语句查询，确保物理备份与主数据库同步
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM
(
SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME) IN
(
SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#
)
) ARCH,
(
SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY
WHERE (THREAD#,FIRST_TIME )
IN
(
SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#
)
) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
1 90 90
1 90 90
--说明 Last Sequence Received和Last Sequence Applied ，不应该有任何差异。
步骤3：在物理备库上停止日志应用服务
SQL> alter database recover managed standby database cancel;
or
DGMGRL> edit database 'MPCDB197' set state="APPLY-OFF";
DGMGRL> show database 'MPCDB197';
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
--查看数据库状态
SQL> set line 300
SQL> SELECT SWITCHOVER_STATUS,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE OTECTION_MODE
------------------------------------------------------------ ----------------------------------
not allowed-PHYSICAL STANDBY- EAD ONLY-MAXIMUM PERFORMANCE
步骤4：在主服务器上设置逻辑备用参数
--查看数据库状态
SQL> set line 300
SQL>
SELECT SWITCHOVER_STATUS,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE FROM V$DATABASE;
SWITCH_STATUS DATABASE_ROLE OPEN_MODE PROTECTION_MODE
------------------------------------------------------------ --------------------------
TO STANDBY PRIMARY READ WRITE MAXIMUM PERFORMANCE
SQL> show parameter log_archive_dest_1
VALUE
------------------------------------ --------------------------------- --------------------------- location=/u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB196
--另外之前的LOG_ARCHIVE_DEST_1是valid for (all_logfiles,all_roles)， 需要修改为online_logfiles,all_roles)
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(online_logfiles,all_roles) db_unique_name=MPCDB196' scope=both;
--LOG_ARCHIVE_DEST_2 不做修改确保和备库一致(dgmgrl 后备库为空）
SQL> show parameter log_archive_dest_2;
VALUE
------------------------------------ --------------------------------- ----
service="sl", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="MPCDB197" net_timeout=30, valid_for=(online_logfile,all_roles)
--接着在OS上新建standby归档目录，新增的log_archive_dest_3指向它
[oracle@DB196 ~]$ mkdir /u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog_logical
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=MPCDB196' scope=both;
SQL> alter system set log_archive_dest_state_3=enable;
步骤5：在主库构建LogMiner字典
--DBMS_LOGSTDBY.BUILD存储过程等待所有现有事务完成。在主数据库上执行的长时间运行的事务将影响此命令的时效性
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
步骤6：将物理备库转换到逻辑备用数据库(备库中运行)
--对于db_name，指定一个数据库名称来标识新的逻辑备用数据库。如果您正在使用一个备用的spfile，那么命令将更新db_name参数，否则它会发出一个消息，提醒您在关闭数据库后设置db_name参数的名称。因为要做滚动升级，所以用原来的db_name,详细信息可参考
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;
or
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY MPCDB197;
步骤7：将逻辑备库启动到mount 状态
SQL> shutdown immediate;
ORA-01109: database not open
SQL> startup mount;
步骤8：修改逻辑备库参数
--另外之前的LOG_ARCHIVE_DEST_1是valid for (all_logfiles,all_roles)， 需要修改为online_logfiles,all_roles)
SQL> show parameter log_archive_dest_1
NAME
------------------------------------
location=/u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB197
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(online_logfiles,all_roles) db_unique_name=MPCDB197' scope=both;
System altered.
----LOG_ARCHIVE_DEST_2 不做修改这里是确保和主库的参数保持一致
--接着在OS上新建standby归档目录，新增的log_archive_dest_3指向它
[oracle@DB197 ~]$ mkdir /u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog_logical
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/fast_recovery_area/MPCDB/MPCDB/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=MPCDB197' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
步骤9：打开逻辑备库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> ALTER DATABASE OPEN;
a.禁止在逻辑备库端删除外籍日志
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
b.执行以下语句可以将逻辑备库不支持的而又在主库上运行过的事务记录到DBA_LOGSTDBY_EVENTS中
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED', DBMS_LOGSTDBY.MAX_EVENTS);
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('RECORD_UNSUPPORTED_OPERATIONS', 'TRUE');
--启动在逻辑备库上的SQL APPLY:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
SQL> set lines 300
SQL>
SELECT SWITCHOVER_STATUS,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE FROM V$DATABASE;
SWITCH_STATUS DATABASE_ROLE OPEN_MODE PROTECTION_MODE
------------------------------------------------------------ -----------------------------------
NOT ALLOWED-LOGICAL STANDBY-READ WRITE MAXIMUM ERFORMANCE
步骤10：
DGMGRL> edit database 'MPCDB197' set state="APPLY-ON";
步骤11：啟動快速故障轉移功能
DGMGRL> enable fast_start failover;
DGMGRL> start observer
