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