10 Ways to Leave your Upgrade, Faster

This blog is based on a RadiOAUG broadcast on April 9, 2013 at Collaborate in Denver.

10 Ways to Speed up your Upgrade.

1) Fast IO – Solid State Drive for the database files

2) Don’t do an in-place upgrade. This allows you to measure the upgrade on the actual machine you’ll use for the production upgrade.

3) Lots of disk space to stage files. Stage the 11i files so you don’t have to clone them before the upgrade. Stage the R1211 files and the post 1213 files, with all the customizations.

4) Try increasing parallel_max_servers to see if this allows greater parallelization.

5) Don’t use a threaded CPU architecture for the database server.

6) If you do use a threaded CPU server, use Automatic Degree of Parallelism (ADOP) to increase the parallelism. There are three modes: Manual, Limited and Auto. We tested Manual and Auto, and had the best results with Auto.

7) XDOLoader – entropy issue. Create a link to /dev/urandom called /dev/random. Note: 1065393.1.

8) Disable ARCHIVELOG Mode and Auditing

9) Use trace to diagnose performance issues:

oradebug setospid OSPID-number
oradebug unlimit
oradebug event 10046 trace name context forever, level 12
oradebug tracefile_name

Run tkprof on the tracefile and examine the tkprof output.

10) There are lots of functional scripts and datafixes that can speed your upgrade: facpupg.sql and glrsgup2.sql are two examples that allow you to run depreciation in advance and prepare posted journals before the upgrade.

 

Posted in E Business Suite, Performance Tuning, R12.1 Upgrade | Comments Off

ATG Genie – AD wish list recommendations

At OOW12, the ATG CAB reviewed the existing wish lists for each ATG module and asked for new recommendations for the wish list. I requested 6 or 7 new wishes, and I was granted 4 wishes.

Please send me your ideas for your wish list and I’ll forward them to the ATG CAB. Anything you really need can be requested. If it makes sense to the development groups they will submit a bug and you’ll be granted your wish.

 

Posted in E Business Suite | Comments Off

David Kurtz’ “How to Clear the Peoplesoft Application Server Cache”

 http://trutek.com/serendipity/index.php?/archives/14-How-to-Clear-the-PeopleSoft-Application-Server-Cache-Without-Shutting-It-Down-by-David-Kurtz.html

Posted in Uncategorized | Comments Off

Use AFPASSWD in 11i

Originally, FNDCPASS was the password utility in 11i and AFPASSWD is the new password utility for R12.1 of EBS. AFPASSWD has been backported from R12.1 and is now available for 11i.  The 11i version for AFPASSWD is delivered as part of patch 13644154.

AFPASSWD only prompts for passwords required for the current operation,
allowing separation of duties between applications administrators and database administrators.

 In contrast, the FNDCPASS utility currently requires specification of the APPS and the SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.

When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

AFPASSWD can be run from the database tier as well as the application tier.
In contrast, FNDCPASS can only be run from the application tier.

Prereqs for patch 13644154:

5903765 – 11i.ATG_PF.H.RUP6
3219567 – Patch 11i.TXK.B Technology Stack Minipack B (also in 11.5.10)
3264822 – Patch 11i.CAC.B
3261254 – Patch 11i.ALR.G
5161676 – MINIPACK 11I.AD.I.5
3036401 – Mini-Pack 11i.HZ.L
3263588 – Patch 11i.XDO.H
3264818 – Patch 11i.UMX.H
3218526 – Patch 11i.BNE.D
3263645 – Patch 11i.AK.G
4206794 – Patch 11i.FRM.H
3262486 – 11i.JTA.F
3261243 – Patch 11i.EC.G
2614213 – AME PATCH :D ELIVERY OF GA AND RULE PRIORITY FUNCTIONALITY
3262159 – Patch 11i.FND.H
2819091 – Patch 11i.BNE.C
3412795 – ADSPLICE PATCH FOR XDO

Posted in Uncategorized | Comments Off

Find the patches in a merged patch

Maintaining and patching the E-Business Suite is faster when using the AD Merge Patch utility or admrgpch as the applmgr user, to merge patches. AD Merge Patch merges separate patches together, and the Patch Name stored in the database is “merged” and the patch driver is u_merged.drv. Merging patches avoids the duplication of effort, during the application of the merged patch, if multiple patches are trying to copy and generate the same file. The biggest time savings is the ability to recompile invalid objects only once, instead of once for each patch.

The database stores information about the patch when each patch is applied. The patch name is usually the same as the patch number. However, for a merged patch, the patch name is “merged”. This makes it difficult to report the patches that have been applied to your instance. One approach is to keep the patches on the filesystem in a directory named after the patch session. 

Another approach to find all patches within a merged patch is to understand the tables that contain this information and the relationships between these tables.

By joining  the following APPLSYS tables, we can relate Patches to Bugs and know when the patches were applied:

  ad_applied_patches   ->  ad_patch_drivers   ->   ad_comprising_patches   ->  ad_bugs       

We can find all the bugs associated with a patch, even a merged patch.

1.      Find the APPLIED_PATCH_ID for a merged patch

 Query the AD_APPLIED_PATCHES table: 

SELECT applied_patch_id, rapid_installed_flag, patch_name, patch_type, maint_pack_level

FROM   applsys.ad_applied_patches

 

  Figure 1

Problem: We need to find all the patches that were part of the merged patch. 

2.      Find the PATCH_DRIVER_ID

295501 is the applied_patch_id for the merged patch.  In order to find the detail of the merged patch, we need to look in the AD_PATCH_DRIVERS table, using the APPLIED_PATCH_ID from the AD_APPLIED_PATCHES table.

From the AD_PATCH_DRIVERS table we find the APPLIED_PATCH_ID 295501 and the corresponding PATCH_DRIVER_ID (279993):

AD_PATCH_DRIVERS Table:

 

 

 

Figure 2

3.      Find all the BUGS

Then, use the corresponding PATCH_DRIVER_ID 279993 in the following query:

SELECT *

  FROM (SELECT ab.bug_number ||DECODE(ab.baseline_name,NULL,”,’.’)

      || ab.baseline_name,  acp.patch_abstract

      FROM  ad_comprising_patches acp, ad_bugs ab

      WHERE acp.bug_id = ab.bug_id AND acp.patch_driver_id =279993)

  WHERE ROWNUM<201

This returns the original bugs/patches that were merged together:

 Figure 3

I use bugs/patches because sometimes a bug number will also be the patch name/number. This happens when the bug is not merged with other bugs into a bigger patch and assigned a surrogate number.  Patches with surrogate numbers are related to multiple bugs.

4.      Join the queries together

By joining the queries together, we get a list of all patches and the patch names, even patch names that were assigned by Oracle. Oracle also uses AD Merge Patch to merge patches.

SELECT ab.bug_number, ap.patch_name, ap.patch_type, ap.maint_pack_level,

       ap.last_update_date

       FROM     applsys.ad_comprising_patches acp,

                applsys.ad_bugs ab,

                applsys.ad_patch_drivers pd,

                applsys.ad_applied_patches ap

       WHERE    acp.bug_id = ab.bug_id

                AND acp.patch_driver_id = pd.patch_driver_id

                AND pd.applied_patch_id = ap.applied_patch_id

       ORDER BY ap.last_update_date desc

 

 

Figure 4

 

Posted in E Business Suite | Comments Off

MOS Extended and Sustaining Support Notice MOS ID 1495337.1

“As part of Oracle’s continued commitment to our customers, we will be providing an exception for the first 13 months of Sustaining Support on Oracle E-Business Suite Release 11.5.10 (11i10), valid from December 1, 2013 – December 31, 2014. This exception support will be comprised of three components:

(1) new fixes for Severity 1 production issues,

(2) United States Form 1099 2013 year-end updates, and

(3) payroll regulatory updates for the United States, Canada, United Kingdom, and Australia for fiscal years ending in 2014. 

In addition, the Extended Support period for E-Business Suite Release 12.1 has been extended through December, 2018. Customers with an active Oracle Premier Support for Software contract will automatically be entitled to Extended Support deliverables for E-Business Suite 12.1.”

Posted in E Business Suite | Comments Off

Three XLA invalid objects if you don’t apply the SLA patch 5233248 to 11i before the R12.1.3 upgrade

When upgrading to 12.1.1 and then 12.1.3, if you don’t apply patch 5233248 during the application of your 11i patches to prepare your database objects for R12, after the upgrade and all the R12.1.3 patches have been applied, there are three invalid objects that can only be resolved by applying this SLA patch to the 11i instance, in advance of the upgrade to R12.1.3. This patch is recommended as a patch needed if you are using “Upgrade by Request”.  There are other methods to implement “Upgrade by Request”, but this is the method we recommend. If you don’t apply this patch and set the start and end dates with the SLA pre-upgrade program shown below, then only the default number of periods will be upgraded. The number of periods that are upgraded by default are six, unless you set the periods in the SLA pre-upgrade program. We highly recommend setting the starting period to the first period in your instance and the ending period to the last period in your instance.

If you plan to upgrade all your data during the upgrade, and I highly recommend upgrading all your data during the upgrade downtime window, you will need to set the start and end dates for all the GL periods in your instance. Set these dates using the “SLA pre-upgrade” concurrent program as follows:

 

If you decide to use less than all the GL periods, the amount of time this saves has been about one to two hours during our customers production upgrades. The post upgrade concurrent request can upgrade any remaining data in a few hours, if you choose not to upgrade all your GL periods during the original production downtime window.  But a downtime window for this post upgrade concurrent program should requested because of the amount of archive logging it creates. If you have a downtime window to process the remaining GL periods, archive logging can be disabled.  If you do not have a downtime window during the post SLA processing, the archive log mount point can fill up and hang your database. For this reason we strongly recommend disabling archive logging during any post processing of SLA data.

This XLA patch needs to be run with 11i patches in order to avoid these three invalid objects after the 12.1.1 / 12.1.3 upgrade:

XLA_00200_AAD_S_000013_PKG
XLA_00200_AAD_S_000014_PKG
XLA_PRE_UPGRADE_PUB_PKG
Posted in E Business Suite, R12.1 Upgrade | Comments Off

11g Database OCA Cheat Sheet Notes

My notes for teaching the OCA 11g DBAI class, for students preparing to take the OCA exam. 

Database Writer writes when:

  1. No free buffers
  2. Too many dirty buffers
  3. 3 second timout
  4. Checkpoint

Log Writer writes when:

  1. session COMMIT
  2. buffer 1/3 full
  3. DBWR is about to write

UNDO

UNDO is used for:

  1. Reversing incomplete transactions
  2. Read consistency (temporary)
  3. Transaction isolation
  4. Flashback (9i temporary)

UNDO errors:

Run out of space ORA-30036 “unable to extend segment in undo tablespace”  Lets the query succeed

Fails on consistent read- undot for CR has been overwritten ORA-01555 “snapshot too old”. This means the transaction will succeed.

FAILURE TYPES

  1. Statement failure
  2. User process failure- Network(listener, network card, route), User error
  3. Media Failure
  4. Instance Failure

RECOVERY

  1. Loss of control file
  2. Loss of online redo log
  3. Loss of datafile in NOARCHIVELOG mode
  4. Loss of datafile in ARCHIVELOG mode

Init parameters

FAST_START_MTTR_TARGET

Views

V$INSTANCE_RECOVERY

Commands

alter system checkpoint

alter system switch logfile

FLASHBACK

db_file_recovery_dest

db_file_recovery_dest_size

V$RECOVERY_FILE_DEST

V$FLASH_RECOVERY_USAGE

How to Set Archive Log Mode

  1. ARCHIVE LOG LIST
  2. Shutdown
  3. ALTER DATABASE ARCHIVELOG
  4. ALTER SYSTEM ARCHIVE LOG CURRENT, or
  5. ALTER SYSTEM SWITCH LOGFILE
  6. SELECT name, is_recovery_dest_file FROM v$archive_log

Commands to Perform a Hot Backup

  1. ALTER TABLESPACE <name> BEGIN BACKUP
  2. LIST BACKUP OF DATABASE
  3. LIST BACKUP OF ARCHIVE ALL;
  4. REPORT NEED BACKUP;
  5. REPORTOBSOLETE

RMAN commands

All the RMAN command are listed at the bottom.

  1. CROSSCHECK
  2. DELETE EXPIRED
  3. DELETE OBSOLETE
  4. CATALOG

Copy the file(s) to the new location.

RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/users.dbf';

Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.

RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';

Switch to the new datafile copy(s) and recover the tablespace.

RMAN> SWITCH DATAFILE 8 TO COPY;
RMAN> RECOVER TABLESPACE users;

Turn the tablespace online again.

RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Remove the old datafile(s).

RMAN> HOST 'rm /u01/app/oracle/product/11.2.0.3/dbs/users.dbf';

AUDITING

DB_DBA_AUDIT_TRAIL –>

Value Based Triggers

FGA_DBMS_FGA (all rows)         These two views combine to form DBA_COMMON_AUDIT_TRAIL

 DBA_FGA_AUDIT_TRAIL–>

ALERTS – Threshold Alerts – MMON – stateful

DBA_SERVER_ALERT

DBA_OUTSTANDING_ALERTS

DBA_ALERT_HISTORY –>   “snapshot too old is stateless”

 

STATISTICS

TABLEs                  DBA_TABLES

COLUMNS             DBA_TAB_COLUMNS

INDEXES               DBA_INDEXES, INDEX_STATS

 

STATISTICS_LEVEL = TYPICAL, ALL, BASIC

 

OPATCH

opatch apply

opatch rollback

 

To move or rename a datafile do the following.

  • Shutdown the database.
  • Rename the physical file on the OS.
  • Start the database in mount mode.
  • Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
  • Open the database.

To move or rename a controlfile do the following.

  • Alter the control_files parameter using the ALTER SYSTEM comamnd.
  • Shutdown the database.
  • Rename the physical file on the OS.
  • Start the database.

 RMAN Commands

Command Purpose
“@” Run a command file.
“@@” Run a command file in the same directory as another command file that is currently running. The @@ command differs from the @ command only when run from within a command file.
“ALLOCATE CHANNEL” Establish a channel, which is a connection between RMAN and a database instance.
“ALLOCATE CHANNEL FOR MAINTENANCE” Allocate a channel in preparation for issuing maintenance commands such as DELETE.
“allocOperandList” A subclause that specifies channel control options such as PARMS and FORMAT.
“ALTER DATABASE” Mount or open a database.
“archivelogRecordSpecifier” Specify a range of archived redo logs files.
“BACKUP” Back up database files, copies of database files, archived logs, or backup sets.
“BLOCKRECOVER” Recover an individual data block or set of data blocks within one or more datafiles.
“CATALOG” Add information about a datafile copy, archived redo log, or control file copy to the repository.
“CHANGE” Mark a backup piece, image copy, or archived redo log as having the status UNAVAILABLE or AVAILABLE; remove the repository record for a backup or copy; override the retention policy for a backup or copy.
“completedTimeSpec” Specify a time range during which the backup or copy completed.
“CONFIGURE” Configure persistent RMAN settings. These settings apply to all RMAN sessions until explicitly changed or disabled.
“CONNECT” Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
“connectStringSpec” Specify the username, password, and net service name for connecting to a target, recovery catalog, or auxiliary database. The connection is necessary to authenticate the user and identify the database.
“CONVERT” Converts datafile formats for transporting tablespaces across platforms.
“CREATE CATALOG” Create the schema for the recovery catalog.
“CREATE SCRIPT” Create a stored script and store it in the recovery catalog.
“CROSSCHECK” Determine whether files managed by RMAN, such as archived logs, datafile copies, and backup pieces, still exist on disk or tape.
“datafileSpec” Specify a datafile by filename or absolute file number.
“DELETE” Delete backups and copies, remove references to them from the recovery catalog, and update their control file records to status DELETED.
“DELETE SCRIPT” Delete a stored script from the recovery catalog.
“deviceSpecifier” Specify the type of storage device for a backup or copy.
“DROP CATALOG” Remove the schema from the recovery catalog.
“DROP DATABASE” Deletes the target database from disk and unregisters it.
“DUPLICATE” Use backups of the target database to create a duplicate database that you can use for testing purposes or to create a standby database.
“EXECUTE SCRIPT” Run an RMAN stored script.
“EXIT” Quit the RMAN executable.
“fileNameConversionSpec” Specify patterns to transform source to target filenames during BACKUP AS COPY, CONVERT and DUPLICATE.
“FLASHBACK” Returns the database to its state at a previous time or SCN.
“formatSpec” Specify a filename format for a backup or copy.
“HOST” Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.
“keepOption” Specify that a backup or copy should or should not be exempt from the current retention policy.
“LIST” Produce a detailed listing of backup sets or copies.
“listObjList” A subclause used to specify which items will be displayed by the LIST command.
“maintQualifier” A subclause used to specify additional options for maintenance commands such as DELETE and CHANGE.
“maintSpec” A subclause used to specify the files operated on by maintenance commands such as CHANGE, CROSSCHECK, and DELETE.
“obsOperandList” A subclause used to determine which backups and copies are obsolete.
“PRINT SCRIPT” Display a stored script.
“QUIT” Exit the RMAN executable.
“recordSpec” A subclause used to specify which objects the maintenance commands should operate on.
“RECOVER” Apply redo logs and incremental backups to datafiles restored from backup or datafile copies, in order to update them to a specified time.
“REGISTER” Register the target database in the recovery catalog.
“RELEASE CHANNEL” Release a channel that was allocated with an ALLOCATE CHANNEL command.
“releaseForMaint” Release a channel allocated with an ALLOCATE CHANNEL FOR MAINTENANCE command.
“REPLACE SCRIPT” Replace an existing script stored in the recovery catalog. If the script does not exist, then REPLACE SCRIPT creates it.
“REPORT” Perform detailed analyses of the content of the recovery catalog.
“RESET DATABASE” Inform RMAN that the SQL statement ALTER DATABASE OPEN RESETLOGS has been executed and that a new incarnation of the target database has been created, or reset the target database to a prior incarnation.
“RESTORE” Restore files from backup sets or from disk copies to the default or a new location.
“RESYNC” Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.
“RUN” Execute a sequence of one or more RMAN commands, which are one or more statements executed within the braces of RUN.
“SEND” Send a vendor-specific quoted string to one or more specific channels.
“SET” Sets the value of various attributes that affect RMAN behavior for the duration of a RUN block or a session.
“SHOW” Displays the current CONFIGURE settings.
“SHUTDOWN” Shut down the target database. This command is equivalent to the SQL*Plus SHUTDOWN command.
“SPOOL” Write RMAN output to a log file.
“SQL” Execute a SQL statement from within Recovery Manager.
“STARTUP” Start up the target database. This command is equivalent to the SQL*Plus STARTUP command.
“SWITCH” Specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file. This command is equivalent to the SQL statement ALTER DATABASE RENAME FILE as it applies to datafiles.
“UNREGISTER DATABASE” Unregisters a database from the recovery catalog.
“untilClause” A subclause specifying an upper limit by time, SCN, or log sequence number. This clause is usually used to specify the desired point in time for an incomplete recovery.
“UPGRADE CATALOG” Upgrade the recovery catalog schema from an older version to the version required by the RMAN executable.
“VALIDATE” Examine a backup set and report whether its data is intact. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksums to verify that the contents can be successfully restored.
Posted in 11g Database, DBAI Certification Overview - OCA | Comments Off

adpatch restart file ‘END OF COMPLETED JOBS’ line is missing

When applying the patch from the apps tier machine and it dies in the middle of the patch, in this case the R12.1.1 patch, 6678700, it said:

Reading completed jobs from restart file (if any).

AutoPatch error:

unable to find job when restarting

Wrong number of fields in restart file when restarting at line number =

This may indicate that the “END OF COMPLETED JOBS’ line is missing.

Notice the patch lists the number of jobs from the beginning of the patch, phase A0, not the current job, in this case is 48177 jobs at phase A144, as shown in the figure at the bottom of this post.

 

 

 

 

 

Other OTN threads suggest restarting the patch from the beginning. This requires a restore of the database, and while this solution works, it’s not the fastest solution.

Please edit the $APPL_TOP/admin/SID/restart/adpmain.rf9 file and remove the last incomplete line. See the figure below, the last line is incomplete compared to the line above it. Make a backup of the restart directory and edit the file and remove the last line.

 

Remove the line that says: Done job: prod= gmd un=GMD file=GMDSTNXT.sql ……

Then, restart the patch and use adctrl to set the jobs to restart.

Thanks, Mike

Posted in E Business Suite, R12.1 Upgrade | Comments Off

Missing Responsibilites after Upgrade to R1213

From: How To Run The Workflow Directory Services Concurrent Program From The SQLplus Prompt [ID 1213304.1]

1. Sync responsibility role data into the Workflow table:

begin
fnd_user_resp_groups_api.sync_roles_all_resp_secgrps(TRUE);
end;

2. Synchronize WF LOCAL tables:

exec WF_LOCAL_SYNCH.BulkSynchronization(‘ALL’)

The Synchoronize WF LOCAL table step above give the following error:

ERROR at line 1:

ORA-12801: error signaled in parallel query server P000

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

ORA-06512: at “APPS.WF_LOCAL_SYNCH”, line 2726

ORA-06512: at line 1

sqlplus apps/apps $FND_TOP/patch/115/sql/affurgol.sql

The run step three from MOS Note [1213304.1]

3. Workflow Directory Services User/Role Validation:

exec wf_maintenance.ValidateUserRoles(p_BatchSize => null, p_check_dangling => TRUE, p_check_missing_ura => TRUE, p_UpdateWho => FALSE);

 Run adstpall.sh to stop the apps tier and adstrtal.sh to start the apps tier.

 

Posted in E Business Suite, R12.1 Upgrade | Comments Off