Let us learn Oracle Database Administration skills: June 2023

Wednesday, June 28, 2023

Oracle 19c Mixed Mode Auditing - Oracle's Default auditing - do they work?

Dears,

Its a quick blog, I wanted to check weather oracle's default AUDITING works as it is written in the document?

I picked 11.2 guide Oracle 11.2 Audit Document & tested the results in 19c.

Interestingly with my tests..

1. SYS user drop command in a schema in PDB is properly captured in OS Audit Trail (as per audit_sys_operation param)

2. ALTER TABLE command from SYS and APPLICATION (the owner of the object) are trapped properly in unified_audit_trail view

3. DROP TABLE command from PDBADMIN (drop any table by default isnt granted to PDBADMIN) targeting the application object was trapped in unified_audit_trail

So all worked as expected. Please see below...

OS audit trail for the drop run by SYS:

Audit file: /u01/app/oracle/admin/ORA19C/adump/F8B08577DBE37CF5E05305C2A8C0FBD0/ORA19C_ora_11276_20230628073917822599918146.aud

which is <ADUMP>/<PDB GUID>/*ora* process id trace

Wed Jun 28 07:57:43 2023 +01:00

LENGTH : '295'

ACTION :[33] 'drop table opdb1_public_user.test'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[9] '793483480'

SESSIONID:[10] '4294967295'

USERHOST:[20] 'vcentos79-oracle-sa1'

CLIENT ADDRESS:[0] ''

ACTION NUMBER:[2] '12'

The commands ran were:

As SYS:

SQL> alter table opdb1_public_user.test add (tabcreate varchar2(20));

Table altered.

SQL> drop table opdb1_public_user.test;

Table dropped.

As Application user:

SQL> alter table opdb1_public_user.test add (tabcreate1 varchar2(10));

Table altered.

SQL> alter table test add (tabcreate2 varchar2(10));

Table altered.

SQL>

As PDBADMIN:

SQL> drop table opdb1_public_user.test purge;

Table dropped.

SQL>

The query to get information on above commands:

set lines 1000

set pages 20000

set colsep ,

col action_name for a12

col OS_USERNAME for a20

col USERHOST for a20

col dbusername for a20

col subclient_program_name for a30

col object_schema for a20

col object_name for a40

col subsql_text for a30

col system_privilege_used for a20

col audit_option for a20

col object_privileges for a20

select action_name

,return_code

,to_char(cast(EVENT_TIMESTAMP as date),'DD/MON/YYYY HH24:MI:SS') evnttime

,SESSIONID

,OS_USERNAME

,USERHOST

,dbusername

,substr(client_program_name,1,30) subclient_program_name

,object_schema

,object_name

,substr(sql_text,1,20) subsql_text

,system_privilege_used

,audit_option

,object_privileges

from unified_audit_trail

order by 1,3;

To filter on object name, use below..

select action_name

,return_code

,to_char(cast(EVENT_TIMESTAMP as date),'DD/MON/YYYY HH24:MI:SS') evnttime

,SESSIONID

,OS_USERNAME

,USERHOST

,dbusername

,substr(client_program_name,1,30) subclient_program_name

,object_schema

,object_name

,substr(sql_text,1,20) subsql_text

,system_privilege_used

,audit_option

,object_privileges

from unified_audit_trail

where object_name='TEST'

order by 1,3

Thanks

Sunday, June 25, 2023

Oracle GoldenGate Microservice Architecture - adminclient usage - control and view extract & replicat process

In this blog, we will try to get the status & perform stop, start of the extract and replicat process of the OGG 21.3 microservice architecture

Connect to OGG Deployment using CLI (adminclient):

Environment variables:

export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1

export PATH=$PATH:$OGG_HOME/bin

If you use certificates, then use OGG_CLIENT_TLS_CAPATH in the path.

enter the below command to start adminclient:

adminclient

once started:

[oracle@vcentos79-oracle-sa1 ~]$ adminclient

Oracle GoldenGate Administration Client for Oracle

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047

Oracle Linux 7, x64, 64bit (optimized) on Jul 28 2021 12:32:46

Operating system character set identified as UTF-8.

OGG (not connected) 1>

Connect to the deployment: Remember the name of our deployment is oggdep01

If you want to how to retrieve the deployment list:

refer to: oracle golden gate 21.3 MA - retrieve deployment list

Command in OGG prompt to connect to a deployment:

connect <url for the admin service of the deployment> deployment <deployment name> user <deployment admin user> password <deployment admin password>

Actual command:

connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

Actual Output:

OGG (not connected) 1> connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

OGG (http://127.0.0.1:9011 oggdep01) 2>

Now let us try to check the extract and replicat group names:

Findout extract group name:

Command in OGG prompt:

INFO EXTRACT *

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 3> INFO EXTRACT *

Extract EXTS Last Started 2023-06-21 07:21 Status ABENDED

Description src

Checkpoint Lag 00:00:00 (updated 103:52:15 ago)

Log Read Checkpoint Oracle Integrated Redo Logs

2023-06-21 09:07:19

SCN 0.3047276 (3047276)

Encryption Profile LocalWallet

OGG (http://127.0.0.1:9011 oggdep01) 4>

If more detailed output is needed:

Command in OGG prompt: INFO EXTRACT * DETAIL

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 4> INFO EXTRACT * DETAIL

Extract EXTS Last Started 2023-06-21 07:21 Status ABENDED <<< EXTS is the extract group name

Description src

Checkpoint Lag 00:00:00 (updated 103:52:43 ago)

Log Read Checkpoint Oracle Integrated Redo Logs

2023-06-21 09:07:19

SCN 0.3047276 (3047276)

Encryption Profile LocalWallet

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

src/es 2 7202 500 EXTTRAIL

Integrated Extract outbound server first scn: Unavailable.Need DBLOGIN.

Integrated Extract outbound server filtering start scn: Unavailable.Need DBLOGIN.

Extract Source Begin End

Not Available 2023-06-18 20:21 2023-06-21 09:07

Not Available 2023-06-18 20:21 2023-06-18 21:42

Not Available 2023-06-18 20:21 2023-06-18 20:53

Not Available * Initialized * 2023-06-18 20:21

Current directory /

Report file /oggdata/oggdep01/var/lib/report/EXTS.rpt

Parameter file /oggdata/oggdep01/etc/conf/ogg/EXTS.prm

Checkpoint file /oggdata/oggdep01/var/lib/checkpt/EXTS.cpe

Process file /oggdata/oggdep01/var/run/EXTS.pce

Error log /oggdata/oggdep01/var/log/ggserr.log

OGG (http://127.0.0.1:9011 oggdep01) 5>

To start the abended EXTRACT:

Ensure the candidate DB and listener are up and running, otherwise extract will keep abending.

Command in OGG prompt:

check status: status extract exts

start extract: start extract exts

check status: status extract exts

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 13> status extract exts

EXTRACT EXTS: ABENDED

OGG (http://127.0.0.1:9011 oggdep01) 14> start extract exts

2023-06-25T16:06:30Z INFO OGG-00975 Extract group EXTS starting.

2023-06-25T16:06:30Z INFO OGG-15426 Extract group EXTS started.

OGG (http://127.0.0.1:9011 oggdep01) 15> status extract exts

EXTRACT EXTS: RUNNING

OGG (http://127.0.0.1:9011 oggdep01) 16>

To start the replicat process:

Findout the replicate group name:

command in OGG prompt:

info replicate *

info replicate * detail

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 16> info replicat *

Replicat REPT Last Started 2023-06-21 07:24 Status ABENDED <<< REPT is the replication group name

Description tgt

Parallel

Checkpoint Lag 00:00:00 (updated 103:59:21 ago)

Log Read Checkpoint File src/es000000002

2023-06-21 09:08:48.625516 RBA 7202

Settings Profile Default

Encryption Profile LocalWallet

OGG (http://127.0.0.1:9011 oggdep01) 17>

Detailed output:

OGG (http://127.0.0.1:9011 oggdep01) 17> info replicat * detail

Replicat REPT Last Started 2023-06-21 07:24 Status ABENDED

Description tgt

Parallel

Checkpoint Lag 00:00:00 (updated 103:59:26 ago)

Log Read Checkpoint File src/es000000002

2023-06-21 09:08:48.625516 RBA 7202

Settings Profile Default

Encryption Profile LocalWallet

Current Log BSN value: 2992415

Low Watermark CSN value: 3034995

High Watermark CSN value: 3034995

Current directory /

Report file /oggdata/oggdep01/var/lib/report/REPT.rpt

Parameter file /oggdata/oggdep01/etc/conf/ogg/REPT.prm

Checkpoint file /oggdata/oggdep01/var/lib/checkpt/REPT.cpr

Checkpoint table GGADMIN.GG_CHECKPOINT

Process file /oggdata/oggdep01/var/run/REPT.pcr

Error log /oggdata/oggdep01/var/log/ggserr.log

OGG (http://127.0.0.1:9011 oggdep01) 18>

Now to start the replicat process:

Ensure the target db and listener are up.

OGG (http://127.0.0.1:9011 oggdep01) 18> status replicat REPT

REPLICAT REPT: ABENDED

OGG (http://127.0.0.1:9011 oggdep01) 19> start replicat REPT

2023-06-25T16:11:12Z INFO OGG-00975 Replicat group REPT starting.

2023-06-25T16:11:12Z INFO OGG-15445 Replicat group REPT started.

OGG (http://127.0.0.1:9011 oggdep01) 20> status replicat REPT << Wait for few secs

REPLICAT REPT: ABENDED

OGG (http://127.0.0.1:9011 oggdep01) 21> status replicat REPT << Wait for few secs, see now the replicat is alive.

REPLICAT REPT: RUNNING

OGG (http://127.0.0.1:9011 oggdep01) 22>

Simillarly to stop the extract and replicat just replace start with stop.

Command for extract:

stop extract exts

status extract exts

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 22> stop extract exts

2023-06-25T16:13:35Z INFO OGG-08100 Sending STOP request to Extract group EXTS.

2023-06-25T16:13:35Z INFO OGG-02964 Extract group EXTS is down (gracefully).

OGG (http://127.0.0.1:9011 oggdep01) 23> status extract exts

EXTRACT EXTS: STOPPED

OGG (http://127.0.0.1:9011 oggdep01) 24>

Command for replicat:

stop replicat REPT

status replicat REPT

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 24> stop replicat REPT

2023-06-25T16:14:26Z INFO OGG-08100 Sending STOP request to Replicat group REPT.

2023-06-25T16:14:26Z INFO OGG-02965 Replicat group REPT is down (gracefully).

OGG (http://127.0.0.1:9011 oggdep01) 25> status replicat REPT

REPLICAT REPT: STOPPED

OGG (http://127.0.0.1:9011 oggdep01) 26>

Notice the image below post extract and replicat are gracefully stopped:

To view the parameters of the extract and replicat:

View params:

Command on OGG: view params <group name>

group name can be of extract's or replicat's.

Extract param: view params exts

Actual command output:

OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 28> view params exts

EXTRACT exts

USERIDALIAS cggadmin DOMAIN OracleGoldenGate

EXTTRAIL src/es

SOURCECATALOG OPDB1

DDL INCLUDE MAPPED

TABLE OPDB1_PUBLIC_USER.*;

OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 29>

Replicat param: view params rept

Actual command output:

OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 29> view params rept

REPLICAT rept

USERIDALIAS ggadmin_DBCASLNT DOMAIN OracleGoldenGate

--DDL EXCLUDE ALL

DDLERROR default discard

REPERROR (default,discard)

DDLOPTIONS REPORT

SOURCECATALOG OPDB1

MAP OPDB1_PUBLIC_USER.*, TARGET OPDB1_PUBLIC_USER.*;

OGG (http://127.0.0.1:9011 oggdep01 as cggadmin@ORA19C/CDB$ROOT) 30>

Here we close this blog page.

Thank you!

Oracle Golden Gate 21.3 Microservice Architecture - Rest API interaction using cURL - Retrieve the details on a deployment

In this blog, let us see how we can retrieve information on a oracle golden gate 21.3 MA deployment

Previous blog, we noticed how to get the list of deployments:
oracle golden gate 21.3 MA - retrieve deployment list

We will see how to get more details about the candidate deployment:

Candidate deployment: oggdep01

Command:

We add the name of the deployment to the command we referecned to list the deployments as you see below..

curl -u ggsca:ggsca \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-X GET http://127.0.0.1:9100/services/v2/deployments/oggdep01 | jq

Actual output:

Let us see how we can retrieve the services part of the deployment now:

Command:
curl -u ggsca:ggsca \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-X GET http://127.0.0.1:9100/services/v2/deployments/oggdep01/services | jq

Actual output:

[oracle@vcentos79-oracle-sa1 ~]$ curl -u ggsca:ggsca \
> -H "Content-Type: application/json" \
> -H "Accept: application/json" \
> -X GET http://127.0.0.1:9100/services/v2/deployments/oggdep01/services | jq
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1748 100 1748 0 0 16775 0 --:--:-- --:--:-- --:--:-- 16807
{
"$schema": "api:standardResponse",
"links": [
{
"rel": "canonical",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services",
"mediaType": "application/json"
},
{
"rel": "self",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services",
"mediaType": "application/json"
},
{
"rel": "describedby",
"href": "http://127.0.0.1:9100/services/v2/metadata-catalog/deploymentServices",
"mediaType": "application/schema+json"
}
],
"messages": [],
"response": {
"$schema": "ogg:collection",
"items": [
{
"links": [
{
"rel": "parent",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services/adminsrvr",
"mediaType": "application/json"
}
],
"$schema": "ogg:collectionItem",
"name": "adminsrvr"
},
{
"links": [
{
"rel": "parent",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services/distsrvr",
"mediaType": "application/json"
}
],
"$schema": "ogg:collectionItem",
"name": "distsrvr"
},
{
"links": [
{
"rel": "parent",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services/pmsrvr",
"mediaType": "application/json"
}
],
"$schema": "ogg:collectionItem",
"name": "pmsrvr"
},
{
"links": [
{
"rel": "parent",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services",
"mediaType": "application/json"
},
{
"rel": "canonical",
"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01/services/recvsrvr",
"mediaType": "application/json"
}
],
"$schema": "ogg:collectionItem",
"name": "recvsrvr"
}
]
}
}
[oracle@vcentos79-oracle-sa1 ~]$

We will mostly be interested in admin services, so let us collect details about the admin service now:

Command:

curl -u ggsca:ggsca \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-X GET http://127.0.0.1:9100/services/v2/deployments/oggdep01/services/adminsrvr | jq

Actual command output:

This completes this blog. Next blog we explore adminclient.

Oracle Golden Gate 21.3 Microservice Architecture - Rest API interaction using cURL - Retrieve the deployment list

In this blog, we will retrieve the deployment list using REST API (cURL) from our service manager:

What you need:

cURL (mostly installed in linux)

jq or mjson.tool from python

Assumption:

You already have installed OGG, made your first OGG deployment. If not refer to the below urls

OGG 21.3 MA - silent mode - installation

OGG 21.3 MA - silent mode - deployment

Let us retrieve the deployment list using cURL now:

Oracle ref: oracle reference note

Command:

curl -u ggsca:ggsca \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-X GET http://127.0.0.1:9100/services/v2/deployments

Command explaination:

-u <username>:<password> - is the service manager's admin username and password

Keep ASIS or dont change: <since this is the supported format for this request type>

-H "Content-Type: application/json" \
-H "Accept: application/json" \

-X mentions the type of request (CURD) : GET (retrieving info)

url : http://127.0.0.1:9100/services/<version>/deployments [allowed value: v2]

Actual output:

[oracle@vcentos79-oracle-sa1 ~]$curl -u ggsca:ggsca -H "Content-Type: application/json" -H "Accept: application/json" -X GET http://127.0.0.1:9100/services/v2/deployments

{"$schema":"api:standardResponse","links":[{"rel":"canonical","href":"http://127.0.0.1:9100/services/v2/deployments","mediaType":"application/json"},{"rel":"self","href":"http://127.0.0.1:9100/services/v2/deployments","mediaType":"application/json"},{"rel":"describedby","href":"http://127.0.0.1:9100/services/v2/metadata-catalog/versionDeployments","mediaType":"application/schema+json"}],"messages":[],"response":{"$schema":"ogg:collection","items":[{"links":[{"rel":"parent","href":"http://127.0.0.1:9100/services/v2/deployments","mediaType":"application/json"},{"rel":"canonical","href":"http://127.0.0.1:9100/services/v2/deployments/ServiceManager","mediaType":"application/json"}],"$schema":"ogg:collectionItem","name":"ServiceManager"},{"links":[{"rel":"parent","href":"http://127.0.0.1:9100/services/v2/deployments","mediaType":"application/json"},{"rel":"canonical","href":"http://127.0.0.1:9100/services/v2/deployments/oggdep01","mediaType":"application/json"}],"$schema":"ogg:collectionItem","name":"oggdep01"}]}}

To make it more readable:

A) using python -mjson.tool:

curl -u ggsca:ggsca \

-H "Content-Type: application/json" \

-H "Accept: application/json" \

-X GET http://127.0.0.1:9100/services/v2/deployments | python -mjson.tool

Actual output:

[oracle@vcentos79-oracle-sa1 ~]$ curl -u ggsca:ggsca \

> -H "Content-Type: application/json" \

> -H "Accept: application/json" \

> -X GET http://127.0.0.1:9100/services/v2/deployments | python -mjson.tool

% Total % Received % Xferd Average Speed Time Time Time Current

Dload Upload Total Spent Left Speed

100 1024 100 1024 0 0 10125 0 --:--:-- --:--:-- --:--:-- 10138

{

"$schema": "api:standardResponse",

"links": [

{

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json",

"rel": "canonical"

{

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json",

"rel": "self"

{

"href": "http://127.0.0.1:9100/services/v2/metadata-catalog/versionDeployments",

"mediaType": "application/schema+json",

"rel": "describedby"

}

"messages": [],

"response": {

"$schema": "ogg:collection",

"items": [

{

"$schema": "ogg:collectionItem",

"links": [

{

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json",

"rel": "parent"

{

"href": "http://127.0.0.1:9100/services/v2/deployments/ServiceManager",

"mediaType": "application/json",

"rel": "canonical"

}

"name": "ServiceManager"

{

"$schema": "ogg:collectionItem",

"links": [

{

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json",

"rel": "parent"

{

"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01",

"mediaType": "application/json",

"rel": "canonical"

}

"name": "oggdep01"

}

]

}

[oracle@vcentos79-oracle-sa1 ~]$

B) Other option (jq):

curl -u ggsca:ggsca \

-H "Content-Type: application/json" \

-H "Accept: application/json" \

-X GET http://127.0.0.1:9100/services/v2/deployments | jq

[oracle@vcentos79-oracle-sa1 ~]$ curl -u ggsca:ggsca \

> -H "Content-Type: application/json" \

> -H "Accept: application/json" \

> -X GET http://127.0.0.1:9100/services/v2/deployments | jq

% Total % Received % Xferd Average Speed Time Time Time Current

Dload Upload Total Spent Left Speed

100 1024 100 1024 0 0 13097 0 --:--:-- --:--:-- --:--:-- 13298

{

"$schema": "api:standardResponse",

"links": [

{

"rel": "canonical",

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json"

{

"rel": "self",

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json"

{

"rel": "describedby",

"href": "http://127.0.0.1:9100/services/v2/metadata-catalog/versionDeployments",

"mediaType": "application/schema+json"

}

"messages": [],

"response": {

"$schema": "ogg:collection",

"items": [

{

"links": [

{

"rel": "parent",

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json"

{

"rel": "canonical",

"href": "http://127.0.0.1:9100/services/v2/deployments/ServiceManager",

"mediaType": "application/json"

}

"$schema": "ogg:collectionItem",

"name": "ServiceManager"

{

"links": [

{

"rel": "parent",

"href": "http://127.0.0.1:9100/services/v2/deployments",

"mediaType": "application/json"

{

"rel": "canonical",

"href": "http://127.0.0.1:9100/services/v2/deployments/oggdep01",

"mediaType": "application/json"

}

"$schema": "ogg:collectionItem",

"name": "oggdep01"

}

]

}

[oracle@vcentos79-oracle-sa1 ~]$

Notice we have 2 deployments:

http://127.0.0.1:9100/services/v2/deployments/ServiceManager

http://127.0.0.1:9100/services/v2/deployments/oggdep01

We are interested in oggdep01

Thank you!

Wednesday, June 21, 2023

Oracle Golden Gate 21.3 Micorservice support for DDL replication

In this blog we will see how Oracle Golden 21.3 Microservice supports DDL replication

Verify with our current Golden Gate replication setup, if DDL operations are replicated.

Source parameters:
Type: INTEGRATED
EXTRACT exts
USERIDALIAS cggadmin DOMAIN OracleGoldenGate
EXTTRAIL src/es
SOURCECATALOG OPDB1
DDL INCLUDE MAPPED
TABLE OPDB1_PUBLIC_USER.*;

Receiver parameters:
Type: Parallel Nonintegerated
REPLICAT rept
USERIDALIAS ggadmin_DBCASLNT DOMAIN OracleGoldenGate
--DDL EXCLUDE ALL
DDLERROR default discard
REPERROR (default,discard)
DDLOPTIONS REPORT
SOURCECATALOG OPDB1
MAP OPDB1_PUBLIC_USER.*, TARGET OPDB1_PUBLIC_USER.*;

Operation 1: Create table

Source PDB:

SQL> create table ggreptst_21jun23 as select 1 as id from dual;
Table created.

SQL> desc ggreptst_21jun23
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER

SQL> select * from ggreptst_21jun23;
ID
----------
1
SQL>

SQL> select table_name,num_rows,last_analyzed from user_Tables order by 1;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
GGREPTST_21JUN23 1 21/JUN/2023 07:31:25 <<<< this one is the true complete GG flyby
JT1 25000000 14/MAY/2023 20:25:13 <<<< until here expdp/impdp (bottom up), remember we inserted 1 record to test the replication initially
JT2 25000000 14/MAY/2023 20:25:47
T1 2 14/MAY/2023 16:17:44
TESTIDXBLOAT 960004 14/MAY/2023 16:18:10
TESTIDXBLOAT_TMP 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP2 9999 10/MAY/2023 23:34:58
TESTIDXBLOAT_TMP3 9999 10/MAY/2023 23:41:42
TESTIDXBLOAT_TMP4 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP5 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP6 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP7 9999 11/MAY/2023 00:09:52
12 rows selected.

SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
GETSEQFLUSH PROCEDURE 18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
GGREPTST_21JUN23 TABLE 21/JUN/2023 07:31:25 21/JUN/2023 07:31:25 <<< our table,created to test ddl
JT1 TABLE 14/MAY/2023 16:46:30 14/MAY/2023 16:46:30
JT2 TABLE 14/MAY/2023 17:05:24 14/MAY/2023 17:05:24
MOVETARGETHWM PROCEDURE 18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
REPLICATESEQUENCE PROCEDURE 18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
SEQTRACE PROCEDURE 18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
T1 TABLE 24/APR/2023 19:49:25 24/APR/2023 19:49:25
TESTIDXBLOAT TABLE 10/MAY/2023 23:06:40 10/MAY/2023 23:10:44
TESTIDXBLOAT_IN INDEX 10/MAY/2023 23:10:42 10/MAY/2023 23:10:42
TESTIDXBLOAT_PK INDEX 10/MAY/2023 23:07:58 10/MAY/2023 23:07:58
TESTIDXBLOAT_TMP TABLE 10/MAY/2023 23:28:25 10/MAY/2023 23:28:25
TESTIDXBLOAT_TMP2 TABLE 10/MAY/2023 23:34:58 10/MAY/2023 23:34:58
TESTIDXBLOAT_TMP3 TABLE 10/MAY/2023 23:41:42 10/MAY/2023 23:41:42
TESTIDXBLOAT_TMP4 TABLE 10/MAY/2023 23:49:06 10/MAY/2023 23:49:06
TESTIDXBLOAT_TMP5 TABLE 10/MAY/2023 23:53:15 10/MAY/2023 23:53:15
TESTIDXBLOAT_TMP6 TABLE 11/MAY/2023 00:06:26 11/MAY/2023 00:06:26
TESTIDXBLOAT_TMP7 TABLE 11/MAY/2023 00:09:52 11/MAY/2023 00:09:52
UPDATESEQUENCE PROCEDURE 18/JUN/2023 12:21:32 18/JUN/2023 12:21:32 <<< not sure why this object is created here, I have ggadmin schema
19 rows selected.

SQL>

Target NonCDB:

SQL> desc GGREPTST_21JUN23
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL>

SQL> select * from GGREPTST_21JUN23;
ID
----------
1

SQL> select table_name,num_rows,last_analyzed from user_Tables order by 1;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
GGREPTST_21JUN23 1 21/JUN/2023 07:31:54 <<<< this one is the true complete GG flyby, see the last analyzed timestamps difference
JT1 25000000 14/MAY/2023 20:25:13
JT2 25000000 14/MAY/2023 20:25:47
T1 2 14/MAY/2023 16:17:44
TESTIDXBLOAT 960004 14/MAY/2023 16:18:10
TESTIDXBLOAT_TMP 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP2 9999 10/MAY/2023 23:34:58
TESTIDXBLOAT_TMP3 9999 10/MAY/2023 23:41:42
TESTIDXBLOAT_TMP4 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP5 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP6 9999 14/MAY/2023 16:18:22
TESTIDXBLOAT_TMP7 9999 11/MAY/2023 00:09:52
12 rows selected.

SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
GETSEQFLUSH PROCEDURE 18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
GGREPTST_21JUN23 TABLE 21/JUN/2023 07:31:28 21/JUN/2023 07:31:28 <<< our DDL test table
JT1 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
JT2 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
MOVETARGETHWM PROCEDURE 18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
REPLICATESEQUENCE PROCEDURE 18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
SEQTRACE PROCEDURE 18/JUN/2023 21:17:43 18/JUN/2023 21:17:43 <<< not sure why this object is created here, I have ggadmin schema
T1 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:17:48
TESTIDXBLOAT_IN INDEX 18/JUN/2023 21:17:44 18/JUN/2023 21:17:47
TESTIDXBLOAT_PK INDEX 18/JUN/2023 21:17:48 18/JUN/2023 21:17:48
TESTIDXBLOAT_TMP TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP2 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP3 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP4 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP5 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP6 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
TESTIDXBLOAT_TMP7 TABLE 18/JUN/2023 21:04:36 18/JUN/2023 21:04:36
UPDATESEQUENCE PROCEDURE 18/JUN/2023 21:17:43 18/JUN/2023 21:17:44 <<< not sure why this object is created here, I have ggadmin schema
19 rows selected.

Operation 2: Add a column to GGREPTST_21JUN23 table

Source table:

SQL> alter table GGREPTST_21JUN23 add (name varchar2(100));
Table altered.
SQL>

07:52:54 SQL> desc GGREPTST_21JUN23
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
NAME VARCHAR2(100)

07:52:58 SQL> select * from GGREPTST_21JUN23;
ID NAME
---------- --------------------
1
Elapsed: 00:00:00.01
07:54:09 SQL>

07:55:46 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23 TABLE 21/JUN/2023 07:31:25 21/JUN/2023 07:51:43 <<< so this is the DDL time

Target table:

07:53:04 SQL> desc GGREPTST_21JUN23
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
NAME VARCHAR2(100)

07:55:07 SQL> select * from GGREPTST_21JUN23;
ID NAME
---------- ----------------------------------------
1
Elapsed: 00:00:00.00
07:55:11 SQL>

07:55:36 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23 TABLE 21/JUN/2023 07:31:28 21/JUN/2023 07:51:47 <<< so this is the DDL time
It looks like we have a 4sec lag; which is quite good.

Operation 3: Truncate the table

Source:

07:57:18 SQL> truncate table GGREPTST_21JUN23;
Table truncated.
Elapsed: 00:00:00.20

07:57:25 SQL> select * from GGREPTST_21JUN23;
no rows selected
Elapsed: 00:00:00.03
07:57:39 SQL>

07:57:39 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23 TABLE 21/JUN/2023 07:31:25 21/JUN/2023 07:57:25 <<< last ddl time

Target:

07:55:41 SQL> select * from GGREPTST_21JUN23;
no rows selected
Elapsed: 00:00:00.00
07:57:32 SQL>

07:57:32 SQL> select object_name,object_type,created,last_ddl_time from user_objects order by 1;
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME
------------------------------ ------------------------------ -------------------- --------------------
..
GGREPTST_21JUN23 TABLE 21/JUN/2023 07:31:28 21/JUN/2023 07:57:28 <<< last ddl time
this time 3secs lag. Note my machines are in sync in terms of time :)

Operation 4: Drop the table

Source:

07:59:02 SQL> drop table GGREPTST_21JUN23 purge;
Table dropped.
Elapsed: 00:00:00.78
08:00:11 SQL>

08:00:32 SQL> select * from GGREPTST_21JUN23;
select * from GGREPTST_21JUN23
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.06

08:00:41 SQL> select count(1) from user_objects where upper(object_name)='GGREPTST_21JUN23';
COUNT(1)
----------
0
Elapsed: 00:00:00.69
08:01:18 SQL>

Target:

08:00:27 SQL> select * from GGREPTST_21JUN23;
select * from GGREPTST_21JUN23
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.00

08:00:46 SQL> select count(1) from user_objects where upper(object_name)='GGREPTST_21JUN23';
COUNT(1)
----------
0
Elapsed: 00:00:00.14
08:01:10 SQL>

How does it look in ADMIN console:

Extract:

Replicat:

So this looks fine, we created a table, modified it, truncated it, dropped it.

All of them reflected fine with our current replication setting.

Next Step: We perform some stress test on GG Replication.