「这是我参与11月更文挑战的第5天，活动详情查看：2021最后一次更文挑战」
目录
查看目标端的SCN号，记录该SCN号（得到该SCN号之后，启动复制进程时，使⽤该SCN号）
一、环境介绍
| 项目 | 源库 | 目标库 |
| 操作系统 | RHEL6.8 | RHEL6.8 |
| 主机名 | s11g | t11g |
| IP地址 | 192.168.56.20 | 192.168.56.21 |
| 数据库及版本 | ORACLE11.2.0.4 | ORACLE11.2.0.4 |
| 数据库字符集 | AL32UTF8 | AL32UTF8 |
| ORACLE SID | orcl | orcl |
| Goldengate用户 | ogg | ogg |
| Goldengate版本 | 112103(Linux x86-64) | 112103(Linux x86-64) |
二、GoldenGate安装（源端+目标端）
1、创建OGG安装目录
mkdir -p /ogg
chown -R oracle:oinstall /ogg
chmod 775 /ogg
2、修改OGG操作系统用户环境变量
cat<<EOF >> /home/oracle/.bash_profile
################ogg profile add#########################
export OGG_HOME=/ogg
export PATH=\$ORACLE_HOME/bin:\$PATH:\$OGG_HOME
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$OGG_HOME:/lib:/usr/lib
alias ggsci='rlwrap ggsci'
EOF
4、上传OGG安装介质并解压
chown -R oracle:oinstall /soft/
su - oracle -c "tar -xvf /soft/fbo_ggs_Linux_x64_ora11g_64bit.tar -C /ogg"
5、建立OGG表空间及用户，给用户授权
create tablespace ogg_data datafile '/oradata/orcl/ogg01.dbf' size 1024M;
create user ogg identified by ogg default tablespace ogg_data;
grant connect,resource,dba,create table,create sequence to ogg;
6、配置TNS
--源端
cat <<EOF >> /etc/hosts
192.168.56.21 t11g
EOF
--目标端
cat <<EOF >> /etc/hosts
192.168.56.20 s11g
EOF
--源端和目标端
cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
SORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = s11g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
TORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = t11g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EOF
7、配置数据库参数
附加日志
alter database add SUPPLEMENTAL log data;
强制日志
alter database force logging;
检查是否开启
select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
开启归档日志模式
mkdir /archivelog
chown -R oracle:oinstall /archivelog/
su - oracle
sqlplus / as sysdba
alter system set log_archive_dest_1='location=/archivelog';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list;
alter system switch logfile;
关闭回收站
alter system set recyclebin=off scope=spfile;
8、GGSCI初始化目录
cd /ogg
ggsci
GGSCI (s11g) 1> create subdirs
三、GoldenGate配置
1、配置mgr进程（源端+目标端）
GGSCI (s11g) 1> edit params mgr
GGSCI (s11g) 2> view params mgr
port 7809
GGSCI (s11g) 3> start mgr
Manager started.
GGSCI (s11g) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (s11g) 6> sh netstat -ntpl|grep 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 5760/./mgr
GGSCI (s11g) 7> sh ps -ef|grep mgr
root 14 2 0 09:59 ? 00:00:00 [async/mgr]
postfix 1683 1676 0 09:59 ? 00:00:00 qmgr -l -t fifo -u
oracle 5760 5752 0 11:52 ? 00:00:00 ./mgr PARAMFILE /ogg/dirprm/mgr.prm REPORTFILE /ogg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
oracle 5852 5752 0 12:15 pts/0 00:00:00 sh -c ps -ef|grep mgr
oracle 5854 5852 0 12:15 pts/0 00:00:00 grep mgr
2、添加表级trandata（源端）
GGSCI (s11g) 1> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (s11g) 4> info trandata scott.emp
Logging of supplemental redo log data is disabled for table SCOTT.EMP.
GGSCI (s11g) 5> add trandata scott.emp
Logging of supplemental redo data enabled for table SCOTT.EMP.
3、配置extract抽取进程（源端）
GGSCI (s11g) 6> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (s11g) 7> add extract ext1,tranlog,begin now,threads 1
EXTRACT added.
GGSCI (s11g) 8> add exttrail ./dirdat/et,extract ext1
EXTTRAIL added.
GGSCI (s11g) 9> edit params ext1
GGSCI (s11g) 10> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
--SETENV (ORACLE_SID = "orcl")
SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
USERID ogg, PASSWORD ogg
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
--DDL INCLUDE ALL
--TABLE scott.emp;
TABLE scott.*;
4、配置pump传输进程（源端）
GGSCI (s11g) 12> add extract pump1,exttrailsource ./dirdat/et,begin now
EXTRACT added.
GGSCI (s11g) 13> add rmttrail ./dirdat/et,extract pump1
RMTTRAIL added.
GGSCI (s11g) 14> edit params pump1
GGSCI (s11g) 15> view params pump1
EXTRACT pump1
RMTHOST 192.168.56.21, MGRPORT 7809
RMTTRAIL ./dirdat/et
PASSTHRU
DYNAMICRESOLUTION
--TABLE scott.emp;
TABLE scott.*;
GGSCI (s11g) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:06:34
EXTRACT STOPPED PUMP1 00:00:00 00:01:43
四、RMAN初始化数据
1、源端启动extract进程
GGSCI (s11g) 4> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (s11g) 5> start pump1
Sending START request to MANAGER ...
EXTRACT PUMP1 starting
GGSCI (s11g) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:12:12 00:00:10
EXTRACT RUNNING PUMP1 00:00:00 00:14:56
2、查询源端是否有长事务运行
select min(start_time) from gv$transaction;
3、RMAN备份源端数据库
注意：备份过程保证抽取进程状态⼀直正常。
--源端和目标端都创建目录
mkdir /backup
chown -R oracle:oinstall /backup
--源端备份全库
su - oracle
rman target /
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 '/backup/bk_%s_%p_%t';
sql 'alter system archive log current';
BACKUP ARCHIVELOG ALL FORMAT '/backup/ARCH_%U';
BACKUP CURRENT CONTROLFILE FORMAT '/backup/bk_controlfile';
release channel ch00;
release channel ch01;
}
4、目标端RMAN恢复
备份文件拷贝至目标端
scp * 192.168.56.21:/backup
目标端将数据库启动到nomount状态
sqlplus / as sysdba
shutdown immediate
startup nomount
目标端恢复控制文件
su - oracle
rman target /
restore controlfile from '/backup/bk_controlfile';
目标端启动到mount
alter database mount;
目标端rman恢复
restore database;
--recover之前需要将redo log都mv或者rm掉，再进行recover
recover database;
否则会报错：
RMAN-03002: failure of recover command at 03/24/2021 13:07:31
ORA-19698: /oradata/orcl/redo01.log is from different database: id=1595737004, db_name=ORCL
查看目标端的SCN号，记录该SCN号（得到该SCN号之后，启动复制进程时，使⽤该SCN号）
SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;
1042454
以resetlogs方式开启目标端数据库（目标端）
alter database open resetlogs;
5、添加GLOBALS参数文件，创建检查点表（目标端）
GGSCI (t11g) 10> edit params ./GLOBALS
GGSCI (t11g) 11> view params ./GLOBALS
GGSCHEMA ogg
checkpointtable ogg.checkpoint
GGSCI (t11g) 12> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (t11g) 13> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
6、配置replicate复制进程（目标端）
GGSCI (t11g) 14> add replicat rep1, exttrail ./dirdat/et, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (t11g) 15> edit params rep1
GGSCI (t11g) 16> view params rep1
REPLICAT rep1
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
MAP scott.*, TARGET scott.*;
7、用SCN启动Replicat（目标端）
GGSCI (t11g) 17> start rep1,aftercsn 1042454
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (t11g) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:07:29
--这边启动报错：
2021-03-24 13:30:22 ERROR OGG-00664 OCI Error creating temporary LOB to retrieve default LOB chunk size (status = 1187-ORA-01187: cannot read from fi
le because it failed verification tests
ORA-01110: data file 201: '/oradata/orcl/temp01.dbf').
2021-03-24 13:30:22 ERROR OGG-01668 PROCESS ABENDING.
--解决方案：
目标端temp临时表空间drop重建
alter database tempfile '/oradata/orcl/temp01.dbf' drop;
alter tablespace temp add tempfile '/oradata/orcl/temp01.dbf' size 19M reuse;
--重新启动replicat进程
GGSCI (t11g) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:02:13
8、检查是否同步
DML测试
查看两端scott.emp数据，数据一致
SYS@orcl>set line222
SYS@orcl>set pagesize100
SYS@orcl>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
源端插入一条数据
insert into emp(empno,ename,job) values (9874,'LUCIFER','DBA');
commit;
查询目标端发现已经同步
SYS@orcl>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9874 LUCIFER DBA
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
15 rows selected.
五、开启DDL
1、添加参数（源端）
GGSCI (s11g) 2> edit params ./GLOBALS
GGSCI (s11g) 3> view params ./GLOBALS
GGSCHEMA ogg
2、在源端执⾏与DDL同步相关的SQL脚本
su - oracle
--切记@marker_setup.sql ⼀定要在cd $OGG_HOME⽬录下执⾏否则会卡主。执⾏脚本时要输⼊ogg管理⽤户，本实例是ogg。
cd /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
3、源端extract 配置
GGSCI (s11g) 10> edit params ext1
GGSCI (s11g) 11> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
--SETENV (ORACLE_SID = "orcl")
SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
USERID ogg, PASSWORD ogg
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
--增加如下ddl参数
DDL INCLUDE ALL
TABLE scott.emp;
4、重启extract进程
GGSCI (s11g) 13> stop ext1
Sending STOP request to EXTRACT EXT1 ...
Request processed.
GGSCI (s11g) 14> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (s11g) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:00
EXTRACT RUNNING PUMP1 00:00:00 00:00:01
5、目标端replicat配置
GGSCI (t11g) 3> edit params rep1
GGSCI (t11g) 4> view params rep1
REPLICAT rep1
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
MAP scott.*, TARGET scott.*;
--添加如下ddl参数
ddl include all
ddlerror default ignore retryop maxretries 3 retrydelay 5
6、重启replicat进程
GGSCI (t11g) 5> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (t11g) 6> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (t11g) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
7、DDL测试
查看scott下的表（源端+目标端）
SCOTT@orcl>select table_name from user_tables;
TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT
4 rows selected.
源端创建一张表
SCOTT@orcl>create table lucifer(id number not null,name varchar2(20));
Table created.
SCOTT@orcl>insert into lucifer(id,name) values (1,'lucifer');
1 row created.
SCOTT@orcl>insert into lucifer(id,name) values (2,'lpc');
1 row created.
SCOTT@orcl>insert into lucifer(id,name) values (3,'hsx');
1 row created.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>select table_name from user_tables;
TABLE_NAME
------------------------------
LUCIFER
SALGRADE
BONUS
EMP
DEPT
5 rows selected.
SCOTT@orcl>select * from lucifer;
ID NAME
---------- --------------------
1 lucifer
2 lpc
3 hsx
3 rows selected.
查看目标端已经同步
SYS@orcl>conn scott/scott
Connected.
SCOTT@orcl>show user
USER is "SCOTT"
SCOTT@orcl>select table_name from user_tables;
TABLE_NAME
------------------------------
LUCIFER
SALGRADE
BONUS
EMP
DEPT
SCOTT@orcl>select * from lucifer;
ID NAME
---------- --------------------
1 lucifer
2 lpc
3 hsx
