Modifying the Listener Port for Database Control

The steps in this section assume you need to change the listener port as well. Since the listener was already created for me here are the steps I had to follow:

1. Stop DB Control by executing the command emctl stop dbconsole

2. Modify the agent to reflect the new port number. Edit $ORACLE_HOME//sysman/emd/targets.xml

The instructions say you only need to modify the listener port value. However, I noticed that there are two entries in this file. One is for the database and another for the listener, each pointing to the default port of 1521. I'm not sure if I needed to change the database port but since the DBA wanted to keep both environments separate I changed it to be safe. I didn't test this procedure without making that change.

Here is a copy of the file (of course username and password values were modified. ;).






















3. Modify $ORACLE_HOME//sysman/config/emoms.properties to reflect the new port. Two entries have to be modified here as well. emdRepPort and emdRepConnectDescriptor should reflect the new port number.

Here are the modified entries with 1522 replacing the standard port 1521.

oracle.sysman.eml.mntr.emdRepPort=1522
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=nitish)(PORT\=1522)))(CONNECT_DATA\=(SERVICE_NAME\=Niti10gdb)))


4. Last but not least start up database console with emctl start dbconsole and verify that you can login successfully.

Monitoring your RMAN backup scripts

There is an easy way to monitor your RMAN backup scripts in a unix environment. I've seen quite a few DBA's log the rman output to a file and then grep it for errors.

Ex.

rman target / CMDFILE /home/oracle/scripts/rman.sql LOG $logfile
errors=`cat $logfile|grep -i error|wc -l`
if test $errors -gt 0
then
mailx -s "BACKUP FAILED" email1@nitish.com, email2@nitish.com <<\!
`cat $logfile`
!
fi

Another method would be to examine the return code from the rman client. Rman returns a value of 0 to the shell if the backup was successful. Otherwise it returns a nonzero value and from what I have seen this is always 1. (Have you seen any other values?)

So another way of writing the above script would be


rman target / CMDFILE /home/oracle/scripts/rman.sql LOG $logfile
status=$?

if [ $status -gt 0 ] ; then
mailx -s "BACKUP FAILED: NITIDB" email1@nitish.com <<\!
`cat $logfile`
\!
else
mailx -s "SuccessfulBackup: NITIDB" email1@nitish.com <<\!
`cat $logfile`
\!
fi

NOTE: Ignore the backlashes before the exclamation points above.

As you can see above, after rman executes I set a status variable. $? is automatically set to the return code from the rman client. After that I check the value and there was an error I send an email saying the backup failed and put the contents of the log file into the body of the message. If the backup was successful, I still send a copy of the logfile.

The reason I do this is because each day I will expect to see either a successful or failed email. I've had issues in the past with cron not executing scheduled jobs (for numerous reasons).

So if I don't receive an email I'll verify the backup manually.

ORA-00600 during adcfgclone.pl dbTier

While cloning a new environment (to me) today I hit the following error:

ORA-00600: internal error code, arguments: [skkxerrp], [skkxdllload],
[SlfFopen], [/u001/CRP1/oracle/crp1db10g/10.2.0/plsql/nativelib/d66/STANDARD__SYS__S__647.so], [No such file or directory], [], [], []

A search of metalink will result in a single hit, NOTE:382767.1. The note mentions two possible solutions:
Setup native PL/SQL before cloning
Modify the rapid clone template file to remove two plsql parameters.
Well, in this case i'm told the production environment has always had pl/sql native compilation, so number 1 didn't apply. If I performed the second potential solution then my newly cloned environment wouldn't be identical to production until I re-setup native compilation.

When I looked at the template file ($ORACLE_HOME/appsutil/template/afinitdb_102.ora), I noticed that by default the parameter plsql_code_type gets reset to INTERPRETED. I'm not sure why this happens, I would have assumed it would be set to the production value of NATIVE. So instead of performing the solutions described in the metalink note I change plsql_code_type to native and re-executed adcfgclone.pl dbTier, which this time completed without error.

ORA-01555 and Automatic Undo

Before you start to troubleshoot you need to gather some information:
If your unaware of the current settings for undo you should check. One way is to use the show parameter command but you can also check the database initialization parameter file or spfile.


SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- -------------
undo_management string AUTO
undo_retention integer 1200
undo_suppress_errors boolean FALSE
undo_tablespace string APPS_UNDOTS1


Determine which time the error occurred from the alert log, which will help you narrow down the time for a query we will use later. You alert log should have an entry similiar to:

Sat Dec 16 23:05:41 2008
ORA-01555 caused by SQL statement below (Query Duration=1506 sec, SCN: 0x0000.a8d6d835):
Sat Dec 16 23:05:41 2011
SELECT X FROM Y WHERE A=B

Now that you the automatic undo settings and the time of the error execute the following query (substitute the timestamp of the error in your alert log in place of the one below, with some buffer at either end):

SQL> select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNDOBLKS, UNXPSTEALCNT, EXPSTEALCNT ,
SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN
from v$undostat
where begin_time between to_date('08/16/2011 22:30:00','MM/DD/YYYY HH24:MI:SS')
and to_date('08/16/2011 23:30:00','MM/DD/YYYY HH24:MI:SS')
order by begin_time;


