bersler / OpenLogReplicator

Open Source Oracle database CDC
https://www.bersler.com
GNU General Public License v3.0
280 stars 131 forks source link

Grant validation always fails for SyS.CCOL$ #117

Closed Naros closed 6 months ago

Naros commented 6 months ago

A quick look at the grants for SYS.CCOL$ shows as follows:

SQL> l
  1  select privilege from dba_tab_privs
  2  where grantee = 'C##DBZUSER'
  3* and table_name = 'CCOL$'
SQL> /

PRIVILEGE
----------------------------------------
SELECT
FLASHBACK

and yet when connecting OpenLogReplicator always states grants are missing:

2024-03-07 01:36:53 INFO  00000 version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production, context: , resetlogs: 0, activation: 0, con_id: 3, con_name: ORCLPDB1
2024-03-07 01:36:53 HINT  run: ALTER SESSION SET CONTAINER = ORCLPDB1;
2024-03-07 01:36:53 HINT  run: GRANT SELECT, FLASHBACK ON SYS.CCOL$ TO c##dbzuser;
2024-03-07 01:36:53 ERROR 10034 grants missing for table SYS.CCOL$
2024-03-07 01:36:53 INFO  00000 Oracle replicator for: ORACLE is shutting down
2024-03-07 01:36:53 INFO  00000 Oracle replicator for: ORACLE allocated at most 32MB memory, max disk read buffer: 0MB

Verified selection check works:

SQL> connect c##dbzuser@ORCLCDB; 
Enter password: 
Connected.
SQL> alter session set container = ORCLPDB1;

Session altered.
SQL> select 1 from sys.ccol$ where 0 = 1;

no rows selected

If I disable the grant validation, I get this error in the logs:

2024-03-07 02:01:00 INFO  00000 client requested to start from scn: 14104426
2024-03-07 02:01:00 INFO  00000 Oracle Replicator for ORACLE in online mode is starting (flags: 4192) from scn: 14104426
2024-03-07 02:01:00 INFO  00000 current resetlogs is: 1127755067
2024-03-07 02:01:00 INFO  00000 starting sequence not found - starting with new batch with seq: 239
2024-03-07 02:01:00 INFO  00000 reading dictionaries for scn: 14104467
2024-03-07 02:01:00 ERROR 10051 OCI: [ORA-01031: insufficient privileges]

This is based on a build using build-dev.sh docker script.

Naros commented 6 months ago

Actually, this was a mistake on my part, I was pointing to the wrong Oracle instance :face_exhaling: Clearly it's time for :bed: