Reflection of my thoughts and technical expriences: Bounded Recovery In Oracle Golden Gate 12c

Oracle online redo log files contain both committed as well as uncommitted transactions, but Oracle GoldenGate only writes committed transactions to the trail files.

Oracle GoldenGate has two kinds of recovery

Normal Recovery:

where the extract process needs all the archive log files starting from the current recovery read checkpoint of the extract

Bounded Recovery:

Bounded Recovery (BR) Interval for an extract which by default is 4 hours and every 4 hours the extract process will makes a Bounded Recovery checkpoint.

Setup DDL in Source Database Server:

[oracle@ggnode ~]$ source 11g.env

[oracle@ggnode ~]$ cd $GG

[oracle@ggnode 11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 14:51:05 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho user

USER is "SYS"

Execute script-1:

SQL> @marker_setup.sql

Enter Oracle GoldenGate schema name:ogguser

Execute script-2:

SQL> @ddl_setup.sql

Enter Oracle GoldenGate schema name:ogguser

Execute script-3:

SQL> @ddl_setup.sql

Enter Oracle GoldenGate schema name:ogguser

Execute script-4:

SQL> @role_setup.sql

Enter GoldenGate schema name:ogguser

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

SQL> GRANT GGS_GGSUSER_ROLE TO ogguser;

Grant succeeded.

Execute script-5:

SQL> @ddl_enable.sql

Trigger altered.

Execute script-6:

SQL> @ddl_pin.sql ogguser

PL/SQL procedure successfully completed.

Execute script-7:

SQL> @marker_status.sql

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to OGGUSER

MARKER TABLE

-------------------------------

MARKER SEQUENCE

-------------------------------

Execute script-8:

SQL> @ddl_status.sql

Please enter the name of a schema for the GoldenGate database objects:

Ogguser

Login Into target database server (12c)

[oracle@ggnode ~]$ source 12c.env

[oracle@ggnode ~]$ sqlplus / as sysdba

SQL> CREATE TABLE GGS_SETUP (PROPERTY VARCHAR2(100), VALUE VARCHAR2(4000));

Table created.

SQL> INSERT INTO GGS_SETUP VALUES ('DDL_TRACE_LEVEL', '0');

1 row created.

SQL> INSERT INTO GGS_SETUP VALUES ('DDL_SQL_TRACING', '0');

1 row created.

SQL> INSERT INTO GGS_SETUP VALUES ('_USEALLKEYS', '0');

1 row created.

SQL> INSERT INTO GGS_SETUP VALUES ('_LIMIT32K', '0');

1 row created.

SQL> INSERT INTO GGS_SETUP VALUES ('DDL_STAYMETADATA', 'OFF');

1 row created.

SQL> commit;

Commit complete.

Login Into Source Database Server (GGSCI Prompt)

[oracle@ggnode 11g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 00:55:59

GGSCI (ggnode.oracle.com) 1> dblogin userid ogguser, password oracle

Successfully logged into database.

GGSCI (ggnode.oracle.com) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORCL 00:00:00 00:00:03

EXTRACT RUNNING PORCL 00:00:00 00:00:07

Note: add this the below in EORCL extract process and create the directory “dirbr” under $GG directory

BR BRDIR ./dirbr BRINTERVAL 5M

GGSCI (ggnode.oracle.com) 11> view param eorcl

extract eorcl

exttrail ./dirdat/lt

userid ogguser, password oracle

BR BRDIR ./dirbr BRINTERVAL 5M

table scott.*;

[oracle@ggnode 11g]$ cat /u01/app/ogg/11g/ggserr.log

Got the following error:

2015-05-02 14:41:25 ERROR OGG-01386 Oracle GoldenGate Capture for Oracle, eorcl.prm: Value for BRINTERVAL must be between 20MINUTES and 96HOURS.

Changed from 5M to 20M in Bounded Recovery Time

GGSCI (ggnode.oracle.com) 11> view param eorcl

extract eorcl

exttrail ./dirdat/lt

userid ogguser, password oracle

BR BRDIR ./dirbr BRINTERVAL 20M

table scott.*;

SQL> connect scott/oracle@orcl

Connected.

SQL> create table myobjects

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23))

Table created.

SQL> insert into myobjects select object_id,object_name,object_type from dba_objects;

75264 rows created.

Checked archive log files generated or not in source database server

[oracle@ggnode 2015_05_02]$ pwd

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_05_02

