Oracle Database Administration

Steps to add disk to disk group Please follow the below steps once disk presented and partitioned by OS team.       1)       Create the disk using ASMLIB For example # /usr/sbin/oracleasm createdisk PRODDATA08 /dev/xvdq1 # (this command needs to be executed as root user)       2)       Since this is a RAC installation, the disks would only be stamped by one node. The other node would just scan for the disks.        For example # /usr/sbin/oracleasm scandisks # (this command needs to be executed as root user) You can use the next steps to validate whether the candidate disk(s) is/are in good shape and can be accessed from each node (without harm the existing production diskgroups) before adding, as follow:       3)       As Oracle or Grid OS user, run the next commands on each node        ...

Please find the sql queries below to get the details of db blocks. Check_db_blocks : ********************* set linesize 512 select v.instance_name ||' - '|| l1.sid ||' ('||s1.client_identifier||' - '||s1.module||')' || ' IS BLOCKING ' || l2.sid||' ('||s2.client_identifier||' - '||s2.module||')' || s1.sql_id || s1.username || s1.program || s1.machine "BLOCKING SESSIONS" from v$instance v, v$lock l1, v$lock l2, v$session s1, v$session s2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2 and l1.sid=s1.sid and l2.sid=s2.sid; Check_db_locks : ******************** set lines 100 select ' SID '|| sid||' ,Serial# '||serial#||' user '||username||' is getting blocked by SID '||blocking_session|| ' for last '||seconds_in_wait||' seconds and wait_event '||event||' for SQL_ID '||sql_id ||' (Locked_Object_ID '||row_wait_...

If you are experiencing the high cpu usage on database (DB) server, you can use the below queries to track the session and sql details which are consuming high cpu resources in oracle database server. sessions based on cpu usage 1: ************************************ set pages 1000 set lines 1000 col OSPID for a06 col SID for 99999 col SERIAL# for 999999 col SQL_ID for a14 col USERNAME for a15 col PROGRAM for a23 col MODULE for a18 col OSUSER for a10 col MACHINE for a25 select * from ( select p.spid "ospid", (se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,ss.BLOCKING_SESSION blk_sid,ss.BLOCKING_SESSION_STATUS blk_status, se.VALUE/100 cpu_usage_sec,ss.event from v$session ss, v$sesstat se, v$statname sn, v$process p where se.STATISTIC# = sn.STATISTIC# and NAME like '%CPU used by this session%' and se.SID = ss.SID and ss.username !='SYS' and ss.status=...

Please run the below queries to trace details during Metric alert "Process Limit % is >= 75" in OEM or Cloud Control 12c or Cloud Control 13c To check the Metrics Process Limit Usage (%): ***************************************************** set lines 300 col INITIAL_ALLOCATION for a17 col LIMIT_VALUE for a12 select resource_name,current_utilization,max_utilization, INITIAL_ALLOCATION,LIMIT_VALUE,((current_utilization*100)/(INITIAL_ALLOCATION)) as "Process limit %" from v$resource_limit where resource_name in ('processes'); The below query will give you all connected sessions (ACTIVE and INACTIVE) to instance: ******************************************************************************************************* set pages 100 set lines 1000 col SPID heading 'PID' for a06 col SID for 99999 col SERIAL# heading 'serial' for 999999 col SQL_ID for a14 col USERNAME for a10 col PROGRAM for a20 col MODULE for a18 col OSU...

To check the output of RMAN: ********************************** select sid,output from v$rman_output; To check backup status: *************************** set lines 1000 set pages 500 col OPERATION for a25 col STATUS for a30 col START_TIME for a18 col END_TIME for a18 col sid for 9999 select SID,RECID,STAMP,OPERATION,OBJECT_TYPE,STATUS,to_char(START_TIME,'dd-mm-yy hh24:mi:ss') START_TIME, to_char(END_TIME,'dd-mm-yy hh24:mi:ss') END_TIME,MBYTES_PROCESSED from v$rman_status order by RECID; To check backup status in last 24 hours: ********************************************* set lines 1000 set pages 500 col OPERATION for a25 col STATUS for a30 col START_TIME for a18 col END_TIME for a18 col sid for 9999 select RECID,STAMP,OPERATION,OBJECT_TYPE,STATUS,to_char(START_TIME,'DD-MON-YY HH24:MI:SS') START_TIME, to_char(END_TIME,'DD-MON-YY HH24:MI:SS') END_TIME,MBYTES_PROCESSED from v$rman_status WHERE START_TIME>=sysdate-1 order ...

Hello all, Here i'm going to give you the script to drop more number of  tables at one go. The below script is being used in our environment to drop more number of tables at the same time whenever we receive a mail from our client. This may help you also if you are in a situation. It's a best option to save time instead of dropping tables one by one. Tomorrow also we will have an activity to drop 111 tables which occupied 105 GB of space & reclaiming physical disk space activity followed by table drop. set timing on SET SERVEROUTPUT ON begin      for X in ( select OWNER,SEGMENT_NAME from dba_segments where SEGMENT_NAME in ( 'BKP_31MAY14', 'BKP_26MAY14', 'BKP_27MAY14', 'BKP_03JUN14', 'BKP_29MAY14', 'BKP_25MAY14', 'BKP_28MAY14', 'BKP_22MAY14', 'BKP_30MAY14', 'BKP_23MAY14', 'BKP_24MAY14', 'BKP_19MAY14', 'BKP_21MAY14', 'BKP_20MAY14', 'BKP_1...

Deal all, Below post content is not my own. It is taken from MOSC document but the example shown below is taken from our environment. I got to know the below topic when we had experienced the bad SQL query performance in one of our production environment during daily batch running time. Hence we had implemented the same mentioned in the title "Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan". Introduction The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints. Some changes in those conditions may affect the calculations of the plan...