While running command in GGSCI, I received the following messages:
GGSCI (rac1.mycompany.mydomain as ggcw@orcl2) 4> ADD TRANDATA HR.* ... 2024-04-06 00:13:51 WARNING OGG-00706 Failed to add supplemental log group on table HR.TEST due to ORA-06550: line 1, column 46: PL/SQL: ORA-00904: "DBMS_XSTREAM_GG_ADM"."SYNCHRONIZATION": invalid identifier ORA-06550: line 1, column 39: PL/SQL: SQL Statement ignored ORA-06550: line 1, column 109: PLS-00201: identifier 'DBMS_XSTREAM_GG_ADM' must be declared ORA-06550: line 1, column 109: PL/SQL: Statement ignored ORA-06550: line 1, column 156: PLS-00201: identifier 'DBMS_CAPTURE_ADM' must be declared ORA-06550: line 1, column 156: PL/SQL: Statement ignored ORA-06550: line 1, column 263: PLS-00201: identifier 'DBMS_XSTREAM_GG_ADM' must be declared ORA-06550: line 1, column 263: PL/SQL: Statement ignored SQL DECLARE saved_sync varchar2(4); BEGIN select dbms_xstream_gg_adm.synchronization into saved_sync from dual; dbms_xstream_gg_adm.synchronization := 'NONE'; DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => '"HR"."TEST"', supplemental_logging => 'none'); dbms_xstream_gg_adm.synchronization := saved_sync; END;.
Solution:
Connect to the database via sqlplus and grant the below privileges to GG user (ggcw in my case):
exec dbms_goldengate_auth.grant_admin_privilege('ggcw'); grant select any dictionary to ggcw; grant insert any table to ggcw; grant update any table to ggcw; grant delete any table to ggcw;
Rerun the statement:
GGSCI (rac1.mycompany.mydomain as ggcw@orcl2) 4> ADD TRANDATA HR.*
In this post I will discuss one of the reasons why golden gate startup may hang and fail on both nodes:
Problem:
[root@primrac1 tmp]# /u01/app/grid/xag/bin/agctl start goldengate gg_replicate
^[[ACRS-2672: Attempting to start 'xag.gg_replicate.goldengate' on 'primrac2'
CRS-2674: Start of 'xag.gg_replicate.goldengate' on 'primrac2' failed
CRS-2679: Attempting to clean 'xag.gg_replicate.goldengate' on 'primrac2'
CRS-2681: Clean of 'xag.gg_replicate.goldengate' on 'primrac2' succeeded
CRS-2563: Attempt to start resource 'xag.gg_replicate.goldengate' on 'primrac2' has failed. Will re-retry on 'primrac1' now.
CRS-2672: Attempting to start 'xag.gg_replicate.goldengate' on 'primrac1'
CRS-2674: Start of 'xag.gg_replicate.goldengate' on 'primrac1' failed
CRS-2679: Attempting to clean 'xag.gg_replicate.goldengate' on 'primrac1'
CRS-2681: Clean of 'xag.gg_replicate.goldengate' on 'primrac1' succeeded
CRS-2632: There are no more servers to try to place resource 'xag.gg_replicate.goldengate' on that would satisfy its placement policy
CRS-4000: Command Start failed, or completed with errors.
Troubleshooting:
Check crsd_scriptagent_oracle.trc trace file on each database node. For my environment the full path of this file is /u01/app/grid/diag/crs/primrac2/crs/trace/crsd_scriptagent_oracle.trc
I found that the following error was repeating in the trace file:
2020-03-05 23:36:30.467 : CLSDADR:3560113920: ERRORdynamic component [R2530613] mapped to [CLSDYNAM]
On the metalink found only this PRCR-1079 : Failed to start resource ora.oc4j, the problem is not the same, but glanced a sentence containing config file.. so here I realized it could be related to the config file. And I was correct.
2019-03-23T21:59:50.237+0000 INFO | Read SharedContext from store for length 1372 of file '/GG_HOME/deploy/var/lib/conf/ServiceManager-config.dat'. (main) 2019-03-23T21:59:50.242+0000 INFO | Read SharedContext from store for length 245 of file '/GG_HOME/deploy/var/lib/conf/global.dat'. (main) 2019-03-23T21:59:50.245+0000 INFO | Read SharedContext from store for length 19 of file '/GG_HOME/deploy/var/lib/conf/ServiceManager-resources.dat'. (main) 2019-03-23T21:59:50.247+0000 ERROR| XAG Integration mode global setting of true does not match command line setting of false. (main) 2019-03-23T21:59:50.249+0000 ERROR| XAG Configuration failed (main)
Solution:
Pass `--xagEnabled` option when starting ServiceManager:
When you have untrusted source and trusted target. Or in simple words, your untrusted source should not have IP address and manager port details of the trusted target system in the configuration. And the trusted target is allowed to know VIP or Host IP of the untrusted source.
How does it work?
1) Start command must be issued on the Trusted Target for the Alias Extract, that is intercepted by Manager. 2) Start command is forwarded to the Manager on the Untrusted Source. 3) Manager on the Untrusted Source starts Passive Extract. 4) Passive Extract retrieves data from source database. 5) After security validation, Manager on the Untrusted Source knows where to send Trails (initiator server IP). And sends captured data. 6) Trail files are read by Replicat. 7) Data from the Trails are applied to the database at the Trusted Target.
GGSCI (primrac1.example.com)> edit params mgr PORT 7809 ACCESSRULE, PROG *, IPADDR *, PRI 1, ALLOW AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 60 AUTOSTART ER *
On the target:
GGSCI (stbyrac1.example.com)> edit params mgr PORT 7809 ACCESSRULE, PROG *, IPADDR *, PRI 1, ALLOW AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 60 AUTOSTART ER *
ACCESSRULE is used to control connection access to the Manager process and the processes under its control. By default there is only deny rule.
For testing purposes I created a rule that grants access to all programs and all IP addresses. In production environment you have to filter incomming IPs. For more information about ACCESSRULE, please click here.
Modify Globals
To allow a remote Director server connection, add the following parameter in ./GLOBALS:
START and STOP commands must be run on Alias Extract. Any GGSCI command executed against the Alias Extract is forwarded to the untrusted source for execution against the Passive Extract.
If you try to STOP or START Passive Extract, you will get the following error:
GGSCI (primrac1.example.com as ggcw@orclgg)> start PASSEXTR ERROR: START is not allowed against a passive group.
GGSCI (primrac1.example.com as ggcw@orclgg)> stop PASSEXTR ERROR: STOP is not allowed against a passive group.
Run start on only Alias Extract:
GGSCI (stbyrac1.example.com as ggcw@orclgg)> start AlExtr Sending START request to MANAGER@PRIMRAC1.EXAMPLE.COM:7809 … Sending START request to MANAGER@stbyrac1.example.com:7809 …
Check Status
GGSCI (stbyrac1.example.com as ggcw@orclgg)> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ALEXTR
GGSCI (primrac1.example.com as ggcw@orclgg)> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING PASSEXTR 00:00:00 00:18:14
For any possible problems you can check ggserr.log file under GG home.
$ /GG_HOME/home_1/ggsci
/GG_HOME/home_1/ggsci: error while loading shared libraries: libclntshcore.so.18.1: cannot open shared object file: No such file or directory
Cause:
GoldenGate put all necessary shared objects (.so files) under its own home directory, but several shared objects may not be found there. They should be borrowed from RDBMS or GI home.
$ /GG_HOME/home_1/ggsci
/GG_HOME/home_1/ggsci: error while loading shared libraries: libclntsh.so.18.1: cannot open shared object file: No such file or directory
Cause:
GoldenGate put all necessary shared objects (.so files) under its own home directory, but several shared objects may not be found there. They should be borrowed from RDBMS or GI home.
/GG_HOME/home_1/ggserr.log file contains the following error:
2019-01-08T23:05:11.364+0000 ERROR OGG-02022 Oracle GoldenGate Capture for Oracle, ext1.prm: Logmining server does not exist on this Oracle database.
2019-01-08T23:05:11.364+0000 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.
Solution:
Register the extract:
GGSCI (stbyrac1.example.com as ggcw@orcl1) 8> register extract ext1 database
2019-01-08 23:10:54 INFO OGG-02003 Extract EXT1 successfully registered with database at SCN 2126943.
Verify:
GGSCI (stbyrac1.example.com as ggcw@orcl1) 9> start ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (stbyrac1.example.com as ggcw@orcl1) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt