There was an incident where statistics were being gathered during prime operating hours causing performance issues. One DBA already verified GATHER_STATS_JOB has already been configured to not run during critical hours. Speculation is stats are being gathered manually and how to prove this? AUTO JOB has OPERATION : gather_database_stats (auto). MANUAL JOB is not being run by scheduler either; otherwise, there would be JOB_NAME. Half of the mystery is solve, but where is gather_table_stats running from? Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production SQL> @pr "SELECT ID,OPERATION,START_TIME,END_TIME,STATUS,JOB_NAME,SESSION_ID FROM dba_optstat_operations where START_TIME>trunc(sysdate-1) ORDER BY start_time DESC" ID : 6939 OPERATION : gather_table_stats START_TIME : 26-APR-18 05.00.12.387231 PM -07:00 END_TIME : 26-APR-18 05.00.21.509607 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6918 OPERATION : export_stats_for_dp START_TIME : 26-APR-18 03.47.09.574643 PM -07:00 END_TIME : 26-APR-18 03.47.25.336241 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 201 ------------------------- ID : 6917 OPERATION : gather_table_stats START_TIME : 26-APR-18 03.10.16.126374 PM -07:00 END_TIME : 26-APR-18 04.59.51.410241 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6916 OPERATION : gather_table_stats START_TIME : 26-APR-18 02.09.44.123132 PM -07:00 END_TIME : 26-APR-18 02.09.45.695904 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6915 OPERATION : gather_table_stats START_TIME : 26-APR-18 12.57.11.352671 PM -07:00 END_TIME : 26-APR-18 02.09.43.579331 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6922 OPERATION : restore_table_stats START_TIME : 26-APR-18 02.00.00.949528 AM -07:00 END_TIME : 26-APR-18 02.00.01.297300 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 877 ------------------------- ID : 6914 OPERATION : gather_table_stats START_TIME : 25-APR-18 11.57.35.764007 PM -07:00 END_TIME : 26-APR-18 12.05.11.086928 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 870 ------------------------- ID : 6921 OPERATION : gather_database_stats (auto) START_TIME : 25-APR-18 10.00.06.197933 PM -07:00 END_TIME : 26-APR-18 02.00.01.621582 AM -07:00 STATUS : TIMED OUT JOB_NAME : ORA$AT_OS_OPT_SY_16648 SESSION_ID : 877 ------------------------- ID : 6913 OPERATION : gather_table_stats START_TIME : 25-APR-18 02.26.33.270421 PM -07:00 END_TIME : 25-APR-18 02.26.42.759697 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 1921 ------------------------- ID : 6912 OPERATION : gather_table_stats START_TIME : 25-APR-18 12.20.35.728909 PM -07:00 END_TIME : 25-APR-18 02.26.18.996076 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6911 OPERATION : gather_table_stats START_TIME : 25-APR-18 10.13.45.127514 AM -07:00 END_TIME : 25-APR-18 10.13.46.570807 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6910 OPERATION : gather_table_stats START_TIME : 25-APR-18 08.57.44.914619 AM -07:00 END_TIME : 25-APR-18 10.13.44.623245 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6920 OPERATION : restore_table_stats START_TIME : 25-APR-18 02.00.00.220393 AM -07:00 END_TIME : 25-APR-18 02.00.00.490261 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 208 ------------------------- PL/SQL procedure successfully completed. SQL>
April 27, 2018
Who’s Gathering DB Stats?
April 18, 2018
Skip Goldengate Replicat Transaction
Overview:
It’s easy to skip replicat transaction; however, there is a requirement to understand the result.
In the example provided, DELETE from PARENT table was not successful because there are ROWS from CHILD table.
Skip replicat transactions resulted in difference of 15273 rows for PARENT table between source and target.
In this case, there were 3 rows from 1 CHILD table causing the issue.
I know what you are thinking – Why was DELETE rows from CHILD table not replicate to begin with?
May be someone did the same – skip transaction and not synchronize the data?
That’s a story for another time. The problem is solved but not the cause.
====================================================================================================
ORA-02292: integrity constraint (OWNER.MARY_JOE_FK) violated - child record found (status = 2292). DELETE FROM "OWNER"."T_JOE" WHERE "JOENUMMER" = :b0.
====================================================================================================
GGATE@SQL> select JOENUMMER from NEWS.T_MARY minus select JOENUMMER from NEWS.T_MARY@source;
JOENUMMER
------------
50000402
50000412
50000422
GGATE@SQL> select JOENUMMER from NEWS.T_MARY@source minus select JOENUMMER from OWNER.T_MARY;
no rows selected
GGATE@SQL>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
GGATE@SQL> select JOENUMMER from OWNER.T_MARY@source where JOENUMMER in (50000402,50000412,50000422);
no rows selected
GGATE@SQL> select JOENUMMER from OWNER.T_MARY where JOENUMMER in (50000402,50000412,50000422);
JOENUMMER
------------
50000402
50000412
50000422
GGATE@SQL> delete from OWNER.T_MARY where JOENUMMER in (50000402,50000412,50000422);
3 rows deleted.
GGATE@SQL> commit;
Commit complete.
GGATE@SQL>
==================================================================================================== Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.15 17640173 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131101.0605.2_FBO Linux, x64, 64bit (optimized), Oracle 11g on Nov 19 2013 03:18:45 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
==================================================================================================== ORA-02292: integrity constraint (OWNER.MARY_JOE_FK) violated - child record found (status = 2292). DELETE FROM "OWNER"."T_JOE" WHERE "JOENUMMER" = :b0. ==================================================================================================== +++ SKIPTRANSACTION GGSCI> start replicat REP1 SKIPTRANSACTION +++ REVIEW PRM [gguser]$ grep -i discard rep1.prm --REPERROR (DEFAULT, DISCARD) REPERROR (-1, DISCARD) REPERROR (2291, DISCARD) DISCARDFILE ./discard/rep1.discard append, MEGABYTES 1024 DISCARDROLLOVER AT 00:01 [gguser]$ +++ REVIEW SKIPPING FROM DISCARD [gguser]$ grep -c "Skipping delete from OWNER.T_JOE" rep1.discard 15276 [gguser]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|head Skipping delete from OWNER.T_JOE at seqno 4475 rba 87850906 * JOENUMMER = 1 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851339 * JOENUMMER = 2 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851735 * [gguser@viz-cp-dc1-p11 oracle]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|tail * JOENUMMER = 50093291 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033367 * JOENUMMER = 50094681 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767 * JOENUMMER = 50094741 +++ REVIEW RBA FROM DISCARD [gguser]$ grep rba rep1.discard|head -1 Aborting transaction on ./dirdat/nd beginning at seqno 4475 rba 87850906 [gguser]$ grep rba rep1.discard|tail -1 Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767 [gguser]$ +++ NOTICE MATCH WITH LOGDUMP Logdump 23 >scanforendtrans End of Transaction found at RBA 94033767 ==================================================================================================== GATHER DATA ====================================================================================================
GGATE@SQL> r
1 select count(*) from
2 (
3 (select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@source)
4 union all
5 (select JOENUMMER from OWNER.T_JOE@source minus select JOENUMMER from OWNER.T_JOE)
6 )
7*
COUNT(*)
———-
15273
GGATE@SQL>
+++ CREATE TEMPORARY TABLE
GGATE@SQL> create table T_JOE_DEL as select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@source;
+++ REVIEW DATA FROM TEMPORARY TABLE TO COMPARE WITH DISCARD
GGATE@SQL> r
1 select * from (
2 select JOENUMMER from T_JOE_DEL order by 1 asc
3* ) where rownum <11
JOE
————
1
2
3
21
23
24
25
26
27
28
10 rows selected.
GGATE@SQL>
GGATE@SQL> r
1 select * from (
2 select JOE from T_JOE_DEL order by 1 desc
3* ) where rownum <11
JOE
————
50094741
50094681
50093291
50093221
50093191
50093101
50092851
50092791
50092781
50092741
10 rows selected.
GGATE@SQL>
==================================================================================================== CORRECT DATA ==================================================================================================== GGATE@SQL> delete from OWNER.T_JOE where JOENUMMER in (select JOENUMMER from T_JOE_DEL); 15273 rows deleted. GGATE@SQL> commit; Commit complete. ==================================================================================================== VERIFY ROW COUNT ==================================================================================================== +++ USING COUNT MAY NOT BE THE BEST OPTION. GGATE@SQL> select count(*) from OWNER.T_JOE; COUNT(*) ---------- 9939 GGATE@SQL> select count(*) from OWNER.T_JOE@source; COUNT(*) ---------- 9939 GGATE@SQL> ==================================================================================================== REVIEW REPORT FILE ==================================================================================================== [gguser]$ grep SKIPTRANSACTION REP1*.rpt rep1.rpt:2018-04-17 12:15:15 INFO OGG-01370 User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906. [gguser]$ grep -i skip ggserr.log 2018-04-17 12:15:14 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (gguser): start replicat rep1 SKIPTRANSACTION. 2018-04-17 12:15:14 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.232.135.44:33310 (START REPLICAT rep1 SKIPTRANSACTION). 2018-04-17 12:15:15 INFO OGG-01370 Oracle GoldenGate Delivery for Oracle, rep1.prm: User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906. [gguser]$ ==================================================================================================== LOGDUMP TO FIND END OF TRANSACTONS ==================================================================================================== Logdump 15 >open ./dirdat/nd004475 Current LogTrail is ./dirdat/nd004475 Logdump 16 >detail on Logdump 17 >fileheader detail Logdump 18 >ghdr on Logdump 19 >detail data Logdump 20 >ggstoken detail Logdump 21 >pos 87850906 Reading forward from RBA 87850906 Logdump 22 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 310 (x0136) IO Time : 2018/04/17 10:47:16.475.512 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 779280 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:16.475.512 Delete Len 310 RBA 87850906 Name: OWNER.T_JOE Before Image: Partition 4 G b GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6b55 4141 5441 4141 6264 7141 4159 0001 | AAAHkUAATAAAbdqAAY.. TokenID x4c 'L' LOGCSN Info x00 Length 10 3732 3833 3730 3834 3135 | 7283708415 TokenID x36 '6' TRANID Info x00 Length 13 3232 2e33 302e 3139 3233 3539 39 | 22.30.1923599 Logdump 23 >scanforendtrans End of Transaction found at RBA 94033767 ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 331 (x014b) IO Time : 2018/04/17 10:47:16.429.234 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x02) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 13903264 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:16.429.234 Delete Len 331 RBA 94033767 Name: OWNER.T_JOE Before Image: Partition 4 G e GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6b55 4141 5741 4141 4e6c 6c41 4177 0001 | AAAHkUAAWAAANllAAw.. Logdump 24 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 174 (x00ae) IO Time : 2018/04/17 10:47:24.429.491 IOType : 15 (x0f) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 13947088 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:24.429.491 FieldComp Len 174 RBA 94034190 Name: OWNER.NEW_DATA Before Image: Partition 4 G b GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6a59 4141 5441 4142 794b 4541 412f 0001 | AAAHjYAATAAByKEAA/.. TokenID x4c 'L' LOGCSN Info x00 Length 10 3732 3833 3730 3834 3538 | 7283708458 TokenID x36 '6' TRANID Info x00 Length 13 3132 2e31 362e 3330 3031 3139 37 | 12.16.3001197 Logdump 25 >open ./dirdat/nd004475 Current LogTrail is ./dirdat/nd004475 Logdump 26 >count LogTrail ./dirdat/nd004475 has 92822 records Total Data Bytes 92730182 Avg Bytes/Record 999 Delete 20937 Insert 5405 FieldComp 724 LargeObject 65755 Others 1 Before Images 21163 After Images 71658 Average of 1589 Transactions Bytes/Trans ..... 61161 Records/Trans ... 58 Files/Trans ..... 5 Logdump 27 >detail on Logdump 28 >filter inc filename OWNER.T_JOE Logdump 29 >count Scanned 10000 records, RBA 12734577, 2018/04/17 07:25:42.524.558 Scanned 20000 records, RBA 25670230, 2018/04/17 08:00:11.480.213 Scanned 30000 records, RBA 38698934, 2018/04/17 08:30:24.488.669 Scanned 40000 records, RBA 51436567, 2018/04/17 08:59:11.452.549 Scanned 50000 records, RBA 63868041, 2018/04/17 09:43:10.477.605 Scanned 60000 records, RBA 76010927, 2018/04/17 10:14:59.472.122 Scanned 70000 records, RBA 94264594, 2018/04/17 10:47:31.447.436 LogTrail ./dirdat/nd004475 has 15296 records Total Data Bytes 4757365 Avg Bytes/Record 311 Delete 15296 Before Images 15296 Filtering matched 15296 records suppressed 77526 records Average of 2 Transactions Bytes/Trans ..... 2745786 Records/Trans ... 7648 Files/Trans ..... 110 OWNER.T_JOE Partition 4 Total Data Bytes 4757365 Avg Bytes/Record 311 Delete 15296 Before Images 15296 Logdump 30 >
April 14, 2018
Framework To Run SQL For All Active DB Instances
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
$
April 13, 2018
Check 12.1.0.2 Alert Log For HugePages Usage
What! Another post on hugepages – seriously?
+ grep 'Dump of system resources acquired for SHARED GLOBAL AREA' -B1 -A22 database alert log + tail -25 2018-04-13T09:40:23.908633-07:00 Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 2018-04-13T09:40:23.916573-07:00 Per process system memlock (soft) limit = UNLIMITED 2018-04-13T09:40:23.920591-07:00 Expected per process system memlock (soft) limit to lock SHARED GLOBAL AREA (SGA) into memory: 2996M 2018-04-13T09:40:23.928517-07:00 Available system pagesizes: 4K, 2048K 2018-04-13T09:40:23.936717-07:00 Supported system pagesize(s): 2018-04-13T09:40:23.943044-07:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 2018-04-13T09:40:23.947112-07:00 2048K 2303 1498 1498 NONE 2018-04-13T09:40:23.951899-07:00 Reason for not supporting certain system pagesizes: 2018-04-13T09:40:23.960107-07:00 4K - Large pagesizes only 2018-04-13T09:40:23.965247-07:00 ==================================================================================================== Tue Apr 10 12:29:13 2018 Dump of system resources acquired for SHARED GLOBAL AREA (SGA) Tue Apr 10 12:29:13 2018 Per process system memlock (soft) limit = 128G Tue Apr 10 12:29:13 2018 Expected per process system memlock (soft) limit to lock SHARED GLOBAL AREA (SGA) into memory: 4002M Tue Apr 10 12:29:13 2018 Available system pagesizes: 4K, 2048K Tue Apr 10 12:29:13 2018 Supported system pagesize(s): Tue Apr 10 12:29:13 2018 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) Tue Apr 10 12:29:13 2018 4K Configured 5 1024005 NONE Tue Apr 10 12:29:13 20182048K 0 2001 0 NONETue Apr 10 12:29:13 2018 RECOMMENDATION: Tue Apr 10 12:29:13 2018 1. For optimal performance, configure system with expected number of pages for every supported system pagesize prior to the next
Thinking Out Loud 