ogg操作手册(14页).doc
-ogg操作手册-第 13 页ogg操作手册版本：V1.0目录1.源端库准备工作3创建ogg管理用户并授权5安装telnet工具5磁盘使用情况62.源端ogg准备工作6设置ogg环境变量6上传ogg软件到相关目录6登陆8创建def文件8创建Manager进程8创建Extract进程9创建Pump进程9开启源端进程10查看进程状态103.目标端ogg准备工作11磁盘使用情况11创建用户并授权11设置ogg环境变量11上传ogg软件到相关目录12登陆13添加检查点系统表13创建Manager进程13创建Replicat进程14开启目标端进程15查看目标端进程的状态151. 源端库准备工作oraclerac1 $ps -ef|grep ogg|grep -v grep/查看业务系统是否安装过oggoraclerac1 $sqlplus / as sysdbaSQL>show parameter processes; /查询oracle最大连接数SQL>select count(*) from v$process; /查询当前连接数SQL> archive log list/查看是否开启归档Database log mode Archive ModeAutomatic archival Enabled 开启状态Archive destination +ARCHDGOldest online log sequence 342Next log sequence to archive 343Current log sequence 343*开启归档的步骤*SQL>shutdown immediate; /必须是immediate 一至性关闭数据库SQL>startup mount;/开启数据库到mount状态SQL> alter system set log_archive_dest_1='location=+archdg'/指定归档日志存放的路径SQL>alter database archivelog;/开启归档SQL>alter database open;/打开数据库 SQL> alter system switch logfile;/切换归档SQL> alter system archive log current;/切换归档SQL>SELECT FORCE_LOGGING FROM V$DATABASE;/查看是否开启强制日志SQL>alter database force logging;/开启强制日志的命令SQL>select supplemental_log_data_min from v$database;/查看是否开启附加日志SQL>alter database add supplemental log data ;/开启附加日至命令SQL>alter system switch logfile;/切换归档创建ogg管理用户并授权SQL>create user cas_sgcis identified by Sap12345;SQL>grant connect to cas_sgcis;SQL>grant SELECT ANY DICTIONARY to cas_sgcis;SQL>grant select any table to cas_sgcis;安装telnet工具oraclerac1 $oraclerac1 $/源端和目标端都要安装telnet编辑设置 ,将disable= yes设置成disable= noecho 'pts/0' >>/etc/securettyecho 'pts/1' >>/etc/securettyservice xinetd restart/设置root远程登录磁盘使用情况oraclerac1 $ df -hFilesystem Size Used Avail Use% Mounted on/dev/sda3 145G 19G 119G 14% /tmpfs 1.5G 980M 457M 69% /dev/shm/dev/sda1 291M 39M 238M 14% /boot2. 源端ogg准备工作oraclerac1 $mkdir /ogg/创建ogg目录oraclerac1 $chown oracle:oinstall -R /oggoraclerac1 $chmod 775 -R /ogg设置ogg环境变量oraclerac1 $vi .bash_profileexport OGG_HOME=/oggoraclerac1 $. .bash_profile上传ogg软件到相关目录/解压ogg软件oraclerac1 ogg$ cp $ORACLE_HOME/lib/libnnz11.so /ogg oraclerac1 ogg$ cp $ORACLE_HOME/lib/libclntsh.so.11.1 /ogg/拷贝ogg所需要的共享文件oraclerac1 ogg$ ./ggsciGGSCI (rac1) 1> create subdirs/初始化ogg目录Creating subdirectories under current directory /oggParameter files /ogg/dirprm: already existsReport files /ogg/dirrpt: createdCheckpoint files /ogg/dirchk: createdProcess status files /ogg/dirpcs: createdSQL script files /ogg/dirsql: createdDatabase definitions files /ogg/dirdef: createdExtract data files /ogg/dirdat: createdTemporary files /ogg/dirtmp: createdStdout files /ogg/dirout: created登陆GGSCI (rac1) 1>dblogin userid cas_sgcis,password Sap12345创建def文件GGSCI (rac1) 1>edit params fdpm_defuserid CAS_SGCIS,password Sap12345table fdpmaster_ah.G_LINE;oraclerac1 ogg$ /生成def定义文件oraclerac1 ogg$ scp /ogg/dirdef/fdpmaster_ah.def root192.168.1.205:/ogg/dirdef/把定义文件传到目标端创建Manager进程GGSCI>edit params mgrPORT 8333 DYNAMICPORTLIST 8334-8999-AUTOSTART ER *AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45创建Extract进程GGSCI (rac1) 2> edit params extractextract extractGETTRUNCATESGETUPDATEBEFORESFETCHOPTIONS FETCHPKUPDATECOLSdynamicresolutionuserid CAS_SGCIS,password Sap12345 TRANLOGOPTIONS ASMUSER sysasm, ASMPASSWORD oracleexttrail /ogg/dirdat/a5table fdpmaster_ah.G_LINE;GGSCI (rac1) 2> add extract Extract,tranlog,threads 2,begin nowGGSCI (rac1) 2> add exttrail /ogg/dirdat/a5,extract Extract创建Pump进程GGSCI (rac1) 2> edit params pumpextract pumppassthrudynamicresolutionuserid CAS_SGCIS,password Sap12345rmthost 192.168.1.205,mgrport 8333rmttrail /ogg/dirdat/a5table fdpmaster_ah.G_LINE;GGSCI (rac1) 2> add extract Pump,exttrailsource /ogg/dirdat/a5GGSCI (rac1) 2> add rmttrail /ogg/dirdat/a5,extract Pump开启源端进程GGSCI (rac1) 3>start mgrGGSCI (rac1) 3>start ExtractGGSCI (rac1) 3>start Pump查看进程状态GGSCI (rac1) 4> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING Extract 00:00:00 00:00:07 EXTRACT RUNNING Pump 00:00:00 00:00:01 3. 目标端ogg准备工作磁盘使用情况rootnode01 # df -hFilesystem Size Used Avail Use% Mounted on/dev/sda3 145G 19G 119G 14% /tmpfs 1.5G 980M 457M 69% /dev/shm/dev/sda1 291M 39M 238M 14% /boot创建用户并授权mysql> create user ' dbscale''%' identified by 'oracle'Query OK, 0 rows affected (0.02 sec)mysql> grant ALL PRIVILEGES on *.* to ' dbscale''%' ;Query OK, 0 rows affected (0.12 sec) rootnode01 # mkdir /ogg/创建ogg目录设置ogg环境变量rootnode01 # vi .bash_profileexport OGG_HOME=/oggrootnode01 #. .bash_profile上传ogg软件到相关目录rootnode01 # unzip ggs_Linux_x64_MySQL_64bit.zip rootnode01 # tar -xvf /解压ogg软件oraclerac1 ogg$ ./ggsciGGSCI (rac1) 1> create subdirs/初始化ogg目录Creating subdirectories under current directory /oggParameter files /ogg/dirprm: createdReport files /ogg/dirrpt: createdCheckpoint files /ogg/dirchk: createdProcess status files /ogg/dirpcs: createdSQL script files /ogg/dirsql: createdDatabase definitions files /ogg/dirdef: createdExtract data files /ogg/dirdat: createdTemporary files /ogg/dirtmp: createdStdout files /ogg/dirout: created登陆GGSCI (rac1) 1>dblogin sourcedb mysql_ogg192.168.1.210:23306,userid dbscale,password oracleGGSCI (rac1) 1>dblogin sourcedb mysql_ogg,userid root,password oracle添加检查点系统表GGSCI (rac1) 1>edit params ./GLOBALSGGSCI (rac1) 1>创建Manager进程GGSCI>edit params mgrPORT 8333 DYNAMICPORTLIST 8334-8999-AUTOSTART ER *AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45创建Replicat进程GGSCI (node01) 2>edit params replicatreplicat replicatGETTRUNCATESapplynoopupdatesdboptions nolimitrowsINSERTUPDATESINSERTDELETESINSERTALLRECORDSREPERROR (DEFAULT, ABEND)TARGETDB mysql_ogg192.168.1.210:23306,userid dbscale,password oracleMap fdpmaster_ah.G_LINE,target yxjcsjpt_fdpmaster_ah.G_LINE,colmap(usedefaults,informatica_row_id=GETENV('RECORD','ROWID'),informatica_date_time=GETENV ('GGHEADER', 'COMMITTIMESTAMP'),informatica_flag=CASE(GETENV('GGHEADER','OPTYPE'),'DELETE','D','UPDATE',CASE(GETENV('GGHEADER','BEFOREAFTERINDICATOR'),'BEFORE','D','AFTER','I',0),'PK UPDATE',CASE(GETENV('GGHEADER','BEFOREAFTERINDICATOR'),'BEFORE','D','AFTER','I',0),'INSERT','I','SQL COMPUPDATE',CASE(GETENV('GGHEADER','BEFOREAFTERINDICATOR'),'BEFORE','D','AFTER','I',0),0),informatica_ogg_seq=COLSTAT(NULL); GGSCI (node01) 2>add replicat replicat,exttrail /ogg/dirdat/a5开启目标端进程GGSCI (node01) 2>start mgrGGSCI (node01) 2>start Replicat查看目标端进程的状态GGSCI (node01 DBLOGIN as dbscale) 54> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT RUNNING Replicat 00:00:00 00:00:02
