A quick validation or oracle GoldenGate replication with database vault "ON":
DV activated:
Source & Target:
SQL> select value from v$option where parameter='Oracle Database Vault';
VALUE
----------------------------------------------------------------
TRUE
SQL>
Load data into the source DB:
insert into ggtest1
select (level+2000) eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename
from dual
connect by level <= 1000;
commit;
Actual output:
C:\Users\vagrant>sqlplus encvault_test/encvault_test@ggsrc04t
..
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
ENCRYPT_TAB1
GGTEST1
TABLE_NAME
--------------------------------------------------------------------------------
ENCRYPT_TAB1
GGTEST1
SQL> select count(1) from ggtest1;
COUNT(1)
----------
1999
COUNT(1)
----------
1999
SQL> select max(eid) from ggtest1;
MAX(EID)
----------
2000
MAX(EID)
----------
2000
SQL> insert into ggtest1
2 select (level+2000) eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename
3 from dual
4 connect by level <= 1000;
2 select (level+2000) eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename
3 from dual
4 connect by level <= 1000;
1000 rows created.
SQL> commit;
Commit complete.
Commit complete.
Kick off the replication which was stopped for db bounce (DV activation):
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 13> start replicat rpt2
2023-08-28T17:01:58Z INFO OGG-00975 Replicat group RPT2 starting.
2023-08-28T17:01:58Z INFO OGG-15445 Replicat group RPT2 started.
2023-08-28T17:01:58Z INFO OGG-00975 Replicat group RPT2 starting.
2023-08-28T17:01:58Z INFO OGG-15445 Replicat group RPT2 started.
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 14> start extract rxt3
2023-08-28T17:02:04Z INFO OGG-00975 Extract group RXT3 starting.
2023-08-28T17:02:04Z INFO OGG-15426 Extract group RXT3 started.
2023-08-28T17:02:04Z INFO OGG-00975 Extract group RXT3 starting.
2023-08-28T17:02:04Z INFO OGG-15426 Extract group RXT3 started.
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 15> status extract rxt3
EXTRACT RXT3: RUNNING
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 16> status replicat rpt2
REPLICAT RPT2: RUNNING
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 17>
REPLICAT RPT2: RUNNING
OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 17>
Validate the target:
SQL> conn encvault_test/encvault_test@192.168.194.11:1521/ggtgt04t
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
ENCRYPT_TAB1
GGTEST1
TABLE_NAME
--------------------------------------------------------------------------------
ENCRYPT_TAB1
GGTEST1
SQL> select count(1) from GGTEST1;
COUNT(1)
----------
1999
COUNT(1)
----------
1999
SQL> select max(eid) from ggtest1;
MAX(EID)
----------
2000
MAX(EID)
----------
2000
>>> replicat stats shows the records were discarded
...
Replicating from ENCVAULT_TEST.GGTEST1 to ENCVAULT_TEST.GGTEST1:
*** Total statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
*** Daily statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
*** Hourly statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
*** Latest statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
End of statistics.
Replicating from ENCVAULT_TEST.GGTEST1 to ENCVAULT_TEST.GGTEST1:
*** Total statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
*** Daily statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
*** Hourly statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
*** Latest statistics since 2023-08-28 18:02:03 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 1000.00
Total operations 0.00
End of statistics.
So let us try add
Example:
EXEC DBMS_MACADM.ADD_AUTH_TO_REALM('realm_name','username');
Actual:
EXEC DBMS_MACADM.ADD_AUTH_TO_REALM('realm_name','username');
Actual:
EXEC DBMS_MACADM.ADD_AUTH_TO_REALM('APP schema','GGADMIN_TGT');
before authorization addition:
SQL> select * from DVSYS.DBA_DV_REALM order by 1;
NAME ,DESCRIPTION ,AUDIT_OPTIONS,REALM_TYP,COM,INH,E, ID#,ORA,PL_
--------------------------------------------------------------------------------------------------------------------------------,----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,-------------,---------,---,---,-,----------,---,---
APP schema ,Protect APP Schema , 1,REGULAR ,NO ,NO ,Y, 5000,NO ,NO
Database Vault Account Management ,Defines the realm for administrators who create and manage database accounts and profiles. , 1,REGULAR ,NO ,NO ,Y, 6,YES,NO
Oracle Database Vault ,Defines the realm for the Oracle Database Vault schemas - DVSYS, DVF and LBACSYS where Database Vault access control configuration and roles are contained. , 1,REGULAR ,NO ,NO ,Y, 2,YES,NO
Oracle Default Component Protection Realm ,Defines the realm to protect default components of the Oracle database. , 1,REGULAR ,NO ,NO ,Y, 10,YES,NO
Oracle Default Schema Protection Realm ,Defines the realm for the Oracle Default schemas. , 1,REGULAR ,NO ,NO ,Y, 8,YES,NO
Oracle Enterprise Manager ,Defines the Enterprise Manager monitoring and management realm. , 1,REGULAR ,NO ,NO ,Y, 7,YES,NO
Oracle System Privilege and Role Management Realm ,Defines the realm to control granting of system privileges and database administrator roles. , 1,REGULAR ,NO ,NO ,Y, 9,YES,NO
7 rows selected.
SQL> select * from DVSYS.DBA_DV_REALM_AUTH order by 1,4,2;
REALM_NAME ,COM,INH,GRANTEE ,AUTH_RULE_SET_NAME ,AUTH_OPTIONS ,COM,INH
--------------------------------------------------,---,---,--------------------,------------------------------,------------------------------,---,---
Database Vault Account Management ,NO ,NO ,DV_ACCTMGR , ,Owner ,NO ,NO
Oracle Database Vault ,NO ,NO ,DVSYS , ,Owner ,NO ,NO
Oracle Database Vault ,NO ,NO ,DV_ADMIN , ,Participant ,NO ,NO
Oracle Database Vault ,NO ,NO ,DV_OWNER , ,Owner ,NO ,NO
Oracle Database Vault ,NO ,NO ,LBACSYS , ,Owner ,NO ,NO
Oracle Default Component Protection Realm ,NO ,NO ,SYS , ,Owner ,NO ,NO
Oracle Default Component Protection Realm ,NO ,NO ,SYSTEM , ,Owner ,NO ,NO
Oracle Default Schema Protection Realm ,NO ,NO ,SYS , ,Owner ,NO ,NO
Oracle Enterprise Manager ,NO ,NO ,DBSNMP , ,Owner ,NO ,NO
Oracle Enterprise Manager ,NO ,NO ,SYSTEM , ,Owner ,NO ,NO
Oracle System Privilege and Role Management Realm ,NO ,NO ,SYS , ,Owner ,NO ,NO
11 rows selected.
after authorization addition:
SQL> select * from DVSYS.DBA_DV_REALM_AUTH order by 1,4,2;
REALM_NAME ,COM,INH,GRANTEE ,AUTH_RULE_SET_NAME ,AUTH_OPTIONS ,COM,INH
--------------------------------------------------,---,---,--------------------,------------------------------,------------------------------,---,---
APP schema ,NO ,NO ,GGADMIN_TGT , ,Participant ,NO ,NO << GGADMIN_TGT is authorized now.
Database Vault Account Management ,NO ,NO ,DV_ACCTMGR , ,Owner ,NO ,NO
Oracle Database Vault ,NO ,NO ,DVSYS , ,Owner ,NO ,NO
Oracle Database Vault ,NO ,NO ,DV_ADMIN , ,Participant ,NO ,NO
Oracle Database Vault ,NO ,NO ,DV_OWNER , ,Owner ,NO ,NO
Oracle Database Vault ,NO ,NO ,LBACSYS , ,Owner ,NO ,NO
Oracle Default Component Protection Realm ,NO ,NO ,SYS , ,Owner ,NO ,NO
Oracle Default Component Protection Realm ,NO ,NO ,SYSTEM , ,Owner ,NO ,NO
Oracle Default Schema Protection Realm ,NO ,NO ,SYS , ,Owner ,NO ,NO
Oracle Enterprise Manager ,NO ,NO ,DBSNMP , ,Owner ,NO ,NO
Oracle Enterprise Manager ,NO ,NO ,SYSTEM , ,Owner ,NO ,NO
Oracle System Privilege and Role Management Realm ,NO ,NO ,SYS , ,Owner ,NO ,NO
12 rows selected.
Now Replication:
Source data load:
SQL> conn encvault_test/encvault_test@ggsrc04t
Connected.
SQL> select max(eid) from ggtest1;
MAX(EID)
----------
3000
SQL> insert into ggtest1
2 select (level+3000) eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename
3 from dual
4 connect by level <= 1000;
1000 rows created.
MAX(EID)
----------
3000
SQL> insert into ggtest1
2 select (level+3000) eid,mod(level,6) pincode,rpad('X',mod(level,5)) branchcode,lpad('A',mod(level,5))||rpad('B',mod(level,5)) ename
3 from dual
4 connect by level <= 1000;
1000 rows created.
SQL> commit;
Commit complete.
Commit complete.
SQL> select count(1) from ggtest1;
COUNT(1)
----------
3999
SQL> select max(eid) from ggtest1;
MAX(EID)
----------
4000
SQL>
SQL> disc
COUNT(1)
----------
3999
SQL> select max(eid) from ggtest1;
MAX(EID)
----------
4000
SQL>
SQL> disc
Target Side:
SQL> conn encvault_test/encvault_test@192.168.194.11:1521/ggtgt04t
Connected.
SQL> select max(eid) from ggtest1;
MAX(EID)
----------
4000 <<< the last 1000 records (the records that were added after authorization) flew without issues
SQL> select count(1) from ggtest1;
COUNT(1)
----------
2999 <<< from 1999 it came to 2999
COUNT(1)
----------
2999 <<< from 1999 it came to 2999
This closes this quick blog. So it is better to realm autorization before starting the replication.
YouTube which pushed me to create this blog:
Thanks
