GoldenGate Integrated Extract Upgrade

Oracle GoldenGate: Compressed tables are not supported until v11.2.0.3 Integrated Extract. [ID 1266389.1]

11.2.0.3 Database specific bundle patch for Integrated Extract 11.2.x [ID 1411356.1]

One reason to upgrade to integrate extract is to support compression. Oracle database must be as least 11.2.0.3 and Patch 13560925 applied.

GGSCI 1> DBLOGIN USERID gguser, PASSWORD AADAAAAAAAAAAA, ENCRYPTKEY key1

Successfully logged into database.

GGSCI 2> INFO ALL

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING PHA02 00:00:00 00:00:04
EXTRACT RUNNING XHA02 00:00:00 00:00:06
REPLICAT RUNNING RHA02 01:08:54 00:00:00

GGSCI 3> INFO *

EXTRACT PHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/xb000002
2012-06-26 14:44:47.060732 RBA 1056

EXTRACT XHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2012-06-26 16:05:41 Seqno 31, RBA 149897728
SCN 0.506201 (506201)

REPLICAT RHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 01:08:56 (updated 00:00:00 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/pa000006
2012-06-26 14:56:55.999661 RBA 484181887

GGSCI 4> STOP EXTRACT XHA02

Sending STOP request to EXTRACT XHA02 …
Request processed.

GGSCI 5> REGISTER EXTRACT XHA02 DATABASE

2012-06-26 16:07:38 INFO OGG-02003 Extract XHA02 successfully registered with database at SCN 511623.

GGSCI 6> ALTER EXTRACT XHA02, UPGRADE INTEGRATED TRANLOG
ERROR: Extract XHA02 is not ready to be upgraded because recovery SCN 509446 has not reached SCN 511623.

GGSCI 7> INFO *

EXTRACT PHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/xb000002
2012-06-26 14:44:47.060732 RBA 1056

EXTRACT XHA02 Last Started 2012-06-26 14:44 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:40 ago)
Log Read Checkpoint Oracle Redo Logs
2012-06-26 16:06:41 Seqno 31, RBA 220454400
SCN 0.509446 (509446)

REPLICAT RHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 01:11:15 (updated 00:00:00 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/pa000007
2012-06-26 14:57:07.908678 RBA 77768545

SWITCH SEVERAL LOGFILES AND WENT HOME :=)

GGSCI 13> START EXTRACT XHA02

Sending START request to MANAGER …
EXTRACT XHA02 starting

GGSCI 14> INFO *

EXTRACT PHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/xb000003
2012-06-26 17:29:09.247516 RBA 1056

EXTRACT XHA02 Last Started 2012-06-26 17:29 Status RUNNING
Checkpoint Lag 01:18:50 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2012-06-26 16:10:29 Seqno 36, RBA 38589788
SCN 0.535937 (535937)

REPLICAT RHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/pa000008
2012-06-26 15:58:18.055952 RBA 484588045

NOTICE TIME DIFFERENCE

GGSCI 15> STOP EXTRACT XHA02

Sending STOP request to EXTRACT XHA02 …
Request processed.

GGSCI 16> ALTER EXTRACT XHA02, UPGRADE INTEGRATED TRANLOG
Extract XHA02 successfully upgraded to integrated capture.

GGSCI 17> START EXTRACT XHA02

Sending START request to MANAGER …
EXTRACT XHA02 starting

GGSCI 18> INFO *

EXTRACT PHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/xb000003
2012-06-26 17:29:09.247516 RBA 1056

EXTRACT XHA02 Initialized 2012-06-26 17:29 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:16 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2012-06-26 17:29:23
SCN 0.623184 (623184)

REPLICAT RHA02 Last Started 2012-06-26 14:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/pa000008
2012-06-26 15:58:18.055952 RBA 484588045

GGSCI 19>

Think Different

I am a PC, BUT

I like to Think Different

It’s ironic since the first few English words I learned when arriving in the U.S were “Hello, Thank you, and Apple”.

In grade school, children are taught A is for Apple. Isn’t it genius?

For obtaining straight A’s, my daughter was introduced to a brand new Apple MacBook PRO.

 

 

 

 

 

 

 

 

