ogg操作手册.docx

1、ogg操作手册ogg操作手册版本：V1.01. 源端库准备工作oraclerac1 $ps -ef|grep ogg|grep -v grep/查看业务系统是否安装过oggoraclerac1 $sqlplus / as sysdbaSQLshow parameter processes; /查询oracle最大连接数SQLselect count(*) from v$process; /查询当前连接数SQL archive log list/查看是否开启归档Database log mode Archive ModeAutomatic archival Enabled 开启状态Archive

2、 destination +ARCHDGOldest online log sequence 342Next log sequence to archive 343Current log sequence 343*开启归档的步骤*SQLshutdown immediate; /必须是immediate 一至性关闭数据库SQLstartup mount;/开启数据库到mount状态SQL alter system set log_archive_dest_1=location=+archdg;/指定归档日志存放的路径SQLalter database archivelog;/开启归档SQLalt

3、er database open;/打开数据库 SQL alter system switch logfile;/切换归档SQL alter system archive log current;/切换归档SQLSELECT FORCE_LOGGING FROM V$DATABASE;/查看是否开启强制日志SQLalter database force logging;/开启强制日志的命令SQLselect supplemental_log_data_min from v$database;/查看是否开启附加日志SQLalter database add supplemental log da

4、ta ;/开启附加日至命令SQLalter system switch logfile;/切换归档1.1创建ogg管理用户并授权SQLcreate user cas_sgcis identified by Sap12345;SQLgrant connect to cas_sgcis;SQLgrant SELECT ANY DICTIONARY to cas_sgcis;SQLgrant select any table to cas_sgcis;1.2安装telnet工具oraclerac1 $rpm -ivh telnet-0.17-47.el6_3.1.x86_64.rpmoraclera

5、c1 $rpm -ivh telnet-server-0.17-47.el6_3.1.x86_64.rpm/源端和目标端都要安装telnet编辑设置 /etc/xinetd.d/telnet ,将disable= yes设置成disable= noecho pts/0 /etc/securettyecho pts/1 /etc/securettyservice xinetd restart/设置root远程登录1.3磁盘使用情况oraclerac1 $ df -hFilesystem Size Used Avail Use% Mounted on/dev/sda3 145G 19G 119G

6、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 /ogg2.1设置ogg环境变量oraclerac1 $vi .bash_profileexport OGG_HOME=/oggoraclerac1 $. .bash_profile2.2上传ogg软件到相关目录oraclerac1 ogg$

7、 unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.ziporaclerac1 ogg$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar/解压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/初始化o

8、gg目录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: createdExtrac

9、t data files /ogg/dirdat: createdTemporary files /ogg/dirtmp: createdStdout files /ogg/dirout: created2.3登陆GGSCI (rac1) 1dblogin userid cas_sgcis,password Sap123452.4创建def文件GGSCI (rac1) 1edit params fdpm_defdefsfile /ogg/dirdef/fdpmaster_ah.defuserid CAS_SGCIS,password Sap12345table fdpmaster_ah.G_L

10、INE;oraclerac1 ogg$ ./defgen paramfile ./dirprm/fdpm_def.prm/生成def定义文件oraclerac1 ogg$ scp /ogg/dirdef/fdpmaster_ah.def root192.168.1.205:/ogg/dirdef/把定义文件传到目标端2.5创建Manager进程GGSCIedit params mgrPORT 8333 DYNAMICPORTLIST 8334-8999-AUTOSTART ER *AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTR

11、ACTS ./dirdat/*,usecheckpoints, minkeepdays 3LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 452.6创建Extract进程GGSCI (rac1) 2 edit params extractextract extractGETTRUNCATESGETUPDATEBEFORESFETCHOPTIONS FETCHPKUPDATECOLSdynamicresolutionuserid CAS_SGCIS,password Sap12345 TRANLOGOPTIONS ASMUSER sysas

12、m, 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 Extract2.7创建Pump进程GGSCI (rac1) 2 edit params pumpextract pumppassthrudynamicresolutionuserid CAS_SGCIS,password Sap12

13、345rmthost 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 Pump2.8开启源端进程GGSCI (rac1) 3start mgrGGSCI (rac1) 3start ExtractGGSCI (rac1) 3start Pump2.9查看进程状态GGSCI

14、(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准备工作3.1磁盘使用情况rootnode01 # df -hFilesystem Size Used Avail Use% Mounted on/dev/sda3 145G 19G 119G 14% /tmpfs 1.5G 980M 457M 69%

15、/dev/shm/dev/sda1 291M 39M 238M 14% /boot3.2创建用户并授权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目录3.2设置ogg环境变量rootnode01 # vi .bash_profileexport OG

16、G_HOME=/oggrootnode01 #. .bash_profile3.3上传ogg软件到相关目录rootnode01 # unzip ggs_Linux_x64_MySQL_64bit.zip rootnode01 # tar -xvf ggs_Linux_x64_MySQL_64bit.tar/解压ogg软件oraclerac1 ogg$ ./ggsciGGSCI (rac1) 1 create subdirs/初始化ogg目录Creating subdirectories under current directory /oggParameter files /ogg/dirpr

17、m: 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/di

18、rout: created3.4登陆GGSCI (rac1) 1dblogin sourcedb mysql_ogg192.168.1.210:23306,userid dbscale,password oracleGGSCI (rac1) 1dblogin sourcedb mysql_ogg,userid root,password oracle3.5添加检查点系统表GGSCI (rac1) 1edit params ./GLOBALScheckpointtable mysql_ogg.chktabGGSCI (rac1) 1add checkpointtable mysql_ogg.ch

19、ktab3.6创建Manager进程GGSCIedit params mgrPORT 8333 DYNAMICPORTLIST 8334-8999-AUTOSTART ER *AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 453.7创建Replicat进程GGSCI (node01) 2edit params replicatrepli

20、cat replicatsourcedefs /ogg/dirdef/fdpmaster_ah.defGETTRUNCATESapplynoopupdatesdboptions nolimitrowsINSERTUPDATESINSERTDELETESINSERTALLRECORDSREPERROR (DEFAULT, ABEND)TARGETDB mysql_ogg192.168.1.210:23306,userid dbscale,password oracleMap fdpmaster_ah.G_LINE,target yxjcsjpt_fdpmaster_ah.G_LINE,colma

21、p(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,AF

22、TER,I,0),INSERT,I,SQL COMPUPDATE,CASE(GETENV(GGHEADER,BEFOREAFTERINDICATOR),BEFORE,D,AFTER,I,0),0),informatica_ogg_seq=COLSTAT(NULL); GGSCI (node01) 2add replicat replicat,exttrail /ogg/dirdat/a5,checkpointtable mysql_ogg.chktab3.8开启目标端进程GGSCI (node01) 2start mgrGGSCI (node01) 2start Replicat3.9查看目标端进程的状态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