Oracle 19C Golden Gate Installation step by step on VM

 Hi  All, 

I am sharing in details step by step oracle 19c Golden Gate installation on VM , 

pre-requisites ::  in my lab i have already install oracle 19C database software on both target and source side.

configuring the listeners on both the nodes

unlocked the all locked accounts on both the nodes.

Lab setup :

i have created 2 VM with OEL 7 OS and Oracle 19C Database 

downloaded the 19C Golden Gate software and copied to both the servers and unzipped.

[oracle@ggnode1 Disk1]$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 10710 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 5056 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2026-05-10_12-54-58AM. Please wait ...[oracle@ggnode1 Disk1]$ You can find the log of this install session at:

 /u01/app/oraInventory/logs/installActions2026-05-10_12-54-58AM.log

[oracle@ggnode1 Disk1]$

Installation start from node1 :

> unzipped the software and starting installation of Oracle 19c Golden Gate software

select 19C option click next

click Install 

Oracle 19c Golden Gate software installation completed successfully.

Install the 19C Golden Gate software in node2

enter the target location 

click on Install 

click on close .

This section completes the Installation of Goldengate Software on Both the target and source nodes .

 we will share continues knowledge on Goldengate.

Thanks,

Srini

How to Troubleshoot Long running SQL In Oracle 19C Database Step by step Lab

 Hi All,

In this post i will show how to Troubleshoot Long running SQL In Oracle 19C Database Step by step Lab, with explanation.

Most of the times we will receive complains from application team,saying that ,  as till yesterday query execution was  right and all of suddenly they started observing lag in execution or and some cases not even giving any result. 

as a DBA when we check from our end, mostly we will see plan change and that is due many reasons. 

Below are few checks DBA can try to find why sql plan got changed. 

Though the Oracle Optimizer, you can just give hint if its a small query and you understand the execution flow as well as you know the data. 

But for many large queries its not possible to fix with hint, you have to live with Oracle Optimizer to decide the SQL plan. 

You can also forcefully map any plan to particular SQL ID, but that option need to consider carefully.

Why Plan got changed? or Why SQL is slow check few option mentioned below.

Course if action as DBA we should check below 11 steps one by one, all steps are not mandatory but as per the issue troubleshoot, we have to check until the issue cause and fix identified.

Check for any stale statistics.

col TABLE_NAME for a30

col PARTITION_NAME for a20

col SUBPARTITION_NAME for a20

select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

Next 

Check any invalid index/Partition : 

col TABLE_NAME for a30

oracle@ggnode1>  select owner,index_name,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STATUS from dba_indexes where status not in ('VALID','N/A');

oracle@ggnode1>  select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,LAST_ANALYZED,STATUS from dba_ind_partitions where status <> 'USABLE';

Check free memory shared pool area. Check too much hard parsing.

oracle@ggnode1> select * from (select SQL_ID,PARSING_SCHEMA_NAME, count(1) from v$sql group by SQL_ID,PARSING_SCHEMA_NAME order by  3 desc,2) where rownum<=10;

Wait/ Blocking analysis.

 Display blocked session and their blocking session details.

SELECT sid, serial#, blocking_session_status, blocking_session FROM   v$session WHERE  blocking_session IS NOT NULL;

Display the resource or event the session is waiting for more than 1 minutes

SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait FROM   v$session where (seconds_in_wait/1000000) > 60 ORDER BY sid;

select sid,seq#,event,state,SECONDS_IN_WAIT from v$session_wait where SECONDS_IN_WAIT > 60;

Monitor Top Waiting Event Using Active Session History (ASH)

SELECT h.event, SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)" FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour AND h.sql_id = SQL.sql_id AND h.user_id = u.user_id AND h.event# = e.event# GROUP BY h.event ORDER BY SUM(h.wait_time + h.time_waited) DESC;

Tablespace Usage

Monitor Overall Oracle Tablespace

SELECT d.STATUS "Status",

d.tablespace_name "Name",

d.contents "Type",

d.extent_management "Extent Management",

d.initial_extent "Initial Extent",

TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",

TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",

TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",

TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MaxSize (M)",

TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00') "Used % of Max"

FROM sys.dba_tablespaces d,

