UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.3

UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.3


11.2.0.3 is the latest release (at the time of writing this post) for the Oracle database. The intent of this guide is to upgrade the current database version from 11.1.0.7 to 11.2.0.3.

The installation procedure for the new Oracle Database 11g Release 2 (11.2) installs the Oracle software into a new Oracle home. This is referred to as an out-of-place upgrade and is different from patch set releases for earlier releases of Oracle Database, where the patch set was always installed in place. Oracle strongly recommends that you follow the steps in this procedure to ensure minimal downtime for the upgrade process and integrity of the new binaries and software libraries.

Also, Manual upgrade method has been used instead of DBUA for better control over the complete upgrade process.

Linux x86-64 is used as platform in the below guide

SOFTWARE SOURCES

You can obtain the 11.2.0.3 software from patch 10404530 on My Oracle Support

Files 1, 2 and 3 are sufficient to cover 11.2.0.3 database (including grid infra)


Part 1: INSTALL ORACLE 11.2.0.3 SOFTWARE



1.1) Check oratab file

Ensure that the oratab file contains an entry for the database to be upgraded



1.2) Check for installed languages

SQL> select LANGUAGE_CODE,LANGUAGE_ID,NLS_LANGUAGE,INSTALLED_FLAG from fnd_languages where INSTALLED_FLAG in (‘I’,’B’);
LANG LANGUAGE_ID NLS_LANGUAGE                  
—- ———– —————————— -
US             0 AMERICAN                       B
CS            30 CZECH                          I
D              4 GERMAN                         I



1.3) Install 11g database software

cd <software location>
./runInstaller

Below are the details of OUI screen and actions.



Screen 1: Configure Security updates

Leave blank and press next



Screen 2: Download Software updates

Check ‘Skip software updates’ and press next



Screen 3:  Select Installation option

Check ‘Install database software only’ and press next



Screen 4: Grid Installation options

Check ‘Single Instance database installation’ and press next



Screen 5: Select Product Languages

Choose English & all other languages required and press next

NOTE : You may select only ‘English language’ even if on EBS side you have many languages installed. The languages that we select during database install/upgrade through OUI are not related to what languages we install in EBS. If you select languages other than the English language, the Oracle installer will bring over extra *.msb files for the languages selected and the user/developer will be able to see “core” ora- messages in languages other than English too.



Screen 6: Select database edition

Check ‘EnterpriseEdition’



Screen 7: Specify Installation location

Input values of ‘Oracle base’ and press next



Screen 8: Privileged operating system groups

Take dba (or whatever group you created ) as OSDBA Group and press next



Screen 9: Perform prerequisite checks

Correct if anything is required to (by fix & check again) and press next



Screen 10: Summary

Check and press Install and press next



Screen 11a: Install

Installation will proceed.



Screen 11b: run root.sh scripts as instructed



Screen 12: Finish

This completes the database 11g installation.


Part 2: PRE-UPGRADE STEPS



2.1) Take Invalid Count

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID';



2.2) Apply mandatory patches



Category a) For customers on E-Business Suite Release 12.1, apply:

Check in ad_bugs and apply whichever patch is not available. All the below patches need to be applied by adpatch

1) 9062910 (11g Release 2 interoperability patch for Release 12.1 )

2) 8919489 (12.1 TXK Delta 3 patch)

3) 9868229

4) 10163753



Category b) ) On 11.2.0.3 home, apply additional 11.2.0.3 RDBMS patches :

Apply the following patches (OPATCH) for all UNIX/Linux platforms:

NOTE: Do not run any of the post install instructions as those will be done after the upgrade.

1) 4247037
 There is a Special post patch Instructions (mentioned below), execute ONLY after database upgrade part is done

Post install steps:
cd $ORACLE_HOME/md/admin
connect to the database using sqlplus / as sysdba

SQL>@catmgdidcode



2) 9858539
There is a post patch install Instructions (step 3, mentioned below), execute ONLY after database upgrade is done

Post install steps:

Run following files as sysdba
cd $ORACLE_HOME/rdbms/admin
@catnomtt.sql
@catnomta.sql
@dbmsmeta.sql
@dbmsmeti.sql
@dbmsmetu.sql
@dbmsmetb.sql
@dbmsmetd.sql
@dbmsmet2.sql
@catmeta.sql
@prvtmeta.plb
@prvtmeti.plb
@prvtmetu.plb
@prvtmetb.plb
@prvtmetd.plb
@prvtmet2.plb
@catmet2.sql– recompile dbms_metadata_int to enable the diffing code

alter package dbms_metadata_int compile plsql_ccflags = ‘ku$xml_enabled:true';

— recompile dbms_metadata_util to enable the xmlschema load code

alter package dbms_metadata_util compile plsql_ccflags = ‘ku$xml_enabled:true';

3) 12942119

4) 12960302

5) 12985184

6) 13001379

7) 13004894

8) 13258936

9) 13366268



2.3) Run utlu112i.sql on existing 11.1.0.7 oracle database

Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory(/tmp) on your system.

Connect via existing 11.1.0.7 database and run utlu112i.sql

Below issue were found and were corrected



ISSUE  1

———-

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

–> plsql_native_library_dir     11.2       OBSOLETE

–> plsql_native_library_subdir_ 11.2       OBSOLETE



SOLUTION 1

—————-

commented out below values in init file

#plsql_native_library_dir = /u01/oracle/<instance_name>/db/tech_st/11.1.0/plsql/nativelib

#plsql_native_library_subdir_count = 149



ISSUE 2

———-

WARNING: –> Database is using a timezone file older than version 14.

…. After the release migration, it is recommended that DBMS_DST package

…. be used to upgrade the 11.1.0.7.0 database timezone version

…. to the latest version which comes with the new release



SOLUTION 2

————–

-ignore

Upgrade to 11.2.0.3. No need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.

The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.



ISSUE 3

———–

WARNING: –> Your recycle bin contains 99 object(s).

…. It is REQUIRED that the recycle bin is empty prior to upgrading

…. your database.  The command:

PURGE DBA_RECYCLEBIN



SOLUTION 3

—————

<run PURGE DBA_RECYCLEBIN>



ISSUE 4

———-

Oracle recommends gathering dictionary statistics prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:



SOLUTION 4

—————-

<run  EXECUTE dbms_stats.gather_dictionary_stats;>



ISSUE 5

———-

Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command

while connected AS SYSDBA:

