Postgres commads

 [oracle@Tesdb ~]$ systemctl status postgresql-15

● postgresql-15.service - PostgreSQL 15 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)

   Active: active (running) since Thu 2024-02-01 17:45:15 +03; 3 days ago

     Docs: https://www.postgresql.org/docs/15/static/

  Process: 33491 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

 Main PID: 33500 (postmaster)

   CGroup: /system.slice/postgresql-15.service

           ├─33500 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/

           ├─33503 postgres: logger

           ├─33505 postgres: checkpointer

           ├─33506 postgres: background writer

           ├─33508 postgres: walwriter

           ├─33509 postgres: autovacuum launcher

           ├─33510 postgres: logical replication launcher

           ├─33541 postgres: postgres Tesdb [local] idle

           ├─65961 postgres: postgres Tesdb 172.16.162.240(61004) idle

           ├─69716 postgres: postgres Tesdb 172.16.162.240(63935) idle

           ├─69816 postgres: postgres Tesdb 172.16.162.240(64108) idle

           ├─71388 postgres: postgres postgres 10.28.68.209(49286) idle

           ├─71411 postgres: postgres Tesdb 172.16.162.240(49191) idle

           └─71552 postgres: postgres postgres 10.28.68.215(44922) idle

[oracle@Tesdb ~]$

[oracle@Tesdb ~]$ sudo -i -u postgres

-bash-4.2$

-bash-4.2$

-bash-4.2$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres)

[oracle@Tesdb ~]$ sudo -i -u postgres

-bash-4.2$

postgres-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres-# \c Tesdb

You are now connected to database "Tesdb" as user "postgres".

Tesdb-#

Tesdb-#

Tesdb-#

Tesdb-# \l

                                                 List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges

-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------

 Tesdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +

           |          |          |             |             |            |                 | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +

           |          |          |             |             |            |                 | postgres=CTc/postgres

(4 rows)

Tesdb-# \c

You are now connected to database "Tesdb" as user "postgres".

Tesdb-#

Tesdb-#

Tesdb-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Tesdb=# \dt

               List of relations

 Schema |       Name        | Type  |  Owner

--------+-------------------+-------+----------

 public | chat_conversation | table | postgres

 public | employees         | table | postgres

 public | orgnaization      | table | postgres

 public | user              | table | postgres

(4 rows)

Tesdb=# \dt+ employees

                                       List of relations

 Schema |   Name    | Type  |  Owner   | Persistence | Access method |    Size    | Description

--------+-----------+-------+----------+-------------+---------------+------------+-------------

 public | employees | table | postgres | permanent   | heap          | 8192 bytes |

(1 row)

Tesdb=# select schemaname,relname from pg_stat_user_Tables;

 schemaname |      relname

------------+-------------------

 public     | employees

 public     | user

 public     | chat_conversation

 public     | orgnaization

Tesdb=# select datname,oid from pg_database;

  datname  |  oid

-----------+-------

 postgres  |     5

 Tesdb    | 16535

 template1 |     1

 template0 |     4

(4 rows)

how to connect database 

-bash-4.2$ psql -U postgres Tesdb

psql (15.5)

Type "help" for help.

Tesdb=#

Tesdb=#

Tesdb=# \conninfo

You are connected to database "Tesdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

creating os user:

+++++++++++++++++++

h-#

Tesdb-#

Tesdb-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Tesdb-# \q

-bash-4.2$

-bash-4.2$

-bash-4.2$ createuser test

-bash-4.2$ psql

psql (15.5)

Type "help" for help.

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 test      |  

 

 

date and time

select now() -interval '2 hours 30 minutes' as deduct_time;

          deduct_time

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

 2024-02-06 07:39:10.676531+03

(1 row)

select now() as current;

            current

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

script to ddl for table--metadata

 export ORACLE_SID="$1"

export ORACLE_HOME=/u01/db/oracle19c/oraHome_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=/u01/db/oracle19c/oraHome_1/lib

 $ORACLE_HOME/bin/sqlplus /nolog << End_SQL >> r_tables.log

 connect / as sysdba

 SET head OFF

