step by step Reading MSSQL DB data from Oracle 19c database using DB link & ODBC Drivers :

 Reading MSSQL DB data from Oracle 19c  database using DB link & ODBC Drivers  :

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

List of steps:

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

1. Root - Install Microsoft SQL server ODBC drivers.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

2. Root - Create ODBC entry in /etc/odbc.ini file pointing to remote.

3. Oracle - create init parameter file initora_19c_DB_MSSQL. Ora

4. Oracle - register SID in listener.ora.

5. Oracle- Create entry in tnsnames.ora pointing to SID.

6. Oracle - create DB link in oracle database.

7. Oracle- Optionally - Create synonym.

8. Install Microsoft SQL server ODBC drivers

How to install the Microsoft ODBC Driver for SQL Server on Linux. It also includes instructions for the optional command-line tools for SQL Server and the unixODBC development headers.

Here we are taking up the ODBC 17 drivers. Please go to the below link and ask to unix team to install the driver on the server.

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver17&tabs=redhat18-install%2Credhat17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

Once installed the package they need to run the ODBC driver using below command.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

9. Create ODBC entry in /etc/odbc.ini file pointing to remote

To create an ODBC entry in /etc/odbc.ini that points to a remote database, you need to define a Data Source Name (DSN) within the file. This DSN will contain the necessary parameters to connect to your remote database.

Here is an example of an odbc.ini entry for a remote database.

[DS_MSSQL]

Description = MSSQL

Driver = /usr/lib64/libmsodbcsql-17.so

Server = 20.20.30.30.\MSSQL_instance1

Port = 57500

Database = MSSQL_DB

UID = user_ora2mssql

Explanation of the parameters:

• [DS_MSSQL]: This is the DSN name. You can choose any descriptive name for your connection.

• Description: A brief description of the data source.

• Driver: The name of the ODBC driver you have installed for your specific database SQL Server. Ensure the corresponding driver is correctly installed and configured in /etc/odbcinst.ini.

• Server : The hostname or IP address of your remote database server.

• Port: The port number on which the database server is listening for  SQL Server.

• Database: The name of the specific database you want to connect to on the remote server.

• UID: The username for authenticating with the database.

10. Create init parameter file initora_19c_DB_MSSQL. Ora

To create a Heterogeneous Services (HS) initialization parameter file for connecting to a SQL Server database named ora_19c_DB, you would create a text file named initora_19c_DB_MSSQL.ora and place it in the $ORACLE_HOME/hs/admin directory. This file contains parameters specific to the gateway, not the main Oracle database.

The file should be located in the $ORACLE_HOME/hs/admin directory.

Below is a sample configuration. The specific parameters required will depend on your environment and the specific Oracle Database Gateway version you are using.

vi $ORACLE_HOME/hs/admin/initora_19c_DB_MSSQL.ora

# needed for the database gateway for odbc

# HS init parameters

HS_FDS_CONNECT_INFO = DS_MSSQL

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so

HS_LANGUAGE= AL16UTF16

HS_FDS_REMOTE_DB_CHARSET=AMERICAN_AMERICA.AL16UTF16

HS_NLS_NCHAR=UCS2

#

# ODBS SPECIFIC ENVIRONMEANT VARIABLES

#

set ODBCINI=/etc/odbc.ini

11. register SID in listener.ora. 

Add the HS SID listener in listener file To register an Oracle System Identifier (SID) for a database instance or a Heterogeneous Services (HS) agent in the listener.ora file, you need to perform static registration. This involves manually adding a SID_DESC entry to the SID_LIST section of the file. 

The listener. Ora file is typically located in the $ORACLE_HOME/network/admin directory

Add a SID_DESC entry:-

Within the SID_LIST_<listener_name> section (by default SID_LIST_LISTENER), add a new SID_DESC block. This block specifies the details for the service you want to register.For an Oracle Database Instance

==================================

LISTENER_ora_19c_DB =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 30.30.30.50)(PORT = 1587))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1587))

    )

  )

 

SID_LIST_LISTENER_ora_19c_DB =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ora_19c_DB)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

    )

    (SID_DESC =

      (SID_NAME = ora_19c_DB_MSSQL)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

      (PROGRAM = /oracle/ora_19c_DB/12201/bin/dg4odbc)

    )

  )

12. Create entry in tnsnames.ora pointing to SID.

To create an entry in the tnsnames.ora file pointing to an Oracle SID, follow these steps:

• Locate the tnsnames.ora file: This file is typically found in the $ORACLE_HOME/network/admin directory. If the TNS_ADMIN environment variable is set, it will point to the directory containing the tnsnames.ora file.

• Open the tnsnames.ora file: Use a text editor to open the tnsnames.ora file.

• To create an entry in the tnsnames.ora file for an Oracle Heterogeneous Services (HS) SID, you need to manually edit the file to include a specific connect descriptor. The HS=OK parameter is crucial in the CONNECT_DATA section to direct Oracle Net to use Heterogeneous Services.

TNS_MSSQL.WORLD =

(DESCRIPTION =

    (SDU = 32768)

    (ADDRESS_LIST =

        (ADDRESS =

          (PROTOCOL = TCP)

          (HOST = 30.30.30.50)

          (PORT = 1587)

        )

    )

    (CONNECT_DATA =

       (SID =ora_19c_DB_MSSQL)

         )

        (HS=OK)

  )

• (HS = OK): This mandatory parameter tells Oracle Net to connect to a non-Oracle system using Heterogeneous Services.

13. Oracle - create DB link in oracle database.

• Permissions: 

Ensure the user creating the database link has the necessary CREATE DATABASE LINK privilege. The remote_user in the CONNECT TO clause must exist in the remote database and have privileges to access the objects you intend to use via the link.

• Public vs. Private: 

Database links can be PUBLIC (accessible by all users) or private (owned by the user who created it). To create a public link, add the PUBLIC keyword after CREATE.

• GLOBAL_NAMES parameter: 

The GLOBAL_NAMES initialization parameter can impact database link behavior. If set to TRUE, the database link name must match the global name of the remote database. Consider setting it to FALSE if strict name matching is not desired.

Note :- Before you creating the DB link they need to be create the SQL server authentication user in SQL server then only you make connection between from Oracle to MSSQL.

Create SQL Server Authentication User:- 

• In SQL Server Management Studio (SSMS): 

o Go to Security → Logins → New Login.

o Choose SQL Server Authentication (not Windows Authentication).

o Set a strong password and ensure Enforce password policy is configured as needed.

