Database Technology

Friday, November 26, 2021

Script to flush the online redologs in oracle database

Script to flush the online redo logs in oracle database:

we have come across some requirement to flush the online redo log and generate the archive forcifully,so created this script.

When you run this script just pass the <SID> and script will create the archives.

flush_oracle_8i_redo.ksh

#!/bin/ksh

#######################################################################

# Purpose :This script force to generate archive

# Developed by Bala nov-17-2021

# need to pass the SID

#######################################################################

wdir=`pwd`

hostname=`hostname`

date=`date +"%b %d, %Y %T"`

export wdir

cd $wdir

ORACLE_SID=$1

export ORACLE_SID=$1

export ORACLE_HOME=/opt/oracle/product/8.1.7

export PATH=$PATH:/opt/oracle/product/8.1.7/bin

export NLS_LANG=AMERICAN_AMERICA.UTF8

export SHLIB_PATH=$ORACLE_HOME/lib

########################################################################

# Check DB status and flush archive #

########################################################################

sqlplus -s /nolog << EOF

connect / as sysdba

set echo on

set verify on

set feedback on

set heading off;

col open_mode for a10

spool /home/sqlutils/${ORACLE_SID}_db_archive_flush.log

select name,open_mode from v\$database;

ALTER SYSTEM ARCHIVE LOG CURRENT;

spool off

EOF

**********************************

Step by step Table migration from AWS RDS postgreSQL to other PostgreSQL DB instance

Step by step Table migration from AWS RDS postgreSQL to other PostgreSQL DB instance:

steps:

1.use the pg_dump to take the backup to plain file

2.restore on target using psql import.

pg_dump --username=db_admin --host=<host_name_aws endpoint> --port=5700 --format=plain --file=backup.sql --dbname=postgrsql_db_dev --table=test_table1

-rw-r--r-- 1 postgres postgres 2294 Nov 26 08:30 backup.sql

<server_name>:~/aws> cat backup.sql

-- PostgreSQL database dump

-- Dumped from database version 11.5

-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET xmloption = content;

SET client_min_messages = warning;

SET row_security = off;

SET default_tablespace = '';

-- Name: test_table1; Type: TABLE; Schema: public; Owner: app456_admin

CREATE TABLE public.test_table1 (

id integer NOT NULL,

app_id integer NOT NULL,

variant_id integer NOT NULL,

principal_id integer NOT NULL,

include boolean NOT NULL

);

ALTER TABLE public.test_table1 OWNER TO app456_admin;

-- Name: test_table1_id_seq; Type: SEQUENCE; Schema: public; Owner: app456_admin

CREATE SEQUENCE public.test_table1_id_seq

AS integer

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE public.test_table1_id_seq OWNER TO app456_admin;

-- Name: test_table1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: app456_admin

ALTER SEQUENCE public.test_table1_id_seq OWNED BY public.test_table1.id;

-- Name: test_table1 id; Type: DEFAULT; Schema: public; Owner: app456_admin

ALTER TABLE ONLY public.test_table1 ALTER COLUMN id SET DEFAULT nextval('public.test_table1_id_seq'::regclass);

-- Data for Name: test_table1; Type: TABLE DATA; Schema: public; Owner: app456_admin

COPY public.test_table1 (id, app_id, variant_id, principal_id, include) FROM stdin;

4 80 43 51 t

5 89 52 51 t

-- Name: test_table1_id_seq; Type: SEQUENCE SET; Schema: public; Owner: app456_admin

SELECT pg_catalog.setval('public.test_table1_id_seq', 5, true);

-- Name: test_table1 test_table1_pkey; Type: CONSTRAINT; Schema: public; Owner: app456_admin

ALTER TABLE ONLY public.test_table1

ADD CONSTRAINT test_table1_pkey PRIMARY KEY (id);

-- Name: test_table1_by_app_id_variant_id; Type: INDEX; Schema: public; Owner: app456_admin

CREATE INDEX test_table1_by_app_id_variant_id ON public.test_table1 USING btree (app_id, variant_id);

-- PostgreSQL database dump complete

***************************restore using import**************************************

psql --username=testuser --host=<host_name> --port=5432 --file=backup.sql --dbname=testdb --table=test_table1

<server_name>:~/aws> psql --username=testuser --host=<host_name> --port=5432 --file=backup.sql --dbname=testdb --table=test_table1

SET

set_config

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

(1 row)

SET

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

ALTER TABLE

COPY 2

setval

--------

(1 row)

ALTER TABLE

CREATE INDEX

<server_name>:~/aws> psql

psql (12.1, server 11.6)

Type "help" for help.

postgres=# \c testdb

psql (12.1, server 11.6)

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

after Data migration to new PostgreSQL instance

testdb=# select * from test_table1;

id1 | app_id | variant_id | principal_id | include

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

14 | 480 | 543 | 751 | t

45 | 489 | 552 | 751 | t

(2 rows)

Thursday, November 25, 2021

script to get the tablespace location and owner of the tablespace in PostgreSQL

script to get the tablespace location in PostgreSQL

Use pg_tablespace_location(tablespace_oid)(PostgreSQL 9.2+) to get the path in the file system where the tablespace is located.

You'll get oid of tablespace from pg_tablespace, so the query should be

select spcname

,pg_tablespace_location(oid)

from pg_tablespace;

spcname | pg_tablespace_location

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

pg_default |

pg_global |

testdb2_data | /rdsdbdata/db/base/tablespace/rdsdbdata/db/base/tablespace/testdb2_data

(3 rows)

******************create DB to specific tablespace in postgres SQL****************

postgres=# create database TEST tablespace TBSspace;

CREATE DATABASE

*****************************

testdb2_connect_prod=> select version();

version

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

PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

(1 row)

postgres_db_dev=> \du db_admin

List of roles

Role name | Attributes | Member of

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

db_admin | Create role, Create DB +| {rds_superuser}

| Password valid until infinity |

postgres_db_dev=> SELECT spcname,spcowner FROM pg_tablespace;

spcname | spcowner

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

pg_default | 10

pg_global | 10

testdb2_data | 16410

(3 rows)

postgres_db_dev=> select usename from pg_user where usesysid='16410';

usename

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

testdb2_admin

(1 row)

testdb2_workbench_dev=> select current_database();

current_database

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

testdb2_workbench_dev

(1 row)

postgres_db_dev=> SELECT current_user;

current_user

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

testdb2_admin

(1 row)

Error while creating database in PostgreSQL

issue:

when I tried to create new DB in postgreSQL got the below error and analyzed the issue and found the solution.

postgres_DEV=> create database postgres_db_dev1;

ERROR: source database "template1" is being accessed by other users

DETAIL: There is 1 other session using the database.

check which user/process holding the template DB

postgres_DEV=> select datname,pid,usename,application_name,client_hostname,client_port from pg_stat_activity where datname='template1';

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

(1 row)

Solution:

Kill a template1 session on PostgreSQL database:

select pg_terminate_backend(pid)

from pg_stat_activity

where pid = '1871';

postgres_DEV=> select pg_terminate_backend(pid)

postgres_DEV-> from pg_stat_activity

postgres_DEV-> where pid = '1871';

pg_terminate_backend

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

(1 row)

postgres_DEV=> CREATE DATABASE test2 TEMPLATE template1;

CREATE DATABASE

Sunday, November 14, 2021

script to find postgres db size:

SELECT

pg_database.datname,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database;

postgres=# \l+ postgres

List of databases

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

(1 row)

select t1.datname AS db_name,

pg_size_pretty(pg_database_size(t1.datname)) as db_size

from pg_database t1

order by pg_database_size(t1.datname) desc;

datname | size

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

testdbTest | 7941 kB

template1 | 7941 kB

template0 | 7801 kB

testdba | 7941 kB

script to check what are the tables available on specific tablespace:

script to check what are the tables available on specific tablespace:

\db+ ts_primary

postgres=# \db+ test_data

List of tablespaces

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

(1 row)

query/script to list tablespace in PostgreSQL:

query to list tablespace in PostgreSQL:

SELECT spcname FROM pg_tablespace;

to list all Tablespace in

postgres=# \db

List of tablespaces

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

query to find out memory values of postgresql memory:

query the list of parameter

select name,setting from pg_settings;

query the list of parameter requires restart

select name,setting from pg_settings where context='postmaster';

show command used to display the vaule of parameter

query to find out memory values of postgresql memory:

select name,setting from pg_settings where name LIKE '%shared_buffer%';

name | setting

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

shared_buffers | 16384

postgres=# show shared_buffers;

shared_buffers

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

128MB

(1 row)

How to backup database object definitions in PostgreSQL database?

Sometimes, you want to backup only database object definitions, not the data This is helpful in the testing phase, which you do not want to move test data to the live system.

To back up objects in all databases, including roles, tablespaces, databases, schemas, tables, indexes, triggers, functions, constraints, views, ownerships, and privileges, you use the following command:

pg_dumpall --schema-only > c:\pgdump\definitiononly.sql

Code language: CSS (css)

If you want to back up role definition only, use the following command:

pg_dumpall --roles-only > c:\pgdump\allroles.sql

Code language: CSS (css)

If you want to backup tablespaces definition, use the following command:

pg_dumpall --tablespaces-only > c:\pgdump\allroles.sql

Code language: CSS (css)

script to find PostgreSQL configuration file location

command/script to find postgreSQL configuration file location

*****************************************************

postgres=# SHOW config_file;

config_file

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

/var/opt/pgsql12/data/postgresql.conf