SELECT name,description from SYS.V$PARAMETER WHERE name

LIKE ‘\_%’ ESCAPE ‘\’



Changes will need to be made in the init.ora or spfile.



SOLUTION 5

————–

Comment out all hidden variables in init file





2.4) Shut down Applications server processes and database listener



shut down all the application services running on Apps tier and also the database listener ( keep the database running)



2.5) Run dbupgdiag.sql on existing 11.1.0.7 oracle database

Run dbupgdiag.sql to find any duplicate objects in sys or system schema

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

It will ask for input directory to create log file

See the instructions/warnings/errors in log file produced and correct if anything



2.6) Purge DBA_RECYCLEBIN on existing 11.1.0.7 oracle database

SQL> PURGE DBA_RECYCLEBIN;

Note: Already done as corrective action in Step 2.3



2.7) Check Hidden Parameters on existing 11.1.0.7 oracle database

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\';

Oracle recommends removing all hidden parameters prior to upgrading.

Note: Already done as corrective action in Step 2.3



2.8) Set the new environment for 11.2.0.3 oracle home

After the software installation of Oracle 11.2.0.3 you must set the environment variables to the new home.

export ORACLE_BASE=/u01/oracle/<instance_name>/

export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata



Make sure that The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])



2.9) Create nls/data/9idata directory on new 11.2.0.3 home

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.

$ perl cr9idata.pl

$ export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata



2.10) Check for TIMESTAMP WITH TIMEZONE Datatype on existing 11.1.0.7 oracle database

The RDBMS DST patching has been greatly improved in 11gR2.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

———-

10

For Upgrade to 11.2.0.3, there is no need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.

The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.



2.11) Check the National Characterset  on existing 11.1.0.7 oracle database

Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.

SQL> select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET';

VALUE

———————-

AL16UTF16



2.12) Run Optimizer Statistics on existing 11.1.0.7 oracle database

When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

$ sqlplus “/as sysdba”

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;



2.13) Drop SYS.ENABLED$INDEXES (conditional)

If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:

SQL> drop table sys.enabled$indexes;

PL/SQL procedure successfully completed.

2.14) Disable Database Vault (conditional)



If you have Database Vault installed, perform steps 1 to 6 of Part 2 of document 1091083.1 on My Oracle Support to disable Database Vault.



Check>>

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault';

PARAMETER                                              VALUE

—————————————————————-

Oracle Database Vault                                 FALSE

If the Value is ‘FALSE’ so no action is required.



2.15) Back up Enterprise Manager Database Control Data (conditional)

You may need to backup the EM data if you have installed.



2.16) Check for any logical corruption

To check for corruption in the dictionary, use the following commands in SQL*Plus (connected as sys):

—————————————————————-

Set verify off

Set space 0

Set line 120

Set heading off

Set feedback off

Set pages 1000

Spool analyze.sql

SELECT ‘Analyze cluster “‘||cluster_name||’” validate structure cascade;’

FROM dba_clusters

WHERE owner=’SYS’

UNION

SELECT ‘Analyze table “‘||table_name||’” validate structure cascade;’

FROM dba_tables

WHERE owner=’SYS’

AND partitioned=’NO’

AND (iot_type=’IOT’ OR iot_type is NULL)

UNION

SELECT ‘Analyze table “‘||table_name||’” validate structure cascade into invalid_rows;’

FROM dba_tables

WHERE owner=’SYS’

AND partitioned=’YES';

spool off

——————————————————————



This creates a script called analyze.sql.

Now execute the following steps:

$ sqlplus “/ as sysdba”

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

SQL> @analyze.sql

This script (analyze.sql) should not return any errors.



Note:

1. ORA-30657 might occur if there is any external table validated, which can be safely ignored as per Note 209355.1 ORA-30657: Using ANALYZE TABLE for an External Table

2. Errors shown below when executing analyze.sql can be ignored:

SP2-0734: unknown command beginning “SQL> SELEC…” – rest of line ignored.

SP2-0042: unknown command “SQL>” – rest of line ignored.

SP2-0734: unknown command beginning “SQL> spool…” – rest of line ignored.

SQL> @analyze.sql

Analyze table “SYS_TZUV2_AFFECTED_REGIONS” validate structure cascade

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table



2.17) Ensure that all snapshot refreshes are successfully completed

Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.

SELECT DISTINCT(TRUNC(last_refresh))FROM dba_snapshot_refresh_times;





2.18) Ensure that no files need media recovery and that no files are in backup mode

Ensure that no files need media recovery and that no files are in backup mode.

SELECT * FROM v$recover_file;

SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE';

This should return no rows.





2.19) Resolve outstanding distributed transactions

Resolve outstanding distributed transactions prior to the upgrade.

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> SELECT local_tran_id FROM dba_2pc_pending;

SQL> EXECUTE dbms_transaction.purge_lost_db_entry(”);

SQL> COMMIT;

Note: no records were returned



2.20) To check if a standby database exists, issue the following query

To check if a standby database exists, issue the following query:

SELECT SUBSTR(value,INSTR(value,’=’,INSTR(UPPER(value),’SERVICE’))+1)

FROM v$parameter WHERE name LIKE ‘log_archive_dest%’ AND UPPER(value) LIKE ‘SERVICE%';

If this query returns a row, then sync the standby database with the primary database.

1. Make sure all the logs are transported to the standby server after a final log switch in the primary.

2. Start the recovery of the standby database with the NODELAY option.

2.21) Disable all batch and cron jobs

Disable all batch and cron jobs.

About jobs initiated with Oracle the packages DBMS_JOB, DBMS_SCHEDULER can be used , regarding cron jobs (external jobs controlled at the OS level), this is a task for your Unix administrator

See also :

Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER

Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB

Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification



2.22) Check SYS and SYSTEM  tablespace



Ensure the users SYS and SYSTEM have ‘SYSTEM’ as their default tablespace.

You must have sufficient space in the tablespace or be set to extents unlimited.



SQL> SELECT username, default_tablespace

FROM dba_users

WHERE username in (‘SYS’,’SYSTEM’);



If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command.



SQL> ALTER user SYS default tablespace SYSTEM;

SQL> ALTER user SYSTEM default tablespace SYSTEM;



2.23) Check AUD$ table

Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.

SQL> SELECT owner,tablespace_name

FROM dba_tables

WHERE table_name=’AUD$';

If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS .

Note: If the AUD$ table exists and is in use, upgrade performance can be effected depending on the number of records in the table.

For 10.2 and later source versions there is now a pre-process script available.

Please review and refer the following note  for complete information :

NOTE:1329590.1 How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later



2.24) Check for externally authenticated SSL users

Check whether database has any externally authenticated SSL users.

SQL> SELECT name FROM sys.user$

WHERE ext_username IS NOT NULL

AND password = ‘GLOBAL';

If any SSL users are found then few Step has to be followed after the upgrade ( refer to the documents mentioned at the end of document under ‘References’ section.



2.25) Take Backup of Configuration files

Note down the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.

SQL> SELECT name FROM v$controlfile;

SQL> SELECT file_name FROM dba_data_files;

SQL> SELECT group#, member FROM v$logfile;



2.26) Check if the  database listener is stopped

Stop the listener for the database.

$ lsnrctl stop

Previous versions of the listener are not supported for use with an Oracle Database 11g Release 2 (11.2) database. However, it is possible to use the new version of the listener with previous versions of Oracle Databases.

You must remove the old listener before creating a new one. If you attempt to create a new listener from the new Oracle Home first, and use the same name and port as the old listener, then Oracle Net Configuration Assistant returns an error.

2.27) Stop other executable such as dbconsole, isqlplus, etc.

Stop other executable such as dbconsole, isqlplus, etc.

$ emctl stop dbconsole

$ isqlplusctl stop



2.28) Shutdown the 11.1.0.7 database and take cold backup

Shutdown the database and take cold backup (more reliable)

$ sqlplus “/as sysdba”

SQL> shutdown immediate;

Back up the Database



2.29) Make a backup of the init<SID>.ora file

Make a backup of the init<SID>.ora file.

Comment out obsoleted parameters if present.

DRS_START

GC_FILES_TO_LOCKS

MAX_COMMIT_PROPAGATION_DELAY

PLSQL_NATIVE_LIBRARY_DIR

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

SQL_VERSION



2.30) Clean Up Duplicate Objects Owned by SYS and SYSTEM schema

Refer > Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema

Since most data dictionary objects should be owned by SYS (see exceptions below) you will want to drop the objects that are owned by SYSTEM in order to clear up this situation.

List all objects that have been created in both the SYS and SYSTEM schema:

column object_name format a30

select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = ‘SYS’)
and owner = ‘SYSTEM';

This will give a list of duplicate objects and you will simply issue the appropriate DROP command to get rid of the object that is owned by the SYSTEM user.

EXCEPTION TO THE RULE (Important!)

Please check the above mentioned note id and do not drop few objects mentioned in it which are related to replication or else it will cause replication to fail.




Part 3: UPGRADE DATABASE TO 11.2.0.3



3.1) Copy over the modified init file

Once the parameter file is modified as per your requirement (already done above), copy the file from old 11.1.0.7 $ORACLE_HOME/dbs to the new 11.2.0.3 $ORACLE_HOME/dbs.



3.2) Check Environment Variables

Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:

export ORACLE_BASE=/u01/oracle/<instance_name>/

export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata

3.3) Update the oratab entry

Update the oratab entry to set the new ORACLE_HOME pointing to <db_name> and disable automatic startup

/etc/oratab entries

#<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.1.0:N

<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.2.0:N

Note: After /etc/oratab is updated to have SID and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the SID which is entered in /etc/oratab against the 11gR2 home.

3.4) Upgrading Database to 11gR2

At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.



$ export ORACLE_SID=<instance_name>

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus “/ as sysdba”

SQL> startup UPGRADE

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.

SQL> set echo on

SQL> SPOOL upgrade.log

SQL> @catupgrd.sql

SQL> spool off

These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting.



3.5) Check the upgraded database



SQL> select banner from v$version;

BANNER
———————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production





3.6) Run utlu112s.sql script

Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed.

$ sqlplus “/as sysdba”

SQL> STARTUP

SQL> @utlu112s.sql



3.7) Run catuppst.sql

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.

SQL> @catuppst.sql

3.8) Run dbupgdiag.sql

Check for the integrity of the upgraded database by running dbupgdiag.sql script

Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (next step)

After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

3.9) Run utlrp.sql

Run $ORACLE_HOME/rdbms/admin/utlrp.sql multiple times to validate the invalid objects in the database, until there is no change in the number of invalid objects.

Note: This step will take some considerable time on first run

Keep monitoring the invalid count by

select count(*) from dba_objects where status =’INVALID';



3.10)  Check components in dba_registry for status ‘VALID’

set lines 150 pages 500

column COMP_NAME format a45

column version format a15

column status format a12

SQL> select comp_name,version,status from dba_registry;



COMP_NAME                                     VERSION         STATUS

——————————————— ————— ————

OLAP Catalog                                  11.2.0.3.0      VALID

Oracle Data Mining                            11.2.0.3.0      VALID

Oracle XML Database                           11.2.0.3.0      VALID

Oracle Text                                   11.2.0.3.0      VALID

Spatial                                       11.2.0.3.0      VALID

Oracle Multimedia                             11.2.0.3.0      VALID

Oracle Database Catalog Views                 11.2.0.3.0      VALID

Oracle Database Packages and Types            11.2.0.3.0      VALID

JServer JAVA Virtual Machine                  11.2.0.3.0      VALID

Oracle Database Java Packages                 11.2.0.3.0      VALID

Oracle XDK                                    11.2.0.3.0      VALID

Oracle Real Application Clusters              11.2.0.3.0      INVALID

OLAP Analytic Workspace                       11.2.0.3.0      VALID

Oracle OLAP API                               11.2.0.3.0      VALID



14 rows selected.






PART 4: POST-UPGRADE STEPS



4.1) Perform patch post-install instructions

Run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.



4.2) Start the new database listener (conditional)

To start the new Oracle 11.2.0 Listener. First copy the old Oracle 11.1.0 $TNS_ADMIN directory to the new Oracle 11.2.0 $TNS_ADMIN. Be careful to change inside the files all the old references to the 11.1.0 directories to the new 11.2.0 directories.



lsnrctl start



 4.3) Set the  environment variables

copy the $ORACLE_11.1.0_HOME/SID_host.env to $ORACLE_11.2_HOME/SID_host.env, make the necessary changes to point the new 11.2.0 home and source the environment ( correct the value in .bash_profile).



4.4) Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

$ sqlplus “/ as sysdba” @adgrants.sql (or adgrants_nt.sql)

[APPS schema name]

4.5) Grant create procedure privilege on CTXSYS



Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:



$ sqlplus apps/[APPS password] @adctxprv.sql \ [SYSTEM password] CTXSYS

4.6) Set CTXSYS parameter

Use SQL*Plus to connect to the database as SYSDBA and run the following command:

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);



4.7) Validate Workflow ruleset

On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:



$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\ [APPLSYS user] [APPS user]



4.8)  Implement and run AutoConfig

Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.



See “Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]”

on My Oracle Support for instructions on how to implement and run AutoConfig.

Section 3.2 explains how to generate a context file on the database tier.



Step 1: Create appsutil.zip

On the application tier (as the APPLMGR user):

Log in to the APPL_TOP environment (source the environment file)

Create appsutil.zip file

perl <AD_TOP>/bin/admkappsutil.pl

This will create appsutil.zip in <INST_TOP>/admin/out



Step 2: Copy appsutil.zip to DB node and uncompress

On the database tier (as the ORACLE user):

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip



Step 3: Generate the Database Context File

Execute the following command to create your Database Context File:

perl <RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl



Step 4:  Run AutoConfig on the Database tier

Run AutoConfig on the Database tier by executing the below command:

On Unix:

<RDBMS_ORACLE_HOME>/appsutil/bin/adconfig.sh contextfile=<context_file>

Shut down all processes, including the database and the listener, and restart them to load the new environment settings.



4.9) Apply post-upgrade ECX patch

If you are on E-Business Suite Release 12.0, apply ECX patch 9922442.

If you are on E-Business Suite Release 12.1, apply patch 9151516.



4.10) Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:

$ sqlplus “/ as sysdba”

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus “/ as sysdba”

SQL> alter system disable restricted session;

SQL> exit;



4.11) Re-create custom database links (conditional)

Create as required



4.12) Re-create grants and synonyms

You must re-create the grants and synonyms in the APPS schema. On the administration server node, as the owner of the Applications file system, run AD Administration and select the  “4.    Maintain Applications Database Entities menu” and then > “Recreate grants and synonyms for APPS schema” task.





4.13) Compile Invalid Objects 

SQL>@utlrp.sql

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects  where STATUS=’INVALID';



4.14) Restart Applications server processes

Start the application process and do a basic check of all application services from EBS side.





4.15) Synchronize Workflow views



Log on to Oracle E-Business Suite with the “System Administrator” responsibility. Click Requests > Run > Single Request and the OK button. Enter the following parameters:



Request Name = Workflow Directory Services User/Role Validation

p_BatchSize = 10000

p_Check_Dangling = Yes

Add missing user/role assignments = Yes

Update WHO columns in WF tables = No


REFERENCES

    Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]
    Oracle Database Upgrade Guide 11g Release 2 (11.2)
    Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
    Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]
    Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1]
    Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

Shrink Datafiles

Shrink Datafiles – Adjusting HWM

This below example is derived from “Shrinking datafiles” scripts from asktom.oracle.com
Scenario:-
Lets suppose the datafile size is 100m with autoexted upto maxsize 32gb.
1) Created a table
2) Inserted 50millions of rows with commit
— Data file size extended from 100mb to 20gb.
3) performed so many DML’s
— Data file size extended from 100mb to >20gb.
4) Truncated Table/Dropped Table.
Space will can be used further to use for other objects in datafile. But as per my requirement when i really won’t use that datafile, then what is the use to keep datafile size > 20gb and which is causing much disk usage at OS level? Instead of that check the HWM of datafile, at what minimum size you can resize it?
Script for MAX-Shrink:-

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/

In lower versions you can use below query to find out possible savings from each data files of Database.

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Sample Output of above query:-

                                                    Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf                13,697   32,708   19,011
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf                13,761   32,748   18,987
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf             13,953   32,748   18,795
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf                13,953   32,728   18,775
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf             15,681   32,738   17,057
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf             15,681   32,718   17,037
/u02/oradata/cpdevdb/psindex_01.dbf                  11,285   26,718   15,433
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf                22,913   32,728    9,815
/u02/oradata/cpdevdb/cpled_2010_1.dbf                 7,721   10,858    3,137
/u02/oradata/cpdevdb/cpled_2009_1.dbf                 4,745    6,828    2,083
/u02/oradata/cpdevdb/cpled_2008_1.dbf                 4,873    6,948    2,075
/u02/oradata/cpdevdb/cpled_2009_2.dbf                 4,825    6,728    1,903
/u02/oradata/cpdevdb/cpled_idx_1.dbf                 19,905   21,000    1,095
                                                                     --------
sum                                                                   145,203

By the output i can reclaim space around 141gb of space, without reorganizing any objects.
Resize Datafiles:-
We can resize datafile up to “Smallest Size Poss” value (or) we can assign any fixed size (or) On top of that we can enable autoextend up to maximum size of datafile.

alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' resize 13700m;
alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' autoextend on next 10m maxsize 32767m;

After resizing datafile, Possible savings recorded is around 19GB.
Execute above Script to check changes after resizing of datafiles:-

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf                13,697   13,700        3
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf                13,761   13,770        9
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf             13,953   13,970       17
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf                13,953   13,970       17
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf             15,681   15,690        9
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf             15,681   15,690        9
/u02/oradata/cpdevdb/psindex_01.dbf                  11,285   11,300       15
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf                22,913   22,920        7
/u02/oradata/cpdevdb/cpled_2010_1.dbf                 7,721    7,730        9
/u02/oradata/cpdevdb/cpled_2009_1.dbf                 4,745    4,750        5
/u02/oradata/cpdevdb/cpled_2008_1.dbf                 4,873    4,900       27
/u02/oradata/cpdevdb/cpled_2009_2.dbf                 4,825    4,850       25
/u02/oradata/cpdevdb/cpled_idx_1.dbf                 19,905   19,910        5
                                                                     --------
sum                                                                       157

In earlier output possible savings is 141gb, Now possible savings is 157mb, So we reclaimed ~141gb of space at OS level. 
Now lets compare OS level free space  Before & After

Killing multiple sessions in Oracle Database

Hi,

We often get task to kill sessions which are active,inactive and the session related with a particular program.When we have one or two sessions we can kill it easily,if we have multiple sessions we can do it as follows:


Step 1:Check the Name of the Database
sqlplus "/as sysdba"

SQL>Select name from v$database;

Step 2:How to kill all inactive session in Database

sqlplus "/as sysdba"

SQL>set heading off

SQL>spool kill12.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
 FROM v$session
 WHERE status ='INACTIVE' and type != 'BACKGROUND';

SQL>spool off

then execute the sql script

SQL> @kill12.sql

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

How to kill all active session in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' and type != 'BACKGROUND';

SQL>spool off

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to kill all ODI sessions in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>set lines 1000
SQL>set linesize 2000
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' AND USERNAME LIKE '%ODI%';

SQL>spool off


Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to get the list of Users and Processes running ODI sessions:

SQL> 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' AND S.USERNAME LIKE '%ODI%'; 

 How to kill a particular object blocking session:

 1.Find the tables(objects) which are locked:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id and o.object_name='XX_OBJECT';

2.Killing the session holding the lock:

--Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

Enjoy DBA tasks...

Happy DBA learning..

Find Trace to Application

FND Debug profiles options enabled userd details | Oracle Apps R12

set pagesize 200
col NAME for a25
col LEV for a6
col CONTEXT for a25
col VALUE for a50
col USER_PROFILE_OPTION_NAME for a37
select po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where (po.profile_option_name like '%AFLOG_ENABLED%' or po.profile_option_name like
'%FND_INIT_SQL%')
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
and svr.node_id (+) = pov.level_value
and org.organization_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE";

*********** OR ***************************
set lines 400
col APP_SHORT for a20
col OPTVAL for a30
col OPTNAME for a40
col D_LEVEL for a40
select distinct
a.application_short_name app_short,
user_profile_option_name optname,
decode(level_id,
10001,'SITE',
10002,'APP : '||a2.application_short_name,
10003,'RESP: '||r.responsibility_key,
10004,'USER: '||u.user_name,
'Unknown') d_level,
profile_option_value optval,
v.last_update_date updated
from fnd_profile_options_vl o,
fnd_profile_option_values v,
fnd_application a,
fnd_application a2,
fnd_responsibility r,
fnd_user u
where (
o.user_profile_option_name like '%Debug%' or
o.user_profile_option_name like '%DEBUG%' or
o.user_profile_option_name like '%Trace%' or
o.user_profile_option_name like '%TRACE%'
)
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = 'Y'
order by 2,1,3,4;



****************************How to truncate FND_LOG_MESSAGES files***********************************
select owner,tablespace_name,segment_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='FND_LOG_MESSAGES' GROUP BY owner,segment_name, tablespace_name;

OWNER                          TABLESPACE_NAME SEGMENT_NAME                                                                      SUM(BYTES/1024/1024/1024)
------------------------------ --------------- --------------------------------------------------------------------------------- -------------------------
APPLSYS                        APPS_TS_TX_DATA FND_LOG_MESSAGES                                                                                 77.0758057


SQL>truncate table APPLSYS.FND_LOG_MESSAGES;


****************LOG messages are not created in FND_LOG_MESSAGES*****************************
   
ALTER SEQUENCE  applsys.FND_LOG_MESSAGES_S MAXVALUE 9999999999;

SQL> select FND_LOG_MESSAGES_S.NEXTVAL from DUAL;
NEXTVAL
----------
2147483815
SQL> select FND_LOG_MESSAGES_S.NEXTVAL from DUAL;
NEXTVAL

Find OPP logfile

SELECT fcpa.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpa, fnd_concurrent_processes fcp
 WHERE fcpa.processor_id = fcp.concurrent_process_id
   AND fcpa.action_type = 6
   AND fcpa.concurrent_request_id = :P_REQUEST_ID;

Script Find Concurrent request

Very Important Concurrent Request Scripts | Oracle Apps R12

****HISTORY OF CONCURRENT REQUEST - SCRIPT (PROGRAM WISE) *****

set pagesize 200
set linesize 200
col "Who submitted" for a25
col "Status" for a10
col "Parameters" for a20
col USER_CONCURRENT_PROGRAM_NAME for a42
SELECT distinct t.user_concurrent_program_name,
r.REQUEST_ID,
to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,
decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",substr(u.description,1,25) "Who submitted",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
FROM
apps.fnd_concurrent_requests r ,
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate-30)
--AND r.requested_by=22378
AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '%%'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');

 *** Requests completion date details ***

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )

request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )

requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )

actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )

actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )

current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration

FROM fnd_concurrent_requests

WHERE request_id = TO_NUMBER('&p_request_id');

*** Reqid_from sid **

SELECT a.request_id, a.PHASE_CODE, a.STATUS_CODE,

d.sid as Oracle_SID,

d.serial#,

d.osuser,

d.process,

c.SPID as OS_Process_ID

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid AND a.PHASE_CODE='R' AND a.STATUS_CODE='R'

AND d.sid = &SID;

How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100

select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,

fnd_concurrent_queues_vl b, fnd_concurrent_requests c

where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID

and a.CONCURRENT_PROCESS_ID = c.controlling_manager

and c.request_id = '&conc_reqid';

Concurrent request status for a given sid?

col MODULE for a20

col OSUSER for a10

col USERNAME for a10

set num 10

col MACHINE for a20

set lines 200

col SCHEMANAME for a10

select s.INST_ID,s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,

s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';

Find out request id from Oracle_Process Id: 

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';

To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,

'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated',  'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where 

ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'

/

To find child requests for Parent request id.

set lines 200

col USER_CONCURRENT_PROGRAM_NAME for a40

col PHASE_CODE for a10

col STATUS_CODE for a10

col COMPLETION_TEXT for a20

SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,

'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated',  'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where  req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';

set col os_process_id for 99

select HAS_SUB_REQUEST, is_SUB_REQUEST, parent_request_id, ORACLE_PROCESS_ID, ORACLE_SESSION_ID, OS_PROCESS_ID from fnd_concurrent_requests where request_id= '&Req_ID' ;

Cancelling Concurrent request :

--By request id 

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where request_id=&req_id;

--by program_id

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where CONCURRENT_PROGRAM_ID=&prg_id;

To terminate the all concurrent requests using by Module wise.

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from gv$session where MODULE like 'GLPREV';

History of concurrent requests which are error out 

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

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; 

***** Find out Concurrent Program which enable with trace****

col User_Program_Name for a40

col Last_Updated_By for a30

col DESCRIPTION for a30

SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",

SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",

SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",

SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION

FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B

WHERE A.ENABLE_TRACE='Y'

AND A.LAST_UPDATED_BY=B.USER_ID;

***Concurrent Program count under QUEUE ***

col  "program name" format a55;

col "name" format  a17;

col "queue name" format a15

col "statuscode" format a3

select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",concurrent_queue_name "QUEUE NAME", priority,decode(phase_code,'P','Pending') "PHASE", 

decode(status_code,'A','Waiting','B','Resuming','C','Normal','D','Cancelled','E','Error','F',

'Scheduled','G','Warning','H','On Hold','I','Normal','M','No Manager','Q','Standby','R','Normal','S',

'Suspended','T','Terminating','U','Disabled','W','Paused','X','Terminated','Z','Waiting') " 

NAME", status_code,count(*) from 

fnd_concurrent_worker_requests 

where  phase_code='P' and hold_flag!='Y' 

and requested_start_date<=sysdate

and concurrent_queue_name<> 'FNDCRM'

and concurrent_queue_name<> 'GEMSPS'

group by 

user_CONCURRENT_PROGRAM_NAME,

concurrent_queue_name,priority,phase_code,status_code

order by count(*) desc

/

***Lists the Manager Names with the No. of Requests in Pending/Running ***

col "USER_CONCURRENT_QUEUE_NAME" format a40;

SELECT a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES,

sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,

sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal,

sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal

FROM FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b

where a.concurrent_queue_id = b.concurrent_queue_id

AND b.Requested_Start_Date<=SYSDATE

GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;

*** Concurrent QUEUE Details *** 

set echo off

set linesize 130

set serveroutput on size 50000

set feed off

set veri off

DECLARE

running_count NUMBER := 0;

pending_count NUMBER := 0;

crm_pend_count NUMBER := 0;

--get the list of all conc managers and max worker and running workers

CURSOR conc_que IS

SELECT concurrent_queue_id,

concurrent_queue_name,

user_concurrent_queue_name, 

max_processes,

running_processes

FROM apps.fnd_concurrent_queues_vl

WHERE enabled_flag='Y' and 

concurrent_queue_name not like 'XDP%' and 

concurrent_queue_name not like 'IEU%' and 

concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;

BEGIN

DBMS_OUTPUT.PUT_LINE('====================================================================================================');

DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue          '||

'Concurrent Queue Name              '||' '||'MAX '||' '||'RUN '||' '||

'Running '||' '||'Pending   '||' '||'In CRM');

DBMS_OUTPUT.PUT_LINE('====================================================================================================');

FOR i IN conc_que 

LOOP

--for each manager get the number of pending and running requests in each queue

SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0), 

nvl(sum(decode(phase_code, 'P', 1, 0)), 0)

INTO running_count, pending_count

FROM fnd_concurrent_worker_requests

WHERE

requested_start_date <= sysdate

and concurrent_queue_id = i.concurrent_queue_id

AND hold_flag != 'Y'; 

--for each manager get the list of requests pending due to conflicts in each manager

SELECT /*+ RULE */ count(1)

INTO crm_pend_count

FROM apps.fnd_concurrent_worker_requests a

WHERE concurrent_queue_id = 4

AND hold_flag != 'Y'

AND requested_start_date <= sysdate

AND exists (

SELECT 'x' 

FROM apps.fnd_concurrent_worker_requests b

WHERE a.request_id=b.request_id

and concurrent_queue_id = i.concurrent_queue_id

AND hold_flag != 'Y'

AND requested_start_date <= sysdate);

--print the output by joining the outputs of manager counts,  

DBMS_OUTPUT.PUT_LINE(

rpad(i.concurrent_queue_id,8,'_')||

rpad(i.concurrent_queue_name,15, ' ')||

rpad(i.user_concurrent_queue_name,40,' ')||

rpad(i.max_processes,6,' ')||

rpad(i.running_processes,6,' ')||

rpad(running_count,10,' ')||

rpad(pending_count,10,' ')||

rpad(crm_pend_count,10,' '));

--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');

END LOOP;

DBMS_OUTPUT.PUT_LINE('====================================================================================================');

END;

/

set verify on

set echo on

Oracle Apps R12 Log File Locations

Oracle Apps R12 Log File Locations

Here is the list of Log file location in Oracle Applications for Startup/Shutdown, Cloning, Patching, DB & Apps Listener and various components in Apps R12

A. Startup/Shutdown Log files for Application Tier in R12

Instance Top is new TOP added in R12

Startup/Shutdown error message text files like adapcctl.txt, adcmctl.txt

$INST_TOP/logs/appl/admin/log

Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)

$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)

$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp]

$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J~, oa*, opmn.log)

$INST_TOP/logs/ora/10.1.2/network/ (listener log)

$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)

B. Log files related to cloning in R12

Preclone log files in source instance

Database Tier  $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)

Application Tier -

$INST_TOP/apps/$CONTEXT_NAME/admin/log/(StageAppsTier_MMDDHHMM.log)

Clone log files in target instance

Database Tier  $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log

Apps Tier  $INST_TOP/admin/log/ApplyAppsTier_.log

If your clone on DB Tier fails while running txkConfigDBOcm.pl (Check metalink note  415020.1)

During clone step on DB Tier it prompts for Target System base directory for source homes and during this you have to give like /base_install_dir like ../../r12 and not oracle home like ../../r12/db/tech_st_10.2.0

C. Patching related log files in R12

i) Application Tier adpatch log  $APPL_TOP/admin/$SID/log/

ii) Developer (Developer/Forms & Reports 10.1.2) Patch  $ORACLE_HOME/.patch_storage

iii) Web Server (Apache) patch  $IAS_ORACLE_HOME/.patch_storage

iv) Database Tier opatch log  $ORACLE_HOME/.patch_storage

D. Autoconfig related log files in R12

i) Database Tier Autoconfig log :

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/adconfig.log

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/NetServiceHandler.log

ii) Application Tier Autoconfig log : $INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

Autoconfig context file location in R12 $INST_TOP/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml

E. R12 Installation Logs

Database Tier Installation

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTechStack_.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ohclone.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/make_.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/installdbf.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_.log RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDatabase_.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log

Application Tier Installation

$INST_TOP/logs/.log

$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppsTechStack.log

$INST_TOP/logs/ora/10.1.2/install/make_.log

$INST_TOP/logs/ora/10.1.3/install/make_.log

$INST_TOP/admin/log/ApplyAppsTechStack.log

$INST_TOP/admin/log/ohclone.log

$APPL_TOP/admin/$CONTEXT_NAME/log/installAppl.log

$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppltop_.log

$APPL_TOP/admin/$CONTEXT_NAME/log//adconfig.log

$APPL_TOP/admin/$CONTEXT_NAME/log//NetServiceHandler.log

Inventory Registration

$Global Inventory/logs/cloneActions.log

$Global Inventory/logs/oraInstall.log

$Global Inventory/logs/silentInstall.log

F. Other log files in R12

1) Database Tier

1.1) Relink Log files

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log

1.2) Alert Log Files

$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

1.3) Network Logs

$ORACLE_HOME/network/admin/$SID.log

1.4) OUI Logs

OUI Inventory Logs :

$ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs

2) Application Tier

$ORACLE_HOME/j2ee/DevSuite/log

$ORACLE_HOME/opmn/logs

$ORACLE_HOME/network/logs

Tech Stack Patch 10.1.3 (Web/HTTP Server)

$IAS_ORACLE_HOME/j2ee/forms/logs

$IAS_ORACLE_HOME/j2ee/oafm/logs

$IAS_ORACLE_HOME/j2ee/oacore/logs

$IAS_ORACLE_HOME/opmn/logs

$IAS_ORACLE_HOME/network/log

$INST_TOP/logs/ora/10.1.2

$INST_TOP/logs/ora/10.1.3

$INST_TOP/logs/appl/conc/log

$INST_TOP/logs/appl/admin/log

UNIX & AIX

1. Kill all the O.S process of a particular user | kill -9

Want to kill all the O.S process that are running under particular user.

UNIX$ ps -aef  | grep -i shaik | awk '{print $2}' | sed 's/^/kill -9 /' > 1.txt
UNIX$./1.txt

2. Copy file from source to destination when size > 0 bytes

#!/bin/ksh
wdate=`date '+%Y-%m-%d'`
wdate1=`date '+%m%d%y'`
logdate=`date '+%m%d%y%H%M%S'`
source=/your source/$wdate
destination=/yourdestination
log_dir=/loglocations/log
log=$log_dir/logfilename_$wdate1.log
while true
do
  if [ -d "$source" ]
  then
echo $source
echo $source/*.csv
      if [ ! -s "$source/*.csv"  ]
      then
         echo " File exists- copying the File to destination @$logdate" >> $log
         cp  $source/*.csv $destination/logfilename_$wdate1.csv
         echo " copied the file..exiting now  $logdate" >>$log
         exit 0
      else
   echo "sleepping ..."
          sleep 10
      fi
  fi
echo $wdate
done

3. find files within directories

find . -type f -exec grep -il "hello" {} \;

or if you know the file type

find . -type f -name "*.ext" -exec grep -il "hello" {} \

4. find number of cpu

On Solaris
psrinfo -v
or
/usr/platform/sun4u/sbin/prtdiag -v

5. use truss to find the process execution

$ truss -aefo logfile
$ truss -rall -wall -p

make sure the logfile is stored where enough size is available.

6. UNIX prstat vs top

While digging for problems in UNIX prstat comes handy in looking at the some of the statistics of the server.

prstat - report active process statistics

example:

Now
statistics are sorted by cpu high -low
prstat -s cpu
or -u for a particular user
prstat -u oracle -s cpu

or top 5 cpu events
prstat -s cpu -n 5

or
prstat -T (total)

Other options available to prstat are:

  -a    Report information about processes and users

-T Report information about processes and tasks.

  -u    Report only processes whose effective user  ID

  -v    Report  verbose  process  usage

  -s key            Sort output lines (that is, processes, lwps, or users)            by  key  in descending order. Only one key can be used            as an argument.             There are five possible key values:             cpu   Sort by process CPU usage. This is the default.             pri   Sort by process priority.             rss   Sort by resident set size.             size  Sort by size of process image.
-n ntop[,nbottom] (comes with ntop or nbottom)

7. Unix memory leak issues.

If several crashes occur that have an associated large core file, then this is a good indication of a potential memory leak.


At any stage if you want to generate the core dump of an active process:

On Solaris:
-0 option is to manually give the path and filename for the core dump
gcore -o /obishared/obiqb/Lognode2/core.19199 19199
gcore: /obishared/obiqb/Lognode2/core.19199.19199 dumped




Start collecting memory consumption statistics.
Solaris

The prstat command can be used to gather basic performance data to help identify if there are any processes consuming a large amount

of memory. For example, the data below is sorted by the SIZE column. SIZE is the total virtual memory size of the process:

prstat -s size


AIX

The ps command can be used to show basic memory usage per process. For example, the data below is sorted by the VSZ column, the total

virtual memory size of the process in KB:

ps -efo "vsz,pid,user,cpu,thcount,comm" | sort –n


HP-UX

The top command can be used to show basic memory usage per process. For example, the SIZE column is the total virtual memory size of

the process in KB:

top

Linux

The ps command can be used to show basic memory usage per process. For example, the data below shows the memory being used by all the

siebmtshmw proceses in KB:


ps axo user,pid,vsz,thcount,cmd | grep siebmtshmw






If you are experiencing some of the symptoms described above or if you suspect a memory leak, it is particularly important that

performance data is captured so the memory leak can be confirmed. It is important to gather at least the following pieces of

information:


Process ID (PID)
Size of the process
The executing command
The timestamp information showing exactly when the data was captured.


One method of capturing this data is to use a shell script. The following are examples of shell scripts that can be used for the

various OS types:

Solaris

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -eo pid,vsz,rss,pcpu,args | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done

AIX

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -efo "pid,vsz,user,cpu,thcount,comm" | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done

HP-UX

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -elf | grep $pid >> ps.log.$pid
done
sleep 30
done


Linux


while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps axo pid,vsz,user,%cpu,thcount,cmd | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done


above are snippets from MOS.
References:

Note 477520.1 "How To Troubleshoot Siebel Server Component Crashes on UNIX". It is however possible for a process to crash as a result of a memory leak.
Note 477004.1 "How Can Users Prevent Core Files from Being Overwritten on UNIX Platforms?"

Backup & Recovery

1. RMAN switch database to copy

RMAN image copy .. switching database to image copy backup.


>r sql
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 23 15:09:10 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size                  2041368 bytes
Variable Size            1258297832 bytes
Database Buffers          553648128 bytes
Redo Buffers               14729216 bytes
Database mounted.


labrman01(MYDB)  /ora_backup/backups/MYDB
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 23 15:10:13 2010

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

RMAN> connect target /

connected to target database: MYDB (DBID=2563884143, not open)

RMAN> connect catalog rcat10g/rcat10g@rcat;

connected to recovery catalog database

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp"
datafile 2 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm"
datafile 3 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr"
datafile 4 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib"
datafile 5 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij"
datafile 6 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm"
datafile 8 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm"
datafile 9 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib"
datafile 10 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq"
starting full resync of recovery catalog
full resync complete


RMAN>  run{
2> set until time "to_date('08/23/2010 15:00:00','mm/dd/yyyy hh24:mi:ss')";
3> recover database;
4> }

executing command: SET until clause

Starting recover at 23-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1092 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00010: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc
archive log thread 1 sequence 7 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000007_0727545786.arc
archive log thread 1 sequence 8 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000008_0727545786.arc
archive log filename=/adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-AUG-10


RMAN> run{
2> sql 'alter database open resetlogs';
3> }

sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
761     774     MYDB     2563884143       PARENT  1          29-OCT-07
761     762     MYDB     2563884143       PARENT  10513166137703 21-JUL-10
761     4636    MYDB     2563884143       PARENT  10513166276407 20-AUG-10
761     5187    MYDB     2563884143       PARENT  10513166280927 20-AUG-10
761     6336    MYDB     2563884143       PARENT  10513166285247 20-AUG-10
761     6786    MYDB     2563884143       CURRENT 10513166407407 23-AUG-10
761     1681    MYDB     2563884143       ORPHAN  10513167403561 20-AUG-10

2.ORA-16038: log sequence# cannot be archived

After the 3 node - 2node RAC Apps database clone one of our DBA forgot to disable the unused thread#3.

i.e Our Prod was 3-node RAC hence three threads for redologs where as our QA system is two node RAC and the 3rd thread should be disabled ( No third node to capture the logs for thread#3) if not during the RMAN backup you will get the below errors.



Starting backup at 31-AUG-12
ORACLE error from target database:
ORA-16038: log 3 sequence# 171 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+DBNAME_FRA_01/DBNAME/onlinelog/group_3.4609.790630963'
ORA-00312: online log 3 thread 1: '+DBNAME_FRA_01/DBNAME/onlinelog/group_3.4547.790630981'

Fix:

Login into the DB:

SQL>  select inst_id,open_mode from gv$database;

   INST_ID OPEN_MODE
---------- --------------------
         1 READ WRITE
         2 READ WRITE


SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          2 UNUSED
         5          2 UNUSED
         6          2 UNUSED
         7          3 UNUSED
         8          3 UNUSED
         9          3 UNUSED
        10          3 UNUSED

10 rows selected.

SQL> alter database disable thread 3;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 9;

Database altered.

SQL> alter database drop logfile group 10;

Database altered.

SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          2 UNUSED
         5          2 UNUSED
         6          2 UNUSED

6 rows selected.

Now restart the RMAN archivelog backup:


Starting backup at 31-AUG-12
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=790830466
input archived log thread=1 sequence=2 RECID=2 STAMP=790851323
input archived log thread=1 sequence=3 RECID=3 STAMP=790893321

3. RMAN-06183: datafile or datafile copy (file number ) larger than MAXSETSIZE

One of our database backups were failing, when looked at the backup log.

RMAN>
connected to target database: MYDB (DBID=1218259347)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
allocated channel: dsk1
channel dsk1: SID=1136 instance=MYDB1 device type=DISK

allocated channel: dsk2
channel dsk2: SID=1419 instance=MYDB1 device type=DISK

Starting backup at 29-NOV-11
released channel: dsk1
released channel: dsk2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/29/2011 16:00:45
RMAN-06183: datafile or datafile copy +DATA_01/MYDB/datafile/xxusa_data.307.757873839 (file number 40) larger than MAXSETSIZE

RMAN>


From the above error message it is evident that MAXSETSIZE is set at the RMAN level.

So to verify this:

unixhost(catalogdb) /export/home/oracle/sshaik
>rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 1 11:46:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target rmanbackup/@diskqb1

connected to target database: diskQB (DBID=1218259347)

RMAN> connect catalog rcat_oth/@catalogdb

connected to recovery catalog database

RMAN> show all;

RMAN configuration parameters for database with db_unique_name diskQB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
----
CONFIGURE MAXSETSIZE TO 30720 M;

Here we go so we have the max file size limit of 30g where as my file size is around 32 g. So to fiz this...

RMAN> CONFIGURE MAXSETSIZE clear
2> ;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 30720 M;
RMAN configuration parameters are successfully reset to default value
starting full resync of recovery catalog
full resync complete

RMAN> show all;

RMAN configuration parameters for database with db_unique_name diskQB are:

==
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
=

4. Unregister database from Recovery Catalog

In Recovery Catalog Database:
If the target database already deleted/dropped/decommissioned or not available
Then in 9i/10g
SQL> alter session set current_schema=RCAT10G_OTH;

Session altered.

SQL> execute dbms_rcvcat.unregisterdatabase(1,129982387);

PL/SQL procedure successfully completed.

If Target database is available and UP, Then in 10g

rman>connect target id/pwd@dbname
rman>connect catalog id/pwd@catalogdb

RMAN> UNREGISTER DATABASE ;

set linesize 1000
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

5. Picked up the above from google:

Size of the redo log file:

SQL> SELECT distinct(to_char((bytes*0.000001),'9990.999')) size_mb
FROM v$log;
2
SIZE_MB
---------
1073.742


size & number of redologs by day:

SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024/1024/1024) TOTAL_SIZE_GB,
to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size
order by 1 desc
/

6.

delete force noprompt archivelog all completed before 'sysdate-7';

If you want to delete archive logs older than few hours:

Verify the time like how many hours older than you want to delete.

SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate-1/11 from dual;


SYSDATE-1/11
-------------------
09/22/2011 08:33:05

SQL> SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
09/22/2011 10:44:15

SQL>

So sysdate-1/11 will delete the files older than 8:30 am.

RMAN>connect target /
delete noprompt archivelog until time '(sysdate-1/11)' backed up 1 times to device type sbt_tape;

or

RMAN> delete force noprompt obsolete;
RMAN> delete force noprompt expired;
cheers.