How to check cursor usage and find high version count sqls? (Oracle)

prompt ====================================================
prompt == Start of check_cursor.sql
prompt ====================================================
prompt ====================================================
prompt == Monitor cursor usage
prompt == Due to bug 21135007, the session_cached_cursor usage might be out
prompt == number due to parallel execution.
prompt ====================================================
set lines 300
col value format a30
col usage format a30
SELECT
   'session_cached_cursors' PARAMETER,
   lpad(VALUE, 5) VALUE,
   decode(VALUE, 0, 'n/a', to_char(100 * used / VALUE, '990') || '%') usage , used
FROM
   (SELECT MAX(s.VALUE) used
      FROM v$statname n, v$sesstat s
      WHERE n.name = 'session cursor cache count'
         AND s.statistic# = n.statistic#
   ),
   (SELECT VALUE
      FROM v$PARAMETER
      WHERE name = 'session_cached_cursors'
   )
UNION ALL
SELECT 'open_cursors', lpad(VALUE, 5), to_char(100 * used / VALUE, '990') || '%', used
FROM(SELECT MAX(SUM(s.VALUE)) used
      FROM v$statname n, v$sesstat s
      WHERE n.name ='opened cursors current'
         AND s.statistic# = n.statistic#
      GROUP BY s.sid
   ),
   (  SELECT VALUE
      FROM v$PARAMETER
      WHERE  name = 'open_cursors'
   );

