Mostly Oracle

My Oracle administration notes/findings. You are welcome to browse and comment.

Sunday, November 1, 2015

Lafayette

Here's a story I just found out today ...

About 230 years ago a 19 year old French boy named Lafayette heard that people in America were rising in a revolt against the English king.  Excited by the ideals of liberty for the people, Lafayette decided to go to America to fight on the American side.

He became very successful, and eventually became a General after returning to France, where he died around 1830.  His body was interred (buried) in a cemetery in the Parisian suburb of Picpus.  His wife and some members of her family are buried there too.

Picpus cemetery is a very special place.  Thirteen years after the revolution in America, there was another one in France; that began with the famous storming of the Bastille prison in 1789.  That prison is gone now and in its place is a big (and famous!) traffic roundabout, near where we stayed last June during our vacation there.

Then a few years after the storming of the Bastille, the Jacobins gained power and started a campaign to eliminate their opposition.  That started a period known as the 'Terror', when many people were sent to the guillotine.  In june and july of 1793, 1306 people had their heads chopped off by guillotine!  All these people were buried in Picpus.  Subsequently only people whose family can be traced to those early victims of the Jacobins can be buried in Picpus.  Lafayette survived the Terror and is buried there because his mother in law and sister in law were killed by the Jaobins.  So they even killed women!

Meanwhile in the 19th century America became a successful industrialized country and a new world power.  They never forgot Lafayette and in 1916/17 they got their chance to repay their debt of gratitude.  During the 1st World War, Germany attacked France.  At  first America stayed out but  after Germany sank the Lusitania (an American passenger ship) , America entered the war.

American troops landed in France around 1917.  A senior officer then looked for the old General's grave in Picpus and installed an American flag there.  The speech he gave was a short one, and it ended with the simple yet moving phrase , "Lafayette, we are here."   About a year later the Germans were defeated and France was free.

To this day it's not unusual for US Navy ships to have French names.  Here's probably the most famous one;
https://en.m.wikipedia.org/wiki/Lafayette_class_submarine

Saturday, June 14, 2014

In my 11gR2 (11.2.0.3) database, I've created a guaranteed restore point without explicitly turning on flashback.   That was a bit of a surprise as going back to a restore point means you need to run the "flashback" command.  So I suspected that Oracle turns on flashback behind the scenes after the guaranteed restore point is created.  The test below confirms this.   

After creating the restore point, v$database.flashback_on changed from NO to RESTORE POINT ONLY :

SQL> select current_scn,log_mode, flashback_on from v$database;

CURRENT_SCN LOG_MODE FLASHBACK_ON
----------- ------------ ------------------
     757896 ARCHIVELOG NO

SQL> select * from v$restore_point;

no rows selected

SQL> create restore point R20140614080801 guarantee flashback database

Restore point created.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY


Flashback logs are actually created in the flash recovery area (which was set before creating the restore point).  

[oracle@orahost1 fast_recovery_area]$ find . -type f -mmin -5 -ls
 49417 51264 -rw-r-----   1 oracle   dba      52436992 Jun 14 08:11 ./NVPDBH1/flashback/o1_mf_9spxnngs_.flb
 49418 51264 -rw-r-----   1 oracle   dba      52436992 Jun 14 08:09 ./NVPDBH1/flashback/o1_mf_9spxnp3b_.flb
1796706 9760 -rw-r-----   1 oracle   dba       9977856 Jun 14 08:14 ./NVPDBH1/controlfile/o1_mf_9hy6087n_.ctl
1796749 51260 -rw-r-----   1 oracle   dba      52429312 Jun 14 08:14 ./NVPDBH1/onlinelog/o1_mf_2_9km0b9k6_.log


... and the database records them here:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE    .19 0 1
REDO LOG   7.03 0 7
ARCHIVED LOG  39.58      .05       80
BACKUP PIECE  12.24 0 2
IMAGE COPY      0 0 0
FLASHBACK LOG   2.01 0
FOREIGN ARCHIVED LOG      0 0 0

7 rows selected.

(The flashback_log row in v$flash_recovery_area_usage was all zero before creating the restore point)

Then after the restore point was dropped, flashback_on became NO again and all the flashback logs were automatically deleted from the recovery area.

