CRS_LIMIT_NPROC=16384 in s_crsconfig_hostname_env.txt file

 

Looks like there is a bug in the Oracle. Even after increasing the OS variables, “ORACLE” is not picking up the values.

Looks like Listener has a default max limit of 16384 in Oracle file , so it will not allowing any listener connections more than the said limit.

 

• ps -ef| grep LOCAL |wc -l               ---------- make sure this is less than 16384  ( if this value is close then new sessions will not be made using listener.

          Note: this is nothing to do with processes that added in the linux config files.

------Here are the steps we need to do to increase the value in this file.

 

$grid_home/crs/install/s_crsconfig_hostname_env.txt

 

Change this value “CRS_LIMIT_NPROC=16384"  To more value to accept more connections.

 This value is picked up by CRS when we start the oracle DB.

Note: we need to Bounce the CRS to take effect.

avoid Cartesian merge join in the explain plan.

Cartesian Merge Join are sometimes more expensive. takes long time to run and uses resources.

To avoid the cartesian merge join you can disable  using below command to make it run in that session. You dont want to apply this change to the DB as it would need oracle support to disable it.

alter session set "_optimizer_cartesian_enabled"=FALSE;

sql output to csv. (with comma in the column data).

 

Script to generate csv output. this scripts covers comma's in the column value to print it correct.

set pagesize 1000

set linesize 1000

set feedback off

set heading off

set echo off

select 'ABC_xyz.csv' spool_name  from dual;

spool &spool_name

select '"sysdate",

'||'"col_name_1",

'||'"col_name_2",

'||'"col_name_3",

'||'"col_name_4",

'||'"col_name_5"' from dual

union all

select distinct '"'||sysdate||'",'

||'"'||col_name_1||'",'

||'"'||col_name_2||'",'

||'"'||col_name_3||'",'

||'"'||col_name_4||'",'

||'"'||to_char(B.col_name_5,'DD-MON-YYYY HH24:MI:SS')||'"'

FROM Table A;

spool off;

Script to find parent-child relationships between tables in oracle.

 Edit the below script and provide the inputs when executed 

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

accept tablename prompt 'Enter the table name:'

accept tableowner prompt 'Enter the table owner name:'

SET LINESIZE 255

set verify off

col GIVEN_TABLE for a30

col PARENT_TABLE for a30

col CHILD_TABLE for a30

col CHILD_TABLE_OWNER for a20

col GIVEN_TABLE_OWNER for a20

col PARENT_TABLE_OWNER for a20

col PARENT_TAB_CONST for a30

col CHILD_TAB_FK_CONST for a30

col G_TAB_columns for a30

col GIVEN_FK_CONST_NAME for a30

col G_TAB_FK_COLUMNS for a30

col GIVEN_TAB_CONST for a30

Prompt Below are the Child tables for &tablename table.

prompt -----------------------------------------------

SELECT p.table_name GIVEN_TABLE, p.owner GIVEN_TABLE_OWNER, p.constraint_name GIVEN_TAB_CONST,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_columns

,c.owner CHILD_TABLE_OWNER,c.table_name CHILD_TABLE,c.constraint_name CHILD_TAB_FK_CONST

FROM dba_constraints p, dba_constraints c,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and p.table_name=t.table_name

and p.constraint_name=t.constraint_name

AND p.table_name = UPPER('&tablename')

and p.owner=('&tableowner')

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

Prompt Below are the Parent tables for &tablename table.

prompt ------------------------------------------------

SELECT c.table_name GIVEN_TABLE,c.owner GIVEN_TABLE_OWNER, c.constraint_name GIVEN_FK_CONST_NAME,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_FK_columns,p.owner PARENT_TABLE_OWNER,p.table_name PARENT_TABLE

,p.constraint_name PARENT_TAB_CONST

FROM dba_constraints p, dba_constraints c ,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and c.table_name=t.table_name

and c.constraint_name=t.constraint_name

AND c.table_name = UPPER('&tablename')

and c.owner='&tableowner'

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

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

script to Block users from logging to schema directly from sql developer or Toad

Edit below script to work as per the need: 

CREATE OR REPLACE TRIGGER BLOCK_USER_LOGIN

AFTER LOGON ON Schema_NAME

DECLARE

v_prog sys.v$session.program%TYPE;

v_name sys.v$session.osuser%TYPE;

BEGIN

SELECT program INTO v_prog

FROM v$session a,dba_users b

where 

a.username=b.username and

(a.program like 'SQL D%' or a.program like 'TOAD') and

AND  a.audsid != 0;

 

IF (v_prog) LIKE '%SQL Dev%'    

THEN

insert into UNFI_TEST_LOGON_DETAILS values (v_prog,sysdate);

commit;

--RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login as schema owner.');

END IF;

END;

/

to drop the changes....

drop trigger BLOCK_USER_LOGIN;

mysql: script to email when sessions in mysql instance are high or reached defined max value.

 Edit once you have copied to Vi Editor

1) session_count.sql

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

