OGG安装测试

一 检查生产库




1、创建附加日志：
SQL> select supplemental_log_data_min from v$database;


SUPPLEME
--------
YES                       -------------若不是YES，需要打开


打开语句：
alter database add supplemental log data;
ALTER SYSTEM SWITCH LOGFILE;


2、打开force log
SQL> select force_logging from v$database;


FOR
---
YES


SQL> ALTER DATABASE FORCE LOGGING;


3、查询压缩表
select owner,table_name from dba_tables where owner = 'HOSTDB' and compression='ENABLED';
select table_owner,table_name from dba_tab_partitions where table_owner = 'HOSTDB' and  compression='ENABLED';


4、
查询不支持的列的表格
select owner,table_name,column_name,data_type from dba_tab_columns where owner = 'HOSTDB' and data_type in ('ANYDATA','ANYDATASET','ANYTYPE','BFILE','BINARY_INTEGER','MLSLABEL','PLS_INTEGER','TIMEZONE_ABBR','TIMEZONE_REGION','URITYPE,UROWID');


5、
select owner,table_name from dba_tables where owner='HOSTDB' and logging='NO' and temporary='N';
alter table INTERP.tt1291 logging;


6、
--创建管理用户
SQL> create tablespace odc_tps datafile '/oradata/c5cdb/odc01.dbf' size 100M autoextend on;


SQL> create user odc identified by odc default tablespace odc_tps;


--授权，DBA权限在安装成功后可以收回
GRANT CONNECT TO odc;
GRANT ALTER ANY TABLE TO odc;
GRANT ALTER SESSION TO odc;
GRANT CREATE SESSION TO odc;
GRANT FLASHBACK ANY TABLE TO odc;
GRANT SELECT ANY DICTIONARY TO odc;
GRANT SELECT ANY TABLE TO odc;
GRANT RESOURCE TO odc;
GRANT DBA TO odc;
--安装成功后可以收回dba权限，但是要授予UNLIMITED TABLESPACE权限。
GRANT UNLIMITED TABLESPACE TO odc;
或者alter user odc quota unlimited on users;




--在ddl_setup时报错，通过授予以下权限解决。
grant create any table to odc;
grant create any view to odc;
grant create any procedure to odc;
grant create any sequence to odc;
grant create any index to odc;
grant create any trigger to odc;
grant create any view to odc;




二 安装软件（生产和备份端安装步骤一样，只有软件及环境变量可能不一样）
1、创建安装目录或文件系统，例如/odc
2、配置library环境变量
HP-UX : SHLIB_PATH
AIX : LIBPATH
LINUX :　LD_LIBRARY_PATH
例如：
export LD_LIBRARY_PATH=/opt/odc:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/opt/odc:$ORACLE_HOME/lib:$LD_LIBRARY_PATH


3、安装软件
$ cd /odc
$ tar xf ggs_AIX_ppc_ora11g_64bit_v11_1_1_0_11_001.tar


./ggsci
ggsci>create subdirs


ggsci>edit param mgr
输入：
port 7809
PURGEOLDEXTRACTS /bmsdata/odc/dirdat/yt*, USECHECKPOINTS, MINKEEPHOURS 24




ggsci>edit param ./GLOBALS
输入：
GGSCHEMA odc
CHECKPOINTTABLE odc.ggs_checkpoint ---保存退出 


start mgr


三 生产库安装DDL相关组件
1. 确定DDL SCHEMA
需要配置在GLOBALS中：GGSCHEMA odc 
2. 
GRANT EXECUTE ON UTL_FILE TO odc;
3.
为odc_DDL_HIST、 odc_MARKER等表准备表空间，如果空间不足，会导致数据库无法进行DDL，从而影响业务。
alter user odc default tablespace QUERY_SERVER;


4. 退出所有ORACLE会话，不允许新会话产生
5.sqlplus "/as sysdba"            ----必须sysdba连入


6.@marker_setup


7. @ddl_setup
  该脚本会提示你所有打开的会话，要求关闭。
  提示输入DDL SCHEMA；odc
  选择安装类型：INITIALSETUP


8. @role_setup 
    提示你执行一条GRANT语句，执行即可！
9. @ddl_enable.sql


提高DDL性能脚本： -----需要dbms_shared_pool包
SQL> @ddl_pin odc




四、 为表格添加supplement log


dblogin userid odc@ytyy password odc   
add trandata interp.*




五、 创建挖掘进程
add extract yt_ext tranlog begin now
add exttrail ./dirdat/yt extract yt_ext


edit param yt_ext


参数：
extract yt_ext


setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid odc@ytyy,password odc
exttrail ./dirdat/yt 
tranlogoptions altarchivelogdest /bak9i/arch
FETCHOPTIONS FETCHPKUPDATECOLS
ddl include objname interp.* exclude objtype 'TRIGGER'
ddloptions addtrandata
table interp.*;
tableexclude interp.SYS_EXPORT_SCHEMA* ;
tableexclude interp.dbms_tabcomp_temp* ;


start yt_ext


--观察，能够挖掘再继续下面的操作




六、增长pump进程
add extract yt_dmp EXTTRAILSOURCE ./dirdat/yt
ADD RMTTRAIL ./dirdat/yt, EXTRACT yt_dmp


edit param yt_dmp


参数：
extract yt_dmp
rmthost 10.18.120.33, mgrport 7809
rmttrail ./dirdat/yt
passthru
table interp.* ;


start yt_dmp


./extract paramfile ./dirprm/yt_dmp.prm reportfile ./dirrpt/yt_dmp.rpt 2>&1 &




七、备份库创建用户


SQL> create tablespace odc_tps datafile '/data/oradata/ytyyogg/odc01.dbf' size 100M autoextend on;


Tablespace created.


SQL> create user odc identified by odc default tablespace odc_tps;


GRANT CONNECT TO odc;
GRANT ALTER ANY TABLE TO odc;
GRANT ALTER SESSION TO odc;
GRANT CREATE SESSION TO odc;
GRANT FLASHBACK ANY TABLE TO odc;
GRANT SELECT ANY DICTIONARY TO odc;
GRANT SELECT ANY TABLE TO odc;
GRANT RESOURCE TO odc;
GRANT DBA TO odc;


八、备份库创建checkpoint table


cd /opt/odc
$ sqlplus odc/odc@ytyyogg
SQL> @chkpt_ora_create.sql
DROP TABLE ggs_checkpoint
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Table created.




九、初始化同步
1、
--生产端查询表格所在表空间
select distinct tablespace_name from dba_tables where owner  in ('INTERP');
TABLESPACE_NAME
------------------------------
USERS
INTERP
select distinct tablespace_name from dba_tab_partitions where table_owner  in ('INTERP');


--备份端查询是否存在以上表空间，若没有则创建，如果不想创建，在后面的impdp中需要配置remap_tablespace参数。


--生产端索引所在表空间
select distinct tablespace_name from dba_indexes where owner in ('INTERP');
TABLESPACE_NAME
------------------------------
USERS
INTERP


select distinct tablespace_name from dba_ind_partitions where index_owner in ('INTERP');


--生产端用户默认临时表空间：
select distinct temporary_tablespace from dba_users
TEMPORARY_TABLESPACE
------------------------------
TEMP




2、
检查undo保留时间及表空间大小
SQL> show parameter undo
SQL> select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name; 
SQL> select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;


3、备份库创建一个连接到生产端得DBLINK
create database link oralink connect to odc identified by odc using 'YTYY';


4、生产端查询SCN
select to_char(current_scn) from v$database;
9874606502


export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
expdp odc/odc@ytyy directory




5、目标端做
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK


impdp odc/odc network_link=oralink schemas=INTERP flashback_scn=9874606502 logfile=imp_tmp1.log


--导入前检查目标端表空间的大小
select segment_type,count(*),sum(bytes/1024/1024) from dba_segments  where owner='BSRUN' group by segment_type;






6、
备份库禁用触发器：
select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where owner='INTERP' and status='ENABLED';


