Oracle DBA (12c/19c) Articles and Scripts

1.How to identify who logged in? Answer: select username, osuser,program,module,machine, terminal,process, to_char(logon_time, 'YYYY-MM-DD HH24:MI:SS') as logon_time, status, CASE status when 'ACTIVE' THEN NULL ELSE last_call_et END as idle_time from v$session where type='USER' order by status; 2.What all sessions are consuming resources? Answer: select s.sid,s.username,s.osuser, to_char(sm.begin_time,'HH24:MI:ss') as interval_start, to_char(sm.end_time, 'HH24:MI:ss') as interval_end, s.machine,s.process,s.program,s.module, sm.cpu,sm.pga_memory,sm.logical_reads,sm.physical_reads, sm.hard_parses,sm.soft_parses, s.logon_time from v$session s inner join v$sessmetric sm on sm.session_id=s.sid where s.type='USER' order by sm.cpu desc; 3.What all statements consume Most Resources? Answer: select * from (select sql_id,sql_text,executions, elapsed_time,cpu_time,buffer_gets,disk_reads, elapsed_time / executions as avg_elapse_

------------- Shell Script To perform complete Database with RMAN backup ----------------------- [oracle@primaryDB u01]$   cat backup.sh #!/bin/ksh rman target / log = "/u01/rman_log.log"  <<EOF run { backup database plus archivelog; } EXIT; EOF ------ Stored the RMAN backup in logfile with Date Format --------------------- [oracle@primaryDB u01]$ vi backup_full.sh #!/bin/ksh DATE_TIME=`date +%m.%d.%y.%H:%M:%S` LOG_FILE=/u01/logs/fullbackup_PRODDB.log.${DATE_TIME} echo >> $LOG_FILE chmod 666 $LOG_FILE # Show the date & time echo --- Begin backup at `date` --- >> $LOG_FILE export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 echo $ORACLE_HOME >> $LOG_FILE export ORACLE_SID=PRODDB echo $ORACLE_SID >> $LOG_FILE $ORACLE_HOME/bin/rman @/ u01/disk_rman_PRODDB.sql >> $LOG_FILE echo --- End backup at `date` --- >> $LOG_FILE [oracle@primaryDB u01]$ cat disk_rman_PRODDB.sql connect target /; run

Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file. The steps to moving a datafile from a diskgroup to another is as below, using RMAN. 1) Identify the datafile to be moved. 2) Identify the diskgroup on to which the datafile has to be moved. 3) Take the datafile offline. 4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER. 5) Rename the datafile to point to new location. 6) Recover the datafile. 7) Bring the datafile online. 8) Verify the new datafile locations. 9) Delete the datafile from its original location. 1) Identify the datafile to be moved. SQL> SELECT FILE_NAME FROM DBA_DATA_FILES; +ASMDISK2/orcl/datafile/users.256.565313879 <======= Move this to ASMDISK1. +ASMDISK1/orcl/sysaux01.dbf +ASMDISK1/orcl/undotbs01.dbf +ASMDISK1/orcl/system01.dbf 2) Identify the diskgroup on to which the datafile has to be moved. SQL> SELECT GROUP_NUMBER, NAME FROM

Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History v$active_session_history View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past. Top Recent Wait Events set pages 50000 lines 32767 col EVENT format a60 select * from ( select active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history where active_session_history.event is not null group by active_session_history.event order by 2 desc) where rownum < 6 / Top Wait Events Since Instance Startup set pages 50000 lines 32767 col event format a60 select event, total_waits, time_waited from v$system_event e, v$event_name n where n.event_id = e.event_id and n.wait_class !='Idle' and n.wait_class = (select

If you have enabled  archive log mode  in your database then LOG_ARCHIVE_FORMAT parameter will come into role. If your database is in archivelog mode then redo log files will be archived and the parameter LOG_ARCHIVE_FORMAT determines the name of the archived log files. LOG_ARCHIVE_FORMAT uses a text string and variables to specify the format of the archived files. The following variables can be used with the LOG_ARCHIVE_FORMAT 1) %s :  log sequence number 2) %S :  log sequence number, zero filled 3) %t :  thread number 4) %T :  thread number, zero filled 5) %a :  activation ID 6) %d :  database ID 7) %r :  resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. Following is an example of how we can set LOG_ARCHIVE_FORMAT in a database. SQL> ALTER SYSTEM SET

COL orauser HEA "   Oracle User   " FOR a17 TRUNC COL osuser HEA " O/S User " FOR a10 TRUNC COL ssid HEA "Sid" FOR a4 COL sserial HEA "Serial#" FOR a7 COL ospid HEA "O/S Pid" FOR a7 COL slogon HEA "  Logon Time  " FOR a14 COL sstat HEA "Status" FOR a6 COL auth HEA "Auth" FOR a4 COL conn HEA "Con" FOR a3 SELECT    ' '||NVL( s.username, '    ????    ' ) orauser,    ' '||s.osuser osuser,    LPAD( s.sid, 4 ) ssid, LPAD( s.serial#, 6 ) sserial,    LPAD( p.spid, 6 ) ospid,    INITCAP( LOWER( TO_CHAR( logon_time, 'MONDD HH24:MI:SS' ) ) ) slogon,    DECODE( s.status, 'ACTIVE', ' Busy ', 'INACTIVE', ' Idle ', 'KILLED', ' Kill ', '  ??  ' ) sstat,    DECODE( sc.authentication_type, 'DATABASE', ' DB ', 'OS', ' OS ', ' ?? ' ) auth,    DECODE( s.server, 'DEDICAT

This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the