prompt ====================================================
prompt == Top 10 cursor hungry sql statements
prompt ====================================================
select *
from (select  t.value ,s.sid, s.sql_id
from v$sesstat t, v$statname n, v$session s
where t.sid=s.sid and n.name= 'session cursor cache count' and t.statistic# = n.statistic#  order by 1 desc) where rownum <=10;

prompt ====================================================
prompt == Total cursors open, by username & machine
prompt ====================================================
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine
from   v$sesstat a, v$statname b, v$session s
where  a.statistic# = b.statistic# and s.sid=a.sid
and    b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;

prompt ====================================================
prompt == Show sqls having high child cursor count > 100
prompt ====================================================

select inst_id, sql_id, sql_profile, plan_hash_value, max(child_number)
from gv$sql group by inst_id, sql_id, sql_profile, plan_hash_value
having max(child_number) >100;


prompt ====================================================
prompt == End of check_cursor.sql
prompt ====================================================

How to check memory components of your database? (Oracle)

This script shows you information about your memory configurations, current usage, adviser views, etc. Before jumping into any conclusions, you should check if the database is using AMM, HugePage, or any other OS related configurations. Changing memory configuration involves at least a rolling restart in most cases.

prompt ====================================================
prompt == start of check_memory.sql
prompt ====================================================

prompt ====================================================
prompt == Show Memory parameters
prompt ====================================================
show parameter memory

prompt ====================================================
prompt == Top 50 memory allocations by sessions
prompt ====================================================

SET LINESIZE 300
SET Pages 300

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT * FROM
(SELECT a.inst_id,
       NVL(a.username,'(oracle)') AS username,
       a.sid,
       a.module,
       a.program,
       Trunc(b.value/1024/1024) AS memory_mb
FROM   gv$session a,
       gv$sesstat b,
       gv$statname c
WHERE  a.sid = b.sid
AND    a.inst_id = b.inst_id
AND    b.statistic# = c.statistic#
AND    b.inst_id = c.inst_id
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC)
WHERE rownum <=50;

prompt ====================================================
prompt == Show SGA PGA hourly usage in last 10 days
prompt ====================================================
set lines 200
set pages 1000
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) totalGb,to_char(SN.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') datetime
from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
from DBA_HIST_SGASTAT
group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
from DBA_HIST_PGASTAT where name = 'total PGA allocated'
group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
and sn.snap_id=pga.snap_id
and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
and sn.END_INTERVAL_TIME > sysdate - 10
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;
prompt ====================================================
prompt == V$SHARED_POOL_ADVICE
prompt ====================================================
select * from V$SHARED_POOL_ADVICE;

prompt ====================================================
prompt == V$SGA_TARGET_ADVICE
prompt ====================================================
select * from v$sga_target_advice;

prompt ====================================================
prompt == V$MEMORY_TARGET_ADVICE
prompt ====================================================
select * from  v$memory_target_advice;

prompt ====================================================
prompt == Show SGA
prompt ====================================================
show sga
show parameter sga

prompt ====================================================
prompt == Current Free SGA
prompt ====================================================
select current_size/1024/1024/1024 free_gb
from V$SGA_DYNAMIC_FREE_MEMORY;

prompt ====================================================
prompt == Current SGA Components
prompt ====================================================
select component, current_size/1024/1024/1024 current_Gb
from V$SGA_DYNAMIC_COMPONENTS;

prompt ====================================================
prompt == Show PGA
prompt ====================================================
show parameter pga

prompt ====================================================
prompt == Max PGA allocated
prompt ====================================================
select name, value/1024/1024/1024 Gb
from V$PGASTAT where name='maximum PGA allocated';


prompt ====================================================
prompt == End of check_memory.sql
prompt ====================================================

How to refresh a testing Cassandra database with production data? (Cassandra)

Context:
Source-Production server: prodcassdb25 (seed), 26, 27
Target-Testing server: testcassdb91 (seed), 92, 93

Steps:
------------------------------------------------------------
Step 1 : scp snapshot files and keyspace definition from Prod to Eng
------------------------------------------------------------
--find and cp the files
[cassandra@prodcassdb25 keyspace_definitions]$ pwd
/cassandra_backup/keyspace_definitions
[cassandra@prodcassdb25 keyspace_definitions]$ ls -lrt|grep 20191213
-rw-r--r-- 1 cassandra dba 49829 Dec 13 03:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_031001.out
-rw-r--r-- 1 cassandra dba 49829 Dec 13 03:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_031001.out
-rw-r--r-- 1 cassandra dba 49829 Dec 13 15:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_151001.out
-rw-r--r-- 1 cassandra dba 49829 Dec 13 15:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_151001.out
[cassandra@prodcassdb25 keyspace_definitions]$
[cassandra@prodcassdb25 keyspace_definitions]$ cp x99b99990_a697_42a0_ad47_c7e139620c98_group_*ddl_20191213_03*.out /home2/kenchen/bk
[cassandra@prodcassdb25 keyspace_definitions]$


[cassandra@prodcassdb25 snapshots]$ pwd
/cassandra_backup/snapshots
[cassandra@prodcassdb25 snapshots]$ cp -R 20191213_0310_* /home2/kenchen/bk

---do scp in testcassdb91 seednode
scp -r prodcassdb25:/home2/kenchen/bk .

--cp the directories to /cassandra_backup/snapshots
[cassandra@testcassdb91 bk]$ cp -R 20191213_0310_* /cassandra_backup/snapshots
[cassandra@testcassdb91 bk]$ ll /cassandra_backup/snapshots |grep 1213
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_02
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_06
[cassandra@testcassdb91 bk]$ ll /cassandra_backup/snapshots
total 16
drwxr-xr-x 43 cassandra dba 4096 Dec 16 14:52 20191116_0310_02
drwxr-xr-x 43 cassandra dba 4096 Dec 16 14:52 20191116_0310_06
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_02
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_06
[cassandra@testcassdb91 bk]$

------------------------------------------------------------
Step 2 : clean up target nodes--drop keyspaces from seed node
------------------------------------------------------------
--in seed node drop keyspaces
cassandra@cqlsh> desc keyspaces;

system_auth         x99b99990_a697_42a0_ad47_c7e139620c98_group_2
system              x99b99990_a697_42a0_ad47_c7e139620c98_group_3
system_distributed  system_traces

cassandra@cqlsh> drop keyspace x99b99990_a697_42a0_ad47_c7e139620c98_group_2;
cassandra@cqlsh> drop keyspace x99b99990_a697_42a0_ad47_c7e139620c98_group_3;
cassandra@cqlsh>

verify on all nodes:
cassandra@cqlsh> desc keyspaces;

system_traces  system_auth  system  system_distributed


------------------------------------------------------------
Step 3 : clean up--run node repair from seed node
------------------------------------------------------------

[cassandra@testcassdb91 ~]$ nodetool repair --full
[2019-12-17 09:17:54,550] Starting repair command #5, repairing keyspace system_auth with repair options (parallelism: parallel, primary range: false, incremental: false, job threads: 1, ColumnFamilies: [], dataCenters: [], hosts: [], # of ranges: 768)
[2019-12-17 09:17:55,056] Repair session 29b29cc0-20f1-11ea-b4c2-4d3d3ded966d for range (-2597627288616693569,-2545287888585638866] finished (progress: 0%)
[2019-12-17 09:17:55,259] Repair session 29b2eae3-20f1-11ea-b4c2-4d3d3ded966d for range (-5356597931097554743,-5339397917747191084] finished (progress: 0%)
[2019-12-17 09:17:55,440] Repair session 29b33901-20f1-11ea-b4c2-4d3d3ded966d for range (-2903199422797207225,-2903114251772426092] finished (progress: 0%)
[2019-12-17 09:17:55,573] Repair session 29b36011-20f1-11ea-b4c2-4d3d3ded966d for range (5078614939157582036,5161374894663544739] finished (progress: 1%)
[2019-12-17 09:17:55,601] Repair session 29b38721-20f1-11ea-b4c2-4d3d3ded966d for range (483483848257142510,494502176940626340] finished (progress: 1%)
[2019-12-17 09:17:55,620] Repair session 29b3ae32-20f1-11ea-b4c2-4d3d3ded966d for range (-7299231363705753989,-7280800272545333799] finished (progress: 1%)
...
...
[2019-12-17 09:18:10,765] Repair session 32114061-20f1-11ea-b4c2-4d3d3ded966d for range (1079344578921085961,1099364809618351926] finished (progress: 99%)
[2019-12-17 09:18:10,767] Repair session 32116770-20f1-11ea-b4c2-4d3d3ded966d for range (8074300300555063568,8078332406054179512] finished (progress: 99%)
[2019-12-17 09:18:10,769] Repair session 32116773-20f1-11ea-b4c2-4d3d3ded966d for range (2905063564631007102,2935904027252613794] finished (progress: 100%)
[2019-12-17 09:18:10,779] Repair completed successfully
[2019-12-17 09:18:10,779] Repair command #6 finished in 2 seconds
[cassandra@testcassdb91 ~]$

verify on all nodes:
cassandra@cqlsh> desc keyspaces;

system_traces  system_auth  system  system_distributed




------------------------------------------------------------
Step 4 : clean up--remove physicical directories from all nodes
------------------------------------------------------------

[cassandra@testcassdb93 data]$ pwd
/cassandra/cassandra/data/data
[cassandra@testcassdb93 data]$ ll
total 12
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
drwxr-xr-x 43 cassandra dba 4096 Dec 16 15:28 x99b99990_a697_42a0_ad47_c7e139620c98_group_2
drwxr-xr-x 43 cassandra dba 4096 Dec 16 15:28 x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_2
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@testcassdb93 data]$
[cassandra@testcassdb93 data]$ ll
total 4
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
[cassandra@testcassdb93 data]$