o Map the login to the required database and assign appropriate roles (e.g., db_datareader.

Syntax :-         CREATE DATABASE LINK my_remote_link     CONNECT TO remote_user IDENTIFIED     BY remote_password    USING 'REMOTE_DB_ALIAS';

Example :- create public database link  mssql_dblink6 connect to "user_ora2mssql" identified by "" using 'TNS_MSSQL';

Select  count(*)  from  "view_test"@mssql_dblink6;

 

14. Optionally - Create synonym.

To create a synonym for a remote object accessed through a database link in Oracle.

Once the database link is established, you can create a synonym for a specific object (table, view, sequence, etc.) in the remote database.

CREATE SYNONYM synonym_name FOR remote_table_name@db_link_n

Example :- Create public synonym mssqlview   For view_test@mssql_dblink6;

After creating the synonym, you can query the remote table using the synonym name as if it were a local object:

Select count(*) from mssqlview.

This will read the Data from MSSQL from Oracle DB

 

 

step by step to read oracle DB data from MSSQL server using linked server in MSSQL database

 Linked Server in MS SQL Server

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

Source : MS SQL 2019 EE on Windows 22

Target : Oracle 19c R2 EE on Linux 8 - IP - 192.168.56.74 Port- 1583 ServiceName- ORA_DB_19c

Pre-Req:

-----------

a. You must have Oracle client installed on MSSQL server.

b. Working Network Connection between both Servers.

   (If required create exception in Firewall.)

c. Remote (Oracle) DB credentials.

d. Proper TNS entry for remote (Oracle) DB.

Steps:

---------

1. Create User in Remote DB Server

Create user oracle identified by oracle123;

grant create session to oracle;

2. Provide Required Permission to User as per your need

grant select any table to oracle;

grant select any dictionary to oracle;

3. Create TNS Entry in Oracle Client TNSNAMES.ora in Source Server (MS SQL Server)

ORA_DB_19c=(DESCRIPTION=

(CONNECT_DATA=

(SERVICE_NAME=ORA_DB_19c))

(ADDRESS=

(PROTOCOL=TCP)(HOST=12.16.53.12)(PORT=1583)

)

)

4. Create Linked Server Using SSMS

You Need 3 Values : 

a. LINKED SERVER NAME -- Any Name you want to use , Just Like any DBLink. --ORACLE_DB

b. Product Name -- Oracle

c. Data Source -- TNS Service Name of Oracle DB

d. Fill Credentials of Remote DB in Security Section --oracle DB user name & password

5. Enable AllowInProcess in SQL Server

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

6. Test Linked Server

7. Fetch records from Oracle DB using Linked Server in MS SQL Server

ORACLE_DB---Linked server name

SELECT * FROM OPENQUERY([ORACLE_DB] , 'select name,open_mode,host_name from v$database,v$instance') ;

SQL script to push the grants to a group in AWS Redshift DB for Read and write access

 1.select 'grant usage on schema ' ||nspname||' to <new_group_name>;' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';  ---1

2.select 'grant select,insert,update,delete on all tables in schema ' ||nspname||' to <new_group_name>;' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';     ---2

3.select 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' ||nspname||' for user "<table_owner>" GRANT select,insert,update,delete ON tables to <new_group_name>;' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';      ---3

SQL script to change the ownership of schema, table ,procedure in AWS Redshift Database

 

SQL script to change the ownership of schema, table ,procedure in AWS Redshift Database

1.change the ownership of schema

select 'alter schema ' ||nspname||' owner to "<new_schema_name>";' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';

2.change the ownership of table

select 'alter table   klg_nga_kla.'||schemaname||'."'||tablename||'" owner to "<new_schema_name>";'

from pg_catalog.pg_tables where schemaname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and schemaname not like 'pg_%';

3.change the ownership of procedure

SELECT 'alter  procedure '||n.nspname||'."'||p.proname||'" owner to "<new_schema_name>";'

    

FROM

    pg_catalog.pg_namespace n

JOIN pg_catalog.pg_proc p ON

    pronamespace = n.oid

join pg_catalog.pg_user b on

    b.usesysid = p.proowner

where

    nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';

--add argument on the procedue while changing the ownership

AWS Redshift SQL scripts to find out session and Audit information

 What is the best way to assess tables that need to be vacuumed or analyzed?

This query returns tables where greater than 20% of rows are unsorted or statistics are 20% stale.

SELECT "database", "schema", "table", unsorted, stats_off

FROM svv_table_info

WHERE unsorted > 20

OR stats_off > 20

How can I troubleshoot loading errors?

Selecting from stl_load_errors provides information about errors during loading, and can be helpful for troubleshooting problematic loads.

SELECT *

FROM stl_load_errors

ORDER BY starttime DESC

LIMIT 100;

How to look specifically for failed logins?

SELECT *

FROM stl_connection_log

WHERE event='authentication failure'

ORDER BY recordtime;

Showing successfully authenticated users with the number of successful authentications:

SELECT username, event, COUNT(*)

FROM stl_connection_log

WHERE event = 'authenticated'

GROUP BY 1, 2

ORDER BY 3 DESC;

Showing successfully authenticated users by hourly buckets:

SELECT DATE_PART(YEAR, recordtime) || '-' ||

LPAD(DATE_PART(MONTH, recordtime),2,'0') || '-' ||

LPAD(DATE_PART(DAY, recordtime),2,'0') || ' ' ||

LPAD(DATE_PART(HOUR, recordtime),2,'0') AS hour_bucket, username, COUNT(*)

FROM stl_connection_log

WHERE event = 'authenticated'

GROUP BY 1, 2

ORDER BY 1, 2 DESC;

Showing a list of the connection drivers used by the redshift users:

SELECT username, application_name, COUNT(*) 

FROM stl_connection_log

WHERE application_name != ''

GROUP BY 1,2

ORDER BY 1,2;

Privilege violation logging & monitoring in AWS Redshift

Prior to setting such access controls, you will be able to see queries pulling data from these resources by querying STL_QUERY, as seen below: Retrieving queries access to specific objects in Redshift:

SELECT * FROM STL_QUERY

WHERE userid!=1

AND querytxt LIKE '%customers%'

ORDER BY query DESC

LIMIT 100;

SELECT username,dbname,recordtime

FROM stl_connection_log

WHERE event='authentication failure'

ORDER BY recordtime > '2022-07-08';

 Get the disk based queries information for last 2 days

SELECT q.query, 

       q.endtime - q.starttime             AS duration, 

       SUM(( bytes ) / 1024 / 1024 / 1024) AS GigaBytes, 

       aborted, 

       q.querytxt 

FROM   stl_query q 

       join svl_query_summary qs 

         ON qs.query = q.query 

WHERE  qs.is_diskbased = 't' 

       AND q.starttime BETWEEN SYSDATE - 2 AND SYSDATE 

GROUP  BY q.query, 

          q.querytxt, 

          duration, 

          aborted 

ORDER  BY gigabytes DESC ;

/* Query showing information about sessions with currently running queries */

SELECT s.process AS pid

       ,date_Trunc ('second',s.starttime) AS S_START

       ,datediff(minutes,s.starttime,getdate ()) AS conn_mins

       ,trim(s.user_name) AS USER

       ,trim(s.db_name) AS DB

       ,date_trunc ('second',i.starttime) AS Q_START

       ,i.query

       ,trim(i.query) AS sql

FROM stv_sessions s

  LEFT JOIN stv_recents i

         ON s.process = i.pid

        AND i.status = 'Running'

WHERE s.user_name <> 'rdsdb'

ORDER BY 1;

/* Query shows EXPLAIN plans which flagged "missing statistics" on the underlying tables */

SELECT substring(trim(plannode),1,100) AS plannode

       ,COUNT(*)

FROM stl_explain

WHERE plannode LIKE '%missing statistics%'

AND plannode NOT LIKE '%redshift_auto_health_check_%'

GROUP BY plannode

ORDER BY 2 DESC;

/* query showing queries which are waiting on a WLM Query Slot */

SELECT w.query

       ,substring(q.querytxt,1,100) AS querytxt

       ,w.queue_start_time

       ,w.service_class AS class

       ,w.slot_count AS slots

       ,w.total_queue_time / 1000000 AS queue_seconds

       ,w.total_exec_time / 1000000 exec_seconds

       ,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_seconds

FROM stl_wlm_query w

  LEFT JOIN stl_query q

         ON q.query = w.query

        AND q.userid = w.userid

WHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE)