BEGIN_TIME UNDOBLKS UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT MAXQUERYLEN
---------------- -------- ------------ ----------- ----------- ------------- -----------
08/16/2011 22:32 10394 0 0 0 0 513
08/16/2011 22:42 5693 0 0 0 0 212
08/16/2011 22:52 10853 0 0 0 0 451
08/16/2011 23:02 8950 0 0 0 0 1653
08/16/2011 23:05 11284 0 0 1 0 1506

5 rows selected.


Based on this query we can determine a couple of things such as the number of transactions which required UNDO during the time period. How many undo blocks they required and whether there were any (successful) atempts to steal undo space from other transactions.

The query also shows which transactions received ora-1555 errors (SSOLDERRCNT) and whether or not there was enough space in the UNDO tablespace (NOSPACEERRCNT). If you look at the row above with a timestamp of 23:05, this is the transaction which produced my ora-1555 error. We know this because the SSOLDERRCNT column for this row has the value 1 and the timestamp is the same as the one in the log file. Since the MAXQUERYLEN for this transaction (1506) is greated than the undo_retention (1200) one solution would be to increase undo_retention.*

*There are other ways to solve this problem to try and avoid ora-1555 errors such as transaction tuning, etc. More detail can be found in Note:10630.1

For this particular case I will set it to 2000 and monitor the undo tablespace. By increasing undo_retention undo is kept for a longer period of time, so you should monitor the undo tablespace to make sure you have enough room. Oracle 9i+ has advisors which can help you determine the impact of raising undo_retention.



If the UNDO tablespace was too small, you would see a value in the NOSPACEERRCNT column. Take a look at the following row with a timestamp of 23:22:


BEGIN_TIME UNDOBLKS UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT MAXQUERYLEN
---------------- -------- ------------ ----------- ----------- ------------- -----------
08/16/2011 23:22 1034 10 0 1 1 1428

This row has a values for UNXPSTEALCNT and NOSPACEERRCNT. This means the transaction was not able to obtain space from the UNDO tablespace and attempted to steal 10 blocks (UNXPSTEALCNT) from other transactions. In this case we need to add space to the UNDO tablespace because it was not large enough to meet the undo_retention requirement.

Oracle Enterprise Manager 12c

Total Cloud Control. Reduce downtime by up to 90%. Improve staff productivity by up to 75%. Reduce capital expenditures on servers by 20% or more. Increase IT agility while lowering the cost of managing IT by an order of magnitude.

Oracle Enterprise Manager is Oracle’s integrated enterprise IT management product line, which provides the industry’s only complete, integrated and business-driven enterprise cloud management solution. Oracle Enterprise Manager creates business value from IT by leveraging the built-in management capabilities of the Oracle stack for traditional and cloud environments, allowing customers to achieve unprecedented efficiency gains while dramatically increasing service levels.

The key capabilities of Enterprise Manager include:

A complete cloud lifecycle management solution allowing you to quickly set up, manage and support enterprise clouds and traditional Oracle IT environments from applications to disk.
Maximum return on IT management investment through the best solutions for intelligent management of the Oracle stack and engineered systems with real-time integration of Oracle’s knowledgebase with each customer environment
Best service levels for traditional and cloud applications through business-driven application management

How does one eliminate duplicates rows from a table?

Here's how you remove the duplicate rows before the primary key or unique indexes can be created:

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

Here column1, column2, column3 constitute the identifying key for each record.
Be sure to replace our_table with the table name from which you want to remove the duplicate rows. The GROUP BY is used on the columns that make the primary key for the table. This script deletes each row in the group after the first row.

Killing Oracle Sessions

There are a number of ways to kill rogue sessions both within Oracle and externally.
• Identify the Session to be Killed
• ALTER SYSTEM KILL SESSION
• ALTER SYSTEM DISCONNECT SESSION
• The Windows Approach
• The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@nitishprod.com (TNS V1-V3)
1 23 287 3834 SYS sqlplus@nitishprod.com (TNS V1-V3)
1 40 387 4663 oracle@nitishprod.com (J000)
1 38 125 4665 oracle@nitishprod.com (J001)

SQL>
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
ALTER SYSTEM DISCONNECT SESSION
The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTIONclause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword

SQL>
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.

How to move Non-RAC Env To RAC Env

Step#1
######

Create password files for both RAC instances
--------------------------------------------

orapwd file=orapwnitiprd password=oranitiprd force=y


Step#2
######

Add RAC specific parameters in pfile
------------------------------------
Ex:

*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener='LISTENERS_PROD1’
prod1a.thread=1
prod1a.instance_number=1
prod1a.undo_tablespace='UNDOTBS1'
prod1b.thread=2
prod1b.instance_number=2
prod1b.undo_tablespace='UNDOTBS2'


init.ora FILE:
############################################################################################################################################

nitiprd1.__db_cache_size=1996488704
nitiprd2.__db_cache_size=1996488704
nitiprd1.__java_pool_size=16777216
nitiprd2.__java_pool_size=16777216
nitiprd1.__large_pool_size=16777216
nitiprd2.__large_pool_size=16777216
nitiprd1.__shared_pool_size=520093696
nitiprd2.__shared_pool_size=520093696
nitiprd1.__streams_pool_size=218103808
nitiprd2.__streams_pool_size=218103808
*.audit_file_dest='/oracle/admin/nitiprd/adump'
*.background_dump_dest='/oracle/admin/nitiprd/bdump'
*.cluster_database_instances=2
*.cluster_database=FALSE
*.compatible='10.2.0.3.0'
*.control_files='/oramxp/706/data/data00/control01.ctl','/oramxp/706/index/index00/control02.ctl','/oramxp/706/misc/miscdata/control03.ctl'
*.core_dump_dest='/oracle/admin/nitiprd/cdump'
*.db_16k_cache_size=109715200
*.db_block_size=8192
*.db_cache_size=409715200
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='nitiprd'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nitiprdXDB)'
nitiprd2.instance_number=2
nitiprd1.instance_number=1
*.job_queue_processes=10
*.log_archive_dest_1='location=/oramxp/706/archive/'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=114572800
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sessions=100
*.sga_max_size=2885760000
*.sga_target=2885760000
*.streams_pool_size=209715200
nitiprd2.thread=2
nitiprd1.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
nitiprd2.undo_tablespace='UNDOTBS2'
nitiprd1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/nitiprd/udump'

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


Step#3
######

Start up the instance if it is not already started and issue the following statement to create your spfile on your raw partition:
---------------------------------------------------------------------------------------------------------------------------------

create spfile='/oramxp/706/misc/miscdata/spfilenitiprd.ora' from pfile='/oracle/product/10g/dbs/initnitiprd.ora';


For easy manageability you can create an initSID.ora file in your local $ORACLE_HOME/dbs directory that points to your spfile:
------------------------------------------------------------------------------------------------------------------------------

initnitiprd1.ora
#################

spfile=/oramxp/706/misc/miscdata/spfilenitiprd.ora

initnitiprd2.ora
#################


spfile=/oramxp/706/misc/miscdata/spfilenitiprd.ora


Step#4
######

Create RAC Data Dictionary Views.
---------------------------------

Create the RAC data dictionary views on the first RAC instance.

SQL> !echo $ORACLE_SID
nitiprd1

SQL> spool /tmp/catclust.log
SQL> @$ORACLE_HOME/rdbms/admin/catclust
...
...
...
SQL> spool off
SQL> shutdown immediate;

Step#5
######

Register the RAC instances with CRS.
------------------------------------

srvctl add database -d nitiprd -o '/oracle/product/10g/'
srvctl add instance -d nitiprd -i nitiprd1 -n u060mxmd11
srvctl add instance -d nitiprd -i nitiprd2 -n u060mxmd12
srvctl start instance -d nitiprd -i nitiprd1


u060mxmd11:/home/oracle>srvctl start instance -d nitiprd -i nitiprd1
PRKP-1001 : Error starting instance nitiprd1 on node u060mxmd11
CRS-0215: Could not start resource 'ora.nitiprd.nitiprd1.inst'.


SQL> startup
ORACLE instance started.

Total System Global Area 2902458368 bytes
Fixed Size 2086904 bytes
Variable Size 771753992 bytes
Database Buffers 2113929216 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 nitiprd1
u060mxmd11
10.2.0.4.0 03-DEC-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


Step#6
######

Create redo logs for the second RAC instance.
---------------------------------------------

alter database add LOGFILE thread 2
GROUP 4 ('/oramxp/706/misc/redo/nitiprd/redo706_04a.log','/oramxp/706/misc/redo/nitiprd/redo706_04b.log') SIZE 500M,
GROUP 5 ('/oramxp/706/misc/redo/nitiprd/redo706_05a.log','/oramxp/706/misc/redo/nitiprd/redo706_05b.log') SIZE 500M,
GROUP 6 ('/oramxp/706/misc/redo/nitiprd/redo706_06a.log','/oramxp/706/misc/redo/nitiprd/redo706_06b.log') SIZE 500M;


Enable Thread No# 2
-------------------

SQL> alter database enable thread 2;

Database altered.


Rename Logfiles for Thread # 1
------------------------------


SQL> alter database rename file '/oramxp/706/data/redo01.log' to '/oramxp/706/misc/redo/nitiprd/redo706_01a.log';

Database altered.

SQL> alter database rename file '/oramxp/706/data/redo02.log' to '/oramxp/706/misc/redo/nitiprd/redo706_02a.log';

Database altered.

SQL> alter database rename file '/oramxp/706/data/redo03.log' to '/oramxp/706/misc/redo/nitiprd/redo706_03a.log';

Database altered.


Add Log Member to Log Groups
----------------------------


SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_01b.log' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_02b.log' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_03b.log' TO GROUP 3;

Database altered.


SQL> select group#, member from v$logfile;