I don’t remember what B or C is for, was it B for ball and C for cat?

Would it be better if  B and C are for – “Belling the Cat”, from Aesop’s Fables?

pldd – list dynamic libraries linked into each process

I am using ldd and pldd on Solaris 10.

I was upgrading the database using dbua and was distracted. Later, I saw the listener process was running from 11.2.0.2

> ps -afe|grep tns
 oracle 26639 6520 0 Jun 04 ? 0:01 /u01/app/oracle/product/11.2.0.2/dbhome_1/bin/tnslsnr listener_db02 -inherit
 oracle 6963 7420 0 12:09:09 pts/3 0:00 grep tns

Strange – Hmm?  What happened and what version is oracle process running?

> ps -afe|grep ora_smon
 oracle 4563 6520 0 10:47:37 ? 0:02 ora_smon_db02
 oracle 7012 7420 0 12:09:19 pts/3 0:00 grep ora_smon
> pldd 4563
4563: ora_smon_db02
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libodmd11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libcell11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxp11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnnz11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclsra11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libdbcfg11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libhasgen11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxn2.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocr11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrb11.so
/u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrutl11.so
/lib/sparcv9/libkstat.so.1
/lib/sparcv9/libnsl.so.1
/lib/sparcv9/libsocket.so.1
/lib/sparcv9/libresolv.so.2
/lib/sparcv9/libgen.so.1
/lib/sparcv9/libdl.so.1
/usr/lib/sparcv9/libsched.so.1
/lib/sparcv9/librt.so.1
/lib/sparcv9/libc.so.1
/lib/sparcv9/libaio.so.1
/usr/lib/sparcv9/libpool.so.1
/lib/sparcv9/libm.so.2
/lib/sparcv9/libmd.so.1
/usr/lib/sparcv9/libxml2.so.2
/lib/sparcv9/libscf.so.1
/lib/sparcv9/libnvpair.so.1
/usr/lib/sparcv9/libexacct.so.1
/lib/sparcv9/libpthread.so.1
/usr/lib/sparcv9/libz.so.1
/lib/sparcv9/libdoor.so.1
/lib/sparcv9/libuutil.so.1
/platform/sun4u-us3/lib/sparcv9/libc_psr.so.1

I have must have not shutdown the listener from the previous version.

That was easy.

 

 

 

Missing sql_id from Active Session

Coskan has a very nice post: >> Where is the sql_id of active session?

Tanel has a nice post: >> What the heck is the SQL Execution ID – SQL_EXEC_ID?

>> V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed.

Just another method.

SQL> @snapper ash=user+program+sqlid+child+event 5 1 200
Sampling SID 200 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

---------------------------------------------------------------------------------------------------------------------
Active% | USERNAME             | PROGRAM                   | SQL_ID          | SQL_CHILD_ | EVENT
---------------------------------------------------------------------------------------------------------------------
   100% | XXXXX_WH             | sqlplus@prodrpt02 (TNS V1 |                 |            | ON CPU

--  End of ASH snap 1, end=2012-05-31 10:44:31, seconds=5, samples_taken=50

PL/SQL procedure successfully completed.

SQL> select sql_id, cursor_type from v$open_cursor where sid = 200 and sql_exec_id is not null;

SQL_ID        CURSOR_TYPE
------------- ----------------------------------------------------------------
fht2wtmnn0ru4 OPEN

SQL> @sqlid fht2wtmnn0ru4
Show SQL text, child cursors and execution stats for SQLID fht2wtmnn0ru4 child 5

no rows selected

no rows selected

SQL> @sqlid fht2wtmnn0ru4 0
Show SQL text, child cursors and execution stats for SQLID fht2wtmnn0ru4 child 0

HASH_VALUE  CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
3913310020    0 SELECT /*+ monitor */ /* sqlt_s79277 */    

 CH# PARENT_HANDLE    OBJECT_HANDLE     PLAN_HASH     PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED     CPU_MS     ELA_MS       LIOS       PIOS      SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
   0 00000005BA44C990 0000000397B44478 2413951034          1          1          1          1              1    5004440 5029771.22   19020425      48760          0               1

SQL>