AND   w.total_queue_Time > 0

-- and q.starttime >= dateadd(day, -7, current_Date)    

-- and ( querytxt like 'select%' or querytxt like 'SELECT%' ) 

ORDER BY w.total_queue_time DESC

         ,w.queue_start_time DESC limit 35;

/* query showing queries which are waiting on a WLM Query Slot */

SELECT w.query

       ,substring(q.querytxt,1,100) AS querytxt

       ,w.queue_start_time

       ,w.service_class AS class

       ,w.slot_count AS slots

       ,w.total_queue_time / 1000000 AS queue_seconds

       ,w.total_exec_time / 1000000 exec_seconds

       ,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_seconds

FROM stl_wlm_query w

  LEFT JOIN stl_query q

         ON q.query = w.query

        AND q.userid = w.userid

WHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE)

AND   w.total_queue_Time > 0

-- and q.starttime >= dateadd(day, -7, current_Date)    

-- and ( querytxt like 'select%' or querytxt like 'SELECT%' ) 

ORDER BY w.total_queue_time DESC

         ,w.queue_start_time DESC limit 35;

Shell script to check and record the database size in Redshift Database

Here is the custom script used to check the Redshift DB size and record it every month .This  will  be useful to get the know the DB growth.

1.create the table on DB  to store the DB size

CREATE TABLE admin.rs_db_size_dev (

    db_name    VARCHAR(256) ENCODE zstd,

    db_size_gb NUMERIC(    db_size_gb NUMERIC(37,2) ENCODE az64,

    date       DATE ENCODE az64

)

DISTSTYLE AUTO

2.schedule the job on cron

2 0 12 * *  /redshiftadmin/aws/scripts/dev_db_size_chk.sh DB1_DW1_India >> /redshiftadmin/aws/audit/log/db_size_dev_india.log 2>&1

3.load the script to  the server.

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

## purpose     :script used to collect Redshift dev DB size details                              #

## Author      : Bala P                                                                          #

## Developed   :10-july-2023 V1                                                                  #

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

#

##!/usr/bin/bash

#

export PGHOST=XXXXXXXXXXX.redshift.amazonaws.com

export PGPORT='5439'

export PGDATABASE=$1

export PGUSER=rsdbadmin

export PGPASSWORD='XXXXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

query_result=$(psql -tA -c  "INSERT INTO admin.rs_db_size_dev (db_name, db_size_GB, date)

SELECT     dbase_name, total_GB AS db_size, CURRENT_DATE   FROM ( SELECT   dbase_name,  SUM(megabytes/1024) AS total_GB

FROM  admin.v_space_used_per_tbl   GROUP BY   dbase_name ) AS aggregated_data;")

#

#

if [[ -n "$query_result" ]]; then

##   if [[ -n "$query_result" && $(echo "$query_result" | grep -c 'exec_time_hours > 0.001') -gt 0 ]]; then

#       

recipient="BALAS@abc.com"

       subject="DB size  @ $PGDATABASE in Dev"

       body="DB size in dev cluster\n$query_result"

       echo -e "$body" | mailx -s "$subject" "$recipient"

      fi

4.output of the script

=# select * from admin.rs_db_size_dev;

 db_name    | db_size_gb |    date

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

DB1_DW1_India |    1654.00 | 2024-10-12

DB1_DW1_India |    1671.00 | 2024-11-12

DB1_DW1_India |    1515.00 | 2024-12-12

DB1_DW1_India |    1528.00 | 2025-01-12

DB1_DW1_India |    1573.00 | 2025-02-12

DB1_DW1_India |    1579.00 | 2025-03-12

DB1_DW1_India |    1666.00 | 2025-04-12

DB1_DW1_India |    1787.00 | 2025-05-12

DB1_DW1_India |    1788.00 | 2025-06-12

DB1_DW1_India |    1791.00 | 2025-07-12

DB1_DW1_India |    1788.00 | 2025-08-12

DB1_DW1_India |    1796.00 | 2025-09-12

DB1_DW1_India |    1801.00 | 2025-10-12

DB1_DW1_India |    1801.00 | 2025-11-12

DB1_DW1_India |    1801.00 | 2025-12-12

DB1_DW1_India |     249.00 | 2023-11-10

DB1_DW1_India |     249.00 | 2023-11-10

DB1_DW1_India |     249.00 | 2023-11-12

DB1_DW1_India |     246.00 | 2023-12-12

DB1_DW1_India |     166.00 | 2024-01-12

DB1_DW1_India |     318.00 | 2024-02-12

DB1_DW1_India |     236.00 | 2024-03-12

DB1_DW1_India |     334.00 | 2024-04-12

DB1_DW1_India |     772.00 | 2024-05-12

DB1_DW1_India |     965.00 | 2024-06-12

DB1_DW1_India |    1082.00 | 2024-07-12

DB1_DW1_India |    1528.00 | 2024-08-12

DB1_DW1_India |    1606.00 | 2024-09-12

(28 rows)

Snowflake Database Admin scripts

 Snowflake DB Admin scripts:

Snowflake DB admin commands/scripts

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

To know the current_user

SELECT CURRENT_USER();

To know the current_Role

SELECT CURRENT_ROLE();

To know the current_DB

SELECT CURRENT_DATABASE();

To use another DB

use DWDB1

To list down the DB's

SHOW DATABASES;

select * from information_schema.databases;

To list down the tables,views,users

show tables;

SHOW WAREHOUSES;

SHOW USERS;

show views;

show functions;

show procedures;

SHOW SCHEMAs;

show roles;

we got Oracle database error ORA-38774: cannot disable media recovery - flashback database is enabled

 Issue:

we got Oracle database error  ORA-38774: cannot disable media recovery - flashback database is enabled when tried to turn off the archive log mode .

Root cause:

oracle DB will not allow  to turn off the archive log mode ,until you disable the Flashback.

Solution:

Total System Global Area 8.7242E+10 bytes

Fixed Size                  5295200 bytes

Variable Size            9663677344 bytes

Database Buffers         7.7309E+10 bytes

Redo Buffers              263139328 bytes

Database mounted.

SQL> alter database noarchivelog;

alter database noarchivelog

*

ERROR at line 1:

ORA-38774: cannot disable media recovery - flashback database is enabled

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

DB1       YES

SQL> alter database flashback off;

Database altered.

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

DB1       NO

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

DB1       MOUNTED

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /oracle/DB1/oraarch/DB1arch

Oldest online log sequence     361611

Current log sequence           361616

SQL>  select name,open_mode from v$database;

NAME      OPEN_MODE

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

DB1       READ WRITE

Shell script to find out super user list in AWS Redshift

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

# purpose     :script used to collect PROD  Redshift super user list for audit

# Author      : Bala P

# Developed   :19-sep-2022 V1

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

#!/usr/bin/bash

export PGHOST=XXXX.redshift.amazonaws.com

export PGPORT='5454'

export PGDATABASE=RS_DB1

export PGUSER=rsdbadmin

DBA=balamani@abc.com

export DBA

export PGPASSWORD='XXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

psql  -f $wdir/super_user_list.sql -o $logdir/super_user_list.sql_output.log

mailx -s "Super user list from Redshift PROD cluster " $DBA  < $logdir/super_user_list.sql_output.log

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

 super_user_list.sql

\qecho

select usename,usesuper FROM pg_catalog.pg_user_info where usesuper='true';

check_tbl_ownr_compl.sql

select  schemaname,tablename ,tableowner from pg_catalog.pg_tables where schemaname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and tableowner not in ('rsdbadmin') and schemaname not like 'pg_%';

 chk_tbl_owner_public.sql

select schemaname,tablename,tableowner  from pg_catalog.pg_tables  where schemaname='public';

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

shell script to find out long running session details in AWS Redshift database

 Here is the shell script to monitor the Long running session details in AWS redshift, we can schedule it .

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

### purpose     :script used to collect Redshift long running session details                     #

### Author      : Bala P                                                                          #

### Developed   :10-july-2023 V1                                                                  #

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

##

##!/usr/bin/bash

##

export PGDATABASE=$1

export PGUSER=rsdbadmin

export PGHOST=XXXXXXXXXX.redshift.amazonaws.com

export PGPORT='5439'

export PGPASSWORD='XXXXXXXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

 query_result=$(psql -tA -c "select SPLIT_PART(SUBSTRING(user_name, 5), '@', 1) as user_name, DATE_TRUNC('second',starttime) as start_time,status,pid,duration/ (1000000.0 * 60 * 60) AS exec_time_hoursi,query  from stv_recents where user_name not in ('rdsdb') and duration/(1000000.0 * 60 * 60) > 3;")

#

 if [[ -n "$query_result" ]]; then

recipient="balamani@abc.com"

       subject="Queries with Execution Time > 3 hours @ $PGDATABASE in PROD"

       body="user_name|starttime |state|pid|exec_time_hours, queries  execution time greater than 3 hours PROD:\n\n$query_result"

     echo -e "$body" | mailx -s "$subject" "$recipient"

            fi

shell script for defragmentation in oracle Database Tables

 Here is the shell script to do de-fragmentation activities for a table in Oracle database. 

You can feed the table name and schema name and run this script in back ground 

#!/bin/ksh

export ORACLE_HOME=/oracle/TESTDB5/1924

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=TESTDB5

TMP_LOG=/oracle/scripts/logs/trans_table_redef.log

echo > $TMP_LOG

sqlplus -s /nolog <<EOF >> $TMP_LOG

whenever sqlerror exit 1

conn / as sysdba

SET SERVEROUTPUT ON;

ALTER SESSION FORCE PARALLEL DML PARALLEL 2;

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('<schema_name>','<Table_name>', DBMS_REDEFINITION.CONS_USE_ROWID);

exit

EOF

# Check if SQL execution was successful

if [ $? -eq 0 ]; then

    SUBJECT="Table Redefinition Completed Successfully"

else

    SUBJECT="Table Redefinition Failed"

fi

# Send the log file via email

mailx -s "$SUBJECT" Balamani@abc.com < $TMP_LOG

shell script to build oracle database with easy interactive mode

 Here is the full  script which will get few input and build the oracle database and configure the listener,tnsnames.ora.pfile/spfile and convert the database to archive log 

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

#purpose       : Script used to build oracle DB,pass requested info                                     #

#Input needed  :DB_NAME,ORACLE_HOME,PORT#,DB CHARSET,sys,system,PDB pws,Redlog size in MB               #

#script will do: create DB,convert archive mode,setup listener& tns and start,add /etc/oratab,BCT       #

#Developed by  :Bala P  3March2025  v2                                                                  #

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

#!/bin/sh

# Prompt for database name and convert to uppercase

echo "Enter the database name:"

read DB_NAME

DB_NAME=$(echo $DB_NAME | tr '[:lower:]' '[:upper:]')

# Prompt the user for   port number

read -p "Enter the port number: " PORT_NUMBER

# Prompt the user for   Redolog size in MB

read -p "Enter the Redolog size in MB: " RLOG

# Prompt for Oracle DB Home

echo "Enter the Oracle DB Home full Path:"

read  ORACLE_HOME

# Check if sqlplus /oracle_home status

if [ ! -x "$ORACLE_HOME/bin/sqlplus" ]; then

echo " sqlplus not found at $ORACLE_HOME/bin. Please check ORACLE_HOME."

exit 1

else

  echo "Oracle_Home FOUND ,proceeding with next steps"

fi

echo -e "\n \n"

#Prompt for  DB CHARACTER SET

echo "Enter the DB  CHARACTER SET"

read charset

# Prompt for passwords

echo "Enter new password for SYS:"

read -s sysPassword

echo "Enter new password for SYSTEM:"

read -s systemPassword

echo "Enter new password for PDBADMIN:"

read -s pdbAdminPassword

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

# Create necessary directories

OLD_UMASK=`umask`

umask 0027

mkdir -p /oracle/${DB_NAME}

mkdir -p $ORACLE_HOME/dbs

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/adump

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/dpdump

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/pfile

mkdir -p /oracle/${DB_NAME}/audit

mkdir -p /oracle/${DB_NAME}/cfgtoollogs/dbca/${DB_NAME}

mkdir -p /oracle/${DB_NAME}/control1

mkdir -p /oracle/${DB_NAME}/control2

mkdir -p /oracle/${DB_NAME}/control3

mkdir -p /oracle/${DB_NAME}/mirrlogA

mkdir -p /oracle/${DB_NAME}/mirrlogB

mkdir -p /oracle/${DB_NAME}/origlogA

mkdir -p /oracle/${DB_NAME}/origlogB

mkdir -p /oracle/${DB_NAME}/data1

mkdir -p /oracle/${DB_NAME}/SystemTBS

mkdir -p /oracle/${DB_NAME}/UndoTBS

mkdir -p /oracle/${DB_NAME}/tempTBS

mkdir -p /oracle/${DB_NAME}/admin/scripts

umask ${OLD_UMASK}

# Create init<DB_NAME>.ora file

rm -f /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora

cat <<EOL > /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora

${DB_NAME}.__oracle_base='/oracle/${DB_NAME}'#ORACLE_BASE set from environment

${DB_NAME}.__db_cache_size=1451700

${DB_NAME}.__java_pool_size=16686

${DB_NAME}.__large_pool_size=16686

${DB_NAME}.__sga_target=1067918334

${DB_NAME}.__shared_io_pool_size=216920

${DB_NAME}.__shared_pool_size=2552992

${DB_NAME}.__data_transfer_cache_size=0

${DB_NAME}.__streams_pool_size=0

*.audit_file_dest='/oracle/${DB_NAME}/admin/${DB_NAME}/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/${DB_NAME}/control1/control01.ctl','/oracle/${DB_NAME}/control2/control02.ctl','/oracle/${DB_NAME}/control3/control03.ctl'

*.db_block_size=8192

*.db_domain='abc.domain.com'

*.db_files=200

*.db_name='${DB_NAME}'

*.diagnostic_dest='/oracle/${DB_NAME}'

*.filesystemio_options='SETALL'

*.log_archive_dest='/oracle/${DB_NAME}/oraarch/${DB_NAME}arch'

*.log_archive_format='%t_%s_%r.arc'

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=3000

*.parallel_execution_message_size=16384

*.parallel_max_servers=20

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=675m

*.processes=500

*.replication_dependency_tracking=FALSE

*.sessions=1000

*.sga_target=1g

*.undo_management='AUTO'

*.undo_retention=21600

*.undo_tablespace='UNDOTBS'

_disable_directory_link_check=TRUE

CONTROL_MANAGEMENT_PACK_ACCESS = "NONE"

EOL

# Create Oracle password file

rm -f $ORACLE_HOME/dbs/orapw${DB_NAME}

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw${DB_NAME} force=y format=12

# Run SQL scripts for DB creation and configuration

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

echo -e "\n \n creating the DB...."

sqlplus /nolog <<EOF

CONNECT SYS/${sysPassword} AS SYSDBA

conn / as sysdba

SET VERIFY OFF

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDB.log

STARTUP NOMOUNT PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';

CREATE DATABASE "${DB_NAME}"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1000

DATAFILE '/oracle/${DB_NAME}/SystemTBS/system01.dbf' SIZE 2072M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/oracle/${DB_NAME}/SystemTBS/sysaux01.dbf' SIZE 2192M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/${DB_NAME}/tempTBS/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

SMALLFILE UNDO TABLESPACE "UNDOTBS" DATAFILE  '/oracle/${DB_NAME}/UndoTBS/undotbs01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

CHARACTER SET "${charset}"

NATIONAL CHARACTER SET AL16UTF16

LOGFILE

GROUP 1(

    '/oracle/${DB_NAME}/origlogA/redo_g1m1.log',

    '/oracle/${DB_NAME}/mirrlogA/redo_g1m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 2 (

    '/oracle/${DB_NAME}/origlogB/redo_g2m1.log',

    '/oracle/${DB_NAME}/mirrlogB/redo_g2m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 3 (

    '/oracle/${DB_NAME}/origlogB/redo_g3m1.log',

    '/oracle/${DB_NAME}/mirrlogB/redo_g3m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 4 (

    '/oracle/${DB_NAME}/origlogA/redo_g4m1.log',

    '/oracle/${DB_NAME}/mirrlogA/redo_g4m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512

USER SYS IDENTIFIED BY "${sysPassword}" USER SYSTEM IDENTIFIED BY "${systemPassword}";

SPOOL OFF

----creating the needed tablespaces--------

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBFiles.log

CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/oracle/${DB_NAME}/data1/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;

ALTER DATABASE DEFAULT TABLESPACE "USERS";

SPOOL OFF

------loading oracle binaries--------------

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBCatalog.log

@$ORACLE_HOME/rdbms/admin/catalog.sql;

@$ORACLE_HOME/rdbms/admin/catproc.sql;

@$ORACLE_HOME/rdbms/admin/catoctk.sql;

@$ORACLE_HOME/rdbms/admin/owminst.plb;

CONNECT SYSTEM/${systemPassword}

@$ORACLE_HOME/sqlplus/admin/pupbld.sql;

CONNECT SYS/${sysPassword} AS SYSDBA

@$ORACLE_HOME/sqlplus/admin/pupdel.sql;

CONNECT SYSTEM/${systemPassword};

SPOOL /oracle/${DB_NAME}/admin/scripts/sqlPlusHelp.log

@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/JServer.log

@$ORACLE_HOME/javavm/install/initjvm.sql;

@$ORACLE_HOME/xdk/admin/initxml.sql;

@$ORACLE_HOME/xdk/admin/xmlja.sql;

@$ORACLE_HOME/rdbms/admin/catjava.sql;

@$ORACLE_HOME/rdbms/admin/catxdbj.sql;

SPOOL OFF

---checked above  this

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/context.log

@$ORACLE_HOME/ctx/admin/catctx.sql Xbkfsdcdf1ggh_123 SYSAUX TEMP LOCK; --no path

ALTER USER CTXSYS ACCOUNT UNLOCK IDENTIFIED BY "CTXSYS";

CONNECT CTXSYS/CTXSYS

@$ORACLE_HOME/ctx/admin/defaults/dr0defin.sql "AMERICAN";

CONNECT SYS/${sysPassword} AS SYSDBA

conn / as sysdba

ALTER USER CTXSYS PASSWORD EXPIRE ACCOUNT LOCK;

@$ORACLE_HOME/rdbms/admin/dbmsxdbt.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/ordinst.log

@$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/interMedia.log

@$ORACLE_HOME/ord/im/admin/iminst.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/cwmlite.log

@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/spatial.log

@$ORACLE_HOME/md/admin/mdinst.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/apex.log APPEND

@$ORACLE_HOME/apex/catapx.sql Xbkfsdcdf1ggh_123 SYSAUX SYSAUX TEMP /i/ NONE;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/postDBCreation.log

host $ORACLE_HOME/OPatch/datapatch -skip_upgrade_check -db ${DB_NAME};

CREATE SPFILE='$ORACLE_HOME/dbs/spfile${DB_NAME}.ora' FROM PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';

SELECT 'utlrp_begin: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

@$ORACLE_HOME/rdbms/admin/utlrp.sql;

SELECT 'utlrp_end: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

SELECT comp_id, status FROM dba_registry;

SHUTDOWN IMMEDIATE;

STARTUP;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/lockAccount.log

BEGIN

FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (

'SYS','SYSTEM') )

LOOP

IF item.AUTHENTICATION_TYPE='PASSWORD' THEN

  DBMS_OUTPUT.PUT_LINE('Locking and Expiring: ' || item.USERNAME);

  EXECUTE IMMEDIATE 'ALTER USER ' ||

         SYS.DBMS_ASSERT.ENQUOTE_NAME(

         SYS.DBMS_ASSERT.SCHEMA_NAME(

         item.USERNAME),FALSE) || ' PASSWORD EXPIRE ACCOUNT LOCK' ;

ELSE

  DBMS_OUTPUT.PUT_LINE('Locking: ' || item.USERNAME);

  EXECUTE IMMEDIATE 'ALTER USER ' ||

         SYS.DBMS_ASSERT.ENQUOTE_NAME(

         SYS.DBMS_ASSERT.SCHEMA_NAME(

         item.USERNAME),FALSE) || ' ACCOUNT LOCK' ;

END IF;

END LOOP;

END;

/

SPOOL OFF

EOF

# Set ORACLE_HOME

export ORACLE_HOME=$ORACLE_HOME

# Get the current hostname

SERVER_NAME=$(hostname)

# Define paths

TNS_PATH="$ORACLE_HOME/network/admin/tnsnames.ora"

LISTENER_PATH="$ORACLE_HOME/network/admin/listener.ora"

TNS_BACKUP_PATH="${TNS_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

LISTENER_BACKUP_PATH="${LISTENER_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

# Backup tnsnames.ora

if [ -f "$TNS_PATH" ]; then

  cp "$TNS_PATH" "$TNS_BACKUP_PATH"

  echo "Backup of tnsnames.ora created at $TNS_BACKUP_PATH"

fi

# Append to tnsnames.ora

cat <<EOF >> "$TNS_PATH"

${DB_NAME}.WORLD=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS =

        (PROTOCOL = TCP)

        (HOST = ${SERVER_NAME})

        (PORT = ${PORT_NUMBER})

      )

    )

    (CONNECT_DATA =

      (SID = ${DB_NAME})

      (GLOBAL_NAME = ${DB_NAME}.WORLD)

    )

  )

#for PROD

RMANCATPRD.WORLD=

  (DESCRIPTION =

    (SDU = 32768)

    (ADDRESS_LIST =

        (ADDRESS =

          (PROTOCOL = TCP)

          (HOST = <catlog_server>)

          (PORT = 1591)

        )

    )

    (CONNECT_DATA =

       (SID = KRMANCATPRD)

       (GLOBAL_NAME = RMANCATPRD.WORLD)

    )

  )

#prod end

EOF

echo "New TNS entry for ${DB_NAME} added to $TNS_PATH"

# Backup listener.ora

if [ -f "$LISTENER_PATH" ]; then

  cp "$LISTENER_PATH" "$LISTENER_BACKUP_PATH"

  echo "Backup of listener.ora created at $LISTENER_BACKUP_PATH"

fi

# Create listener.ora

cat <<EOF > "$LISTENER_PATH"

LISTENER_${DB_NAME} =

  (ADDRESS_LIST =

    (ADDRESS =

      (PROTOCOL = IPC)

      (KEY = ${DB_NAME}.WORLD)

    )

    (ADDRESS =

      (PROTOCOL = IPC)

      (KEY = ${DB_NAME})

    )

    (ADDRESS =

      (PROTOCOL = TCP)

      (HOST = ${SERVER_NAME})

      (PORT = ${PORT_NUMBER})

    )

  )

