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

Check Database availability

In Automation process , you can use below script, check DB available or not.


#!/bin/ksh

. ~oracle/.bash_profile

if (( $# < 1 || $# > 1 ))
then
        print "Incorrect arguments"
        print "Usage : $0 <DATABASE_CONNECT_STRING>"
        exit
fi

DATABASE_NAME=$1
DBA_PWD=`cat ~oracle/.pwd`
LOG=/tmp/check_db.${DATABASE_NAME}.log

####################################################################################

$ORACLE_HOME/bin/sqlplus system/${DBA_PWD}@${DATABASE_NAME}  << EOF > ${LOG}
set pages 0
select 'Testing DB Connection' from dual;
exit;
EOF

if grep 'Testing DB Connection' ${LOG} ;
then
exit 0
else
mail -s '***ERROR: DB Connection Error for '"${DATABASE_NAME}"'@'"${MACHINE}"'. Please check.('"$0"')' $DBAEMAIL < ${LOG};
exit 1
fi

[INS-08101] Unexpected error while executing the action at state: 'performChecks

When you install or upgrade from 11gR1 to 12c you can see below error in installer log file(  /tmp/OraInstall2014-12-08_11-32-53AM.) or installer stop to move next step.

ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:608
oracle.install.commons.flow.FlowException: [INS-08101] Unexpected error while executing the action at state: 'performChecks'
        at oracle.install.commons.flow.AbstractFlowExecutor.startAction(AbstractFlowExecutor.java:368)
        at oracle.install.commons.flow.AbstractFlowExecutor.enterVertex(AbstractFlowExecutor.java:601)
        at oracle.install.commons.flow.AbstractFlowExecutor.transition(AbstractFlowExecutor.java:341)
        at oracle.install.commons.flow.AbstractFlowExecutor.nextState(AbstractFlowExecutor.java:276)
        at oracle.install.commons.flow.AbstractFlowExecutor.nextViewState(AbstractFlowExecutor.java:235)
        at oracle.install.commons.flow.DefaultFlowNavigator.goForward(DefaultFlowNavigator.java:58)
        at oracle.install.commons.flow.jewt.FlowWizard$1.run(FlowWizard.java:137)
        at oracle.install.commons.flow.jewt.FlowWizard$TransitionManager$1.run(FlowWizard.java:113)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
        at java.lang.Thread.run(Thread.java:682)
Caused by: java.lang.NullPointerException
        at oracle.ops.mgmt.cluster.ClusterInfo.getReleaseVersionString(ClusterInfo.java:1991)
        at oracle.ops.mgmt.cluster.ClusterInfo.getSIHAReleaseVersionString(ClusterInfo.java:1431)
        at oracle.ops.verification.framework.util.VerificationUtil.getSIHAReleaseVersionObjWithException(VerificationUtil.java:7946)
        at oracle.ops.verification.framework.util.VerificationUtil.getSIHAReleaseVersionObjWithException(VerificationUtil.java:7919)
        at oracle.ops.verification.framework.util.VerificationUtil.getSIHAReleaseVersionObj(VerificationUtil.java:7851)
        at oracle.ops.verification.framework.util.VerificationUtil.getSIHAReleaseVersionObj(VerificationUtil.java:7791)
        at oracle.ops.verification.framework.engine.task.TaskFactory.isLocalNodeCRSRunning(TaskFactory.java:1457)
        at oracle.ops.verification.framework.engine.task.TaskFactory.getTaskListSysReq(TaskFactory.java:4738)
        at oracle.ops.verification.framework.engine.task.TaskFactory.getTaskListSysReq(TaskFactory.java:4440)
        at oracle.ops.verification.framework.engine.task.TaskFactory.getTaskListPreSIDBInst(TaskFactory.java:2763)
        at oracle.ops.verification.framework.engine.task.TaskFactory.getTaskList(TaskFactory.java:581)
        at oracle.ops.verification.framework.engine.task.TaskFactory.getTaskList(TaskFactory.java:830)
        at oracle.cluster.verification.ClusterVerification.getPreReqTasksForSIDBInst(ClusterVerification.java:1012)
        at oracle.install.library.util.cvu.CVUHelper.getPreReqTasksForSIDBInst(CVUHelper.java:414)
        at oracle.install.ivw.db.action.PrereqAction.getProductVerificationTasks(PrereqAction.java:131)
        at oracle.install.commons.base.interview.common.action.AbstractPrereqAction.execute(AbstractPrereqAction.java:89)
        at oracle.install.commons.flow.AbstractFlowExecutor.startAction(AbstractFlowExecutor.java:366)
        ... 13 more

---# End Stacktrace #-----------------------------

*******************************

Problem:

ORA_NLS10 setting is incorrect. 

Fix:

For 11g/12c, it's unnecessary to set ORA_NLS10. The issue was solved after the environment variable is unset and installer restarted.

ORA-00054: resource busy and acquire with NOWAIT specified

Step 1: Identify the session which is locking the object
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';

Step 2: kill that session using
alter system kill session 'sid,serial#'; 

ORA-20079: full resync from primary database is not done

When we added new files in primary database sometime below errors raise in Stand by database while RMAN
backup is running.

RMAN backups are run from Standby server.  Whenever a structural change is made on the primary , attempts to resync from the primary using db_unique_name  during the standby backup fails:

ORA-20079: full resync from primary database is not done

doing automatic resync from primary
resyncing from database with DB_UNIQUE_NAME testdb

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 08/23/2014 22:45:24
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of partial resync command on default channel at 08/23/2014 22:45:24
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

Files have been added at the Primary site

RMAN is trying to implicitly resync from the Primary using db_unique_name as it is aware that structural changes have been made.

However, RMAN is unable to connect to the primary because no connect string was used when connecting to the standby - in order to resync from another host in a Data Guard configuration , the connection to target must be made with a username, password and alias.

Solution:

Use a TNS connect string when connecting to the standby

Primary: testdb
standby: testdb123

$ rman target  sys/test123@testdb123 catalog rman/rman@rcat
RMAN> resync catalog from db_unique_name testdb;
resyncing from database with DB_UNIQUE_NAME testdb
starting full resync of recovery catalog
full resync complete



Now you can run RMAN backup.

Enable temporary sudo access

Below script would be useful how to give temporary  root sudo access  to user.


sudo -u test1 /home/test1/scripts/tempsudoaccess.sh oracle test101.testdb.com TICK000202


#!/bin/bash
# Para 1 => User
# Para 2 => Server
USAGE="tempsudoaccess.sh <User Name> <FQDN of Server> <Ticket Number>"
if [ $# -ne 3 ]; then
        echo $USAGE
        exit
fi
USERNAME="$1"
USERNAMEOK=""
USERNAMEOK="`id $USERNAME | grep ^id`"
SRVNAME="$2"
TICKET="$3"
if [ "$USERNAMEOK" != "" ]; then
        echo "Invalid User"
else
        echo "rm -f /etc/sudoers.d/$USERNAME" > /tmp/$USERNAME
        echo "# Access Granted per Ticket : $TICKET" > /tmp/${USERNAME}_sudo
        if [ "$USERNAME" == "oracle" ]; then
                echo "$USERNAME ALL=(root) NOPASSWD:ALL" >> /tmp/${USERNAME}_sudo
        else
                echo "$USERNAME ALL=(root) ALL" >> /tmp/${USERNAME}_sudo
        fi
        scp -rq /tmp/$USERNAME* $SRVNAME:/tmp/
        #ssh -n $SRVNAME "sudo mv -f /tmp/$USERNAME /opt; sudo /bin/chown root.root /tmp/${USERNAME}_sudo; sudo /bin/chmod 440 /tmp/${USERNAME}_sudo; sudo mv -f /tmp/${USERNAME}_sudo /etc/sudoers.d/$USERNAME; sudo at now + 7 days < /opt/$USERNAME"
        ssh -n $SRVNAME "sudo /bin/chown root.root /tmp/${USERNAME}_sudo; sudo /bin/chmod 440 /tmp/${USERNAME}_sudo; sudo mv -f /tmp/${USERNAME}_sudo /etc/sudoers.d/$USERNAME; sudo at now + 7 days < /tmp/$USERNAME"
fi

Change the sequence number

CREATE OR REPLACE PROCEDURE SYS.SEQUENCE_NEWVALUE(

seqowner VARCHAR2,

seqname VARCHAR2,

newvalue NUMBER) AS

ln NUMBER;

ib NUMBER;

BEGIN

SELECT last_number, increment_by

INTO ln, ib

FROM dba_sequences

WHERE sequence_owner = upper(seqowner)

AND sequence_name = upper(seqname);

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname ||

' INCREMENT BY ' || (newvalue - ln);

EXECUTE IMMEDIATE 'SELECT ' || seqowner || '.' || seqname ||

'.NEXTVAL FROM DUAL' INTO ln;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqowner || '.' || seqname

|| ' INCREMENT BY ' || ib;

END;

GRANT EXECUTE ON sequence_newvalue TO gokhan;

EXEC sequence_newvalue( 'GOKHAN', 'SAMPLE_SEQ', 10000 );


delete child record

 alter table test.Table1 enable constraint table1_FK;
alter table test.Table1 enable constraint table1_FK
                                                     *
ERROR at line 1:
ORA-02298: cannot validate (test.table1_fk) - parent keys not
found

select 'delete from '  ||c.owner||'.'||c.table_name ||' a where not exists (select ''x'' from ' ||r.owner||'.'||r.table_name ||' where '||rc.column_name||' = a.'||cc.column_name||')'
 from dba_constraints c,
 dba_constraints r,
 dba_cons_columns cc,
 dba_cons_columns rc
 where c.constraint_type = 'R'
 and c.owner not in ('SYS','SYSTEM')
 and c.r_owner = r.owner
 and c.owner = cc.owner
 and r.owner = rc.owner
 and c.constraint_name = cc.constraint_name
 and r.constraint_name = rc.constraint_name
 and c.r_constraint_name = r.constraint_name
 and cc.position = rc.position
 and c.owner = 'TEST'
 and c.table_name = 'TABLE1'
 and c.constraint_name = TABLE1_FK'

Change SYSMAN Password in 12C

Steps to follow if the current SYSMAN password is unknown

1. Stop all the OMS:


oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl stop oms

Output.

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
Note:

Execute the same command on the primary OMS machine and Standby as well. Do not include '-all' as the Admin Server needs to be up during this operation.

2. Modify the SYSMAN password:


oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd ktest100 -new_pwd Btest100


Output:

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.

3. Stop the Admin server on the primary OMS machine and re-start all the OMS:

oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl stop oms -all

Output:

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

oracle@test100:/u01/app/em/12.1.0.2/oms/bin$ ./emctl start oms

Output:

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

 

Performance Tuning scripts

Top Recent Wait Events

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

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 wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

First determine the snapshot id values for the period in question.

In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum <  6

Top CPU Consuming SQL During A Certain Time Period

Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
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,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

Top Segments ordered by Physical Reads

col segment_name format a20
col owner format a10 
select segment_name,object_type,total_physical_reads
 from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
 where statistic_name in ('physical reads')
 order by total_physical_reads desc)
 where rownum < 6 

Top 5 SQL statements in the past one hour

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum < 6

SQL with the highest I/O in the past one day

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum < 6

Top CPU consuming queries since past one day

select * from (
select 
 SQL_ID, 
 sum(CPU_TIME_DELTA), 
 sum(DISK_READS_DELTA),
 count(*)
from 
 DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
 s.snap_id = a.snap_id
 and s.begin_interval_time > sysdate -1
 group by 
 SQL_ID
order by 
 sum(CPU_TIME_DELTA) desc)
where rownum < 6

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.

In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

select * from
 (
select
 sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
 from
dba_hist_sqlstat sql,
dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
and
 s.snap_id= &snapid
 order by
 c3 desc)
 where rownum < 6 
/

Analyse a particular SQL ID and see the trends for the past day

select
 s.snap_id,
 to_char(s.begin_interval_time,'HH24:MI') c1,
 sql.executions_delta c2,
 sql.buffer_gets_delta c3,
 sql.disk_reads_delta c4,
 sql.iowait_delta c5,
sql.cpu_time_delta c6,
 sql.elapsed_time_delta c7
 from
 dba_hist_sqlstat sql,
 dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
 and s.begin_interval_time > sysdate -1
 and
sql.sql_id='&sqlid'
 order by c7
 /

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

Show the TEMP tablespace history of sort usage

Script that will show the TEMP tablespace history of sort usage

select distinct

c.username "user",

c.osuser ,

c.sid,

c.serial#,

b.spid "unix_pid",

c.machine,

c.program "program",

a.blocks * e.block_size/1024/1024 mb_temp_used  ,

a.tablespace,

d.sql_text

from

v$sort_usage a,

v$process b,

v$session c,

v$sqlarea d,

dba_tablespaces e

where c.saddr=a.session_addr

and b.addr=c.paddr

and c.sql_address=d.address(+)

and a.tablespace = e.tablespace_name;

How to find out mapped oracle ASM disk.

First you need enter into root.

sudo su -

Then change directory into cd /etc/init.d

[root@test200 init.d]# ./oracleasm listdisks
DATA01
FRA01
OCRVOTE01

If you want find which ASM disk is mapped which device, then you must use oracleasm querydisk with ASM disk name.


[root@test200 init.d]# oracleasm querydisk -d DATA01
Disk "DATA01" is a valid ASM disk on device [8,15]

As see you, DATA01 is valid ASM disk on device [8,15].

What is means?
It means DATA01 is mapped to device [8,15].

How to find [8,15] device?
We must use Linux ls –l  command as below.

[root@test200 init.d]# ls -l /dev/* | grep 8, | grep 15
brw-rw---- 1 root disk      8,  15 Jan  12 13:22 /dev/sdf

Now, we can say DATA01 is mapped to /dev/sdf

ORA-17628, ORA-19505 during RMAN DUPLICATE FROM ACTIVE

The following error is reported trying to create a Physical Standby database or clone  your database different location    using "duplicate from active database" :


input datafile file number=00032 name=+test/datafile/test01.425.877491083
RMAN-03009: failure of backup command on ch01 channel at 04/20/2015 20:39:29
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ch01: starting datafile copy
input datafile file number=00035 name=+test/datafile/test02.386.877491081
RMAN-03009: failure of backup command on ch02 channel at 04/20/2015 20:39:30
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ch02: starting datafile copy
input datafile file number=00115 name=+test/datafile/test03.260.877491083
RMAN-03009: failure of backup command on ch01 channel at 04/20/2015 20:39:30
ORA-17628: Oracle error 19505 returned by remote Oracle server

One datafile  is not using OMF name while the rest of the datafiles are using OMF name.

It is not oracle bug and this expected behavior.

The reason the duplicate of the database is failing is because there is no db_file_name_convert and the datafile that has an alias is not using an OMF name, so a new OMF name is not created for it and the filename is unchanged.

The parameter "parameter_value_convert"  changes the string  from 'xxx' to 'yyy' in other  initialization parameters, but not in the datafile names.  If the names of the datafiles are desired to be changed,  then  db_file_name_convert should be used


Solution:

1. Use the parameter DB_FILE_NAME_CONVERT and specify the complete location of the datafile using alias :

SET DB_FILE_NAME_CONVERT='+DATA/xxx/datafile','+DATA/yyy/datafile/'


or

2. Create the directory "xxx" in the diskgroup where the image copy is being created by the auxiliary database.



 

Backup cron jobs

If you have corn jobs in your server, below script helpful to take backup cronjobs details.



#!/bin/ksh
#
#   Keep a backup on crontab entries for 90 days
#
#- ENV VARIABLES -#

. ~oracle/.env
DATE=`date +%y%m%d`
CRONTAB_DIR=/u01/app/oracle/prod/
MACHINE=`hostname`
export DATE MACHINE

# Keep last 30  days of crontab entries
find $CRONTAB_DIR -name "crontab.*.txt" -mtime +30 -exec rm {} \;

crontab -l > $CRONTAB_DIR/crontab.$DATE.txt

if [[ $? -gt 0 ]];then
mail -s 'Error creating backup crontab on '$MACHINE'' $DBAEMAIL << EOF
Problems creating backup crontab. Check $CRONTAB_DIR for backup crontabs.
EOF
exit
fi

exit 0

RMAN DUPLICATE: Errors In Krbm_getDupCopy found in alert.log

Issue.

Executing active duplicate for standby:
duplicate target database for standby from active database ...

In the alert.log appear messages like this:

RMAN DUPLICATE: Errors in krbm_getDupCopy
Errors in file /u02/app/oracle/diag/rdbms/odocdr/odOCDR/trace/odOCDR_ora_27666.trc:
ORA-19625: error identifying file +DATA/orcl/datafile/users_ts.895.787168577
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/datafile/users_ts.895.787168577
ORA-15012: ASM file '+DATA/orcl/datafile/users_ts.895.7871685777' does not exist

 

Solution:

 

As files have already been deleted from auxiliary destination, ignore those messages.

When the files already copied can be used by next duplicate trial then don't remove the files. If you don't delete the files after a failed duplicate then krbm_getDupCopy will find the files and you will see no errors.

If you don't want to see those messages in alert.log but datafiles have already been deleted, on
Auxiliary host, delete the file $ORACLE_HOME/dbs/_rm_dup_<dup_db>.dat  where dup_db is the name of the clone instance.

Inside this file rman finds the name of the datafiles already copied to auxiliary host.

Redo log switch count per day

select to_char(first_time,'DD-MON') day,   

sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",   

sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",   

sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",   

sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",   

sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",   

sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",   

sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",   

sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",   

sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",   

sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",   

sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",   

sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",   

sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",   

sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",   

sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",   

sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",   

sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",   

sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",   

sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",   

sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",   

sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",   

sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",   

sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",   

sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23",   

count(to_char(first_time,'MM-DD')) Switches_per_day   

from v$log_history   

where trunc(first_time) between trunc(sysdate) - 16 and trunc(sysdate)   

group by to_char(first_time,'DD-MON')    

order by to_char(first_time,'DD-MON') ; 

Which Tablespaces Do Not Have Enough Free Space

Below script find out which tablespaces don't have space in the database.

column  Today NEW_VALUE mhoy noprint format a1 trunc
column  Time NEW_VALUE mhora noprint format a1 trunc
column  inst NEW_VALUE minst noprint format a1 trunc

select upper(instance) inst
  from v$thread;

set pagesize 53
set linesize 120
set feedback off
ttitle left 'DB Report' -
       right mtoday skip 1 -
       left minst -
       right mtime skip 1 -
       center 'DATAFILES AND SIZES OF TABLESPACES' skip 3;
btitle skip 2 center 'Page :' format 999 sql.pno;

break on Tablespace on report
compute sum of KBTotal on report 
compute sum of KBUsed  on report
compute sum of KBFree  on report

column Tablespace format a12
column File_name  format a32
column status     format a10
column KBTotal    format 99,999,990
column KBUsed     format 99,999,990
column KBFree     format 99,999,990
column %Used      format 990.99
column %Free      format 990.99
column Extents    format 990
column MaxExtentKB format 999,999 

spool tbsp_free

SELECT TO_CHAR(sysdate, 'DD/MM/YY')   Today
     , TO_CHAR(sysdate, 'hh24:mi:ss') Time
     , df.tablespace_name          "Tablespace"
     , df.file_name                "File_name"
     , count(*)                    "Extents"
     , NVL(df.bytes,0)/1024        "KBTotal"
     , (NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024 "KBUsed"
     , SUM(NVL(fs.bytes,0))/1024   "KBFree"
     , (((NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024)*100)/((NVL(df.bytes,0)
/1024)) "%Used"
     , ((SUM(NVL(fs.bytes,0))/1024)*100) / (NVL(df.bytes,0)/1024)  "%Free"
     , MAX(NVL(fs.bytes,0))/1024   "MaxExtentKB"
  FROM dba_data_files df
     , dba_free_space fs
 WHERE df.file_id= fs.file_id(+)
 GROUP BY df.tablespace_name
        , df.file_name
        , df.bytes
 ORDER BY df.tablespace_name
/
spool off
ttitle off
btitle off