GROUP# MEMBER
---------- -----------------------------------------------------------------
1 /oramxp/706/misc/redo/nitiprd/redo706_01a.log
2 /oramxp/706/misc/redo/nitiprd/redo706_02a.log
3 /oramxp/706/misc/redo/nitiprd/redo706_03a.log
4 /oramxp/706/misc/redo/nitiprd/redo706_04a.log
4 /oramxp/706/misc/redo/nitiprd/redo706_04b.log
5 /oramxp/706/misc/redo/nitiprd/redo706_05a.log
5 /oramxp/706/misc/redo/nitiprd/redo706_05b.log
6 /oramxp/706/misc/redo/nitiprd/redo706_06a.log
6 /oramxp/706/misc/redo/nitiprd/redo706_06b.log
1 /oramxp/706/misc/redo/nitiprd/redo706_01b.log
2 /oramxp/706/misc/redo/nitiprd/redo706_02b.log
3 /oramxp/706/misc/redo/nitiprd/redo706_03b.log

12 rows selected.



Run following command:
----------------------
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)


SQL> alter system set thread=2 scope=spfile sid='nitiprd2';

System altered.


Shutdown and startup the Node#1
-------------------------------


Step#7
######

Create undo tablespace for the second RAC instance
--------------------------------------------------


SQL> create undo tablespace UNDOTBS2 datafile '/oramxp/706/undo/undotbs02.dbf' size 20G;

Tablespace created.


SQL> select tablespace_name, file_name from dba_data_files where tablespace_name=’UNDOTBS2’;

TABLESPACE FILE_NAME
--------------------- --------------------------------------
UNDOTBS2 /oramxp/706/misc/undotbs02.dbf






SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

System altered.




Step#8
######

Start the second instance.


SQL> startup nomount
ORACLE instance started.

Total System Global Area 2902458368 bytes
Fixed Size 2086904 bytes
Variable Size 771753992 bytes
Database Buffers 2113929216 bytes
Redo Buffers 14688256 bytes
SQL> alter database mount;

Database altered.



SQL> alter system set instance_number=2 scope=spfile;

System altered.



SQL> alter database open;

Database altered.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 nitiprd2
u060mxmd12
10.2.0.4.0 03-DEC-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

RMAN-06059: expected archived log not found

RMAN attempted to backup an archive log file, but couldn't find it.

This can happen for a variety of reasons; the file has been manually moved or deleted, the archive log destination has recently been changed, the file has been compressed, etc.


Starting backup
current log archived
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 14:24:55
RMAN-06059: expected archived log not found, lost of archived log
compromises recoverability
ORA-19625: error identifying file /u02/oradata/scr9/archive/1_1160.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:

change archivelog all crosscheck;
It is advisable to perform a full backup of the database at this point.

When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though.

RPM Tip - Determining the architecture of an installed package.

As many of you are aware, one of the first steps prior to installing Oracle is to determine which operating system packages are required. On 64bit systems you are required to have both the 32bit and 64bit versions installed for some.

For example, one such required package is libstdc++-devel. If you execute rpm with the -qa options (q for query mode, a for all installed packages) you will see the following if it is installed:



[oravis@nitish.oracle.com~]$ rpm -qa | grep libstdc++-devel
libstdc++-devel-3.4.6-10

If your on a 64bit OS how do you know if this is the 32bit or 64bit version? You need specify a query format in the rpm command. In the following example I extract the name, version, release and architecture information for the libstdc++-devel package.


[oravis@nitish.oracle.com ~]$ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}.rpm \
%{INSTALLTIME:date}\n" | grep libstdc++
libstdc++-devel-3.4.6-10_x86_64.rpm Mon 06 Oct 2008 07:46:50 PM EDT
libstdc++-devel-3.4.6-10_i386.rpm Mon 06 Oct 2008 07:46:50 PM EDT

So based on this output I have both of the required packages and I can quickly identify which package I need to install if one is missing. Note: I always pipe rpm to grep because if you don't specify the package name properly you won't get any results. ie:


[oravis@nitish.oracle.com ~]$ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}.rpm \
%{INSTALLTIME:date}\n" libstdc
[oravis@nitish.oracle.com ~]$

11i and Listener Passwords

A standard practice for any Oracle database should be to enable passwords on your listeners. EBS is no exception and recently Oracle published a metalink note: 386374.1 on how to do this for 11.5.x environments.

There are some pre-reqs, specifically:

3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher

The note has detailed steps on how to set the password at the application and database tiers.

In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:

TNS-01169: The listener has not recognized the password

Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.



#!/usr/bin/perl -w

if ($#ARGV !=1) {
die "Usage: check_alert.pl ex. hostname /var/opt/oracle/listener.log.\n";
}
sleep 2;

$interval=60; # How many seconds before we check to see if data has been written to the logfile;
$email_threshold=5; # How many errors within the interval before an email gets sent;
$hostname=$ARGV[0];
$file=$ARGV[1];
open(filePtr, $file) or die "Can't find $file\n";


for (;;) {
@errors=("Subject: Listener Password Errors for $hostname\n");
$currTime = localtime(time);
push(@errors,"Here are some errors found at $currTime for $hostname.\n");

while () {
chop $_;
if (/TNS-01169/) {
push(@errors, "$_\n");
}
}

if ($#errors > $email_threshold) {
$rndExt = time;
$rndFile = "alert_errors_$rndExt";
open (TMPFILE, ">/tmp/$rndFile");

foreach $error (@errors) {
print TMPFILE $error;
}
close(TMPFILE);
system ("mail username\@mycompany.com < /tmp/$rndFile");
system ("rm /tmp/$rndFile");
}

sleep $interval;
seek filePtr, 0, 1;
}



The script takes two parameters:

A couple of key things you may want to customize in the above script are the $intervaland $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.

R12: Sometimes cloning doesn’t remove all source references

I’m in the process of applying the latest round of security patches to our environment. While patching NITISH (I’ve already applied it to a sandbox and DEV environment), I hit the following error:

OPatch found the word "error" in stderr, please look into it.
You may have to run the make again.
Stderr Output (from /var/NITISH/apps/tech_st/10.1.2/.patch_storage/12828135/make_local.stderr):
/usr/bin/ld: cannot find –ljava
collect2: ld returned 1 exit status
make[1]: *** [/u01/NITISH/apps/tech_st/10.1.2/sysman/lib/libnmemso.so] Error 1
make: *** [libnmemso] Error 2

The issue was with the same file:

$ORACLE_HOME/sysman/lib/env_sysman.mk

The following line was still pointing to the source system:


JRE_LIB_DIR=/u01/PROD/apps/tech_st/10.1.2/jdk/jre/lib/i386

I changed it to NITISH, reran the patch and it completed successfully. Just a reminder that cloning doesn’t update all the necessary files.

ORA-29283: invalid file operation

Error starting at line 1 in command:
BEGIN
DECLARE
file_name VARCHAR2(50);
file_handle utl_file.file_type;
BEGIN
file_name := 'table.txt';
file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
SELECT column 1
||'~'||column 2
||'~'||column 3 out_line
FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;

Error report:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7
29283. 00000 - "invalid file operation"

Cause: An attempt was made to read from a file or directory that does
not exist, or file or directory access was denied by the
operating system.

Action: Verify file and directory access privileges on the file system,
and if reading, verify that the file exists.


We had migrated our servers from standalone to RAC and sometimes they worked sometimes not.

It was because we were not using the shared location for utl directory.

Once we put the files on shared location,it started working.

Recovery from RMAN cold Backup

The RMAN documentation differentiates between "inconsistent" and "consistent" backups on the basis of whether the database is OPEN or not (respectively) during the Backup.
The nomenclature "inconsistent backup" makes me nervous. Why not call it the good old "HOT" Backup ?

To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the controlfiles. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.
RMAN does not backup the Online Redo Log files. With an OS scripted backup, you had the option -- you could choose to include these files in your backup if you were careful about how you planned to use Cold Backups for Roll-Forward recoveries with ArchiveLog.

Since RMAN does not backup the Online Redo Log files, you must, perforce, OPEN RESETLOGS on a Restore. With a scripted backup, if you also included your Online Redo Log files in your backup and restore (provided that you did not plan to apply any ArchiveLogs), you could simply STARTUP the database and continue LogSequenceNumbers again. (Of course, you might be duplicating LogSequenceNumbers if the database had been active in ArchiveLog mode since the backup, so you have to be careful to distinguish the two "streams" of ArchiveLogs).


Here below is the simplest "consistent" Backup and Restore using RMAN :



C:\>rman

Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:20:17 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 536870912 bytes

Fixed Size 1291652 bytes
Variable Size 297798268 bytes
Database Buffers 234881024 bytes
Redo Buffers 2899968 bytes

RMAN> backup database;

Starting backup at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=58 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\NITISH10G\SYSTEM01.DBF
input datafile fno=00003 name=C:\NITISH10G\SYSAUX01.DBF
input datafile fno=00002 name=F:\NITISH10G\TEST_TBS_01.DBF
input datafile fno=00004 name=C:\NITISH10G\USERS01.DBF
input datafile fno=00005 name=C:\NITISH10G\EXAMPLE01.DBF
input datafile fno=00007 name=C:\NITISH10G\UNDO.DBF
channel ORA_DISK_1: starting piece 1 at 08-MAY-08
channel ORA_DISK_1: finished piece 1 at 08-MAY-08
piece handle=C:\NITISH10G_FLASH\NITISH10G\BACKUPSET\2011_05_08\O1_MF_NNNDF_TAG200
0508T222041_42631X6C_.BKP tag=TAG20110508T222041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:47
Finished backup at 08-MAY-08

Starting Control File and SPFILE Autobackup at 08-MAY-08
piece handle=C:\NITISH10G_FLASH\NITISH10G\AUTOBACKUP\2011_05_08\O1_MF_S_654214767
426358M1_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-08

RMAN>
RMAN> shutdown

database dismounted
Oracle instance shut down

RMAN>

******************* BACKUP COMPLETED *****************
******************************************************

========= database files deleted ====================
++++++++++++++++++++++++++++++++++++++++++++++++++++++
======================================================



C:\>rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:32:07 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 536870912 bytes

Fixed Size 1291652 bytes
Variable Size 301992572 bytes
Database Buffers 230686720 bytes
Redo Buffers 2899968 bytes

RMAN>
RMAN> restore controlfile from autobackup;

Starting restore at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK

recovery area destination: \NITISH10G_FLASH
database name (or database unique name) used for search: NITISH10G
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\NITISH10G_FLASH\NITISH10G\AUTOBACKUP\2011
_05_08\O1_MF_S_654214767_426358M1_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\NITISH10G\CONTROL01.CTLa
output filename=C:\NITISH10G\CONTROL02.CTL
output filename=C:\NITISH10G\CONTROL03.CTL
Finished restore at 08-MAY-08

RMAN>
RMAN> restore database;

Starting restore at 08-MAY-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/08/2011 22:33:28
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>
RMAN> restore database;

Starting restore at 08-MAY-08
Starting implicit crosscheck backup at 08-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK
Crosschecked 45 objects
Finished implicit crosscheck backup at 08-MAY-08

Starting implicit crosscheck copy at 08-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-MAY-08

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\NITISH10G_FLASH\NITISH10G\AUTOBACKUP\2011_05_08\O1_MF_S_654214767_42
6358M1_.BKP

using channel ORA_DISK_1

skipping datafile 2; already restored to file F:\NITISH10G\TEST_TBS_01.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\NITISH10G\SYSTEM01.DBF
restoring datafile 00003 to C:\NITISH10G\SYSAUX01.DBF
restoring datafile 00004 to C:\NITISH10G\USERS01.DBF
restoring datafile 00005 to C:\NITISH10G\EXAMPLE01.DBF
restoring datafile 00007 to C:\NITISH10G\UNDO.DBF
channel ORA_DISK_1: reading from backup piece C:\NITISH10G_FLASH\NITISH10G\BACKUPS
ET\2011_05_08\O1_MF_NNNDF_TAG20110508T222041_42631X6C_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\NITISH10G_FLASH\NITISH10G\BACKUPSET\2011_05_08\O1_MF_NNNDF_TAG2011
0508T222041_42631X6C_.BKP tag=TAG20110508T222041
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAY-08

RMAN>
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/08/2011 22:36:48
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>
RMAN> alter database open resetlogs;

database opened

RMAN>

Upgrade Oracle to 11.1.0.7

My only 11G instance was running 11.1.0.6 and needed to be upgrade it to 11.1.0.7 to fix a couple of bugs. Apparently this patchkit also includes some new functionality. Before starting the upgrade I needed to do a bit of housekeeping. This instance is relatively small so I performed a full database backup as well as a full database export. I also created a pfile for good measure.

create pfile from spfile ;

I like to have a list of invalid objects to I can compare the before and after.

select object_name, owner, object_type
from all_objects where status like ‘INVALID’;

Next I checked the prerequisites for this upgrade. The time_zone check is a very important check that needs to be made.

select version from v$timezone_file;

I am running version 4 so I am okay to upgrade. Check out MetaLink Note 568125.1 if you are running anything other than version 4. Next I ran the script utlu111i.sql in the $OH/rdbms/admin directory. It is designed to let you know if there are issues to address prior to the upgrade. Next I shutdown my Oracle instance and performed the upgrade through the GUI installer. All went well. Then it was time to upgrade my instance.

sqlplus /nolog
startup upgrade
spool upgrade.log
@?/rdbms/admin/catupgrd.sql
spool off

This process took about 90 minutes to run so plan enough time for your upgrade. The script was run without errors. The final step of the script shutdown the database. I started the database normally, then shut it down normally, then started it up again normally. I generally take these extra steps just to ensure everything looks okay. To check the post-upgrade status this script is helpful

@?/rdbms/admin/utlu111s.sql

Next I needed to fix some invalid object which I did with this script.

@?/rdbms/admin/utlrp.sql

The final step to the upgrade process is to take a full database backup. Now I am fully upgraded to the latest version of Oracle. To check my version I ran.

select * from v$version
And then
select comp_name,status,version from dba_registry;

How to Install Oracle Enterprise Management Agent using silent install

Operating System: Solaris 10 SPARC 64 bit
Agent Version: 11.1.0.1.0

Where to download software:

Go to http://www.oracle.com/technetwork/oem/grid-control/downloads/agentsoft-090381.html
Accept the License Agreement
Under “Agent Software 64-bit platform” click on the “+” before Sun SPARC Solaris
Download the 11.1.0.1.0 Management Agent software
Transfer the zip file to the remote server where you intend to perform the installation.
Create a directory /home/oracle/em_agent_install in the remote server
Move the agent software zip file to the /home/oracle/em_agent_install
Unzip the downloaded agent software zip file on remote server

Editing Response File:

Go to

cd /home/oracle/em_agent_install/solaris/response
Make a backup of the additional_agent.rsp file

cp additional_agent.rsp additional_agent_backup.rsp
Edit the the following fields in the response file. For OMS_HOST make sure to set appropriate hostname where you have installed the Oracle Management Service. If you have chosen secure agent connections then set the “AGENT_REGISTRATION_PASSWORD” parameter. Also make sure to change the OMS_PORT value if you changed it during the Oracle Management Service (OMS) installation.

The parameter ORACLE_AGENT_HOME_LOCATION defines the location for the agent installation. A directory agent11g gets created under the path defined in the ORACLE_AGENT_HOME_LOCATION. If you want to install it in a different location, change this parameter.

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
INSTALL_UPDATES_SELECTION="skip"
ORACLE_AGENT_HOME_LOCATION="/opt/app/oracle/"
b_silentInstall=true
OMS_HOST="YOUR_OMS_HOST.YOUR_DOMAIN.com"
OMS_PORT="4889"
AGENT_REGISTRATION_PASSWORD="YOUR_REGISTRATION_PASSWORD"
FROM_LOCATION="/home/oracle/em_agent_install/solaris/agent/stage/products.xml"

Leave the rest of the parameters as-it-is and Save the file and exit the editor.

Silent Installation:

Go to

cd /home/oracle/em_agent_install/solaris/agent

Run the following command:

./runInstaller -silent -responsefile /home/oracle/em_agent_install/solaris/response/additional_agent.rsp

How to Speed up ASM Disks Rebalancing Operation

How to Speed up ASM Disks Rebalancing Operation:

When I tried to drop two ASM disks (each ~ 100GB) from the ASM DISKGROUP I have noticed that the rebalance operation is taking more than an hour by querying the v$asm_operation.

SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;

Here is the ouput from the above command:

GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
2 REBAL RUN 1 62

The total size of the diskgroup before dropping the two disks of interest is 500 GB of which 250 GB is free. So I decided to drop two ASM disks of each ~ 100 GB using ASMCA.

After doing little research, I altered the rebalance power for the diskgroup using the following command:

alter diskgroup DISKGROUP rebalance power 5;
The rebalance operation started again, this time the estimated time came down to about 15 minutes.

SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;

Here is the output from the above

GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
2 REBAL RUN 5 15

Until the rebalance operation is finished, you shouldn’t touch the underlying physical disks.

How to Apply Patch 12827731 (Rolling Fashion) – 11.2.0.2.4 Grid Patch Set Update

How to Apply Patch 12827731 – 11.2.0.2.4 Grid Patch Set Update

12827731 Grid Infrastructure Patch Set Update is a combination of patch numbers 12827731 and 1282776. This patch can be applied in a rolling fashion.


In order to apply the patch, the opatch utility version must be 11.2.0.1.5 or later.

How to check and update the version of opatch utility?

Assuming that you have the appropriate version of the opatch utility

Download patch p12827731_112020_Linux-x86-64.zip from support.metalink.com
Transfer the zip file to the server where you intend to apply the patch [Note: I created a directory /oratemp01/11202/software and transferred the zip file to this location]
cd to the patch directory and unzip the patch file

cd /oratemp01/11202/software
unzip p12827731_112020_Linux-x86-64.zip
You will notice that the file extracts into two sub-directories 12827731 and 12827726

First set the appropriate environment.

export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
I ran the prereq check using the following command to determine whether any currently installed one-off patches conflict with the current PSU patch

opatch prereq CheckconflictAgainstOHWithDetail -phBaseDir ./12827731


Generating OCM Response File:

During the Installation of the Patch you will be prompted to enter the path for the Oracle Configuration Manager (OCM) Response file. Here is how to generate it.

export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

cd $ORACLE_HOME/OPatch/ocm/bin
./emocmrsp
You will be prompted to enter you oracle support email address and password, if you wish to be informed about Oracle Securuity meastures enter them and hit enter, if not just hit enter. A response file gets created at /u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp

Procedure to Apply the Patch to Non-Shared Grid Infrastructure HOME and Non-Shared RDBMS HOME:

I am going to use opatch auto command to apply the patch first to a Non-shared Grid Infrastructure HOME and then to a Non-Shared Oracle RDBMS HOME

Shut down all the databases running under the to be patched 11.2.0.2.x Grid Home (Remeber ASM for 11gR2 runs under the Grid Infrastructure HOME)

Step-1: Apply the Patch to the Non-Shared Grid Infrastructure HOME

First Set the appropriate environment variables

export ORACLE_HOME=/u01/app/11.2.0.2/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
Don’t shutdown the clusterware

Go to the patch directory

cd /oratemp01/11202/software
Issue the following command as root user or some one who has sudo privileges

sudo opatch auto -oh $ORACLE_HOME

[oracle@xyz software]$ sudo opatch auto -oh $ORACLE_HOME
Executing /usr/bin/perl /u01/app/11.2.0.2/grid/OPatch/crs/patch112.pl -patchdir /oratemp01/11202 -patchn software -oh /u01/app/11.2.0.2/grid -paramfile /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/11.2.0.2/grid/OPatch/crs/../../cfgtoollogs/opatchauto2011-11-16_14-33-59.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
Successfully unlock /u01/app/11.2.0.2/grid
patch /oratemp01/11202/software/12827731 apply successful for home /u01/app/11.2.0.2/grid
patch /oratemp01/11202/software/12827726 apply successful for home /u01/app/11.2.0.2/grid
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4123: Oracle High Availability Services has been started.

Do a opatch lsinventory to check if the patch has been applied to the 11.2.0.2.x GRID Infrastructure HOME

opatch lsinventory
Step-2: Apply the Patch to the 11.2.0.2.x Non-Shared RDBMS HOME

As mentioned earlier, in order to apply 12827731 patch the opatch utility version should be higher than 11.2.0.1.5.

How to check and update the version of opatch utility?

Shutdown all the databases associated with the 11.2.0.2.x Non-Shared RDBMS HOME

Set the appropriate environment variables

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db11202_r
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
Enter the following command as root user or someone with sudo privileges

sudo opatch auto -oh $ORACLE_HOME

When prompted enter the full path to the OCM (oracle configuration manager) response file generated in the Step-1

Here is the update from the above command:

[oracle@xyz software]$ sudo opatch auto -oh $ORACLE_HOME
[sudo] password for oracle:
Executing /usr/bin/perl /u01/app/oracle/product/11.2.0/db11202_r/OPatch/crs/patch112.pl -patchdir /oratemp01/11202 -patchn software -oh /u01/app/oracle/product/11.2.0/db11202_r -paramfile /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/oracle/product/11.2.0/db11202_r/OPatch/crs/../../cfgtoollogs/opatchauto2011-11-16_14-49-41.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0.2/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/app/11.2.0.2/grid/OPatch/ocm/bin/ocm.rsp
patch /oratemp01/11202/software/12827731/custom/server/12827731 apply successful for home /u01/app/oracle/product/11.2.0/db11202_r
patch /oratemp01/11202/software/12827726 apply successful for home /u01/app/oracle/product/11.2.0/db11202_r

Do a opatch lsinventory to check if the patch has been applied to the 11.2.0.2.x RDBMS HOME

opatch lsinventory

Perform similar steps on the remaining nodes one after another (rolling fashion)

Startup all the databases, associated with the patched RDBMS HOME

For each database set ORACLE_SID variable and load modified sql files into the database. Perform these steps only from one node.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db11202_r
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
At the sql prompt enter the following commands:

@?/rdbms/admin/catbundle.sql psu apply
QUIT

Block Developers from Different TOOLS

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/
SHOW ERRORS

ORA-06553: PLS-801: internal error [56319]

Few things to do

1) Running utlirp.sql (this is going to invalidate most (for me around 80,000 objects) and then recompiles them again) Took a helluva time.. but got it sorted out. in the end.

2) Had to run utlirp.sql when database is in migration mode. (remember I ported the db from 32 bit to 64 bit).

I am sure that this "IS A" solutions as now, I am getting the same error with 32bit version of oracle software. However with 64 bit I am fine. now. before this the error was with 64 bit and 32 bit was clean.
-------------- Other Scenario---------------------------------------------------------
Worked great, I had to restore a 32 bit database on a 64 bit system. After restore/recover operations with RMAN I did the following.

1) startup upgrade;
2) @?/rdbms/admin/utlirp.sql
3) shutdown immediate;
4) startup;

------------Other Scenario-----------------------------------------------------

Received the "ORA-06553: PLS-801: internal error [56319]" error after cloning a db, while trying to drop a user.

SQL> drop user QCSITEADMIN_DB0 cascade;
drop user QCSITEADMIN_DB0 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56319]

and it got resolved after executing "utlirp.sql".

---------------------------------------------------My scenario -----------------

SYS @ avco >>conn volume/trace

ERROR:

ORA-06553: PLS-801: internal error [56327]

Error accessing package DBMS_APPLICATION_INFO

Connected.

VOLUME @ avco >>

VOLUME @ avco >>@utlirp.sql

VOLUME @ avco >>WHENEVER SQLERROR EXIT;

VOLUME @ avco >>DOC

DOC>#######################################################################

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if there the database was not opened in UPGRADE mode

DOC>

DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and

DOC> re-execute utlirp.sql

DOC>#######################################################################

DOC>#######################################################################

DOC>#

VOLUME @ avco >>SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

2 WHERE status != 'OPEN MIGRATE';

SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

*

ERROR at line 1:

ORA-00942: table or view does not exist



Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production

With the Automatic Storage Management option

[oracle@VOL-ORATEST admin]$ s

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 24 12:35:48 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Release 11.2.0.1.0 - Production

With the Automatic Storage Management option

SYS @ avco >>shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS @ avco >>startup upgrade

ORACLE instance started.



Total System Global Area 246910976 bytes

Fixed Size 1335752 bytes

Variable Size 192941624 bytes

Database Buffers 50331648 bytes

Redo Buffers 2301952 bytes

Database mounted.

Database opened.

SYS @ avco >>@utlirp.sql

......................

.........................

............................

PL/SQL procedure successfully completed.



SYS @ avco >>

SYS @ avco >>DOC

DOC>#######################################################################

DOC>#######################################################################

DOC> utlirp.sql completed successfully. All PL/SQL objects in the

DOC> database have been invalidated.

DOC>

DOC> Shut down and restart the database in normal mode and run utlrp.sql to

DOC> recompile invalid objects.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SYS @ avco >>

SYS @ avco >>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS @ avco >>startup

ORACLE instance started.



Total System Global Area 246910976 bytes

Fixed Size 1335752 bytes

Variable Size 192941624 bytes

Database Buffers 50331648 bytes

Redo Buffers 2301952 bytes

Database mounted.

Database opened.

SYS @ avco >>

SYS @ avco >>connect volumexx/xxxxx

Connected.

VOLUME @ avco >>