[oracle@ggnode 2015_05_02]$ ls -lrth

total 66M

-rw-r----- 1 oracle oinstall 20M May 2 14:10 o1_mf_1_9_bn93d8wx_.arc

-rw-r----- 1 oracle oinstall 46M May 2 14:40 o1_mf_1_10_bn954kob_.arc

Note: Again created some of the objects in “scott” schema to generate archive log files

SQL> create table myobjects1

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23));

Table created.

SQL> insert into myobjects1 select object_id,object_name,object_type from dba_objects;

75265 rows created.

SQL> create table myobjects2

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23));

Table created.

SQL> insert into myobjects2 select object_id,object_name,object_type from dba_objects;

75266 rows created.

SQL> create table myobjects3

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23));

Table created.

SQL> insert into myobjects3 select object_id,object_name,object_type from dba_objects;

75267 rows created.

SQL> create table myobjects4

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23));

Table created.

SQL> insert into myobjects4 select object_id,object_name,object_type from dba_objects;

75268 rows created.

SQL> create table myobjects5

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23));

Table created.

SQL> insert into myobjects5 select object_id,object_name,object_type from dba_objects;

75269 rows created.

Note: “Commit” has not given in Source Database Server. Because of these tables archive log no : 11 generated

SQL> !

[oracle@ggnode 11g]$ cd /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_05_02

[oracle@ggnode 2015_05_02]$ ls -lrth

total 107M

-rw-r----- 1 oracle oinstall 20M May 2 14:10 o1_mf_1_9_bn93d8wx_.arc

-rw-r----- 1 oracle oinstall 46M May 2 14:40 o1_mf_1_10_bn954kob_.arc

-rw-r----- 1 oracle oinstall 42M May 2 15:21 o1_mf_1_11_bn97jjyk_.arc

Login Into RMAN from source database server and taken backup of archive log (11) and deleted from the disk

[oracle@ggnode 2015_05_02]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 15:21:39 2015

connected to target database: ORCL (DBID=1406171494)

RMAN> backup archivelog sequence 11 delete input;

Starting backup at 02-MAY-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=49 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=11 RECID=6 STAMP=878656875

channel ORA_DISK_1: starting piece 1 at 02-MAY-15

channel ORA_DISK_1: finished piece 1 at 02-MAY-15

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_05_02/o1_mf_annnn_TAG20150502T153639_bn98fj4q_.bkp tag=TAG20150502T153639 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_05_02/o1_mf_1_11_bn97jjyk_.arc RECID=6 STAMP=878656875

Finished backup at 02-MAY-15

RMAN> exit

SQL> commit;

Commit complete.

SQL> select count(*) from myobjects;

COUNT(*)

----------

75264

1 row selected.

SQL> select count(*) from myobjects1;

COUNT(*)

----------

75265

1 row selected.

SQL> select count(*) from myobjects2;

COUNT(*)

----------

75266

1 row selected.

SQL> select count(*) from myobjects3;

COUNT(*)

----------

75267

1 row selected.

SQL> select count(*) from myobjects4;

COUNT(*)

----------

75268

1 row selected.

SQL> select count(*) from myobjects5;

COUNT(*)

----------

75269

1 row selected.

Check the Bounded Recovery Directory in Oracle Golden Gate Home:

[oracle@ggnode EORCL]$ pwd

/u01/app/ogg/11g/dirbr/BR/EORCL

[oracle@ggnode EORCL]$ ls -lrth

total 20K

drwxrwxr-x 2 oracle oinstall 4.0K May 2 14:41 stale

-rw-rw---- 1 oracle oinstall 64K May 2 15:53 CP.EORCL.000000003

Note the trail files generated in source database server

[oracle@ggnode 11g]$ cd dirdat/

[oracle@ggnode dirdat]$ ls -lrth

total 67M

-rw-rw-rw- 1 oracle oinstall 1.2K May 2 14:13 lt000000

-rw-rw-rw- 1 oracle oinstall 1.1K May 2 14:44 lt000001

-rw-rw-rw- 1 oracle oinstall 1.1K May 2 14:53 lt000002

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:17 lt000003

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:17 lt000004

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:20 lt000006

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:20 lt000005

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:20 lt000008

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:20 lt000007

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:20 lt000009

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:20 lt000011

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:20 lt000010

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:21 lt000013

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:21 lt000012

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:37 lt000014

-rw-rw-rw- 1 oracle oinstall 4.8M May 2 15:37 lt000015

-rw-rw-rw- 1 oracle oinstall 4.7M May 2 15:37 lt000016

SQL> select count(*) from MYOBJECTS;

COUNT(*)

----------

75264

SQL> select count(*) from MYOBJECTS1;

COUNT(*)

----------

75265

SQL> select count(*) from MYOBJECTS2;

COUNT(*)

----------

75266

SQL> select count(*) from MYOBJECTS3;

COUNT(*)

----------

75267

SQL> select count(*) from MYOBJECTS4;

COUNT(*)

----------

75268

SQL> select count(*) from MYOBJECTS5;

COUNT(*)

----------

75269

[oracle@ggnode ~]$ source 12c.env

[oracle@ggnode ~]$ cd $GG

[oracle@ggnode 12c]$ cd dirdat/

[oracle@ggnode dirdat]$ ls -lrth

total 67M

-rw-r----- 1 oracle oinstall 1.3K May 2 14:14 rt000000

-rw-r----- 1 oracle oinstall 1.3K May 2 14:41 rt000001

-rw-r----- 1 oracle oinstall 1.3K May 2 14:53 rt000002

-rw-r----- 1 oracle oinstall 4.8M May 2 15:17 rt000003

-rw-r----- 1 oracle oinstall 4.8M May 2 15:17 rt000004

-rw-r----- 1 oracle oinstall 4.8M May 2 15:20 rt000006

-rw-r----- 1 oracle oinstall 4.8M May 2 15:20 rt000005

-rw-r----- 1 oracle oinstall 4.8M May 2 15:20 rt000008

-rw-r----- 1 oracle oinstall 4.8M May 2 15:20 rt000007

-rw-r----- 1 oracle oinstall 4.8M May 2 15:20 rt000010

-rw-r----- 1 oracle oinstall 4.8M May 2 15:20 rt000009

-rw-r----- 1 oracle oinstall 4.8M May 2 15:20 rt000011

-rw-r----- 1 oracle oinstall 4.8M May 2 15:21 rt000013

-rw-r----- 1 oracle oinstall 4.8M May 2 15:21 rt000012

-rw-r----- 1 oracle oinstall 4.8M May 2 15:37 rt000015

-rw-r----- 1 oracle oinstall 4.8M May 2 15:37 rt000014

-rw-r----- 1 oracle oinstall 4.7M May 2 15:37 rt000016

[oracle@ggnode dirdat]$ pwd

So, Tables and Data successfully transferred from source database server (11g) to target database server (12c) after deleting archive log files from the source database server. Data has moved because of Bounded Recovery (BR) functionality.

GGSCI (ggnode.oracle.com) 28> !

info eorcl showch

EXTRACT EORCL Last Started 2015-05-02 14:53 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Log Read Checkpoint Oracle Redo Logs

2015-05-02 15:37:40 Seqno 12, RBA 7660544

SCN 0.1174681 (1174681)

Current Checkpoint Detail:

Read Checkpoint #1

Oracle Redo Log

Startup Checkpoint (starting position in the data source):

Thread #: 1

Sequence #: 11

RBA: 8525328

Timestamp: 2015-05-02 14:53:02.000000

SCN: 0.1170390 (1170390)

Redo File: /u01/app/oracle/oradata/orcl/redo02.log

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Thread #: 1

Sequence #: 12

RBA: 7660048

Timestamp: 2015-05-02 15:37:40.000000

SCN: 0.1174681 (1174681)

Redo File: /u01/app/oracle/oradata/orcl/redo03.log

Current Checkpoint (position of last record read in the data source):

Thread #: 1

Sequence #: 12

RBA: 7660544

Timestamp: 2015-05-02 15:37:40.000000

SCN: 0.1174681 (1174681)

Redo File: /u01/app/oracle/oradata/orcl/redo03.log

BR Previous Recovery Checkpoint:

Thread #: 0

Sequence #: 0

RBA: 0

Timestamp: 2015-05-02 14:53:30.606674

SCN: Not available

Redo File:

BR Begin Recovery Checkpoint:

Thread #: 0

Sequence #: 11

RBA: 39571472

Timestamp: 2015-05-02 15:21:11.000000

SCN: 0.1173346 (1173346)

Redo File:

BR End Recovery Checkpoint:

Thread #: 1

Sequence #: 12

RBA: 7307776