SET feed OFF
SET trimspool ON
SET linesize 32767
SET pagesize 32767
SET echo OFF
SET termout OFF
SET verify OFF

 

@r_tables.sql

 

End_SQL

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

more r_tables.sql

spool readwrite_tables.sql
select 'alter table ' || owner || '.' || table_name || ' read write;' from dba_tables where read_only='YES';
spool off

 

spool readonly_tables.sql
select 'alter table ' || owner || '.' || table_name || ' read only;' from dba_tables where read_only='YES';
spool off

create the public synonyms in the target database.

 

1. Prepare the script to create the public synonyms in the target database.

cat header.txt > 13_b_get_public_synonym.pomdir01.sql.out

cat 13_b_get_public_synonym. pomdir01.sql.out | grep "CREATE PUBLIC SYNONYM" | grep -v SELECT  >> create the 13_create_public_synonym.sql

echo "quit" >> create the 13_create_public_synonym.sql

Huge page script

 

awk '/Hugepagesize:/{p=$2} / 0 /{next} / kB$/{v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$2;next} {h[$0]=$2} /HugePages_Total/{hpt=$2} /HugePages_Free/{hpf=$2} {h["HugePages Used (Total-Free)"]=hpt-hpf} END{for(k in v) print sprintf("%-60s %10d",k,v[k]/p); for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)}' /proc/meminfo|sort -nr|grep --color=auto -iE "^|( HugePage)[^:]*"

Analyze a particular SQL ID and see the trends for the past day

 Analyse a particular SQL ID and see the trends for the past day

set pages 50000 lines 32767

select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

set pages 50000 lines 32767

select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS
/

shell script to send sql query output to html format to mail body

 #!/bin/bash

# oracle credentials

set oracle env variablbles

# Email details

TO= mail id

subject =" report"

#get tablespace details

sqlplus -S / as sysdba << EOF > /tmp/tablespace_details.html

SET MARKUP HTML ON

SET FEEBACKUP OFF

SET VERIFY OFF

SET UNDERLINE OFF

SET TRIMSPOOL ON

SET LINESIZE 200

SET LONG 50000

----SQL QUERY

EOF

#get email content

body=$(cat /tmp/tablespace_details.html)

#send email

export CONTENT_TYPE=text/html

echo -e "TO:$TO\nSubject: $SUBJECT\nContent-Type:$CONTENT_TYPE\n\n$body" | /usr/sbin/sendmail -t

#clearnup

rm /tmp/tablespace_details.html

schema refresh from source to target(prod to qa)

 

Source Database Side :

Step 1:

Check the Schema Exist or Not. Schema must be there in source database.

SQL> select username, account_status, created from dba_users where username='ORACLE';

Step 2: 

Check the schema size 

SQL> select owner, sum(bytes)/1024/1024  "MB"  from dba_segments where owner="ORACLE";

Step 3:

Take the count of schema objects, this will be use full after complete the refresh to compare both target and source schema objects.

SQL> select object_type, count(*) from dba_objetcts where owner='ORACLE' group by object_type;

Step 4:

Before going to take the export, first check mount point size where you're  going to store the export dumpfile, if mount point doesn't have sufficient space export job gets fail.

example :

]$  cd /d01/exports

exports]$ df  -h  .  (in Linux)  df  -g . (AIX and Sun Solaris)

Step 5 :

Create a datapump directory in database level, default datapump directory  location is " /app/oracle/product/10.2.0/rdbms/log/".

First create a directory in OS Level

]$ mkdir -p  /d01/exports 

Next create in database levele

SQL>  create or replace directory DATAPUMP as '/d01/exports';

Step 6 :

Now take the export of schema.

]$ expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile= ORACLE_DBNAME.dmp logfile= ORACLE_DBNAME.log schemas=ORACLE

Step 7 :

If  you're going to import on same server means no need to move the dumpfile anywhere, incase if you're going to import this dump file in some other server, copy this dumpfile through SCP command 