STARTUP_WAIT_TIME_LISTENER_${DB_NAME} = 0

CONNECT_TIMEOUT_LISTENER_${DB_NAME} = 10

TRACE_LEVEL_LISTENER_${DB_NAME} = OFF

SID_LIST_LISTENER_${DB_NAME} =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ${DB_NAME})

      (ORACLE_HOME = $ORACLE_HOME)

    )

  )

EOF

echo "New listener entry for ${DB_NAME} added to $LISTENER_PATH"

# Define paths for sqlnet.ora

SQLNET_PATH="$ORACLE_HOME/network/admin/sqlnet.ora"

SQLNET_BACKUP_PATH="${SQLNET_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

# Backup sqlnet.ora

if [ -f "$SQLNET_PATH" ]; then

  cp "$SQLNET_PATH" "$SQLNET_BACKUP_PATH"

  echo "Backup of sqlnet.ora created at $SQLNET_BACKUP_PATH"

fi

# Append to sqlnet.ora

cat <<EOF >> "$SQLNET_PATH"

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

NAME.DEFAULT_ZONE = WORLD

SQLNET.EXPIRE_TIME = 0

TCP.NODELAY=YES

EOF

#start the listener

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

lsnrctl start LISTENER_${DB_NAME}

TNSPING_LOG="tnsping_${DB_NAME}.log"

# Run tnsping and log the output

tnsping ${DB_NAME}.WORLD > "$TNSPING_LOG" 2>&1

# Check tnsping result

if grep -q "OK" "$TNSPING_LOG"; then

  echo "TNSPING successful: This is good"

else

  echo "TNSPING failed: Please check the configuration"

fi

# Check logs for errors or warnings

LOG_FILES="/oracle/${DB_NAME}/admin/scripts/*.log"

OUTPUT_FILE="/oracle/${DB_NAME}/admin/scripts/error_check.log"

ERRORS_FOUND=false

> "$OUTPUT_FILE"  # Clear previous output

for file in $LOG_FILES

do

  if grep -iq "ORA-" "$file" ; then

    ERRORS_FOUND=true

    ERR_FILE="${file}_err"

    # Extract relevant lines and save to .log_err file

    grep -iE "ORA-" "$file" > "$ERR_FILE"

    echo "Issues found in: $(basename "$file") $(basename "$ERR_FILE")" >> "$OUTPUT_FILE"

  fi

done

if [ "$ERRORS_FOUND" = false ]; then

  echo "Everything is good with Db creation." > "$OUTPUT_FILE"

fi

#Backup /etc/oratab with timestamp

timestamp=$(date +%Y%m%d_%H%M%S)

cp /etc/oratab /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp

# Append entry to /etc/oratab

echo "${DB_NAME}:${ORACLE_HOME}:Y" >> /etc/oratab

echo "Entry added to /etc/oratab and backup created as /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp"

# Set environment variables arch mode conversion

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

# Check if DB is already in ARCHIVELOG mode

archive_mode=$(sqlplus -s / as sysdba <<'EOF'

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SELECT log_mode FROM v$database;

EXIT

EOF

)

archive_mode=$(echo "$archive_mode" | xargs)  # Trim whitespace

if [ "$archive_mode" == "ARCHIVELOG" ]; then

  echo "Database is already in ARCHIVELOG mode. Skipping conversion."

else

  echo "Converting database to ARCHIVELOG mode..."

  sqlplus / as sysdba <<EOF

  SPOOL convert_db_archive_mode_bct.log

  SHOW PARAMETER spfile;

  CREATE PFILE='$ORACLE_HOME/pfile_before_arch.ora' FROM SPFILE;

  SHUTDOWN IMMEDIATE;

  STARTUP MOUNT;

  ALTER DATABASE ARCHIVELOG;

  ALTER DATABASE OPEN;

  ARCHIVE LOG LIST;

  alter database enable block change tracking using file '/oracle/${DB_NAME}/SystemTBS/${DB_NAME}_block_change.dbf';

  SPOOL OFF

  EXIT

EOF

fi

echo "exporting Db variables final check"

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

EMAIL="BALAmani@abc.com"

# Run SQL to get DB name and open mode

DB_STATUS=$(sqlplus -s / as sysdba <<'EOF'

SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF

SELECT open_mode FROM v$database;

EXIT;

EOF

)

# Check if SQL*Plus ran successfully

if [[ $? -ne 0 ]]; then

    echo "SQL*Plus execution failed."

    exit 1

fi

# Trim whitespace

