How to check Data Guard Physical standby status? (Oracle)

For data guard issues from an existing physical standby, I usually only use the following queries to diagnostic the problem. These queries can serve 95% of the cases and tell me where is the problem from.

For the rest 5% that you would like to look deeper, check out the srdc_DGPhyStby_diag.sql from Oracle support site. It gives you a report with all details in html format.

set tab off;
set linesize 300;
set pagesize 1000;
set long 100;
set verify off;
set feedback off;
--spool check_dg_tmp.out;
prompt
prompt
prompt *********************************
prompt showing ..... DB information
prompt *********************************

column host_name format a20
column  "STARTUP_TIME" format a25
select name Database,INSTANCE_NAME,OPEN_MODE,DATABASE_STATUS,database_role,logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP_TIME" from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... Standby applying status
prompt *********************************

select process,status,sequence#,thread#,BLOCK#,BLOCKS,pid from gv$managed_standby order by 2 desc;

prompt
prompt
prompt *********************************
prompt showing ..... DG status
prompt *********************************
select INSTANCE_NAME,database_role, switchover_status,PROTECTION_MODE,PROTECTION_LEVEL , FLASHBACK_ON,force_logging "FORCE_LOGGING"  from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... archive log destination (location and roles, 1 - 6)
prompt *********************************
column dest_name format a19
column destination format a22
column db_unique_name format a12
column gap_status format a15
column dest_id format 9999
select a.DEST_ID, a.dest_name, a.type, a.STATUS, a.DESTINATION,  a.VALID_TYPE, a.VALID_ROLE, a.db_unique_name, b.gap_status
FROM V$ARCHIVE_DEST a,  V$ARCHIVE_DEST_STATUS b WHERE a.dest_id=b.dest_id and a.DESTINATION is not null;


prompt
prompt
prompt *********************************
prompt showing ..... archive log state (enable or defer)
prompt *********************************
show parameter log_archive_dest_state_1
show parameter log_archive_dest_state_2
show parameter log_archive_dest_state_3
show parameter log_archive_dest_state_4
show parameter log_archive_dest_state_5
show parameter log_archive_dest_state_6

prompt
prompt
prompt *********************************
prompt showing ..... archive log file format ( %t_%s_%r.arc)
prompt *********************************
show parameter log_archive_format

prompt
prompt
prompt *********************************
prompt showing ..... convert parameters (log and db_file)
prompt *********************************
show parameter convert

prompt
prompt
prompt *********************************
prompt showing ..... flashback retention
prompt *********************************
show parameter  db_flashback_retention_target

prompt
prompt
prompt *********************************
prompt showing ..... DB recover file dest and size
prompt *********************************
show parameter db_recovery

prompt
prompt
prompt *********************************
prompt showing ..... local listener and remote listener
prompt *********************************
show parameter remote_listener
show parameter local_listener

--spool off;
exit;

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 ====================================================