Oracle

Friday, January 30, 2015

OGG-01028 unexpected LOB record or record fragment

My replicat REP1 abended with the below error for SCHEMA.TEST table

ERROR OGG-01028 unexpected LOB record or record fragment in /gg/dirdat/VC/VC000079, rba 1367.

Cause: Replicat altered to start on trail where the first record is a LOB and NOT the base record.

Solution: Start the Replicat Manually from the beginning of the LOB record.

Start logdump utility to find out the root cause

[oracle@oracledev11 gg]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605

Logdump 411 >ghdr on

Logdump 412 >detail on

Logdump 413 >open /gg/dirdat/VC/VC000079

Current LogTrail is /gg/dirdat/VC/VC000079

Logdump 414 >pos o

Error: Invalid file position (O)

Logdump 415 >DETAIL DATA

Logdump 416 >pos 0

Reading forward from RBA 0

Logdump 417 >n

2015/01/25 17:52:55.283.720 FileHeader Len 1359 RBA 0

Name: *FileHeader*

3000 01a2 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...

0003 3200 0004 2000 0000 3300 0008 02f2 33b1 25be | ..2... ...3.....3.%.

3008 3400 001c 001a 7572 693a 736c 3733 7670 7064 | 0.4.....uri:oraclepro

6270 3030 393a 3a67 673a 686f 6d65 3600 001d 001b | d009::gg:home6.....

2f67 672f 4747 3131 2f64 6972 6461 742f 5643 2f56 | /gg/dirdat/VC/V

4330 3030 3037 3937 0000 0101 3800 0004 0000 004f | C0000797....8......O

3900 0008 0000 0000 05f5 db9a 3a00 0081 0b31 3038 | 9...........:....108

Logdump 418 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 2020 (x07e4) IO Time : 2015/01/25 17:52:52.996.472

IOType : 116 (x74) OrigNode : 255 (xff)

TransInd : . (x01) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 25679 AuditPos : 159347796

Continued : Y (x01) RecCount : 3 (x03)

2015/01/25 17:52:52.996.472 LargeObject Len 2020 RBA 1367

Name: SCHEMA.MYTEST

After Image: Partition 4 m

0000 0003 0000 0005 0000 0002 0000 07d0 0000 07d0 | ....................

204d 6f62 696c 652f 3131 4235 3534 6120 5361 6661 | Mobile/3222F Safa

7269 2f39 3533 372e 3533 222c 2254 484d 5f62 726f | ri/1234.53","THM_bro

7773 6572 5f73 7472 696e 675f 6861 7368 3a34 6563 | wser_string_hash:4ec

6134 3532 3034 3731 3166 3265 3634 3764 3030 3161 | xxxxxxxxxxxxxxxxxxx

3133 3438 3734 3632 3322 2c22 5448 4d5f 6373 735f | 12344322","THM_css_

696d 6167 655f 6c6f 6164 6564 3a79 6573 222c 2254 | image_loaded:yes","T

Column 3, LOB fragment 2 of 5

Size 2000 (x07d0)

Offset 2000 (x07d0)

Logdump 419 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 86 (x0056) IO Time : 2015/01/25 17:52:52.996.472

IOType : 116 (x74) OrigNode : 255 (xff)

TransInd : . (x01) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 25679 AuditPos : 159347796

Continued : Y (x01) RecCount : 4 (x04)

2015/01/25 17:52:52.996.472 LargeObject Len 86 RBA 3463

Name: SCHEMA.MYTEST

After Image: Partition 4 m

0000 0003 0000 0005 0000 0003 0000 0fa0 0000 0042 | ...................B

3022 2c22 5448 4d5f 7072 6f66 696c 6564 5f64 6f6d | 0","THM_profiled_dom

6169 6e3a 7365 6375 7265 2e63 6865 636b 6f75 742e | ain:xxxxx.xxxx.

7669 7361 2e63 6f6d 222c 2254 484d 5f70 726f 6669 | corp.com","THM_profi

6c65 645f 646f | led_do

Column 3, LOB fragment 3 of 5

Size 66 (x0042)

Offset 4000 (x0fa0)

………………..

