===== Oracle Database ===== ==== Size ==== /* Get total size allocated */ select sum(bytes)/1024/1024 size_in_mb from dba_data_files; /* Get total space used */ select sum(bytes)/1024/1024 size_in_mb from dba_segments; /* Break down size usage by user */ select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner; === Monthly DB growth === select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB from v$datafile group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM') order by 1, 2; === Get DB growth over time data lifetime=== SET LINESIZE 200 SET PAGESIZE 200 COL "Database Size" FORMAT a13 COL "Used Space" FORMAT a11 COL "Used in %" FORMAT a11 COL "Free in %" FORMAT a11 COL "Database Name" FORMAT a13 COL "Free Space" FORMAT a12 COL "Growth DAY" FORMAT a11 COL "Growth WEEK" FORMAT a12 COL "Growth DAY in %" FORMAT a16 COL "Growth WEEK in %" FORMAT a16 SELECT (select min(creation_time) from v$datafile) "Create Time", (select name from v$database) "Database Name", ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size", ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space", ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %", ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space", ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %", ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY", ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %", ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK", ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %" FROM (SELECT BYTES FROM V$DATAFILE UNION ALL SELECT BYTES FROM V$TEMPFILE UNION ALL SELECT BYTES FROM V$LOG) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE GROUP BY FREE.P; ==== Archived Redo Logs ==== === Identify tablespaces generating redo logs === select tablespace_name, contents, logging from dba_tablespaces; TABLESPACE_NAME CONTENTS LOGGING ------------------------------ --------- --------- SYSTEM PERMANENT LOGGING SYSAUX PERMANENT LOGGING UNDOTBS1 UNDO LOGGING TEMP TEMPORARY NOLOGGING USERS PERMANENT LOGGING UNDOTBS2 UNDO LOGGING UNDOTBS3 UNDO LOGGING APEX PERMANENT LOGGING O_TS_DATA1 PERMANENT NOLOGGING O_TS_INDEX1 PERMANENT NOLOGGING O_TS_DATA2 PERMANENT NOLOGGING O_TS_INDEX2 PERMANENT NOLOGGING O_TS_DATA3 PERMANENT NOLOGGING O_TS_INDEX3 PERMANENT NOLOGGING TOOLS PERMANENT LOGGING O_OSH_DATA1 PERMANENT LOGGING O_OSH_INDEX1 PERMANENT LOGGING SELECT tablespace_name, force_logging FROM dba_tablespaces; alter tablespace O_OSH_INDEX1 nologging; SELECT * FROM USER_TABLES; SELECT table_name, tablespace_name, logging FROM USER_TABLES; SELECT table_name, tablespace_name, logging FROM USER_TABLES where logging='YES'; === Archive log sizes Per Day === SELECT SUM_ARCH.DAY, SUM_ARCH.GENERATED_MB, SUM_ARCH_DEL.DELETED_MB, SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB" FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY, SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2)) GENERATED_MB FROM V$ARCHIVED_LOG WHERE ARCHIVED = 'YES' GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH, ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY, SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2)) DELETED_MB FROM V$ARCHIVED_LOG WHERE ARCHIVED = 'YES' AND DELETED = 'YES' GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+) ORDER BY TO_DATE (DAY, 'DD/MM/YYYY'); /* or */ select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'), thread# order by 1; To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well. The methods are: 1) Query //**V$SESS_IO**//. This view contains the column **BLOCK_CHANGES** which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo. The query you can use is: SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 desc, 1, 2, 3, 4; Run the query multiple times and examine the delta between each occurrence of **BLOCK_CHANGES**. Large deltas indicate high redo generation by the session. 2) Query //**V$TRANSACTION**//. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the **USED_UBLK** and **USED_UREC** columns). The query you can use is: SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4; Run the query multiple times and examine the delta between each occurrence of **USED_UBLK** and **USED_UREC**. Large deltas indicate high redo generation by the session. ==== Performance Queries ==== == Get process and session count for instance == select resource_name,current_utilization,max_utilization from v$resource_limit where resource_name in ('processes','sessions'); == Query Average active sessions == select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS from (select min(sample_time) sample_time from v$active_session_history ash ) start_time, (select max(sample_time) sample_time from v$active_session_history ) end_time, v$active_session_history ash where ash.sample_time between start_time.sample_time and end_time.sample_time group by end_time.sample_time,start_time.sample_time; == As above but for the last hour == select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS from (select min(sample_time) sample_time from v$active_session_history ash where sample_time between sysdate-1/24 and sysdate) start_time, (select max(sample_time) sample_time from v$active_session_history where sample_time between sysdate-1/24 and sysdate) end_time, v$active_session_history ash where ash.sample_time between start_time.sample_time and end_time.sample_time group by end_time.sample_time,start_time.sample_time; == Show DB block changes == SET pagesize 200 linesize 200 col owner format a10 col statistic_name format a10 col perc format 99.99 col object_type format a10 col value format 999,999,999,999.99 col statistic_name format a30 select a.inst_id, a.statistic_name, a.owner, a.object_name, a.object_type, a.value, (a.value / b.sum_value) * 100 perc from (select * from (select inst_id, owner, object_name, object_type, value, rank() over(partition by inst_id, statistic_name order by value desc) rnk, statistic_name from gv$segment_statistics where value > 0) where rnk < 11) a, (select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id) b where a.statistic_name = b.statistic_name and a.inst_id = b.inst_id and a.statistic_name = 'db block changes' order by a.inst_id, a.statistic_name, a.value desc; == To find the execution plans of currently long-running queries == set lines 200 pages 200 col operation for a32 col plan_options for a20 col plan_object_name for a24 col id for 999 break on sql_id on plan_hash select sql_id, sql_plan_hash_value plan_hash, plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost from v$sql_plan_monitor where status = 'EXECUTING' order by key, id; == Gathering system statistics in Exadata mode (if needed) == To see if Exadata specific optimizer stats have been gathered, run the following query on a system with at least 11.2.0.2 BP18 or 11.2.0.3 BP8 Oracle software. If PVAL1 returns null or is not set, Exadata specific stats have not been gathered. select pname, PVAL1 from aux_stats$ where pname='MBRC'; exec dbms_stats.gather_system_stats('EXADATA'); select * from sys.aux_stats$; == Show load balancing advisory events == SET PAGES 60 COLSEP '|' LINES 132 NUM 8 VERIFY OFF FEEDBACK OFF COLUMN user_data HEADING "AQ Service Metrics" FORMAT A60 WRAP BREAK ON service_name SKIP 1 SELECT TO_CHAR(enq_time, 'HH:MI:SS') Enq_time, user_data FROM sys.sys$service_metrics_tab ORDER BY 1 ; ==== Memory ==== Memory allocation for an Oracle DB is tricky business, and requires some thought and examination. The Maximum Availability Architecture document suggests:\\ OLTP applications: \\ **SUM of all databases' (SGA_TARGET + PGA_AGGREGATE_TARGET) + 4MB * (Maximum PROCESSES ) < Physical Memory per Database Node**\\ Some documents suggest up to 10MB for the per process memory allocation.\\ Data Warehouse applications:\\ **SUM of databases (SGA_TARGET + 3* PGA_AGGREGATE_TARGET) < Physical Memory per Database Node** == get the number of processes == show parameter processes; == consider the maximum and average memory used by a process when sizing this value == select AVG(ALLOCATED_AVG) from DBA_HIST_PROCESS_MEM_SUMMARY; select MAX(ALLOCATED_AVG) from DBA_HIST_PROCESS_MEM_SUMMARY; == Show PGA size and stats == column VALUE format 999999999999999; select * from v$pgastat; SHOW PARAMETER PGA_AGGREGATE_TARGET; /* maximum historical PGA usage */ select max(value)/1024/1024 MaxMB from dba_hist_pgastat where name='maximum PGA allocated'; == Show SGA size, pool subareas and stats == show parameter sga_target; show parameter sga_max_size; select pool, max(bytes)/1024/1024 as MaxMB from dba_hist_sgastat group by pool order by max(bytes)/1024/1024 desc; SELECT component, current_size/1024/1024 as size_mb, min_size/1024/1024 as min_size_mb FROM v$sga_dynamic_components WHERE current_size > 0 ORDER BY component; /* total current SGA */ SELECT SUM(current_size)/1024/1024 FROM v$sga_dynamic_components; == show implication of possible changes to SGA size == SELECT sga_size, sga_size_factor, estd_db_time_factor FROM v$sga_target_advice ORDER BY sga_size ASC; Think of the ESTD_DB_TIME_FACTOR as the amount of time required to process an operation which takes 1 second in the current configuration.\\ == Session current and max memory usage per session == SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "SESSION", to_char(prc.spid, '999999999') "PID/THREAD", to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE", to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE" FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins, v$statname stat1, v$statname stat2 WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory' AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max' AND se1.sid = ssn.sid AND se2.sid = ssn.sid AND ssn.paddr = bgp.paddr (+) AND ssn.paddr = prc.addr (+); == show pga usage per process == SELECT s.sid, p.spid, DECODE(s.program, NULL, p.program, s.program) AS "Program", pga_used_mem, pga_alloc_mem, pga_max_mem FROM v$process p, v$session s WHERE s.paddr = p.addr ORDER BY s.sid; == Tuning advice for MEMORY_TARGET parameter == select * from v$memory_target_advice order by memory_size; == List total Oracle DB memory (SGA+PGA) == export ORAENV_ASK=NO for ORACLE_SID in `ps -ef|grep pmon|grep -v grep|awk -F_ '{print $3}'` do . oraenv sqlplus -s "/ as sysdba" < ==== Parameter Queries ==== == Get SGA size in MB == select sum(value)/1024/1024 from v$sga; == Get PGA size in MB == select name, value/1024/1024 from v$pgastat where name like 'total PGA a%'; == Get memory parameter values == select sid, name, value from v$spparameter where name in ('memory_target','sga_target','sga_max_size', 'pga_aggregate_target','memory_max_target', 'use_large_pages'); == Buffer Cache Tuning == COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)' COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers' COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor' COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads' SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON'; Estd Phys Estd Phys Cache Size (MB) Buffers Read Factor Reads ---------------- ------------ ----------- ------------ 2,432 284,924 6.39 ############ <-- 10% of cache 4,864 569,848 2.10 ############ 7,296 854,772 1.49 ############ 9,728 1,139,696 1.31 ############ 12,160 1,424,620 1.21 ############ 14,592 1,709,544 1.14 ############ 17,024 1,994,468 1.10 ############ 19,456 2,279,392 1.06 ############ 21,888 2,564,316 1.03 ############ 24,320 2,849,240 1.01 ############ 25,216 2,954,212 1.00 ############ <-- Current size of cache 26,752 3,134,164 .99 ############ 29,184 3,419,088 .97 ############ 31,616 3,704,012 .96 ############ 34,048 3,988,936 .94 ############ 36,480 4,273,860 .93 ############ 38,912 4,558,784 .92 ############ 41,344 4,843,708 .92 ############ 43,776 5,128,632 .91 ############ 46,208 5,413,556 .90 ############ 48,640 5,698,480 .85 ############ <-- 200% of cache == Get allocated cpu == show parameter cpu_count; ==== Logging ==== == Disable Client / Server logging == To disable Oracle Net logging to the sqlnet.log file, add the following parameters and values to the //SQLNET.ORA// file: LOG_DIRECTORY_CLIENT = /dev/null OR LOG_FILE_CLIENT = /dev/null Default location of the net admin files is //ORACLE_HOME/network/admin// Any process already running will continue to log to the sqlnet.log file, until it is restarted. == Disable Listener Logging == In order to disable logging without stopping the listener, using the LSNRCTL command as follows: LSNRCTL>set current_listener (if not using default LISTENER) LSNRCTL>set LOG_STATUS off LSNRCTL>save_config If you're trying to disable listener logging temporarily, do not issue SAVE_CONFIG. YMMV for scan listeners. Once listener logging is disabled, you can now safely delete or archive the existing listener log. To restore logging again and/or to create a new listener log, simply enable logging as follows: LSNRCTL>set current_listener (if not using default LISTENER) LSNRCTL>set LOG_STATUS on For a more permanent solution, disable logging in the listener.ora file //$ORACLE_HOME/network/admin/listener.ora// LOGGING_LISTENER = OFF LOGGING_LISTENER_SCAN1 = OFF LOGGING_LISTENER_SCAN2 = OFF LOGGING_LISTENER_SCAN3 = OFF LOGGING_LISTENER_IB = OFF TRACE_LEVEL_LISTENER = OFF Once complete, run $ lsnrctl LSNRCTL> set current_listener LSNRCTL> reload == Clear all listener logs == for i in `ls /u01/app/grid/diag/tnslsnr/*/*/alert/log.xml`; do > $i; done == ADRCI diag log management == adrci> show homes set home show control set control (SHORTP_POLICY=360) /* hours */ set control (LONGP_POLICY=4380) show control purge == Problems with ADRCI == ADR base = "/misc/oracle" adrci> purge DIA-48322: Relation [INCIDENT] of ADR V[2] incompatible with V[2] tool DIA-48210: Relation Not Found DIA-48166: error with opening ADR block file because file does not exist [/misc/oracle/diag/tnslsnr/yyy/listener/metadata/INCIDENT.ams] [0] adrci> show home ADR Homes: diag/tnslsnr/yyy/listener adrci> migrate schema Schema migrated. adrci> purge adrci> quit The error is due to the mismatch in the metadata and one can use "//migrate schema//" command(as shown above) to upgrade the metadata of the corresponding diagnostics destination to the level mandated by the invoked adrci utility. Similarly for errors like "DIA-48318: ADR Relation [INCIDENT] of version=4 cannot be supported", one has to downgrade the schema using the higher level adrci(adrci>//migrate schema -downgrade//) and then use low level adrci to get away with the error. for f in $( adrci exec="show homes" | grep -v "ADR Homes:" ); do echo "set control ${f}:"; adrci exec="set home $f; set control \(SHORTP_POLICY=360, LONGP_POLICY=4380\); show control;" ; done for f in $( adrci exec="show homes" | grep -v "ADR Homes:" ); do adrci exec="set home $f; purge;" ; done Change diagnostic files destination: alter system set diagnostic_dest='/u01/app/oracle/admin'; alter system set diagnostic_dest='/u01/app/oracle/admin' scope=both; ==== Auditing Control ==== == Get size of Audit Table == select segment_name table_name ,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$'); == Show count of audited actions == select username,OWNER,OBJ_NAME,ACTION,ACTION_NAME,count(*) from dba_audit_trail group by username,OWNER,OBJ_NAME,ACTION,ACTION_NAME; ==Show audited options== SELECT * FROM DBA_STMT_AUDIT_OPTS order by user_name,audit_option; SELECT * FROM DBA_PRIV_AUDIT_OPTS order by user_name,privilege; SELECT * FROM DBA_OBJ_AUDIT_OPTS order by owner,object_name,object_type; SELECT * FROM ALL_DEF_AUDIT_OPTS; ==Empty AUD$ table== select * from AUD$; truncate TABLE AUD$; ==Examples== noaudit ; /* Don't audit this table */ noaudit alter
; /* Don't audit alter commands on this table; */ noaudit select
by ; /* Don't audit selects on this table by username */ noaudit insert
by ; /* Don't audit inserts on this table by username */ noaudit insert table; /* Don't audit inserts on this table */ select username, action_name from dba_audit_trail; # DBMS_AUDIT_MGMT is a package for managing audit trail records BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, default_cleanup_interval => 12 /* hours */); END; ==Showing/Setting Audit Log Parameters== show parameter AUDIT_SYS_OPERATIONS; show parameter AUDIT_SYSLOG_LEVEL; alter system set AUDIT_SYSLOG_LEVEL='local0.info' scope=spfile sid='*'; alter system set AUDIT_SYS_OPERATIONS=FALSE scope=spfile sid='*'; ==== Session Tracing ==== == Disable Session Tracing by session_id == select inst_id,sid, serial#, program from gv$session where program like '%LMS%' and INST_ID=3; EXEC DBMS_SYSTEM.set_ev(si=>687, se=>1, ev=>10046, le=>0, nm=>''); EXEC DBMS_SYSTEM.set_sql_trace_in_session(687,1,false); /* or */ EXEC DBMS_SUPPORT.stop_trace; /* or single session */ EXEC DBMS_SYSTEM.set_sql_trace_in_session(785,1,false); == Loop to disable all tracing on all processes == for i in `cat procs.txt`; do sqlplus '/as sysdba' <$i, se=>1, ev=>10046, le=>0, nm=>''); EXEC DBMS_SYSTEM.set_sql_trace_in_session($i,1,false); EOF done == Disable tracing with DBMS_MONITOR package == EXEC DBMS_MONITOR.session_trace_disable; for i in `cat procs.txt`; do sqlplus '/as sysdba' <$i, serial_num=>1); EOF done ==== Resource Manager ==== == Activate Resource Manager Plan == ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:MIXED_WORKLOAD_PLAN'; == Show top(active) plan == SELECT name, is_top_plan FROM v$rsrc_plan; NAME IS_TO -------------------------------- ----- DAY_PLAN TRUE == Show plans == SELECT plan,status,comments FROM dba_rsrc_plans; == List consumer groups per session == SELECT sid,serial#,username,resource_consumer_group FROM v$session; == Show sessions and waits == SELECT name, active_sessions, queue_length, consumed_cpu_time, cpu_waits, cpu_wait_time FROM v$rsrc_consumer_group; SELECT se.sid sess_id, co.name consumer_group, se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time FROM v$rsrc_session_info se, v$rsrc_consumer_group co WHERE se.current_consumer_group_id = co.id; == Show historical CPU waits == SELECT sequence# seq, name, cpu_wait_time, cpu_waits, consumed_cpu_time FROM v$rsrc_cons_group_history; == Show resource consumer groups privileges == SELECT * FROM dba_rsrc_consumer_group_privs; == Show resource plan directives == select plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3 from DBA_RSRC_PLAN_DIRECTIVES order by plan, cpu_p1 desc, cpu_p2 desc, cpu_p3 desc; /* with utilization and parallelism limits */ select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, mgmt_p4, parallel_degree_limit_p1, max_utilization_limit, parallel_queue_timeout from DBA_RSRC_PLAN_DIRECTIVES order by plan, mgmt_p1 desc, mgmt_p2 desc, mgmt_p3 desc; ==== Parallelism ==== == Show/Alter parallelism == show parameter PARALLEL_DEGREE_POLICY; alter system set parallel_degree_policy=auto; show parameter PARALLEL_THREADS_PER_CPU; == Show parallel query statistics == select * from v$pq_sesstat; select * from v$pq_sysstat; select * from v$px_process_sysstat; == Show current parallel query session detail == select slave_name, sessions, idle_time_cur, cpu_secs_total from v$pq_slave order by slave_name; == Show global parallelism details == select name, value from gv$sysstat where upper(NAME) like '%PARALLEL OPERATIONS%' OR upper(NAME) like '%PARALLELIZED%' OR upper(NAME) like '%PX%'; == SQL statement to display the most recent parallel query execution details == select tq_id, server_type, process, num_rows from v$pq_tqstat where dfo_number = (select max(dfo_number) from v$pq_tqstat) order by tq_id, decode (substr(server_type,1,4), 'Prod', 0, 'Cons', 1, 3) ; == Show statistics on parallel execution operations == SELECT dfo_number, tq_id, server_type, process, num_rows FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type, process; ==== ASM ==== [[https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=191753695637759&id=1386147.1&_afrWindowMode=0&_adf.ctrl-state=rften80r7_4|How to Replace a Hard Drive in an Exadata Storage Server (Hard Failure) (Doc ID 1386147.1)]]\\ [[Identify and add failed Exadata disks back into ASM]] == Check rebalancing operations == SQL> select * from gv$asm_operation; == Show rebalance operation estimates == select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION; == Check the number of disks per group == For Exadata there should be 12 DATA, 10 RECO, 12 DBFS (MODE_STATUS=ONLINE or MOUNT_STATUS=CACHED). SQL> select group_number,failgroup,mode_status,count(*) from v$asm_disk group by group_number, failgroup,mode_status; == Group 0 are problem disks == SQL> select path, header_status from v$asm_disk where group_number=0; == Get more detail == SQL> select group_number, path, header_status, mount_status, name from v$asm_disk where path like '%cel16man%'; == Add disk to diskgroups == SQL> alter diskgroup dbfs_dg add disk 'o/192.168.3.143/DBFS_DG_CD_04_m1cel03man' name DBFS_DG_CD_04_M1CEL03MAN force rebalance nowait SQL> alter diskgroup reco_m1 add disk 'o/192.168.3.143/RECO_M1_CD_04_m1cel03man' name RECO_M1_CD_04_M1CEL03MAN == Show disks by group == select d.name, r.group_number,r.inst_id,r.operation,r.state,r.power from gv$asm_operation r, v$asm_diskgroup d where r.group_number = d.group_number; == Find offline ASM disks == select group_number, name, path, mode_status, mount_status, failgroup from v$asm_disk where mode_status='OFFLINE'; == Show disks per diskgroup == SELECT NVL(a.name, '[CANDIDATE]') disk_group_name, b.path disk_file_path, b.name disk_file_name, b.failgroup disk_file_fail_group FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) ORDER BY a.name; == Drop old disk == alter diskgroup DATA_M1 drop disk '_DROPPED_0074_DATA_M1'; == Change ASM rebalance power (priority) == alter diskgroup rebalance power 1 nowait === ASMCMD === == Moving multiple files inside ASM == for i in $(asmcmd ls +RECOC1/DB/ARCHIVELOG/RESTORE2); do asmcmd cp +RECOC1/DB/ARCHIVELOG/RESTORE2/$i +DATAC1/TMPARCH/RESTORE2; asmcmd rm +RECOC1/DB/ARCHIVELOG/RESTORE2/$i; done ==== Licensing ==== [[https://www.oracle.com/assets/processor-core-factor-table-070634.pdf]]\\ [[https://docs.oracle.com/cd/E80920_01/DBMLI/exadata-capacity-on-demand.htm#DBMLI147]] ==== Oracle Enterprise Manager ===== Have found that OEM 12cv5 Agent misbehaves - high CPU, memory leaks.\\ Here's how to resolve:\\ 1. As Oracle user, gracefully shutdown by killing any leftover processes $agent_inst/bin/emctl stop agent $ps -ef | grep java | grep '' $ps -ef | grep perl Kill if any JAVA /PERL process id is active from AGENT HOME directory. $kill -9 2. Move/Delete old files from /agent_inst/sysman/emd/state/* to a new directory Example: $mv /u02/agent_inst/sysman/emd/state/* /u02/tmp/ 3. Execute agent clearstate agent $agent_inst/bin/emctl clearstate agent 4. Startup agent $agent_inst/bin/emctl status agent $agent_inst/bin/emctl start agent ==== RMAN ==== == Register database in rman catalog == connect target / connect catalog rmanuser/rmanpass@catalogserver.co.za ; REGISTER DATABASE; REPORT SCHEMA; == Show progress of running backup == col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB" col input_mbytes for 99,999,990.00 justify right head "READ_MB" col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB" col output_device_type for a10 justify left head "DEVICE" col complete for 990.00 justify right head "COMPLETE %" col compression for 990.00 justify right head "COMPRESS|% ORIG" col est_complete for a20 head "ESTIMATED COMPLETION" col recid for 9999999 head "ID" select recid , output_device_type , dbsize_mbytes , input_bytes/1024/1024 input_mbytes , output_bytes/1024/1024 output_mbytes , (output_bytes/input_bytes*100) compression , (mbytes_processed/dbsize_mbytes*100) complete , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete from v$rman_status rs , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) where status='RUNNING' and output_device_type is not null / == Block Change Tracking == Can be seen by existence of Oracle ctwr (change tracker writer) process [root@server ~]# ps -ef | grep ctwr oracle 72084 1 0 15:43 ? 00:00:04 ora_ctwr_dbnamed1 Used by RMAN for a change tracking index for incremental backups.\\ For large 11.2.0.4 databases this can cause backups to hang.\\ The following queries can be used to tune this feature.\\ /* Status of feature */ select status from V$BLOCK_CHANGE_TRACKING; /* List the BCT filename */ select filename,size from V$BLOCK_CHANGE_TRACKING; /* Disable change tracking altogether */ ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; /* Show memory allocation for BCT (this could be too small)*/ SELECT * FROM v$sgastat WHERE name like 'CTWR%'; /* Recommended size for change tracking memory buffer */ select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from x$krcstat; /* Set the undocumented change tracking buffer size (part of the SGA) */ ALTER SYSTEM SET "_bct_public_dba_buffer_size"= 33562624; ==== ACFS ==== Cluster mountable Filesystem within ASM\\ ==Create ACFS Command== Either created manually or via the *asmca* tool. /sbin/mkfs -t acfs /dev/asm/ Following commands should be run as privileged user /u01/app/12.1.0.2/grid/bin/srvctl add filesystem -d /dev/asm/ -m /u01/mount/point -u oracle -fstype ACFS -autostart ALWAYS /u01/mount/point start filesystem -d /dev/asm/ chown oracle:oinstall /u01/mount/point chmod 775 /u01/mount/point ==== DBMS SCHEDULER ==== == List scheduled jobs and if active == select owner, job_name, enabled from DBA_SCHEDULER_JOBS WHERE owner = 'OWNER_USER'; == Identify job action == select owner, job_name, job_action, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'JOB_NAME'; == Stop a running job == exec DBMS_SCHEDULER.STOP_JOB(job_name => 'OWNER.JOB_NAME',force => TRUE); == Disable a job (once stopped) == exec DBMS_SCHEDULER.disable('OWNER.JOB_NAME'); ==== Automatic Database Diagnostic Monitor (ADDM) ==== The CONTROL_MANAGEMENT_PACK_ACCESS has three settings: NONE - Oracle Diagnostics Pack and Oracle Tuning Pack is disabled on the database server, is strongly discouraged by Oracle, but you must if you haven't purchased a license. DIAGNOSTIC - Oracle Diagnostics Pack is enabled on the database server DIAGNOSTIC+TUNING - Both Oracle Diagnostics Pack and Oracle Tuning Pack are enabled on the database server /* Display current value */ show parameter control_management_pack_access NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_management_pack_access string DIAGNOSTIC+TUNING SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 64 <---- /* Disable feature */ alter system set control_management_pack_access='NONE'; SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY; COUNT(*) ---------- 0 <----- The STATISTICS_LEVEL initialization parameter has been around for quite some time and determines the level of database and operating system statistics that will be collected. Setting this parameter to TYPICAL or ALL will enable automatic database diagnostic monitoring, which is what we want. The three valid values are as follows: TYPICAL - the default setting will collect all the major statistics that Oracle deems necessary for database self-management while providing the best overall performance. Typically, no pun intended, TYPICAL is usually adequate for most environments. ALL - in addition to the TYPICAL collections, ALL will collect additional statistics such as timed operating system (OS) statistics and plan execution statistics. BASIC - disables many of the important statistics and is highly discouraged. show parameters STATISTICS_LEVEL; ====Clusterware Version/Patch Level==== crsctl resides in //$GRID_HOME\bin// directory. ==Active Version of Clusterware== Display the active version and the configured patch level of the Oracle Clusterware software running in the cluster. $ crsctl query crs activeversion Oracle Clusterware active version on the cluster is [12.1.0.0.2]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [456789126]. ==Patch Level of Clusterware== Display the patch level in the Grid home patch repository. The patch level corresponds to only the local node in which the command is executed. $ crsctl query crs releasepatch Oracle Clusterware release patch level is [3180840333] and the complete list of patches is [13559647] on the local node. ==Oracle Clusterware Software Version== Display the version of the Oracle Clusterware software installed $ crsctl query crs releaseversion Oracle High Availablity Services release version on the local node is [12.2.0.1.0] ==Check Patch Level of Clusterware on Specific Node== Display the configured patch level of the installed Oracle Clusterware. $ crsctl query crs softwarepatch Oracle Clusterware patch level on node [node1] is [456789126] ==Latest Running Version of Clusterware== Display latest version of the software that has been successfully started on the specified node. $ crsctl query crs softwareversion Oracle Clusterware version on node [node1] is [12.2.0.1.0] ==Verify a Patch is Installed== $ /grid/OPatch/opatch lsinventory -bugs_fixed | grep ^27463879