oracleagent - ORACLEAGENT BLOG

Enable DBA tasks auditing in Postgres

1. Create a specific database role called rds_pgaudit:

CREATE ROLE rds_pgaudit;

2. Modify the DB parameter group by setting the following parameters

pgaudit.role = rds_pgaudit
rds.restrict_password_commands=1
shared_preload_libraries=pg_stat_statements,pgaudit
pgaudit.log= role

3. Reboot the RDS instance.

Validate if the parameter group is updated otherwise reboot the RDS.

4. Confirm that pgaudit is initialized by running the following command:

show shared_preload_libraries;
show pgaudit.role;
show rds.restrict_password_commands;
show pgaudit.log;

5. Create the pgaudit extension by running the following command:

CREATE EXTENSION pgaudit;
\dx

6. Confirm that pgaudit.role is set to rds_pgaudit by running the following command:

show pgaudit.role;

7. Configure the pgaudit.log parameter to audit any of the following:

ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

ALL audits the following commands.
MISC audits miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
DDL audits all data description language (DDL) that is not included in the ROLE class.
ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
FUNCTION audits function calls and DO blocks.
WRITE audits INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
READ audits SELECT and COPY when the source is a relation or a query.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

DATABASE LINK FROM ORACLE TO MySQL

1. Introduction

This document describes steps to create a database link between Oracle to MySQL (Heterogeneous Services) / Oracle Gateway, Regarding Oracle Documentation Gateways will integrate with any number of non-Oracle systems from an Oracle application.

All bold statements are shell commands or configuration steps.

2. Database Link Configuration

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

It is a connection between two physical database servers that allows a client to access them as one logical database.

2.1 Configure Database Link from ORACLE to MySQL

To start Configure Database Link we need to follow the below steps:

Step #1:-

Determine the Version for dg4odbc on the Operating system using

>file $ORACLE_HOME/bin/dg4odbc

This Command Explain which version of ODBC Driver.

Step #2:-

Install unixODBC and MySQL connector (This step should be done as root user).

# yum install unixODBC

# yum install mysql-connector-odbc

Step #4:-

(As Oracle User). Export Variables.

In Linux – .bash_profile

In Solaris -.profile

export LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/libmyodbc5.so:$ORACLE_HOME/lib

export ODBCINI=/etc/odbc.ini

export ODBCSYSINI=/etc

Step #5:-

(As Root User).

Configure odbc.ini located in (/etc), the file should look like the below:-

# Driver from the mysql-connector-odbc package

# Setup from the unixODBC package

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc5.so

Setup = /usr/lib64/libodbcmyS.so

# Driver64 = /usr/lib64/libmyodbc5.so

# Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

DATABASE = cs_dm_stage

UID = <username>

PWD = welcome123!@#

Option = 3

SERVER = nyads2hslrdb02.na.weightwatchers.net

PORT = 3306

It’s configured.

Step #6:-

As Oracle User:-

[root@server etc]# cd /usr/lib64

[root@server lib64]# ldd libmyodbc5.so

linux-vdso.so.1 => (0x00007fff1afff000)

libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f4b16c01000)

libmysqlclient_r.so.16 => /usr/lib64/mysql/libmysqlclient_r.so.16 (0x00007f4b1686c000)

libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f4b16635000)

libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f4b1641c000)

libm.so.6 => /lib64/libm.so.6 (0x00007f4b16197000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f4b15f7a000)

libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f4b15d0d000)

libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f4b15928000)

libz.so.1 => /lib64/libz.so.1 (0x00007f4b15712000)

libodbcinst.so.2 => /usr/lib64/libodbcinst.so.2 (0x00007f4b15501000)

libc.so.6 => /lib64/libc.so.6 (0x00007f4b1516c000)

libdl.so.2 => /lib64/libdl.so.2 (0x00007f4b14f68000)

libfreebl3.so => /usr/lib64/libfreebl3.so (0x00007f4b14d65000)

/lib64/ld-linux-x86-64.so.2 (0x0000003f64c00000)

libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f4b14b20000)

libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f4b14839000)

libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f4b14635000)

libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f4b14408000)

libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f4b141fd000)

libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f4b13ff9000)

libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f4b13ddf000)

libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f4b13bc0000)

The Most Important Thing in the above output is to check if there is nothing in the library “Not Found”.

Step #7:-

(As Oracle User).

Test connection using odbc only, Only on OS Level

# ./isql –v MySQL

The below is output:-

Connected

Until this step we didn’t do anything only configure odbc, next step will explain how to configure Oracle.

Step #8:-

Configuration for Oracle Start with initSID.ora file. Oracle already create a template located in $ORACLE_HOME/hs/admin just copy it with the new name, the default one is initdg4odbc.ora we can leave without change and edit it.