------------------------------------------------------------
Step 5 : Restore keyspace meta data from seed node
------------------------------------------------------------
--restore group 2
[cassandra@testcassdb91 bk]$ pwd
/home2/cassandra/bk
[cassandra@testcassdb91 bk]$ ll
total 112
drwxr-xr-x 43 cassandra dba  4096 Dec 17 09:07 20191213_0310_02
drwxr-xr-x 43 cassandra dba  4096 Dec 17 09:07 20191213_0310_06
-rw-r--r--  1 cassandra dba 49829 Dec 17 09:07 x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_031001.out
-rw-r--r--  1 cassandra dba 49829 Dec 17 09:07 x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_031001.out
[cassandra@testcassdb91 bk]$ /cassandra/cassandra/bin/cqlsh 10.12.171.130 -u cassandra -p cassandra -f x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_031001.out

--you can check progress in other nodes
[cassandra@testcassdb92 data]$ ll
total 8
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
drwxr-xr-x  7 cassandra dba 4096 Dec 17 09:29 x99b99990_a697_42a0_ad47_c7e139620c98_group_2

--then restore group 3
[cassandra@testcassdb91 bk]$ /cassandra/cassandra/bin/cqlsh 10.12.171.130 -u cassandra -p cassandra -f x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_031001.out


------------------------------------------------------------
Step 6 : Drain and shut down each Cassandra node, one node at a time.
------------------------------------------------------------
[cassandra@testcassdb93 data]$ nodetool drain
[cassandra@testcassdb93 data]$ sudo /etc/init.d/cassandra_init_script.sh stop
Shutdown Cassandra: pid is 7576
pid 7576 is running
OK