In Oracle 10g I remember the database has to be mounted to turn on flashback, but not in 11gR2 (not sure about 11gR1).  So I guess that's why Oracle can turn it on behind the scenes when required by creating the guaranteed restore point.

Finally, I tested flashback database to the restore point and that worked too; the database opened successfully after reset logs.


Friday, January 4, 2013

 

Upgrade to R12 (Round 2)


Preupgrade


Restore R11i backup of 30th Nov

Database Backup
Nov 30 23:34 /backup/ERPPJD/2012_11_30
Application Backup
Nov 30 23:45 /backup/ERPPJD/2012_11_30_20

Set up

Set appstier env to R11i
Go to Maintenance mode sqlplus apps/clone @${AD_TOP}/patch/115/sql/adsetmmd.sql ENABLE    

From R12.1 Upgrade Guide part# E16342-04:
Apply patch 7705743 (TUMS)
Round 1 applied patch 5120936.  This should be replaced by patch 7705743.  

Skipping step "Drop custom triggers".  This needs custom schema name and password and drops triggers named ALR_%.  All our ALR_% triggers are owned by APPS.  

Set initialization parameters alter system set recyclebin=off scope=spfile; and bounce database.  

SHOULD HAVE TURNED OFF ARCHIVELOG MODE HERE.    

Unset the R11i environment (edit the .bash_profile and login again to applpjd)

Check GUEST password
select fnd_web_sec.validate_login('GUEST','') from dual; Should return Y.

applpjd@gsulerpd13 /backup/R12.1.1.32bit/startCD/Disk1/rapidwiz   Disk space after removing erpr12 (keeping some backups in /u14/app/erpr12.back.after1stattemp):  

Filesystem                                                         Size Used Avail Use% Mounted on
172.29.158.27:/vol/gsulerpd13_applbase_u14/gsulerpd13_applbase_u14 130G 71G  60G   55% /u14



Create R12 Apps Tier filesystem

Follow the same steps as per Round 1 but using the 32 bit Apps s/w.

[applpjd@gsulerpd13 /backup/R12.1.1.32bit/startCD/Disk1/rapidwiz]$ nohup ./rapidwiz &

Tools is in oraAS/Disk1: [applpjd@gsulerpd13 /backup/R12.1.1.32bit]$ grep -i tools ora*/Disk*/disk.label
oraAS/Disk1/disk.label:Label=Oracle Applications Rapid Install - Tools
oraAS/Disk2/disk.label:Label=Oracle Applications Rapid Install - Tools
oraAS/Disk3/disk.label:Label=Oracle Applications Rapid Install - Tools

Elapsed: about 1hr. 


Filesystem                                                         Size Used Avail Use% Mounted on
172.29.158.27:/vol/gsulerpd13_applbase_u14/gsulerpd13_applbase_u14 130G 98G 33G 75% /u14
=> 17GB used.

 

Patches discovered in previous run

Set the environment to R12 (edit the .bash_profile and login again to applpjd)



Create the R12 defaults file in the usual way.  This required the R12 adgrants.sql to be applied first.  

For ORA-00979: not a Group by in pechktsk.sql, apply patch 8855023 in R12 env in preinstall mode.  (2 mins)  

For PMI_REP_PROD_STATUS Will Not Compile, apply Patch 9062910 in R12 env in preinstall mode.  (3 mins)  

As a result of these 2 steps adpatch,
  • creates $APPL_TOP/admin/$TWO_TASK/preinstall directory and populates them with the drivers from these 2 patches.
  • updates the scripts in $PER_TOP and $PMI_TOP

The following 3 steps "AD.B Patches", "CUP", "Main R12 Upgrade Patch" are from the 12.1.1 Release Notes 798258.1

AD.B Patches

Merge and apply patches 9179588, 9477107, 7461070.   Unzip these 3 patches into  /capgemshare/other/R12/merge1.in
[applpjd@gsulerpd13 /capgemshare/other/R12]$ admrgpch -s merge1.in -d merge1.out -admode -merge_name merged_ADB

Start: 2:32 pm 03/01/2013
adpatch defaultsfile=/u14/app/erpr12/ERPPJD/apps/apps_st/appl/admin/ERPPJD/adalldefaults.txt.ERPPJD logfile=u_merged_ADB-1.log patchtop=/capgemshare/other/R12/merge1.out driver=u_merged_ADB.drv workers=4 interactive=Y

