List Session Details for a Given Time Period

--
-- List Session Details for a Given Time Period
--
-- s_time format = '04/JAN/2019 04:00:00.000' 
-- e_time format = '04/JAN/2019 04:00:00.000'  
-- inst_no = Instance Number for RAC.  Use 1 for non RAC
--
 
SET PAUSE ON
SET PAUSE 'Press Return To Continue'
SET HEADING ON
SET LINESIZE 300
SET PAGESIZE 60
 
COLUMN Sample_Time FOR A12
COLUMN username FOR A20
COLUMN sql_text FOR A40
COLUMN program FOR A40
COLUMN module FOR A40
 
SELECT
   sample_time,
   u.username,
   h.program,
   h.module,
   s.sql_text
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s
WHERE  sample_time
BETWEEN '&s_time' and '&e_time'
AND
   INSTANCE_NUMBER=&inst_no
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
ORDER BY 1
/

--
-- List All Columns in dba_hist_active_sess_history for a Given Time Period
--
-- Best run from a GUI like SQL Developer, Toad etc.
--
-- s_time format = '04/JAN/2019 04:00:00.000' 
-- e_time format = '04/JAN/2019 04:00:00.000'
--
 
SELECT * 
FROM dba_hist_active_sess_history 
WHERE sample_time 
BETWEEN '&s_time' 
AND '&e_time' 
ORDER BY sample_time ASC
/

RMAN-06059: expected archived log not found,loss of archived log compromises recoverability

Problem:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 12/29/2015 05:09:31

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

ORA-19625: error identifying file /tmp/thread_1_seq_51256.712.898891279

ORA-27047: unable to read the header block of file

Linux-x86_64 Error: 25: Inappropriate ioctl for device

Additional information: 1

Solution:

Step 1:

RMAN> delete noprompt expired archivelog all;

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1618 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1865 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=5 device type=DISK

List of Archived Log Copies for database with db_unique_name testdb

=====================================================================

Key     Thrd Seq     S Low Time

------- ---- ------- - ---------

1       1    51256   X 19-DEC-15

        Name: /tmp/thread_1_seq_51256.712.898891279

deleted archived log

archived log file name=/tmp/thread_1_seq_51256.712.898891279 RECID=1 STAMP=898907388

Deleted 1 EXPIRED objects

Step 2:

RMAN> crosscheck archivelog all;

Monitor Cluster Time Synchronization

# check_time_sync.ksh
# This script checks clock synchronization between all nodes of the cluster
#
#
. ~oracle/.database_profile
. ~oracle/.cluster_profile
cluhost=`hostname`
export CLUHOST=`echo $cluhost |awk -F. '{print $1}'`
export PRODDIR=~oracle/prod/
LogDate=`date +%Y%m%d.%H%M%S`
CLULOG=/tmp/cluvfy_clocksync_$LogDate.log

export CLULOG CLUERR ERREMAIL

$ORACLE_HOME/bin/cluvfy comp clocksync -n all > $CLULOG
CLUERR=`grep 'Verification of Clock Synchronization across the cluster nodes was successful.' $CLULOG | wc -l`

if [  "${CLUERR}" -eq 0  ] ; then
  mail -s "${CLUHOST}"': Alarm: Clock Synchronization check failed between nodes of the cluster' $ERREMAIL < $CLULOG
else
 CLUERR=`grep 'PRVF-5413' $CLULOG | wc -l`
 if [ "${CLUERR}" -gt 0 ] ; then
    mail -s "${CLUHOST}"': Alarm: Clock Synchronization check failed between nodes of the cluster' $ERREMAIL < $CLULOG
 fi
fi


rm -f $CLULOG

How to take backup listener log, SCAN listener log

 This script is to backup the alert log and listener log to a backup location.  A weekly or monthly cron job can be scheduled to run this script.



#!/bin/ksh
.  ~oracle/.env
export timestamp=`date +%y%m%d`
export machine=`hostname`
typeset -u machine
export MACHINE=`echo $machine |awk -F. '{print $1}'`
export LIS_NAME1=NOLISTENER1
export LIS_NAME2=NOLISTENER2
export LIS_NAME3=NOLISTENER3
case ${MACHINE} in
        NODE101)
        export LIS_NAME1=listener_scan1
        export LIS_NAME2=listener_scan2
        export LIS_NAME3=listener_scan3
        export LIS_NAME=listener
        export LOGBACKDIR=~oracle/prod/backup/logbackup
        ;;
        *)
        echo "\n\n Error: Environment \"${Market}\" not defined for this job"
        exit 8
esac

#mkdir $LOGBACKDIR

#
# copy the log files to the backup directory and create an empty *.log file
#

cp ${ORACLE_BASE}/diag/tnslsnr/${HOSTNAME}/${LIS_NAME}/trace/${LIS_NAME}.log $LOGBACKDIR/${LIS_NAME}.$timestamp
if [ $? == 0 ]
then
> ${ORACLE_BASE}/diag/tnslsnr/${HOSTNAME}/${LIS_NAME}/trace/${LIS_NAME}.log
fi

if [ "$LIS_NAME1" != "NOLISTENER1" ]; then
cp /u01/app/11.2.0/grid_2/log/diag/tnslsnr/${HOSTNAME}/${LIS_NAME1}/trace/${LIS_NAME1}.log $LOGBACKDIR/${LIS_NAME1}.$timestamp
if [ $? == 0 ]
then
> /u01/app/11.2.0/grid_2/log/diag/tnslsnr/${HOSTNAME}/${LIS_NAME1}/trace/${LIS_NAME1}.log
fi
gzip ${LOGBACKDIR}/${LIS_NAME1}.$timestamp
fi


