Thinking Out Loud

April 14, 2018

Framework To Run SQL For All Active DB Instances

Filed under: Uncategorized — mdinh @ 11:52 am

Requirement is to configure hugepages for multiple RAC database instances.

pmon processes

grid     12692     1  0 09:39 ?        00:00:00 asm_pmon_+ASM1
grid     13296     1  0 09:39 ?        00:00:00 mdb_pmon_-MGMTDB
oracle   13849     1  0 09:40 ?        00:00:00 ora_pmon_DEV1
oracle   13851     1  0 09:40 ?        00:00:00 ora_pmon_QA1
oracle   13854     1  0 09:40 ?        00:00:00 ora_pmon_PERF1
oracle   13855     1  0 09:40 ?        00:00:00 ora_pmon_TEST1
oracle   14998     1  0 09:40 ?        00:00:00 ora_pmon_INT1

Create parameter.sh which will run parameter.sql.
You might be thinking, WTH is this person thinking!
I wanted SQL script to be reusable.

Update April 16, 2018:

Afterthought, might be better to only create one shell script, e.g. runsql.sh vs multiples

Run parameter.sql

oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba @ parameter.sql

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:25:56 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options


NAME                           CDB
------------------------------ ---
HAWK                           NO


NAME                           DISPLAY_VALUE INST_ID     CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database               TRUE                1          0 FALSE         FALSE
                               TRUE                2          0 FALSE         FALSE
cluster_database_instances     2                   1          0 4294967295    TRUE
                               2                   2          0 4294967295    TRUE
db_file_name_convert                               1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
db_name                        hawk                1          0 NULL          FALSE
                               hawk                2          0 NULL          FALSE
db_unique_name                 hawk                1          0 NONE          TRUE
                               hawk                2          0 NONE          TRUE
instance_groups                                    1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
instance_name                  hawk1               1          0 NULL          TRUE
                               hawk2               2          0 NULL          TRUE
instance_number                1                   1          0 0             FALSE
                               2                   2          0 0             FALSE
instance_type                  RDBMS               1          0 NONE          TRUE
                               RDBMS               2          0 NONE          TRUE
memory_max_target              0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
memory_target                  0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
pdb_file_name_convert                              1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
pga_aggregate_limit            2G                  1          0 1             TRUE
                               2G                  2          0 1             TRUE
pga_aggregate_target           256M                1          0 0             FALSE
                               256M                2          0 0             FALSE
sga_max_size                   768M                1          0 1000          TRUE
                               768M                2          0 1000          TRUE
sga_target                     768M                1          0 0             FALSE
                               768M                2          0 0             FALSE
use_large_pages                TRUE                1          0 NULL          FALSE
                               TRUE                2          0 NULL          FALSE

34 rows selected.

13:25:56 SYS @ hawk1:>

Run parameter.sh

oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$ ~/parameter.sh

******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------

******** SQL Script: /home/oracle/parameter.sql

The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:26:55 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>
NAME                           CDB
------------------------------ ---
HAWK                           NO


NAME                           DISPLAY_VALUE INST_ID     CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database               TRUE                1          0 FALSE         FALSE
                               TRUE                2          0 FALSE         FALSE
cluster_database_instances     2                   1          0 4294967295    TRUE
                               2                   2          0 4294967295    TRUE
db_file_name_convert                               1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
db_name                        hawk                1          0 NULL          FALSE
                               hawk                2          0 NULL          FALSE
db_unique_name                 hawk                1          0 NONE          TRUE
                               hawk                2          0 NONE          TRUE
instance_groups                                    1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
instance_name                  hawk1               1          0 NULL          TRUE
                               hawk2               2          0 NULL          TRUE
instance_number                1                   1          0 0             FALSE
                               2                   2          0 0             FALSE
instance_type                  RDBMS               1          0 NONE          TRUE
                               RDBMS               2          0 NONE          TRUE
memory_max_target              0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
memory_target                  0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
pdb_file_name_convert                              1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
pga_aggregate_limit            2G                  1          0 1             TRUE
                               2G                  2          0 1             TRUE
