“This is the fifth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

I. Introduction to the environment

project	The source repository	Object library
The operating system	RHEL6.8	RHEL6.8
The host name	s11g	t11g
The IP address	192.168.56.20	192.168.56.21
Database and Version	ORACLE11.2.0.4	ORACLE11.2.0.4
Database character set	AL32UTF8	AL32UTF8
ORACLE SID	orcl	orcl
Goldengate user	ogg	ogg
Goldengate version	112103(Linux x86-64)	112103(Linux x86-64)

2. GoldenGate installation (source + destination)

1. Create an OGG installation directory

mkdir -p /ogg
chown -R oracle:oinstall /ogg
chmod 775 /ogg
Copy the code

2. Modify OGG operating system user environment variables

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
Copy the code

4. Upload the OGG installation media and decompress it

chown -R oracle:oinstall /soft/
su - oracle -c "tar -xvf /soft/fbo_ggs_Linux_x64_ora11g_64bit.tar -C /ogg"
Copy the code

5, create OGG tablespace and user, authorize user

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;
Copy the code

6. Configure TNS

-- source cat <<EOF >> /etc/hosts 192.168.56.21T11G EOF-- target cat <<EOF >> /etc/hosts
192.168.56.20 s11g
EOFCat >>$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
Copy the code

7. Set database parameters

Additional log

alter database add SUPPLEMENTAL log data;
Copy the code

Forced to log

alter database force logging;
Copy the code

Check whether it is enabled.

select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
Copy the code

Enable the archive log mode

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;
Copy the code

Close the recycle bin

alter system set recyclebin=off scope=spfile;
Copy the code

8. GGSCI initializes directory

cd /ogg
ggsci

GGSCI (s11g) 1> create subdirs
Copy the code

Three, GoldenGate configuration

1. Configure the MGR process (source + target)

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 mgrCopy the code

Add table level 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.
Copy the code

3, Configure extract extract process (source)

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") the 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.*;Copy the code

4. Configure pump transfer process (source)

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:43Copy the code

4. RMAN initialization data

1. Start the extract process on the source end

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 
Copy the code

2. Check whether long transactions are running on the source end

select min(start_time) from gv$transaction;
Copy the code

3, RMAN backup source database

Note: The backup process ensures that the extraction process status is always normal.

Mkdir /backup chown -r oracle:oinstall /backup -- Oracle rman target/run {allocate channel ch00type 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;
}
Copy the code

4. RMAN of the target is restored

Copy the backup file to the target end

SCP * 192.168.56.21: / backupCopy the code

The target starts the database to nomount

sqlplus / as sysdba
shutdown immediate
startup nomount
Copy the code

The target restores the control file

su - oracle
rman target /
restore controlfile from '/backup/bk_controlfile';
Copy the code

The target is started to mount

alter database mount;
Copy the code

The RMAN of the target end is restored. Procedure

restore database; Redo is required before recoverlogRecover recover database if mv or RM is dropped. Rman-03002: Failure of recovercommand at 03/24/2021 13:07:31
ORA-19698: /oradata/orcl/redo01.log is from different database: id=1595737004, db_name=ORCL
Copy the code

View the SCN number of the target end and record the SCN number (after obtaining the SCN number, the SCN number will be used when starting the replication process)

SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;
1042454
Copy the code

Start the target database with resetLogs (target)

alter database open resetlogs;
Copy the code

Add GLOBALS parameter file and create checkpoint table (target end)

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.

Copy the code

6. Configure replicate process (target end)

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.*;
Copy the code

7. Enable Replicat with SCN

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: cannotread 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'/oradata/orcl/temp01.dbf' drop;
alter tablespace temp add tempfile '/oradata/orcl/temp01.dbf'size 19M reuse; -- Restart the REPLICat process GGSCI (T11G) 18> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING replicat RUNNING REP1 00:00:00 00:02:13Copy the code

8. Check whether the data is synchronized

DML test

Check scott.emp data on both ends. The data is consistent

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.
Copy the code

The source inserts a piece of data

insert into emp(empno,ename,job) values (9874.'LUCIFER'.'DBA');
commit;
Copy the code

Synchronization was found on the target end. Procedure

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.
Copy the code

5. Enable DDL

1. Add parameters (source)

GGSCI (s11g) 2> edit params ./GLOBALS

GGSCI (s11g) 3> view params ./GLOBALS
GGSCHEMA ogg
Copy the code

2. Execute SQL scripts related to DDL synchronization at the source end

su - oracle
-- Remember that @marker_setup. SQL must be executed in the CD $OGG_HOME directory otherwise it will stall. When executing the script, enter the ogG management user, which is ogg in this example.
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
Copy the code

3, source extract configuration

GGSCI (s11g) 10> edit params ext1

GGSCI (s11g) 11> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
--SETENV (ORACLE_SID = "orcl") the SETENV (ORACLE_HOME = / u01 / app/oracle/product / 11.2.0 / db) USERID ogg, PASSWORD ogg THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 EXTTRAIL. / dirdat/et DYNAMICRESOLUTION DDL, increase the following DDL parameters INCLUDE ALL TABLE scott.emp;Copy the code

Restart the extract process

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 
Copy the code

5. Replicat is configured on the target end

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 include all DDlerror default ignore retryop maxretries 3 Retrydelay 5Copy the code

6. Restart the Replicat process

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    

Copy the code

7. DDL testing

View the table under Scott (source + target)

SCOTT@orcl>select table_name from user_tables;

TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT

4 rows selected.
Copy the code

The source creates a table

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.

Copy the code

Check whether the target is synchronized

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
Copy the code

OGG (11G) single instance — > Single instance build — isomorphism and version

directory