tee ./logs/session.log

select count(*) from information_schema.processlist;

notee;

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

2 ) Session_count.sh

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

#!/bin/ksh

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

# PROGRAM NAME: session_count.sh   for MySQL

#

# USAGE: ./session_count.sh 

#

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

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d%H%M")

hostname=`hostname -s`

MAILTO=USER_SIVA@ABC.com

PWD=/opt/siva/scripts/sessions  # put script dir path if you want to run from "crontab"

repdir=$PWD

LOG=$repdir/logs

if [ ! -d $LOG ]; then

mkdir  $LOG

else 

rm $LOG/session.log

if [ -f $LOG/session_details.html ]; then

rm $LOG/session_details.html

fi

fi

####### ******************** Below list of changes are req:

conf_file=/etc/my.cnf

BACKUP_USER=TEST_USER     # can be root.

BACKUP_PASS=`cat /opt/.orapasswd/.backupusr`     # to hide password

Vald_sess_C=200             # max value limit to email.

log_ret=30                  # retention for logfiles

Logfile=mysql_Session_"$hostname"_$DATE.log

echo " started $DATE" >>$LOG/$Logfile

#### Changes are done:

####### ******************** run Active file:

if [ -f $LOG/bkp_active_file ]

 then

   echo "previous mysql session_count.sh script is still running. confirm before running again" | mailx -s "Mysql-Session Status: $hostname - Failed to execute" $MAILTO

   exit 0

fi

###

touch $LOG/bkp_active_file

mysql -Ns -u TEST_USER -p$BACKUP_PASS < $repdir/session_count.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1 

####### ******************** Check the session count:

Check_Sess_C=`cat $LOG/session.log`

if [ $Check_Sess_C -gt $Vald_sess_C ];

then

mysql -H -u TEST_USER -p$BACKUP_PASS < $repdir/session_details.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1

####### ******************** Email Output to Mail:

( echo " HOST: $hostname - MySQL Sessions are High - Details: "

  echo "To: USER_SIVA@ABC.com"

  echo "From:  <$hostname> "

   echo "MIME-Version: 1.0"

   echo "Content-Type: text/html; charset=US-ASCII"

echo "<html>"

echo "<body>"

   echo "<font size="+2">" "<font color="red">"

   echo "<u>"

   echo "Mysql sessions are high - Count - $Check_Sess_C ."

   echo "</u>"

   echo "<br/>"

   echo "<font size="-1">" "<font color="black">"

   echo "<pre>"

   cat $LOG/session_details.html

   echo  "<pre>"

   echo "Successful end of $repdir/session_count.sh"

echo "</body>"

echo "</html>"

 ) | /usr/sbin/sendmail $MAILTO

### End of Email

fi

####### ******************** Purge Log directory:

if [ ! -d $LOG ]; then

mkdir  $LOG

else

/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;

fi

####### ******************** Remove run active file:

if [ -f $LOG/bkp_active_file ]

 then

rm $LOG/bkp_active_file

echo " removed run-active file" >> $LOG/$Logfile

fi

exit

### All steps are Done.

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

Mysql : Script to restore backup using MEB.

 Edit once you have copied to Vi editor...

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

#!/bin/ksh

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

# PROGRAM NAME: mysql-meb-restore_auto.sh 

#

# USAGE: Mysql- Restoring databases from MEB backup. 

#

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

#

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d%H")

hostname=`hostname -s`

MAILTO=USER_SIVA@ABC.com

PWD=/opt/siva/scripts/restore  # put script dir path if you want to run from "crontab"

repdir=$PWD

LOG=$repdir/dblogs

echo $LOG

## *********************** Below list of changes are req:

conf_file=/etc/my.cnf

BACKUP_USER=TEST_USER

BACKUP_PASS=`cat /opt/.orapasswd/.backupusr`       # to hide password

datadir=/opt/data/mysql   #### make sure this is correct.

bkup_dir_a=/backup/test/TESTDB/meb/FULL/      # Backup location hardcoded:

innodb_log_file_v=1610612736                  # change innodb_log_file_size as  in the backup file 

Logfile=mysql_restore_auto_"$hostname"_$DATE.log

