最近有客户可能要上逻辑STANDBY,验证下一些操作
做个记录
-- 排表
alter database stop logical standby apply;
EXEC DBMS_LOGSTDBY.SKIP('DML','CTAIS3','TEST_NONPK')
EXEC DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','CTAIS3','TEST_NONPK')
alter database start logical standby apply immediate;
-- 重新初始化
alter database stop logical standby apply;
EXEC dbms_logstdby.instantiate_table('CTAIS3','TEST_NONPK','TO_PROD')
EXEC DBMS_LOGSTDBY.UNSKIP('DML','CTAIS3','TEST_NONPK')
EXEC DBMS_LOGSTDBY.UNSKIP('SCHEMA_DDL','CTAIS3','TEST_NONPK')
alter database start logical standby apply immediate;
可以使用EXP/IMP等办法同步,主库EXP前一定要停止备库APPLY。
IMP后启动APPLY。可能有重复数据,需要使用SKIP_ERROR跳过重复数据
-- 错误数据处理
alter database stop logical standby apply;
EXEC DBMS_LOGSTDBY.SKIP_ERROR('DML','CTAIS3','TEST_PK');
alter database start logical standby apply immediate;
不是按照事务丢弃数据,只丢弃错误数据
discard记录:dba_logstdby_events
-- 排用户
alter database stop logical standby apply;
EXEC DBMS_LOGSTDBY.SKIP('DML','CTAIS4','%');
EXEC DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','CTAIS4','%');
alter database start logical standby apply immediate;
通配符 %,默认使用like方式确定对象
-- 排除DDL
使用NON_SCHEMA_DDL不复制那些和SCHEMA无关的DDL,如角色
DBLINK不会复制
跳过了的DDL :SELECT * FROM dba_logstdby_events WHERE status LIKE 'ORA-%DDL%' ORDER BY 1 DESC
建议跳过创建索引的语句 exec DBMS_LOGSTDBY.SKIP('ANALYZE INDEX',NULL, NULL, NULL);
在逻辑备库上自己维护索引
-- 目标端维护动作
alter session disable guard;
exec dbms_stats.gather_schema_stats('ctais2');
create table test(id number);
insert into test values(111);
alter session enable guard;
alter database guard all/standby/none; 禁止所有维护动作/只对复制维护的对象禁止/不禁止
-- 添加数据文件的同步
db_file_name_convert参数无效
需要定义HANDLE
CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL(OLD_STMT IN VARCHAR2,STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,NAME IN VARCHAR2,XIDUSN IN NUMBER,XIDSLT IN NUMBER,
XIDSQN IN NUMBER,ACTION OUT NUMBER,NEW_STMT OUT VARCHAR2) AS
BEGIN
NEW_STMT := REPLACE(OLD_STMT, 'ORCL', 'DGORCL');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL;
/
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', proc_name => 'SYS.HANDLE_TBS_DDL');
-- 重设相关表高水位
alter database stop logical standby apply;
EXEC dbms_logstdby.set_tablespace('SYSAUX')
alter database start logical standby apply immediate;
也可用于迁移相关表到其他表空间。默认在SYSAUX表空间
做个记录
-- 排表
alter database stop logical standby apply;
EXEC DBMS_LOGSTDBY.SKIP('DML','CTAIS3','TEST_NONPK')
EXEC DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','CTAIS3','TEST_NONPK')
alter database start logical standby apply immediate;
-- 重新初始化
alter database stop logical standby apply;
EXEC dbms_logstdby.instantiate_table('CTAIS3','TEST_NONPK','TO_PROD')
EXEC DBMS_LOGSTDBY.UNSKIP('DML','CTAIS3','TEST_NONPK')
EXEC DBMS_LOGSTDBY.UNSKIP('SCHEMA_DDL','CTAIS3','TEST_NONPK')
alter database start logical standby apply immediate;
可以使用EXP/IMP等办法同步,主库EXP前一定要停止备库APPLY。
IMP后启动APPLY。可能有重复数据,需要使用SKIP_ERROR跳过重复数据
-- 错误数据处理
alter database stop logical standby apply;
EXEC DBMS_LOGSTDBY.SKIP_ERROR('DML','CTAIS3','TEST_PK');
alter database start logical standby apply immediate;
不是按照事务丢弃数据,只丢弃错误数据
discard记录:dba_logstdby_events
-- 排用户
alter database stop logical standby apply;
EXEC DBMS_LOGSTDBY.SKIP('DML','CTAIS4','%');
EXEC DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','CTAIS4','%');
alter database start logical standby apply immediate;
通配符 %,默认使用like方式确定对象
-- 排除DDL
使用NON_SCHEMA_DDL不复制那些和SCHEMA无关的DDL,如角色
DBLINK不会复制
跳过了的DDL :SELECT * FROM dba_logstdby_events WHERE status LIKE 'ORA-%DDL%' ORDER BY 1 DESC
建议跳过创建索引的语句 exec DBMS_LOGSTDBY.SKIP('ANALYZE INDEX',NULL, NULL, NULL);
在逻辑备库上自己维护索引
-- 目标端维护动作
alter session disable guard;
exec dbms_stats.gather_schema_stats('ctais2');
create table test(id number);
insert into test values(111);
alter session enable guard;
alter database guard all/standby/none; 禁止所有维护动作/只对复制维护的对象禁止/不禁止
-- 添加数据文件的同步
db_file_name_convert参数无效
需要定义HANDLE
CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL(OLD_STMT IN VARCHAR2,STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,NAME IN VARCHAR2,XIDUSN IN NUMBER,XIDSLT IN NUMBER,
XIDSQN IN NUMBER,ACTION OUT NUMBER,NEW_STMT OUT VARCHAR2) AS
BEGIN
NEW_STMT := REPLACE(OLD_STMT, 'ORCL', 'DGORCL');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL;
/
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', proc_name => 'SYS.HANDLE_TBS_DDL');
-- 重设相关表高水位
alter database stop logical standby apply;
EXEC dbms_logstdby.set_tablespace('SYSAUX')
alter database start logical standby apply immediate;
也可用于迁移相关表到其他表空间。默认在SYSAUX表空间
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-730526/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8242091/viewspace-730526/