1.long 字段的无法使用OGG 同步

 

2.clob字段的导入导出Bug , 生产使用network-link 导入导出太慢了，本地导入导出速度会快3到4倍 。但是测试环境的情况却相反

测试环境和生产环境测试结果如下： 最后还是以生产环境的数据为主。

原因有2点，第一 ： 测试环境的网络没有拥塞，所以感觉测试环境network-link I/O 速度可以接受。第二测试环境的I/O 太慢了，跟生产环境不是一个等量级，所以感觉生产环境 本地I/O 会快很多。

 

数据局迁移遇到的Bug

 

1.DRAFT_LOG 这个表有6G ，有clob 字段

 2．_LOG 这张表因为有clob 字段，这张表是核心表，导出时间和导入时间会有比较长。大概有19G ，

因为有clob 字段，导出时间会比较长。根据我们测试，

 

 

expdp /impdp 索引分离迁移  http://blog.51cto.com/itbull/1078724

数据泵 expdp导出

nohup expdp  system/******** dumpfile=lysb_20121113_%U.dmp directory=dmp_dir schemas=scott logfile=expdp_20121113.log job_name=lysbexpdp &

index导入时会占用大量时间，导入时先导入处index外的其他对象包括数据

nohup impdp system/******** dumpfile=lysb_20121113_%U.dmp directory=dmp_dir schemas=scott logfile=impdp_20121113.log job_name=lysbexpdp TABLE_EXISTS_ACTION=REPLACE EXCLUDE=INDEX &

index可以再重建或者单独导入

nohup impdp system/******** dumpfile=lysb_20121113_%U.dmp directory=dmp_dir schemas=scott logfile=impdp_201211132.log job_name=lysbexpdp include=INDEX &

 

 

 

测试1：（测试环境），不使用network_link 方式，

 

EXPDP 这2张表时间会达到3个小时。

IMPDP 这2张表时间也会超过3个小时。

 

 

测试２:  (测试环境) ，使用network_link 方式，

 

时间会缩短一倍，达到3个半小时。

 

 

测试３：（生产环境），使用network_link 方式，

 

从上午11点25发起，持续到16点，已经跑了4个半小时，还在导入。

 

测试4:  (生产环境) ，使用本地导入导出方式，不使用network_link 方式，除去BAK_RGCT_DRAFT_LOG这个6G表。

 

EXPDP 所有生产用户时间会达到1个小时10分钟。

IMPDP 所有生产用户时间会达到1个小时10分钟。

 

可以在2个半小时左右完成，

 

 

1.

检查项 "1.创建空库不需要初始化

2.检查生产的FRA空间检查

生产的在维护期间会创建一个回滚点并保留7天，所以要检查灾备FRA的卷空间是否能保留7天的日志量。不足需申请扩空间。"

新建目标空库 "1，只包含system,sysaux,undotbs1,temp,users 表空间，不需要初始化。

set line 200 pagesize 200

select tablespace_name from dba_tablespaces a

where (a.tablespace_name not in ('TEMP','SYSAUX','SYSTEM','USERS')

and a.contents not in ('TEMPORARY','UNDO')) ;

2，字符集、block size与源库保持一致

select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

show parameter block_size;

3，安装组件和源生产一致。

select comp_name,version,status from dba_registry order by 1;

4.源库和目标库具有相同的timeone；

SELECT VERSION FROM V$TIMEZONE_FILE;

8.db_cache_size

设置为和源库相同

show parameter db_cache_size

9.注：9.1.目标库cpu_count 不要在spfile里进行设置，不需要和源库保持一致

9.2.目标库的parallel_max_servers设置要和源库一致。

10.job_queue_processes，aq_tm_processes 设置为0。

11.修改新建的目标库为非归档模式

12，11g数据库参数按标准新建库手册。

13. 关闭Flashback。

select flashback_on from v$database; --结果为NO

15.awr配置为间隔15分钟，保留90天

exec dbms_workload_repository.modify_snapshot_settings(interval=>15, retention=>90*24*60);

col SNAP_INTERVAL format a20

col RETENTION format a20

select * from dba_hist_wr_control;"

检查迁移对象是否是闭包的 "1：检查迁移用户引用非迁移用户的对象，如果引用的是系统表酌情处理；如果引用的是非系统表则要整改。

select * from dba_dependencies dd where dd.owner in

('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') --替换为实际用户

and dd.referenced_owner not in

('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE')

2：检查非迁移用户引用迁移用户的对象，如果存在则与开发讨论处理方法。

select * from dba_dependencies dd where dd.type<>'SYNONYM' and dd.owner not in

('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ----替换为实际用户

and dd.referenced_owner in

('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ----替换为实际用户

/"

检查迁移schema的trigger是否是闭包的 "1:检查触发器的owner<>table_owner且owner不在迁移用户列表而table_owner在迁移用户列表的情况，这种触发器需要手工迁移

select * from dba_triggers where owner<>table_owner and owner not in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') and table_owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ;

2:检查触发器的owner<>table_owner且owner在迁移用户列表而table_owner不在迁移用户列表的情况，这种触发器视情况决定是否迁移

select * from dba_triggers where owner<>table_owner and owner not in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') and table_owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ;

3:检查触发器的triggering_eventowner'LOGON '且owner在迁移用户列表或table_owner在迁移用户列表的情况，这种触发器expdp不能导出需要手工迁移

select * from dba_triggers where triggering_event='LOGON 'and (owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') or table_owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE'));

"

根据10g生产库中待迁移job清单信息，生成job迁移脚本并在测试环境验证通过。特别注意：脚本所有待迁移job其next_date都必须设为无限大。 t_update_status();

检查10g生产库中是否有业务scheduler存在，这些scheduler也要进行迁移，如果存在有，本步要把迁移scheduler的脚本准备好 0

"

创建11g目标库到10g生产库的dblink" "create public database link dblink_migup connect to dbmgr identified by &pw

using ' 生产库连接串 ';

3:验证dblink是否通

select * from dual@dblink_migup; --有结果返回表示通的否则不通需处理

"

"

1. 创建要迁移的用户清单配置表(sys..t_user_cfg)

" "create table sys.t_user_cfg as SELECT username, default_tablespace

FROM dba_users@dblink_migup

WHERE username not in

('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS',

'CTXSYS', 'XDB', 'WKSYS', 'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS',

'LBACSYS', 'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS', 'ADAMS',

'TRACESVR', 'DBSNMP', 'PERFSTAT', 'DBSTATS', 'EXPFULL',

'MDDATA', 'MGMT_VIEW', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS',

'SYSMAN', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS',

'MDSYS', 'FLOWS_FILES', 'SPATIAL_WFS_ADMIN_USR',

'SPATIAL_CSW_ADMIN_USR', 'APEX_PUBLIC_USER', 'XS$NULL','DMSYS')

minus

select username, default_tablespace from dba_users;"

目标端新增表空间 "1.查看源库表空间名称及大小，在目标库新建相同大小表空间

set line 200 pagesize 200

select tablespace_name from dba_tablespaces a

where a.tablespace_name not in ('TEMP','SYSAUX','SYSTEM','USERS') ;

--注意临时表空间是否有特殊的，CREATE TEMPORARY TABLESPACE RLMSTMP TEMPFILE '+DATA_HDG' SIZE 100M AUTOEXTEND ON;

2.打开所有表空间的自动扩展

select 'alter database datafile '''||file_name||''' autoextend on next 100M;' from dba_data_files ;

select 'alter database tempfile '''||file_name||''' autoextend on next 100M;' from dba_temp_files ;

执行上面生成的脚本

复核：

select df.tablespace_name,df.autoextensible from dba_temp_files df where df.autoextensible='NO';

select df.tablespace_name,df.autoextensible from dba_data_files df where df.autoextensible='NO';"

目标端创建role "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/01_create_role.sql

select 'create role ' || role || ';'

from (select role

from dba_roles@dblink_migup

minus

select role from dba_roles);

spool off;

在目标库执行01_create_role.sql"

创建密码函数 "执行附件脚本创建密码函数sys.VERIFY_FUNCTION_11G

PS:这个密码函数如果老库的名称不一样，需要把老库的函数迁移过来。"

创建profile "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/02_deal_profile.sql

select (case

when rn = 1 then

('create profile ' || profile ||' limit ' || resource_name ||' ' || limit || (case

when rn = max_rn then

';'

end)) else(resource_name || ' ' || limit || (case

when rn = max_rn then

';'

end)) end)

from (select profile,

resource_name,

limit,

row_number() over(partition by profile order by profile, resource_name) rn,

count(*) over(partition by profile) max_rn

from dba_profiles@dblink_migup

where profile in

(select distinct profile

from dba_profiles@dblink_migup

minus

select distinct profile from dba_profiles)

order by profile, resource_name)

order by profile, resource_name;

spool off;

执行02_deal_profile.sql

alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

alter profile default limit FAILED_LOGIN_ATTEMPTS 10;

复核：

select * from dba_profiles minus select * from dba_profiles@dblink_migup;

select * from dba_profiles@dblink_migup minus select * from dba_profiles;"

创建公共同义词 "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/03_create_public_synonym.sql

SELECT

'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'

FROM dba_synonyms@dblink_migup

WHERE owner = 'PUBLIC'

AND table_owner IN ( select username from sys.t_user_cfg t) ;

spool off;

在目标库执行03_create_public_synonym.sql"

移植cron "

将原hp平台上的crontab移植到新linux主机上。

"

 

 2.

21:00 导出BBSP.RGCT_DRAFT_LOG "export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

cd /db/db1/oradata/dba/exp_table

vi expdp_rgct_log.par

userid='/ as sysdba'

directory=schema_exp

dumpfile=expdp_rgct_log_%U.dmp

logfile=expdp_rgct_draft_log.log

parallel=4

filesize=10240M

tables=(BBSP.RGCT_DRAFT_LOG)

content=all "

22:00 scp传输到linux scp *.dmp cradmin@10.198.217.193:/db/db1/app/opdb1/db1_impdp

22:00 新库创建用户 "create user bbsp identified by values 'DF01EB216CE6E6BE' default tablespace bbsp profile default;

grant unlimited tablespace to bbsp;

alter user bbsp quota unlimited on users;

alter user bbsp quota unlimited on bbsp;

"

22:10 导入BBSP.RGCT_DRAFT_LOG "chown -R opdb1:dba /db/db1/app/opdb1/db1_impdp/*.dmp

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

cd /db/db1/app/opdb1/db1_impdp

vi impdp_rgct_log.par

userid='/ as sysdba'

directory=schema_imp

dumpfile=expdp_rgct_log_%U.dmp

logfile=impdp_rgct_draft_log.log

parallel=4

tables=(BBSP.RGCT_DRAFT_LOG)

content=all

nohup impdp parfile=impdp_rgct_log.par &

检查导出导入日志是否有报错"

T-0 "DBA检查DB中的long oper、

rbs占用，

并请运营确认和做相应处理" "sqlplus '/ as sysdba'

alter system switch logfile ;

alter system switch logfile ;

alter system switch logfile ;

alter system archive log current;

1）检查long operation

select sid, target,opname, sofar,totalwork,time_remaining

from gv$session_longops where time_remaining>0;

2）检查占用回滚段较多的事务：

select xidusn,s.last_call_et, s.username, used_ublk,USED_UREC,space,

RECURSIVE, NOUNDO, START_UBAFIL,start_ubablk, start_uext

from gv$transaction t, gv$session s

where t.ses_addr=s.saddr;

"

1:00 维护时间开始处理，通知应用停止应用 应用将库的所有相关应用系统和job、ETL任务等停止下来。

T-0 检查卷，冻结源库双机软件 "冻结源库的双机，

冻结目标库的双机"

T-0 全备，磁带永久保留

T-0 DBA停止报警，屏蔽cron "cd $HOME

touch cron_null

crontab cron_null

crontab -l 返回值应该为空"

T-0 修改端口 "lsnrctl stop db1

修改监听端口为1537

cd $ORACLE_HOME/network/admin

vi listener.ora

lsnrctl start db1"

T-0 重建到旧库的db links。 "1:在目标库重建迁移用的临时dblink ---直接使用连接串

drop public database link dblink_migup;

create public database link dblink_migup connect to dbmgr identified by db1234DBA

using ' (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.198.69.10)(PORT = 1537)) (CONNECT_DATA =(sid = db1)))';

2:验证dblink是否通

select * from dual@dblink_migup; --有结果返回表示通的否则不通需处理"

T-0 修改job_queue_processes，aq_tm_processes为0，关闭audit "保留记录job_queue_processes及aq_tm_processes修改前的值。

show parameter job_queue_processes --- 1000

show parameter aq_tm_processes ---1

alter system set job_queue_processes=0 scope=both;

alter system set aq_tm_processes=0 scope=both;

alter system set audit_trail=none scope=spfile;"

T-0 "DBA检查DB中的long oper、

rbs占用，

并请运营确认和做相应处理" "sqlplus '/ as sysdba'

alter system switch logfile ;

alter system switch logfile ;

alter system switch logfile ;

alter system archive log current;

1）检查long operation

select sid, target,opname, sofar,totalwork,time_remaining

from gv$session_longops where time_remaining>0;

2）检查占用回滚段较多的事务：

select xidusn,s.last_call_et, s.username, used_ublk,USED_UREC,space,

RECURSIVE, NOUNDO, START_UBAFIL,start_ubablk, start_uext

from gv$transaction t, gv$session s

where t.ses_addr=s.saddr;

"

T-0 修改stream_pool_size alter system set streams_pool_size=128M scope=both;

T-0 1.设置JOB的next_date为无限大 "备份原库的Job:create table dbmgr.dbajobs_old_bak as select * from dba_jobs;

复核：SELECT COUNT(*) FROM dbmgr.dbajobs_old_bak;

设置JOB的next_date为无限大："

T-0 原库创建用户清单表sys.t_user_list "create table sys.t_user_cfg as SELECT username, default_tablespace

FROM dba_users

WHERE username not in

('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS',

'CTXSYS', 'XDB', 'WKSYS', 'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS',

'LBACSYS', 'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS', 'ADAMS',

'TRACESVR', 'DBSNMP', 'PERFSTAT', 'DBSTATS', 'EXPFULL',

'MDDATA', 'MGMT_VIEW', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS',

'SYSMAN', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS',

'MDSYS', 'FLOWS_FILES', 'SPATIAL_WFS_ADMIN_USR',

'SPATIAL_CSW_ADMIN_USR', 'APEX_PUBLIC_USER', 'XS$NULL','DMSYS');

grant select on sys.t_user_cfg to public;"

T-0 部署数据比对的procedure "grant select on dba_tab_columns to dbmgr;

create table dbmgr.TAB_HASH

(

OWNER VARCHAR2(30),

TABLE_NAME VARCHAR2(50),

SQL_VALUE VARCHAR2(4000),

HASH_VALUE NUMBER(20)

) ;

CREATE OR REPLACE PROCEDURE DBMGR.GET_TAB_HASH8(V_OWNER VARCHAR2,

V_TABLE_NAME VARCHAR2) AS

v_sql_temp varchar2(4000);

v_hash number(20);

BEGIN

FOR r in (SELECT COLUMN_NAME

FROM DBA_TAB_COLUMNS

WHERE OWNER = V_OWNER

AND TABLE_NAME = V_TABLE_NAME

AND DATA_TYPE NOT IN ('CLOB', 'BLOB')

and OWNER IN (SELECT USERNAME FROM sys.t_user_cfg)

order by 1 asc) loop

v_sql_temp := v_sql_temp || '||' || R.COLUMN_NAME;

end loop;

v_sql_temp := 'select/*+ parallel(t 8) full(t)*/ sum(dbms_utility.get_hash_value(' ||

substr(v_sql_temp, 3) || ', 0, 999999999)) from ' ||

V_OWNER || '.' || V_TABLE_NAME || ' t';

execute immediate v_sql_temp

into v_hash;

insert into tab_hash

(owner, table_name, sql_value, hash_value)

values

(V_OWNER, V_TABLE_NAME, v_sql_temp || ';', v_hash);

commit;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

/"

T-0 为GG回滚库做的disable/enable trigger以及constraint脚本。 "set linesize 300

SPOOL $HOME/disable_constraint_r.sql

SELECT 'spool ./disable_constraint_r.log' FROM dual;

SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'

FROM dba_constraints

WHERE owner IN (select username from sys.t_user_cfg)

AND constraint_type = 'R'

ORDER BY constraint_type desc, owner, table_name, constraint_name;

SELECT 'spool off' FROM dual;

SPOOL OFF

SPOOL ./disable_trigger.sql

SELECT 'spool ./disable_trigger.log' FROM dual;

SELECT

'alter trigger '||owner||'.'||trigger_name||' disable;'

FROM dba_triggers

WHERE owner IN (select username from sys.t_user_cfg)

AND status <> 'DISABLED'

ORDER BY owner, table_name, trigger_name;

SELECT 'spool off' FROM dual;

SPOOL OFF

SPOOL ./enable_trigger.sql

SELECT 'spool ./enable_trigger.log' FROM dual;

SELECT

'alter trigger '||owner||'.'||trigger_name||' enable;'

FROM dba_triggers

WHERE owner IN (select username from sys.t_user_cfg)

AND status = 'ENABLED'

ORDER BY owner, table_name, trigger_name;

SELECT 'spool off' FROM dual;

SPOOL OFF

SPOOL ./enable_constraint_r_novalidate.sql

SELECT 'spool ./enable_constraint_r_novalidate.log' FROM dual;

SELECT

'alter table '||owner||'.'||table_name||' modify constraint '||constraint_name||' enable novalidate;'

FROM dba_constraints

WHERE owner IN (select distinct username from sys.t_user_cfg)

AND constraint_type = 'R'

AND STATUS = 'ENABLED'

ORDER BY constraint_type desc, owner, table_name, constraint_name;

SELECT 'spool off' FROM dual;

SPOOL OFF

SPOOL ./enable_constraint_src_r.sql

SELECT 'spool ./enable_constraint_src_r.log' FROM dual;

SELECT

'alter table '||owner||'.'||table_name||' modify constraint '||constraint_name||' enable novalidate;'

FROM dba_constraints

WHERE owner IN (select distinct username from sys.t_user_cfg)

AND constraint_type = 'R'

AND status = 'ENABLED'

ORDER BY constraint_type desc, owner, table_name, constraint_name;

SELECT 'spool off' FROM dual;

SPOOL OFF"

T-0 重启生产库 "

shutdown immediate;

startup

"

T-0 停止同城灾备db 没有灾备库

T-0 清空回收站 Purge dba_recyclebin;

T-0 expdp导出 "export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

connect / as sysdba

create or replace directory schema_exp as '/db/db1/oradata/expdp_db1';

cd /db/db1/oradata/expdp_db1

userid='/ as sysdba'

directory=schema_exp

dumpfile=expdp_schemas_%U.dmp

logfile=expdp_schemas.log

parallel=6

filesize=10240M

schemas=BBSP,BBSPODS,BCDPETL,DBMONOPR

content=all

EXCLUDE=TABLE:""in ('BAK_RGCT_DRAFT_LOG','RGCT_DRAFT_LOG')""

nohup expdp parfile=expdp.par &"

检查error more expdp_schemas.log |grep -i error

T-0 impdp导入 "scp *.dmp cradmin@10.198.217.193:/db/db1/app/opdb1/db1_impdp

chown -R opdb1:dba /db/db1/app/opdb1/db1_impdp/*.dmp

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

userid='/ as sysdba'

directory=schema_imp

dumpfile=expdp_schemas_%U.dmp

logfile=impdp_schemas.log

parallel=6

schemas=BBSP,BBSPODS,BCDPETL,DBMONOPR

content=all

nohup impdp parfile=impdp.par &"

T-0 检查error more impdp_schemas.log |grep -i error

T-0 grant GRANT SELECT ON "BBSP"."RGCT_DRAFT_LOG" TO "BCDPETL"

T-0 查出时点ID记录数最大的做保存 select max(l.id) from bbsp.rgct_draft_log l

T-0 用记录的最大id查出新增数据 select count(*) from bbsp.rgct_draft_log l where l.id>xxxxx; --替换为上一步查出的值

T-0 补录新增数据到bbsp.rgct_draft_log "insert into bbsp.rgct_draft_log select * from bbsp.rgct_draft_log@dblink_migup where id>xxxxxxxx ;

commit;"

T-0 "数据核对

并且点开lob字段查询是否有值" "select count(*) from bbsp.rgct_draft_log ;

select * from bbsp.rgct_draft_log ;"

T-0 清除impdp导到目标库sys用户下的job并复核 "select * from dba_jobs;

清除impdp导到sys用户下的job

DECLARE X NUMBER;

BEGIN

for cur in ( select * from dba_jobs j where j.log_user = 'SYS') loop

SYS.DBMS_iJOB.remove(job => cur.job );

COMMIT;

end loop ;

commit;

END;

/"

T-1 创建公共同义词 "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/03_create_public_synonym.sql

SELECT

'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'

FROM dba_synonyms@dblink_migup

WHERE owner = 'PUBLIC'

AND table_owner IN ( select username from sys.t_user_cfg t) ;

spool off;

在目标库执行03_create_public_synonym.sql"

T-0 生成用户审计信息 "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/04_audit_users.sql

SELECT

'audit connect by '||user_name||';'

FROM dba_stmt_audit_opts@dblink_migup

WHERE audit_option = 'CREATE SESSION'

and user_name in ( select username from sys.t_user_cfg )

and user_name is not null;

SELECT

'audit '||audit_option||' by '||user_name||' by session whenever successful;'

FROM dba_stmt_audit_opts@dblink_migup

WHERE audit_option <> 'CREATE SESSION'

and user_name in ( select username from sys.t_user_cfg )

and user_name is not null;

SPOOL OFF;"

T-0 添加用户审计信息 执行04_audit_user.sql

T-0 给用户和角色授权 "1.执行附件脚本04_grant.sql

2.可以看到生成的脚本$HOME/grant_role_priv.sql,$HOME/grant_sys_priv.sql,$HOME/grant_sys_tab_priv.sql

3.检查没问题后执行上面生成的3个脚本"

T-0 目标库编译失效对象 sqlplus> @?/rdbms/admin/utlrp.sql;

T-0 对比源库和目标库确保无新增失效对象 "select owner ,object_name, status from dba_objects where status = 'INVALID'

minus

select owner ,object_name, status from dba_objects@dblink_migup where status = 'INVALID';"

T-0 检查表和索引的统计信息是否被锁住 "select owner,table_name,partition_name,t.stattype_locked from dba_tab_statistics t where t.stattype_locked is not null and t.owner in (select username from sys.t_user_cfg);

select owner,index_name,table_owner,table_name,partition_name,stattype_locked from dba_ind_statistics t where t.stattype_locked is not null and t.owner in (select username from sys.t_user_cfg);

如果有结果，说明有表或索引统计信息被锁定

执行下列拼出来的语句

select case when partition_name is null then 'exec dbms_stats.unlock_table_stats(ownname =>''' ||owner || ''',tabname =>''' || table_name || ''');'

else 'exec dbms_stats.unlock_table_stats(ownname =>''' ||owner || ''',tabname =>''' || table_name ||''',partname =>'''|| partition_name||''');' end

from dba_tab_statistics t

where t.stattype_locked is not null

and t.owner in (select username from sys.t_user_cfg);

select case when partition_name is null then 'exec dbms_stats.unlock_table_stats(ownname =>''' ||table_owner || ''',tabname =>''' || table_name || ''');'

else 'exec dbms_stats.unlock_table_stats(ownname =>''' ||table_owner || ''',tabname =>''' || table_name ||''',partname =>'''|| partition_name||''');' end

from dba_ind_statistics t where t.stattype_locked is not null and t.owner in (select username from sys.t_user_cfg);"

T-0 "复核以下项在源和目标环境中是否相等

" " 检查迁移用户系统权限

检查迁移用户角色权限

检查迁移用户对象权限

检查迁移用户审计

检查迁移用户对象同义词

检查迁移用户db_link

检查迁移用户context

检查迁移用户的对象总数

迁移用户的constraint

检查迁移用户的index(含索引字段)

检查迁移用户的表结构

迁移用户的trigger

检查迁移用户的表记录数

检查迁移用户的seq数及last_number"

T-0 核心表数据比对 "select count(*)登记中心数 from BBSP.rgct_bill_info

union

select count(*)登记中心历史数 from BBSP.rgct_bill_hist

T-0 打开归档 "shutdown immediate;

startup nomount;

alter database mount;

alter database archivelog;

alter database open;"

T-0 关闭undo和temp表空间的自动扩展 "select 'alter database datafile '''||file_name||''' autoextend off;' from dba_data_files where tablespace_name like 'UNDO%';

select 'alter database tempfile '''||file_name||''' autoextend off;' from dba_temp_files ;

执行产出脚本"

T-0 复核job参数 "检查Job参数设置,设置为0

SQL> show parameter job_queue_processes --0

SQL> show parameter aq_tm_processes --0"

force logging开启 alter database force logging;

T-0 gg混滚链路处理 "alter table BBSP.CODE logging;

alter table BBSP.PRODUCT logging;

"

T-0 gg链路处理 按gg回滚手册进行部署

T-0 配置监听 "【新库恢复监听】

lsnrctl stop db1

并修改监听端口为1528

cd $ORACLE_HOME/network/admin

vi listener.ora

lsnrctl start db1 --监听端口是1528"

T-0 DNS切换，清理缓存 找应用值班同事执行

T-0 验证连通性 sqlplus客户端验证

T-0 启动中间件和相关应用

T-0 通知开发、应用验证应用

T-0 打开Job参数 "SQL> alter system set job_queue_processes = 1000 scope = both;

SQL> alter system set aq_tm_processes=1 scope = both;;

复核：

SQL> show parameter job_queue_processes

SQL> show parameter aq_tm_processes

"

T-0 恢复cron文件 无

T-0 注册catalog "Rman注册：

步骤如下（主库）：

shell>rman target / catalog rman11g/rman11g@cat11g

RMAN> register database;

RMAN> report schema;

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

RMAN>show all;"

 

 

2.1 set_job_nextdate.sql

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

set SERVEROUTPUT on size 200000

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss' ;

begin

for cur in ( select * from dba_jobs j where j.BROKEN = 'N' ) loop

sys.dbms_ijob.next_date(job => cur.job,next_date=>to_date('2999/9/9','yyyy/mm/dd')) ;

end loop ;

commit ;

end;

/

select count(1) from dba_jobs j where j.next_date < to_date('2999-09-09','yyyy-mm-dd') and j.broken = 'N';

 

2.2 04_grant.sql

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/grant_role_priv.sql

SELECT 'spool $HOME/grant_role_priv.log' FROM dual;

SELECT 'grant ' || granted_role || ' to ' || grantee || CASE admin_option

WHEN 'YES' THEN

' with admin option;'

ELSE

';'

END

FROM dba_role_privs@dblink_migup

WHERE grantee IN

( select username from sys.t_user_cfg

union

select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)

and granted_role like 'R\_%' escape '\'

) ;

SELECT 'spool off;' FROM dual;

SPOOL OFF;

commit;

SET termout on echo on

--SET termout OFF feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--@$HOME/grant_role_priv.sql

--commit;

 

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/grant_sys_priv.sql

SELECT 'spool $HOME/grant_sys_priv.log' FROM dual;

SELECT

'grant '||privilege||' to '||grantee

||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END

FROM dba_sys_privs@dblink_migup

WHERE grantee IN

( select username from sys.t_user_cfg

union

select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)

and granted_role like 'R\_%' escape '\'

) ;

SELECT

'grant '||privilege||' to '||grantee

||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END

FROM dba_sys_privs@dblink_migup

WHERE grantee IN (SELECT role FROM dba_roles WHERE password_required <> 'NO' AND ROLE LIKE 'R\_%' ESCAPE '\');

SELECT 'spool off;' FROM dual;

SPOOL OFF;

commit;

SET termout on echo on

 

--SET termout OFF feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--@$HOME/grant_sys_priv.sql

--commit;

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/grant_sys_tab_priv.sql

SELECT 'spool $HOME/grant_sys_tab_priv.log' FROM dual;

SELECT

'grant '||privilege||' on '

||CASE WHEN substr(table_name,1,2)='V$' THEN 'V_$'||substr(table_name,3,length(table_name-2)) ELSE table_name END

||' to '||grantee||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END

FROM dba_tab_privs@dblink_migup

WHERE grantee IN

( select username from sys.t_user_cfg

union

select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)

and granted_role like 'R\_%' escape '\'

)

AND owner = 'SYS'

and table_name NOT in('EXPDIR','V_$TEMP_HISTOGRAM','V_$STANDBY_APPLY_SNAPSHOT','GV_$TEMP_HISTOGRAM','GV_$STANDBY_APPLY_SNAPSHOT' ,'DBA_ADVISOR_DIRECTIVES','DBA_CACHEABLE_TABLES' , 'DBA_CACHEABLE_OBJECTS_BASE' , 'DBA_CACHEABLE_OBJECTS' ,'DBA_USERS2') -----opms Special

AND privilege NOT IN ('READ','WRITE');

SELECT

'grant '||privilege||' on directory '||table_name||' to '||grantee

||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END

FROM dba_tab_privs@dblink_migup

WHERE grantee IN

( select username from sys.t_user_cfg

union

select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)

and granted_role like 'R\_%' escape '\'

)

AND owner = 'SYS'

AND privilege IN ('READ', 'WRITE')

AND table_name not in ( 'EXPDIR' );

SELECT 'spool off;' FROM dual;

SPOOL OFF;

commit;

SET termout on echo on

--SET termout OFF feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--@$HOME/grant_sys_tab_priv.sql

--commit;

 

 

2.3 check.sql

----check object -----------------

--CHECK OBJECT

1.准备工作 (11G RAC)

step 0:

alter user dbmgr identified by dbmgr1234;

alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.197.1.203)(PORT=1528)))' sid='db1' scope = memory;

step 1:

drop public database link dblink_migup;

create public database link dblink_migup connect to dbmgr identified by dbmgr1234 using ' (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.197.1.103)(PORT = 1528)) (CONNECT_DATA =(sid = db1)))';

step 2:

drop table sys.t_user_cfg;

create table sys.t_user_cfg as SELECT username, default_tablespace

FROM dba_users

WHERE username not in

('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS',

'CTXSYS', 'XDB', 'WKSYS', 'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS',

'LBACSYS', 'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS', 'ADAMS',

'TRACESVR', 'DBSNMP', 'PERFSTAT', 'DBSTATS', 'EXPFULL',

'MDDATA', 'MGMT_VIEW', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS',

'SYSMAN', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS',

'MDSYS', 'FLOWS_FILES', 'SPATIAL_WFS_ADMIN_USR','SQLTXPLAIN','DBMONOPR','SCOTT',

'SPATIAL_CSW_ADMIN_USR', 'APEX_PUBLIC_USER', 'XS$NULL','DMSYS','SQLTXADMIN','ZABBIX','DBMGR','OGG','OVSEE','FOGLIGHT','PAO');

set pagesize 0

select * from sys.t_user_cfg ;

 

2:验证dblink是否通

select * from dual@dblink_migup;

--有结果返回表示通的否则不通需处理" 未完成

3.开始使用连接PL/SQL developer 端连接数据库 RAC 11G

用户名:dbmgr/dbmgr1234

字符串:

10.197.1.205:1528/db

4.PL/SQL devoper 开一个SQL 窗口，执行以下语句，（重点关注 jobs dbmgr.save_ash('db') 在11g是否存在;）

 

--CHECK jobs

select log_user,schema_user,what,broken

from dba_jobs@dblink_migup

where schema_user in (select username from sys.t_user_cfg)

and last_date is not null

minus

select log_user,schema_user,what,broken

from dba_jobs

where schema_user in (select username from sys.t_user_cfg)

and last_date is not null;

--CHECK scheduler_jobs

select job_name from dba_scheduler_jobs@dblink_migup where owner !='SYS'

minus

select job_name from dba_scheduler_jobs where owner !='SYS';

--CHECK OBJECT

select owner,object_name,object_type,status

from dba_objects@dblink_migup

where owner in (select username from sys.t_user_cfg)

and status <> 'INVALID'

minus

select owner,object_name,object_type,status

from dba_objects

where owner in (select username from sys.t_user_cfg)

and status <> 'INVALID';

----check system priv-------------

select grantee,privilege from dba_sys_privs@dblink_migup

where grantee in ( select username from sys.t_user_cfg)

minus

select grantee,privilege from dba_sys_privs

where grantee in ( select username from sys.t_user_cfg) ;

----check role priv-------------

select grantee,granted_role,admin_option from dba_role_privs@dblink_migup

where grantee in ( select username from sys.t_user_cfg)

minus

select grantee,granted_role,admin_option from dba_role_privs

where grantee in ( select username from sys.t_user_cfg) ;

---check role----

select role from dba_roles@dblink_migup

minus

select role from dba_roles;

--CHECK SYS PRIVS

select grantee,privilege,admin_option

from dba_sys_privs@dblink_migup

where grantee in (select username from sys.t_user_cfg)

minus

select grantee,privilege,admin_option

from dba_sys_privs

where grantee in (select username from sys.t_user_cfg) ;

--CHECK ROLE PRIVS

select grantee,granted_role,admin_option

from dba_role_privs@dblink_migup

where grantee in (select username from sys.t_user_cfg)

minus

select grantee,granted_role,admin_option

from dba_role_privs

where grantee in (select username from sys.t_user_cfg) ;

----check user object priv-------------

select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs@dblink_migup

where grantee in ( select username from sys.t_user_cfg)

minus

select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs

where grantee in ( select username from sys.t_user_cfg) ;

-----check quota---------------

select username,max_bytes

from dba_ts_quotas@dblink_migup

where username in (select username from sys.t_user_cfg)

minus

select username,max_bytes

from dba_ts_quotas

where username in (select username from sys.t_user_cfg) ;

--check audit-------------

select user_name,audit_option,success,failure

from dba_stmt_audit_opts@dblink_migup

where user_name in ( select username from sys.t_user_cfg)

minus

select user_name,audit_option,success,failure

from dba_stmt_audit_opts

where user_name in ( select username from sys.t_user_cfg) ;

--check synonym no rows selected

select owner,synonym_name,table_owner,table_name

from dba_synonyms@dblink_migup

where table_owner in ( select username from sys.t_user_cfg)

minus

select owner,synonym_name,table_owner,table_name

from dba_synonyms

where table_owner in ( select username from sys.t_user_cfg) ;

--check db links no rows selected

select owner,db_link,username,host from dba_db_links@dblink_migup

where owner in ( select username from sys.t_user_cfg)

minus

select owner,db_link,username,host from dba_db_links

where owner in ( select username from sys.t_user_cfg) ;

--check db context no rows selected

select namespace,schema,package,type

from dba_context@dblink_migup

where schema in ( select username from sys.t_user_cfg)

minus

select namespace,schema,package,type

from dba_context

where schema in ( select username from sys.t_user_cfg) ;

--CHECK OBJECT COUNT

--select owner,object_type,status,count(*)

-- from dba_objects@dblink_migup

-- where owner in (select username from sys.t_user_cfg)

-- And (owner,object_name) Not In (Select owner,segment_name From dba_lobs@dblink_migup

-- Union All Select owner,index_name From dba_lobs@dblink_migup)

-- group by owner,object_type,status

--minus

--select owner,object_type,status,count(*)

-- from dba_objects

-- where owner in (select username from sys.t_user_cfg)

--- group by owner,object_type,status;

--check objects

--老库比新库，结果应为空

select owner,object_name,object_type

from dba_objects@dblink_migup

where owner in (select username from sys.t_user_cfg)

minus

select owner,object_name,object_type

from dba_objects

where owner in (select username from sys.t_user_cfg) ;

--新库比老库，结果显示出来的对象需要邓玉坤确认是否是新建对象

select * from (

select owner,object_name,object_type

from dba_objects

where owner in (select username from sys.t_user_cfg)

minus

select owner,object_name,object_type

from dba_objects@dblink_migup

where owner in (select username from sys.t_user_cfg) ) t where t.object_name not like 'SYS_IL000%' and t.object_name not like 'AQ$_%';

--check table no rows selected

select owner,table_name,partitioned,iot_type,temporary

from dba_tables@dblink_migup

where owner in ( select username from sys.t_user_cfg)

minus

select owner,table_name,partitioned,iot_type,temporary

from dba_tables

where owner in ( select username from sys.t_user_cfg) ;

--check constraint no rows selected

select owner, constraint_name, constraint_type, table_name, r_owner, status

from dba_constraints@dblink_migup

where owner in (select username from sys.t_user_cfg)

and constraint_name not like 'SYS%' and constraint_name not like 'BIN$%'

minus

select owner, constraint_name, constraint_type, table_name, r_owner, status

from dba_constraints

where owner in (select username from sys.t_user_cfg)

and constraint_name not like 'SYS%' and constraint_name not like 'BIN$%';

------check index---------------------

select index_owner,index_name,table_owner,table_name,column_name,column_position

from dba_ind_columns@dblink_migup

where table_owner in (select username from sys.t_user_cfg)

minus

select index_owner,index_name,table_owner,table_name,column_name,column_position

from dba_ind_columns

where table_owner in (select username from sys.t_user_cfg)

;

--check tab columns no rows selected

select owner,table_name,column_name,data_type,data_length,nullable,column_id from dba_tab_columns@dblink_migup

where owner in (select username from sys.t_user_cfg) and table_name not like 'AQ$%'

and table_name not like 'BIN$%'

minus

select owner,table_name,column_name,data_type,data_length,nullable,column_id from dba_tab_columns

where owner in (select username from sys.t_user_cfg) and table_name not like 'AQ$%'

;

--check comments no rows selected

select owner,table_name,column_name,comments from dba_col_comments@dblink_migup

where owner in (select username from sys.t_user_cfg)

and table_name not like 'BIN$%'

minus

select owner,table_name,column_name,comments from dba_col_comments

where owner in (select username from sys.t_user_cfg)

;

------check profile

select * from dba_profiles@dblink_migup

minus

select * from dba_profiles ;

------check mview

select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews@dblink_migup

where owner in (select username from sys.t_user_cfg)

minus

select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews

where owner in (select username from sys.t_user_cfg)

;

-----check user

select a.username,a.password apwd,b.password bpwd,a.profile apf,b.profile bpf,

a.account_status acs,b.account_status bcs

From

(

select du.username,su.password,du.profile,du.account_status

from dba_users@dblink_migup du,sys.user$ su

where username in (select username from sys.t_user_cfg) and du.username = su.name

) a

,

(

select du.username,su.password,du.profile,du.account_status

from dba_users du,sys.user$ su

where du.username in (select username from sys.t_user_cfg)

and du.username = su.name

) b

Where a.username = b.username And (a.password <> b.password Or a.profile <> b.profile Or a.account_status <> b.account_status);

--------check trigger--

select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status

from dba_triggers@dblink_migup

where owner in (select username from sys.t_user_cfg)

minus

select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status from dba_triggers

where owner in (select username from sys.t_user_cfg)

;

--check table owner trigger no rows selected

select owner,trigger_name,trigger_type,table_owner,table_name,column_name

from dba_triggers@dblink_migup where owner in (select username from sys.t_user_cfg)

minus

select owner,trigger_name,trigger_type,table_owner,table_name,column_name

from dba_triggers where owner in (select username from sys.t_user_cfg);

----------check sequence 结果为空

select ds.sequence_owner,

ds.sequence_name,

ds.min_value,

ds.max_value,

ds.increment_by,

ds.cycle_flag,

ds.order_flag,

ds.cache_size

from dba_sequences@dblink_migup ds

where sequence_owner in (select username from sys.t_user_cfg)

minus

select ds.sequence_owner,

ds.sequence_name,

ds.min_value,

ds.max_value,

ds.increment_by,

ds.cycle_flag,

ds.order_flag,

ds.cache_size

from dba_sequences ds

where sequence_owner in (select username from sys.t_user_cfg);

--比对sequence值，结果应为空，新库SEQUNCE值比老库大

select *

from (select sequence_owner,

sequence_name,

lastnum_10,

lastnum_11,

lastnum_11 - lastnum_10 delta_num

from (select ds.sequence_owner sequence_owner,

ds.sequence_name sequence_name,

ds.last_number lastnum_10,

dt.last_number lastnum_11

from dba_sequences dt, dba_sequences@dblink_migup ds

where dt.sequence_owner in

(select username from sys.t_user_cfg)

and dt.sequence_owner = ds.sequence_owner

and dt.sequence_name = ds.sequence_name))

where delta_num < 0;

5.删除db_link

drdrop public database link dblink_migup;op public database link dblink_migup;

 

 

###################################

3.OGG 安装

 

上传不同平台的goldengate软件 gg软件两个平台都要安装

"goldengate临时装到data卷上

1.cd /db/{ORACLE_SID}/data

mkdir goldendate

2.unzip goldengate软件

" 请注意 goldengate 软件区分os平台和oracle版本 ,另外对于RAC，goldengate软件安装目录需要设置为共享存储

"cd

vi .profile

##追加以下内容

GGS_HOME=/db/{ORACLE_SID}/data/goldengate

export GGS_HOME

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GGS_HOME:$LD_LIBRARY_PATH

export LD_LIBRARY_PATH

PATH=$GGS_HOME:$PATH

export PATH

hp-unix:step:

. ./profile_bcds

" "1、请注意平台不同，profile文件配置也不同

2、对于RAC，每个节点上的profile都需要配置；"

"1.检查:

select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

2.如果是未有, 打开supplementary log:

set timing on

alter system checkpoint;

alter system checkpoint;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;" "1.linux 结果如下：

SUPPLEME

--------

YES

2.hp unix 结果如下：

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

YES

"

"create user ggmgr identified by ggmgr123 default tablespace users temporary tablespace temp;

grant create session,alter session to ggmgr;

grant alter system to ggmgr;

grant resource to ggmgr;

grant connect to ggmgr;

grant select any dictionary to ggmgr;

grant flashback any table to ggmgr;

grant select any table to ggmgr;

grant select any table to ggmgr;

grant insert any table to ggmgr;

grant update any table to ggmgr;

grant delete any table to ggmgr;

grant select on dba_clusters to ggmgr;

grant execute on dbms_flashback to ggmgr;

grant create table to ggmgr;

grant create sequence to ggmgr;

grant alter any table to ggmgr;

grant dba to ggmgr;

grant lock any table to ggmgr;" 1.linux/hp-unix done

"cd $GGS_HOME

mkdir direnv"

./ggsci

"create subdirs

exit"

"cd $GGS_HOME

./ggsci

GGSCI> EDIT PARAMS ./GLOBALS

--输入如下内容

CHECKPOINTTABLE ggmgr.gg_checkpoint_tab

syslog none" "1.linux/hp-unix done

当使用GG版本11.x时，需要在GG manager参数中配置syslog none，使gg日志不写入操作系统的syslog。"

"GGSCI> DBLOGIN USERID ggmgr, PASSWORD ggmgr123

GGSCI> ADD CHECKPOINTTABLE ggmgr.gg_checkpoint_tab

GGSCI>exit" 1.linux/hp-unix done

"cd $GGS_HOME/direnv

1.vi db.oby

SETENV (ORACLE_SID = ""bcds"")

2.vi user.oby

userid ggmgr, password ggmgr123

" "1.linux/hp-unix done

ORACLE_SID = ""bcds"" 注意大小写要和环境变量保持一致"

"cd $GGS_HOME/dirprm

vi mgr.prm

port 7809

dynamicportlist 7809-7819

autostart er *

autorestart er *, retries 24, waitminutes 3, resetminutes 1440

lagreportminutes 10

laginfoseconds 30

LAGCRITICALMINUTES 45

purgeoldextracts ./dirdat/*, usecheckpoints, minkeephours 168

" "

1.linux/hp-unix done

删除PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 "

"cd $GGS_HOME

./ggsci

start mgr"

info mgr

GGSCI>dblogin userid ggmgr, password ggmgr12

 

 

2.迁移当天设置回滚环境

 创建2条链路，链路一(step 1)是非CLOB 表，   链路2 （step 2）是clob 表

准备参数文件 准备e进程、d进程、r进程的参数文件

为ggmgr用户临时授权 "grant insert any table to ggmgr;

grant delete any table to ggmgr;

grant update any table to ggmgr;

"

设置参数 alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;

创建gg_send表 "CREATE TABLE ggmgr.GG_SEND(

TESTER VARCHAR2(30) NOT NULL,

SOURCE_DATABASE VARCHAR2(10) NOT NULL,

TARGET_DATABASE VARCHAR2(10) NOT NULL,

TEST_TIME DATE DEFAULT SYSDATE,

Constraint PK_GG_SEND primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

COMMENT ON COLUMN ggmgr.GG_SEND.TESTER IS '测试人员UM账号';

COMMENT ON COLUMN ggmgr.GG_SEND.SOURCE_DATABASE IS '源数据库SID';

COMMENT ON COLUMN ggmgr.GG_SEND.TARGET_DATABASE IS '目标数据库SID';

COMMENT ON COLUMN ggmgr.GG_SEND.TEST_TIME IS '测试时间';

COMMENT ON TABLE ggmgr.GG_SEND is 'GoldenGate接收端连通性测试表';

 

2. step 2 table 2:

CREATE TABLE ggmgr.GG_SEND_2(

TESTER VARCHAR2(30) NOT NULL,

SOURCE_DATABASE VARCHAR2(10) NOT NULL,

TARGET_DATABASE VARCHAR2(10) NOT NULL,

TEST_TIME DATE DEFAULT SYSDATE,

Constraint PK_GG_SEND_2 primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

COMMENT ON COLUMN ggmgr.GG_SEND_2.TESTER IS '测试人员UM账号';

COMMENT ON COLUMN ggmgr.GG_SEND_2.SOURCE_DATABASE IS '源数据库SID';

COMMENT ON COLUMN ggmgr.GG_SEND_2.TARGET_DATABASE IS '目标数据库SID';

COMMENT ON COLUMN ggmgr.GG_SEND_2.TEST_TIME IS '测试时间';

COMMENT ON TABLE ggmgr.GG_SEND_2 is 'GoldenGate接收端连通性测试表';

"

添加trandata "cd $GGS_HOME

./ggsci

dblogin userid ggmgr password ggmgr123

delete trandata BBSP.*

delete trandata BBSPODS.*

delete trandata BCDPETL.*

add trandata BBSP.*

add trandata BBSPODS.*

add trandata BCDPETL.*

"

--WARNING OGG-00869  All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.  这个报错可以忽略

 

 

对超过32列无主键的表添加trandata "

执行/tmp/dba/add_trandata_long32.sql

--报错WARNING OGG-01387需要执行"

 

1.部署pkg（排除了clob表）

2.执行查询

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool /tmp/dba/add_trandata_long32.sql

select trim(sql_add_supp) from TABLE(

select cast(gg_supplemental_32.sql_supplemental_32('AFA')

as gg_supplemental_32_table_type )

from dual

);

select trim(sql_add_supp) from TABLE(

select cast(gg_supplemental_32.sql_supplemental_32('AUTEK')

as gg_supplemental_32_table_type )

from dual

);

 

检查trandata是否添加成功 "执行下面sql，返回空为成功。( --如果不为 空 ，add trandata BBSPODS.*  ，会报错error no data found retrieving导致漏掉一些表，所以需要手工add trandata BBSPODS.table_name) 

select 'add trandata '||owner||'.'|| table_name from dba_tables where owner in ('BBSP',' BBSPODS','BCDPETL') and table_name not in (select distinct table_name from dba_log_group_columns where owner in ('BBSP',' BBSPODS','BCDPETL'));

--排除掉一些临时备份表或者CLOB表或者名称为小写表，剩余的表就需要手工add   trandata BBSPODS.table_name

--clob 表会自动创建唯一索引的

--add trandata BBSPODS.*  会漏掉一些表，所以需要手工add trandata BBSPODS.table_name

--同时在GGSCI 重新运行返回的结果SQL，

 

"

上传参数文件 "cd $GGS_HOME/dirprm

cd $GGS_HOME/dirprm

step 1:

将e_rollbk.prm,d_rollbk.prm上传到此目录下

step 2:

将e_rollbk_2.prm,d_rollbk_2.prm上传到此目录下

chown -R opDB:dba /crdb/DB/data/goldengate/dirprm

 

chown -R opbcds:dba /ogg/bcds/dirprm

"

创建GG进程 "cd $GGS_HOME  (注意  OGG 的进程名总数不能超过8个 )

./ggsci

cd $GGS_HOME

./ggsci

dblogin userid ggmgr password ggmgr123

step 1:

add extract e_rollbk, tranlog, begin now

add exttrail ./dirdat/e1, extract e_rollbk, megabytes 50

add extract d_rollbk, exttrailsource ./dirdat/e1, params dirprm/d_rollbk.prm

add rmttrail ./dirdat/dr, extract d_rollbk, megabytes 50

step 2:

add extract e_roll_2, tranlog, begin now

add exttrail ./dirdat/e2, extract e_roll_2, megabytes 50

add extract d_roll_2, exttrailsource ./dirdat/e2, params dirprm/d_roll_2.prm

add rmttrail ./dirdat/d2, extract d_roll_2, megabytes 50

 

"

修改回滚链路r进程参数文件 "cd $GGS_HOME/dirprm

cd $GGS_HOME/dirprm

step 1:

上传r_rollbk.prm

step 2:

上传r_rollbk_2.prm

chown -R opUSER:dba /crdb/DB/oradata/goldengate/dirprm

chown -R opbcds:dba /oggfs/ogg/dirprm "

创建GG进程 "cd $GGS_HOME

./ggsci

dblogin userid ggmgr password ggmgr123

step 1:

add replicat r_rollbk, exttrail ./dirdat/dr, params dirprm/r_rollbk.prm , CHECKPOINTTABLE ggmgr.gg_checkpoint_tab

step 2:

add replicat r_roll_2, exttrail ./dirdat/d2, params dirprm/r_roll_2.prm , CHECKPOINTTABLE ggmgr.gg_checkpoint_tab

 

"

创建目标端连通性测试表

step 1:

CREATE TABLE ggmgr.GG_RECEIVE_R_ROLLBK(

TESTER VARCHAR2(30) NOT NULL,

SOURCE_DATABASE VARCHAR2(10) NOT NULL,

TARGET_DATABASE VARCHAR2(10) NOT NULL,

TEST_TIME DATE DEFAULT SYSDATE,

Constraint PK_GG_RECEIVE_R_ROLLBK primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.TESTER IS '测试人员UM账号';

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.SOURCE_DATABASE IS '源数据库SID';

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.TARGET_DATABASE IS '目标数据库SID';

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.TEST_TIME IS '测试时间';

COMMENT ON TABLE ggmgr.GG_RECEIVE_R_ROLLBK is 'GoldenGate接收端连通性测试表';

step 2:

CREATE TABLE ggmgr.GG_RECEIVE_R_ROLLBK_2(

TESTER VARCHAR2(30) NOT NULL,

SOURCE_DATABASE VARCHAR2(10) NOT NULL,

TARGET_DATABASE VARCHAR2(10) NOT NULL,

TEST_TIME DATE DEFAULT SYSDATE,

Constraint PK_GG_RECEIVE_R_ROLLBK_2 primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.TESTER IS '测试人员UM账号';

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.SOURCE_DATABASE IS '源数据库SID';

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.TARGET_DATABASE IS '目标数据库SID';

COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.TEST_TIME IS '测试时间';

COMMENT ON TABLE ggmgr.GG_RECEIVE_R_ROLLBK_2 is 'GoldenGate接收端连通性测试表';

 

';

"

禁用级联删除和触发器 "SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'

FROM dba_constraints

WHERE owner in ('BBSP',' BBSPODS','BCDPETL')

AND constraint_type = 'R'

ORDER BY constraint_type desc, owner, table_name, constraint_name;

select 'alter trigger '||owner||'.'||trigger_name||' disable ;'

from all_triggers

where owner in ('BBSP',' BBSPODS','BCDPETL')

order by owner,trigger_name;

复核：

select owner,table_name,constraint_name,status from dba_constraints where owner in ('BBSP',' BBSPODS','BCDPETL') and constraint_type = 'R' and status <> 'DISABLED'; --无结果产出

select owner,trigger_name,table_owner,table_name,status from dba_triggers where owner in ('BBSP',' BBSPODS','BCDPETL') and status <> 'DISABLED'; --无结果产出

"

启动extract和dump进程 

step 1:

start e_rollbk

start d_rollbk

step 2:

start e_roll_2

start d_roll_2

info all

<进程都是RUNNING状态>

 

启动同步到unix的回滚R进程

cd $GGS_HOME

./ggsci

step 1:

start r_rollbk

step 2:

start r_roll_2

 

插入测试信息 "sqlplus '/as sysdba'

step 1

sqlplus '/as sysdba'

insert into ggmgr.GG_SEND values('测试','source_sid','target_sid',SYSDATE);

commit;

step 2:

insert into ggmgr.GG_SEND_2 values('测试','source_sid','target_sid',SYSDATE);

commit;

检测测试信息是否同步过来 "sqlplus '/as sysdba'

step 1:

sqlplus '/as sysdba'

select * from ggmgr.GG_RECEIVE_R_ROLLBK ;

step 2:

sqlplus '/as sysdba'

select * from ggmgr.GG_RECEIVE_R_ROLLBK_2 ;

for clob table:

select count(*) from f_cus_reprintinfo;

for no-clob table:

select count(*) from t_ibps_paymentbook

"

 

3.3 OGG PKG

 

查看32列以上 没有主键 的非clob表   (排除了clob表，如果有 clob 表，那么可能需要另外单独为clob表 单独创建一个链路,解释2  CLOB 表会自动 创建一个唯一性的索引。可以正常使用ogg 同步，所以下面pkg 不检测clob 表) ：

1.部署pkg

2.执行查询

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

select trim(sql_add_supp) from TABLE(

select cast(gg_supplemental_32.sql_supplemental_32('BBSP')

as gg_supplemental_32_table_type )

from dual

);

 

 

Pkg info：

create or replace type gg_supplemental_32_row_type as object

(

owner varchar2(32),

table_name varchar2(32),

seq# number,

sql_add_supp varchar2(2048)

);

/

create or replace type gg_supplemental_32_table_type as table of gg_supplemental_32_row_type;

/

create or replace package gg_supplemental_32 AUTHID CURRENT_USER is

function sql_supplemental_32(i_owner varchar2)

return gg_supplemental_32_table_type;

end gg_supplemental_32;

/

create or replace package body gg_supplemental_32 as

function sql_supplemental_32(i_owner varchar2)

return gg_supplemental_32_table_type as

z_data gg_supplemental_32_table_type := gg_supplemental_32_table_type();

n number;

cursor c is

select dt.owner || '.' || dt.table_name obj,

dt.column_name,

dt.table_name,

dt.owner,

Row_Number() over(partition by dt.owner || '.' || dt.table_name order by dt.column_name) rn,

do.object_id

from dba_tab_columns dt, dba_objects do

where dt.owner = i_owner

and dt.data_type not in

('CLOB', 'BLOB', 'NCLOB', 'LONG', 'LONG RAW', 'ANYDATA',

'ALERT_TYPE', 'XMLTYPE', 'WM_PERIOD', 'SQL_PLAN_TABLE_TYPE',

'BFILE')

and dt.data_type not like '%$%'

and dt.owner = do.owner

and dt.table_name = do.object_name

and do.OBJECT_TYPE='TABLE'

and dt.table_name in

(SELECT distinct (table_name) obj

FROM dba_tab_columns

WHERE owner = i_owner

AND column_id > 32

AND table_name in

(SELECT table_name

FROM dba_tables

WHERE owner = i_owner

MINUS (SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = i_owner

AND cdef.type# = 2                                            

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM dba_indexes idx

WHERE idx.owner = i_owner

AND idx.uniqueness = 'UNIQUE')));

obj_prev varchar2(200);

obj_flag number;

sql_add_supp varchar2(4000);

sql_post varchar2(2000);

n_g number;

pre_n_g number;

z_owner varchar2(32);

z_table_name varchar2(32);

begin

sql_post := ') always;';

obj_flag := 0;

for x in c loop

n_g := trunc((x.rn - 1) / 32) + 1;

n := mod(x.rn, 32);

z_owner := x.owner;

z_table_name := x.table_name;

if obj_prev is null then

sql_add_supp := 'ALTER TABLE ' || x.obj ||

' ADD SUPPLEMENTAL LOG GROUP GGS_' ||

substr(x.table_name, 1, 16) || '_' || x.object_id || '_' || n_g || '(' ||

x.column_name;

obj_prev := x.obj;

pre_n_g := n_g;

elsif (obj_prev = x.obj) then

if (n_g <> pre_n_g) then

-- print out sql statement

sql_add_supp := sql_add_supp || sql_post;

z_data.extend;

z_data(z_data.count) := gg_supplemental_32_row_type(z_owner,

z_table_name,

n_g,

sql_add_supp);

-- reset

pre_n_g := n_g;

sql_add_supp := 'ALTER TABLE ' || x.obj ||

' ADD SUPPLEMENTAL LOG GROUP GGS_' ||

substr(x.table_name, 1, 16) || '_' || x.object_id || '_' || n_g || '(' ||

x.column_name;

else

sql_add_supp := sql_add_supp || ' ,' || x.column_name;

end if;

else

-- print out sql statement

sql_add_supp := sql_add_supp || sql_post;

z_data.extend;

z_data(z_data.count) := gg_supplemental_32_row_type(z_owner,

z_table_name,

n_g,

sql_add_supp);

-- reset

pre_n_g := n_g;

obj_prev := x.obj;

sql_add_supp := 'ALTER TABLE ' || x.obj ||

' ADD SUPPLEMENTAL LOG GROUP GGS_' ||

substr(x.table_name, 1, 16) || '_' || x.object_id || '_' || n_g || '(' ||

x.column_name;

end if;

end loop;

-- print last sql if exist --

sql_add_supp := sql_add_supp || sql_post;

z_data.extend;

z_data(z_data.count) := gg_supplemental_32_row_type(z_owner,

z_table_name,

n_g,

sql_add_supp);

return z_data;

end sql_supplemental_32;

end;

/

 

 

###解释说明：

 

解释1 非clob 表带有唯一主键的表。

SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = i_owner

AND cdef.type# = 2                                            

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

 

###以下是查  clob 表和非clob 表都带有唯一主键的表

SELECT idx.table_name

FROM dba_indexes idx

WHERE idx.owner = i_owner

AND idx.uniqueness = 'UNIQUE'

解释2  CLOB 表会自动 创建一个唯一性的索引。所以OGG

You are not allowed to operate directly on system-defined LOB index.

Whenever you create a LOB column the database automatically creates two segments (LOB Data Segment, LOB index Segment).  You cannot drop/alter any of these segments separately, the only way to do so is to drop the LOB column itself and consequently the two associated segments will be dropped.

TEST CASE

=========

SQL> create user user1 identified by user1;

User created.

SQL> grant dba to user1;

Grant succeeded.

SQL> connect user1/user1

Connected.

SQL> create table LOB_TAB(id number, name CLOB);

Table created.

SQL> insert into LOB_TAB values (1,'Ahmed');

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name, index_type from dba_indexes where table_name like 

'LOB_TAB' and owner like 'USER1';

INDEX_NAME INDEX_TYPE

------------------------------ ---------------------------

SYS_IL0000051347C00002$$ LOB

SQL> drop index SYS_IL0000051347C00002$$;

drop index SYS_IL0000051347C00002$$

*

ERROR at line 1:

ORA-22864: cannot ALTER or DROP LOB indexes

SQL> alter table LOB_TAB drop column name;

Table altered.

SQL>

You can use the DBA_LOBS view to query for all the LOB objects related to a 

certain user and then drop their columns one by one:

SQL> col table_name format a20

SQL> col column_name format a20

SQL> select table_name, column_name from dba_lobs where owner like 'USER1';

TABLE_NAME COLUMN_NAME

-------------------- --------------------

LOB_TAB NAME

Note:  If you want to move them, see Note 130814.1 - How to move LOB Data to Another Tablespace

 

 

###解释 3

 

target  :

--db.oby

SETENV (ORACLE_SID = "USER")

 

--mgr.prm

port 7809

dynamicportlist 7809-7819

autostart er *

autorestart er *, retries 24, waitminutes 3, resetminutes 1440

lagreportminutes 10

laginfoseconds 30

LAGCRITICALMINUTES 45

purgeoldextracts ./dirdat/*, usecheckpoints, minkeephours 168

 

---r_roll_2.prm

REPLICAT r_roll_2

obey ./direnv/db.oby

--If the NLS_CHARACTERSET of the Target DB is not the same as the Source DB, db_esb_lang.oby must be set in this file

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

obey ./direnv/user.oby

ASSUMETARGETDEFS

-- control how often Extract and Replicat make their routine checkpoints

CHECKPOINTSECS 30

-- control the number of SQL operations that are contained in a Replicat transaction when

GROUPTRANSOPS 20000

-- Sets a global response to all errors except those for which explicit,

-- Roll back the transaction and terminate processing abnormally

REPERROR DEFAULT, ABEND

-- generate a discard file to which GoldenGate can log records that it cannot process,

-- Adds new content to existing content if the file already exists

DISCARDFILE ./dirrpt/r_rollbk_2.dsc, append, MEGABYTES 2000

-- set a schedule for aging discard files

DISCARDROLLOVER AT 05:30 ON friday

-- report a count of transaction records that Extract or Replicat processed since startup

REPORTCOUNT EVERY 10000 RECORDS, RATE

-- alter the rules for processing wildcarded table specifications in a TABLE, SEQUENCE, or MAP statement

WILDCARDRESOLVE DYNAMIC

-- control whether or not duplicate MAP statements for the same source and target objects are accepted in a parameter file

ALLOWDUPTARGETMAP

map ggmgr.gg_send_2 , target ggmgr.gg_receive_r_rollbk_2, &

colmap (

tester = tester ,

source_database = source_database ,

target_database = target_database ,

test_time = test_time

);

MAP USER.*, TARGET USER.*;

MAP AUTEK.*, TARGET AUTEK.*;

 

--r_rollbk.prm

REPLICAT r_rollbk

obey ./direnv/db.oby

--If the NLS_CHARACTERSET of the Target DB is not the same as the Source DB, db_esb_lang.oby must be set in this file

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

obey ./direnv/user.oby

ASSUMETARGETDEFS

-- control how often Extract and Replicat make their routine checkpoints

CHECKPOINTSECS 30

-- control the number of SQL operations that are contained in a Replicat transaction when

GROUPTRANSOPS 20000

-- Sets a global response to all errors except those for which explicit,

-- Roll back the transaction and terminate processing abnormally

REPERROR DEFAULT, ABEND

-- generate a discard file to which GoldenGate can log records that it cannot process,

-- Adds new content to existing content if the file already exists

DISCARDFILE ./dirrpt/r_rollbk.dsc, append, MEGABYTES 2000

-- set a schedule for aging discard files

DISCARDROLLOVER AT 05:30 ON friday

-- report a count of transaction records that Extract or Replicat processed since startup

REPORTCOUNT EVERY 10000 RECORDS, RATE

-- alter the rules for processing wildcarded table specifications in a TABLE, SEQUENCE, or MAP statement

WILDCARDRESOLVE DYNAMIC

-- control whether or not duplicate MAP statements for the same source and target objects are accepted in a parameter file

ALLOWDUPTARGETMAP

map ggmgr.gg_send , target ggmgr.gg_receive_r_rollbk, &

colmap (

tester = tester ,

source_database = source_database ,

target_database = target_database ,

test_time = test_time

);

MAP USER.*, TARGET USER.*;

MAP AUTEK.*, TARGET AUTEK.*;

 

---user.oby

userid ggmgr, password ggmgr123

 

 

source :

---d_roll_2.prm

--------------------------------------------------------------------------------------

-- This prm file is used to set the todatapump parameters

-- You need to replace all the '<>'

-- Please input the parameter into GoldenGate management system before you replace it

-- Please refer 'gg_wux_ref_v104.pdf' for the parameters's detail means

--------------------------------------------------------------------------------------

EXTRACT d_roll_2

obey ./direnv/db.oby

obey ./direnv/user.oby

RMTHOST 10.198.67.14, MGRPORT 7809

-- Specifies a trail to which extracted data is written on a remote system

RMTTRAIL ./dirdat/d2

--list the tables

TABLE ggmgr.gg_send_2;

table USER.E_CUS_REPRINTINFO;

 

--d_rollbk.prm

--------------------------------------------------------------------------------------

-- This prm file is used to set the todatapump parameters

-- You need to replace all the '<>'

-- Please input the parameter into GoldenGate management system before you replace it

-- Please refer 'gg_wux_ref_v104.pdf' for the parameters's detail means

--------------------------------------------------------------------------------------

EXTRACT d_rollbk

obey ./direnv/db.oby

obey ./direnv/user.oby

RMTHOST 10.198.67.14, MGRPORT 7809

-- Specifies a trail to which extracted data is written on a remote system

RMTTRAIL ./dirdat/dr

--list the tables

TABLE ggmgr.gg_send;

TABLE USER.*;

TABLE AUTEK.*;

 

--db.oby

SETENV (ORACLE_SID = "USER")

--e_roll_2.prm

--------------------------------------------------------------------------------------

-- This prm file is used to set the extract parameters

-- You need to replace all the '<>'

-- Please input the parameter into GoldenGate management system before you replace it

-- Please refer 'gg_wux_ref_v104.pdf' for the parameters's means

--------------------------------------------------------------------------------------

EXTRACT e_roll_2

obey ./direnv/db.oby

obey ./direnv/user.oby

--These two parameter must be added in OGG 11.1.1.1 (ȸö to elusion a bug. Do not add them in OGG 11.1.1 (}¸ö,for they are

--not recognised before the version of OGG 11.1.1.X.

TRANLOGOPTIONS _NOREADAHEAD ANY

TRANLOGOPTIONS LOGRETENTION DISABLED

TRANLOGOPTIONS DBLOGREADER

--GoldenGate manager will monitor the long transections which is running upper than 4 hours, interval 30min

WARNLONGTRANS 4h, CHECKINTERVAL 30m

-- Specifies a trail to which extracted data is written on the local system

EXTTRAIL ./dirdat/e2

--list the tables

TABLE ggmgr.gg_send_2;

table USER.E_CUS_REPRINTINFO;

 

--d_rollbk.prm

--------------------------------------------------------------------------------------

-- This prm file is used to set the todatapump parameters

-- You need to replace all the '<>'

-- Please input the parameter into GoldenGate management system before you replace it

-- Please refer 'gg_wux_ref_v104.pdf' for the parameters's detail means

--------------------------------------------------------------------------------------

EXTRACT d_rollbk

obey ./direnv/db.oby

obey ./direnv/user.oby

RMTHOST 10.198.67.14, MGRPORT 7809

-- Specifies a trail to which extracted data is written on a remote system

RMTTRAIL ./dirdat/dr

--list the tables

TABLE ggmgr.gg_send;

TABLE USER.*;

TABLE AUTEK.*;

 

 

---e_rollbk.prm

--------------------------------------------------------------------------------------

-- This prm file is used to set the extract parameters

-- You need to replace all the '<>'

-- Please input the parameter into GoldenGate management system before you replace it

-- Please refer 'gg_wux_ref_v104.pdf' for the parameters's means

--------------------------------------------------------------------------------------

EXTRACT e_rollbk

obey ./direnv/db.oby

obey ./direnv/user.oby

--These two parameter must be added in OGG 11.1.1.1 (ȸö to elusion a bug. Do not add them in OGG 11.1.1 (}¸ö,for they are

--not recognised before the version of OGG 11.1.1.X.

TRANLOGOPTIONS _NOREADAHEAD ANY

TRANLOGOPTIONS LOGRETENTION DISABLED

TRANLOGOPTIONS DBLOGREADER

--GoldenGate manager will monitor the long transections which is running upper than 4 hours, interval 30min

WARNLONGTRANS 4h, CHECKINTERVAL 30m

-- Specifies a trail to which extracted data is written on the local system

EXTTRAIL ./dirdat/e1

--list the tables

TABLE ggmgr.gg_send;

TABLE USER.* ;

TABLE AUTEK.* ;

tableexclude USER.E_CUS_REPRINTINFO;

 

--mgr.prm

port 7809

dynamicportlist 7809-7819

autostart er *

autorestart er *, retries 24, waitminutes 3, resetminutes 1440

lagreportminutes 10

laginfoseconds 30

LAGCRITICALMINUTES 45

purgeoldextracts ./dirdat/*, usecheckpoints, minkeephours 168

 

--user.oby

userid ggmgr, password ggmgr123