Oracle Database Administration

--Check the existing blocking sessions in database: set lines 130 pages 1000 col BLOCKING_STATUS for a100 select s1.INST_ID,s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 / --Check internal blocking sessions which are holding in background: set lines 200 set pages 1000 col event for a40 col obj for a30 select inst_id,sid,blocking_session BSID,blocking_instance BINST,sql_id,event,last_Call_et,wait_time_micro, (select object_name from dba_objects where object_id = row_wait_obj#) obj, decode(event,'enq: TX - row lock contention',dbms_rowid.ROWID_CREATE(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#),null) rid f...

Please refer http://gavinsoorma.com/2009/08/read-only-tablespace-restore-and-recovery/ READ ONLY Tablespace Restore and Recovery Keeping static or historical data in read only tablespaces is a good practice especially for data warehouse type environments. Using the RMAN SKIP READONLY command, we can reduce the backup window and overhead as well by excluding these read only tablespaces from the database backupsets. But we need to keep in mind that we need to take at least one backup of the tablespace after it has been made read only and thereafter we can use the SKIP READONLY command to exclude these tablespaces from the daily or weekly database backups. However, while doing a restore we need to use the CHECK READONLY keywords otherwise by default the read only tablespaces will not be restored and hence the recovery will also bypass these tablespaces. Subssequent attempts to open the database will fail. Let us illustrate the same with an example where we have ma...

CPU usage SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"  FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' -- CPU  AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 1800 -- active within last 1/2 hour AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours ORDER BY st.value; SET ECHO OFF COL HOURS          FORMAT 999,990.99 COL MESSAGE        FORMAT A22 COL OPNAME         FORMAT A16        HEA "OPERATION" COL PCT_COMPLETE   FORMAT 99.9        HEA "PCT"  COL SERIAL#        FORMAT 99999 COL SID            FORMAT 9999 COL STARTED        FORMAT A16        HEA "START TIME" COL TARG...

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner from gv$locked_object l, dba_objects o where l.object_id = o.object_id; select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner, 'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session from v$locked_object l, dba_objects o, v$session s where l.object_id = o.object_id and l.session_id = s.sid; select sid, serial# from gv$session where sid = 0000; alter sysetm kill session ''; select s.sid, s.serial#, p.spid unix_process, s.osuser, s.username, s.machine, s.program, s.sql_hash_value current_hash_value, q1.sql_text current_sql_text, s.prev_hash_value prev_hash_value, q2.sql_text prev_sql_text, to_char(s.logon_time,'DD/MM/YYYY HH24:MI') session_logon_time, o.owner ||'.'|| o.object_name locked_object, l.type lock_type, l.lmode lock_mode, l.ctime lock_time_in_sec from v$lock l, v$session s, v$pro...

you can do with oakcli Deploy Oracle Database Appliance Configure network for Oracle Database Appliance deployment Patching Oracle Database Appliance Unpacking packages into oakcli repository Troubleshoot Oracle Database Appliance Monitor Oracle Database Appliance Validate Oracle Database Appliance Applying the Core Configuration key Copying the deployment configuration file Locate a disk on ODA Manage ODA Repository Manage ODA diagnostics collection oakcli -h command to list the different options available with oakcli: oakcli show -h check if whether ODA deployment is Bare Metal or Virtualized [root@raj ~]# oakcli show env_hw o get the ODA software version [root@raj ~]# oakcli show version oakcli show -h o get the ODA software and component version [root@raj ~]# /opt/oracle/oak/bin/oakcli show version -detail oakcli show disk To list ASM disk groups on ODA [root@raj ~]# oakcli show diskgroup To list the Local file system and ACFS file system ...

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...