Timestamp: 2015-05-02 15:33:10.000000

SCN: 0.1174151 (1174151)

Redo File:

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 16

RBA: 4878345

Timestamp: 2015-05-02 15:37:45.665800

Extract Trail: ./dirdat/lt

Header:

Version = 2

Record Source = A

Type = 10

# Input Checkpoints = 1

# Output Checkpoints = 1

File Information:

Block Size = 2048

Max Blocks = 100

Record Length = 2048

Current Offset = 0

Configuration:

Data Source = 3

Transaction Integrity = 1

Task Type = 0

Status:

Start Time = 2015-05-02 14:53:30

Last Update Time = 2015-05-02 15:37:45

Stop Status = A

Last Result = 400

GGSCI (ggnode.oracle.com) 29>

View the report of Extract (EORCL) from source database server

GGSCI (ggnode.oracle.com) 30> view report eorcl

***********************************************************************

Oracle GoldenGate Capture for Oracle

Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 01:04:34

Starting at 2015-05-02 14:53:30

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Wed Feb 22 17:37:40 EST 2012, Release 2.6.32-300.10.1.el5uek

Node: ggnode.oracle.com

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 5100

Description:

***********************************************************************

** Running with the following parameters **

***********************************************************************

2015-05-02 14:53:30 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

extract eorcl

exttrail ./dirdat/lt

userid ogguser, password ******

DDL INCLUDE ALL;

DDLOPTIONS ADDTRANDATA, REPORT

BR BRDIR ./dirbr BRINTERVAL 20M

table scott.*;

2015-05-02 14:53:30 INFO OGG-01815 Virtual Memory Facilities for: BR

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u01/app/ogg/11g/dirbr/BR/EORCL.

Bounded Recovery Parameter:

BRINTERVAL = 20M

BRDIR = ./dirbr

2015-05-02 14:53:30 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u01/app/ogg/11g/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 64G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 128G

CACHESIZEMAX (strict force to disk): 96G

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

Database Language and Character Set:

NLS_LANG = ".AL32UTF8"

NLS_LANGUAGE = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "AL32UTF8"

2015-05-02 14:53:30 WARNING OGG-01423 No valid default archive log destination directory found for thread 1.

2015-05-02 14:53:30 INFO OGG-01513 Positioning to Sequence 11, RBA 8525328, SCN 0.1170390.

2015-05-02 14:53:30 INFO OGG-01516 Positioned to Sequence 11, RBA 8525328, SCN 0.1170390, May 2, 2015 2:53:02 PM.

2015-05-02 14:53:30 INFO OGG-01055 Recovery initialization completed for target file ./dirdat/lt000002, at RBA 1098.

2015-05-02 14:53:30 INFO OGG-01478 Output file ./dirdat/lt is using format RELEASE 11.2.

2015-05-02 14:53:30 INFO OGG-01026 Rolling over remote file ./dirdat/lt000002.

2015-05-02 14:53:30 INFO OGG-01053 Recovery completed for target file ./dirdat/lt000003, at RBA 1098.

2015-05-02 14:53:30 INFO OGG-01057 Recovery completed for all targets.

***********************************************************************

** Run Time Messages **

***********************************************************************

2015-05-02 14:53:30 INFO OGG-01517 Position of first record processed Sequence 11, RBA 8525328, SCN 0.1170390, May 2, 2015 2:53:02 PM.

TABLE resolved (entry ogguser.GGS_MARKER):

TABLE "OGGUSER"."GGS_MARKER";

Using the following key columns for source table OGGUSER.GGS_MARKER: SEQNO, FRAGMENTNO, OPTIME.

2015-05-02 15:04:44 INFO OGG-01487 DDL found, operation [create table sample (no number) (size 32)], start SCN [1170966], commit SCN [1171028] instance [orcl (1)], DDL seqno [12], mar

ker seqno [12].

2015-05-02 15:04:44 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [SAMPLE].

2015-05-02 15:04:44 INFO OGG-00497 Writing DDL operation to extract trail file.

2015-05-02 15:04:46 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.SAMPLE], operation [ALTER TABLE "SCOTT"."SAMPLE" ADD SUPPLEMENTAL LOG GROUP "GGT_76941" ("NO") ALWAYS

/* GOLDENGATE_DDL_REPLICATION */ (size 115)].

2015-05-02 15:04:46 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.SAMPLE], operation [ALTER TABLE "SCOTT"."SAMPLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GOLDENGATE

_DDL_REPLICATION */ (size 102)].

