Your DB at the Top speed

Tips & tricks

Oracle Enterprise Manager (EM)
Tuning tips
Command-line history in SQL*Plus, RMAN, ADRCI

Oracle Enterprise Manager (EM)

EM reinstalling procedure

After changing of hostname IP address, EM dbconsole port, instance SID, or even TZ settings, EM is going to fail. Using "emca" you can reconfigure eg. the port number to 1150:

emca -reconfig ports -DBCONTROL_HTTP_PORT 1150

but in more serious cases there's only one solution - reinstallation. I tried many approaches (fixing output errors and so on) but this one is effective. Later I've found that Oracle says exactly the same ;-) ... and better do it off rush hours due to its high resource consumption.

Check the status:

emctl status dbconsole

1st way:

emca -deconfig dbcontrol db -repos drop
# then
emca -config dbcontrol db -repos create

2nd way:

emca -deconfig dbcontrol db
# then
emca -config dbcontrol db -repos recreate

When something went wrong, delete DB Control Repository Objects manually with SQLPlus:

drop user sysman cascade;
drop role MGMT_USER;
drop user MGMT_VIEW cascade;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym SETEMVIEWUSERCONTEXT;

Now, try again "emca" with create or recreate option.

Example of using an input file for "emca" parameters - dropping EM repository (don't worry, not database, as some may think) with required parameters, for RAC, silent mode:

emca -deconfig dbcontrol db -repos drop -cluster -silent -respFile emca.par

# where emca.par content:
  DB_UNIQUE_NAME=[name]
  PORT=1521
  SYS_PWD=[pwd]
  SYSMAN_PWD=[pwd2]
  DBSNMP_PWD=[pwd3]
  HOST=[hostname]
  SID=[sid]
  ORACLE_HOSTNAME=[hostname]

Checking which port is correct:

cat $ORACLE_HOME/install/portlist.ini
# eg.
# Enterprise Manager Console HTTP Port (orcl) = 1158

Calling EM in browser:

http://[hostname]:1158/em

Tested for 10g.

Securing EMKEY procedure

EM stores passwords for server, database and other sensitive data in its repository in a hash form. Thanks to that, authentication for many EM tasks is done in background without prompting for user credentials. But they can be decrypted! Let's better secure it.

Check the status:

emctl status emkey -sysman_pwd "oracle"

By default, the emkey is stored in the $ORACLE_HOME/sysman/config/emkey.ora file.
If you don't have the file on your file system, create one, before securing procedure:

emctl config emkey -repos -emkeyfile $ORACLE_HOME/sysman/config/emkey.ora \
-force -sysman_pwd "oracle"

Now, remove the emkey from repository:

emctl config emkey -remove_from_repos -sysman_pwd "oracle"

The Em Key has been removed from the Management Repository.
Make a backup copy of OH/sysman/config/emkey.ora file and store it on 
another machine.
WARNING: Encrypted data in Enterprise Manager will become unusable if 
the emkey.ora file is lost or corrupted.

Done ;-)

More on Troubleshooting Enterprise Manager

Tuning tips

Starting point

The two main areas of measuring overall performance rate for DB instance are:
Buffer Cache Hit Ratio - it calculates how often a requested block has been found in the buffer cache without requiring disk access.
DB time - The most important of the time model statistics. It represents the total time spent in database calls and is a indicator of the total instance workload. It is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions). DB time is measured cumulatively from the time that the instance was started.

Simple query for "Buffer Cache Hit Ratio" (the result should be near 1):

select NAME, 1-(PHYSICAL_READS/(DB_BLOCK_GETS + CONSISTENT_GETS)) "RATIO" 
from v$buffer_pool_statistics;

and "DB time" metric, which in short, is sum of all activities of all sessions:

select STAT_NAME, Round(VALUE/1000000) "Time (Sec)"
from v$sys_time_model
order by 2 desc;

The result you may compare with other elapsed times to see which one plays the biggest role in DB activity.

Everybody says, tuning is very complex task, and it is.
For newbies is good to know where to start from, so start with AWR report and look at the "Top 5 events". Also, you'll find there "DB time" and "Buffer Cache Hit Ratio" values.

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Here you can see what you need:

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       367 10-Sep-09 10:00:07        41       4.3
  End Snap:       368 10-Sep-09 11:00:11        42       4.2
   Elapsed:               60.07 (mins)
   DB Time:                2.56 (mins)

...

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   97.32    In-memory Sort %:  100.00
            Library Hit   %:   92.17        Soft Parse %:   89.85
         Execute to Parse %:   62.98         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:    0.01     % Non-Parse CPU:   12.17

...

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                    Avg
                                                    wait   % DB
Event                           Waits     Time(s)   (ms)   time Wait Class
----------------------------- ------- ----------- ------ ------ ----------
DB CPU                                         106          68.8
db file sequential read         5,463          24      4   15.4 User I/O
direct path read                2,831          16      5   10.1 User I/O
db file scattered read            184           2      9    1.0 User I/O
control file sequential read    3,594           1      0     .5 System I/O

You've got a lot o information without writing difficult scripts. The other thing is to know how to interpret it ... it's a lifetime story, so learn and good luck!
;-)

Command-line history in SQL*Plus, RMAN, ADRCI

With this tool you can scroll down and up all the command history typed in the above Oracle tools in Linux shell like in Windows.
Download it from: http://utopia.knoware.nl/~hlub/uck/rlwrap, then unpack in e.g. /tmp directory.

#as root
  cd /tmp/rlwrap*
  ./configure
  make
  make install

#run
  su - oracle
  rlwarp sqlplus / as sysdba

To make life easier define these alias in ~/.bashrc:

alias sql='export ORACLE_SID=dwh11r2; rlwrap sqlplus / as sysdba'
alias rman='export ORACLE_SID=dwh11r2; rlwrap rman target /'
alias adr='rlwrap adrci'

That's all.
To run immediately type:

. ~/.bashrc
sql

History matters! ;-)


NOTE
====
As with any code, ensure to test this scripts and hints in a development environment before attempting to run it in production.