DB_STATUS=$(echo "$DB_STATUS" | xargs)

# Log the status

echo "Database status returned: $DB_STATUS"

# Check if status is "READ WRITE"

if [[ "$DB_STATUS" == "READ WRITE" ]]; then

    echo "DB creation completed successfully." | mailx -s "DB $DB_NAME build completed, proceed with next steps" "$EMAIL"

else

    echo "Database is not in READ WRITE mode. Current status: $DB_STATUS"

    exit 1

fi

exit 0

SAP Oracle Database table migration to new tablespace using brtools

 issue:

we have requirement to move the table from tablespace A1 to B1 using brtools.

solution:

The below script and steps will take care the table movement to new tablespace without confirmation from the  user --brspace -u / -c force,it will force to continue.

brspace -u / -c force -f  tbreorg -s PSAPSR3 -o SAPSR3 -t /BIC/AZD_PUR8200 -n PSAPSR3TEST   -p 4

-f  tbreorg  ----> function name

-s PSAPSR3 ----> source schema tablespace name

 -o SAPSR3 ----> source schema tablespace name

-t /BIC/AZD_PUR8200 ---->  tables name that needs to be migrated

-n PSAPSR3TEST  ----> new  tablespace name

 -p 4   ---->parallel option

<server_name>:orab1p 67> brspace -u / -c force -f  tbreorg -s PSAPSR3 -o SAPSR3 -t /BIC/AZD_PUR8200 -n PSAPSR3TEST   -p 4

BR1001I BRSPACE 7.40 (47)

BR1002I Start of BRSPACE processing: sfrnodks.tbr 2025-07-17 11:22:10

BR0484I BRSPACE log file: /oracle/B1P/sapreorg/sfrnodks.tbr

BR0280I BRSPACE time stamp: 2025-07-17 11:22:11

BR1009I Name of database instance: B1P

BR1010I BRSPACE action ID: sfrnodks

BR1011I BRSPACE function ID: tbr

BR1012I BRSPACE function: tbreorg

BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0280I BRSPACE time stamp: 2025-07-17 11:22:13

BR0813I Schema owners found in database B1P: SAPSR3*

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR0657I Input menu 353 # please enter/check input values

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

Options for reorganization of tables: SAPSR3./BIC/AZD_PUR8200 (degree 1)

 1 * Reorganization action (action) ............ [reorg]

 2 - Reorganization mode (mode) ................ [online]

 3 - Create DDL statements (ddl) ............... [yes]

 4 ~ New destination tablespace (newts) ........ [PSAPSR3TEST]

 5 ~ Separate index tablespace (indts) ......... []

 6 - Parallel threads (parallel) ............... [4]

 7 ~ Table/index parallel degree (degree) ...... []

 8 ~ Category of initial extent size (initial) . []

 9 ~ Sort by fields of index (sortind) ......... []

10 # Index for IOT conversion (iotind) ......... [FIRST]

11 - Compression action (compress) ............. [none]

12 # LOB compression degree (lobcompr) ......... [medium]

13

Standard keys: c - cont, b - back, s - stop, r - refr

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

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR0134I Unattended mode with 'force' active - continuing processing with default reply 'cont'

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR1108I Checking tables for reorganization...

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR1112I Number of tables selected/skipped for reorganization: 1/0

BR0370I Directory /oracle/B1P/sapreorg/sfrnodks created

BR0280I BRSPACE time stamp: 2025-07-17 11:22:15

BR1101I Starting 'online' table reorganization...

BR0280I BRSPACE time stamp: 2025-07-17 11:22:15

BR1124I Starting 'online' reorganization of table SAPSR3./BIC/AZD_PUR8200 ...

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1105I Table SAPSR3./BIC/AZD_PUR8200 reorganized successfully

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1141I 1 of 1 table processed - 23691300 of 23691300 rows done

BR0204I Percentage done: 100.00%, estimated end time: 11:24

BR0001I **************************************************

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1102I Number of tables reorganized successfully: 1

BR0280I BRSPACE time stamp: 2025-07-17 11:24:37

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbreorg' completed

BR1008I End of BRSPACE processing: sfrnodks.tbr 2025-07-17 11:24:37

BR0280I BRSPACE time stamp: 2025-07-17 11:24:38

BR1005I BRSPACE completed successfully

script to list the oracle database ORA- errors from oracle Database views

 

script to list the oracle database ORA- errors

COLUMN ORIGINATING_TIMESTAMP FORMAT A40

COLUMN message_text FORMAT A100

set linesize 300

set pages 300

SELECT ORIGINATING_TIMESTAMP , message_text

FROM X$DBGALERTEXT

WHERE originating_timestamp > (SYSDATE - 5)

AND message_text LIKE '%ORA-%'

ORDER BY originating_timestamp;

ORIGINATING_TIMESTAMP                    MESSAGE_TEXT

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

14-JUL-25 11.25.14.115 AM +01:00         ORA-1100 signalled during: alter database mount...

15-JUL-25 01.52.59.280 PM +01:00         Patch Description: ORA-00800  SOFT EXTERNAL ERROR, ARGUMENTS  [SET PRIORITY FAILED], [VKTM] , DISM(1

                                         6)

Script to find out user query running which priority in AWS Redshift Database

 

Script to find out user  query running  which priority in AWS Redshift Database:

 SELECT w.query, i.userid, w.service_class, w.state,w.query_priority

 FROM stv_wlm_query_state w

 JOIN stv_inflight i ON w.query = i.query

WHERE w.state = 'Running' and i.userid in ('219','384','105');

Redshift_TEST_DB=# SELECT w.query, i.userid, w.service_class, w.state,w.query_priority

Redshift_TEST_DB-#  FROM stv_wlm_query_state w

Redshift_TEST_DB-#  JOIN stv_inflight i ON w.query = i.query

Redshift_TEST_DB-# WHERE w.state = 'Running' and i.userid in ('219','384','105');

   query   | userid | service_class |      state       |    query_priority

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

 129306353 |    105 |           100 | Running          | High

 129302344 |    105 |           100 | Running          | High

(2 rows)

script to find out assumerole granted to user in AWS redshift Database.

 

script to find out assumerole granted to user in AWS redshift.

select username, iam_role, cmd FROM pg_get_iam_role_by_user('TESTUSER@abc.domain.com') res_iam_role(username text, iam_role text, cmd text);

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle 19.24.0.0

Issue:

we had ORA-800 popping up after the DB creation  and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.

Error:

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle document and MOS notes] [] [] [] [] [] [] [] []

[00]: dbgexExplicitEndInc [diag_dde]

[01]: dbgeEndDDEInvocationImpl [diag_dde]

[02]: ksbsethighpri_int [background_proc]<-- Signaling

[03]: ksbrdp [background_proc]

[04]: opirip [OPI]

solution :

we had ORA-800 popping up after the DB creation  and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.Then we applied the below Patch and  issue resolved.

p34672698_1924000DBRU_Linux-x86-64.zip

no more ORA-800 on the alert log.

Step -by Step Applying the Patch:

**********************bug patch apply on standalone server************************

(I) Prerequisites

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

