PostgreSQL 13 pg_stat_statements

Overview

Today, we will discuss, how to configure pg_stat_statements in PostgreSQL 13 for Performance improvement strategies

The pg_stat_statements module provides a means for tracking the planning and execution statistics of all SQL statements executed by a server. The module must be loaded by adding pg_stat_statements to shared_preload_libraries in PostgreSQL. conf, because it requires additional shared memory.

Let's start

We will run below SQL Query for pg_stat_statements findings.

SQL Query (Make sure that you have opened and connected with your database using PgAdmin 4 Tool)

SQL

SELECT * FROM pg_stat_statements ORDER BY Total_time DESC;

Error Message

As we can see, it's generating an Error message with the description "ERROR: relation "pg_stat_statements" does not exist at character 17

How to resolve this Error

First of all, we have to install an Extension for pg_stat_statements for further work, so we will type the below SQL command in PgAdmin 4 Query Editor

CREATE Extension pg_stat_statements;

Now, as we can see the problem is not resolved yet, so after digging out for finding the root cause

Following PostgreSQL.conf entries are required to be added for proper functionality

Open PostgreSQL.conf and add these lines

# Configuration for pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'

#compute_query_id = on

pg_stat_statements.max = 10000

pg_stat_statements.track = all

Restart PostgreSQL services from Services.msc

Now the problem is still there, what is exactly happening with this Error message.

Looking into Log File

After reading PostgreSQL documentation, i found these lines which are explaining clearly about this error message

pg_stat_statements for Postgres 13 does not contain: total_time, min_time, max_time, mean_time, stddev_time columns

https://www.postgresql.org/docs/13/pgstatstatements.html

Modify the above Query/SQL statement

SQL Query

SELECT t2.rolname, t3.datname, queryid, calls
, ( total_plan_time + total_exec_time ) / 1000 as total_time_seconds
, ( min_plan_time + min_exec_time ) / 1000 as min_time_seconds, ( max_plan_time + max_exec_time ) / 1000 as max_time_seconds
,( mean_plan_time + mean_exec_time ) / 1000 as mean_time_seconds
,( stddev_plan_time + stddev_exec_time ) / 1000 as stddev_time_seconds
, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written
, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read
, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds
FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid)
JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'
AND queryid IS NOT NULL;

Conclusion

So in this article, we have learned how to configure pg_stat_statements in PostgreSQL 13.

Useful Links

https://github.com/prometheus-community/postgres_exporter/issues/541

https://stackoverflow.com/questions/31021174/pg-stat-statements-enabled-but-the-table-does-not-exist

https://www.postgresql.org/docs/current/pgstatstatements.html

13c OMS Schedule Backup

Overview

Let's configure Oracle 13c OMS that is running with the following details:-

Operating System        Oracle Linux 6.10 (el6uek.x86_64)

Database SID               testdb

Database Version         Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Oracle 13c OMS Ver   Oracle Enterprise Manager Cloud Control 13c Release 3

Following is the script for taking weekly cold backup for 13c OMS Server in a Linux environment

Step 1

Create a script with cold.sh as per my sample script

Script

echo

echo

date

echo

echo "Shutting Down OMS Services of 13c Monitoring Cloud Control for Cold Backup ......."

echo

echo

echo

export ORACLE_HOME=/u01/db/dbhome_1

export ORACLE_SID=testdb

export OMS_HOME=/u01/cloud/middleware

export AGENT_HOME=/u01/cloud/agent/agent_13.3.0.0.0

$OMS_HOME/bin/emctl stop oms -all

/u01/cloud/agent/agent_13.3.0.0.0/bin/emctl stop agent

echo "Shutting Down Database and Listener Services of 13c Monitoring Cloud Control for Cold Backup ......."

echo

echo

echo

#$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF1

#shutdown immediate;

#EOF1

#sqlplus "/ as sysdba" <<EOF1

#shutdown immediate;

#EOF1

#$ORACLE_HOME/bin/lsnrctl stop

date

echo

sleep 10;

date

echo "All Services are down for 13c Monitoring Cloud Control Cold Backup ......."

echo

echo

echo

date

echo "Starting Tar Backup for 13c Monitoring Cloud Control with Database"

echo

echo

echo

AS1=Oracle13c-OMS-TAR_$(date +%d-%m-%Y).tgz

#AS2=Oracle13c-DB-TAR_$(date +%d-%m-%Y).tgz

#AS3=Oracle13c-DBInventory-TAR_$(date +%d-%m-%Y).tgz

echo

/bin/tar -cvzf /u01/backup/cold/$AS1 /u01/cloud

