Extracting DML Stats With GGate

Hi all,

After having GoldenGate setup in your environment, on of the most frequent questions you most likely face is how much work is being done by your GGate environment. Or even you may want to set up some dummy extraction just to measure it before start your GGate implementation project.

GoldenGate provides stats command to report the work which is done. It’s recommended to reset the counters/stats before any testing you want or before start gathering, for better outputs.

The retrieving can be done for the total (since last reset), daily or even on table level, as per below:

1. Reset counters/stats:

stats extract ext_test, reset

2. Retrieve stats for a Table since Reset:

stats extract ext_test, table owner1.test, latest

3. Retrieve stats Total since last start of extract:

stats extract ext_int, totalsonly owner1.test

4. Retrieve stats from the day of a table:

stats extract ext_int, daily, table owner1.test

5. Retrieve stats from the day of all schema tables configured on extract:

stats extract ext_int, daily, table owner1.*


Example of Output:

GGSCI (myserver.local) 1> stats ext_test total daily

Sending STATS request to EXTRACT EXT_TEST ...

Start of Statistics at 2019-12-16 15:21:56.

Output by User Exit:

Extracting from OWNER1.TABLE1 to OWNER1.TABLE1:

*** Total statistics since 2019-12-16 07:18:14 ***
        Total inserts                                   2744.00
        Total updates                                      0.00
        Total deletes                                    300.00
        Total discards                                     0.00
        Total operations                                3044.00

*** Daily statistics since 2019-12-16 07:18:14 ***
        Total inserts                                   2744.00
        Total updates                                      0.00
        Total deletes                                    300.00
        Total discards                                     0.00
        Total operations                                3044.00

More about it?
Here: https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/ggsci_commands030.htm#GWURF216

Hope it Helps!