Elapsed: 15 mins [COULD USE > 4 WORKERS HERE!]

From the REAME of 7461070, run adgrants.sql:
sqlplus / as sysdba @adgrants.sql APPLSYS
NOTE: This caused problems later on with asostats.sql and afffvst1.sql unable to compile FND_STATS (see below)

Consolidated Upgrade Patch (CUP)

Merge and apply in preinstall mode, 7303029 and 33 patches from Note 1448102.1 Pre-install patches Report.

These patches were saved into /u14/app/erpr12.back.after1stattemp
Only the patches in the Upgrade Script Failure column were selected. 


[applpjd@gsulerpd13 /u14/app/erpr12.back.after1stattemp/ERPPJD/apps/apps_st/appl/au/12.0.0/patch/115/driver]$ ls p*.zip

while read fname
> do
> unzip -q $fname -d /capgemshare/other/R12/merge2.in
> done

admrgpch -s merge2.in -d merge2.out -merge_name merged_CUP

time adpatch preinstall=y defaultsfile=/u14/app/erpr12/ERPPJD/apps/apps_st/appl/admin/ERPPJD/adalldefaults.txt.ERPPJD logfile=u_merged_CUP-2.log patchtop=/capgemshare/other/R12/merge2.out driver=u_merged_CUP.drv workers=12 interactive=Y
Elapsed: about 2 mins.

This further populates the R12 $APPL_TOP/admin/$TWO_TASK/preinstall folder with u_merged_CUP.drv

Main R12 Upgrade Patch

Following the README from 7303029,
cd $AU_TOP/patch/115/driver
time admrgpch -d . -preinstall -master u6678700.drv
Elapsed: about 2 mins


Start: Thu Jan 03 2013 15:41:55
time adpatch options=nocopyportion,nogenerateportion defaultsfile=/u14/app/erpr12/ERPPJD/apps/apps_st/appl/admin/ERPPJD/adalldefaults.txt.ERPPJD logfile=u_merged_AMEngUPG-2.log patchtop=$AU_TOP/patch/115/driver driver=u_merged.drv workers=16 interactive=Y

Notes

After 5 mins, get "Do you want to run AutoPatch with these actions turned off [Yes] ?" Yes (lost 30 mins here)

Fri Jan 04 2013 00:35:27

FAILED: file asostats.sql on worker 3.
FAILED: file afffvst1.sql on worker 5.


This is due to inability for APPS to compile FND_STATS.  This was a post-install step for patch 7461070, but is was run with APPLSYS parameter instead of APPS.
Fix:
sqlplus / as sysdba @adgrants.sql APPS, not APPLSYS.
Restart workers.
(lost 8 hours here)
  continuing ...





Wednesday, December 12, 2012

wget script without a proxy

This works from a direct internet connection with no proxy.  It's basically the same as what's available from metalink when you select Download except,

1. Added " --no-check-certificate " to the WGET command.

2. Combined the wget commands for some of the pre-install patches to be mergged with the main R12.1 upgrade driver


#!/bin/sh

LANG=C
export LANG

SSO_USERNAME=

SSO_PASSWORD=

WGET="/usr/bin/wget --no-check-certificate "

COOKIE_FILE=/tmp/$$.cookies

LOGDIR=.
LOGFILE=$LOGDIR/wgetlog-`date +%m-%d-%y-%H:%M`.log

OUTPUT_DIR=.

if [ "$SSO_PASSWORD " = " " ]
then
echo "Please edit script and set SSO_PASSWORD"
exit
fi


SSO_RESPONSE=\
`$WGET --user-agent="Mozilla/5.0" https://updates.oracle.com/Orion/Services/download 2>&1 grep Location`


SSO_TOKEN=`echo $SSO_RESPONSE  | cut -d '=' -f 2 | cut -d ' ' -f 1`
SSO_SERVER=`echo $SSO_RESPONSE | cut -d ' ' -f 2 | cut -d 'p' -f 1,2`

SSO_AUTH_URL=sso/auth

AUTH_DATA="ssousername=$SSO_USERNAME&password=$SSO_PASSWORD&site2pstoretoken=$SSO_TOKEN"

$WGET --user-agent="Mozilla/5.0" --secure-protocol=auto --post-data $AUTH_DATA --save-cookies=$COOKIE_FILE --keep-session-cookies $SSO_SERVER$SSO_AUTH_URL -O sso.out >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10041471_R12.AR.B_R12_GENERIC.zip?aru=13395755&patch_file=p10041471_R12.AR.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10041471_R12.AR.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10096115_R12.AP.B_R12_GENERIC.zip?aru=12912097&patch_file=p10096115_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10096115_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10096191_R12.AP.B_R12_GENERIC.zip?aru=12912174&patch_file=p10096191_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10096191_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1


$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10163753_R12.BIV.B_R12_GENERIC.zip?aru=13174224&patch_file=p10163753_R12.BIV.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10163753_R12.BIV.B_R12_GENERIC.zip >> $LOGFILE 2>&1


$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10170555_R12.AP.B_R12_GENERIC.zip?aru=12991057&patch_file=p10170555_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10170555_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10275581_R12.CS.B_R12_GENERIC.zip?aru=13123965&patch_file=p10275581_R12.CS.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10275581_R12.CS.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10359715_R12.AP.B_R12_GENERIC.zip?aru=13263004&patch_file=p10359715_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10359715_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p10362575_R12.PJB.B_R12_GENERIC.zip?aru=13206811&patch_file=p10362575_R12.PJB.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p10362575_R12.PJB.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p11071399_R12.AP.B_R12_GENERIC.zip?aru=13351118&patch_file=p11071399_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p11071399_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p11653739_R12.AP.B_R12_GENERIC.zip?aru=13780259&patch_file=p11653739_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p11653739_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p11847569_R12.AP.B_R12_GENERIC.zip?aru=13567978&patch_file=p11847569_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p11847569_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p12347791_R12.PJC.B_R12_GENERIC.zip?aru=13663296&patch_file=p12347791_R12.PJC.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p12347791_R12.PJC.B_R12_GENERIC.zip >> $LOGFILE 2>&1


$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p12347791_R12.PJC.B_R12_GENERIC.zip?aru=13663296&patch_file=p12347791_R12.PJC.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p12347791_R12.PJC.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p12877002_R12.AP.B_R12_GENERIC.zip?aru=14419405&patch_file=p12877002_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p12877002_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p12930626_R12.PJC.B_R12_GENERIC.zip?aru=14064914&patch_file=p12930626_R12.PJC.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p12930626_R12.PJC.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13016846_R12.AP.B_R12_GENERIC.zip?aru=14197186&patch_file=p13016846_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13016846_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13509912_R12.PJC.B_R12_GENERIC.zip?aru=14420119&patch_file=p13509912_R12.PJC.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13509912_R12.PJC.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13557244_R12.AP.B_R12_GENERIC.zip?aru=14551421&patch_file=p13557244_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13557244_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13626520_R12.AR.B_R12_GENERIC.zip?aru=14748479&patch_file=p13626520_R12.AR.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13626520_R12.AR.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13695167_R12.CE.B_R12_GENERIC.zip?aru=14794874&patch_file=p13695167_R12.CE.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13695167_R12.CE.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13774403_R12.AP.B_R12_GENERIC.zip?aru=14616899&patch_file=p13774403_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13774403_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13793279_R12.AR.B_R12_GENERIC.zip?aru=14643120&patch_file=p13793279_R12.AR.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13793279_R12.AR.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13837821_R12.AP.B_R12_GENERIC.zip?aru=14672530&patch_file=p13837821_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13837821_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13872862_R12.CE.B_R12_GENERIC.zip?aru=14698257&patch_file=p13872862_R12.CE.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13872862_R12.CE.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p13980548_R12.AR.B_R12_GENERIC.zip?aru=14812957&patch_file=p13980548_R12.AR.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p13980548_R12.AR.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p14332425_R12.AR.B_R12_GENERIC.zip?aru=15336419&patch_file=p14332425_R12.AR.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p14332425_R12.AR.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p8615142_R12.AP.B_R12_GENERIC.zip?aru=11483074&patch_file=p8615142_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p8615142_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p8681082_R12.AP.B_R12_GENERIC.zip?aru=11560853&patch_file=p8681082_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p8681082_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p8712047_R12.AP.B_R12_GENERIC.zip?aru=11567301&patch_file=p8712047_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p8712047_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p9067770_R12.AP.B_R12_GENERIC.zip?aru=11934110&patch_file=p9067770_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p9067770_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p9104076_R12.AP.B_R12_GENERIC.zip?aru=11941560&patch_file=p9104076_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p9104076_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p9257954_R12.AP.B_R12_GENERIC.zip?aru=12157663&patch_file=p9257954_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p9257954_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p9366024_R12.AP.B_R12_GENERIC.zip?aru=12386413&patch_file=p9366024_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p9366024_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p9366024_R12.AP.B_R12_GENERIC.zip?aru=12386413&patch_file=p9366024_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p9366024_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p9799876_R12.AP.B_R12_GENERIC.zip?aru=12673033&patch_file=p9799876_R12.AP.B_R12_GENERIC.zip" -O $OUTPUT_DIR/p9799876_R12.AP.B_R12_GENERIC.zip >> $LOGFILE 2>&1

rm -f sso.out

# rm -f $COOKIE_FILE



Tuesday, December 4, 2012

Upgrade to R12

Reference

R12.1 Upgrade Guide (_08 is current)


Preupgrade

R11 backup:
Backup of database and application are at:

Database Backup
Nov 30 23:34 /backup/ERPPJD/2012_11_30
Application Backup
Nov 30 23:45 /backup/ERPPJD/2012_11_30_20_14



Disk space:[applpjd@gsulerpd13 ~/R12]$ cat df.out
Tue Dec  4 11:52:36 EST 2012
[applpjd@gsulerpd13 /backup/R12.1.1/startCD]$ df -Ph
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00   31G  5.2G   24G  18% /
/dev/sda1             494M   24M  446M   5% /boot
tmpfs                 2.0G     0  2.0G   0% /dev/shm
172.29.176.12:/home/capgemshare   16G  8.2G  6.6G  56% /capgemshare
172.29.158.27:/vol/gsu_home_vlan133/gsu_home_vlan133   40G  320K   40G   1% /export/home
172.29.158.27:/vol/gsulerpd13_applbase_u14/gsulerpd13_applbase_u14  100G   36G   65G  36% /u14
172.29.158.27:/vol/ultnapv004_004/gsulerpd01_support  220G  164G   57G  75% /support
172.29.158.27:/vol/ultnapv004_098/gsulerpd01_backup  1.4T  1.2T  229G  84% /backup




Create R12 Apps Tier filesystem

[applpjd@gsulerpd13 /backup/R12.1.1/startCD/Disk1/rapidwiz]$ ll

-r-xr-xr-x 1 1213 1101 71841 Mar 26 2009 adautostg.pl
drwxrwxr-x 2 1213 1101 4096  Apr 29 2009 bin
-r-xr-xr-x 1 1213 1101 556   Mar 26 2009 ClientWiz.cmd
drwxrwxr-x 6 1213 1101 4096  Apr 29 2009 driver
drwxrwxr-x 2 1213 1101 4096  Apr 29 2009 etc
drwxrwxr-x 2 1213 1101 4096  Apr 29 2009 File
drwxrwxr-x 4 1213 1101 4096  Apr 29 2009 images
drwxrwxr-x 4 1213 1101 4096  Apr 29 2009 jlib
drwxrwxr-x 9 1213 1101 4096  Apr 29 2009 jre
drwxrwxr-x 3 1213 1101 4096  Apr 29 2009 oui
-r-xr-xr-x 1 1213 1101 9407  Mar 26 2009 rapidwiz
-r-xr-xr-x 1 1213 1101 671   Mar 26 2009 RapidWiz.cmd
-r-xr-xr-x 1 1213 1101 24329 Apr 2 2009 RapidWiz.ini
-r-xr-xr-x 1 1213 1101 1092  Mar 26 2009 RapidWizVersion
-r-xr-xr-x 1 1213 1101 357   Mar 26 2009 RapidWizVersion.cmd
drwxrwxr-x 2 1213 1101 4096  Apr 29 2009 template
drwxrwxr-x 10 1213 1101 4096 Apr 29 2009 unzip

[applpjd@gsulerpd13 /backup/R12.1.1/startCD/Disk1/rapidwiz]$ echo $APPL_TOP
/u14/app/erp11i/apps

[applpjd@gsulerpd13 /backup/R12.1.1/startCD/Disk1/rapidwiz]$ export DISPLAY=gsulerpd11:99  
[applpjd@gsulerpd13 /backup/R12.1.1/startCD/Disk1/rapidwiz]$ nohup ./rapidwiz &

ignore this screen

  Leave the OCM Configuration Manager details blank.  
  Select port pool 14 (for Projects Dev)    


Values presented are for applpjd (apps tier) environment.  Edit to match db tier env.  E.g.
Base Directory   /u14/app/orapjd/product/11.2.0


Next, enter passwords for APPS, APPLSYSPUB and GUEST.

Then, edit paths as follows ...

Base Directory    /u14/app/erp12/ERPPJD
Instance Dir      /u14/app/erp12/ERPPJD/inst

... which gives, 
APPL_TOP          /u14/app/erp12/ERPPJD/apps/apps_st/appl

COMMON_TOP        /u14/app/erp12/ERPPJD/apps/apps_st/comn
Tools ORACLE_HOME /u14/app/erp12/ERPPJD/apps/tech_st/10.1.2
Web ORACLE_HOME   /u14/app/erp12/ERPPJD/apps/tech_st/10.1.3
Temp Directory    /u14/app/erp12/ERPPJD/inst/apps/ERPPJD_gsulerpd13/temp

NB: APPL_TOP,aux1/2/3=APPL_TOP




All services enabled except "Other" ...

"Add to server ..." only adds applications node.
DB node is 32 bit - cannot change.


Click OK and,
  • Rapid Wiz performs system checks.
  • Config file written to, 
    $INST_TOP/apps/$CONTEXT_NAME/conf_ERPPJD.txt



Result,








Click Next and it starts installing R12.   4:00pm Tuesday 04 Dec 2012.

Appltop logfile - $INST_TOP/apps/$CONTEXT_NAME/logs/12041609.log




It will start asking questions for Tools, etc.  Search as follows ...

[applpjd@gsulerpd13 /backup/R12.1.1]$ grep -i tools oraA*/Disk*/disk.label

oraAS/Disk1/disk.label:Label=Oracle Applications Rapid Install - Tools
oraAS/Disk2/disk.label:Label=Oracle Applications Rapid Install - Tools
oraAS/Disk3/disk.label:Label=Oracle Applications Rapid Install - Tools


[applpjd@gsulerpd13 /u14/app/erp12/ERPPJD/inst/apps/ERPPJD_gsulerpd13/logs]$
tail -3 12041609.log


Disk platform missmatch.
required platform: Linux

Disks for platform: LINUX_X86-64

Download R12.1.1 for 32 bit linux Apps tier into /backup/R12.1.1.32bit

Restarted at Wed Dec 5 15:38:16 EST 2012
( ./rapidwiz -restart just gave the same screens. 
This time it started with the APPLTOP-Disk 1 dvd, not the Tools dvd)

Timing: After approx. 30 mins,



[applpjd@gsulerpd13 ~]$ df -Ph /u14
Filesystem            Size  Used Avail Use% Mounted on
172.29.158.27:/vol/gsulerpd13_applbase_u14/gsulerpd13_applbase_u14  100G   60G   41G  60% /u14
=> 60gb - 36gb = 24gb used.


Fix

mv -i erp12 erp12.00; mkdir -p erp12/ERPPJD/inst
Downloaded the 32 bit startcd.unzipped all the zip files ... unzip -q -o ...

./rapidwiz  (no change in database)

Logs before specifying base directory:
[applpjd@gsulerpd13 /tmp/12061204]$ ls -ltr
total 140
-rw-r--r-- 1 applpjd oaa 13813 Dec  6 12:05 gsulerpd03_VIS_db.xml
-rw-r--r-- 1 applpjd oaa 12103 Dec  6 12:07 conf_ERPPJD.txt
-rw-r--r-- 1 applpjd oaa 92233 Dec  6 12:08 gsulerpd13_ERPPJD_apps.xml
-rw-r--r-- 1 applpjd oaa 12840 Dec  6 12:08 12061204.log


Timing: After 1 hour:


Ignorable error in the logfile (see : 1112613.1)
.ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2,:3,:4,:5); END;: 1; The element reports_port is missing from the context file /u14/app/erp12/ERPPJD/inst/apps/ERPPJD_gsulerpd13/appl/admin/ERPPJD_gsulerpd13.xml.

df -Ph
172.29.158.27:/vol/gsulerpd13_applbase_u14/gsulerpd13_applbase_u14 100G 88G 13G 88% /u14

Click Next


(IANA Character Set: UTF-8)



No R12 applpjd processes were running so do not try to connect


R11i patches that should have been applied before rapidwiz
4712852 - Minipack 11i.AD.I.4 - already applied
5753359 - already applied

5467526 - already applied


5120936 - applied succesfully
          cd $AD_TOP/patch/115/sql
          sqlplus APPS/ @adtums.sql DATA_PUMP_DIR
          => gsulerpd03:/tmp/tumsr12.html is generated.
          tumsr12.html lists steps that can be omitted.

5726010 - applied succesfully (not adpatch)
          INTERIM tablespace is created with 
          datafile gsulerpd03:$ORACLE_HOME/dbf/interim.dbf 200MB


R12 AD patch

Set the environment to R12

[applpjd@gsulerpd13 ~]$ ln -s /u14/app/erp12/ERPPJD/apps/apps_st/appl/APPSERPPJD_gsulerpd13.env .


Set the applpjd .bash_profile to the above link and remove the hard-coded APPL_TOP, COMMON_TOP etc.  

Apply R12.AD.A 4502962

This patch is superceeded by AD.C, but apply only AD.A.   
 
 
Backup
Backup the new R12 appstier and R11i database into,
orapjd@gsulerpd03:/backup/ERPPJD/2012_12_07_pre4440000_ap
orapjd@gsulerpd03:/backup/ERPPJD/2012_12_07_pre4440000_db


Apply patch 7461070
    Run adgrants.sql from this patch in RDBMS


Apply patch 6678700
         
    adpatch options=nocopyportion,nogenerateportion
        (these options will ask for confirmation even with defaults file)



Start:Sun Dec 09 2012 21:32:20

Dec  9 23:51
FAILED: file pechktsk.sql on worker  4.
ORA-00979: not a GROUP BY expression

(Sigh)    

Fix: Note ID 1083981.1. 
Patch 8855023 for R12. 



Pre-install patches that should have been merged with the main driver

See Note 1448102.1 

Download the  EBS_R12.1_Preinstall_2012_Nov_20.zip file

Downloaded for


EBS
   -> Upgrade script Failure
      -> FIN: AP AR CE
      -> PJ: PJC PJB
      -> HRMS: none
      -> CRM: CS

p10041471_R12.AR.B_R12_GENERIC.zip
p10096115_R12.AP.B_R12_GENERIC.zip
p10096191_R12.AP.B_R12_GENERIC.zip
p10163753_R12.BIV.B_R12_GENERIC.zip
p10170555_R12.AP.B_R12_GENERIC.zip
p10275581_R12.CS.B_R12_GENERIC.zip
p10359715_R12.AP.B_R12_GENERIC.zip
p10362575_R12.PJB.B_R12_GENERIC.zip
p11071399_R12.AP.B_R12_GENERIC.zip
p11653739_R12.AP.B_R12_GENERIC.zip
p11847569_R12.AP.B_R12_GENERIC.zip
p12347791_R12.PJC.B_R12_GENERIC.zip
p12877002_R12.AP.B_R12_GENERIC.zip
p12930626_R12.PJC.B_R12_GENERIC.zip
p13016846_R12.AP.B_R12_GENERIC.zip
p13509912_R12.PJC.B_R12_GENERIC.zip
p13557244_R12.AP.B_R12_GENERIC.zip
p13626520_R12.AR.B_R12_GENERIC.zip
p13695167_R12.CE.B_R12_GENERIC.zip
p13774403_R12.AP.B_R12_GENERIC.zip
p13793279_R12.AR.B_R12_GENERIC.zip
p13837821_R12.AP.B_R12_GENERIC.zip
p13872862_R12.CE.B_R12_GENERIC.zip
p13980548_R12.AR.B_R12_GENERIC.zip
p14332425_R12.AR.B_R12_GENERIC.zip
p8615142_R12.AP.B_R12_GENERIC.zip
p8681082_R12.AP.B_R12_GENERIC.zip
p8712047_R12.AP.B_R12_GENERIC.zip
p9067770_R12.AP.B_R12_GENERIC.zip
p9104076_R12.AP.B_R12_GENERIC.zip
p9257954_R12.AP.B_R12_GENERIC.zip
p9366024_R12.AP.B_R12_GENERIC.zip
p9799876_R12.AP.B_R12_GENERIC.zip

mkdir /u14/app/erp12/ERPPJD/apps/apps_st/ap pl/admin/ERPPJD/preinstall
unzip all the patches into the preinstall directory.

cd /u14/app/erp12/ERPPJD/apps/apps_st/appl/au/12.0.0/patch/115/driver


   admrgpch -d . -preinstall -master u6678700.drv

   Executing the merge of the patch drivers

   No driver files exists 
        in /u14/app/erp12/ERPPJD/apps/apps_st/appl/admin/ERPPJD/preinstall.

   -- Processing file: u6678700.drv
   -- Done processing file: u6678700.drv
   1 unified drivers merged.
   Patch merge completed successfully
   Please check the log file at ./admrgpch.log.

Timing: took 4.5 hours!

However, patch 8855023 is not in the pre-install list.  Probably should apply the R11i version of that patch (9716146) before applying the merged upgrade driver.

Continuing with the pre-install patches note,  ....
"Apply the newly merged Release 12.1 upgrade driver (for example, $AU_TOP/patch/115/driver/u_merged.drv) with adpatch options=nocopyportion,nogenerateportion"

adpatch defaultsfile=/u14/app/erp12/ERPPJD/apps/apps_st/appl/admin/ERPPJD/adalldefaults.txt.ERPPJD logfile=umerged_6678700-01.log patchtop=$AU_TOP/patch/115/driver driver=u_merged.drv workers=4 interactive=Y options=nocopyportion,nogenerateportion



AutoPatch started at: Wed Dec 12 2012 10:35:55   At 15:38 Wednesday, workers 1,3,4 wait for worker 2 on IGSPS84S.pls which suffered a deadlock. Fix: adctrl "Tell manager that a worker failed its job".  adpatch re-assigns the job and continues on.  14:30 Thursday.        

Stopped again after 12 minutes!


-rw-r--r-- 1 applpjd oaa 7739559 Dec 13 14:42 umerged_6678700-01.log



Worker Code     Context           Filename                   Status
------ -------- ----------------- -------------------------- --------------
1 Run AutoPatch R120 Wait
2 Run AutoPatch R120 apwdflxs.pls Assigned
3 Run AutoPatch R120 Wait
4 Run AutoPatch R120 Wait

Cause: "Tell manager that a worker failed its job" seems to have disabled the worker.  (adworker 2 is defunct in Unix)

Fix:
applpjd 18003 17807 0 Dec12 pts/1 00:00:22 [adworker]


kill 18003

=> all of them died and adpatch stopped!

Restart adpatch. 9:32am Friday Dec 14th.

Fri Dec 14 2012 09:54:47

FAILED: file PMIRPTV.odf on worker 1.
ORA-00979: not a GROUP BY expression
occurred while executing the SQL statement:
CREATE OR REPLACE FORCE VIEW APPS.PMI_REP_PROD_STATUS_V (
 

Fix:
See [ID 1250054.1]
=> Apply, patch 9062910 (INTEROPERABILITY PATCH FOR R12.1.1 ON 11.2 RDBMS) PMIRPTV.odf -> 120.0.12010000.2

Restart adpatch 11:45am



Sun Dec 16 2012 12:37:15, this error occured:

3  Run AutoPatch R120 EDRXDOMigration.class FAILED

java.sql.SQLRecoverableException: Io exception: Connection reset   All attempts to fix this problem with SQLNET.INBOUND_CONNECT_TIMEOUT = 120 failed.    Subsequent attempts to restart adpatch caused all workers to fail.    
Thu Dec 20 15:05:58 EST 2012
Restoring R11i backup of /backup/ERPPJD/2012_11_30


Followers