Thursday, September 12, 2024

Enhancing Query Performance: Leveraging In-Memory Optimized Dates in Oracle 23ai

In-Memory Column Store

Unlike traditional row-based storage, The In-Memory Column Store (IM column store) stores tables and partitions in memory using a columnar format optimized for rapid scans. This columnar format is optimized for analytical workloads, allowing for efficient scanning of specific columns without needing to read entire rows. 

In-Memory Optimized Dates

To enhance the performance of DATE-based queries DATE components (i.e. DAY, MONTH, YEAR) can be extracted and populated in the IM column store leveraging the In-Memory Expressions framework. This approach enables faster query processing on DATE columns, significantly improving the performance of date-based analytic queries.

Friday, September 6, 2024

Enhancing Efficiency in Oracle 23ai: Using the Automatic SQL Transpiler

 


What is Context Switching between PL/SQL and SQL?

The PL/SQL engine is a virtual machine that resides in memory and processes the PL/SQL m-code instructions. When the PL/SQL engine encounters an SQL statement, a context switch is made to pass the SQL statement to the Oracle server processes. The PL/SQL engine waits for the SQL statement to complete and for the results to be returned before it continues to process subsequent statements in the PL/SQL block. 

In other words, context switching in Oracle databases refers to the overhead incurred when control shifts between the SQL engine and the PL/SQL engine during the execution of mixed SQL and PL/SQL code. The two engines operate in different runtime environments, so when you run SQL statements within PL/SQL code, the database has to 'switch' between these environments. This switching can cause performance degradation.

Automatic PL/SQL to SQL Transpiler

PL/SQL functions within SQL statements are automatically converted (transpiled) into SQL expressions whenever possible.

Wednesday, September 4, 2024

Exploring Bigfile Tablespaces with Default Settings in oracle 23ai


BIGFILE Tablespace

A bigfile tablespace is a tablespace with a single, but large datafile. Traditional small file tablespaces, in contrast, typically contain multiple datafiles, but the files cannot be as large. Making SYSUAX, SYSTEM and USER tablespaces bigfile by default will benefit large databases by reducing the number of datafiles, thereby simplifying datafile, tablespace and overall global database management for users.

Wednesday, August 28, 2024

Securing Your Oracle Database: Step-by-Step Guide to Configuring and Using DBSAT


Oracle Database Security Assessment Tool (DBSAT) is a popular command-line tool that helps identify areas where your database configuration, operation, or implementation introduces risks and recommends changes and controls to mitigate those risks. This tool is essential for database administrators and security professionals who want to ensure their Oracle environments are protected against both internal and external threats.

DBSAT consists of three primary components: Collector, Reporter, and Discoverer, each playing a crucial role in assessing and enhancing database security.

  1. Collector is the first step in the DBSAT process. It connects to the Oracle database and gathers critical security-related information. This includes details about users, roles, privileges, configuration settings, and more. The data collected is then stored in an encrypted file, ensuring that sensitive information is protected during the assessment process.
  2. Reporter takes the output from the Collector and generates comprehensive reports. These reports provide an in-depth analysis of the database's security posture, highlighting vulnerabilities, misconfigurations, and areas that require attention. The Reporter component also offers prioritized recommendations for improving security, making it easier for administrators to address the most critical issues first. The Reporter outputs four reports in HTML, XLS, JSON and Text formats.
  3. Discoverer is focused on identifying sensitive data within the database. It scans tables and columns to detect and classify sensitive information such as personally identifiable information (PII), financial data, and other critical assets. The Discoverer executes SQL queries against database dictionary views to discover sensitive data, and outputs reports in HTML and CSV formats. 

1. Prerequisites:

1.1.    Python: Make sure Python is installed on your system.

I checked that Python was installed on the OS.

[root@Eclipsys ~]# python -V
Python 2.6.6

If Python isn't installed on the OS, we should install it using the following command.

[root@Mytest ~]# sudo yum install python3
OR
[root@Mytest ~]# sudo dnf install python3

1.2.    Java: you need a Java 8 JDK,

I checked Java was installed on the OS

[root@Eclipsys ~]# java -version
java version "1.8.0_172"
Java(TM) SE Runtime Environment (build 1.8.0_172-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.172-b11, mixed mode)

If Java wasn't installed on the OS, at first download for example jdk-8u202-linux-x64.tar.gz, then we should install it using the following commands.

[root@bill111 opt]# cd /opt/
[root@Mytest opt]# tar -xvf jdk-8u202-linux-x64.tar.gz
[root@Mytest opt]# cd jdk1.8.0_202/
[root@Mytest jdk1.8.0_191]# pwd
/opt/jdk1.8.0_202

[root@Mytest jdk1.8.0_202]# update-alternatives --install /usr/bin/java java /opt/jdk1.8.0_202/bin/java 0

[root@Mytest jdk1.8.0_202]# update-alternatives --config java

There is 1 program that provides 'java'.

  Selection    Command
-----------------------------------------------
*+ 1           /opt/jdk1.8.0_202/bin/java