ERROR: ´Failed to push ldap config data to libOvd for service instance “idstore.ldap” in JPS context “default”` in OSB 12C

The error below occurs on OSB server and AdminServer of the domain.


#### <> <> <> <1539087463845> <[severity-value: 4] [partition-id: 0] [partition-name: DOMAIN] > ailed. Reason:

There are 1 nested errors:

oracle.security.jps.JpsException: JPS-02592: Failed to push ldap config data to libOvd for service instance “idstore.ldap” in JPS context “default”, cause: org.xml.sax.SAXException: Error Parsing at line #2: 1.
org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.security.jps.internal.config.OpssCommonStartup.start(OpssCommonStartup.java:209)
at oracle.security.jps.wls.JpsWlsStartup.start(JpsWlsStartup.java:96)
at oracle.security.jps.JpsStartup.start(JpsStartup.java:215)
at oracle.security.jps.wls.JpsDefaultService.start(JpsDefaultService.java:74)
at weblogic.server.AbstractServerService.postConstruct(AbstractServerService.java:76)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.glassfish.hk2.utilities.reflection.ReflectionHelper.invoke(ReflectionHelper.java:1262)
at org.jvnet.hk2.internal.ClazzCreator.postConstructMe(ClazzCreator.java:332)
at org.jvnet.hk2.internal.ClazzCreator.create(ClazzCreator.java:374)
at org.jvnet.hk2.internal.SystemDescriptor.create(SystemDescriptor.java:471)
at org.glassfish.hk2.runlevel.internal.AsyncRunLevelContext.findOrCreate(AsyncRunLevelContext.java:228)
at org.glassfish.hk2.runlevel.RunLevelContext.findOrCreate(RunLevelContext.java:85)
at org.jvnet.hk2.internal.Utilities.createService(Utilities.java:2072)
at org.jvnet.hk2.internal.ServiceHandleImpl.getService(ServiceHandleImpl.java:114)
at org.jvnet.hk2.internal.ServiceHandleImpl.getService(ServiceHandleImpl.java:88)
at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$QueueRunner.oneJob(CurrentTaskFuture.java:1213)
at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$QueueRunner.run(CurrentTaskFuture.java:1144)
at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:643)
at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:348)
at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:333)
at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:54)
at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:617)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:397)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:346)
Caused by: oracle.security.jps.service.idstore.IdentityStoreException: JPS-02592: Failed to push ldap config data to libOvd for service instance “idstore.ldap” in JPS context “default”, cause: org.xml.sax.SAXException: Error Parsing at line #2: 1.
org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.security.jps.internal.idstore.util.LibOvdUtil$LibOvdDataPusherImpl.pushLdapDataToLibOvdIntenal(LibOvdUtil.java:641)
at oracle.security.jps.internal.idstore.util.LibOvdUtil$LibOvdDataPusherImpl.pushLdapDataToLibOvd(LibOvdUtil.java:593)
at oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider$NoLibOvd.getInstance(LdapIdentityStoreProvider.java:274)
at oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider.getInstance(LdapIdentityStoreProvider.java:118)
at oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider.getInstance(LdapIdentityStoreProvider.java:125)
at oracle.security.opss.internal.runtime.ServiceContextManagerImpl.createContextInternal(ServiceContextManagerImpl.java:636)
at oracle.security.opss.internal.runtime.ServiceContextManagerImpl.createDefaultContext(ServiceContextManagerImpl.java:319)
at oracle.security.opss.internal.runtime.ServiceContextManagerImpl.initialize(ServiceContextManagerImpl.java:241)
at oracle.security.jps.internal.config.OpssCommonStartup$1.run(OpssCommonStartup.java:150)
at java.security.AccessController.doPrivileged(Native Method)
at oracle.security.jps.internal.config.OpssCommonStartup.start(OpssCommonStartup.java:134)
… 26 more
Caused by: org.xml.sax.SAXException: Error Parsing at line #2: 1.
org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.ods.virtualization.engine.config.parsers.ConfigErrorHandler.fatalError(ConfigErrorHandler.java:103)
at oracle.xml.parser.v2.XMLError.flushErrorHandler(XMLError.java:435)
at oracle.xml.parser.v2.XMLError.flushErrors1(XMLError.java:290)
at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:476)
at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:417)
at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:246)
at oracle.xml.jaxp.JXDocumentBuilder.parse(JXDocumentBuilder.java:175)
at oracle.xml.jaxp.JXDocumentBuilder.parse(JXDocumentBuilder.java:131)
at oracle.ods.virtualization.engine.config.parsers.ConfigParser.parseConfiguration(ConfigParser.java:335)
at oracle.ods.virtualization.engine.config.parsers.ConfigParser.parseConfiguration(ConfigParser.java:307)
at oracle.ods.virtualization.engine.config.parsers.ConfigParser.parseConfiguration(ConfigParser.java:292)
at oracle.ods.virtualization.config.ConfigureAdapters.doConfigure(ConfigureAdapters.java:950)
at oracle.ods.virtualization.config.ConfigureAdapters.configure(ConfigureAdapters.java:911)
at oracle.security.jps.internal.idstore.util.LibOvdUtil$LibOvdDataPusherImpl.pushLdapDataToLibOvdIntenal(LibOvdUtil.java:632)
… 36 more
Caused by: org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.xml.parser.v2.XMLError.flushErrorHandler(XMLError.java:425)
… 48 more

Probably Corrupted the file adapters.os_xml and file was empty.


1) Take a backup of $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml
2) Delete corrupted adapters.os_xml
3) Copy adapters.os_xml from $MW_HOME/oracle_common/modules/oracle.ovd/templates/ to $DOMAIN_HOME/config/fmwconfig/ovd/default/
4) Restart the server.

That’s all for today.

Oracle Streams: Adding a Table to Streams Replication

Feel old yet?
Don’t worry, we all know that there are still companies using Streams. So, here goes a quick manual for a regular activity: Add a new table to replication using SYS.DBMS_STREAMS_ADM.

For this, assume the following:

STREAMS SETUP OWNER => streams_adm
CAPTURE PROCESS => str_capture
APPLY_PROCESS => str_apply

The high level steps to add table to streams are as follows:

1. Stop streams CAPTURE
2. Create the table on the target database
3. Configure the CAPTURE process
4. Add a rule to the APPLY process
5. Instantiate the new object
6. Create constraints and indexes
7. Restart streams CAPTURE and APPLY

Let’s detail them:

More“Oracle Streams: Adding a Table to Streams Replication”

IPv6 Formatting for JDBC and SQLPlus

Hey all!
Seems new right? But it’s available since 11gR2.
Not needed to explain what is IPV6, right? Any questions, go here.

In summary the only thing you need is to enclose the IPv6 address in square brackets. Like this:

For Easy Connect on IPV4:

SQL> connect user/pass@


For Easy Connect on IPV6:

SQL> connect user/pass@[1:95e05a:g0d:da7a:2007]:1521/GREPORADB

For JDBC (thin) IPV4:




For JDBC (thin) IPV6:

(ADDRESS=(PROTOCOL=TCP)(HOST=[1:95e05a:g0d:da7a:2007]) (PORT=1521))



As you can imagine, the same applies to your TNSNAMES entries.

Also, according to this, it can be used even for your LISTENER:

    (HOST = [1:95e05a:g0d:da7a:2007])(PORT =1521))


Create datasource 11g in silent mode

Today I will provide scripts to help create datasource(DS) in weblogic 11g in silent mode.

Necessary files:

After downloading the files, edit DS.properties to XA datasource or edit DSnoXA.properties to noXA datasource with enviroment data where will be created datasource.

For example, DS.properties:More“Create datasource 11g in silent mode”

GGate: STOP request pending. There are open, long-running transactions.

Hey all!
So, these days I was about to stop the GGate extract to add a new table to replication and saw this:

GGSCI (greporasrvr) 2> stop extract GREPORA_EXT

Sending STOP request to EXTRACT GREPORA_EXT ...

STOP request pending. There are open, long-running transactions.
Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts.
To force Extract to stop, use the SEND EXTRACT GREPORA_EXT, FORCESTOP command.
Oldest redo log file necessary to restart Extract is:

Redo Log Sequence Number 616, RBA 135673360.

2018-04-12 15:55:41 WARNING OGG-01742 Command sent to EXTRACT GREPORA_EXT returned with an invalid response.

Interesting, right?
Let’s discover what is causing the situation:

GGSCI (greporasrvr) 2> send extract GREPORA_EXT, showtrans duration 20 MIN

Sending showtrans request to EXTRACT GREPORA_EXT ...

Oldest redo log file necessary to restart Extract is:

Redo Log Sequence Number 616, RBA 135673360

XID: 92.1.1460254
Items: 1
Redo Thread: 1
Start Time: 2018-04-12:08:27:03
SCN: 22.120909138 (94610189650)
Redo Seq: 616
Redo RBA: 135673360
Status: Running

XID: 69.7.1771671
Items: 1
Redo Thread: 1
Start Time: 2018-04-12:13:27:29
SCN: 22.121387178 (94610667690)
Redo Seq: 629
Redo RBA: 1457168
Status: Running

15:59:12 SYS@GREPDB>select * from gv$transaction where xidusn=92;
1 000000018FEA2170 92 1 1460254 121 548870 24255 48 ACTIVE 04/12/18 08:27:02 120909138 22 168 2
131578 24097 43 0000000198A49D68 7683 NO NO NO NO

0 0 0 0 0 0 0 0 81875 4581610 23108750 1415217 -1.675E+09 197 12-APR-2018 08:27:02 0 0
9.4610E+10 0 5C0001001E481600 0000000000000000 0000000000000000

1 row selected.

16:00:08 SYS@GREPDB>select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='000000018FEA2170';
885 55577 db file sequential read feedprocs12 39za7ttrfjbr1 55 6x9pg77q982g3
JDBC Thin Client JDBC Thin Client

1 row selected.

16:00:37 SYS@GREPDB>set verify off
set pagesize 999
col username format a13
col prog format a22
col sql_text format a140
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col etime format 9,999,999.99
col execs for 99999,999
select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
from v$sql s, dba_users u
sql_id like nvl('&sql_id',sql_id)
and u.user_id = s.parsing_user_id
Enter value for sql_id: 39za7ttrfjbr1

------------- ------ ---------- ---------- ------------- ------------- -------------
39za7ttrfjbr1 0 72089250 0 25,434.97 25,434.97 GREPORA
--SQL Name:summary/envision/insertMyTable INSERT INTO mytable ( .......) 

1 row selected.

Now we know it’s being caused by this long insert in mytable, but how can I proceed?
There are basically two options:
1. Kill the session on DB: Long rollback predicted, be aware of the impact and rollback activity, plus application effects if any.
2. Force stop of Extract with SEND EXTRACT GREPORA_EXT, FORCESTOP: In this case there is no effect for the database and no rollback, HOWEVER, when extract start again it will need to start from this cancelled sequence. First question, do I have this sequence still available? As you could see before, sequence 616 on would be needed, lets check on our availability:

16:01:41 SYS@GREPDB>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA_GREPDB
Oldest online log sequence 619
Next log sequence to archive 634
Current log sequence 634

So, in case it be needed, be aware you’ll need your archivelogs and, if already removed, restore may be needed.

There is also third option: 3. Wait until transaction be completed. 🙂

If 1 or 2 need to be chosen, choose wisely. 🙂

Hope this helps.

Disk Space is not Released After Deleting Files

When deleting a large files, the file is deleted successfully but the size of the filesystem does not reflect the change.
The files was be deleted, but only restarting the jvm or java process released the disk space.
Usually occurs with log files.

The lsof command show the files opened in system.
For example: lsof |grep deleted

java 15138 oracle 2959r REG 253,3 5875027 131163 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1.out03422 (deleted)
java 15138 oracle 3054r REG 253,3 10480928 131166 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1-diagnostic-81.log (deleted)
java 15138 oracle 3062r REG 253,3 10479417 131200 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1-diagnostic-82.log (deleted)

The command output shows pid, owner, file descriptor (fd), size and file.

If can’t restart the process, it is possible to force the system to de-allocate the space consumed by an in-use file by forcing the system to truncate the file.
$ echo > /proc/pid/fd/fd_number

Be careful not to truncate unwanted files.

In my case:
$ echo > /proc/15138/fd/2959
$ echo > /proc/15138/fd/3054
$ echo > /proc/15138/fd/3062

That’s all for today.

GGATE ABBENDED: ORA-00308: Cannot Open Archived Log

Hi all!
Ok, so this is one of the most common issues for GGate administration. How to solve it? Easy thing.

First let’s understand what it means: It means the redologs don’t have the required information (assuming integrated) and you have already deleted archivelogs the extract needs. Why? Probably because you already backed up those archivelogs and they were not needed for the database anymore.

Unfortunately we don’t have any kind of ARCHIVELOG DELETION POLICY to guarantee extracts had already read it, like we have for Dataguard. So, what can we do?

Restore the missing archivelogs.

But first let’s confirm on the errors. Some examples:

ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ext1.prm: Opening ASM file +ARCH/2_11055_742755632.dbf in DBLOGREADER mode: (308) ORA-00308: cannot open archived log '+ARCH/2_11055_742755632.dbf' ORA-17503.


ERROR OGG-01028 Oracle GoldenGate Capture for Oracle,ext1.prm: Getting attributes for ASM file +ARCH/2_86720_716466928.dbf,

SQL : (15056)

ORA-15056: additional error message ORA-15173: entry '2_86720_716466928.dbf' does not exist in directory '/...


Restore all archive logs starting from recovery checkpoint until the current checkpoint and restart the extract:
More“GGATE ABBENDED: ORA-00308: Cannot Open Archived Log”

Error JPS-06516: Failed to get credential store in Webcenter Portal

After startup NodeManager wrongly started from the root account, the following error occurs:

<Apr 9, 2018 8:34:41 AM BRT> <Warning> <oracle.jps.credstore> <JPS-01050> <Opening of wallet based credential store failed. Reason java.io.IOException: PKI-02002: Unable to open the wallet. Check password. >
<Apr 9, 2018 8:34:41 AM BRT> <Warning> <oracle.webcenter.framework.service.WebCenterConfig> <WCS-43054> <An error occured while trying to lookup connection Pagelet Producer.
javax.naming.NamingException [Root exception is oracle.adf.share.jndi.ConnectionException: java.lang.IllegalArgumentException: oracle.security.jps.service.keystore.KeyStoreServiceException: JPS-06516: Failed to get credential store.

Verify that the user owns the managed server has access to all files and directories in the temporary directory, specifically oracle-dfw* directories.


If there are files and / or directories with different owners of the managed server owner that presents the problem, back up the existing files and delete or move them.

That’s all for today.

Granting OSB Test Console access in OSB 12C

In weblogic console, add the user to the role ‘IntegrationAdmin’:
Security Realms > myrealm > Roles and Polices > Realm Roles > Global Roles > IntegrationAdmin > View Role Conditions


In EM, add the user to the following roles:
Middleware Administrator

More“Granting OSB Test Console access in OSB 12C”