Friday 24 April 2015

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!!!


Friday 10 April 2015

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!!!

Wednesday 1 April 2015

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, ORA-29283: invalid file operation

When doing export occur above error, let’s check directories:
SQL> select directory_name, directory_path from dba_directories
DIRECTORY_NAME     DIRECTORY_PATH
——————————————————————————–
DUMP_DIR       /u02/dump_dir


Two thing’s that hadn’t done is:

1) given correct permissions for that user to acccess the logical directory for export:
SQL> GRANT read, write on directory dump_dir TO ika; <-- username
2) create the physical directory
[oracle@oel6]$ mkdir -p /u02/dump_dir
After that our export run successfully;



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

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

Tuesday 31 March 2015

How to clone the Oracle database software



This note describes the process of cloning an Oracle Home - in this case we are cloning 11g Release 2 Oracle Home from source machine DEV to target machine QA.

STEP 1: Take tar backup of source oracle home location

On source (DEV)

ORACLE_HOME location is: /u01/app/oracle/product/11.2.0/dbhome_1

Go to directory on source machine one level higher and tar the db_home1 directory and all sub-directories

[oracle@(DEV):~]$ pwd

[oracle@(DEV):~]$ /u01/app/oracle/product/11.2.0 

[oracle@(DEV):~]$ tar -cvf 11gr2.tar dbhome_1

STEP 2: Create required directory on target database

On Target (QA)

Create the directory /u01/app/oracle/product/11.2.0 if it does not exist

STEP 3: Scp tar backup of target server and uncompress it.

On Source

scp or ftp the tar file to this location on the target machine

[oracle@(DEV):~]$scp -rp 11gr2.tar oracle@qa:/u01/app/oracle/product/11.2.0 

On target (QA)

Uncompress the tar file

[oracle@(QA):~]$ cd /u01/app/oracle/product/11.2.0

[oracle@(QA):~]$ tar -xvf 11gr2.tar

STEP 4: Clone the Oracle Home using the perl script clone.pl which is located under the $ORACLE_HOME/clone/bin

[oracle@(QA):~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/clone/bin

[oracle@(QA):~]$ perl clone.pl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 ORACLE_HOME_NAME=11GR2_HOME

./runInstaller -clone -waitForCompletion  "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1" "ORACLE_HOME_NAME=11GR2_HOME" -silent -noConfig -nowait
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 13044 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-06-28_12-19-47PM. Please wait ...Oracle Universal Installer, Version 11.2.0.1.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oracle/oraInventory/logs/cloneActions2010-06-28_12-19-47PM.log
.................................................................................................... 100% Done.



Installation in progress (Monday, June 28, 2010 12:20:29 PM WST)
..............................................................................                                                  78% Done.
Install successful

Linking in progress (Monday, June 28, 2010 12:21:06 PM WST)
Link successful

Setup in progress (Monday, June 28, 2010 12:23:34 PM WST)
Setup successful

End of install phases.(Monday, June 28, 2010 12:34:05 PM WST)
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.
--------------------------------------
The "/u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
The "/u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

--------------------------------------
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u01/app/oracle/product/11.2.0/dbhome_1/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The cloning of 11GR2_HOME was successful.
Please check '/u01/app/oracle/oraInventory/logs/cloneActions2010-06-28_12-19-47PM.log' for more details.

Run the root.sh script as root

[oracle@(QA):~]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@(QA):~]$./root.sh
Check /u01/app/oracle/product/11.2.0/dbhome_1/install/root_dev_2010-06-29_07-42-31.log for the output of root script

[oracle@(QA):~]$cat /u01/app/oracle/product/11.2.0/dbhome_1/install/root_qa_2010-06-29_07-42-31.log

Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/dbhome_1
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

STEP 4: Check oraInventory has updated with new oracle home.

We can now check that the OraInventory has been updated with the 11g Release 2 Oracle Home details.

On this machine, earlier there was only a 10g Oracle Home installed. When we check the contents of the inventory.xml file, we will see that it has information about the 11g Oracle Home – 11GR2_HOME

[oracle@(QA):~]$ cat /var/opt/oracle/oraInst.loc
inventory_loc=/u01/app/oracle/oraInventory
inst_group=dba

[oracle@(QA):~]$cd /u01/app/oracle/oraInventory

[oracle@(QA):~]$ ls
backup                   ContentsXML              logs                     oraInstaller.properties  oui
Contents                 install.platform         oraInst.loc              orainstRoot.sh           sessionContext.se

[oracle@(QA):~]$ cd ContentsXML

[oracle@(QA):~]$  ls
comps.xml      inventory.xml  libs.xml


[oracle@(QA):~]$ vi inventory.xml 

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2009, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.1.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="ora1020" LOC="/u01/app/oracle/product/10.2.0" TYPE="O" IDX="1"/>
<HOME NAME="11GR2_HOME" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2"/>
</HOME_LIST>
</INVENTORY>


It’s done. J






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

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