Enter to keep the current selection[+], or type selection number: 1
[root@Mytest jdk1.8.0_202]# java -version
java version "1.8.0_202"
Java(TM) SE Runtime Environment (build 1.8.0_202-b08)
Java HotSpot(TM) 64-Bit Server VM (build 25.202-b08, mixed mode) 

2. Configure DBSAT on the Oracle Linux

2.1.    Create usr_dbsat: If you have a user with DBA privileges, you don’t need to create a new user. However, if you prefer to have a separate user for executing DBSAT, which is recommended, use the following commands to create the user and grant the necessary privileges.

create user usr_dbsat identified *****;
grant select on sys.dba_users_with_defpwd to usr_dbsat;
grant create session to usr_dbsat;
grant select_catalog_role to usr_dbsat;
grant select on sys.registry$history to usr_dbsat;
grant select on audsys.aud$unified to usr_dbsat;
grant audit_viewer to usr_dbsat;
grant capture_admin to usr_dbsat;

NOTE: If you have a container database you should create this user in the container database you want to check using dbsat. In the following example, I had a container database on OCI DBCS. I connected to the target PDB and created the user.

2.2.    Create dbsat directory and extract the zip file in this directory

[oracle@Eclipsys dbsat]$ mkdir -p /u01/app/oracle/dbsat/{output,temp}
[oracle@Eclipsys dbsat]$ cd /u01/app/oracle/dbsat/
[oracle@Eclipsys dbsat]$ unzip dbsat.zip
[oracle@Eclipsys dbsat]$ ls
dbsat  dbsat.bat  dbsat.zip  Discover  jython-standalone-2.7.3.jar  output  sa.jar  sat_collector.sql  temp  xlsxwriter

3. Run the dbsat collect command.

It is recommended that the following be added to the tnsnames:ora file.

[oracle@Eclipsys dbsat]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
myservice =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <host_ip>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = <sid>)
    )
  )

Now we can execute the collect command using the service name, database name, or PDB name if we have a container database.

[oracle@Eclipsys dbsat]export JAVA_HOME=/usr/lib/jvm/jre-1.8-oracle-x64/bin/java
[oracle@Eclipsys dbsat]./dbsat collect usr_dbsat@myservice myservice_output

Here is the sample output of executing the above command.

4. Execute dbsat report command. 

4.1.    dbsat.zip: download the dbsat.zip file, from Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)

[oracle@Eclipsys dbsat]$ export JAVA_HOME=/usr/lib/jvm/jre-1.8-oracle-x64
[oracle@Eclipsys dbsat]$ ./dbsat report myservice_output 

Here is the sample output of executing the above command.

4.2.    Let's interpret the output:

At the beginning of the report, we have a summary that shows the number of high, medium, and low risks. DBSAT divides the database into several categories and checks the security assessment in each category. This can be observed in the 'Section' column of the table below.

In the subsequent sections of the report, we can see each high, medium, and low risk categorized separately. For example, we have a high risk in Network Encryption, which is a subsection of Network Configuration in our report.

In addition, the report includes another table that shows Security Features Utilized. This table highlights security best practices that are not implemented in our database (Currently used = NO), indicating gaps in those sections. Therefore, it is recommended to identify and implement these features to strengthen our security.

5. Execute dbsat discoverer command.

To execute the Discoverer command, we first need to edit the dbsat.config file and add our database information, as shown in the following example, where I used ORACLE_SID as the service name

[oracle@Eclipsys dbsat]$ vi Discover/conf/dbsat.config
#default is localhost
        DB_HOSTNAME = tms-oda1-nd1     ###use the servername

#DB_PORT is the port at which the DBSAT tool needs to connect to
#default is 1521
        DB_PORT = 1521

#DB_SERVICE_NAME is the service Name for the DB, I used ORACLE_SID
#use ORACLE_SID
        DB_SERVICE_NAME = sid
Here is an example of executing the Discoverer command.





Tuesday, July 16, 2024

How to Roll Forward a Standby Database Using RMAN Incremental Backup After Adding a Datafile to Primary

Rolling forward a standby database involves applying incremental backups to synchronize changes made to the primary database. This process becomes more complicated when a new datafile is added to the primary database. In this article, I will outline the essential steps and considerations involved in rolling forward a standby database using RMAN incremental backups after the addition of a datafile on the primary side.

Automating Archive Log Cleanup in DBCS Standby Databases: A Step-by-Step Guide

In Oracle Cloud Infrastructure (OCI) Database Cloud Service (DBCS), the ability to use crontab or similar utilities to schedule jobs for the oracle user is restricted by default due to security and operational best practices. Here's why this restriction exists and alternative approaches you can use:

Wednesday, June 5, 2024

Set up DELTA (DB Endpoint Latency Testing Ammeter) to test the latency from a Windows system using venv to an oracle database

 What is the DELTA?

DELTA (DB Endpoint Latency Testing Ammeter) is a tool or framework designed to measure the latency of database endpoints. It is used to assess the performance of database connections, helping developers and database administrators understand the time it takes for a database query to travel from the client to the database server and back.

Enhancing Query Performance: Leveraging In-Memory Optimized Dates in Oracle 23ai

In-Memory Column Store Unlike traditional row-based storage, The  In-Memory Column Store  (IM column store) stores tables and partitions in ...