物理备库转逻辑备库的处理

Physical备库切换到logical备库，需要在primary库构建LogMiner字典及开启supplemental日志，在这之前应先停用physical备库的MRP进程，以避免提前应用含LogMiner字典的REDO数据，造成转换为逻辑Standby后，SQL应用时没有LogMiner字典数据的REDO数据而影响到逻辑Standby与Primary的正常同步。

备库执行;

1、ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2、生成LogMiner字典，这样redo日志中的变化才能被LogMiner字典的SQL Apply组件正确解析。

主库构建LogMiner字典

create tablespace logmnrtbs datafile '/opt/oracle/oradata/RHT/logmnrtbs.dbf' size 100m autoextend on next 5m maxsize 2000m;

execute dbms_logmnr_d.set_tablespace('logmnrtbs');

exec dbms_logstdby.build;

3、如果需要处理TEMP就进行TEMP表空间的处理

ALTER DATABASE TEMPFILE '/opt/oracle/oradata/RHT/temp01.dbf' DROP;

create temporary tablespace temp1 tempfile '/opt/oracle/oradata/RHT/temp.dbf' size 2000m autoextend on next 100m maxsize unlimited;

4、转physical备库为logical备库：

把物理备库恢复为逻辑备库

SQL> shutdown immediate;

SQL> startup mount exclusive;

ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;

alter database recover to logical standby RHT(数据库名是db_name，很重要一定要弄对);

5、由于逻辑standby与primary数据库事务并不一致，因此第一次打开时必须指定resetlogs子句

shutdown immediate;

startup mount;

ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

–开启实时应用

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

–开启应用

ALTER DATABASE START LOGICAL STANDBY APPLY;

–关闭应用

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

select name,open_mode,database_role,protection_mode from v$database; 

优先性能

EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS',8);

EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS',4);

EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS',16); 

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

过滤对于审计表的处理

exec dbms_logstdby.skip('DML','SYS','AUD$');

exec dbms_logstdby.skip('SCHEMA_DDL','SYS','AUD$');

1、dba_logstdby_events

查看逻辑standby近期操作日志

2、dba_logstdby_log

记录当前归档日志应用情况

select * from dba_logstdby_events order by event_time;

3、v$logstdby_status

显示LogMiner的统计信息和状态

4、v$logstdby_process

SELECT type, status, high_scn FROM v$logstdby;

显示当前log应用服务的相关信息，常用于诊断日志逻辑应用的性能问题

5、v$logstdby_progress

显示log应用服务当前进行状态

6、v$logstdby_status

显示sql应用的大致状态

故障处理案例

一、故障现象：逻辑STANDBY数据库日志文件异常，无法应用

先查看一下哪个文件异常，出现GAP：

解决逻辑standby Gap问题

2.1 对于logical standby database，在logical standby database上查询DBA_LOGSTDBY_LOG视图。

SELECT THREAD#, SEQUENCE#, FILE_NAME

  FROM DBA_LOGSTDBY_LOG L

 WHERE NEXT_CHANGE# NOT IN

       (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#)

 ORDER BY THREAD#, SEQUENCE#;

 

2.2 Copy 缺失的log file到logical standby database，然后注册：
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_7.arc';
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_8.arc';
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/disk1/oracle/dbs/log-1292880008_9.arc';

处理过程一：把正确的文件放在指定目录后参试再注册，但提示重复注册

SQL> ALTER DATABASE REGISTER LOGFILE '/archivelog/lstandbyarch_2_27774_614088933.arc';

ALTER DATABASE REGISTER LOGFILE '/archivelog/lstandbyarch_2_27774_614088933.arc'

*

ERROR at line 1:

ORA-01289: cannot add duplicate logfile

处理过程二：尝试删除日志，但不成功

SQL> execute dbms_logmnr.add_logfile('/archivelog/lstandbyarch_2_27774_614088933.arc',dbms_logmnr.REMOVEFILE);

BEGIN dbms_logmnr.add_logfile('/archivelog/lstandbyarch_2_27774_614088933.arc',dbms_logmnr.REMOVEFILE); END;

*

ERROR at line 1:

ORA-01290: cannot remove unlisted logfile

/archivelog/lstandbyarch_2_27774_614088933.arc

ORA-06512: at "SYS.DBMS_LOGMNR", line 68

ORA-06512: at line 1

处理过程三：通过delete from system.logmnr_log$删除日志成功，后注册成功，问题排除

SQL> delete from system.logmnr_log$ where session#=1 and sequence#=27774;

提示开启了备库模式不能删除时：

alter session disable guard;

会话已更改。

JSSLDG2> create table tmp55 as select * From b;

表已创建。

只有关闭了guard保护之后，才能操作数据，然后别忘了再启用guard，以避免不经意的操作对逻辑standby的配置造成影响。

JSSLDG2> alter session enable guard;

1 row deleted.

SQL> ALTER DATABASE REGISTER LOGFILE '/archivelog/lstandbyarch_2_27774_614088933.arc';

Database altered.

SQL> 

二、故障现象： ORA-26786造成逻辑备库无法应用SQL一例

1、找到事务异常的sql和事务id

SQL> select XIDUSN, XIDSLT, XIDSQN , status , event from dba_logstdby_events order by event_time;

XIDUSN XIDSLT XIDSQN STATUS EVENT
---------- ---------- ---------- ------------------------------ -------------------------------
5 22 1826 ORA-01403: no data found delete from "SHANKAR"."TEST1" where "ID" = 1 and
"ENAME" = 'shankar' and ROWID = 'AAAAAAAABAAAJHaAAA'

You should not compare the ROWID of PRIMARY & STANDBY database as it will be complete 
different and sql apply will not check the row based on ROWID that is shown in the sql statement above.

2.执行跳过这个事务的脚本
2) Skip the failing transaction from logical standby database.

Execute the procedure dbms_lostdby.skip_transaction(XIDUSN , XIDSLT ,XIDSQN) of the failing transaction.

SQL> exec dbms_logstdby.skip_transaction (5,22,1826);

3、重新恢复同步命令。

重新初始化同步：

注意初始化时，注意关闭数据同步后再操作：

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

exec DBMS_LOGSTDBY.INSTANTIATE_TABLE('RHT', 'TSYSOPTION', 'RHTZS');