(SELECT tablespace_name,

SUM(bytes) bytes,

SUM(decode(autoextensible,'NO',bytes,'YES',maxbytes))

maxbytes FROM dba_data_files GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space

GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

ORDER BY 10 DESC;

Check for Memory parameters usage

Estimation Of Shared Memory Pool Size vs. Time Saved 

SELECT shared_pool_size_for_estimate "Pool Size (MB)",

estd_lc_size "Lib Cache Size (MB)",

estd_lc_time_saved/1000000 "Lib Cache Time Saved (Sec)"

FROM v$shared_pool_advice;

Estimation Of Buffer Cache Size vs. Physical Reads

SELECT size_for_estimate "Cache Size (MB)",

buffers_for_estimate "Buffers",

estd_physical_read_factor "Estd Phys|Read Factor",

estd_physical_reads "Estd Phys| Reads"

FROM v$db_cache_advice

WHERE name = 'DEFAULT'

AND block_size = (SELECT VALUE FROM V$PARAMETER WHERE name = 'db_block_size')

AND advice_status = 'ON';

SGA Advisor ::: 

select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

PGA Advisor :::: 

select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

 SQL Tuning using :::: 

SQL> @?/rdbms/admin/sqltrpt

 AWR various Report.

SQL> @?/rdbms/admin/awrsqrpt.sql --> awr report for only single sql_id

SQL> @?/rdbms/admin/awrrpt.sql   --> Traditional awr report for instance.

SQL> @?/rdbms/admin/awrgrpt.sql -- AWR Global Report (RAC) (global report)

SQL> @?/rdbms/admin/awrgdrpt.sql -- AWR Global Diff Report (RAC)

Other important scripts under $ORACLE_HOME/rdbms/admin

SQL> @?/rdbms/admin/spawrrac.sql -- Server Performance RAC report

SQL> @?/rdbms/admin/awrsqrpt.sql -- Standard SQL statement Report

SQL> @?/rdbms/admin/awrddrpt.sql -- Period diff on current instance

SQL> @?/rdbms/admin/awrrpti.sql -- Workload Repository Report Instance (RAC)

Note :::  To more analysis why plan has changed one can check support.oracle.com.

Thanks,

Srini

How to find the top 10 sql's and Find How much PGA usage in Oracle 19C DB

 Dear All,

In this post i am sharing few useful SQL's and How to find the top 10 sql's and Find How much PGA usage in Oracle 19C DB.

1) To find corrupt Object/Segments using file_id and block_id

select segment_name,block_id,file_id from dba_extents where FILE_ID=&1 and &2 between block_id and block_id+blocks-1

/

 Indexes created on Table_name

set lin 10000

col INDEX_NAME for a30

col TABLE_NAME for a30

col index_type for a25

col status for a10

--col LAST_ANALYZED for a19 wrap

col column_name for a20

col column_expression for a25

select a.INDEX_NAME ,b.column_name, column_expression,a.TABLE_NAME,STATUS,INDEX_TYPE

from user_indexes a,user_ind_columns b, user_ind_expressions c

where a.table_name = upper(trim('&1'))

and a.TABLE_NAME=b.TABLE_NAME

and a.INDEX_NAME=b.INDEX_NAME

and a.INDEX_NAME=c.INDEX_NAME (+)

and a.INDEX_NAME=c.INDEX_NAME (+)

/ 

Current running query

set lin 10000

col sid for 999999999

col serial# for  9999999999

col osuer for a20

col SQL_TEXT for a70 wrap

col osuser for a10

col status for a10

--select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sqlarea b

--where a.SQL_ADDRESS=b.ADDRESS

--and b.HASH_VALUE = a.SQL_HASH_VALUE

select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sql b

where a.SQL_ADDRESS=b.ADDRESS

and b.HASH_VALUE = a.SQL_HASH_VALUE

order by 6 desc,5 desc

/

To find query how much memory (PGA)

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid FORMAT 999  HEADING 'SID'

COLUMN oracle_username  FORMAT a12     HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username FORMAT a9  HEADING 'O/S User' JUSTIFY right

COLUMN session_program  FORMAT a18     HEADING 'Session Program' TRUNC

COLUMN session_machine  FORMAT a18      HEADING 'Machine'  JUSTIFY right TRUNC

COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'

COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'

COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'

COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

SELECT

    s.sid  sid

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , s.program     session_program

  , lpad(s.machine,8)    session_machine

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session pga memory') session_pga_memory

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session pga memory max')    session_pga_memory_max

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session uga memory') session_uga_memory

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session uga memory max')    session_uga_memory_max

FROM

    v$session  s

ORDER BY session_pga_memory DESC

/

 SID  Oracle User  O/S User Session Program               Machine     PGA Memory PGA Memory Max     UGA Memory UGA Memory MAX

---- ------------ --------- ------------------ ------------------ -------------- -------------- -------------- --------------

  20                 oracle oracle@ggnode1 (M0  ggnode1               32,850,736     59,786,032     23,623,584     29,389,984

  18                 oracle oracle@ggnode1 (MM  ggnode1               23,875,304     26,562,280      4,287,472     21,663,552

 252                 oracle oracle@ggnode1 (TT  ggnode1               20,333,360     20,333,360        367,072        367,072

 274                 oracle oracle@ggnode1 (J0  ggnode1               11,682,608    104,809,264        367,072        367,072

 246                 oracle oracle@ggnode1 (DB  ggnode1                8,953,728     10,478,112        367,072        367,072

  42                 oracle oracle@ggnode1 (M0  ggnode1                5,718,832              0         65,488        628,864

 262                 oracle oracle@ggnode1 (M0  ggnode1                5,522,224      8,274,736      3,650,952      5,102,328

  41          SYS    oracle sqlplus@ggnode1 (T  ggnode1                5,006,336     19,489,792      1,611,368      2,893,104

   9                 oracle oracle@ggnode1 (DI  ggnode1                4,844,408      4,844,408        367,072        367,072

  27                 oracle oracle@ggnode1 (CJ  ggnode1                4,411,112      7,294,696      2,462,592      5,014,824

  28                 oracle oracle@ggnode1 (W0  ggnode1                3,556,144      4,408,112      2,070,120      2,633,496

   7                 oracle oracle@ggnode1 (DB  ggnode1                3,533,688      4,123,512      1,742,320      1,873,296

 275                 oracle oracle@ggnode1 (W0  ggnode1                3,425,072      4,408,112      1,929,584      3,006,712

 251                 oracle oracle@ggnode1 (LR  ggnode1                3,354,136      3,681,816        970,864      1,152,952

 250                 oracle oracle@ggnode1 (RE  ggnode1                2,764,312      3,026,456        970,856      1,280,040

  12                 oracle oracle@ggnode1 (SM  ggnode1                2,698,776      3,419,672        970,864      1,458,232

   6          SYS    oracle oracle@ggnode1 (OF  ggnode1                2,567,704      2,567,704        825,488        890,976

  38                 oracle oracle@ggnode1 (Q0  ggnode1                2,442,032      2,900,784      1,036,344      1,341,656

  10                 oracle oracle@ggnode1 (LG  ggnode1                2,379,496      2,379,496        367,072        367,072

  11                 oracle oracle@ggnode1 (CK  ggnode1                2,282,496      2,282,496        367,072        367,072

   4                 oracle oracle@ggnode1 (W0  ggnode1                2,245,424      2,573,104        839,888      1,210,680

   3                 oracle oracle@ggnode1 (GE  ggnode1                2,174,488      2,174,488        367,072        367,072

 263                 oracle oracle@ggnode1 (Q0  ggnode1                2,114,352      2,114,352        498,048        498,048

 259                 oracle oracle@ggnode1 (W0  ggnode1                2,114,352      6,964,016        498,048        498,048

 256                 oracle oracle@ggnode1 (AQ  ggnode1                2,043,416      2,043,416        367,072        367,072

  13                 oracle oracle@ggnode1 (W0  ggnode1                2,031,760      7,291,696        629,048      1,283,976

  15                 oracle oracle@ggnode1 (W0  ggnode1                1,917,744      2,442,032        498,056      1,152,952

   8                 oracle oracle@ggnode1 (SV  ggnode1                1,912,344      1,912,344        367,072        367,072

 253                 oracle oracle@ggnode1 (MM  ggnode1                1,912,344      1,912,344        367,072        432,560

 245                 oracle oracle@ggnode1 (PM  ggnode1                1,846,808      1,846,808        367,072        367,072

 244                 oracle oracle@ggnode1 (VK  ggnode1                1,846,808      1,846,808        367,072        367,072

 242                 oracle oracle@ggnode1 (DI  ggnode1                1,846,808      1,846,808        367,072        367,072

 241                 oracle oracle@ggnode1 (GE  ggnode1                1,846,808      1,846,808        367,072        367,072

 240                 oracle oracle@ggnode1 (MM  ggnode1                1,846,808      1,846,808        367,072        367,072

  19                 oracle oracle@ggnode1 (TM  ggnode1                1,846,808      1,846,808        367,072        367,072

  17                 oracle oracle@ggnode1 (PX  ggnode1                1,846,808      1,846,808        367,072        367,072

 239                 oracle oracle@ggnode1 (VK  ggnode1                1,846,808      1,846,808        367,072        367,072

   2                 oracle oracle@ggnode1 (PS  ggnode1                1,846,808      1,846,808        367,072        367,072

 249                 oracle oracle@ggnode1 (SM  ggnode1                1,846,808      1,846,808        367,072        367,072

 238                 oracle oracle@ggnode1 (CL  ggnode1                1,797,728      1,797,728        367,072        367,072

   1                 oracle oracle@ggnode1 (PM  ggnode1                1,797,728      1,797,728        367,072        367,072

 255                 oracle oracle@ggnode1 (Q0  ggnode1                1,786,672      1,786,672        432,560        432,560

  24                 oracle oracle@ggnode1 (TT  ggnode1                1,655,600      1,655,600        367,072        367,072

  22                 oracle oracle@ggnode1 (QM  ggnode1                1,655,600      1,655,600        367,072        367,072

   5                 oracle oracle@ggnode1 (SC  ggnode1                1,655,600      1,655,600        367,072        367,072

 269                 oracle oracle@ggnode1 (W0  ggnode1                1,655,600      1,655,600        367,072        367,072

 248                 oracle oracle@ggnode1 (LG  ggnode1                1,655,600      1,655,600        367,072        367,072

 247                 oracle oracle@ggnode1 (LG  ggnode1                1,655,600      1,655,600        367,072        367,072

 237                 oracle oracle@ggnode1 (TT  ggnode1                1,655,600      1,655,600        367,072        367,072

 243                 oracle oracle@ggnode1 (SC  ggnode1                1,655,600      1,655,600        367,072        367,072

  35                 oracle oracle@ggnode1 (W0  ggnode1                1,655,600      1,655,600        367,072        367,072

51 rows selected.

Find  Active SQL old versions ::: 

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,

    SID,  

    MACHINE,

    REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,

    ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

    || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'

    || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT

FROM    V$SESSION SES,  

    V$SQLtext_with_newlines SQL

where SES.STATUS = 'ACTIVE'

    and SES.USERNAME is not null

    and SES.SQL_ADDRESS    = SQL.ADDRESS

    and SES.SQL_HASH_VALUE = SQL.HASH_VALUE

    and Ses.AUDSID <> userenv('SESSIONID')

order by runt desc, 1,sql.piece;

Thanks,

Srini

Oracle Deep Data Security - lab examples and use cases - 2026

 Hi All,

In this post i am going share about Oracle Deep Data Security .

You will learn how to shift from fragmented, application-based controls to a centralized, declarative model that evaluates identity and context at runtime. 

The brief offers practical guidance for enforcing least-privilege access, protecting AI-driven workflows such as retrieval-augmented generation (RAG), and allowing agents to operate within clearly defined guardrails.

 It also explains how capabilities such as cell-level authorization, secure identity propagation, and controlled privilege elevation can reduce risk without sacrificing flexibility or performance.

 Identity & context-aware access control across workloads :::

Key benefits include: 

 Reduced data exposure risk with database-enforced authorization across all access paths .

 Least-privilege access for users and non-human identities, including AI agents .

 Rapid adaptation to changing security requirements by separating policy from application code. 

 Strengthened governance with centralized, end-user-aware auditing.

 Performance and scale maintained for enterprise workloads.

Why Agentic AI increases the risk  ? 

Agentic AI changes how applications interact with data. Instead of executing predefined logic, agents generate SQL at runtime based on user input and model reasoning. This shift introduces new security risks that traditional controls were not designed to address.

Declarative policy model  :

Deep Data Security enforces access control through declarative SQL policies, referred to as Data Grants. These policies define which operations—such as SELECT, INSERT, UPDATE, and DELETE—are allowed on specific rows, columns, or individual data values. 

Policies use SQL predicates to identify the data they apply to and can incorporate joins, subqueries, and runtime attributes. This enables precise, context-aware decisions based on both user attributes and data relationships.

 Policy for row-level access ::

Cell-level authorization ::

 Cell-level authorization enables control over individual data values within a row. 

This allows organizations to enforce strict least-privilege access without creating complex views or duplicating data structures. 

For example: 

 Employees can view their own records but update only contact details .

 Managers can update salaries for direct reports but not their own.

 Sensitive attributes such as SSNs remain restricted even when other fields are visible

Runtime policy enforcement ::

  At runtime, Deep Data Security evaluates authorization policies and transparently rewrites queries and other SQL operations, independent of application logic, to enforce authorization controls. In effect, Deep Data Security serves as the policy decision point (PDP), while the database SQL engine functions as the policy enforcement point (PEP). This helps ensure end users can access only authorized data, regardless of the SQL executed by an agent or application, which helps mitigate prompt injection and SQL injection attacks.

Authorization APIs ::

  

End-user security context

The Oracle Deep Data Security advantage  ::

Securing AI-driven access requires enforcement at the data layer. 

Oracle Deep Data Security, built into Oracle AI Database 26ai, embeds centralized, declarative authorization policies directly in the database. By separating authorization logic from application code, it helps organizations apply consistent access controls across agentic AI, analytics, and enterprise applications, without depending on how SQL is generated. 

Realizing enterprise benefits :::

 Oracle Deep Data Security helps organizations scale AI adoption while maintaining governance and control: 

Enforce identity- and context-aware access: Evaluate runtime security context for both human users and AI agents to support least-privilege access. 

 Protect data at a granular level: Apply row-, column-, and cell-level authorization without requiring complex application logic or data duplication. 

 Simplify application development: Decouple authorization policies from application code to reduce hardcoded logic and streamline updates. 

 Apply consistent policy enforcement: Extend controls across relational data, vector data used in retrieval augmented generation (RAG), and heterogeneous Lakehouse environments. 

Strengthen governance and auditing: Centralize auditing of database activity with end-user and agent attribution to support security and compliance requirements.

Thanks,

Srini

Oracle 19C DB How to export the dump file using LOGTIME parameter step by step lab 2026

 Dear All,

In this post i am sharing Oracle 19C DB  How to export the dumpfile using LOGTIME parameter step by step lab .

This parameter LOGTIME give us the timestamp for each and every export operation during expdp., therefore its easy to check the diagnise/elapsed time taken for execution.

 default LOGTIME=NONE, If you want to use this feature use this parameter in expdp command.

Types of LOGTIME parameters:

LOGTIME=NONE : Default value which will not display timestamp in both command prompt as well as export log file.

LOGTIME=ALL : Display the timestamp in both command prompt as well as export log file.

LOGTIME=LOGFILE : Display the timestamp in only export log file, not in command prompt.

LOGTIME=STATUS : Display the timestamp in only command prompt and not in export log file.

Check the DB version and status :

create schema and that schema we can use it for this lab

grant the schema and connect to that schema create some objects 

Now take the export of the dump file for his schema 

if schema exist in CDB$ROOT use below command :

expdp directory=DATA_PUMP_DIR dumpfile=racsdev_sch.dmp logfile=racsdev_sch.log schemas=racsdev LOGTIME=ALL

If schema exist in PDB use below command

Logtime parameter for all option ::  

expdp system/****@pdb1 directory=DATA_PUMP_DIR dumpfile=racsdev_sch1.dmp logfile=racsdev_sch1.log schemas=racsdev LOGTIME=ALL

Without logtime parameter output 

Logtime parameter with logfile option : 

Display the timestamp in only export log file, not in command prompt.

Logtime parameter STATUS option 

Display the timestamp in only command prompt and not in export log file

Hope this will help when you are working with real-time tasks, with LOGTIME options.

Thanks,

Srini