Pluggable database save state Automate Pluggable DB opening during Container instance startup PDB Saved State is a feature in 12.1.0.2 for saving the OPEN state of all PDBS when CDB is restarted. This is for preserving the state of a Pluggable database proir to CDB… Read More
Oracle,Pluggable Different ortypes of creating Pluggable Database manually There are multiple ways to create a pluggable database, in this article we would go through the process of creating PDB manually as in certain cases where GUI model is not feasible as creating a PDB using… Read More
Hi Readers, In this article, we will see the Oracle : Shell script for Crontab Monitoring alert THIS SCRIPT WILL IDENTIFY changes with the crontab file made comparing with original file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
[oracle@nposqa1 ~]$ cat /home/oracle/DBA/bin/crontab_compare.bash #!/bin/bash # set -x # Get the current crontab entries # Compare with the expected crontab entries # Notify if any changes found ######################################################################################### # Control file locations separately. # create following variables in the file # ORIGFILE = Original file to compare the current crontab entries # EMAILRECIPIENTS='example@example.com' # EMAILBODY=Location of email body file. VARIABLES=$1 . $VARIABLES HOST=$(hostname) CURRFILE='/tmp/currcron.file' DIFFILE='/tmp/crondif.file' crontab -l > $CURRFILE diff $ORIGFILE $CURRFILE > $DIFFILE #Function to push email to users on alert function pushmail { SUBJ=$1 MAILBODY=$2 RECIPIENTS=$3 mail -s "$SUBJ" $RECIPIENTS < $MAILBODY } if [ -s $DIFFILE ] then echo "" > $EMAILBODY echo "Attention: Found changes in the current crontab entry" >>$EMAILBODY echo "" >> $EMAILBODY echo "---- Below are the changes from DIFF command ----" >> $EMAILBODY echo "" >> $EMAILBODY cat $DIFFILE >> $EMAILBODY pushmail "ALERT: Found changes in crontab entry on server $HOST" $EMAILBODY $EMAILRECIPIENTS fi |
Config file :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[oracle@nposqa1 ~]$ cat /home/oracle/DBA/cfg/crontab_compare.cfg # Control file locations separately. # create following variables in the file # ORIGFILE = Original file to compare the current crontab entries # EMAILRECIPIENTS='example@example.com' # EMAILBODY=Location of email body file. export DIR='/home/oracle/DBA' export DBA_BIN=${DIR}/bin export DBA_LOG=${DIR}/log export DBA_CFG=${DIR}/cfg export DBA_SQL=${DIR}/sql export ORIGFILE="${DBA_CFG}/crontab_orig.cron" export EMAILRECIPIENTS="admin@ktexperts.com" export EMAILBODY="${DBA_CFG}/crontab_emailbody.file" |
Create original crontab file :
|
1 |
cd /home/oracle/DBA/cfg\ |
|
1 |
crontab -l >crontab_orig.cron |
Add below… Read More
RAC Important Commands (SecureCRT) Dear Readers, In this article, we will see the Oracle : Important Commands for RAC (SecureCRT) Menu Bar : RAC Button : VD_Location
|
1 |
crsctl query css votedisk |
|
1 2 3 4 5 |
## STATE File Universal Id File Name Disk group -- ----- ----------------- --------- --------- 1. ONLINE 970d29b0fdca4fb3bfa2210f71ebf828 (VIN:SSD_E0_S00_XXYZ41896P1) [DATA] 2. ONLINE 1422365a8a334f2fbfda2a8e9a348d78 (VIN:SSD_E0_S02_XXYZ41868P1) [DATA] 3. ONLINE 2752bc1b291a4f97bf904ff37b43c19b (VIN:SSD_E0_S04_XXYZ41948P1) [DATA] |
Button : ocrcheck
|
1 |
ocrcheck |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Status of Oracle Cluster Registry is as follows : Version : 4 Total space (kbytes) : 491684 Used space (kbytes) : 85328 Available space (kbytes) : 406356 ID : 496404425 Device/File Name : +DATA Device/File integrity check succeeded Device/File not configured Device/File not configured Device/File not configured Device/File not configured Cluster registry integrity check succeeded Logical corruption check bypassed due to non-privileged user |
Button : ConfigDB
|
1 |
srvctl config database -d ORCL |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Database unique name: ORCL Database name: ORCL Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1 Oracle user: oracle Spfile: +DATA/ORCL/PARAMETERFILE/spfile.266.1005927959 Password file: +DATA/ORCL/PASSWORD/pwdorcl.260.1005927685 Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: Disk Groups: DATA,REDO Mount point paths: Services: orcl_app_svc,orcl_int_svc Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: dbaoper Database instances: orcl11,orcl12 Configured nodes: server03,server04 Database is administrator managed |
Button : config service
|
1 |
srvctl config service -d ORCL |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
Service name: orcl_app_svc Server pool: Cardinality: 2 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Failover type: Failover method: TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: Pluggable database name: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Session State Consistency: GSM Flags: 0 Service is enabled Preferred instances: orcl1,orcl2 Available instances: Service name: orcl_int_svc Server pool: Cardinality: 2 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Failover type: Failover method: TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: Pluggable database name: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Session State Consistency: GSM Flags: 0 Service is enabled Preferred instances: orcl1,orcl2 Available instances: |
Button… Read More
Hi Dears, In this article, we will see Important Oracle v$parameters (SecureCRT) Tab Name : v$parameter Button Name : SGA
|
1 2 3 4 5 |
col name for a50 set linesize 300 col value for a40 select name,value from v$parameter where name like '%sga%'; select &sga/1024/1024/1024 GB from dual; |
Output :
|
1 2 3 4 5 6 7 8 |
NAME VALUE -------------------------------------------------- ---------------------------------------- sga_max_size 4294967296 pre_page_sga TRUE lock_sga FALSE allow_group_access_to_sga FALSE sga_target 4294967296 unified_audit_sga_queue_size 1048576 |
|
1 2 3 4 5 6 |
select &sga/1024/1024/1024 GB from dual; Enter value for sga: 4294967296 GB ---------- 4 |
Button Name : SGA_TARGET
|
1 2 3 4 5 |
col name for a50 set linesize 300 col value for a40 select name,value from v$parameter where name like '%sga_target%'; select &sga_target/1024/1024/1024 GB from dual; |
Output :
|
1 2 3 4 5 6 7 8 9 10 |
NAME VALUE -------------------------------------------------- ---------------------------------------- sga_target 4294967296 select &sga_target/1024/1024/1024 GB from dual; Enter value for sga_target: 4294967296 GB ---------- 4 |
Button Name : PGA_Aggregate_target
|
1 2 3 4 5 |
SQL> col name for a50 SQL> set linesize 300 SQL> col value for a40 select name,value from v$parameter where name like '%pga_aggregate_target%'; select &pga_aggregate_target/1024/1024/1024 GB from dual; |
Output :
|
1 2 3 4 5 |
SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual; NAME VALUE -------------------------------------------------- ---------------------------------------- pga_aggregate_target 2147483648 |
|
1 2 3 4 5 |
SQL> Enter value for pga_aggregate_target: 2147483648 GB ---------- 2 |
Button Name :… Read More
Dear Readers, In this article, we will see the Oracle : Important Commands for Dataguard (SecureCRT) Menubar Name : Dataguard Tab Name : Instance_info
|
1 2 3 4 5 6 7 |
set linesize 300 col machine for a60 select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY |
Tab Name : DG_GAP
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 44791 44791 0 2 42354 42354 0 3 40223 40223 0 SQL> THREAD# APPLIED MAX(SEQUENCE#) ---------- --------- -------------- 1 IN-MEMORY 44791 1 NO 39800 1 YES 44790 2 NO 38782 2 YES 42354 3 NO 38673 3 YES 40223 |
Tab Name : ERR_DEST
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
set linesize 300 col dest_name for a30 col error for a70 select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4); select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1; DEST_ID DEST_NAME STATUS ERROR ---------- ------------------------------ --------- -------------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 INACTIVE 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 DEFERRED SQL> INST_ID DEST_NAME STATUS ERROR ---------- ------------------------------ --------- -------------- 1 LOG_ARCHIVE_DEST_1 VALID |
Tab Name : Date Format
|
1 |
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'; |
Tab Name :… Read More
Dear Readers, In this article, we will see the Oracle : Important Queries for Parallel Feature (SecureCRT). Menubar Name : Parallel Tab Name : Create table show parameter parallel_max_servers
|
1 |
create table owner.tablename_bkp as select /*+ parallel(tablename,64) */ * from owner.tablename ; |
|
1 |
create table vinod.tablename_bkp as select /*+ parallel(EMP,4) */ * from SCOTT.EMP; |
Tab Name : Finding_Parallel_Queries
|
1 2 3 4 5 6 |
set linesize 300 col username for a19 col terminal for a15 col osuser for a15 col program for a45 col time for a25 |
|
1 2 3 4 5 6 7 8 9 |
select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER from gv$session where program like '%P0%' and sql_id is not null; INST_ID SID SQL_ID PROGRAM USERNAME TIME TERMINAL OSUSER ---------- ---------- ------------- --------------------------------------------- ------------------- ------------------------- --------------- --------------- 1 58 29d991q9rq5kn oracle@awsserver003 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle 1 59 29d991q9rq5kn oracle@awsserver003 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle 2 37 29d991q9rq5kn oracle@awsserver004 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle 2 207 29d991q9rq5kn oracle@awsserver004 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle |
Tab Name : Select Query with Parallel Option… Read More
Dear Readers, In this article, we will see the Oracle: Important commands for Lock/Unlock Stats and Backup/Restore Old Stats (SecureCRT). MenuBar Name : Lock/Unlock Stats and Backup/Restore Old Stats Tab Name : Check Stats for Table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
--example : 'DEPT','EMP','SALGRADE' set linesize 300 set pagesize 100 col owner for a15 col LAST_ANALYZED for a30 col table_name for a40 select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS , CHAIN_CNT, AVG_ROW_LEN CACHE, to_char(LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER'; Enter value for selected_tables_check_example: 'TEST' Enter value for owner: VINOD old 4: from dba_tables where table_name in (&Selected_Tables_Check_Example) and owner='&OWNER' new 4: from dba_tables where table_name in ('TEST') and owner='VINOD' OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT CACHE LAST_ANALYZED --------------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------------------------ VINOD TEST 2 5 0 0 3 18-FEB-2021 22:00:21 |
Tab Name : Backup Existing Stats
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT'); exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016'); select count(*) from vinod.TEST_STAT; SQL> exec dbms_stats.create_stat_table ( ownname => 'VINOD',stattab => 'TEST_STAT'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.export_table_stats ( ownname => 'VINOD',stattab => 'TEST_STAT', tabname => 'TEST',statid => 'stats04252016'); PL/SQL procedure successfully completed. SQL> select count(*) from vinod.TEST_STAT; COUNT(*) ---------- 2 |
Tab… Read More
Dear Readers, In this article, we will see the Oracle : Important Queries to check Table Fragmentation (SecureCRT). Menubar Name : Fragmentation Tab name : Fragmented_tablesList in HTML
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
col spoolname new_value spoolname select 'Fragmented_tables'||to_char(sysdate, 'YYYYMONDD')||'.html' spoolname from dual; spool '&spoolname' set pagesize 200 set markup html on WITH STAT_SPACE AS( SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE FROM DBA_TABLES WHERE OWNER='SCOTT'), SEG_SPACE AS ( SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB FROM DBA_SEGMENTS WHERE 1=1 AND OWNER='SCOTT' AND SEGMENT_TYPE='TABLE') SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) claim_gb FROM STAT_SPACE STSP, SEG_SPACE SESP WHERE abs(STSP.TBL_STAT_SPACE - sesp.SEGSIZE_GB) > 1 AND STSP.TABLE_NAME = SESP.SEGMENT_NAME AND STSP.OWNER = SESP.OWNER ORDER BY CLAIM_GB desc; spool off exit |
Output : Tab name : BLOCKS Size
|
1 2 3 4 5 6 7 8 9 10 11 |
set linesize 300 col table_name for a30 col "Blocks Size" for a30 select table_name,round((blocks*8),2)||' KB' "Blocks Size",(round((blocks*8),2))/1024/1024 ||' GB' "Blocks Size" from dba_tables where owner='&OWNER' and table_name = upper('&table_name'); TABLE_NAME Blocks Size Blocks Size ------------------------------ ------------------------------ ------------------------------ EMPTEST123 1231264 KB 1.174224853515625 GB |
Tab name : Rows Size
|
1 2 3 4 5 6 7 8 |
set linesize 300 select table_name,round((num_rows*avg_row_len/1024),2)||' KB' "Rows Size",(round((num_rows*avg_row_len/1024),2))/1024/1024||' GB' "Rows Size" from dba_tables where owner='&OWNER' and table_name = upper('&table_name'); TABLE_NAME Rows Size Rows Size ------------------------------ ------------------------------------------------------------------------ ---------------------------- EMPTEST123 10597.63 KB .0101066875457763671875 GB |
Tab name… Read More
Dear Readers, In this article, we will see the Oracle : Important Queries to check Blocking sessions (SecureCRT). Menubar : Blocking Tab : Blocking Sessions
|
1 2 3 4 5 6 7 8 9 10 11 |
set linesize 300 select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('| |W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID; B.USERNAME||'('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||')ISCURRENTLY'||B.STATUS||'FORLAST'||B.LAST_CALL_ET|| -------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------- VINOD (51,25124,@1) is Currently INACTIVE for last 48 Sec and it's BLOCKING user VINOD (57,30908,@1) |
Tab : Blocking_session_event
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
set linesize 300 set pagesize 100 col machine for a50 col event for a50 col username for a25 select inst_id,sid,serial#,username,event,status,sql_id,BLOCKING_SESSION,machine from gv$session where event like '%lock%' or event like '%latch%' or event like '%buffer%' or event='latch: row cache objects' or event='enq: TM - contention' or event='enq:_TX_-_index_contention' and type!='BACKGROUND' order by 1; INST_ID SID SERIAL# USERNAME EVENT STATUS SQL_ID BLOCKING_SESSION MACHINE ---------- ---------- ---------- ------------------------- -------------------------------------------------- -------- ------------- ---------------- ------------- 1 57 30908 VINOD enq: TX - row lock contention ACTIVE dbc1d3gdvz06s 51 awsserver123 |
Tab : SQLs INVOLVED IN BLOCKING
|
1 2 3 4 5 6 7 8 |
set head on PROMPT ##################################################################################### PROMPT SQLs INVOLVED IN BLOCKING PROMPT ##################################################################################### set pagesize 100 set linesize 300 col sql_text for a100 col SQL_ID for a30 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT distinct /*+ RULE */ SQL_ID, sql_text FROM GV$SQL WHERE SQL_ID IN ( select SQL_ID from (select SQL_ID from gv$session where BLOCKING_SESSION >0) union select B.SQL_ID --Blocker Current SQL from (select INST_ID,SID,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,SQL_ID from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID union select B.PREV_SQL_ID --Blocker PRIV SQL from (select INST_ID,SID,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,PREV_SQL_ID from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID ) ORDER BY sql_id; SQL_ID SQL_TEXT ------------------------------ -------------------------------- dbc1d3gdvz06s update test set a=3 where a=1 |
Tab : Objects involved in Blocking Lock… Read More