------------------------------------------------------------
Step 7 : On seed node, delete all files in the commitlog and saved_caches directory
------------------------------------------------------------
[cassandra@testcassdb91 bk]$ cd /cassandra/cassandra/data/
[cassandra@testcassdb91 data]$ ll
total 0
drwxr-xr-x 2 cassandra dba  43 Dec 17 09:44 commitlog
drwxr-xr-x 8 cassandra dba 192 Dec 17 09:33 data
drwxr-xr-x 2 cassandra dba  51 Dec 17 07:26 saved_caches
[cassandra@testcassdb91 data]$ rm -r commitlog/* saved_caches/*
[cassandra@testcassdb91 data]$


------------------------------------------------------------
Step 8 : On seed node, run restore script
------------------------------------------------------------

cat SnapshotKeyspace_master.dat to get snapshot name and Keyspace name

[cassandra@prodcassdb25 ~]$ cat /cassandra/admin/logs/SnapshotKeyspace_master.dat|grep -w "ARCHIVING Snapshot" |grep 20191213
Fri Dec 13 03:10:03 PST 2019 | 20191213_031001 | Info | ARCHIVING Snapshot : 20191213_0310_02 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_2
Fri Dec 13 03:10:08 PST 2019 | 20191213_031001 | Info | ARCHIVING Snapshot : 20191213_0310_06 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_3
Fri Dec 13 15:10:04 PST 2019 | 20191213_151001 | Info | ARCHIVING Snapshot : 20191213_1510_02 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_2
Fri Dec 13 15:10:08 PST 2019 | 20191213_151001 | Info | ARCHIVING Snapshot : 20191213_1510_06 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@prodcassdb25 ~]$

--prepare the commands as below
./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_2 20191213_0310_02
./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_3 20191213_0310_06


[cassandra@testcassdb91 bk]$ cd /cassandra/admin/scripts/
[cassandra@testcassdb91 scripts]$ ./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_2 20191213_0310_02

        Restoring tables from directory, '/cassandra_backup/snapshots/20191213_0310_02', to directory, '/cassandra/cassandra/data/data/x99b99990_a697_42a0_ad47_c7e139620c98_group_2'
        Restore snapshot, '20191213_0310_02', to keyspace, 'x99b99990_a697_42a0_ad47_c7e139620c98_group_2'
Continue (y/n)?y

Restoring table, 'api_portal_apiapppolicybindings'
Restoring table, 'api_portal_apiorgpolicybindings'
Restoring table, 'api_portal_apipolicyparameters'
Restoring table, 'api_portal_portalactionqueue'
Restoring table, 'api_portal_portalapiaccessstore'
Restoring table, 'api_portal_portalapi'
Restoring table, 'api_portal_portalapimethod'
Restoring table, 'api_portal_portalapiquotaconstraintstore'
Restoring table, 'api_portal_portalapiquotadetailsstore'
Restoring table, 'api_portal_portalapiquotastore'
Restoring table, 'api_portal_portalapplicationpermissionstore'
Restoring table, 'api_portal_portalconfigstore'
Restoring table, 'api_portal_portalexports'
Restoring table, 'api_portal_portalidentitystore'
Restoring table, 'api_portal_portalorganizationstore'
Restoring table, 'api_portal_portalorganizationstoreldap'
Restoring table, 'api_portal_portalregistrationtoken'
Restoring table, 'api_portal_portalremotehost'
Restoring table, 'api_portal_portaltimestamp'
Restoring table, 'api_portal_portaluserstore'
Restoring table, 'api_portal_portaluserstoreldap'
Restoring table, 'api_portal_portalvirtualizedapi'
Restoring table, 'api_portal_portalvirtualizedapimethod'
Restoring table, 'api_server_portalapikeystore'
Restoring table, 'api_server_portalapplicationstore'
Restoring table, 'api_server_portalexternalclientstore'
Restoring table, 'api_server_portaloauthresourcesstore'
Restoring table, 'api_server_portaloauthstore'
Restoring table, 'backendcredentials_backendcreds'
Restoring table, 'clientappsuc1_app'
Restoring table, 'clientappsuc1_apporchardclient'
Restoring table, 'counter_snapshot'
Restoring table, 'counter_table'
Restoring table, 'kps_schema'
Restoring table, 'oauth_accesstokens'
Restoring table, 'oauth_authorizations'
Restoring table, 'oauth_authzcodes'
Restoring table, 'oauth_clientaccesstokens'
Restoring table, 'oauth_clientrefreshtokens'

Restoring table, 'oauth_refreshtokens'
Restoring table, 'statuscode_statuscd'
[cassandra@testcassdb91 scripts]$
[cassandra@testcassdb91 scripts]$

[cassandra@testcassdb91 scripts]$ ./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_3 20191213_0310_06

        Restoring tables from directory, '/cassandra_backup/snapshots/20191213_0310_06', to directory, '/cassandra/cassandra/data/data/x99b99990_a697_42a0_ad47_c7e139620c98_group_3'
        Restore snapshot, '20191213_0310_06', to keyspace, 'x99b99990_a697_42a0_ad47_c7e139620c98_group_3'
Continue (y/n)?y

Restoring table, 'api_portal_apiapppolicybindings'
Restoring table, 'api_portal_apiorgpolicybindings'
Restoring table, 'api_portal_apipolicyparameters'
Restoring table, 'api_portal_portalactionqueue'
Restoring table, 'api_portal_portalapi'
Restoring table, 'api_portal_portalapiaccessstore'
Restoring table, 'api_portal_portalapimethod'
Restoring table, 'api_portal_portalapiquotaconstraintstore'
Restoring table, 'api_portal_portalapiquotadetailsstore'
Restoring table, 'api_portal_portalapiquotastore'
Restoring table, 'api_portal_portalapplicationpermissionstore'
Restoring table, 'api_portal_portalconfigstore'
Restoring table, 'api_portal_portalexports'
Restoring table, 'api_portal_portalidentitystore'
Restoring table, 'api_portal_portalorganizationstore'
Restoring table, 'api_portal_portalorganizationstoreldap'
Restoring table, 'api_portal_portalregistrationtoken'
Restoring table, 'api_portal_portalremotehost'
Restoring table, 'api_portal_portaltimestamp'
Restoring table, 'api_portal_portaluserstore'
Restoring table, 'api_portal_portaluserstoreldap'
Restoring table, 'api_portal_portalvirtualizedapi'
Restoring table, 'api_portal_portalvirtualizedapimethod'
Restoring table, 'api_server_portalapikeystore'
Restoring table, 'api_server_portalapplicationstore'
Restoring table, 'api_server_portalexternalclientstore'
Restoring table, 'api_server_portaloauthresourcesstore'
Restoring table, 'api_server_portaloauthstore'
Restoring table, 'backendcredentials_backendcreds'
Restoring table, 'clientappsuc1_app'
Restoring table, 'clientappsuc1_apporchardclient'
Restoring table, 'counter_snapshot'
Restoring table, 'counter_table'
Restoring table, 'kps_schema'
Restoring table, 'oauth_accesstokens'
Restoring table, 'oauth_authorizations'
Restoring table, 'oauth_authzcodes'
Restoring table, 'oauth_clientaccesstokens'
Restoring table, 'oauth_clientrefreshtokens'
Restoring table, 'oauth_refreshtokens'
Restoring table, 'statuscode_statuscd'
[cassandra@testcassdb91 scripts]$

------------------------------------------------------------
Step 9 : On other nodes, delete all files in the commitlog and saved_caches directory
------------------------------------------------------------
[cassandra@testcassdb93 data]$ ll
total 0
drwxr-xr-x 2 cassandra dba  80 Dec 17 09:44 commitlog
drwxr-xr-x 8 cassandra dba 192 Dec 17 09:33 data
drwxr-xr-x 2 cassandra dba  51 Dec 17 07:28 saved_caches
[cassandra@testcassdb93 data]$ rm -r commitlog/* saved_caches/*
[cassandra@testcassdb93 data]$




------------------------------------------------------------
Step 10 : On other nodes, delete all files in KEYSPACE directories being restored
------------------------------------------------------------
[cassandra@testcassdb93 data]$ pwd
/cassandra/cassandra/data/data
[cassandra@testcassdb93 data]$ ll
total 4
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
drwxr-xr-x  2 cassandra dba    6 Dec 17 10:00 x99b99990_a697_42a0_ad47_c7e139620c98_group_2
drwxr-xr-x  2 cassandra dba    6 Dec 17 10:00 x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@testcassdb93 data]$
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_2/*
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_3/*
[cassandra@testcassdb93 data]$


------------------------------------------------------------
Step 11 : One at a time, start the Cassandra seed node, and then the other nodes
------------------------------------------------------------
wait for each to be in Up/Normal (UN) state in nodetool status before you proceed to the next node.

sudo /etc/init.d/cassandra_init_script.sh start

[cassandra@testcassdb91 scripts]$ sudo /etc/init.d/cassandra_init_script.sh start
Starting Cassandra: OK
[cassandra@testcassdb91 scripts]$ nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address        Load       Tokens       Owns (effective)  Host ID                               Rack
DN  10.12.171.132  ?          256          100.0%            72a30930-0561-4252-b26a-05a2ee2e6c05  rack1
UN  10.12.171.130  101.16 MB  256          100.0%            62099913-cca1-45c9-b4de-43fbc6492846  rack1
DN  10.12.171.131  ?          256          100.0%            5e97be86-89c2-4068-ac8f-66763d3ce5fd  rack1

[cassandra@testcassdb91 scripts]$



------------------------------------------------------------
Step 12 : run full repair on each node one at a time
------------------------------------------------------------
nodetool repair -pr --full


------------------------------------------------------------
Step 13 : verify
------------------------------------------------------------

How to tell if your database is RAC? (Oracle)

You are a consultant and just got paged to a database issue from a new client. Their DBA is on vacation and this is your first time to logon to their system. How can you tell if this is a RAC database? If it is RAC, how many instances/nodes? Where are the other nodes?

• check if it is a RAC database

          show parameter cluster
          --if it is RAC, cluster_database should be "true".
          --cluster_database_instances would show you how many nodes of it is a RAC

• find IPs of other nodes

          cat /etc/hosts
          --this command should tell you IPs of other nodes

How to merge two large tables? (Oracle)

Scenario:
There are two large tables, table A has 3 billion records and table B has 80 million records. How to merge them into one table?

Solutions:
################
# Solution 1:
################
Use MERGE sql statement with parallel.
--pros: simple and easy to understand
--cons: the requirement to hardware is relatively high

################
# Solution 2:
################

Hash partition both tables by ID, then use MERGE with parallel. This will use Oracle's new feature PWJ -- Partition Wise Join. 

################
# Solution 3:
################

Create a new table C, then insert into C using full outer job of table A and B. Then rename table C, rebuild indexes...etc.

--PWJ + parallel + nologging + compress

################
# Solution 4:
################

11g + version only

Use DBMS_PARALLEL_EXECUTE to separate table B(which has less records) by ROWID. Then use cursor/plsql to loop thru the smaller tables.

--pros: less rollback/UNDO

--cons: very slow using cursor .

################
# Solution 5:
################
Compare records in both A and B and remove duplicate ones. Then do a direct INSERT.
--pros: no MERGE, less expensive
--cons: more DML than other solutions

How to see progress of long running sql statements? (Oracle)

From now and then, you are asked by your teammates about when his/her sql statements will finish. Here is a script that shows an estimation. It can be used for any long running sqls including RMAN jobs as well.

Sometimes this estimation might not be accurate. The elapsed_seconds might change to zero or a few seconds frequently for some long running jobs. That means the job is progressing and it is not hang.

prompt ====================================================
prompt == Start of check_longops.sql
prompt ====================================================

prompt ====================================================
prompt == Check Long Operation notes
prompt == These are just Estimations and sometimes not accurate.
prompt ==
prompt ==
prompt ====================================================

SET PAGES 100
SET LINE 200
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A20
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
COLUMN message FORMAT a80
COLUMN opname FORMAT a20
column username format a20

SELECT s.sid,
       sl.username,
       opname,
       MESSAGE,
       s.serial#,
       s.machine,
       ROUND (sl.elapsed_seconds / 60) || ':' || MOD (sl.elapsed_seconds, 60)
           elapsed,
       ROUND (sl.time_remaining / 60) || ':' || MOD (sl.time_remaining, 60)
           remaining,
       DECODE (totalwork, 0, 0, ROUND (sl.sofar / sl.totalwork * 100, 2))
           progress_pct
  FROM gv$session s, gv$session_longops sl
 WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND sofar <> totalwork;

 prompt ====================================================
 prompt == End of check_longops.sql
 prompt ====================================================

How to check locking status? (Oracle)

When you are called to help an application hang issue, you would like to know if there are any database sessions are blocked and what transactions are blocking them and who/which session/which machines are blocking them.

The following script would help you find out if there are any locks in the database and who is blocking them.

prompt ====================================================

prompt == Start of check_lock.sql

prompt ====================================================

prompt ====================================================

prompt == Check Locks notes

prompt == 

prompt == Who is blocking whom?

prompt == 

prompt ====================================================

select s1.username || ' '|| s1.machine 

|| ' ( SID=' || s1.sid || ' ) is blocking ' 

|| s2.username || ' '|| s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status , s2.event as wait_event

from v$lock l1, v$session s1, v$lock l2, v$session s2 

where s1.sid=l1.sid and s2.sid=l2.sid 

and l1.BLOCK=1 and l2.request > 0 

and l1.id1 = l2.id1 

and l2.id2 = l2.id2 ; 

prompt ====================================================

prompt == End of check_lock.sql

prompt ====================================================

 

How to check IO performance? (Oracle)

During performance tuning or being called to debug a performance issues of the database system, we always would like to know the current I/O status and how it compares with baseline periods. This is a handy script that would give you useful information.

Please keep in mind that busy I/O might not necessary lead to the conclusion that I/O is the bottleneck or culprit of the performance issue.

prompt ====================================================
prompt == Start of check_io.sql
prompt ====================================================

prompt ====================================================
prompt == Check IO notes
prompt == OS level should support Async for better performance.
prompt == In general, for waits 1000+ per hour, elapsed time
prompt == should be less than 15~20ms.
prompt ==
prompt == Single Block -->wait on db file sequential read -->index visit or rollback
prompt ==
prompt == Multi Block -->wait on db file scattered read -->full table scan or index fast full scan
prompt ==
prompt ==
prompt ==
prompt == How to Tell if the I/O of the Database is Slow (Doc ID 1275596.1)
prompt ====================================================
prompt ====================================================
prompt == Show IO Stats
prompt ====================================================
set lines 300
set pages 300
select * from DBA_HIST_IOSTAT_DETAIL;

prompt ====================================================
prompt == Show IO Metric
prompt ====================================================
select * from dba_hist_sysmetric_history where metric_name like 'I/O%Megabytes%';

prompt ====================================================
prompt == End of check_io.sql
prompt ====================================================

How to generate a report of all invalid objects in the database? (Oracle)

When should we use this script?
--Before and after upgrading your database
--Before and after deploying new DDL changes to your database
--Debug ORA-04063 or similar errors


DOC
#######################################################################
#######################################################################
   The following statement will generate a html report Invalid.html
   which shows invalid object details of the database.
   Please note that the last two statements might fail in higher db
   versions.
#######################################################################
#######################################################################
#

spool Invalid.html

set pagesize 50000
set markup html on

col comp_id format a10
col comp_name format a30
col version format a10
col object_name format a30
col owner format a15
select * from v$version;
select comp_id, comp_name, status, version from dba_registry;
select count(*) from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
select owner,object_name,object_type, status from dba_objects where status = 'INVALID';
select owner,object_name,object_type, status from dba_objects where status = 'INVALID'and owner IN ('SYSTEM' , 'SYS' );
select comp_name, version,status from dba_registry where STATUS <> 'VALID';
select * from registry$sys_inv_objs;
select * from registry$nonsys_inv_objs;

set markup html off
spool off

How to find the sessions that are using most CPUs? (Oracle)

Here is a handy script I usually use.

check_cpu.sql

prompt ====================================================
prompt == Start of check_cpu.sql
prompt ====================================================

prompt ====================================================
prompt == Check CPU notes
prompt == OS level CPU usage can be monitor by top or topas
prompt == Where you should be able to pin point the top
prompt == process ID
prompt == 
prompt ====================================================
prompt ====================================================
prompt == Show top 20 CPU usage sessions
prompt ====================================================
set lines 300
set pages 300
col machine format a20
col program format a30
col name format a30
select * from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20;


prompt ====================================================
prompt == Check Sql Text related to above sql_id
prompt ====================================================
SET LONG 100000 
select sql_id, SQL_TEXT from v$SQL where SQL_ID in (
select sql_id from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20
);

prompt ====================================================
prompt == End of check_cpu.sql
prompt ====================================================