Default Oracle GoldenGate Software installation (same binary file, directories, users, etc, etc, etc).
Necessary Database Patch:
Apply: 11.2.0.3 Database specific bundle patch for Integrated Extract 11.2.x (Doc ID 1411356.1)
Oracle Goldengate Configuration:
connect to goldengate/database source
dblogin USERID oggadm1@db01, Password "oggadm1"
Set Manager
edit mgr
Port 7809
PurgeOldExtracts ./dirdat/*, UseCheckpoints
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART ER t*
AUTORESTART ER t*, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
Register the Extract Group
GGSCI (lnxdb01.localdomain) 12> REGISTER EXTRACT extbt DATABASE
2014-01-14 21:59:10 INFO OGG-02003 Extract EXTBT successfully registered with database at SCN 1288511.
Add Extract Process
GGSCI (lnxdb01.localdomain) 13> ADD EXTRACT extbt INTEGRATED TRANLOG, BEGIN NOW
EXTRACT added.
Add Extract Trail
GGSCI (lnxdb01.localdomain) 14> ADD EXTTRAIL /u02/ogg01/dirdat/bt, EXTRACT extbt
EXTTRAIL added.
Add Data Pump Process
GGSCI (lnxdb01.localdomain) 15> ADD EXTRACT dpbt EXTTRAILSOURCE /u02/ogg01/dirdat/bt
EXTRACT added.
Add Data Pump Trail
GGSCI (lnxdb01.localdomain) 16> ADD RMTTRAIL /u02/ogg02/dirdat/tp, EXTRACT dpbt
RMTTRAIL added.
Verification
GGSCI (lnxdb01.localdomain) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED DPBT 00:00:00 00:01:18
EXTRACT STOPPED EXTBT 00:00:00 00:04:26
GGSCI (lnxdb01.localdomain) 18> info extract *
EXTRACT DPBT Initialized 2014-01-14 22:03 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:55 ago)
Log Read Checkpoint File /u02/ogg01/dirdat/bt000000
First Record RBA 0
EXTRACT EXTBT Initialized 2014-01-14 22:00 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:05:03 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2014-01-14 22:00:31
SCN 0.0 (0)
Set Extract Parameters
edit param extbt
EXTRACT extbt
USERID oggadm1@db01, Password "oggadm1"
TRANLOGOPTIONS INTEGRATED PARAMS (MAX_SGA_SIZE 100)
EXTTRAIL /u02/ogg01/dirdat/bt
TABLE breno.tstbt;
Set Data Pump Parameters
edit param dpbt
EXTRACT dpbt
USERID oggadm1@db01, Password "oggadm1"
RMTHOST lnxdb02, MGRPORT 7809
RMTTRAIL /u02/ogg02/dirdat/tp
TABLE breno.tstbt;
Set trandata for user schema
GGSCI (lnxdb01.localdomain) 22> Add SchemaTranData breno
2014-01-14 22:25:08 INFO OGG-01788 SCHEMATRANDATA has been added on schema breno.
GGSCI (lnxdb01.localdomain) 23> list table breno.*
BRENO.TSTBT
Found 1 tables matching list criteria.
GGSCI (lnxdb01.localdomain) 24>
connect to goldengate/database target
Set Manager
edit mgr
Port 7809
PurgeOldExtracts ./dirdat/*, UseCheckpoints
add replicat
GGSCI (lnxdb02.localdomain) 1> dblogin USERID oggadm2@db02, Password "oggadm2"
Successfully logged into database.
GGSCI (lnxdb02.localdomain) 2> ADD REPLICAT repbt, EXTTRAIL /u02/ogg02/dirdat/tp
REPLICAT added.
set Replicat Parameters
edit param repbt
REPLICAT repbt
USERID oggadm2@db02, Password "oggadm2"
ASSUMETARGETDEFS
DiscardFile ./dirrpt/rptbt.dsc, Append
MAP breno.tstbt TARGET breno.tstbt;
Start Goldengate Extract (goldengate source)
[oracle@lnxdb01 ogg01]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_
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 (lnxdb01.localdomain) 1> dblogin USERID oggadm1@db01, Password "oggadm1"
Successfully logged into database.
GGSCI (lnxdb01.localdomain) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPBT 00:00:00 00:00:18
EXTRACT STOPPED EXTBT 00:00:00 00:00:14
GGSCI (lnxdb01.localdomain) 3> start extract EXTBT
Sending START request to MANAGER ...
EXTRACT EXTBT starting
GGSCI (lnxdb01.localdomain) 4> start extract DPBT
Sending START request to MANAGER ...
EXTRACT DPBT starting
GGSCI (lnxdb01.localdomain) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPBT 00:00:00 00:00:02
EXTRACT RUNNING EXTBT 00:00:00 00:00:05
GGSCI (lnxdb01.localdomain) 6>
Start Goldengate replicat(goldengate target)
GGSCI (lnxdb02.localdomain) 8> start replicat repbt
Sending START request to MANAGER ...
REPLICAT REPBT starting
GGSCI (lnxdb02.localdomain) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPBT 00:00:00 00:00:01
GGSCI (lnxdb02.localdomain) 10>
Tests
SQL> create user breno identified by breno ;
User created.
SQL> grant dba, connect, resource, create session to breno;
Grant succeeded.
SQL> conn breno/breno
Connected.
SQL>
SQL> create table tstbt (id number, name varchar2(100));
Table created.
SQL> alter table tstbt ADD CONSTRAINT tstbt_pk PRIMARY KEY (id);
Table altered.
SQL>
SQL>
SQL> conn breno/breno
Connected.
SQL> insert into tstbt values (1, 'test breno 1');
1 row created.
SQL> commit;
Commit complete.
SQL>
[oracle@lnxdb02 dirdat]$ sqlplus breno/breno
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 14 22:50:57 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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> set pages 5000 lin 5000
SQL> col name for a30
SQL> select * from tstbt;
ID NAME
---------- ------------------------------
1 test breno 1
SQL>
SQL> select table_name, LOGGING, COMPRESSION, COMPRESS_FOR from user_tables;
TABLE_NAME LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TSTBT YES DISABLED
SQL> alter table TSTBT compress for oltp;
Table altered.
SQL>
SQL> insert into TSTBT values (2, 'test compress');
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name, LOGGING, COMPRESSION, COMPRESS_FOR from user_tables;
TABLE_NAME LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TSTBT YES ENABLED OLTP
SQL> insert into TSTBT values (3, 'test after compress source');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into TSTBT values (4, 'test after compress target');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
[oracle@lnxdb02 dirdat]$ sqlplus breno/breno
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 14 22:50:57 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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> set pages 5000 lin 5000
SQL> col name for a30
SQL> select * from tstbt;
ID NAME
---------- ------------------------------
1 test breno 1
SQL> /
ID NAME
---------- ------------------------------
1 test breno 1
2 test compress
SQL> select table_name, LOGGING, COMPRESSION, COMPRESS_FOR from user_tables;
TABLE_NAME LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TSTBT YES DISABLED
SQL> select * from tstbt;
ID NAME
---------- ------------------------------
1 test breno 1
2 test compress
SQL> select * from tstbt;
ID NAME
---------- ------------------------------
1 test breno 1
2 test compress
3 test after compress source
SQL> alter table TSTBT COMPRESS FOR OLTP;
Table altered.
SQL> select * from tstbt;
ID NAME
---------- ------------------------------
1 test breno 1
2 test compress
3 test after compress source
4 test after compress target
SQL>
GGSCI (lnxdb01.localdomain) 6> info extract *
EXTRACT DPBT Last Started 2014-01-14 22:43 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Log Read Checkpoint File /u02/ogg01/dirdat/bt000001
2014-01-14 23:37:29.000000 RBA 1745
EXTRACT EXTBT Last Started 2014-01-14 22:43 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2014-01-14 23:40:02
SCN 0.1336226 (1336226)
GGSCI (lnxdb01.localdomain) 7>
GGSCI (lnxdb01.localdomain) 8> info RMTTRAIL *
Extract Trail: /u02/ogg02/dirdat/tp
Extract: DPBT
Seqno: 0
RBA: 1789
File Size: 100M
Extract Trail: /u02/ogg01/dirdat/bt
Extract: EXTBT
Seqno: 1
RBA: 1745
File Size: 100M
GGSCI (lnxdb01.localdomain) 9>
-- Scripts by Oracle
-- GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script (Doc ID 1448324.1)
-- ------------------------------
-- Summary of GoldenGate Integrated Extracts configured in database
-- ------------------------------
set lines 180
col extract_name format a8 heading 'Extract|Name'
col capture_name format a20 heading 'Capture|Name'
col capture_type format a10 heading 'Capture|Type'
col real_time_mine format a8 heading 'RealTime|Mine?'
col protocol format a8 heading 'OGG|Capture|Protocol'
col status Heading 'Status'
col state Heading 'Current|Capture|State'
col capture_user format a12 Heading 'Capture|User'
col inst_id Heading 'Instance'
col version format a12 Heading 'Capture|Version'
col startup_time heading 'Process|Startup|Time'
col mined_MB Heading 'Redo|Mined|MB'
col sent_MB Heading 'Sent to|Extract|Mb'
col STATE_CHANGED_TIME Heading 'Last |State Changed|Time'
col Current_time Heading 'Current|Time'
col capture_lag Heading 'Capture|Lag|seconds'
select SYSDATE Current_time,substr(c.capture_
c.capture_name,
c.capture_user,
c.capture_type,
decode(cp.value,'N','NO', 'YES') Real_time_mine,
c.version,
decode(bitand(xc.flags_
c.logminer_id,
c.status,
DECODE (g.STATE,null,' WAITING FOR CLIENT REQUESTS ',
'PAUSED FOR FLOW CONTROL', ' WAITING FOR CLIENT REQUESTS ',
'WAITING FOR INACTIVE DEQUEUERS',''||g.
g.state) State,
(SYSDATE- g.capture_message_create_time)
g.bytes_of_redo_mined/1024/
g.startup_time,
g.inst_id
from dba_capture c,
x$knstcap xc,
gv$goldengate_capture g,
dba_capture_parameters cp
where
c.capture_name=xc.capname_
and c.capture_name=cp.capture_name and cp.parameter='DOWNSTREAM_REAL_
and c.status='ENABLED'
union all
select SYSDATE Current_time, substr(c.capture_name,9,8) extract_name,
c.capture_name,
c.capture_user,
c.capture_type,
decode(cp.value, 'N','NO', 'YES') Real_time_mine,
c.version,
'Unavailable',
c.logminer_id,
c.status,
'Unavailable',
NULL,
NULL,
NULL,
NULL
from dba_capture c,
dba_capture_parameters cp
where
c.status in ('DISABLED','ABORTED') and c.purpose='GoldenGate Capture'
and c.capture_name=cp.capture_name and cp.parameter='DOWNSTREAM_REAL_
order by extract_name;
Current Extract Capture Capture Capture RealTime Capture Capture
Time Name Name User Type Mine? Version Protocol LOGMINER_ID Status
--------- -------- -------------------- ------------ ---------- -------- ------------ -------- ----------- --------
Current
Capture
State
------------------------------
Capture Redo Process
Lag Mined Startup
seconds MB Time Instance
---------- ---------- --------- ----------
15-JAN-14 EXTBT OGG$CAP_EXTBT OGGADM1 LOCAL YES 11.2.0.3.0 V1
>
WAITING FOR TRANSACTION
1 18.6597862 14-JAN-14 1
SQL>
-- ------------------------------
-- Integrated Extract key parameters
-- ------------------------------
col parallelism format a20
col max_sga_size format a12
col excludetag format a20
col excludeuser format a20
col getapplops format a10
col getreplicates format a13
col checkpoint_frequency format a20
select cp.capture_name,substr(cp.
max(case when parameter='PARALLELISM' then value end) parallelism
,max(case when parameter='MAX_SGA_SIZE' then value end) max_sga_size
,max(case when parameter='EXCLUDETAG' then value end) excludetag
,max(case when parameter='EXCLUDEUSER' then value end) excludeuser
,max(case when parameter='GETAPPLOPS' then value end) getapplops
,max(case when parameter='GETREPLICATES' then value end) getreplicates
,max(case when parameter='_CHECKPOINT_
from dba_capture_parameters cp, dba_capture c where c.capture_name=cp.capture_name
and c.purpose='GoldenGate Capture'
group by cp.capture_name;
Capture Extract
Name Name PARALLELISM MAX_SGA_SIZE EXCLUDETAG EXCLUDEUSER GETAPPLOPS GETREPLICATES CHECKPOINT_FREQUENCY
-------------------- -------- -------------------- ------------ -------------------- -------------------- ---------- ------------- --------------------
OGG$CAP_EXTBT EXTBT 2 100 Y N
SQL>
-- ------------------------------
-- Integrated Extract Logminer session info
-- ------------------------------
SQL> col session_name Heading 'Capture|Name'
SQL> col available_txn Heading 'Available|Chunks'
SQL> col delivered_txn Heading 'Delivered|Chunks'
SQL> col difference Heading 'Ready to Send|Chunks'
SQL> col builder_work_size Heading 'Builder|WorkSize'
SQL> col prepared_work_size Heading 'Prepared|WorkSize'
SQL> col used_memory_size Heading 'Used|Memory'
SQL> col max_memory_size Heading 'Max|Memory'
SQL>
SQL> select session_name, available_txn, delivered_txn,
2 available_txn-delivered_txn as difference,
3 builder_work_size, prepared_work_size,
4 used_memory_size , max_memory_size
FROM v$logmnr_session order by session_name;
5
Capture Available Delivered Ready to Send Builder Prepared Used Max
Name Chunks Chunks Chunks WorkSize WorkSize Memory Memory
------------------------------
OGG$CAP_EXTBT 0 0 0 0 0 6486184 69206016
SQL>
-- ------------------------------
-- LOGMINER STATISTICS
-- ------------------------------
SQL> COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A32
SQL> COLUMN NAME HEADING 'Statistic' FORMAT A32
SQL> COLUMN VALUE HEADING 'Value' FORMAT 9999999999999999
SQL>
SQL> select c.capture_name, name, value from gv$goldengate_capture c, gv$logmnr_stats l
2 where c.logminer_id = l.session_id
3 and name in ('bytes paged out', 'pageout time (seconds)',
4 'bytes of redo mined', 'bytes checkpointed',
5 'checkpoint time (seconds)',
6 'resume from low memory', 'distinct txns in queue'
7 )
8 order by 1,2;
Capture
Name Statistic Value
------------------------------
OGG$CAP_EXTBT bytes checkpointed 0
OGG$CAP_EXTBT bytes of redo mined 19706692
OGG$CAP_EXTBT bytes paged out 0
OGG$CAP_EXTBT checkpoint time (seconds) 0
OGG$CAP_EXTBT distinct txns in queue 0
OGG$CAP_EXTBT pageout time (seconds) 0
6 rows selected.
Patchs Applied
[oracle@lnxdb01 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.
------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.
Interim patches (2) :
Patch 16764932 : applied on Fri Jan 10 16:27:27 GMT-03:00 2014
Unique Patch ID: 16322428
Created on 24 May 2013, 15:53:54 hrs PST8PDT
Bugs fixed:
14692460, 13499412, 13516727, 16238044, 13652437, 14653968, 13794550
15947884, 13569591, 13615767, 14744263, 12977562, 14050233, 12905053
14393728, 13328193, 14175146, 13853654
This patch overlays patches:
16056266
This patch needs patches:
16056266
as prerequisites
Patch 16056266 : applied on Fri Jan 10 16:22:04 GMT-03:00 2014
Unique Patch ID: 15962803
Patch description: "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Created on 12 Mar 2013, 02:14:47 hrs PST8PDT
Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
13566938, 13593999, 10350832, 14138130, 12919564, 13561951, 13624984
13588248, 13080778, 13914613, 13804294, 14258925, 12873183, 13645875
14472647, 12880299, 14664355, 14409183, 12998795, 14469008, 13719081
13492735, 13496884, 12857027, 14263036, 14263073, 13732226, 13742433
16368108, 16314469, 12905058, 13742434, 12849688, 12950644, 13742435
13464002, 13534412, 12879027, 13958038, 14613900, 12585543, 12535346
12588744, 11877623, 13786142, 12847466, 13649031, 13981051, 12582664
12797765, 14262913, 12923168, 13384182, 13612575, 13466801, 13484963
14207163, 11063191, 13772618, 13070939, 12797420, 13041324, 16314467
16314468, 12976376, 11708510, 13680405, 14589750, 13026410, 13742437
13737746, 14644185, 13742438, 13326736, 13596521, 13001379, 16344871
13099577, 9873405, 14275605, 13742436, 9858539, 14841812, 11715084
16231699, 14040433, 12662040, 9703627, 12617123, 12845115, 12764337
13354082, 14459552, 13397104, 13913630, 12964067, 12983611, 13550185
13810393, 12780983, 12583611, 14546575, 13476583, 15862016, 11840910
13903046, 15862017, 13572659, 16294378, 13718279, 14088346, 13657605
13448206, 16314466, 14480676, 13419660, 13632717, 14063281, 14110275
13430938, 13467683, 13420224, 13812031, 14548763, 16299830, 12646784
13616375, 14035825, 12861463, 12834027, 15862021, 13632809, 13377816
13036331, 14727310, 13685544, 15862018, 13499128, 16175381, 13584130
12829021, 15862019, 12794305, 14546673, 12791981, 13787482, 13503598
10133521, 12718090, 13399435, 14023636, 13860201, 12401111, 13257247
13362079, 14176879, 12917230, 13923374, 14220725, 14480675, 13524899
13559697, 9706792, 14480674, 13916709, 13098318, 13773133, 14076523
13340388, 13366202, 13528551, 12894807, 13454210, 13343438, 12748240
14205448, 13385346, 15853081, 14273397, 12971775, 13582702, 10242202
13035804, 13544396, 16382353, 8547978, 14226599, 14062795, 13035360
12693626, 13332439, 14038787, 14062796, 12913474, 14841409, 14390252
16314470, 13370330, 13059165, 14062797, 14062794, 12959852, 13358781
12345082, 12960925, 9659614, 13699124, 14546638, 13936424, 13338048
12938841, 12658411, 12620823, 12656535, 14062793, 12678920, 13038684
14062792, 13807411, 13250244, 12594032, 15862022, 9761357, 12612118
13742464, 14052474, 13911821, 13457582, 13527323, 15862020, 13910420
13502183, 12780098, 13705338, 13696216, 14841558, 10263668, 15862023
16056266, 15862024, 13554409, 13645917, 13103913, 13011409, 14063280
------------------------------
OPatch succeeded.
Nenhum comentário:
Postar um comentário