#/bin/tar -cvzf /u01/backup/cold/$AS2 /u01/db

#/bin/tar -cvzf /u01/backup/cold/$AS3 /u01/oraInventory

echo

echo

date

echo

echo "Backup Successfully Completed for 13c Monitoring Cloud Control"

echo "Starting 13c Monitoring Cloud Control Services ......."

$ORACLE_HOME/bin/lsnrctl start

$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF1

startup;

EOF1

$OMS_HOME/bin/emctl start oms

/u01/cloud/agent/agent_13.3.0.0.0/bin/emctl start agent

date

echo "All Services are Up after 13c Monitoring Cloud Control Cold Backup Completion ......."

echo

echo

Step2

Create a crontab schedule job for running the above script on a weekly basis

01 00 * * 6 /home/oracle/backup/scripts/cold.sh > /home/oracle/backup/logs/cold.log

Conclusion

This script will run at 00:01 on Saturday Night, you may change it accordingly as per your desired time.

Migrate WebLogic 12c steps in Linux

Overview

Here I am going to share with you how we can rename WebLogic 11g Instance for cloning purposes or as per any other requirement.

Example

We have 2 x Linux Servers

Server 1 = prodserver.domain.com

Server 2 = testserver.domain.com

Step 1

Edit file vi /oracle/domains/FRDomain/config/fmwconfig/servers/AdminServer/applications/em/META-INF/emoms.properties

And change

1.       oracle.sysman.emSDK.svlt.ConsoleServerHost=testserver.domain.com

2.       oracle.sysman.emSDK.svlt.ConsoleServerName=testserver.domain.com:7001_Management_Service

Step 2

Open file /oracle/domains/FRDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/rwservlet.properties and change host name entry.

Step 3

Edit Start and Stop Web logic scripts

Open file /oracle/domains/FRDomain/bin/startWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/stopWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/startManagedWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/stopManagedWebLogic.sh and change host name entry.

Step 4

Verify both scripts

1)      Start Weblogic using script and Form and Report Services.

2)      Stop Weblogic using script and Form and Report Services.

Conclusion

In this way, we can make a Test Server for Weblogic Forms and Reports environment.

Microsoft SQL Server and PostgreSQL have some similar data types while others are not. Before migration, it is important to keep in mind the right types representing:

 SQL Query to get Row Count For all Tables in SQL Server

 

Convert Oracle Database Windows to Linux

Open oracle database  SQL prompt from the command line for checking the current OS Version

Finding Supported platforms (Need to connect to SQLPLUS)

sqlplus “/ as sysdba”

SQL> select * from v$transportable_platform order by 2;

Checking the Number of Objects in our database

Query is

Select count(*) from dba_objects;

As we can see our source database has 79885 objects

Note: We will compare it After the Migration process completion

Verifying the SOURCE database is ready for migration

Database Name: testdb

Platform Name: Microsoft Windows IA (32-bit)

Note: We will shutdown the database first and open it in read-only mode

Shutdown immediate;

Startup mount;

Alter database open read only;

Now below command will make it ready for migration

Set serveroutput on;

Declare

V_return Boolean;

Begin

V_return:=dbms_tdb.check_db('Linux x86 64-bit');

End;

/

PL/SQL procedure successfully completed. (See below Window);

Check and See if External objects are present 

Declare

V_return Boolean;

Begin

V_return:=dbms_tdb.check_external;

End;

/

PL/SQL procedure successfully completed. 

The following directories exist in the database we will use for this activity: 

SYS.DATA_PUMP_DIR 

We are not using it here in this exercise (We will Skip this Step)

Open RMAN prompt for Starting Conversion

Commands to Run (Windows CMD)

rman target /

Converting Database or Preparing for our Destination OS

Command

convert database new database 'testdb' transport script 'C:\Users\kamran\testdb\trasnport.sql' db_file_name_convert 'D:\app\oradata\testdb\testdb' 'C:\Users\kamran\testdb';

Now over transport.sql script is ready, so we will move forward to the Target instance and complete the migration process

Now create Pfile from SPfile on Source database Windows Server using below

Command

 create pfile='C:\Users\kamran\testdb\init_testdb.ora' from spfile;

Copy Source datafile from c:\users\kamran\testdb to Destination Linux using Winscp

Destination Server Settings (Linux Server 64 Bit)

Name     pmstestug

Version   el6uek.x86_64  

Edit init_testdb.ora

Change destination path as per the below image

Executing TRANSPORT.SQL Script on destination

Conclusion

We have successfully migrated our Windows 32 Bit Oracle 11g Database to Linux 64Bit

Thanks for your time and patience.