By reading the above current trail file it is clear that it is trying to apply a LOB segment (RBA 1367) which is fragmented meaning(applying 2 of 5) it is not starting from 1 which is missing in 'VC000079' trail file

Lets go to previous trail file(VC000078) and search for the LOB:

Logdump 439 >DETAIL DATA

Logdump 440 >GHDR ON

Logdump 441 >detail on

Logdump 442 >open /gg/dirdat/VC/VC000078 (going one trail file back)

Current LogTrail is /gg/dirdat/VC/VC000078

Logdump 443 >pos eof (going End of file)

Reading forward from RBA 99998989

Logdump 444 >pos reverse (Reading from reverse)

Reading in reverse from RBA 99998989

Logdump 445 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 2020 (x07e4) IO Time : 2015/01/25 17:52:52.993.564

IOType : 116 (x74) OrigNode : 255 (xff)

TransInd : . (x01) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 25679 AuditPos : 159347796

Continued : Y (x01) RecCount : 2 (x02)

2015/01/25 17:52:52.993.564 LargeObject Len 2020 RBA 99996893

Name: SCHEMA.MYTEST

After Image: Partition 4 m

0000 0003 0000 0005 0000 0001 0000 0000 0000 07d0 | ....................

7b22 616d 6d56 646e 6153 6572 7669 6365 5265 7175 | {"oracleserviceRequ

6573 7422 3a7b 2250 726f 6475 6374 496e 766f 6b65 | est":{"ProductInvoke

7254 7970 6522 3a22 565f 4d45 222c 2243 6f6e 7465 | rType":"XXXX","Conte

7874 5479 7065 223a 2250 7572 6368 6173 6549 6e74 | xtType":"PurchaseInt

656e 7422 2c22 5265 7175 6573 7449 6422 3a22 3331 | ent","RequestId":"31

6136 3032 3566 2d37 3664 622d 3431 6133 2d62 3338 | a1234f-76db-xxxx-b38

Column 3, LOB fragment 1 of 5

Size 2000 (x07d0)

Offset 0 (x0000)

Logdump 446 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 116 (x0074) IO Time : 2015/01/25 17:52:52.995.853

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x00) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 25679 AuditPos : 159347796

Continued : Y (x01) RecCount : 1 (x01)

2015/01/25 17:52:52.995.853 Insert Len 116 RBA 99996643 ß LOB starts from here

Name: SCHEMA.MYTEST

After Image: Partition 4 G b

