Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for November, 2022

Real Application Testing for Capture and Replay in a PDB, a great addition was made in 19c

Posted by FatDBA on November 20, 2022

I have used RAT many times in the past and is no doubt the best tool to simulate Oracle database workloads, it empowers us, the DBAs, to make proactive changes while knowing exactly what to expect when a change is implemented and minimizes the likelihood of any negative repercussions.

Once again I’d got a chance to use it during a migration project where we moved to Multitenant 19.15 environment. Database Release 19c, you can capture and replay the workload from within an individual pluggable database (PDB). This enhancement enables you to capture and replay workloads at the PDB level. This leads to better testing, less downtime, and more effective and efficient change control. This was earlier not possible and was only limited to capture and replay multitenant databases at the root multitenant container database (CDB) level.

Todays post is to show how to do that using a short demo on one of my test build.

-- For testing purpose, writing a shell script to capture workload. 
[oracle@oracleontario ~]$cat pdbtest_fatdba.sh
while sleep 1; do \
sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> &>/dev/null <<EOF
set head off
set pages 0
select count(*) from dixit.bigtab;
exit
EOF
done




-- Create a capture directory on the source database server. This is where all your capture specific files will reside. 
[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 19 09:05:12 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 19 2022 09:03:41 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> CREATE or replace DIRECTORY mycapture_dir AS '/ontadomain/home/capture_pdb_fatdbatest';

Directory created.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0


-- Lets start the test, call the shell script which we prepared in the begining. 
[oracle@oracleontario ~]$sh ./pdbtest_fatdba.sh&
[1] 2189


And now capture the workload, use DBMS_WORKLOAD_CAPTURE.start_capture to collect workload data. Once completed, copy all the files from source to target server where they will be replayed.

[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 19 09:05:41 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 19 2022 09:05:39 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'PDB_666', dir => 'mycapture_dir', duration => 900);
END;
/

PL/SQL procedure successfully completed.

SQL> SQL> col name for a10
col status for a22
SELECT id, name, STATUS FROM dba_workload_captures WHERE name='PDB_666';

        ID NAME       STATUS
---------- ---------- --------------------
        31 PDB_666      IN PROGRESS

SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/  

PL/SQL procedure successfully completed.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

Next we have to create the replay directory structure, process captured files and initialzie the replay.

[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 1 01:20:18 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 19 2022 09:06:37 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> CREATE or REPLACE DIRECTORY replaydir_fatdbatest1 AS '/ontadomain/home/replay_pdb_fatdbatest';

Directory created.

SQL>




-- Process all files that we have captured during the capture step. 
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.process_capture('replaydir_fatdbatest1');
END;
/  

PL/SQL procedure successfully completed.

SQL>





-- Initialize the replay once above step of capture is sucessfully completed. 
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'REPLAY_FATDBA1', replaydir_fatdbatest1 => 'replaydir_fatdbatest1');
END;
/  

PL/SQL procedure successfully completed.

SQL>

Next we have to remap connections, prepare the replay process and start the replay clients.

SQL> select conn_id,schedule_cap_id,capture_conn,replay_conn  from dba_workload_connection_map;

   CONN_ID SCHEDULE_CAP_ID CAPTURE_CONN                                       REPLAY_CONN
---------- --------------- -------------------------------------------------- --------------------------------------------------
         1                 (DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERV
                           ICE_NAME=<PDB_SERVICE_NAME>)(CID=(PROGRAM=sqlplus)
                           (HOST=<HOSTNAME>)(USER=oracle)))(A
                           DDRESS=(PROTOCOL=TCP)(HOST=<IP_ADDRESS>)(PORT=<port>
                           )(HOSTNAME=<HOSTNAME>)))


SQL> BEGIN
  DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 1,
                           replay_connection => 'DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxx)(CID=(PROGRAM=sqlplus)(HOST=<HOSTNAME>)(USER=xxxxx)))(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=xxxx)(HOSTNAME=xxxx)))');
END;
/

PL/SQL procedure successfully completed.




-- Prepare your replay. 
SQL> BEGIN
  DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY;
END;
/

PL/SQL procedure successfully completed.

SQL>



-- start the replay client. 
[oracle@oracleontario ~]$wrc <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> replaydir=/refresh/home/replay_pdb1 mode=replay CONNECTION_OVERRIDE=TRUE

Workload Replay Client: Release 19.15.0.0.0 - Production on Sun Mar 1 01:29:11 2022

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Wait for the replay to start (01:29:11)

Start the replay process on the target to fire the workload on the target and once completed generate the replay or any comparison reports.

SQL> BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/  

PL/SQL procedure successfully completed.

SQL>

-- Verify its status
SQL> COL NAME FOR A10
SQL> SELECT id, name, STATUS FROM dba_workload_replays where name = 'REPLAY_FATDBA1';

        ID NAME            STATUS
---------- --------------- ----------------------------------------
         1 REPLAY_FATDBA1  IN PROGRESS

SQL>
SQL> SELECT id, name, STATUS FROM dba_workload_replays where name = 'REPLAY_FATDBA1';

        ID NAME            STATUS
---------- --------------  ----------------------------------------
         1 REPLAY_FATDBA1  COMPLETED

SQL>




-- Now when its completed we can generate the REPLAY report or any other reportings like compare report etc. 
SQL> set long 50000
set pages 50000
set lines 500SQL> SQL>
SQL> select dbms_workload_replay.report (replay_id => 1, format=> 'TEXT') from dual;

DBMS_WORKLOAD_REPLAY.REPORT(REPLAY_ID=>1,FORMAT=>'TEXT')
--------------------------------------------------------------------------------
DB Replay Report for REPLAY_FATDBA1
--------------------------------------------------------------------------------
-------------------

--------------------------------------------------------------------------
| DB Name  | DB Id      | Release    | RAC | Replay Name | Replay Status |
--------------------------------------------------------------------------
| FATDBA12 | 1218318131 | 19.15.0.0.0 | NO  | REPLAY_FATDBA1    | COMPLETED     |
--------------------------------------------------------------------------
... ...
..........
.............
......................

Hope It Helped!
Prashant Dixit

Advertisement

Posted in Uncategorized | Tagged: , , , , | Leave a Comment »

Some of the new exotic wait events in Oracle 21c …

Posted by FatDBA on November 12, 2022

Hi Guys,


Lately I was doing tests on Oracle 21c and came across few of the exotic new wait events added to Oracle 21c. I was using 21.3.0.0.0 and have noticed few new Exadata, ASM, Dataguard, AI/ML, Exascale, Cluster, exa persistent memory, CTWR etc. specific waits were added to the database. I have also noticed some strange looking waits as well i.e. ‘Spare1′,’Spare2′,’Spare3’ ….

Being an innovation release, and since this is a beta version, there is no document available or published for the customer for any of these new waits on Metalink

Couple of new ‘Bloom Filter‘ related waits added to 21c
Bloom Filter EOF
Bloom Filter Fetch

Bunch of new Exadata ‘smart Index|Table scan’ related, User I/O classed wait events added to 21c are
cell smart index scan request
cell smart index scan: db timezone upgrade
cell smart index scan: disabled by user
cell smart index scan: pass through
cell smart table scan request
cell smart table scan: db timezone upgrade
cell smart table scan: disabled by user
cell smart table scan: pass through
cell worker online completion
cell worker retry

Few of the new machine learning & Artificial Intellegence related wait events added in 21c are
enq: AI - Seek operation
enq: AI - dequeue operation
enq: AI - lwm update operation
enq: AI - remove subscriber
enq: AI - start cross operation
enq: AI - stop cross operation
enq: AI - truncate operation

Some of the new RAC (cluster class), global cache (cache fusion) block related waits added to 21c are
gc cr block direct read
gc current block direct read

Some of the new EDSLib/EBSLib latch waits, which are used to gum the code between RDBMS and Exascale storage added in 21c are given below.
latch: EDSLib File
latch: EDSLib Message
latch: EDSLib Root
latch: EGSLib Root
latch: EGSLibDS Root
latch: EGSLibDataStore Object
latch: EGSLibDataStoreShard Object
latch: EGSLibForum Object
latch: EGSLibNet Root
latch: EGSLibSP Root
latch: EGSLibStoragePool Object
latch: EGSLibStoragePoolRing Object
latch: FOB s.o list parent latch

This is by far the most interesting one, with no name and they seem some sort of free or extra wait events names. I am familiar with few of the other ‘spare’ i.e. “enq: BS – krb spare”, “enq: SP..xx”, “enq: SX.xx.xxx“, but these are different
spare 10
spare 2
spare 3
spare 4
spare 5
spare 6
spare 7
spare 8
spare 9

Couple of new ASM related wait events were added too
enq: AM - ASM Scrubbing
enq: AM - ASM used quota calculation

Some of the new enqueue waits on Change Tracking file are
enq: CT - CTWR DBA buffer resize
enq: CT - CTWR datafile sync
enq: CT - CTWR thread checkpoint

Some of the new enqueue related waits on Exadata Persistent Memory (PMEM)
enq: FF - PMEMfs - ODM
enq: FF - PMEMfs - Param
enq: FF - PMEMfs - Resize
enq: FF - PMEMfs - aXtnd pool
enq: FF - PMEMfs - reSze pool

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

Kafka Java Client for Transactional Event Queues (TEQ), Another great feature added to Oracle 21c …

Posted by FatDBA on November 6, 2022

Hi Guys,

Recently I was testing some advance queuing functions to setup an asynchronous communication (using event queues) between few applications and users on Oracle 21c database, to build a messaging platform. I was looking for a way to connect my client side libraries to allow Kafka APIs to connect to Oracle DB.

I had no clue how to do this integration, and then I came across this official documentation which talks about a new functionality in Oracle Database Version 21c – Kafka Java Client for Transactional Event Queues (TEQ) that enables Kafka application compatibility with Oracle Database. This provides easy migration of Kafka applications to TEQ.

Oracle Transactional Event Queue (TEQ) makes it easy to implement event-based applications. It is also highly integrated with Apache Kafka. Apart from enabling apps that use Kafka APIs to transparently operate on Oracle TEQ, Oracle TEQ also supports bi-directional information flow between TEQ and Kafka, so that changes are available in TEQ or Kafka as soon as possible in near-real-time.

Apache Kafka Connect is a framework included in Apache Kafka that integrates Kafka with other systems. Oracle TEQ will provide standard JMS package and related JDBC, Transaction packages to establish the connection and complete the transactional data flow. Oracle TEQ configures standard Kafka JMS connectors to establish interoperability and complete the data flow between the two messaging systems.

Another great blog post on the topic : https://docs.oracle.com/en/database/oracle/oracle-database/21/adque/Kafka_cient_interface_TEQ.html#GUID-94589C97-F323-4607-8C3A-10A0EDF9DA0D

Hope It Helps!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

 
%d bloggers like this: