How to lock/unlock statistics on a table

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.
The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

STEP 1: Create Table for test

SQL> create table test ( x number );

Table created.

STEP2: Create index on table.

SQL> create index test_idx on test(x);

Index created.

STEP3: Check stats are not locked on table.

When stats is not locked the value of stattype_locked is NULL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–

STEP4: Lock the statistics

SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

STEP5: Now check stats are locked status

When stats is locked the value of stattype_locked is ALL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

STEP 6: Try to gather statistics on locked table and index


SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');

BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

SQL> analyze index ajaffer.test_idx compute statistics;

analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

STEP7: Unlock the statistics

SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Restore the database from tape backup of the Database

Suppose the entire server is lost, 

we have the  tape copy of the backup of the database , which includes RMAN backup pieces and control file backup
Assuming that were the case, we would have to rebuild the entire server, reinstall the OS and the Oracle Software, then restore the backup of the database from tape. . If the Archives and online redo are lost, the recovered database will not include all transactions,
but it will be current up to the last backup.
Steps Required
1.copy the tape backup of the database to disk
2.create the init.ora file
3.create the password file
 4.start the instance in nomount
5.restore the spfile from autobackup spfile backup piece
6.shutdown immediate
7.startup nomount
 8.RESTORE CONTROLFILE FROM AUTOBACKUP;
9.alter database mount;
10.catalog all the backup piece into RMAN
11.restore database
12.recover database;
13.alter database open resetlogs;
14. check the status of listener and if any connection issues then workout and fix the problem
Detailed Description of Steps
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost Stasdb_database]$ . oraenv

ORACLE_SID = [statsdb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@localhost Stasdb_database]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 13 15:49:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database (not started)

RMAN> startup nomount
Oracle instance started
Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                243271152 bytes
Database Buffers             641728512 bytes
Redo Buffers                   6344704 bytes

RMAN> RESTORE SPFILE FROM '/u02/app/Stasdb_database/c-782188580-20120613-02';
Starting restore at 13-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u02/app/Stasdb_database/c-782188580-20120613-02
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-12

RMAN> shutdown immediate;
Oracle instance shut down

RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area     893562880 bytes
Fixed Size                     2218512 bytes
Variable Size                243271152 bytes
Database Buffers             641728512 bytes
Redo Buffers                   6344704 bytes

RMAN> RESTORE CONTROLFILE FROM '/u02/app/Stasdb_database/c-782188580-20120613-02';
Starting restore at 13-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/statsdb/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/statsdb/control02.ctl
Finished restore at 13-JUN-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/u01/app/oracle/flash_recovery_area/statsdb';
Starting implicit crosscheck backup at 13-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 100 objects
Finished implicit crosscheck backup at 13-JUN-12
Starting implicit crosscheck copy at 13-JUN-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-JUN-12
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/flash_recovery_area/statsdb
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hgndeia5_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
File Name: /u01/app/oracle/flash_recovery_area/statsdb/hgndeia5_1_1

RMAN> restore database;
Starting restore at 13-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/statsdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/statsdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/statsdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/statsdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/statsdb/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/statsdb/tripointmonitor01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/statsdb/hhndeia8_1_1 tag=TAG20120613T144216
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:56
Finished restore at 13-JUN-12

RMAN> recover database;
Starting recover at 13-JUN-12
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2345
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/statsdb/hindeimj_1_1 tag=TAG20120613T144851
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/STATSDB/archivelog/2012_06_13/o1_mf_1_2345_7xl72djp_.arc thread=1 sequence=2345
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/STATSDB/archivelog/2012_06_13/o1_mf_1_2345_7xl72djp_.arc RECID=1851 STAMP=785865820
unable to find archived log
archived log thread=1 sequence=2346
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/13/2012 16:03:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2346 and starting SCN of 92188455

RMAN> alter database open resetlogs;
database opened

RMAN>

Your database is ready to use.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Perfomance Tuning Related Queries.

Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History V$ 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

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

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;


Find locks Between 5 and 6 PM on 3/10/15

REM Find locks Between 5 and 6 PM on 3/10/15
set linesize 200
set pagesize 200
col sql_text format a40
col module format a20
SELECT  
            distinct a.sql_id,
            a.inst_id,
            a.blocking_session as BLK_sess,
            a.blocking_session_serial# as BLK_SESS_Serial#,
            a.user_id,
            s.sql_text,
            a.module
FROM  
            GV$ACTIVE_SESSION_HISTORY a,
            gv$sql s
where
            a.sql_id=s.sql_id
            and
            blocking_session is not null
            and
            a.user_id <> 0
            and
            a.sample_time between
                        to_timestamp('2015-03-10 19:00:00','YYYY-MM-DD HH24:MI:SS') and
                        to_timestamp('2015-03-10 18:00:00','YYYY-MM-DD HH24:MI:SS');


Find Invalid Objects

COLUMN object_name FORMAT A30
set linesize 200
set pagesize 200
col object_name format a30
select
  owner,
  object_name,
  object_type, status
from
  dba_objects
where
  owner not in
      ('SYS','SYSTEM','DBSNMP','OUTLN','OLAPSYS','MDSYS','ORDPLUGINS','XDB','DMSYS')
  and
  status='INVALID'
  and
  not object_name like 'BIN$%';


Find Largest Tables in Database (row wise)

WITH BigTbl AS (
SELECT DISTINCT num_rows, table_name, owner
FROM ALL_TABLES
WHERE num_rows >= 5
ORDER BY num_rows DESC)
SELECT ROWNUM RANK, num_rows, table_name, owner
FROM BigTbl
WHERE ROWNUM <= 10;


Find TOP 10 FULL TABLE SCAN SQLs

select * from
   (select b.username,
       t.sql_id,
       sql_fulltext,
       p.operation,
       p.options
   from
       v$sqlarea t,
       v$sql_plan p,
       v$session b
   where
       t.hash_value=p.hash_value
   and
        t.sql_id=b.sql_id
   and
       p.operation='TABLE ACCESS'
   and
       p.options='FULL'
   and
       p.object_owner not in ('SYS','SYSTEM')
   order by
       DISK_READS DESC,
       EXECUTIONS DESC)
where rownum <=10;

Shows Top “Running SQLs”

select  distinct spid,
       s.sid,
       s.serial#,
       to_char(sysdate - last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",
       logon_time,
       osuser,
       s.program,
       schemaname,
       sql_text
from v$session s,
       v$process p,
       v$sql t
where s.paddr=p.addr
   and t.hash_value(+)=s.sql_hash_value
   and s.type !='BACKGROUND';


Temporary tablespace usage

SELECT
            s.sid,
            s.username,
            u.tablespace,
            s.sql_hash_value||'/'||u.sqlhash hash_value,
            u.segtype,
            u.contents,
            u.blocks
FROM
            v$session s,
            v$tempseg_usage u
WHERE
            s.saddr=u.session_addr
order by
            u.blocks;



REM DATAFILE I/O NOTES:
REM File Name - Datafile name
REM Physical Reads - Number of physical reads
REM Reads % - Percentage of physical reads
REM Physical Writes - Number of physical writes
REM Writes % - Percentage of physical writes
REM Total Block I/O's - Number of I/O blocks
REM Use this report to identify any "hot spots" or I/O contention

set linesize 200
set pagesize 200
col name format a46
set linesize 200
set pagesize 200
col name format a46
select      NAME,
      PHYRDS "Physical Reads",
      round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
      PHYWRTS "Physical Writes",
      round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
      fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
      select      sum(PHYRDS) PHYS_READS,
            sum(PHYWRTS) PHYS_WRTS
      from        v$filestat
      ) pd,
      v$datafile df,
      v$filestat fs
where      
      df.FILE# = fs.FILE#
order by
      fs.PHYBLKRD+fs.PHYBLKWRT desc;

I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

ORA-01031: insufficient privileges while configure Enterprise Manager

Configuring OEM manually seems to be very straight forward but sometimes you may come across erors which make it difficult.

Below is one of the problem and its solution which I faced last week.

[oracle@PSNA-UAT-DBO001(VOTEUT01):~]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 6, 2015 4:53:32 AM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:

Database SID: VOTEUT01

Listener port number: 1571

Listener ORACLE_HOME: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