2015-05-02 15:04:46 INFO OGG-00479 Successfully deleted TRAN DATA for [CREATE] DDL operation, table [SCOTT.SAMPLE], operation [ALTER TABLE "SCOTT"."SAMPLE" DROP SUPPLEMENTAL LOG GROUP

"GGT_76941" /* GOLDENGATE_DDL_REPLICATION */ (size 102)].

2015-05-02 15:06:49 INFO OGG-01487 DDL found, operation [create table myobjects

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23)) (size 215)], start SCN [1171618], commit SCN [1171633] instance [orcl (1)], DDL seqno [18], marker seqno [18].

2015-05-02 15:06:49 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [MYOBJECTS].

2015-05-02 15:06:49 INFO OGG-00497 Writing DDL operation to extract trail file.

2015-05-02 15:06:49 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS], operation [ALTER TABLE "SCOTT"."MYOBJECTS" ADD SUPPLEMENTAL LOG GROUP "GGT_76942" ("OBJECT_

ID","OBJECT_NAME","OBJECT_TYPE") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 153)].

2015-05-02 15:06:49 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS], operation [ALTER TABLE "SCOTT"."MYOBJECTS" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GOLD

ENGATE_DDL_REPLICATION */ (size 105)].

2015-05-02 15:06:49 INFO OGG-00479 Successfully deleted TRAN DATA for [CREATE] DDL operation, table [SCOTT.MYOBJECTS], operation [ALTER TABLE "SCOTT"."MYOBJECTS" DROP SUPPLEMENTAL LOG

GROUP "GGT_76942" /* GOLDENGATE_DDL_REPLICATION */ (size 105)].

2015-05-02 15:07:45 INFO OGG-01021 Command received from GGSCI: SHOWTRANS.

2015-05-02 15:13:39 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p5100_extr: start=SeqNo: 11, RBA: 13176336, SCN: 0.1171730 (1171730), Timestamp: 2015-05-02 15:06:58

.000000, end=SeqNo: 11, RBA: 17458176, SCN: 0.1172106 (1172106), Timestamp: 2015-05-02 15:13:09.000000, Thread: 1.

Wildcard TABLE resolved (entry scott.*):

table "SCOTT"."MYOBJECTS";

2015-05-02 15:17:22 WARNING OGG-00869 No unique key is defined for table 'MYOBJECTS'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may

be used to define the key.

Using the following key columns for source table SCOTT.MYOBJECTS: OBJECT_ID, OBJECT_NAME, OBJECT_TYPE.

2015-05-02 15:17:22 INFO OGG-01026 Rolling over remote file ./dirdat/lt000003.

2015-05-02 15:17:23 INFO OGG-01026 Rolling over remote file ./dirdat/lt000004.

2015-05-02 15:19:49 INFO OGG-01487 DDL found, operation [create table myobjects1

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23))

(size 217)], start SCN [1172326], commit SCN [1172341] instance [orcl (1)], DDL seqno [24], marker seqno [24].

2015-05-02 15:19:49 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [MYOBJECTS1].

2015-05-02 15:19:49 INFO OGG-00497 Writing DDL operation to extract trail file.

2015-05-02 15:19:49 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS1], operation [ALTER TABLE "SCOTT"."MYOBJECTS1" ADD SUPPLEMENTAL LOG GROUP "GGT_76943" ("OBJEC

T_ID","OBJECT_NAME","OBJECT_TYPE") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 154)].

2015-05-02 15:19:49 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS1], operation [ALTER TABLE "SCOTT"."MYOBJECTS1" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GO

LDENGATE_DDL_REPLICATION */ (size 106)].

2015-05-02 15:19:49 INFO OGG-00479 Successfully deleted TRAN DATA for [CREATE] DDL operation, table [SCOTT.MYOBJECTS1], operation [ALTER TABLE "SCOTT"."MYOBJECTS1" DROP SUPPLEMENTAL LO

G GROUP "GGT_76943" /* GOLDENGATE_DDL_REPLICATION */ (size 106)].

Wildcard TABLE resolved (entry scott.*):

table "SCOTT"."MYOBJECTS1";

2015-05-02 15:20:05 WARNING OGG-00869 No unique key is defined for table 'MYOBJECTS1'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may

be used to define the key.

Using the following key columns for source table SCOTT.MYOBJECTS1: OBJECT_ID, OBJECT_NAME, OBJECT_TYPE.

2015-05-02 15:20:05 INFO OGG-01026 Rolling over remote file ./dirdat/lt000005.

2015-05-02 15:20:05 INFO OGG-01026 Rolling over remote file ./dirdat/lt000006.

2015-05-02 15:20:05 INFO OGG-01487 DDL found, operation [create table myobjects2

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23))

(size 217)], start SCN [1172513], commit SCN [1172525] instance [orcl (1)], DDL seqno [30], marker seqno [30].

2015-05-02 15:20:05 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [MYOBJECTS2].

2015-05-02 15:20:05 INFO OGG-00497 Writing DDL operation to extract trail file.

2015-05-02 15:20:06 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS2], operation [ALTER TABLE "SCOTT"."MYOBJECTS2" ADD SUPPLEMENTAL LOG GROUP "GGT_76944" ("OBJEC

T_ID","OBJECT_NAME","OBJECT_TYPE") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 154)].

2015-05-02 15:20:06 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS2], operation [ALTER TABLE "SCOTT"."MYOBJECTS2" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GO

LDENGATE_DDL_REPLICATION */ (size 106)].

2015-05-02 15:20:06 INFO OGG-00479 Successfully deleted TRAN DATA for [CREATE] DDL operation, table [SCOTT.MYOBJECTS2], operation [ALTER TABLE "SCOTT"."MYOBJECTS2" DROP SUPPLEMENTAL LO

G GROUP "GGT_76944" /* GOLDENGATE_DDL_REPLICATION */ (size 106)].

Wildcard TABLE resolved (entry scott.*):

table "SCOTT"."MYOBJECTS2";

2015-05-02 15:20:26 WARNING OGG-00869 No unique key is defined for table 'MYOBJECTS2'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may

be used to define the key.

Using the following key columns for source table SCOTT.MYOBJECTS2: OBJECT_ID, OBJECT_NAME, OBJECT_TYPE.

2015-05-02 15:20:26 INFO OGG-01026 Rolling over remote file ./dirdat/lt000007.

2015-05-02 15:20:26 INFO OGG-01026 Rolling over remote file ./dirdat/lt000008.

2015-05-02 15:20:27 INFO OGG-01487 DDL found, operation [create table myobjects3

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23)) (size 216)], start SCN [1172875], commit SCN [1172889] instance [orcl (1)], DDL seqno [36], marker seqno [36].

2015-05-02 15:20:27 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [MYOBJECTS3].

2015-05-02 15:20:27 INFO OGG-00497 Writing DDL operation to extract trail file.

2015-05-02 15:20:27 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS3], operation [ALTER TABLE "SCOTT"."MYOBJECTS3" ADD SUPPLEMENTAL LOG GROUP "GGT_76945" ("OBJEC

T_ID","OBJECT_NAME","OBJECT_TYPE") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 154)].

2015-05-02 15:20:27 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS3], operation [ALTER TABLE "SCOTT"."MYOBJECTS3" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GO

LDENGATE_DDL_REPLICATION */ (size 106)].

2015-05-02 15:20:27 INFO OGG-00479 Successfully deleted TRAN DATA for [CREATE] DDL operation, table [SCOTT.MYOBJECTS3], operation [ALTER TABLE "SCOTT"."MYOBJECTS3" DROP SUPPLEMENTAL LO

G GROUP "GGT_76945" /* GOLDENGATE_DDL_REPLICATION */ (size 106)].

Wildcard TABLE resolved (entry scott.*):

table "SCOTT"."MYOBJECTS3";

2015-05-02 15:20:46 WARNING OGG-00869 No unique key is defined for table 'MYOBJECTS3'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may

be used to define the key.

Using the following key columns for source table SCOTT.MYOBJECTS3: OBJECT_ID, OBJECT_NAME, OBJECT_TYPE.

2015-05-02 15:20:46 INFO OGG-01026 Rolling over remote file ./dirdat/lt000009.

2015-05-02 15:20:47 INFO OGG-01026 Rolling over remote file ./dirdat/lt000010.

2015-05-02 15:20:47 INFO OGG-01026 Rolling over remote file ./dirdat/lt000011.

2015-05-02 15:20:48 INFO OGG-01487 DDL found, operation [create table myobjects4

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23)) (size 216)], start SCN [1173046], commit SCN [1173060] instance [orcl (1)], DDL seqno [42], marker seqno [42].