pga_aggregate_target           256M                1          0 0             FALSE
                               256M                2          0 0             FALSE
sga_max_size                   768M                1          0 1000          TRUE
                               768M                2          0 1000          TRUE
sga_target                     768M                1          0 0             FALSE
                               768M                2          0 0             FALSE
use_large_pages                TRUE                1          0 NULL          FALSE
                               TRUE                2          0 NULL          FALSE

34 rows selected.

13:26:55 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$

Requirements are .sh and .sql must have the same name and reside in the same location.
.sh can be called from any location.

parameter.sh


#!/bin/sh
# --------------------------------------------------------------------------------
# parameter.sh
# MDinh April 12, 2018
#
# Shell script will run SQL script having the same base name
# for all active database instances.
# --------------------------------------------------------------------------------
DN=`dirname $0`
BN=`basename $0`
SQL_SCRIPT_DIR=$DN
SQL=`echo $BN|cut -d'.' -f1`.sql
echo
echo "******** Current ora_pmon:"
echo "----------------------------------------"
ps -eo cmd|grep ora_pmon|grep -v grep
echo "----------------------------------------"
echo
echo "******** SQL Script: "$SQL_SCRIPT_DIR/$SQL
echo
for x in `ps -eo cmd|grep ora_pmon|grep -v grep|awk -F "_" '{print $NF}'`
do
  ORAENV_ASK=NO
  set -a
  ORACLE_SID=$x
  . oraenv
  set +a
  sysresv|tail -1

sqlplus -L "/ as sysdba" << EOF
whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
start $SQL_SCRIPT_DIR/$SQL
exit
EOF
if [ "$?" != "0" ]; then
  echo "$ORACLE_SID ERROR: Running $SQL_SCRIPT_DIR/$SQL"
  exit 1
fi
done
exit

 

parameter.sql


col name for a30
col value for a30
col default_value for a13
col display_value for a13
col inst_id for 99
break on name
set lines 200 pages 1000 trimsp on tab off
select name,CDB from v$database
;
select name,display_value,inst_id,con_id,default_value,isdefault
from gv$parameter
where regexp_like (name,'^sga|^pga|^memory|^cluster.*database|^instance|use_large_pages|db.*name','i')
order by name,value,inst_id
;

 

Frame work for shell script is the same. Just make a copy and update any comments.

oracle@racnode-dc1-1:hawk1:/home/oracle
$ ll
total 36
-rwxr-xr-x 1 oracle oinstall   19 Feb 10 20:44 db.env
-rwxr-xr-x 1 oracle oinstall   49 Feb 10 20:45 gi.env
-rwxr-xr-x 1 oracle oinstall 1020 Apr 14 13:24 parameter.sh
-rw-r--r-- 1 oracle oinstall  414 Apr 14 13:24 parameter.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_only.sh
-rw-r--r-- 1 oracle oinstall  430 Apr 12 17:50 set_db_use_large_pages_only.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_true.sh
-rw-r--r-- 1 oracle oinstall  430 Apr 12 17:53 set_db_use_large_pages_true.sql
-rw-r--r-- 1 oracle oinstall 1909 Jan 29 02:39 wc.sql

oracle@racnode-dc1-1:hawk1:/home/oracle
$ diff parameter.sh set_db_use_large_pages_true.sh
3c3
< # parameter.sh 
--- 
> # set_db_use_large_pages_true.sh

oracle@racnode-dc1-1:hawk1:/home/oracle
$ ./set_db_use_large_pages_true.sh

******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------

******** SQL Script: ./set_db_use_large_pages_true.sql

The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:48:17 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>
NAME                           CDB
------------------------------ ---
HAWK                           NO


NAME                           DISPLAY_VALUE INST_ID     CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
use_large_pages                TRUE                1          0 NULL          FALSE
                               TRUE                2          0 NULL          FALSE

13:48:17 SYS @ hawk1:>alter system set USE_LARGE_PAGES=TRUE scope=spfile sid='*'
13:48:17   2  ;

System altered.

13:48:17 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/home/oracle
$
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: