Step for doing Oracle Apps Upgrade from 11i to R12/12i (12.0.4)

Upgrade Requirements :

- You can upgrade to R12/12i (12.0.4) ONLY from release 11.5.8, 11.5.9.X or 11.5.10.X (For any previous release like 11.5.7 , upgrade them to 11.5.10.2 first)

- You can upgrade database to version 10.2.X during upgrade (11.5.8, 11.5.9.X, 11.5.10.X) or BEFORE R12 upgrade (only for 11.5.9.X and 11.5.10.X)

.
Upgrade is broadly divided in to three steps

1. Preupgrade Step (lay down file system for R12 using rapidwiz/RapidInstall)
2. Upgrade Step (Upgrade database to 10gR2 (if not already upgraded), apply R12.AD.A on R12 APPL_TOP and apply 12.0.4 patch database portion only)
3.Post Upgrade step (Run rapidwiz/RapidInstall one more time to configure and start services on R12)

.

A. Pre-Upgrade Process
1.Layout file system for R12 using rapidwiz with option as upgrade.

Run rapidwiz from R12 software >> Select option “Upgrade to Oracle Applications Release 12“  (Check Figure)

In Select Upgrade Action screen select “Create Upgrade File System” (Check Figure)

Follow further screens and installer will install R12 file system (This step will NOT configure and start services in R12, It will only install R12 files)

Note : There are other preupgrade steps as well like migrate backup, TUMS (optional), OATM, convert to multi org, gather stats and functional preupgrade steps

B. Upgrade Process
2.Migrate database to 10gR2 – This step is required if you have not upgraded your database with 11i itself. Use ORACLE_HOME (10.2 on database tier) which came as part of R12 upgrade (Create Upgrade file system above in preupgrade step)

 Also check following Metalink Notes

403339.1 – Oracle 10g R2 Database preperation Guidelines for Oracle E-Business Suite R12 Upgrade
396009.1 - Database initialization parameters for Oracle apps R12

3. Put your Apps into database in to maintenance mode
4.Run adpatch 6604842 (on R12 appl_top, environment variable at this stage should point to R12 APPL_TOP and ORACLE_HOME in application tier should point to FORMS (10.1.2) in R12) to upgrade AD product of R12 to 12.0.4 (Patch 6604842 is available as R12.AD.A)

5. Run 12.0.4 patch 6394500(u6394500.drv ) which is under $AU_TOP/patch/115/driver (on R12/12.0.4 APPL_TOP). 

Note:  You have to apply this patch on 12.0.4  APPL_TOP.

Apply this with nocopyportion and nogenerateportionas copy and generate portion is already available as part of R12 install done above in preupgarde step.

Apply/Upgrade to 12.0.4 using

adpatch options=nocopyportion,nogenerateportion

6.  Disable maintenance mode

C. Post Upgrade Process
Database and database listener should be up at this stage.

7. Run Rapid Install one more time (with different option check figure) to configure and start services on R12/12i.

Run rapidwiz one more time
Select option “Upgrade to Oracle Applications Release 12“  (Check Figure)

In Select Upgrade Action screen select “Configure Upgraded Release 12 Instance” (This select is different from selection in Pre-Upgrade Step)

  Change file name as shown in figure to your new $APPL_TOP/admin/$CONTEXT_NAME.xml (R12)

There are other finishing tasks which are out of scope of this post like backup, reset password, setup client machines (java plugin), printers, workflow notification mailer and various functional steps

Next we will see how to do R12 upgrade from 12.1.2 to 12.1.3 version.

Cloning Principle & What changed in Rapid Clone from 11i to R12/12i

Cloning Principle

1. Cloning is creating replica of your source instance (PRODUCTION) to target (DEVLOPMENT) so that patch level & data in target instance is same as source instance.

2. Overview of cloning steps

2.1 Ensure you are autoconfig enabled on both Application Tier & Database tier (This is for Rapid Clone)
2.2 Maintain snapshot is executed at least once after any patches(tech stack) or database changes(adding new tablespace)
2.3 Run Preclone on DB Tier (on Source )
2.4 Run Preclone on Apps Tier (on Source)
2.5 Copy source instance file system to target instance
2.6 Run postclone/config on DB Tier (on Target Instance)
2.7 Run postclone/config on Apps Tier (on Target Instance)
2.8 Update Profile option (server, responsibility, user level), Printer, workflow config settings

What changed in Rapid Clone from 11i to R12/12i

1. adpreclone.pl script in 11i is in $COMMON_TOP/admin/scripts/$CONTEXT_NAME where as same perl script in R12/12i is in $INST_TOP/admin/scripts

2. Files to copy, If target instance pre-exist

—In 11i you copy $APPL_TOP, $OA_HTML, $OA_JAVA, $OA_JRE_TOP, $COMMON_TOP/util, $COMMON_TOP/clone, 806 & iAS ORACLE_HOME, Database ORACLE_HOME & DB_TOP
—In R12/12i you copy $APPL_TOP, $COMMON_TOP, Forms & Reports ORACLE_HOME (10.1.2), Web IAS_ORACLE_HOME (10.1.3) , Database ORACLE_HOME & DB_TOP 

3. Clone Configuration Script on target instance (adcfgclone.pl in both 11i & R12, 12i is in same location $COMMON_TOP/clone/bin)

4. There is no change in clone script location at Database Tier in 11i or R12/12i

Oracle Apps R12 - Output Post Processor OPP

Concurrent processing uses the Output Post Processor (OPP) to enforce post-processing actions for concurrent requests.

Post-processing actions are actions taken on concurrent request output. An

example of a post-processing action is that used in publishing concurrent

requests with XML Publisher.

For example, say a request is submitted with an XML Publisher template

specified as a layout for the concurrent request output. After the concurrent

manager finishes running the concurrent program, it will contact the OPP

to apply the XML Publisher template and create the final output.

A concurrent manager contacts an available OPP process when

a running concurrent request needs an OPP processing action.

A concurrent manager uses a local OPP process (that, is, on the same node) by default, but will choose a remote OPP if no local OPP process is available.

There should always be at least one OPP process active in the system.

If no OPP service is available, completed requests that require OPP processing will complete with a status of Warning.

An OPP service is multi-threaded and will start a new thread for each concurrent request it processes. You can control the number of simultaneous threads for an OPP service instance by adjusting the Threads per Process parameter for the instance.

If all the OPP services have reached their respective maximum number of threads, the requests waiting to be processed remain in a queue to be processed as soon as threads become available.

If request throughput has become slow, you may want to increase the number of threads per process for the OPP. It is recommended that you keep the number of threads per process between 1 and 20.

Concurrent Managers

Concurrent Manager as name suggest is Manager, which manages your concurrent requests in Oracle Apps 11i. You can assign single request or batch job & let concurrent manager manage these request.

The Master Concurrent Managers

There is a lot of talk about “the” concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are “super” Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:

• Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.
• Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
• Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules.

However, the ICM can be configured to take over the CRM’s job to resolve incompatibilities.

How to Start/Stop Concurrent Manager – In Oracle Applications 11i as you might already be aware that all startup shutdown scripts in 11i(11.5.10) are in $OAD_TOP/admin/script/$CONTEXT_NAME where $OAD_TOP is also called as $COMMON_TOP and $CONTEXT_NAME is your SID_hostname, adcmctl.shwhere ad (In my view) is application dba, cm is concurrent manager & ctl means control. When you run adcmctl.sh it records execution of this command in text file with name adcmctl.txt at directory $COMMON_TOP/admin/log/$CONTEXT_NAME. adcmctl.sh called another script startmgr which in turn callsbatchmgr in $FND_TOP/bin and this is actual file which fire up Internal Concurrent Manager Process. Where do concurrent request or manager logfiles and output files go?

The concurrent manager first looks for the environment variable $APPLCSF. If this is set, it creates a path using two other environment variables: $APPLLOG and $APPLOUT.

It places log files in $APPLCSF/$APPLLOG, output files go in

$APPLCSF/$APPLOUT

So for example, if you have this environment set:

$APPLCSF = /u01/appl/common

$APPLLOG = log

$APPLOUT = out

The concurrent manager will place log files in /u01/appl/common/log, and output files in /u01/appl/common/out

If $APPLCSF is not set, it places the files under the product top of the application associated with the request. For example, a PO report would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT.

How to findout whether concurrent manager is running through backend?

Findout whether concurrent manager is running or not, by using “ps -ef | grep FNDLIBR”

(FND Foundation, LIB Library).

What are the logfile and output file naming conventions?

Request logfiles: l<request id>.req

Manager logfiles:

ICM logfile: Default is std.mgr, can be changed with the mgrname

startup parameter

Concurrent manager log: w<XXXXXX>.mgr

Transaction manager log: t<XXXXXX>.mgr

Conflict Resolution manager log: c<XXXXXX>.mgr

Where: <XXXXXX> is the concurrent process id of the manager.

Can I delete a concurrent manager?

No. You can disable the manager by checking the ‘Enabled’ checkbox, or you can simply terminate the manager and it will not run again unless you reactivate it. If it is really necessary, you can query the manager in the ‘Define Manager’ form, and delete the row. (It is recommended that you DO NOT do this).

How can I check to see if a concurrent manager is running?

One way to see if a manager is running is to use the ‘Administer Concurrent Managers’ form. Navigate to Concurrent->Managers->Administer.

You will see two columns labeled ‘Actual’ and ‘Target’. The Target column lists the number of processes that should be running for each manager for this particular workshift. The Actual column lists the number of processes that are actually running. If the Actual column is zero, there are no processes running for this manager. If the Target column is zero, then either a workshift has not been assigned to this manager, or the current workshift does not specify any target processes. If the target column is not zero, and then the manager processes have either failed to start up, or gone down.

How to define the concurrent manager - The navigation of the concurrent manager is

Concurrent > manager > Define

In that we will assign the specialization rules, workshifts, and program libraries to the manager.

How to assign the specialization rule for the manager - The navigation of the concurrent manager specialization rule is

Concurrent > manager > Specializationrule

By assigning the specialization rule we can find out which concurrent request the concurrent manager is going to read.

R12 – Concurrent Program Tracing

Concurrent Program Tracing :

Reference Note : 296559.1

CASE 1 : Concurrent Program Tracing without bind variables
1) Follow the following navigation to enable logging for conc prog
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace
2) Execute the concurrent program using the following navigation and note down the request id
3) Collect the trace file using the script provided here

CASE 2: Concurrent Program Tracing with bind variables and waits 
1) Note down the following values

SELECT value FROM v$parameter WHERE name = ‘max_dump_file_size’;
SELECT value FROM v$parameter WHERE name = ‘timed_statistics’;

2) Execute the following commands as sysdba

ALTER SYSTEM SET max_dump_file_size = unlimited;
ALTER SYSTEM SET timed_statistics = true;
ALTER SYSTEM SET EVENTS ’10046 trace name context forever, level 12′;

3) Execute the concurrent program using the following navigation and note down the request id
4) Collect the trace file using the script provided here
5) Turn off tracing the reset the values

ALTER SYSTEM SET EVENTS ’10046 trace name context off’;
ALTER SYSTEM SET max_dump_file_size = <value from step 1>;
ALTER SYSTEM SET timed_statistics = <value from step 1>;

CASE 3:
Enabling the trace for a concurrent request for which you donot have privileges to run the concurrent Request.
1) Ask the person who is privileged to run the concurrent program and get the request id ‘xxxxx’
2) Get the oracle_process_id for that concurrent request.

SQL>select request_id,oracle_process_id from fnd_concurrent_requests where request_id in (‘xxxxxxx’);

3) Now get the session details ( SID and Serial ) using value obtained from step 2

col “SID/SERIAL” format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ‘,’ || s.serial# “SID/SERIAL”
, s.username
, s.osuser
, s.status
, p.spid “OS PID”
, s.inst_id
, s.module
from sys.gv_$session s
, sys.gv_$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and p.spid=&value_from_step2
order by to_number(p.spid);

4) Execute the following command to enable the trace :

SQL> EXECUTE dbms_support.start_trace_in_session (&SID,&SERIAL,binds=>true,waits=>true);

NOTE : You need to run this command on the corresponding rac node, inst_id from step3)
5) Collect the trace from udump location and investigate the issue.

Automatic SQL Tuning in Oracle Database 11g

g automates the SQL Tuning process by identifying problematic SQLg:

Oracle Database 11

statements, running SQL Tuning Advisor on them, and implementing the resulting SQL profile

recommendations to tune the statement without requiring user intervention. Automatic SQL Tuning

uses the AUTOTASK framework through a new task called “Automatic SQL Tuning” that runs

every night by default. Here is a brief description of the automated SQL tuning process in Oracle

Database 11

Step 1: Based on the AWR Top SQL identification (SQLs that were top in four different time

periods: the past week, any day in the past week, any hour in the past week, or single response time),

Automatic SQL Tuning targets for automatic tuning.

Steps 2 and 3: While the Automatic SQL Tuning task is executing during the maintenance window,

the previously identified SQL statements are automatically tuned by invoking SQL Tuning Advisor.

As a result, SQL profiles will be created for them if needed. However, before making any decision,

the new profile is carefully tested.

Step 4: At any point in time, you can request a report about these automatic tuning activities.

You then have the option of checking the tuned SQL statements to validate or remove the automatic

SQL profiles that were generated.

How to setup 11g active dataguard

Main advantage of 11g dataguard is

■Ability to open the standby database in read-only mode while the database is still in managed recovery mode. This means 11g active dataguard database is bi-functional, provides disaster recovery and at the same time can be used for reporting purpose.

■Able to open standby database in read-write. This is very useful to turn our standby database into application testing / development purposes. This is achieved through snapshot standby database.

Steps

Setting up active dataguard is very easy and straight forward. Initial steps to create active dataguard are as same as setting up 10g dataguard. The steps are almost similar with only few different commands. In this scenario, I have named my Niti_Production database as ‘Niti_Prod’ and standby database as ‘Niti_Stdby’ :

1. First of all, parameter file (with appropriate dataguard parameters) and standby control file must be created from the Niti_Production database.

2.Setup listener.ora and tnsnames.ora for connectivity between Niti_Production and standby.

3. Connect to rman

rman target sys/oracle@Niti_Prod auxiliary sys/oracle@Niti_Stdby

4. With 11g active dataguard, 1 simple command will create standby without even backing up Niti_Prod database and placing it in a staging area.

RMAN>duplicate target database for standby from active database;

5. Standby database successfully created with the db is in mount state.

Active Dataguard

1. Use dataguard broker to enable active dataguard.

2. If you try to open database during this time, you will get error .

3. In order to open the standby database, connect to dgmrl and change the apply state.

4. Open the standby database now.



5. Re-enable the apply state so that redo transferred and applied to standby database while the database is open for querying.

6. To disable the active dataguard, the steps are the same :

I. DGMGRL> edit database Niti_Stdby set state=apply-off;

II. Shutdown the Niti_Stdby database

III. Startup mount the Niti_Stdby database

IV. DGMGRL> edit database Niti_Stdby set state=apply-on;

Snapshot standby database

1. In order to create snapshot standby database :

■standby database must be a physical standby database
■Flashback logging must be enabled on both Niti_Production and standby database.

2. After enabling flashback mode, connect to dgmrl utility in the primary database :



3. Convert the physical standby to snapshot standby with a single command .

4. From now on, we can do any testing (like creating new schemas, tables and so on) on our standby database. Please note that at this point of time, all the redo generated in our Niti_Production database will be still shipped to standby database. But it is just that not going to be applied until the database is converted into physical standby mode.

5. Once the testing done, you can convert the snapshot standby database to physical standby with just 1 command.

DGMGRL>convert database Niti_Stdby to physical standby;

6. Note that when the above command executed

■All the changed made to the snapshot standby database ( such as creating new schemas, tables) has been terminated
■Previous physical standby database state is initiated
■Physical standby database is mounted and MRP process is initiated. MRP will apply all the logs which was shipped yet applied during the snapshot standby database state.

7. Duration for this process is depends on few factors :

■The amount of changes made to the database during the snapshot standby database state. More changes will lead to more time to rewind the changes via flashback database option.
■The amount of archived logs generated during the snapshot standby database state. More archive logs will cause more time to apply it when the database is converted to physical standby database.

Oracle Concurrent Request Error Script

Do you need a script to check on the error on your Oracle Concurrent Request? Sometimes a script to check on the Oracle Concurrent Request is more useful than navigating into the Oracle Applications to check.

The script below will return any error in Oracle Concurrent Request for 7 days and for all the request. With the error information output from the script below, you may find the frequency of the error in your Oracle Concurrent Request.

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 7
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;

Long Running Concurrent Request In Oracle

Looking on how to check long running concurrent request in Oracle Apps 11i or R12? Here’s the overview of the SQL query script to detect the session information of each program.

First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request.


SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC


You can see the request id and other relevant information from the result.

Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend.



SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '11696'
ORDER BY a.spid, c.piece


Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session.

Find SID from Concurrent Program request ID

SELECT sid
FROM v$session
WHERE paddr LIKE
(SELECT addr
FROM v$process
WHERE spid =
(SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER()
)
);

Oracle 11g SQL Plan Management – SQL Plan Baselines

Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. The previous efforts were Outlines and SQL Profiles. Both of those efforts were based on the idea that hints could be applied to limit the optimizers choices down to one. That approach seems a little flawed. Why not just save the plan and be done with it? I believe that’s what Baselines are intended to do. Unfortunately, they don’t appear to do it yet. But they do have an advantage over Outlines and SQL Profiles in that they at least save the plan_hash_value, so they know if they are reproducing the correct plan or not.

Oracle SQL plan management (SPM) relieves the problem of environmental changes causing thousands of SQL statements to change their explain plan steps.

By default, the parameters optimizer_capture_sql_plan_baselines is set to FALSE and optimizer_use_sql_plan_baselines is set to TRUE.

Oracle notes that when SQL plan management is enabled, only known and verified plans are used, and all plan changes are automatically verified. During execution plan verification, only "better" execution plans will be implemented.

Oracle SQL plan management also offers a new package called dbms_spm and a new DBA view dba_sql_plan_baseline to allow the DBA to manage their SQL plans.

dbms_spm.evolve_sql_plan_baseline - This SPM procedure tests a new execution plan against a "verified" plan to determine if the new plan has comparable (or better) execution performance.

dbms_spm.load_plans_from_cursor_cache - This SPM procedure will extract revised SQL explain plans directly from the library cache.

dbms_spm.load_plans_from_sqlset - This SPM procedure allow the DBA to take pre-tested execution plans from a SQL tuning Set (STS), after running a workload test, and load them for production use.

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.