log_ret=15                                    # retention for logfiles.

echo  "started $DATE" >>$LOG/$Logfile

## Disable below lines if $bkup_dir is hardcoded:   below lines will automatically select last night backup.

p_date=$(date --date='-1 day' '+%Y-%m-%d')

bk_dir="$p_date*"

bkup_dir_f=`find $bkup_dir_a -type d -name "$bk_dir"`

bkup_dir=$bkup_dir_f

echo $bkup_dir

#bkup_dir=/backup/test/TESTDB/meb/FULL/2020-07-20_23-00-08   # disable above line and enable this line if you want to mention backup directroy.

########## Changes are done:

#**** Check if Backup directory exists:

if [ ! -d "$bkup_dir" ]; then

echo "backup directory $bkup_dir doesn't exists" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

exit;

else

echo "$bkup_dir exists" 

fi

###

## ***************** run Active file:

if [ -f $LOG/bkp_active_file ]

 then

   echo "previous mysql restore/backup is still running. confirm before running again" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

   exit 0

fi

### checking Logs Directory:

if [ ! -d $LOG ]; then

mkdir  $LOG

else

/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;

fi

####

## ************************************************* Prep Steps before Start of Restore:

touch $LOG/bkp_active_file

####### Checking MySQL Instance status:

echo "Step 1: checking MySQL Instance status:"

inst_up=`ps -ef| grep mysqld.pid |wc -l`

if [ "$inst_up" -gt 1 ]; then

echo "Mysql instance is up. Shutting down now."

sudo systemctl stop mysqld

RETCODE=$?

print  "RETCODE:  $RETCODE"

if [ "$RETCODE" -ne 0 ]; then

   print  "echo MySQL Instance did not shutdown cleanly. please check" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

rm $LOG/bkp_active_file

exit 0;

fi

else

echo "Mysql instance is not up"

fi

#### 

### Prep step for Mysql Restore:

echo "Step 2: cleaning the $datadir for restore:"

cd $datadir

sudo /usr/bin/rm -r $datadir/* 

sudo /usr/bin/rm -r /data/log/*

####

## ************************************************* Start Of MySQL Restore:

echo "Step 3: MySQL restore started:"

/opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=/etc/my.cnf --port=1234 --host=localhost --user=$BACKUP_USER --password=$BACKUP_PASS --datadir=/data/data/mysql --innodb_log_file_size=$innodb_log_file_v --with-timestamp --backup-dir=$bkup_dir --messages-logdir=$LOG copy-back-and-apply-log >>$LOG/$Logfile 2>&1

####

## *********************** Check Any errors in the logfile:

echo "Step 4: Checking errors in Restore:"

error_chk=$(cat $LOG/$Logfile | awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /WARNING/{print buf,$0}' | wc -l)

if [[ $error_chk -le 1 ]]

  then

     echo "Mysql Restore completed successfully - ignorable warning(1) - $error_chk " |tee -a $LOG/$Logfile

   else

    echo "Mysql Restore  was unsuccessful - has more errors than required(1). - error count - $error_chk" |tee -a $LOG/$Logfile

    echo "Mysql Restore  was unsuccessful - has more errors than required(1). - error count - $error_chk . please check" | mailx -s "Mysql-Restore Status: Completed with errors" $MAILTO

fi

####

## ********************** Remove run active file:

if [ -f $LOG/bkp_active_file ]

 then

rm $LOG/bkp_active_file

echo " removed run-active file" |tee -a $LOG/$Logfile

fi

## ************************************************* Post steps after MySQL Restore: 

echo "Step 5: Changing $datadir permissions:"

sudo chown -R mysql.mysql $datadir              # enable this if you are using different owner than mysql.

echo "Step 6: Starting MySQL Instance:"

sudo systemctl start mysqld

RETCODE=$?

print  "RETCODE:  $RETCODE"

if [ "$RETCODE" -ne 0 ]; then

   print  "MySQL Instance did not start cleanly. please check" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

exit 0;

fi

## *********************** Final MySQL status:

inst_up=`ps -ef| grep mysqld.pid |wc -l`

if [ "$inst_up" -gt 1 ]; then

echo "Mysql-Restore Status: All steps are done" |tee -a $LOG/$Logfile

echo "Mysql instance is up - After-Restore - Final-step Done " | mailx -s "Mysql-Restore Status: $hostname - completed" $MAILTO

else

echo "Mysql instance is not up - After-Restore - Final-step failed" | mailx -s "Mysql-Restore Status: $hostname -failed " $MAILTO 

fi

####

## All steps Done: End