Skip to main content

Posts

Steps/procedure for Adding new disk to ACFS disk group or extending/increasing the size of ACFS file system diskgroup

Hello Guys, Here we are going to see the steps to add new disk to ACFS file system disk group or How to extend/increase the size of ACFS file system diskgroup (ACFS - Oracle Automatic Storage Management Cluster File System). This is also real time scenario of my environment. We received critical alert on ACFS file system mount point /prodtop which is having only 10GB free space and 90% of disk group space has been used. So customer requested us to increase size of /prodtop to another 10GB. Before extending the diskgroup [root@node1: ~]# df -Th /prodtop/ Filesystem                    Type      Size  Used Avail Use% Mounted on /dev/asm/prodtop-153  ACFS    95G   85G   11G  90%    /prodtop So here we go. 1) Ask OS team to provide a new partitioned disk size of 10GB which should be shareable across all nodes if it is a clustered environment. For example *********** We got the below partitioned disk. Disk /dev/xvdj: 10.7 GB, 10737418240 bytes     Device Boot   
Post a Comment
Read more
Recent posts

Cloud Control EM13c - Loader Throughput (rows per second) for Loader_D crossed the critical threshold and Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold

Hello Guys, Here is an another post related to Cloud Control EM13c - Real-time Scenario. Suddenly We were receiving the following warning and critical alerts from EM13c Cloud Control during the time slot between 12 AM and 04:00 AM daily. 1)  Message= Loader Throughput (rows per second) for Loader_D crossed the critical threshold (xx). Current value: xx.xx 2)  Message= Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold (x,xxx). Current value: xxxx.xx 3)  ORA-error stack (3,136) and ORA-error stack (609) were also logged in alert log 4)  Message= Incident (BEA-310003 [LowMemory]) detected in $OMS_HOME/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag/ofm/GCDomain/EMGC_OMS1/alert/log.xml at time/line number: When we analyzed the AWR reports of EM PDB repository database, we found there were few PL/SQL statements given below causing this issue with wait event  SQL*Net Break/reset To Client Waits. SQL statement wit
Post a Comment
Read more

Steps to add ASM disk to existing ASM diskgroup

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         For example $> id $> /usr/sbin/oracleasm listdisks $> /usr/sbin/oracleasm querydisk <each disk from previous output>       4)       Co
Post a Comment
Read more

SQL queries to get the blocking session details in oracle database

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_
Post a Comment
Read more

SQL query to get the sessions causing high CPU usage on database (DB) server

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=
Post a Comment
Read more

Metrics Process Limit Usage percentage (%) is critical or warning in OEM or Cloud Control 12c or Cloud Control 13c

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
Post a Comment
Read more

RMAN Job progress

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
Post a Comment
Read more