Oracle 11g 单实例到单实例OGG同步实施文档RMAN 初始
1.环境介绍 类别 源端 目标端 数据库类型 单实例 单实例 数据库版本 11.2.0.4 11.2.0.4 Oracle_SID cndba cndba DB_NAME cndba cndba 主机IP地址 192.168.1.85 192.168.1.86 OS版本 RedHat 6.7 RedHat 6.7 OGG版本 11.2.1
<h2>1.环境介绍</h2>2.安装前的准备工作
2.1.源端创建GoldenGate用户表空间
create tablespace ogg_data datafile '/u01/app/oracle/oradata/cndba/ogg01.dbf' size 100m autoextend off;
2.2.源端创建GoldenGate用户
create user ogg identified by ogg default tablespace ogg_data; grant connect,resource,dba,create table,create sequence to ogg;
2.3.目标端创建GoldenGate用户表空间
create tablespace ogg_data datafile '/u01/app/oracle/oradata/cndba/ogg01.dbf' size 100m autoextend off;
2.4.目标端创建GoldenGate用户表空间
create user ogg identified by ogg default tablespace ogg_data; grant connect,resource,dba,create table,create sequence to ogg;
2.5.源端创建测试用户及测试数据
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table test (id number(10) primary key ,name varchar(8));
Table created. SQL> insert into test values(1,'zhangsan');
1 row created. SQL> insert into test values(2,'lisi');
1 row created.
SQL> commit;
2.6.目标端创建测试用户及测试数据
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table test (id number(10) primary key ,name varchar(8));
Table created. 目标端不需要插入数据
2.7.源端开启归档模式、强制日志、附加日志
2.7.1.查看是否开启归档模式、强制日志、附加日志 SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
NOARCHIVELOG NO NO
2.7.2.开启归档 [root@www.cndba.cn cndba]# mkdir -p /u01/archive [root@www.cndba.cn cndba]# chown -R oracle:oinstall /u01/archive/ SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 5 Current log sequence 7 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 1060585472 bytes Fixed Size 2260000 bytes Variable Size 905970656 bytes Database Buffers 146800640 bytes Redo Buffers 5554176 bytes Database mounted. SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system set log_archive_dest_1='location=/u01/archive';
System altered.
SQL> archive log liset SP2-0718: illegal ARCHIVE LOG option SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/archive Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7
2.7.3.开启强制日志 SQL> alter database force logging; Database altered.
2.7.4.开启附加日志 SQL> alter database add supplemental log data;
Database altered.
2.7.5.查看是否开启归档模式、强制日志、附加日志 SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
ARCHIVELOG YES YES
2.7.6.查看回收站是否关闭 SQL> show parameter recycle
NAME TYPE VALUE
buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string on SQL> alter system set recyclebin=off scope=spfile;
System altered. --重启数据库查看 SQL> show parameter recycle
NAME TYPE VALUE
buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string OFF
3.GoldenGate安装
3.1.源端安装OGG
3.1.1.创建软件安装目录并赋权 [root@www.cndba.cn software]# mkdir -p /u01/app/oracle/ogg [root@www.cndba.cn software]# chown -R oracle:oinstall /u01/app/oracle/ogg
3.1.2.配置oracle用户环境变量 [oracle@www.cndba.cn ~]$ vi .bash_profile 设置Library 路径 假设OGG的安装目录是/u01/app/oracle/ogg，那么在/home/oracle/.bash_profile文件里添加如下内容： export OGG_HOME=$ORACLE_BASE/ogg export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib --source 使修改生效： [oracle@www.cndba.cn ~]$ source .bash_profile
3.1.3.解压ogg文件 [root@www.cndba.cn software]# cd /software/ [root@www.cndba.cn software]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [root@www.cndba.cn software]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/oracle/ogg [root@www.cndba.cn ogg]# chown -R oracle:oinstall /u01/app/oracle/ogg 注意：/u01/app/oracle/ogg 是$OGG_HOME
3.1.4.运行ogg并创建目录 [oracle@www.cndba.cn ~]$ cd $OGG_HOME [oracle@www.cndba.cn ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (cndba) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm: already exists Report files /u01/app/oracle/ogg/dirrpt: created Checkpoint files /u01/app/oracle/ogg/dirchk: created Process status files /u01/app/oracle/ogg/dirpcs: created SQL script files /u01/app/oracle/ogg/dirsql: created Database definitions files /u01/app/oracle/ogg/dirdef: created Extract data files /u01/app/oracle/ogg/dirdat: created Temporary files /u01/app/oracle/ogg/dirtmp: created Stdout files /u01/app/oracle/ogg/dirout: created
3.2.目标端安装OGG
3.2.1.创建软件安装目录并赋权 [root@www.cndba.cn software]# mkdir -p /u01/app/oracle/ogg [root@www.cndba.cn software]# chown -R oracle:oinstall /u01/app/oracle/ogg
3.2.2.配置oracle用户环境变量 [oracle@www.cndba.cn ~]$ vi .bash_profile 设置Library 路径 假设OGG的安装目录是/u01/app/oracle/ogg，那么在/home/oracle/.bash_profile文件里添加如下内容： export OGG_HOME=$ORACLE_BASE/ogg export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib --source 使修改生效： [oracle@www.cndba.cn ~]$ source .bash_profile
3.2.3.解压ogg文件 [root@www.cndba.cn software]# cd /software/ [root@www.cndba.cn software]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [root@www.cndba.cn software]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/oracle/ogg [root@www.cndba.cn ogg]# chown -R oracle:oinstall /u01/app/oracle/ogg 注意：/u01/app/oracle/ogg 是$OGG_HOME
3.2.4.运行ogg并创建目录 [oracle@www.cndba.cn ~]$ cd $OGG_HOME [oracle@www.cndba.cn ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (cndba) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm: already exists Report files /u01/app/oracle/ogg/dirrpt: created Checkpoint files /u01/app/oracle/ogg/dirchk: created Process status files /u01/app/oracle/ogg/dirpcs: created SQL script files /u01/app/oracle/ogg/dirsql: created Database definitions files /u01/app/oracle/ogg/dirdef: created Extract data files /u01/app/oracle/ogg/dirdat: created Temporary files /u01/app/oracle/ogg/dirtmp: created Stdout files /u01/app/oracle/ogg/dirout: created
4.GoldenGate配置
4.1.OGG源端配置
4.1.1.配置mgr进程
GGSCI (cndba) 3> edit params mgr
GGSCI (cndba) 4> view params mgr
port 7809
GGSCI (cndba) 5> start mgr
Manager started.
GGSCI (cndba) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看7809端口是否启用
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 :::7809 :::* LISTEN 14176/./mgr
GGSCI (cndba) 8> sh ps -ef|grep mgr --查看mgr进程是否存在
root 14 2 0 13:24 ? 00:00:00 [async/mgr] postfix 1867 1860 0 13:26 ? 00:00:00 qmgr -l -t fifo -u oracle 14176 14114 0 15:43 ? 00:00:00 ./mgr PARAMFILE /u01/app/oracle/ogg/dirprm/mgr.p oracle 14185 14114 0 15:44 pts/0 00:00:00 sh -c ps -ef|grep mgr oracle 14187 14185 0 15:44 pts/0 00:00:00 grep mgr
4.1.2.添加表级transdata GGSCI (cndba) 10> dblogin userid ogg,password ogg Successfully logged into database. 查看是否开启 GGSCI (cndba) 11> info trandata test.test GGSCI (cndba) 11> add trandata test.* Logging of supplemental redo data enabled for table TEST.TEST. 注意：为了方便这里直接添加整个用户下表级transdata，如果只是同步部分表的，请批量 执行add trandata test.tablename
4.1.3.配置extract抽取进程 GGSCI (cndba) 13> dblogin userid ogg,password ogg Successfully logged into database.
GGSCI (cndba) 14> add extract ext1, tranlog, begin now, threads 1 EXTRACT added.
GGSCI (cndba) 15> add exttrail ./dirdat/et, extract ext1 EXTTRAIL added.
GGSCI (cndba) 16> edit params ext1 GGSCI (cndba) 17> view params ext1
EXTRACT ext1 SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") --此处数据库字符集设为一致 --SETENV (ORACLE_SID = "cndba") SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) USERID ogg, PASSWORD ogg THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 EXTTRAIL ./dirdat/et DYNAMICRESOLUTION --DDL INCLUDE ALL TABLE test.*;
4.1.4.配置pump传输进程 GGSCI (cndba) 18> add extract pump1,exttrailsource ./dirdat/et,begin now EXTRACT added.
GGSCI (cndba) 19> add rmttrail ./dirdat/et,extract pump1 RMTTRAIL added.
GGSCI (cndba) 20> edit params pump1 GGSCI (cndba) 21> view params pump1 EXTRACT pump1 RMTHOST 192.168.1.86, MGRPORT 7809 RMTTRAIL ./dirdat/et PASSTHRU DYNAMICRESOLUTION TABLE test.*;
GGSCI (cndba) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:18:00
EXTRACT STOPPED PUMP1 00:00:00 00:04:07
4.2.OGG目标端配置
4.2.1.配置mgr进程
GGSCI (cndba) 3> edit params mgr
GGSCI (cndba) 4> view params mgr
port 7809
GGSCI (cndba) 5> start mgr
Manager started.
GGSCI (cndba) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看7809端口是否启用
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 :::7809 :::* LISTEN 14176/./mgr
GGSCI (cndba) 8> sh ps -ef|grep mgr --查看mgr进程是否存在
root 14 2 0 13:24 ? 00:00:00 [async/mgr] postfix 1867 1860 0 13:26 ? 00:00:00 qmgr -l -t fifo -u oracle 14176 14114 0 15:43 ? 00:00:00 ./mgr PARAMFILE /u01/app/oracle/ogg/dirprm/mgr.p oracle 14185 14114 0 15:44 pts/0 00:00:00 sh -c ps -ef|grep mgr oracle 14187 14185 0 15:44 pts/0 00:00:00 grep mgr
5.初始化数据-RMAN
5.1.启动生产端和容灾端的管理进程
--源端 [oracle@www.cndba.cn ogg]$ cd /u01/app/oracle/ogg [oracle@www.cndba.cn ogg]$ ./ggsci
GGSCI (cndba) 1> start mgr Manager started.
GGSCI (cndba) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:40:35
EXTRACT STOPPED PUMP1 00:00:00 00:26:42
--目标端
[oracle@host1 ~]$ cd /u01/app/oracle/ogg
[oracle@host1 ogg]$ ./ggsci
GGSCI (host1) 1> start mgr Manager started.
GGSCI (cndba) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
5.2.启动源端的EXTRACT进程
GGSCI (cndba) 2> start ext1
Sending START request to MANAGER ... EXTRACT EXT1 starting
GGSCI (cndba) 4> start pump1
Sending START request to MANAGER ... EXTRACT PUMP1 starting
GGSCI (cndba) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:03
EXTRACT RUNNING PUMP1 00:00:00 00:28:57
5.3.查看数据库中所有事务的开始时间
查看数据库中所有事务的开始时间，直到其大于抽取进程的启动时间再开始备份数 据库，因为GoldenGate 的只获取在Extract 启动以后的交易变化，在Extract 启动之 前开始而在Extract 启动以后才完成的交易GoldenGate 将会忽略这些交易，这些被忽 略的交易数据就会丢失。所以需要等数据库中所以的交易都在Extract 启动之后开始 的才能开始备份数据库。通过v$transaction 视图来查看数据库中的交易： select min(start_time) from v$transaction; 这里是测试环境没有事物，可以进行后面的备份了。
5.4.RMAN 备份源端数据库
当所有在Extract 启动之前的开始的交易都完成后，我们就可以使用RMAN 备份生产端的数据库了。备份数据库的过程中一定要密切监控Extract 进程的状态，保证其一直正常运行：
5.4.1.源端使用RMAN备份全库 注意：备份过程保证抽取进程状态一直正常。 --备份归档和控制文件如果有三个日志文件组，可以按照以下方式进行切换，如果更多，则对应增加切换的次数即可） --备份数据库 run { allocate channel ch00 type disk maxpiecesize 10g; allocate channel ch01 type disk maxpiecesize 10g; sql 'alter system switch logfile'; sql 'alter system switch logfile'; sql 'alter system switch logfile'; sql 'alter system switch logfile'; crosscheck backupset; delete noprompt expired backupset; backup database format '/u01/backup/bk%s%p%t'; sql 'alter system archive log current'; BACKUP ARCHIVELOG ALL FORMAT '/u01/backup/ARCH%U'; BACKUP CURRENT CONTROLFILE FORMAT '/u01/backup/bk_controlfile'; release channel ch00; release channel ch01; } --将备份文件拷贝到目标主机上。 cd /u01/backup/ [root@www.cndba.cn backup]# scp * 192.168.1.86:/u01/backup/ --在目标端给备份文件授权 [root@www.cndba.cn backup]# chown -R oracle:oinstall /u01/backup/ 5.5.恢复目标端数据库 5.5.1.讲数据库启动到nomount SQL> startup nomount ORACLE instance started.
Total System Global Area 1060585472 bytes Fixed Size 2260000 bytes Variable Size 905970656 bytes Database Buffers 146800640 bytes Redo Buffers 5554176 bytes
5.5.2.恢复控制文件 RMAN> restore controlfile from '/u01/backup/bk_controlfile';
Starting restore at 07-JUN-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/cndba/control01.ctl output file name=/u01/app/oracle/oradata/cndba/control02.ctl Finished restore at 07-JUN-18
5.5.3.将数据库启动到mount SQL> alter database mount;
Database altered.
5.5.4.注册备份集 RMAN> catalog start with '/u01/backup/';
using target database control file instead of recovery catalog searching for all files that match the pattern /u01/backup/
List of Files Unknown to the Database
File Name: /u01/backup/bk_controlfile
Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done
List of Cataloged Files
File Name: /u01/backup/bk_controlfile
5.5.5.恢复数据库 RMAN> RESTORE DATABASE;
Starting restore at 07-JUN-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cndba/system01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cndba/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/bk_31_1_978141141 channel ORA_DISK_1: piece handle=/u01/backup/bk_31_1_978141141 tag=TAG20180607T015220 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/cndba/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cndba/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/cndba/ogg01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/bk_32_1_978141141 channel ORA_DISK_1: piece handle=/u01/backup/bk_32_1_978141141 tag=TAG20180607T015220 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 07-JUN-18
RMAN> RECOVER DATABASE; executing command: SET until clause
Starting recover at 07-JUN-18 using channel ORA_DISK_1
starting media recovery media recovery complete, elapsed time: 00:00:00
Finished recover at 07-JUN-18
5.5.6.使用以下SQL语句查找目标端数据库的SCN号：(得到该SCN号之后，启动复制进程时，使用该SCN号) SQL> SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;
CHECKPOINT_CHANGE# CHECKPOINT_T
1066024 07-JUN-18 1066024 07-JUN-18 1066024 07-JUN-18 1066024 07-JUN-18 1066024 07-JUN-18 a) 如果目标端数据库在“5.4.1”操作之后，又额外追加了源数据库rman备份后的归档日志，导致目标端数据库的SCN号大于“5.4.1”中SCN号。所以必须以目标端数据库当前的SCN为主，从而避免数据重复。 b) 如果目标端数据库在“5.4.1”操作之后，没有额外追加源数据库rman备份后的归档日志，则“5.5.6”中得到的SCN号应该与“5.4.1”中的SCN号相等； --以resetlogs方式打开数据库 SQL> alter database open resetlogs; Database altered.
5.5.7.添加GLOBALS参数文件，创新检查点表 GGSCI (cndba) 7> edit params ./GLOBALS
GGSCI (cndba) 8> view params ./GLOBALS --添加以下内容： GGSCHEMA ogg checkpointtable ogg.checkpoint
GGSCI (cndba) 9> dblogin userid ogg,password ogg Successfully logged into database.
GGSCI (cndba) 10> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
5.5.8.配置replicat复制进程 GGSCI (cndba) 11> add replicat rep1, exttrail ./dirdat/et, checkpointtable ogg.checkpoint REPLICAT added.
GGSCI (cndba) 12> edit params rep1 GGSCI (cndba) 13> view params rep1
REPLICAT rep1 setenv (ORACLE_SID=cndba) SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1") USERID ogg,PASSWORD ogg ASSUMETARGETDEFS HANDLECOLLISIONS REPERROR (DEFAULT, DISCARD) DDLERROR DEFAULT DISCARD DDLOPTIONS REPORT DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100 MAP test., TARGET test.;
5.5.9.用SCN 启动Replicat [oracle@www.cndba.cn ogg]$ cd $OGG_HOME [oracle@www.cndba.cn ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (cndba) 15> start rep1, aftercsn 1066024
Sending START request to MANAGER ... REPLICAT REP1 starting
GGSCI (cndba) 33> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:07
6.检查同步是否正常
6.1.DML测试
--检查目标端数据是否正常 GGSCI (cndba) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
SQL> select * from test;
ID NAME
1 zhangsan 2 lisi --源端表中添加数据 SQL> insert into test values(3,'wanger');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
1 zhangsan 2 lisi 3 wanger --目标端查看 SQL> select * from test; ID NAME
1 zhangsan 2 lisi 3 wanger 可以看到可以同步过来的。
7.开启DDL
7.1.添加参数
GGSCI (cndba) 8> edit params ./GLOBALS GGSCI (cndba) 9> view params ./GLOBALS
GGSCHEMA ogg
7.2.在源端执行与DDL同步相关的SQL脚本
切记@marker_setup.sql 一定要在cd $OGG_HOME目录下执行否则会卡主。执行脚本时要输入ogg管理用户，本实例是ogg。 cd /u01/app/oracle/ogg sqlplus / as sysdba grant execute on utl_file to ogg; @marker_setup.sql @ddl_setup.sql @role_setup.sql grant GGS_GGSUSER_ROLE to ogg; @ddl_enable.sql @ddl_pin ogg @marker_status
7.3.源端extract 配置
GGSCI (cndba) 12> edit params ext1 GGSCI (cndba) 16> view params ext1
EXTRACT ext1 SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") --此处数据库字符集设为一致 --SETENV (ORACLE_SID = "cndba") SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) USERID ogg, PASSWORD ogg THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 EXTTRAIL ./dirdat/et DYNAMICRESOLUTION DDL INCLUDE ALL TABLE test.*; 重启extract进程 GGSCI (cndba) 13> stop ext1
Sending STOP request to EXTRACT EXT1 ... Request processed.
GGSCI (cndba) 14> start ext1
Sending START request to MANAGER ... EXTRACT EXT1 starting
7.4.目标端replicat 配置
GGSCI (cndba) 8> edit params rep1 GGSCI (cndba) 9> view params rep1
REPLICAT rep1 setenv (ORACLE_SID=cndba) SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1") USERID ogg,PASSWORD ogg ASSUMETARGETDEFS HANDLECOLLISIONS REPERROR (DEFAULT, DISCARD) DDLERROR DEFAULT DISCARD DDLOPTIONS REPORT DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100 MAP test., TARGET test.; ddl include all ddlerror default ignore retryop maxretries 3 retrydelay 5 重启replicat进程 GGSCI (cndba) 10> stop rep1
Sending STOP request to REPLICAT REP1 ... Request processed.
GGSCI (cndba) 11> start rep1
Sending START request to MANAGER ... R SQL> insert into test1 values(1,'zhangsan');
1 row created.
SQL> commit;
EPLICAT REP1 starting
7.5.DDL测试
--源端： SQL> create table test1 (id number(10) primary key ,name varchar(8));
Table created. Commit complete. --目标端： SQL> desc test1 Name Null? Type
ID NOT NULL NUMBER(10) NAME VARCHAR2(8)
SQL> select * from test1;
ID NAME
1 zhangsan
更多Oracle相关信息见Oracle 专题页面 https://www.558idc.com/topicnews.aspx?tid=12