alter trigger INTERP.BMS_MV_ALT_AUTOFILL disable;
alter trigger INTERP.BMS_PRESENTOUT_QTY_AUTOFILL disable;
alter trigger INTERP.BMS_STI_DOC_AUTOFILL disable;
alter trigger INTERP.BMS_STI_DTL_AUTOFILL disable;
alter trigger INTERP.BMS_STO_DOC_AUTOFILL disable;
alter trigger INTERP.BMS_STO_DTL_AUTOFILL disable;
alter trigger INTERP.BMS_STO_DTL_CHECK disable;
alter trigger INTERP.BMS_ST_IO_DOC_TRIGGER disable;
alter trigger INTERP.BMS_ST_IO_DTL_TMP_TRIGGER disable;
alter trigger INTERP.BMS_ST_IO_DTL_TRIGGER disable;
alter trigger INTERP.BMS_ST_QTY_AUTOFILL disable;
alter trigger INTERP.BMS_TR_DTL_TRIGGER disable;
alter trigger INTERP.GSP_CHK_TO_SHPTYP_TRI disable;
alter trigger INTERP.GSP_CONFILE_TO_QUFILE disable;
alter trigger INTERP.GSP_MDCHK_TO_SHPKND_TRI disable;
alter trigger INTERP.PUB_GOODS_UNITCHANGE_TRI disable;
alter trigger INTERP.ZX_BMS_DEMATIC_CR_TRG disable;
alter trigger INTERP.ZX_BMS_IMSEVENT_REDUND_TR disable;
alter trigger INTERP.ZX_BMS_SA_RECENTBUSI_TRG disable;
alter trigger INTERP.ZX_BMS_SFDAIOUP_TRA_TRG disable;
alter trigger INTERP.ZX_BMS_SFDATOCOMPANY_CR_TR disable;
alter trigger INTERP.ZX_BMS_STREALQTY_IOTMP_TRG disable;
alter trigger INTERP.ZX_BMS_STREALQTY_RGPLAN_TRG disable;
alter trigger INTERP.ZX_BMS_SUCON_RECENTBUSI_TRG disable;
alter trigger INTERP.ZX_BMS_WOCOLLECT_STATUS_TRG disable;
alter trigger INTERP.ZX_LOG_COMPANYCUSTOM_TRG disable;
alter trigger INTERP.ZX_LOG_COMPANYSUPPLY_TRG disable;
alter trigger INTERP.ZX_LOG_EXAMCUSTOM_TRG disable;
alter trigger INTERP.ZX_LOG_GOODSLOT_TRG disable;
alter trigger INTERP.ZX_LOG_GOODSPACK_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPEMP_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPEXT_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPOP_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPROLE_TRG disable;
alter trigger INTERP.ZX_MVPOSIDPRIT_TRG disable;
alter trigger INTERP.ZX_LOG_GSPLICENSE_TRG disable;
alter trigger INTERP.ZX_LOG_GSPGOODSLICENSE_TRG disable;
alter trigger INTERP.ZX_LOG_COMPANYBASE_TRG disable;
alter trigger INTERP.ZX_LOG_GOODSBASE_TRG disable;
alter trigger INTERP.ZX_DEMATIC2YTYY_TR disable;
alter trigger INTERP.ZX_LOG_GSPLICENSE_TRG disable;




移除JOB：
select job,log_user,schema_user from dba_jobs;
exec dbms_job.remove();
exec dbms_ijob.remove(21);


select * from dba_scheduler_jobs where owner= 'INTERP';
禁用：
DBMS_SCHEDULER.DISABLE('job1, job2, job3, sys.jobclass1, sys.jobclass2');
或删除：
DBMS_SCHEDULER.DROP_JOB (job_name => 'my_job1');


十、创建replicat进程


dblogin userid odc@ytyyogg password odc
add replicat yt_rep exttrail ./dirdat/yt


edit param yt_rep


参数：
replicat yt_rep
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid odc@ytyyogg, password odc
--handlecollisions
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DBOPTIONS DEFERREFCONST
HANDLETPKUPDATE
--batchsql
ddlerror 955 ignore
ddlerror 1917 ignore
ddlerror 24344 ignore
ddl include mapped
DISCARDFILE ./dirrpt/yt.dsc, APPEND megabytes 20
DISCARDROLLOVER on sunday
--DISCARDROLLOVER
--ddlerror 942 ignore
map INTERP.* target INTERP.* ;








十一、在初始化完成后启动yt_rep


start yt_rep atcsn 9874606502

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25427762/viewspace-1128062/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25427762/viewspace-1128062/