TECH SOLUTIONS

Friday, September 27, 2013

Clone an Oracle database using a cold backup..

This procedure will clone a database using a cold copy of the source database files. If a cold backup of the database is available, restore it to the new location and jump to step 2.

1. Identify and copy the database files
With the source database started, identify all of the database's files. The following query will display all datafiles, tempfiles and redo logs:
```
set lines 100 pages 999
col name format a50
select name, bytes
from    (select name, bytes
 from v$datafile
 union all
 select name, bytes
 from  v$tempfile
 union  all
 select  lf.member "name", l.bytes
 from v$logfile lf
 , v$log l
 where lf.group# = l.group#) used
, (select sum(bytes) as poo
 from dba_free_space) free
/
```
Make sure that the clone databases file-system is large enough and has all necessary directories. If the source database has a complex file structure, you might want to consider modifying the above sql to produce a file copy script.

Stop the source database with:
```
shutdown immediate
```
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

Start the source database up again
```
startup
```
2. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:
```
create pfile='init<new database sid>.ora' from spfile;
```
This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Note. Pay particular attention to the control locations.
3. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
```
alter database backup controlfile to trace as '/home/oracle/cr_<new sid>.sql'
/
```
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
- Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
- Remove any lines that start with --
- Remove any lines that start with a #
- Remove any blank lines in the 'CREATE CONTROLFILE' section.
- Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
- Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
- If the file paths are being changed, alter the file to reflect the changes.
Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
```
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 453
LOGFILE
  GROUP 1 '/u03/oradata/dg9a/redo01.log'  SIZE 100M,
  GROUP 2 '/u03/oradata/dg9a/redo02.log'  SIZE 100M,
  GROUP 3 '/u03/oradata/dg9a/redo03.log'  SIZE 100M
DATAFILE
  '/u03/oradata/dg9a/system01.dbf',
  '/u03/oradata/dg9a/undotbs01.dbf',
  '/u03/oradata/dg9a/cwmlite01.dbf',
  '/u03/oradata/dg9a/drsys01.dbf',
  '/u03/oradata/dg9a/example01.dbf',
  '/u03/oradata/dg9a/indx01.dbf',
  '/u03/oradata/dg9a/odm01.dbf',
  '/u03/oradata/dg9a/tools01.dbf',
  '/u03/oradata/dg9a/users01.dbf',
  '/u03/oradata/dg9a/xdb01.dbf',
  '/u03/oradata/dg9a/andy01.dbf',
  '/u03/oradata/dg9a/psstats01.dbf',
  '/u03/oradata/dg9a/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/dg9a/temp01.dbf'
     SIZE 104857600  REUSE AUTOEXTEND OFF;
```
4. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
```
echo $ORACLE_SID
```
If this doesn't output the new database sid go back and investigate.
5. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):
```
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>
```
5. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
```
sqlplus "/ as sysdba"
@/home/oracle/cr_<new database sid>
```
It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:
```
ORA-01113: file 1 needs media recovery
```
You probably forgot to stop the source database before copying the files. Go back to step 1 and recopy the files.
```
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl'
ORA-27038: skgfrcre: file exists
```
Double check the pfile created in step 2. Make sure the control_files setting is pointing at the correct location. If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.
6. Perform a few checks
If the last step went smoothly, the database should be open. It is advisable to perform a few checks at this point:
- Check that the database has opened with:
```
select status from v$instance;
```
  The status should be 'OPEN'
- Make sure that the datafiles are all ok:
```
select distinct status from v$datafile;
```
  It should return only ONLINE and SYSTEM.
- Take a quick look at the alert log too.
7. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:
```
alter database rename global_name to <new database sid>
/
```
8. Create a spfile
From sqlplus:
```
create spfile from pfile;
```
9. Change the database IDIf RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
From sqlplus:
```
shutdown immediate
startup mount
exit
```
From unix:
```
nid target=/
```
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
```
shutdown immediate
startup mount
alter database open resetlogs
/
```
10. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.
11. Finished

Clone Oracle Apps R12 Short Steps

Pre-Clone Steps:

On the DB server:

· cd $ORACLE_HOME/appsutil/scripts/

· perl adpreclone.pl dbTier

· Check the log file under

· $ORACLE_HOME/appsutil/log//StageDBTier_06201503.log

On the apps tier server:

· cd $ADMIN_SCRIPTS_HOME

· perl adpreclone.pl appsTier

· Check the log file $APPL_TOP/admin/log/

Clone Steps:

Copy the source apps tier file system to the target system.

· APPL_TOP.

· COMMON_TOP

· ORACLEAS_10.1.2_ORACLE_HOME

· ORACLEAS_10.1.3_ORACLE_HOME

Copy source system database tier file system to the target system. As user oracle:

· Shutdown normal the source system database.

· Copy the database (.dbf) files plus ORACLE_HOME to the target system.

On the target Database system

· cd $ORACLE_HOME/appsutil/clone/bin

· perl adcfgclone.pl dbTier

· Verify errors the log $ORACLE_HOME/appsutil/log/

On the target apps system

· $cd $COMMON_TOP/clone/bin

· $perl adcfgclone.pl appsTier

· Verify the errors the log. $APPL_TOP/admin//log

· Once clone is finished. If you have customized environment then change post clone profile options, utl_file_dir etc.

Wednesday, September 18, 2013

ALL CHECKS

# Database Version

SQL> desc v$version

SQL> select banner from v$version;

external hard disk mount
mount -t ntfs-3g /dev/sdb1 /prasad123

For unmount
umount -t ntfs-3g /dev/sdb1 /prasad123

create password file at dbs
orapwd file=orapwTESTC password=sys@123 ignorecase=y

Log as sysdba using created password file for test purpose

sqlplus sys/sys@123@TEST as sysdba

# $cd /u01/apps/oracle/oradata/
$ tar czf /home/oracle/ora10g.tar.gz ora10g
$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1
$ cd /u01/apps/oracle/product/10.2.0/
$ tar czf /home/oracle/oraHomeBackup.tar.gz db_1
$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1

$ cd /u01/apps/oracle/product/10.2.0/
$ rm -fr db_1
$ tar xzf /home/oracle/oraHomeBackup.tar.gz *
$ cd /u01/apps/oracle/oradata/
$ rm -fr ora10g
$ tar xzf /home/oracle/ora10g.tar.gz *

# Change password with fndcpass

FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN sysadmin

# $ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1

# gzip file.txt
For Expand
gunzip file.txt.gz

# CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

# expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

# expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

# expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

# SELECT current_scn FROM v$database;
SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual

# SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
SELECT SCN_TO_TIMESTAMP(5474751) FROM dual;

# adpatch options=nocompiledb

# select home_url from apps.icx_parameters;
SQL> select home_url from icx_parameters;

# how to create soft links
ln -s /webroot/home/httpd/test.com/index.php /home/vivek/index.php
ls -l

# select * from dba_datapump_jobs;

# how to check temporary tablespace

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;

# alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;

# alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp01.dbf' resize 100M;

alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp08.dbf' resize 1G;

# ALTER TABLESPACE lmtbsb
   ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;

# CREATE TABLESPACE TEMP3 DATAFILE '/R12/oracle/SHIV/db/apps_st/data/temp03.dbf' SIZE 250M;

# ALTER TABLESPACE tbs_03
    ADD DATAFILE 'tbs_f04.dbf'
    SIZE 100K
    AUTOEXTEND ON
    NEXT 10K
    MAXSIZE 100K;

# ALTER TABLESPACE tbs_03
    DROP DATAFILE 'tbs_f04.dbf';

# ALTER TABLESPACE undots1
RETENTION NOGUARANTEE;

# ALTER TABLESPACE undots1
RETENTION GUARANTEE;

# Temporary Datafiles

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:

    Tempfiles are always set to NOLOGGING mode.

    You cannot make a tempfile read-only.

    You cannot rename a tempfile.

    You cannot create a tempfile with the ALTER DATABASE command.

    Media recovery does not recognize tempfiles.

        BACKUP CONTROLFILE does not generate any information for tempfiles.

        CREATE CONTROLFILE cannot specify any information about tempfiles.

    Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or V$DATAFILE.

CREATE temporary TABLESPACE DT_TEMP
tempfile '/u02/dbs/RIDEV/temp01.dbf' SIZE 320M
extent management local
uniform size 8M

# path for data R12
/R12/oracle/SHIV/db/apps_st/data

# select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name

# select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name

# CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf'      SIZE 50M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

# ALTER TABLESPACE lmtbsb
   ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;

# ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
   RESIZE 100M;

# ALTER TABLESPACE temp_demo ADD TEMPFILE 'temp05.dbf' SIZE 5 AUTOEXTEND ON;

# ALTER TABLESPACE temp_demo DROP TEMPFILE 'temp05.dbf';

# All Invalid objects in database.

SELECT COUNT(*) FROM all_objects WHERE Status = 'INVALID';

Free size of database

SELECT /*+ FIRST_ROWS*/ROUND(SUM(bytes/1024/1024/1024)) FROM dba_free_space;

# Total size of Database

SELECT ROUND(SUM(bytes/1024/1024/1024)) FROM dba_data_files;

SELECT count(*) FROM v$session;

# 7. Total no of Process

SELECT COUNT (*) FROM v$process;

# 8. Total no of concurrent Process

SELECT COUNT(*) FROM fnd_concurrent_processes;

# 9. Total no of concurrent request

SELECT COUNT(*) FROM fnd_concurrent_requests;

# Number of users

select user_name,count(*)
from apps.fnd_logins a, apps.fnd_user b
where a.user_id=b.user_id
and a.login_id in
(
select login_id
from apps.fnd_login_responsibilities
where end_time is null
and trunc(start_time) = trunc(sysdate)
)
group by a.user_id, b.user_name;

Friday, September 13, 2013

How I upgrade our Oracle EBS from R12.1.1 to 12.1.3

SkySys - Oracle Applications EBS 12.1.3 Login page

Objective:
To describe the procedure I have followed in upgrading our Oracle EBS Release 12.1.1 to 12.1.3. While the procedure mentioned in this blog are the exact steps I have followed (including the commands issued) the best document to read about the upgrade is Oracle support note 1080973.1.
Our Oracle Server current configuration:-
a. Hardware and host environment:-
> Base server:  Windows 7 Professional 64-bit, CPU = Intel Core i7-2600 @3.40GHz, RAM = 16.0 GB
> VMware platform on top of Windows 7:- VMware Server 2.0.2
> Virtual machine specifications:-
Operating system type = Red Hat Enterprise Linux 4 (32-bit)
HDD space allocated = 400GB
RAM allocated = 8.0 GB
b. Oracle EBS server (current) – Vision instance
hostname = ls1.skysys.com.au (alias “ls1″)
Installation home = /home/oracle/VIS
Oracle Database version = 11.1.0.7
Applications Release name/version = 12.1.1
Upgrade procedure followed:-
1. I have downloaded following patches and extracted into /home/oracle/R12-Patches directory.
9239089
9239090
9817770
9966055
9239095
2. Stopped all application tier services.
$ /home/oracle/VIS/inst/apps/VIS_ls1/admin/scripts/adstpall.sh apps/******
I kept the Oracle Enterprise Manage DB console service running as it helped me monitor the database performance, tablespaces health, etc.
3. Switched the instance into maintenance mode.
$cd /home/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=adadmin.log menu_option=ENABLE_MAINT_MODE workers=4
4. Run adgrants.sql as a pre-requisite for the first patch 9239089
- “adgrants.sql” is a file extracted out of 9239089 patch. Copied this sql script into $ORACLE_HOME/appsutil/admin directory.
$ cd /home/oracle/VIS/db/tech_st/11.1.0/appsutil/admin
$ sqlplus /nolog
SQL> @adgrants.sql apps
—- adgrants.sql executed and database changes committed.
SQL> exit
5. Applied the first patch 9239089.
$ cd /home/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$ . APPSVIS_ls1.env
$adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9239089.log patchtop=/home/oracle/VIS/R12-Patches/9239089 driver=u9239089.drv workers=8
6. Applied the second patch 9239090.
$ cd /home/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$ . APPSVIS_ls1.env
$adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9239090.log patchtop=/home/oracle/VIS/R12-Patches/9239090 driver=u9239090.drv workers=8
The 9239090 patch ran for nearly 12 hours. There was an incident when one of the workers has failed and I have to skip the job (worker) by using “adctl” utility.
7. Applied the next patch 9239095.
$ /home/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$ . APPSVIS_ls1.env
$ adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9239095.log patchtop=/home/oracle/VIS/R12-Patches/9239095 driver=u9239095.drv workers=8
Now moved on to the post update steps:-
8. Applied the mandatory post-install patch 981770.
$ cd /home/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$ . APPSVIS_ls1.env
$ adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9817770.log patchtop=/home/oracle/VIS/R12-Patches/9817770 driver=u9817770.drv workers=8
9. Applied the second mandatory post-install patch 9966055.
$ cd /home/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$ . APPSVIS_ls1.env
$ adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9966055.log patchtop=/home/oracle/VIS/R12-Patches/9966055 driver=u9966055.drv workers=8
10. Updated the DB tier with the EBS 12.1.3 code level
$ cd $APPL_TOP
$ . APPSVIS_ls1.env
> Executed the admkappsutil.pl utility to create the appsutil.zip
$ cd $AD_TOP/bin
$ admkappsutil.pl
> As the outcome of the above script the appsutil.zip got created into the $INST_TOP/admin/out directory.
> Copied appsutil.zip (created in previous step) to $ORACLE_HOME
$ cp $INST_TOP/admin/out/appsutil.zip $ORACLE_HOME
> Unzipped the appsutil.zip at $ORACLE_HOME
$ cd $ORACLE_HOME
$ unzip -o appsutil.zip
> Executed the autoconfig utilities on Database tier:
$ perl $ORCLE_HOME/appsutil/bin/adbldxml.pl
$ cd $ORACLE_HOME/appsutil/bin
$ sh adconfig.sh
11. Executed the adpreclone.pl on database tier and then on the applications tier.
$ cd $ORACLE_HOME
$ . VIS_ls1.env        — sourced database environment
$perl appsutil/bin/adpreclone.pl dbTier
$ cd $APPL_TOP/appl/scripts
$ . /VIS_ls1.env        — sourced application environment
$ perl $INST_TOP/appl/appsutil/bin/adpreclone.pl dbTier
12. Got the environment back to normal from maintenance mode.
$adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=adadmin.log menu_option=DISABLE_MODE workers=4
13. Inquired the Oracle EBS code level to see the upgrade is all well.
$ cd $ORALE_HOME
$ . VIS_ls1.env
$ sqlplus apps/*****
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
———————————————-
12.1.3
13. Restarted the application services.
$ cd $ADMIN_SCRIPTS_HOME
$ sh adstrtal.sh apps/*****

Upgrade 12.1.1 to 12.1.3

After Installation of Oracle Applications R12.1.1,I have upgraded to R12.1.3 Successfully in my Servers. The following patches are required and download from the Oracle Support as per their environment.In this Note I have added some more patches for avoiding the feature errors and for avoiding invalid objects. Before going to proceed please check the metal ink Note Oracle E-Business Suite Release 12.1.3 Readme [ID 1080973.1].
Required patches:
p9239089_R12.AD.B_R12_LINUX.zip
p10349415_R12.AD.B_R12_GENERIC.zip (Optional)
p9239090_R12_LINUX_1of6.zip
p9239090_R12_LINUX_2of6.zip
p9239090_R12_LINUX_3of6.zip
p9239090_R12_LINUX_4of6.zip
p9239090_R12_LINUX_5of6.zip
p9239090_R12_LINUX_6of6.zip
p9239095_R12_GENERIC.zip
p9822544_R12.MSC.B_R12_GENERIC.zip(Optional)
Post update patches are :
p9966055_R12.FND.B_R12_GENERIC.zip
p9817770_R12.ATG_PF.B_R12_LINUX.zip
The following information will provide upgradation process for Ooracle Applications R12.1.1 to R12.1.3.
Step1: Run adadmin and put Maintenance Mode
Step2: Apply Prerequisite R12.AD.B.DELTA.3 Patch 9239089
Do the following Tasks before going to apply the Patch R12.AD.B.DELTA.3 Patch 9239089
Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.
Usage:
1.Create $ORACLE_HOME/appsutil/admin on the database server.
2.Copy adgrants.sql (UNIX) from this patch directory to $ORACLE_HOME/appsutil/admin.Or, copy adgrants_nt.sql (Windows) from this patch directory to %ORACLE_HOME%\appsutil\admin.
3.Set the environment to point to ORACLE_HOME on the database server.
4.Use SQL*Plus to run the script:
UNIX:
$ sqlplus /nolog
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql
Step3: Apply R12.AD.B_R12_GENERIC.zip Patch 10349415
Step4: Apply Oracle E-Business Suite Release 12.1.3 Patch 9239090
Step5:. Apply Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack patch 9239095
Step6:.Apply Patch 9822544 for Advanced Supply Chain Planning. (Optional).This patch fixes the issue of collecting resource requirements of OPM batches WIP warehouse does not belong to resource warehouse.
Step7: Apply post Update Patches:
–Apply mandatory Patch 9817770 (9817770:R12.ATG_PF.B [POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATEDPATCH].)
–Apply mandatory Patch 9966055 (9966055:R12.FND.B [TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED].)
Notice
After you have upgraded to Oracle E-Business Suite Release 12.1.3, perform the following post-update steps to update all database tier nodes with the code level, provided by Oracle E-Business Suite Release 12.1.3Step8: Run Application and Database Environment files.
@Application Tier:
Run AutoConfig on the APPL_TOP.
Run the admkappsutil.pl utility to create the file appsutil.zip in the /admin/out directory.
perl /bin/admkappsutil.pl@Database Tier:
Copy or FTP the appsutil.zip file to the . Uncompress appsutil.zip under the .
cd
unzip -o appsutil.zip
Run AutoConfig on the .
Step9:. Run adpreclone.pl on the database tier and the application tier
perl adpreclone.pl dbTier
perl adpreclone.pl appsTier
Step10: Disable Maintenance Mode using adadmin and check the version using following sql statement.
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
————————————————–
12.1.3

Oracle R12 Vision Demo Upgrade to 12.1.3 Successful

How to upgrade Oracle EBS R12.1.1 to 12.1.3

Here are the complete guidelines to upgrade Oracle Ebusiness Suite to 12.1.3 from 12.1.1. Before upgrade our database version was 11.1.0.7.

Although we have followed the doc Oracle E-Business Suite Release 12.1.3
Readme [ID 1080973.1], we have done some changes in the steps for successful upgrade.

Oracle Apps 12.1.3 upgrade is only possible if your system is in 12.1.1 or upper version. If you have not upgraded your EBS to 12.1.1 version you can follow the following article to do so.

how-to-upgrade-oracle-ebs-r1206-to-1211

Prerequisite Steps

Step 1

At first make some changes in the database parameters for upgrade. You need to set the following parameters using initialization file.

recyclebin=false

_pga_max_size=104857600

_disable_fast_validate = TRUE

Use the following kind of comand to changes the mentioned three parameters.

alter system set "_disable_fast_validate"=FALSE SCOPE=BOTH;

[If you don't change the _pga_max_size to greater than 104857600. You may get the following error during upgrade.

ORA-04030: out of process memory when trying to allocate 822904 bytes

(pga heap,kco buffer)

ORA-07445: exception encountered: core dump [dbgtfdFileWrite()+48]

[SIGSEGV] [ADDR:0xFFFFFFFF7FFC1C88] [PC:0x1063BD2D0] [Address not mapped

to object] []]

For additional information check the Document 761570.1

Step 2

Apply the specific database patches as applicable for your database by following Database Preparation Guidelines for an E-Business Suite Release 12.1.1 Upgrade [ID 761570.1] .

[Our oracle inventory was corrupted, so, I have created a local inventory for opatch application and applied the patches. For opatch issues you can check the following articles.

some-common-opatch-application-issue

oracle-inventory-configuration-during

Additionally you can apply the opatch with no_inventory option in this situation.

opatch apply no_inventory]

Step 3

If you are already upgraded to 12.1.1 version, you have already upgraded form tier and web tier. So, no action is required for form and web tier upgrade. But, you can always check the latest doc for form and web tier latest patches.

Main Upgrade Patch Application

Step 4

Download the following two patches from metalink 9239089, 9239090.

Using autopatch utility first apply R12.AD.B.DELTA.3 Patch 9239089. Then apply the main upgrade patch 9239090.

The main patch application has taken more than three days to complete in our environment. Our system is 8 CPU solaris box with 16 GB RAM. The main patch failed two times with error. We have fixed the errors using adctrl utility and resumed the patch application.

Post upgrade steps

Step 5

Apply the following two patches immediately after upgrade 9817770, 9966055.

Step 6

Source the application env file. Then run autoconfig in apps tier.

$ $ADMIN_SCRIPTS_HOME/adautocfg.sh

Now generate the appsutil.zip file.

$ perl /bin/admkappsutil.pl

Copy appsutil.zip file in DB_HOME. Unzip the appsutil.zip file.

After sourcing the database env file run autoconfig in database tier.

Step 7

Now run preclone in db tier first, then in apps tier.

perl adpreclone.pl dbTier

perl adpreclone.pl appsTier

Step 8

Apply the language patch of the main patch 9239090 if applicable in your environment. In our case French was the additional language that is installed in our system. So, we have applied the french version of 9239090 in this step.

Step 9

Do the step 6 and step 7 again after applying language pack. Then, restrat the whole apps tier and database tier.

This step is not mentioned in the upgrade doc. But, if you don't do it your application login page will not open.

Step 10

Reference document

Oracle E-Business Suite Release 12.1.3 Readme [ID 1080973.1]

Database Preparation Guidelines for an E-Business Suite Release 12.1.1 Upgrade [ID 761570.1]

Database Initialization Parameters for Oracle Applications Release 12 [ID 396009.1]

Oracle® E-Business Suite Upgrade Guide

-----------------------------------

Issue faced 1

ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

Check the following url for solution.

http://forums.oracle.com/forums/thread.jspa?threadID=415560&tstart=15