expdp]$ scp  -p  username@servername:\do1\targetlocation   (It will ask you target server password )

We have completed almost all steps in source database end, now we are moving to target database side,

Target Database :

Step 1 :

Check the mount pint size, it should be more then schema size.

]$  cd /d01/exports

exports]$ df  -h  .  (in Linux)  df  -g . (AIX and Sun Solaris)

Step 2 :

Create a directory same like how we have create for source database.

First create a directory in OS Level

]$ mkdir -p  /d01/exports 

Next create in database levele

SQL>  create or replace directory DATAPUMP as '/d01/exports';

Step 3:

Take target schema backup before importing.(for safe side). with export command.

]$ expdp \'/ AS SYSDBA\' directory=DATAPUMP dumpfile= ORACLE_DBNAME.dmp logfile= ORACLE_DBNAME.log schemas=ORACLE

Step 4 :

SCRIPTS:Find complete list of  objects in the schema

Use this script when you do not need to drop the schema, But only the dependent objects in the schema. For ex :- to preserve DB-links, grants, privileges

Below scripts will generate all of the drop statements needed to drop almost all objects (sometimes 1-2 of them will have problems and you will have to manually drop those) from a specified schema (it prompts you for the schema).

EXECUTION

Login to the database where the schema to be dropped exists. Copy and paste the following script , double checking that you are in the correct database!:

--Initializations

set linesize 1000;

SET VERIFY OFF

col owner format a15;

col object_name format a30;

col object_type format a20;

--Select Non-system object owners

SELECT OWNER,OBJECT_TYPE,COUNT(*)

FROM SYS.DBA_OBJECTS

WHERE OWNER NOT IN ('SYS','SYSTEM','TSMSYS','ORACLE_OCM','WMSYS','PATMAN','OUTLN','PUBLIC','DBSNMP','XDB','APPQOSSYS','CTXSYS')

GROUP BY OWNER,OBJECT_TYPE

ORDER BY OWNER,OBJECT_TYPE;

--Select specific Owner, Object_Type & Count

SELECT OWNER,OBJECT_TYPE,COUNT(*)

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

GROUP BY OWNER,OBJECT_TYPE

ORDER BY OWNER,OBJECT_TYPE;

--Drops: Tables, Indexes, & Triggers

SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME||' CASCADE CONSTRAINTS PURGE;'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('TABLE');

--Drops: Sequences, Views, Packages, Functions & Procedures, Synonyms

SELECT 'DROP '|| OBJECT_TYPE || ' ' ||OWNER||'.'||OBJECT_NAME||';'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('PACKAGE','SEQUENCE','VIEW','FUNCTION','PROCEDURE','SYNONYM','TRIGGER');

--Drops: Types

SELECT 'DROP '|| OBJECT_TYPE || ' ' ||OWNER||'.'||OBJECT_NAME||' FORCE;'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('TYPE');

--DO NOT DROP OR IMPORT DBLINKS, EXCLUDE=DATABASE_LINK

SELECT OWNER, OBJECT_TYPE, COUNT(*) 

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('DATABASE LINK')

GROUP BY OWNER, OBJECT_TYPE;

OR

Drop the Schema at destination database:

SQL> Drop Schema <schema_name> cascade; 

(Better drop only schema objects instead of dropping  schema)

Step 5 :

Import the dumpfile into target schema

impdp \'/ AS SYSDBA\' directory=DATAPUMP dumpfile=ORACLE_DATABASE.dmp logfile=ORACLE_DATABASE.log schemas=ORACLE

Step 6 :

Compare the Object Count with source database.

SQL> select object_type, count(*) from dba_objetcts where owner='ORACLE' group by object_type;

(If all the objects same and looks good go ahead and run utlrp.sql.

Step 7 :

Check invalid objects count 

SQL> select owner, object_type, status, count(*)

from sys.dba_objects

where status = ‘INVALID’

group by owner, object_type, status

order by owner, object_type, status;

SQL> @?/rdbms/admin/utlrp.sql