Edit the file and set the new variables like the below:-

HS_FDS_CONNECT_INFO = MySQL

HS_FDS_TRACE_LEVEL = 4

HS_FDS_TRACE_FILE_NAME = /home/oracle/odbc_trace.txt

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so

#HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1

#HS_NLS_NCHAR = AMERICAN_AMERICA.WE8ISO8859P1

set ODBCINI= /etc/odbc.ini

HS_FDS_SUPPORT_STATISTICS= FALSE

HS_FDS_SQLLEN_INTERPRETATION = 64

HS_RPC_FETCH_REBLOCKING= OFF

HS_FDS_FETCH_ROWS = 1

Step #9:-

This Step will describe how to configure Listener.ora, You can download Listener.ora and copy/Paste the entire to make sure there’s nothing wrong or you can create your own. Open Listener.ora and add the below line depending on your Configuration:-

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(ENVS = LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/libmyodbc5.so:/u01/app/oracle/product/11.2.0.4/db_1/lib)

(SID_NAME = dg4odbc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)

(PROGRAM = dg4odbc)

)

Some Notes about the above:-

1-SID_LIST_LISTENER à Indicate to your LISTENER NAME.

2- SID_NAME= Depend init….ora file.

3- Change ORACLE_HOME.

4- LD_LIBRARY_PATH Depend on where you installed unixODBC and MySQL connecter.

5- Program Indicate to init…ora name.

Now After editing Listener.ora Save it, this step requires restarting Listener.ora

> Reload LISTENER

lsnrctl reload

Step #10:-

Finally, add new entire to tnsnames.ora

dg4odbc =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=bosporadb01)(PORT=1622))

(CONNECT_DATA=(SID=dg4odbc))

(HS=OK)

)

tnsping dg4odbc should work.

Step #11:-

This step will create a database link for MySQL.

SQL > create public database link mysql_dblink connect to “csmigration” identified by ” welcome123!@#” using ‘dg4odbc’;

Now Test it:-

**SQL > Select * from “cs_memberdata_staging”@ mysql_dblink;**

We have done the setup.

MySQL Server Details:

Server Name: <server-name/host name>

Database: cs_dm_stage

Username: <username>

Password: <password>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

𝗟𝗼𝗮𝗱 𝗕𝗮𝗹𝗮𝗻𝗰𝗲𝗿 𝘃𝘀 𝗥𝗲𝘃𝗲𝗿𝘀𝗲 𝗣𝗿𝗼𝘅𝘆 𝘃𝘀 𝗔𝗣𝗜 𝗚𝗮𝘁𝗲𝘄𝗮𝘆

What are the differences between a load balancer, a reverse proxy, and an API gateway?
All three are used to optimize and manage web traffic. However, they vary in their function and use cases:
A 𝗹𝗼𝗮𝗱 𝗯𝗮𝗹𝗮𝗻𝗰𝗲𝗿 is a device that distributes incoming network traffic across multiple servers. The goal is to ensure that no single server is overwhelmed with traffic, which can lead to slow response times or even downtime. Load balancers are ideal for high-traffic websites or applications that need to handle a large volume of requests.
A 𝗿𝗲𝘃𝗲𝗿𝘀𝗲 𝗽𝗿𝗼𝘅𝘆, on the other hand, is a server that sits between the client and the web server. The reverse proxy intercepts requests from clients and forwards them to the appropriate server. The reverse proxy can also cache frequently requested content, which can help improve performance and reduce server load. Reverse proxies are ideal for websites or applications that need to handle a large number of concurrent connections.
An 𝗔𝗣𝗜 𝗴𝗮𝘁𝗲𝘄𝗮𝘆 is a server that acts as an intermediary between clients and backend servers. The API gateway is responsible for managing API requests, enforcing security policies, and handling authentication and authorization. API gateways are ideal for microservices architectures, where multiple services need to be accessed through a single API.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Performance Tuning in Oracle

The process of analyzing and reducing processing time is called Performance Tuning…

SQL Tuning

The iterative process of improving the performance of SQL statements to measurable and achievable goals.

Scope of Tuning

1) Analyze and create the index

2) Rewrite SQL

3) Using Hints

4) All improvements have been done from SQL and its related Objects

5) Tuning the Server, CPU, RAM, and Network

6) Tuning the instance memory parameters

7) Optimizer setting to improve performance

Where to start with Performance Tuning

1) Manual inspection of code ( Number of lines of code is small)

2) Execute the code with multiple log statements and analyze the statements that take more time

3) Tune the SQL code and PLSQL code separately

How to optimize code

1) Implement the logic in SQL ( instead of PLSQL ) as far as possible

2) Use Analytical Functions as much as possible

3) Use Global Temporary Tables

4) Use Insert append hints

5) Use pass-by-reference ( NO COPY Compiler hint)

6) Consolidate the redundant code

7) Use LOB variables only if needed (Use Extended Data types from 12C)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Advantages of PL/SQL

1) Tight integrated with SQL

PL/SQL lets you use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudo columns…

PL/SQL fully supports SQL data types – You need not convert between PL/SQL and SQL data types.

You can give a PL/SQL data item the data type of a column or row of a database table without explicitly specifying that data type
using %TYPE Attribute and %ROWTYPE Attribute.

PL/SQL supports both static and dynamic SQL. Static SQL is SQL whose full text is known at compile time.
Dynamic SQL is SQL whose full text is not known until run time. Dynamic SQL lets you make your applications more flexible and versatile.

2) High Performance

PL/SQL lets you send a block of statements to the database, significantly reducing traffic between the application and the database.

Bind Variables
When you embed a SQL INSERT, UPDATE, DELETE, MERGE, or SELECT statement directly in your PL/SQL code,
the PL/SQL compiler turns the variables in the WHERE and VALUES clauses into bind variables.
Oracle Database can reuse these SQL statements each time the same code runs, which improves performance.
PL/SQL does not create bind variables automatically when you use dynamic SQL, but you can use them with dynamic SQL by specifying them explicitly.

Optimizer
The PL/SQL compiler has an optimizer that can rearrange code for better performance.

Subprograms
PL/SQL subprograms are stored in executable form, which can be invoked repeatedly. Because stored subprograms run in the database server,
a single invocation over the network can start a large job. This division of work reduces network traffic and improves response times.
Stored subprograms are cached and shared among users, which lowers memory requirements and invocation overhead.

3) High Productivity

PL/SQL has many features that save designing and debugging time, and it is the same in all environments.
PL/SQL lets you write compact code for manipulating data.
PL/SQL can query, transform, and update data in a database.

4) Portability

You can run PL/SQL applications on any operating system and platform where Oracle Database runs.

5) Scalability

PL/SQL stored subprograms increases scalability by centralizing application processing on the database server.
The shared memory facilities of the shared server let Oracle Database support thousands of concurrent users on a single node.

6) Manageability

PL/SQL stored subprograms increase manageability because you can maintain only one copy of a subprogram, on the database server,
rather than one copy on each client system.
Any number of applications can use the subprograms, and you can change the subprograms without affecting the applications that invoke them.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Diff between decode and case statement

Decode:

1) Decode is an oracle function.
2) Decode function can not be used as a parameter to a procedure or function.
3) Decode function internally uses only the equality operator.
4) Decode function is only used in SQL statements.
5) Decode function does not expect datatype consistency.
6) Decode function performance is low.

Case Statement:

1) Case statement is ANSI standard product.
2) Case statements can be used as a parameter to a procedure or function.
3) Case statement works with all SQL operators.
4) Case statements can be used in the PLSQL construct also.
5) Case statement expects datatype consistency.
6) Case statement performance is high.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Difference between row level trigger and statement level trigger

Row level trigger:

1) For each row clause used
2) Allow using when clause in row level trigger
3) Row level trigger has: new: old qualifiers
4) In row-level trigger, the trigger body is executed for each and every row for a DML statement
5) Performance low as compared to statement level trigger

Statement level trigger:

1) For each row clause not used
2) Not allowed using when clause in statement level trigger
3) Statement level trigger does not have: new: old qualifiers
4) in statement level trigger, the trigger body is executed only once per DML statement
5) Performance is high as compared to row-level trigger

Please find out all of our articles send us the Invitation or Following us:

Difference between Procedure and Function

Procedure:

1)Used mainly to execute a certain process
2)Cannot call in SELECT statement
3)Use the OUT parameter to return the value
4)It is not mandatory to return the value
5)RETURN will simply exit the control from the subprogram.
6)Return datatype will not be specified at the time of the creation

Function:

1)Used mainly to perform some calculation
2)A Function that contains no DML statements can be called in the SELECT statement
3)Use RETURN to return the value
4)It is mandatory to return the value
5)RETURN will exit the control from the subprogram and also returns the value
6)Return datatype is mandatory at the time of the creation

Please find out all of our articles send us the Invitation or Following us:

Sql_profiles vs Sql_baselines vs Sql_patches

In Oracle, a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure the best performance. In essence, the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables, etc. When you run the SQL Tuning Advisor for the list of recommendations you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick the best execution plans at run time.

SQL Baseline on the other hand is more of a brute force method when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but in instances, where data skew, is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.

In any case knowledge of the application, and data may make you prefer one over the other, in case you are clueless about this, then I would suggest you stick with the SQL Profile.

A SQL patch is a SQL manageability object that can be generated by the SQL Repair Advisor, in order to circumvent a plan which causes a failure. In essence, a SQL patch tells the optimizer to change the plan in some way or avoid an optimization so that the failure does not occur. For instance, if a failure happens during index access, then generating a plan without that index (or without any index) may work around the failure