0000 000c 0000 0008 3833 3731 3035 3036 0001 0028 | ........83710506...(

0000 0024 3965 6463 3564 6530 2d30 6665 612d 3431 | ...$gddgg5w4-0fea-41

3361 2d61 3938 332d 6666 3362 3834 6465 6561 3039 | 3a-a324-ff3b84feea09

0002 0008 0000 0004 5644 4e41 0004 0005 0000 0001 | ........BSDA........

3500 0500 1f00 0032 3031 352d 3031 2d32 353a 3137 | 5......2015-01-25:17

3a35 323a 3533 2e37 3031 3030 3030 3030 | :52:53.701000000

Column 0 (x0000), Len 12 (x000c)

Column 1 (x0001), Len 40 (x0028)

Column 2 (x0002), Len 8 (x0008)

Column 4 (x0004), Len 5 (x0005)

Column 5 (x0005), Len 31 (x001f)

Logdump 447 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 1805 (x070d) IO Time : 2015/01/25 17:52:52.995.853

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 25466 AuditPos : 55964348

Continued : N (x00) RecCount : 1 (x01)

2015/01/25 17:52:52.995.853 Insert Len 1805 RBA 99994708

Name: SCHEMA2.LOCATION

After Image: Partition 4 G s

0000 000c 0000 0008 3632 3839 3432 3433 0001 0041 | ........62894243...A

0000 003d 6874 7470 733a 2f2f 766d 6564 666d 2e76 | ...=https://oracle.dom

6973 612e 636f 6d3a 3834 3433 2f64 666d 7365 7276 | ain.com:8443/oracleserv

6963 6573 2f76 312f 636f 6e73 756d 6572 2f64 6563 | ices/v1/consumer/dec

6973 696f 6e00 0200 2f00 0000 2b31 3432 3232 3038 | ision.../...+12345678

3230 335f 3536 375f 3532 335f 6c37 3370 3132 336d | 203_567_523_l73p123m

5f43 4845 434b 4f55 542d 5749 4447 4554 0003 000e | _xxxxxx....

Column 0 (x0000), Len 12 (x000c)

Column 1 (x0001), Len 65 (x0041)

Column 2 (x0002), Len 47 (x002f)

Column 3 (x0003), Len 14 (x000e)

Column 4 (x0004), Len 5 (x0005)

Column 5 (x0005), Len 184 (x00b8)

Column 6 (x0006), Len 4 (x0004)

Column 7 (x0007), Len 6 (x0006)

Column 8 (x0008), Len 1295 (x050f)

Column 9 (x0009), Len 31 (x001f)

Column 10 (x000a), Len 8 (x0008)

Column 11 (x000b), Len 4 (x0004)

Column 12 (x000c), Len 8 (x0008)

Column 13 (x000d), Len 31 (x001f)

Column 14 (x000e), Len 31 (x001f)

Here we found the LOB segment in '78' trail file so lets start the replicat using the above highlighted rba number:

Syntax: ggsci> alter <replicat>, extseqno ### ,extrba <RBA of record from above output>

ggsci> alter REP1, extseqno 78 ,extrba 99996643

ggsci> start REP1

For more info:

OGG Replicat Abends On LOB Data. OGG Error: OGG-01028: Unexpected LOB Record Or Record Fragment In ... (Trail File) (Doc ID 1903819.1)

Wednesday, November 19, 2014

ORA-12516, TNS: listener could not find available handler with matching protocol stack

My Application Team getting below error and unable to initiate connection to the oracle database:

error: ORA-12516, TNS: listener could not find available handler with matching protocol stack

When I connected and checked on the database, (here my db is RAC server and using spfile)

On NODE 2:

Checking alert log file

vi alert_DEVD2.log

No errors found

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 18 19:35:36 2014

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL>

col name format a30

col value format a10

SQL> select name, value from v$spparameter where name in ('processes','sessions');

NAME VALUE

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

processes 400

sessions 400

or, check the max and current utilization of these parameters

select resource_name, current_utilization/limit_value*100,current_utilization, max_utilization,limit_value from v$resource_limit where resource_name in ('processes','sessions');

SQL> select count(*) from v$process;

COUNT(*)

----------

166

On NODE 1 :

SQL> select * from v$resource_limit where resource_name in ('processes','sessions');

vi alert_DEVD1.log

ORA-00020: maximum number of processes (400) exceeded

ORA-20 errors will not be written to the alert log for

the next minute. Please look at trace files to see all

the ORA-20 errors.

SQL> select count(*) from v$process;

COUNT(*)

----------

380

SQL> select count(*) from gv$process;

COUNT(*)

----------

546

From the above output I see my processes are exceeded the value set by oracle(400) and resulting the error in alert log. Then checked the connections to the database:

Set linesize 280;

select count(*),INST_ID,username,osuser,status,machine from gv$session group by INST_ID,username,osuser,status,machine order by count(*),INST_ID;

(or) for single node:

select count(*),username,osuser,status,machine from v$session group by username,osuser,status,machine order by count(*);

Check the output from the above query and kill if find any INACTIVE sessions (idle)

Solution:

According to oracle, PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.

If the database is a RAC database then each NODE can have different values for PROCESS parameter.

SQL> sho parameter spfile;

NAME TYPE VALUE

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

spfile string +DATADG/DEVD/spfiledevd.ora

Increase the Process parameter (replace xxx with any higher value)

SQL> alter system set processes=XXX scope=spfile;

System altered.

SQL> alter system set Sessions=XXX scope=spfile;

System altered.

*** Bounce the database to take effect ***

If processes=x

sessions=x*1.1+5

transactions=sessions*1.1

If the database is running on pfile then follow below steps:

1. Edit the database init.ora

2. Locate and increase the processes parameter to a higher value.

3. Save the init.ora file

4. Stop and restart the database

5. Re-connect to the database instance

For more Refer:

Troubleshooting Guide - ORA-20: Maximum Number Of Processes (%S) Exceeded (Doc ID 1287854.1)

Tuesday, October 14, 2014

OGG-00049 Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified

Today when doing Initial load my extract got ABENDED with the error below

2014-10-14 19:46:37 ERROR OGG-00049 Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified.

2014-10-14 19:46:37 ERROR OGG-01668 PROCESS ABENDING.

GGSCI (oracledev01.com) 1> view params initEXT

EXTRACT initEXT

SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")

SETENV(ORACLE_SID=orcl)

sourceistable

USERID ggs_admin,PASSWORD xxxxx

RMTHOST oracleqa01, mgrport 7809

RMTTRAIL /u01/dev/oracle/input/ggs/remote/cb/DEV_ORCL.dat, purge

TABLE DEV.TAB1;

TABLE DEV.TAB2;

Since here i'm sending the ".dat" files to the target server for the reporting purpose i'm not using Datapump and Replicat process, just sending files directly from my extract process (datapump process is always OPTIONAL)

SOURCEISTABLE: extracts the records from the source tables directly.

RMTFILE: Specifies the location and file name of the extract file that will be written during the load.

SOLUTION: Remove the EXTTRAIL or RMTTRAIL parameter.

When I replaced RMTTRAIL with RMTFILE, then my issue got resolved.

RMTFILE /u01/dev/oracle/input/ggs/remote/cb/DEV_ORCL.dat, purge

GGSCI (oracledev01.domain.com) 3> start initEXT

Sending START request to MANAGER ...

EXTRACT INITEXT starting

GGSCI (oracledev01.domain.com) 4> info initEXT

EXTRACT INITEXT Initialized 2014-10-14 17:40 Status RUNNING

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

Reference:

Goldengate Extract Abends with Error Message: Trails Cannot Be Used When SOURCEISTABLE/SOURCEISFILE Is Specified (Doc ID 1457442.1)

Friday, October 10, 2014

Purge 11g LISTENER Logs using ADRCI

While doing server Housekeeping and found my Listener log files are consuming lot of space and contains logs from past few months.

According to Oracle doc, “Listener log files ('.xml' files) are not purged via the 'automatic' purge”. Therefore in 11g releases, these log files (and trace files) will not get automatically purged and so you need to manually delete them when they are no longer required.

Below are my listener log files on node1 of my 2 node RAC

OS version: RHEL 5.x
Oracle version: 11.2.0.3

[root@oracledev01 alert]# pwd

/u01/app/oracle/diag/tnslsnr/oracledev01/listener/alert

[root@oracledev01 alert]# ls -lrth

total 1.1G

-rw-r----- 1 grid oinstall 11M Aug 13 02:56 log_1.xml

-rw-r----- 1 grid oinstall 11M Aug 13 09:49 log_2.xml

-rw-r----- 1 grid oinstall 11M Aug 13 16:33 log_3.xml

-rw-r----- 1 grid oinstall 11M Aug 13 23:35 log_4.xml

-rw-r----- 1 grid oinstall 11M Aug 14 06:20 log_5.xml

-rw-r----- 1 grid oinstall 11M Aug 14 13:38 log_6.xml

-rw-r----- 1 grid oinstall 11M Aug 14 20:57 log_7.xml

-rw-r----- 1 grid oinstall 11M Aug 15 04:05 log_8.xml

-rw-r----- 1 grid oinstall 11M Aug 15 11:47 log_9.xml

-rw-r----- 1 grid oinstall 11M Aug 15 19:15 log_10.xml

-rw-r----- 1 grid oinstall 11M Aug 16 02:48 log_11.xml

-rw-r----- 1 grid oinstall 11M Aug 16 11:11 log_12.xml

-rw-r----- 1 grid oinstall 11M Aug 16 21:36 log_13.xml

-rw-r----- 1 grid oinstall 11M Aug 17 08:05 log_14.xml

-rw-r----- 1 grid oinstall 11M Aug 17 17:57 log_15.xml

-rw-r----- 1 grid oinstall 11M Aug 18 03:34 log_16.xml

-rw-r----- 1 grid oinstall 11M Aug 18 12:47 log_17.xml

……….

o/p trimmed

……….

-rw-r----- 1 grid oinstall 11M Oct 4 16:30 log_93.xml

-rw-r----- 1 grid oinstall 11M Oct 5 11:11 log_94.xml

-rw-r----- 1 grid oinstall 11M Oct 5 15:28 log_95.xml

-rw-r----- 1 grid oinstall 11M Oct 5 20:27 log_96.xml

-rw-r----- 1 grid oinstall 11M Oct 6 01:28 log_97.xml

-rw-r----- 1 grid oinstall 11M Oct 6 06:44 log_98.xml

-rw-r----- 1 grid oinstall 11M Oct 6 12:09 log_99.xml

-rw-r----- 1 grid oinstall 11M Oct 6 17:33 log_100.xml

-rw-r----- 1 grid oinstall 11M Oct 6 23:33 log_101.xml

-rw-r----- 1 grid oinstall 11M Oct 7 17:04 log_102.xml

-rw-r----- 1 grid oinstall 11M Oct 8 10:42 log_103.xml

-rw-r----- 1 grid oinstall 11M Oct 9 03:27 log_104.xml

-rw-r----- 1 grid oinstall 11M Oct 9 12:21 log_105.xml

-rw-r----- 1 grid oinstall 11M Oct 9 20:59 log_106.xml

-rw-r----- 1 grid oinstall 11M Oct 10 04:27 log_107.xml

-rw-r----- 1 grid oinstall 11M Oct 10 11:57 log_108.xml

-rw-r----- 1 grid oinstall 6.0M Oct 10 16:19 log.xml

NOTE: log.xml files will be archived into a log_1.xml, log_2.xml and so on... into the same location as soon as it grows to 10MBytes

[root@oracledev01 alert]# . oraenv

ORACLE_SID = [root] ? DBNAME1

The Oracle base has been set to /u01/app/oracle

[root@oracledev01 alert]# adrci

ADRCI: Release 11.2.0.3.0 - Production on Fri Oct 10 16:38:55 2014

ADR base = "/u01/app/oracle"

adrci> show home

ADR Homes:

diag/tnslsnr/oracledev01/listener

adrci> set home diag/tnslsnr/oracledev01/listener

adrci> SHOW CONTROL

ADR Home = /u01/app/oracle/diag/tnslsnr/oracledev01/listener:

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

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

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

2146670485 720 8760 2011-04-22 20:50:43.449927 +00:00 1 2 80 1 2011-04-22 20:50:43.449927 +00:00

1 rows fetched

From the above output we see LAST_AUTOPRG_TIME & LAST_MANUPRG_TIME are empty, meaning that they are not being purged automatically and no one purged manually

adrci> purge -age 10080 -type ALERT (purging older than 7 days, 10080 mins)

adrci> exit

Verify:

[root@oracledev01 alert]# ls -lrth

total 197M

-rw-r----- 1 grid oinstall 11M Oct 3 20:41 log_90.xml

-rw-r----- 1 grid oinstall 11M Oct 4 03:01 log_91.xml

-rw-r----- 1 grid oinstall 11M Oct 4 09:44 log_92.xml

-rw-r----- 1 grid oinstall 11M Oct 4 16:30 log_93.xml

-rw-r----- 1 grid oinstall 11M Oct 5 11:11 log_94.xml

-rw-r----- 1 grid oinstall 11M Oct 5 15:28 log_95.xml

-rw-r----- 1 grid oinstall 11M Oct 5 20:27 log_96.xml

-rw-r----- 1 grid oinstall 11M Oct 6 01:28 log_97.xml

-rw-r----- 1 grid oinstall 11M Oct 6 06:44 log_98.xml

-rw-r----- 1 grid oinstall 11M Oct 6 12:09 log_99.xml

-rw-r----- 1 grid oinstall 11M Oct 6 17:33 log_100.xml

-rw-r----- 1 grid oinstall 11M Oct 6 23:33 log_101.xml

-rw-r----- 1 grid oinstall 11M Oct 7 17:04 log_102.xml

-rw-r----- 1 grid oinstall 11M Oct 8 10:42 log_103.xml

-rw-r----- 1 grid oinstall 11M Oct 9 03:27 log_104.xml

-rw-r----- 1 grid oinstall 11M Oct 9 12:21 log_105.xml

-rw-r----- 1 grid oinstall 11M Oct 9 20:59 log_106.xml

-rw-r----- 1 grid oinstall 11M Oct 10 04:27 log_107.xml

-rw-r----- 1 grid oinstall 11M Oct 10 11:57 log_108.xml

-rw-r----- 1 grid oinstall 6.3M Oct 10 16:39 log.xml

$ date

Fri Oct 10 17:36:34 GMT 2014

adrci> SHOW CONTROL

ADR Home = /u01/app/oracle/diag/tnslsnr/oracledev01/listener:

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

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

2146670485 720 8760 2011-04-22 20:50:43.449927 +00:00 2014-10-10 16:39:37.644372 +00:00 1 2 80 1 2011-04-22 20:50:43.449927 +00:00

1 rows fetched

I see today’s date now in the LAST_MANUPRG_TIME column

NOTE: Similarly we can purge files in different component using same command

ALERT — files in the ./alert directory

INCIDENT — files in the ./incident/incdir_<incid> directory. Please note that 'purge -age "n" -type INCIDENT' does not remove the *.lck files from the /lck directory in the incident schema

TRACE — files in the ./trace directory

CDUMP — files in the ./cdump directory

UTSCDMP — files in the ./trace/cdmp_<timestamp> directories

STAGE — files in the ./stage directory

SWEEP — files in the ./sweep directory

HM — files in the ./hm directory and metadata in the HM schema

IPS — files in the ./incpkg directory and metadata in the IPS schema

Referrence:

How To Purge Listener Log.Xml File? (Doc ID 816871.1)

Why Are My Listener Logs & Traces Not Purged By The ADR? (Doc ID 1438242.1)

ADR Auto Purge Does Not Work On All RAC Instances, ASM and Standby Databases (Doc ID 1326983.1)

Monday, August 18, 2014

ORA-26947: Oracle GoldenGate replication is not enabled

When I’m trying to setup OGG On my Linux Machine after finished installing (Installation Steps) when I start my extract process it got ABENDED with the below Error message and when I see the ORA error in the error log and after researching some docs then I realized Oracle introduced a new OGG parameter in order to enable OGG replication

ERROR:

2014-08-15 18:00:38 WARNING OGG-01423 No valid default archive log destination directory found for thread 1.

2014-08-15 18:00:38 INFO OGG-01515 Positioning to begin time Aug 15, 2014 5:44:29 PM.

----output Trimmed

2014-08-15 18:00:59 ERROR OGG-00446 Opening ASM file +FRADG/gsource/archivelog/2014_08_15/thread_1_seq_7.302.85568

4013 in DBLOGREADER mode: (26947) ORA-26947: Oracle GoldenGate replication is not enabled.

Not able to establish initial position for begin time 2014-08-15 17:44:29.

2014-08-15 18:00:59 ERROR OGG-01668 PROCESS ABENDING.

Solution:

For Oracle DB versions 11.2.0.4 and 12.1.0.2 there will be a new init.ora parameter called ENABLE_GOLDENGATE_REPLICATION. In order for some of the Oracle GoldenGate functionality to work, this parameter must be set to true.

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

System altered.

No need to Bounce the database instance

Referrence : ENABLE_GOLDENGATE_REPLICATION database parameter (Doc ID 1568417.1)

Thursday, May 8, 2014

Enable TDE in oracle Goldengate CLASSIC mode

Ref: [Doc ID: 1451327.1]

Ref: http://docs.oracle.com/cd/E35209_01/doc.1121/e35957.pdf

To support TDE on Oracle 10.2.0.5 or 11.2.0.2, download and apply Oracle Patch 10395645 to the source database. Oracle 11.2.0.3 patchset includes this patch

Since my database is on 11.2.0.2 Two-Node RAC on LINUX so i'm applying below patch (see patch doc for how to apply)

$ opatch apply online -connectString<SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_Node2>:<Password_Node2>:<Node2_Name>,<SID_NodeN>:<Username_NodeN>:<Password_NodeN>:<NodeN_Name><PATCH_TOP_DIR>/10395645

From NODE 1: (everything from NODE1 unless specified)

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

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

DEV011

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED

2 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED

Status "CLOSED" because my database doesn't have any Wallet created yet

SQL> select * from v$encryption_wallet;

WRL_TYPE

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

WRL_PARAMETER STATUS

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

file

/opt/oracle/app/admin/DEV01/wallet CLOSED

Put the below code in sqlnet.ora of GI and OH home location :

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/app/admin/DEV01/wallet/)))

NOTE: Create the directories on both nodes if they doesn't exists on the server

ex: mkdir -p /opt/oracle/app/admin/DEV01/wallet

copy sqlnet.ora file to other nodes into same locations (GRID and RDBMS homes)

ex: scp sqlnet.ora oracle@oracledev012.domain.com:/opt/oracle/database/11.2.0.2/network/admin

scp sqlnet.ora oracle@oracledev012.domain.com:/opt/grid/11.2.0.2/network/admin

Now create a wallet password using below sql command from one node:

SQL> alter system set encryption key identified by "xxxxx";

System altered.

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet OPEN

2 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED

copy the wallet file from /opt/oracle/app/admin/DEV01/wallet/ from node1 to other nodes

**** Now, BOUNCE the database ****

$ srvctl stop database -d DEV01

$ srvctl start database -d DEV01

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/app/admin/DEV01/wallet/ CLOSED

2 file

/opt/oracle/app/admin/DEV01/wallet/ CLOSED

Observe, Oracle wallet location has been changed after database bounce

SQL> alter system set encryption wallet open identified by “xxxxx";

System altered.

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/app/admin/DEV01/wallet/ OPEN

2 file

/opt/oracle/app/admin/DEV01/wallet/ OPEN

From GG_HOME directory, run prvtclkm.plb, and grant privilege to goldengate user

(missing this step may cause extract error: PLS-00201: identifier 'SYS.DBMS_INTERNAL_CLKM' must be declared)

oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 18 20:59:07 2013

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>@prvtclkm.plb

Package created.

Library created.

Package body created.

SQL>grant execute on sys.dbms_internal_clkm to gguser;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ cd /opt/oracle/app/admin/DEV01/wallet

From wallet location, execute below:

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA

Now, create an entry forORACLEGG in the wallet (this will ask you to create sharedsecret password)

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Your secret/Password is missing in the command line

Enter your secret/Password:enter_sharedsecret_password

Re-enter your secret/Password:enter_sharedsecret_password

Enter wallet password:enter_wallet_password

Now verify whether OGG entry has been created or not

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA

Now we’ll see the new ORACLEGG entry in the wallet (only in node1), so to create the entry in other nodes copy the wallet to other nodes

Here I’m doing SCP the wallet file from node1 to other nodes

$ scp * oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet

After copying wallet file from node1 to node2 list and see for new encryption for ORACLEGG entry from Node2 server

(from NODE2) oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA

NOTE:

- close wallet from all the instances.

- re-open the wallet from all the instances.

Ex:

alter system set encryption wallet close identified by "xxxx"; (node1)

alter system set encryption wallet open identified by "xxxx"; (node1)

alter system switch logfile;

alter system set encryption wallet close identified by "xxxx"; (node2)

alter system set encryption wallet open identified by "xxxx"; (node2)

alter system switch logfile;

Connect to ggsci and encrypt the sharedsecret password

GGSCI (oracleDEV01.domain.com) 2> ENCRYPT PASSWORD sharedsecret AES128 ENCRYPTKEY WALLETENCR

Encrypted password: AADAAAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI

Algorithm used: AES128

Where WALLETENCR à key mentioned in the ENCKEYS file

See: Generate GOLDENGATE Encryption Keys usingKeygen

In the Extract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option

GGSCI (oracleDEV01.domain.com) 3> edit params EXT

DBOPTIONS DECRYPTPASSWORD AADAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI AES128 &

ENCRYPTKEY WALLETENCR

Bounce the extract process to take this effect

About this blog

The contents in my blog are uploaded purely according to my convenience & based on my results and should not be considered professional advice, official documentation, or a substitute for vendor‑supported guidance, viewers may or may not get same results ............Any actions you take based on the content of this blog are strictly at your own risk. I am not responsible for any losses, damages, outages, or issues arising from the use or misuse of the information provided here. All product names, trademarks, and registered trademarks are the property of their respective owners. I thank you everyone for visiting my blog