Before you install or deinstall the patch, ensure that you meet the following requirements:

1. Ensure that the Oracle home on which you are installing the patch or from which you are rolling back the patch is Oracle Database 19 Release 19.24.0.0.240716DBRU.

2. Ensure that 19 Release 19.24.0.0.240716DBRU Patch Set Update (PSU) 36582781 is already applied on the Oracle Database.

3. Oracle recommends you to use the latest version available for 19 Release 19.24.0.0.240716DBRU. If you do not have OPatch 19 Release 19.24.0.0.240716DBRU, then download it from patch# 6880880 for 19.24.0.0.240716DBRU release.

4. Ensure environment variable ORACLE_HOME is set correctly.

5. Ensure that the $PATH definition has the following executables: make, ar, ld and nm. The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin.

6. Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched. Run the following command as respective Oracle home owner to check the consistency.

    % $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

    Note:

    - If this command succeeds, it lists the Oracle components that are installed in the home.

    - Save the output so you have the status prior to the patch apply.

    - If this command fails, contact Oracle Support Services for assistance.

7. (Only for Installation) Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>. Extract the contents of the patch ZIP file to the location (PATCH_TOP_DIR) you have created above. To do so, run the following command:

    $ unzip -d <PATCH_TOP_DIR> p34672698_1924000DBRU_Linux-x86-64.zip

8. (Only for Installation) Determine whether any currently installed interim patches conflict with this patch 34672698 as shown below:

    $ cd <PATCH_TOP_DIR>/34672698

    $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

    The report will indicate the patches that conflict with this patch and the patches for which the current 34672698 is a superset.

    Note:

    Upon execution of the Opatch command, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME. OPatch categorizes conflicts into the following types:

    - Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply  - In this case, continue with the patch installation because the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.

    - Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services.

(II) Installation

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

To install the patch, follow these steps:

     As root user, change oradism permissions

        Pre-install    //these make the existing oradism owned by grid home owner and replaceable

        a.ls -ld $GI_HOME/bin/oradism

          i.-rwsr-x--- 1 root  dba  <GI_HOME>/bin/oradism

2. Apply Patch to DB home(s) as DB home owner :

       Set your current directory to the directory where the patch is located

       and then run the OPatch utility by entering the following commands:

       As root user, change oradism permissions:

       $ cd <PATCH_TOP_DIR>/34672698

         Pre-install    //these make the existing oradism owned by "oracle" user and replaceable

         a.ls -ld $ORACLE_HOME/bin/oradism

           i.-rwsr-x--- 1 root  dba  <ORACLE_HOME>/bin/oradism

         b.change ownership to the DB home owner (i.e oracle)

           i.chown oracle $ORACLE_HOME/bin/oradism

           ii.chmod 0750 $ORACLE_HOME/bin/oradism

         c.ls -ld $ORACLE_HOME/bin/oradism

       As DB home owner, apply the patch:

make sure NO DB & listener process running

  ---> /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

<ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <PATCH_TOP_DIR>/34672698

3. Verify whether the patch has been successfully installed by running the following command:

   As  the Oracle Database home owner, run the following command:

   $ opatch lsinventory -oh <ORACLE_HOME>

3b. Post-install

    These make the new oradism binary owned by root with setuid, and executable by "oracle" user

3b.2 - For DB home(s) as root user

        a.ls -ld $ORACLE_HOME/bin/oradism

        b.change owner to 'root' and enable setuid bit

          i.chown root $ORACLE_HOME/bin/oradism

          ii.chmod 4750 $ORACLE_HOME/bin/oradism

        c.ls -ld $ORACLE_HOME/bin/oradism

----when I delete the patch ,it generate the error : when I apply the patch ,it stops the error

Starting background process VKTM

2025-07-03T11:25:14.278411+01:00

Errors in file /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/trace/TESTDB78_vktm_276905.trc  (incident=28045):

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []

Incident details in: /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/incident/incdir_28045/TESTDB78_vktm_276905_i28045.trc

2025-07-03T11:25:14.279793+01:00

----Stop the DB services

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

----pre-checks

<server_name>:/oracle/TESTDB78/19.0.0/34672698> /oracle/TESTDB78/19.0.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.46

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

   from           : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version    : 12.2.0.1.46

OUI version       : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-15_13-28-23PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Version 19.24.0.0.0

[oracle@<server_name> 34672698]$ ls -ld $ORACLE_HOME/bin/oradism

-rwxr-x--- 1 oracle dba 147848 Jul  3 11:14 /oracle/TESTDB78/19.0.0/bin/oradism

[oracle@<server_name> 34672698]$ /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

Oracle Interim Patch Installer version 12.2.0.1.46

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

   from           : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version    : 12.2.0.1.46

OUI version       : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-42-39AM_1.log

Verifying environment and performing prerequisite checks...

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

Start OOP by Prereq process.

Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.46

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

   from           : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version    : 12.2.0.1.46

OUI version       : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   34672698

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/oracle/TESTDB78/19.0.0')

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '34672698' to OH '/oracle/TESTDB78/19.0.0'

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 34672698 successfully applied.

Log file location: /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

OPatch succeeded.

script to check if the schema part of the data share or not in aws Redshift database

 script to check if the schema  part of the data share or not in aws Redshift database:

 SELECT * FROM SVV_DATASHARE_OBJECTS where object_type='schema' and object_name='<schema_name>';

share_type|share_name       |object_type|object_name|producer_account|producer_namespace  

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

outbound |abcprod_k1222_ds|schema     |schema_name|122234555555    |1234567890

script to check select access for entire schema in AWS Redshift database.

 

script to check  select access for entire schema in AWS Redshift database.

SELECT 

    tablename,

    usename

FROM

    pg_catalog.pg_tables AS tables,

    pg_catalog.pg_user AS users

WHERE 

    tables.schemaname = '<schema_name>'

    AND users.usename = 'username@abc.com'

    AND tables.schemaname not  like 'pg_%'

    AND NOT HAS_TABLE_PRIVILEGE(users.usename, tables.tablename, 'select');

script to check user has select,insert,update,delete access on specific schema in AWS Redshift Database

 script to check user has select,insert,update,delete  access on specific schema in AWS Redshift Database:

SELECT 

     tablename

     ,usename

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'select') AS sel

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'insert') AS ins

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'update') AS upd

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'delete') AS del

FROM

(SELECT * from FROM pg_catalog.pg_tables

WHERE schemaname = '<schema_name>' ) as tables

,(SELECT * FROM pg_catalog.pg_user where usename='username@abc.com') AS users;

script to check user has select,insert,update,delete access on specific table in schema in AWS Redshift Database

 script to check user has select,insert,update,delete  access on specific table in schema in AWS Redshift Database

SELECT 

     tablename

     ,usename

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'select') AS sel

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'insert') AS ins

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'update') AS upd

     ,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'delete') AS del

FROM

(SELECT * from FROM pg_catalog.pg_tables

WHERE schemaname = '<schema_name>' and tablename in ('dm_sales_rptg_ecomm_new')) as tables

,(SELECT * FROM pg_catalog.pg_user where usename='username@abc.com') AS users;