ASM ORACLE_HOME: /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID [ +ASM ]:

ASM port [ 1571 ]:

ASM username [ ASMSNMP ]:

ASM user password:

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

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Local hostname ................ PSNA-UAT-DBO001.ad.issgovernance.com

Listener ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Listener port number ................ 1571

Database SID ................ VOTEUT01

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

ASM ORACLE_HOME ................ /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID ................ +ASM

ASM port ................ 1571

ASM user role ................ SYSDBA

ASM username ................ ASMSNMP

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

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

WARNING : While repository is dropped the database will be put in quiesce mode.

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

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 6, 2015 4:54:32 AM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log.

Apr 6, 2015 4:54:33 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl

WARNING: ORA-01031: insufficient privileges

Apr 6, 2015 4:54:33 AM oracle.sysman.emcp.EMConfig perform

SEVERE:

Database connection through listener failed. Fix the error and run EM Configuration Assistant again.

Some of the possible reasons may be:

1) Listener port 1571 provided is incorrect. Provide the correct port.

2) Listener is not up. Start the Listener.

3) Database service VOTEQA01 is not registered with listener. Register the database service

4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.

5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=<virtual host>.

6) /etc/hosts does not have correct entry for hostname.

Refer to the log file at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log for more details.

Could not complete the configuration. Refer to the log file at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_53_32.log for more details.

You may face this issue because OS user not added to onistall or dba group.but in my case it was there.

After researching I found the reason was missing password file.So to resolve it I have created new password file and re-ran the command and it completed successfully.

[oracle@PSNA-UAT-DBO001(VOTEUT01):dbs]$orapwd file=orapw$ORACLE_SID password=oracle entries=1 force=y

[oracle@PSNA-UAT-DBO001(VOTEUT01):dbs]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 6, 2015 4:58:39 AM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:

Database SID: VOTEUT01

Listener port number: 1571

Listener ORACLE_HOME: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

ASM ORACLE_HOME: /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID [ +ASM ]:

ASM port [ 1571 ]:

ASM username [ ASMSNMP ]:

ASM user password:

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

You have specified the following settings

Database ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Local hostname ................ PSNA-UAT-DBO001.ad.issgovernance.com

Listener ORACLE_HOME ................ /opt/app/oracle/VOTEPR01/product/11.2.0/db_1

Listener port number ................ 1571

Database SID ................ VOTEUT01

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

ASM ORACLE_HOME ................ /opt/app/oracle/GI/product/11.2.0/grid_1

ASM SID ................ +ASM

ASM port ................ 1571

ASM user role ................ SYSDBA

ASM username ................ ASMSNMP

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

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

WARNING : While repository is dropped the database will be put in quiesce mode.

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

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 6, 2015 4:59:28 AM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /opt/app/oracle/VOTEPR01/cfgtoollogs/emca/VOTEQA01/emca_2015_04_06_04_58_39.log.

Apr 6, 2015 4:59:30 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Apr 6, 2015 5:01:54 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Apr 6, 2015 5:01:55 AM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Apr 6, 2015 5:08:59 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Apr 6, 2015 5:09:01 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository

INFO: Uploading configuration data to EM repository (this may take a while) ...

Apr 6, 2015 5:09:54 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Uploaded configuration data successfully

Apr 6, 2015 5:09:55 AM oracle.sysman.emcp.ParamsManager getLocalListener

WARNING: Error retrieving listener for PSNA-UAT-DBO001.ad.issgovernance.com

Apr 6, 2015 5:09:57 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) ...

Apr 6, 2015 5:10:06 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

Apr 6, 2015 5:10:06 AM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Apr 6, 2015 5:10:29 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Apr 6, 2015 5:10:29 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://PSNA-UAT-DBO001.ad.issgovernance.com:5500/em <<<<<<<<<<<

Apr 6, 2015 5:10:32 AM oracle.sysman.emcp.EMDBPostConfig invoke

WARNING:

************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /opt/app/oracle/VOTEPR01/product/11.2.0/db_1/PSNA-UAT-DBO001.ad.issgovernance.com_VOTEQA01/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

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

Enterprise Manager configuration completed successfully

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!