Oracle provides three mechanisms to deal with execution plans: SQL Profiles, SQL Baselines, and SQL Patches. Profiles are proposed by the Tuning Advisor and it’s mostly based on adapting the cardinalities to match reality. Baselines allow us to provide a list of the accepted execution plans for a statement.SQL Patches are part of the SQL Repair Advisor and add hints to a specific statement.

Please find out all of our articles send us the Invitation or Following us:

How to Find allocation space datafile wise

How to query for current allocation, and utilization of data files in Oracle DB:

SELECT SUBSTR (df.NAME, 1, 60) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Author: oracleagent

1. Create a specific database role called rds_pgaudit:

CREATE ROLE rds_pgaudit;

2. Modify the DB parameter group by setting the following parameters

pgaudit.role = rds_pgaudit rds.restrict_password_commands=1 shared_preload_libraries=pg_stat_statements,pgaudit pgaudit.log= role

3. Reboot the RDS instance.

Validate if the parameter group is updated otherwise reboot the RDS.

4. Confirm that pgaudit is initialized by running the following command:

show shared_preload_libraries; show pgaudit.role; show rds.restrict_password_commands; show pgaudit.log;

5. Create the pgaudit extension by running the following command:

CREATE EXTENSION pgaudit; \dx

6. Confirm that pgaudit.role is set to rds_pgaudit by running the following command:

show pgaudit.role;

7. Configure the pgaudit.log parameter to audit any of the following:

ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

1. Introduction

This document describes steps to create a database link between Oracle to MySQL (Heterogeneous Services) / Oracle Gateway, Regarding Oracle Documentation Gateways will integrate with any number of non-Oracle systems from an Oracle application.

All bold statements are shell commands or configuration steps.

2. Database Link Configuration

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

It is a connection between two physical database servers that allows a client to access them as one logical database.

2.1 Configure Database Link from ORACLE to MySQL

To start Configure Database Link we need to follow the below steps:

Step #1:-

Determine the Version for dg4odbc on the Operating system using

>file $ORACLE_HOME/bin/dg4odbc

This Command Explain which version of ODBC Driver.

Step #2:-

Install unixODBC and MySQL connector (This step should be done as root user).

# yum install unixODBC

# yum install mysql-connector-odbc

Step #4:-

(As Oracle User). Export Variables.

In Linux – .bash_profile

In Solaris -.profile

export LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/libmyodbc5.so:$ORACLE_HOME/lib

export ODBCINI=/etc/odbc.ini

export ODBCSYSINI=/etc

Step #5:-

(As Root User).

Configure odbc.ini located in (/etc), the file should look like the below:-

# Driver from the mysql-connector-odbc package

# Setup from the unixODBC package

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc5.so

Setup = /usr/lib64/libodbcmyS.so

# Driver64 = /usr/lib64/libmyodbc5.so

# Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

DATABASE = cs_dm_stage

UID = <username>

PWD = welcome123!@#

Option = 3

SERVER = nyads2hslrdb02.na.weightwatchers.net

PORT = 3306

It’s configured.

Step #6:-

As Oracle User:-

[root@server etc]# cd /usr/lib64

[root@server lib64]# ldd libmyodbc5.so

linux-vdso.so.1 => (0x00007fff1afff000)

libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f4b16c01000)

libmysqlclient_r.so.16 => /usr/lib64/mysql/libmysqlclient_r.so.16 (0x00007f4b1686c000)

libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f4b16635000)

libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f4b1641c000)

libm.so.6 => /lib64/libm.so.6 (0x00007f4b16197000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f4b15f7a000)

libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f4b15d0d000)

libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f4b15928000)

libz.so.1 => /lib64/libz.so.1 (0x00007f4b15712000)

libodbcinst.so.2 => /usr/lib64/libodbcinst.so.2 (0x00007f4b15501000)

libc.so.6 => /lib64/libc.so.6 (0x00007f4b1516c000)

libdl.so.2 => /lib64/libdl.so.2 (0x00007f4b14f68000)

libfreebl3.so => /usr/lib64/libfreebl3.so (0x00007f4b14d65000)

/lib64/ld-linux-x86-64.so.2 (0x0000003f64c00000)

libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f4b14b20000)

libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f4b14839000)

libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f4b14635000)

libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f4b14408000)

pgaudit.role = rds_pgaudit
rds.restrict_password_commands=1
shared_preload_libraries=pg_stat_statements,pgaudit
pgaudit.log= role

show shared_preload_libraries;
show pgaudit.role;
show rds.restrict_password_commands;
show pgaudit.log;

CREATE EXTENSION pgaudit;
\dx