if [ "$LIS_NAME2" != "NOLISTENER2" ]; then
cp /u01/app/11.2.0/grid_2/log/diag/tnslsnr/${HOSTNAME}/${LIS_NAME2}/trace/${LIS_NAME2}.log $LOGBACKDIR/${LIS_NAME2}.$timestamp
if [ $? == 0 ]
then
> /u01/app/11.2.0/grid_2/log/diag/tnslsnr/${HOSTNAME}/${LIS_NAME2}/trace/${LIS_NAME2}.log
fi
gzip ${LOGBACKDIR}/${LIS_NAME2}.$timestamp
fi

if [ "$LIS_NAME3" != "NOLISTENER3" ]; then
cp /u01/app/11.2.0/grid_2/log/diag/tnslsnr/${HOSTNAME}/${LIS_NAME3}/trace/${LIS_NAME3}.log $LOGBACKDIR/${LIS_NAME3}.$timestamp
if [ $? == 0 ]
then
> /u01/app/11.2.0/grid_2/log/diag/tnslsnr/${HOSTNAME}/${LIS_NAME3}/trace/${LIS_NAME3}.log
fi
gzip ${LOGBACKDIR}/${LIS_NAME3}.$timestamp
fi
#
# zip up the old logs files in the background
#
cd $LOGBACKDIR
gzip ${LIS_NAME}.$timestamp
ls -lrt *.$timestamp.*

find $LOGBACKDIR -name '*' -mtime +365 -exec rm {} \;

exit

Find out active user in the database

Below Query find out active user in the database.

select name, CTIME as Created, PTIME as PssWdDate, EXPTIME as ExpirePasswdDate,
       expiry_date as Expired_acct_date, LTIME as Locked, lock_date, account_status,
       nvl(W.priv,'READ') "R/W"
  from sys.USER$ u, dba_users du,
       (select distinct y.username, y.priv from dba_users U,
    (
     select distinct dsp.grantee username,'WRITE' priv
      from dba_sys_privs dsp
      where (dsp.PRIVILEGE like 'ADMIN%'
         or dsp.PRIVILEGE like 'ALTER%'
         or dsp.PRIVILEGE like 'CREATE ANY%'
         or dsp.PRIVILEGE like 'CREATE C%'
         or dsp.PRIVILEGE like 'CREATE DAT%'
         or dsp.PRIVILEGE like 'CREATE IND%'
         or dsp.PRIVILEGE like 'CREATE JO%'
         or dsp.PRIVILEGE like 'CREATE LIB%'
         or dsp.PRIVILEGE like 'CREATE MATE%'
         or dsp.PRIVILEGE like 'CREATE OPE%'
         or dsp.PRIVILEGE like 'CREATE P%'
         or dsp.PRIVILEGE like 'CREATE RO%'
         or dsp.PRIVILEGE like 'CREATE SEQ%'
         or dsp.PRIVILEGE like 'CREATE T%'
         or dsp.PRIVILEGE like 'CREATE VIE%'
         or dsp.PRIVILEGE like 'DELETE%'
         or dsp.PRIVILEGE like 'DROP%'
         or dsp.PRIVILEGE like 'INSERT%'
         or dsp.PRIVILEGE like 'UPDATE%'
         or dsp.PRIVILEGE like 'UN%'
         or dsp.privilege like 'GRANT%'
         or dsp.privilege like 'ANAL%'
         or dsp.privilege like 'MANAGE%'
         or dsp.privilege like 'FORCE%'
         or dsp.privilege like '%PORT%'
         or dsp.privilege like 'FLASHBACK%'
         or dsp.privilege like 'EXECUTE%'
         or dsp.privilege like 'AUDIT %'
         or dsp.privilege like 'DE%'
         or dsp.privilege like '%QUE%'
         or dsp.privilege like 'UN%'
         or dsp.privilege like 'BACKUP%'
         or dsp.privilege like 'BECOME%'
         or dsp.privilege like 'MERGE%'
         or dsp.PRIVILEGE like 'LOCK%')
        and dsp.PRIVILEGE not in ('CREATE SESSION','UNLIMITED TABLESPACE')
     UNION ALL
     select distinct dtp.grantee username,'WRITE' priv
       from dba_tab_privs dtp
      where dtp.privilege in ('FLASHBACK','ON COMMIT REFRESH',
      'ALTER','DEQUEUE','UPDATE','DELETE','DEBUG',' QUERY REWRITE',
      'USE','INSERT','INDEX','WRITE','REFERENCES','MERGE VIEW')
     UNION ALL
     select distinct drp.grantee username, 'WRITE' PRIV
       from dba_role_privs drp
      where GRANTED_ROLE in ('APPLICATION','DBA','RESOURCE')
     UNION ALL
     select distinct dtp.grantee usernmae, 'WRITE' Priv
       from dba_tab_privs dtp
      where privilege not in ('READ','SELECT','EXECUTE')
       ) Y
        where U.username = Y.username
    ) W
 where u.name = du.username
   and du.username=w.username(+)
   and account_status in ('OPEN','EXPIRED(GRACE)','LOCKED(TIMED)','EXPIRED(GRACE) '||'&'||' LOCKED(TIMED)')
order by 1