Upgrading 11i EBS 9205 DB to 10g R2 as a part of upgrade to R12

1. sql > startup

2. sql > CREATE TABLESPACE sysaux DATAFILE '/dbf/dbf/sysaux01.dbf'

SIZE 500M REUSE

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

ONLINE;

 

3. sql > shutdown

 

4. sql > startup upgrade

 

5. sql > spool upgrade.log

 

6. sql > @catupgrd.sql

 

7. sql > @utlu102s.sql

 

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

 

 

 

what happens when flash_recovery_area is full

I encountered this error while bringing up the database.

ORA-16014: log 2 sequence# 240 not archived, no available destinations

This was because the log_archive_dest_10 or flash_recovery_area is full. I checked the v$recovery_file_dest and the space allocated and used were equal and are of 7G in size.
Space reclaimable is zero.

Using RMAN i did the following:

RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=131 devtype=DISK

validation failed for archived logarchive log filename=D:\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2006_08_25\O1_MF_1_69_2GXRYZ3S_.ARC recid=32 stamp=599419203validation failed for archived log
............similaryly it gave validation error for all the archive log files as I have deleted them from the operating system level.

I then ran the following...

RMAN> delete expired archivelog all;
released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=131 devtype=DISK
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive logarchive log filename=D:\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2006_08_25\O1_MF_1_69_2GXRYZ3S_.ARC recid=32 stamp=599419203

............ like this it deleted 134 objects I deleted. The space used in the v$recovery_file_dest has come down gradually.

SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
D:\flash_recovery_area 7147483648 7140811776 0 162

SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
D:\flash_recovery_area 7147483648 473303552 0 22

Oracle Apps 11i + R12 Concepts

The Oracle Applications technology layer lies between the Oracle Applicationstechnology stack and the Oracle Applications product-specific modules. It providesfeatures common to all Oracle Applications products.

The Applications DBA (AD) product provides a set of tools for administration of the OracleApplications file system and database. AD tools are used for installing, upgrading,maintaining, and patching the Oracle Applications system.

AK (Common Utilities) is an active data dictionary that enables you to define Oracle Applications components for the HTML-based Applications, and generate many of the characteristicsof Oracle Applications at runtime.The Oracle Common Modules can be used to develop inquiry applications for theHTML-based Applications, without the need for any programming. They allow storageof language-translated labels for all the attributes on a transaction page, thus assistingwith the provision of support for multiple languages.

AU(Application utilities) hosts a collection of files copied from other products. This allows generating on-site classes of files such as Forms and reports. Generating forms or reports may requireaccess to shared PL/SQL libraries, so these files are copied to AU_TOP as well.

Oracle Application Object Library (FND)
The Oracle Application Object Library is a key component of the Applications
technology layer. It consists of a collection of reusable code, programs, and database objects that provides common functionality across all products.

Responsibility
A responsibility is a level of authority in Oracle Applications that lets Applications users access only those functions and data appropriate to their roles in the organization.

Oracle Workflow (OWF)
Oracle Workflow delivers a complete workflow management system that supports business process based integration. Its technology enables modeling, automation, and continuous improvement of business processes, routing information of any type according to user-defined business rules. Oracle Workflow also provides an infrastructure for the enterprise-wide communication of data related to defined business events.

Read again...WF support Business process based integration, automation, routing of information as per user-defined rules and enterprise-wide data communication.

Directory structure in Oracle Applications R12

DB SERVER:
apps_st --(Data_top) (This has all the dbf’s)
tech_st -- 10.2.0 Home (Oracle Binaries)


APPS SERVER

Apps_st -- Appl_top(Oracle apps Product tops) && Common_top (Log, out, HTML files)

Tech_st -- 10.1.2 10.1.3 (Ora_home for tech stack)

The apps/apps_st/comn (COMMON_TOP) directory contains files used by manydifferent Oracle Applications products, and which may also be used with third-partyproducts.The admin directory, under the COMMON_TOP directory, is the default location forthe concurrent manager log and output directories.COMMON_TOP/html: The OA_HTML environment setting points to the html directory. The Oracle Applications HTML-based sign-on screen and Oracle HTML-based Applications HTMLfiles are installed here. The html directory also contains other files used by theHTML-based products, such as Java Server Page (JSP) files, Java scripts, XML files, andstyle sheets. Typically, the path will look like:/appmgr/apps/apps_st/comn/webapps/oacore/html. Two newsubdirectories are META-INF and WEB-INF, introduced to meet J2EE specifications.
The java directoryRelease 12 introduces some significant changes to the locations in which the varioustypes of Java files are stored. Rapid Install installs all Oracle Applications class files inthe COMMON_TOP/classes directory, pointed to by the $JAVA_TOP environmentvariable. Zip and jar files are installed in the $COMMON_TOP/lib directory, pointed toby the $AF_JLIB environment variable (introduced with Release 12). The top-level Javadirectory, $COMMON_TOP/java, is pointed to by the $JAVA_BASE environmentvariable.
# APPLTMP is the directory in which Oracle Applications# temporary files are created.#
APPLTMP="/apps/xxx/xxxcomn/temp"export APPLTMP
## APPLPTMP is the directory in which PL/SQL output files are created.#
APPLPTMP="/apps/xxx/xxxdb/9.2.0/temp/xxx_essdbpu41"export APPLPTMP
APPLCSF is the top-level directory in which the Concurrent Manager# puts log and output files.
FNDNAM is the name of your AOL schema.
GWYUID is the schema name and password for your public schema.
# National Language Support environment variables#
NLS_LANG="American_America.UTF8"export NLS_LANG
NLS_DATE_FORMAT="DD-MON-RR"export NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS=".,"export NLS_NUMERIC_CHARACTERS
NLS_SORT="binary"export NLS_SORT
# Add perl from the iAS location to the PATH and set PFER5LIB for Perl
Get customer-defined environment variable settings#. /apps/xxx/xxxappl/admin/adovars.env
The main applications environment file, called .env on UNIX,and .cmdon Windows.
The consolidated environment file, called APPS.env on UNIX,and APPS.cmd on Windows.
CONTEXT_NAME is the Applications context, Its default value is _.
APPSORA.env or APPS.env are both consolidated env files which calls the 8.0.6 env, CONTEXT_NAME.env files together with the custom environment env files.
There is also the file $APPL_TOP/admin/applsum.txt, applptch.txt which is more of a summary of the same information
To get the complete patch details for patches applied in Dec 2000$ cd $AD_TOP/patch/115/sql$ sqlplus /SQL> @adphrept.sql 3 ALL ALL 12/01/00 12/31/00 ALL ALL ALL ALL ALL N N N N N
$AD_TOP/sql/adcompsc.pls apps apps PA (This will compile all the objects in Apps starting with PA% alter java class “/584c2e1f_FunctionManager1” compile;
If you have invalid SYSTEM or SYS objects you can use $ORACLE_HOME/rdbms/admin/utlrp.sql to compile just these invalid objects
$ adpatch apply=no
$ adpatch preinstall=y
$ adpatch options=nogenrep,nogenrpll

Following are some examples of commands in a ‘g’ driver. Once again, remember that
· The .fmb files are stored in $AU_TOP, and when generated the executable (.fmx) is stored under the product.
· The .pll is stored under $AU_TOP, and when generated the executable (.plx) is also stored under $AU_TOP
CONCURRENT MANAGER
If the ICM should go down, you can restart it with 'startmgr'. You do not need to kill the other managers first
FND_COCNURRENT_PROCESSES and FND_CONCURRENT_QUEUES, FND_CONCURRENT_REQUESTS FND_CONCURRENT_PROGRAMS update fnd_concurrent_requestsset status_code='X', phase_code='C'where status_code='T';If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
The fnd (Application Object Library) directoryThis directory and its subdirectories contain the scripts and programs that are used asthe foundation for all Applications products to build data dictionaries, forms and Cobject libraries.
Most temporary files are written to the location specified by the APPLTMPenvironment setting, which is set by Rapid Install.Applications also produces temporary PL/SQL output files used in concurrentprocessing. These files are written to a location on the database server node specified bythe APPLPTMP environment setting. The APPLPTMP directory must be the samedirectory as specified by the utl_file_dir parameter in your database initialization file.
The adovars.env fileThe adovars.env file, located in $APPL_TOP/admin, specifies the location of variousfiles such as Java files, HTML files, and JRE (Java Runtime Environment) files. It iscalled from the main applications environment file, .env.

Oracle Apps 11.5.10 with 10g RAC

I had tough time gathering stuff (both increasing my Laptop RAM and getting time and energy). I had spent two weeks to get 10g RAC working with Oracle apps 11i. I upgraded 9.2.05 of 11i to 10g R2. Moved the dbf's to the shared storage location....More things tomorrow

Oracle 10g RAC on Solaris 10

I had been longing to do this..May be because of lack of resources or learning, I couldn't do it all these days. Honestly, I was searching for a way to get the shared storage work on my Laptop.

I came across iSCSI (OpenFiler) and immediately tried to get Solaris working with it. I downloaded the pre-installed version of Solaris 10 from Sun site. Its pretty good and I bet you would keep gazing at the look and feel of the Java console. pretty decent.

I configured two solaris boxes and did some work using iscsiadm on solaris. Before this, I added the solaris nodes IP's in the openfiler console to allow them to access the shared devices.

And Bingo!!! I could see the partitions on the solaris nodes.

Fortunately or unfortunately, there was a power outage at my home, and both solaris boxes which were on external disk crashed...Resultingly, they were not coming up fine, and ran into maintenance mode. Wasted an hour running fsck and svcs clear boot archive. They are breathing fine now. I am going to install 10g RAC tomorrow. I need to configure raw devices for OCR and VOTING DISK and use ASM option for dbfs.

My second stint with Oracle 10g R2 RAC on RHEL4 was a bit tiresome and a lot of learning though. Earlier when I installed RAC 10g on RHEL4 I had learnt of configuring the shared storage in VMWARE. and the options to use to allow both nodes to share the common disk.set this parameter in both of the vmx files of the vmware--- disk.locking="FALSE" and there are other parameters for disklibcache etc. But disk.locking alone worked for me fine. I used GSX 3.2.1 on windows 2003 server and OCFS2 for OCR and VOTING DISK, and datafiles.
I had tried the option of having the shared oracle_home and crs_home and was successful. The problem I faced while configuring shared disks, the UUID of the shared disk (that we can see in the OCFS2CONSOLE) was not matching between the two nodes and resultingly the nodes were treating the shared disks as individual disks. I realised this later when I ran mounted.ocfs2 -d which showed two different things on both nodes.
So what I learnt was to unload,offline and stop the ocfs2 cluster stack using /etc/init.d/o2cb unload,offline and stop and restart again. Also there is a link between the MAC address of the NIC cards with o2cb. so once when we start the o2cb, there should not be any change in the IP or MAC address. else, we get the 'Transport end point reached error'.
First start the o2cb cluster on the first node selecting 'configure nodes' and if we dont get the values of the nodes and their ips on the second node when we select 'configure nodes', then copy the cluster.conf in /etc/ocfs2 to the second node. this should resolve the issue. Then start the cluster stack on the second node. OCFS2 hearbeat would show offline at this moment as we haven't yet formatted and mounted the shared disks. Once we format and mount the shared disk on first node, we should see the /dev/sdx on the second node as well. Dont forget to match the UUID and running mounted.ocfs2 -d.

Few notes on space reorg

Note:303709.1 : Reclaiming unused space in APPLSYSD tablespace

Note 267132.1 "Hints and Tips Running Synchronize Workflow Local Tables With 11i.OWF.G/11.5.9"

Note 294861.1 : "Synchronize WF Tables Gives Ora-1653 Trying to Extend

Note 298698.1 "Avoiding abnormal growth of FND_LOBS table"

Note 189800.1 "FND Related Tablespaces Growing at Rapid and Excessive Rate

Note 77635.1 "How to Determine Real Space used by a Table (Below the High Water Mark)"

Note 1029252.6 "How to Resize a Datafile" to reduce the size of the datafile.

Note 130866.1 "How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark" to find the minimum datafile size you can get.

Note 230627.1 "9i Export/Import Process for Oracle Applications Release 11i".

Note 1019709.6 Script to Report Tablespace Free and Fragmentation

Note 267132.1 Hints and Tips Running Synchronize Workflow Local Tables With 11i.OWF.G/11.5.9

Note 294861.1 Synchronize WF Tables Gives Ora-1653 Trying to Extend WF_LOCAL_ROLES_STAGE or WF_LOCAL_USER_ROLES _STAGE Tables


Note 298698.1 Avoiding abnormal growth of FND_LOBS table

Note 189800.1 FND Related Tablespaces Growing at Rapid and Excessive Rate

Note 77635.1 How to Determine Real Space used by a Table

Note 115586.1 How to Deallocate Unused Space from a Table, Index

Note 224027.1 Objects Created When Creating a Queue table

Note 304522.1 How to Move Queue Tables without using export import

Note 130814.1 How to move LOB Data to Another Tablespace

Note 1029252.6 How to Resize a Datafile" to reduce the size of datafile.

Note 130866.1 How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark

Note 1019474.6 Script: To Create Tablespace Block Map

Note 230627.1 9i Export/Import Process for Oracle Applications Release 11i

Note 269291.1 Oracle Applications Tablespace Migration Utility User Documentation