2015-05-02 15:20:48 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [MYOBJECTS4].

2015-05-02 15:20:48 INFO OGG-00497 Writing DDL operation to extract trail file.

2015-05-02 15:20:48 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS4], operation [ALTER TABLE "SCOTT"."MYOBJECTS4" ADD SUPPLEMENTAL LOG GROUP "GGT_76946" ("OBJEC

T_ID","OBJECT_NAME","OBJECT_TYPE") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 154)].

2015-05-02 15:20:48 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS4], operation [ALTER TABLE "SCOTT"."MYOBJECTS4" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GO

LDENGATE_DDL_REPLICATION */ (size 106)].

2015-05-02 15:20:49 INFO OGG-00479 Successfully deleted TRAN DATA for [CREATE] DDL operation, table [SCOTT.MYOBJECTS4], operation [ALTER TABLE "SCOTT"."MYOBJECTS4" DROP SUPPLEMENTAL LO

G GROUP "GGT_76946" /* GOLDENGATE_DDL_REPLICATION */ (size 106)].

Wildcard TABLE resolved (entry scott.*):

table "SCOTT"."MYOBJECTS4";

2015-05-02 15:21:06 WARNING OGG-00869 No unique key is defined for table 'MYOBJECTS4'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may

be used to define the key.

Using the following key columns for source table SCOTT.MYOBJECTS4: OBJECT_ID, OBJECT_NAME, OBJECT_TYPE.

2015-05-02 15:21:06 INFO OGG-01026 Rolling over remote file ./dirdat/lt000012.

2015-05-02 15:21:06 INFO OGG-01026 Rolling over remote file ./dirdat/lt000013.

2015-05-02 15:21:07 INFO OGG-01487 DDL found, operation [create table myobjects5

(

OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(128),

OBJECT_TYPE VARCHAR2(23)) (size 216)], start SCN [1173226], commit SCN [1173242] instance [orcl (1)], DDL seqno [48], marker seqno [48].

2015-05-02 15:21:07 INFO OGG-00487 DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [MYOBJECTS5].

2015-05-02 15:21:07 INFO OGG-00497 Writing DDL operation to extract trail file.

2015-05-02 15:21:07 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS5], operation [ALTER TABLE "SCOTT"."MYOBJECTS5" ADD SUPPLEMENTAL LOG GROUP "GGT_76947" ("OBJEC

T_ID","OBJECT_NAME","OBJECT_TYPE") ALWAYS /* GOLDENGATE_DDL_REPLICATION */ (size 154)].

2015-05-02 15:21:07 INFO OGG-00477 Successfully added TRAN DATA for table [SCOTT.MYOBJECTS5], operation [ALTER TABLE "SCOTT"."MYOBJECTS5" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS /* GO

LDENGATE_DDL_REPLICATION */ (size 106)].

2015-05-02 15:21:07 INFO OGG-00479 Successfully deleted TRAN DATA for [CREATE] DDL operation, table [SCOTT.MYOBJECTS5], operation [ALTER TABLE "SCOTT"."MYOBJECTS5" DROP SUPPLEMENTAL LO

G GROUP "GGT_76947" /* GOLDENGATE_DDL_REPLICATION */ (size 106)].

2015-05-02 15:33:46 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p5100_extr: start=SeqNo: 11, RBA: 39571472, SCN: 0.1173346 (1173346), Timestamp: 2015-05-02 15:21:11

.000000, end=SeqNo: 12, RBA: 7307776, SCN: 0.1174151 (1174151), Timestamp: 2015-05-02 15:33:10.000000, Thread: 1.

Wildcard TABLE resolved (entry scott.*):

table "SCOTT"."MYOBJECTS5";

2015-05-02 15:37:18 WARNING OGG-00869 No unique key is defined for table 'MYOBJECTS5'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may

be used to define the key.

Using the following key columns for source table SCOTT.MYOBJECTS5: OBJECT_ID, OBJECT_NAME, OBJECT_TYPE.

2015-05-02 15:37:18 INFO OGG-01026 Rolling over remote file ./dirdat/lt000014.

2015-05-02 15:37:19 INFO OGG-01026 Rolling over remote file ./dirdat/lt000015.

GGSCI (ggnode.oracle.com) 31>

Reflection of my thoughts and technical expriences

Tuesday, May 5, 2015

Bounded Recovery In Oracle Golden Gate 12c

1 comment: