GENERATING EXCEL OUTPUTS FROM EXISTING STANDARD ORACLE REPORTS IN ORACLE EBUSINESS SUITE R12

Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.
Customers request converting lot of reports to excel.
As you can imagine, converting all reports to excel is a mammoth task using BI (XML) Publisher.

There is a trick to capture Oracle Reports output (text output) into a excel sheet.

This trick uses the power of XML and MS Excel to format the data. This is trick is a simple and effective work around.

Basic Steps :

1) Set the output format for the report to XML

2) Run the report to generate output.

3) Save the output file locally as a XML file.

4) Open the file using MS Excel.

5) To make it more beautiful, you may use a MS Excel Template.

The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.
 

How to decrypt the weblogic password

To decrypt the WebLogic password follow the below steps

 1)Take the adminserver boot. Properties details

[appgvpk1@server5050 security]$ cat $EBS_DOMAIN_HOME/servers/AdminServer/security/boot.properties
#Sun May 08 17:51:57 EDT 2016
password={AES}RL4vuk2Y1rreNBi0EmKNt0x8zY10ckmKxmv+j64CGak\=
username={AES}YOyAsoH6TA9BvK2qxjayQh3NvkQ4W3/3pygLNc4vWUM\=
[appgvpk1@server5050 security]$

 2)create decrypt.py file in

 [appgvpk1@server5050 security]$ cd $EBS_DOMAIN_HOME/security

[appgvpk1@server5050 security]$ cat decrypt.py
from weblogic.security.internal import *
from weblogic.security.internal.encryption import *
encryptionService = SerializedSystemIni.getEncryptionService(".")
clearOrEncryptService = ClearOrEncryptedService(encryptionService)

# Take encrypt password from user
pwd = raw_input("Paste encrypted password ({AES}fk9EK...): ")

# Delete unnecessary escape characters
preppwd = pwd.replace("\\", "")

# Display password
print "Decrypted string is: " + clearOrEncryptService.decrypt(preppwd)
[appgvpk1@server5050 security]$

[appgvpk1@server5050 security]$ pwd
/erppwrc1/erpapp/fs2/FMW_Home/user_projects/domains/EBS_domain_erppwrc1/security

3) source the   setDomainEnv.sh

[appgvpk1@server5050 security]$ cd $EBS_DOMAIN_HOME/bin
[appgvpk1@server5050 bin]$ ls -ltr
total 56
drwxr-x--- 2 appgvpk1 oinstall  4096 May  7 15:29 service_migration
drwxr-x--- 2 appgvpk1 oinstall  4096 May  7 15:29 server_migration
drwxr-x--- 2 appgvpk1 oinstall  4096 May  7 15:29 nodemanager
-rwxr-x--- 1 appgvpk1 oinstall  2010 May  7 15:29 secureWebLogic.sh
-rwxr-x--- 1 appgvpk1 oinstall  2003 May  7 15:29 stopWebLogic.sh
-rwxr-x--- 1 appgvpk1 oinstall  2473 May  7 15:29 stopManagedWebLogic.sh
-rwxr-x--- 1 appgvpk1 oinstall  5704 May  7 15:29 startWebLogic.sh
-rwxr-x--- 1 appgvpk1 oinstall  3251 May  7 15:29 startManagedWebLogic.sh
-rwxr-x--- 1 appgvpk1 oinstall 17349 May  7 15:29 setDomainEnv.sh
[appgvpk1@server5050 bin]$. ./setDomainEnv.sh


4)run the decrypt password script
 
[appgvpk1@server5050 security]$ cd $EBS_DOMAIN_HOME/security
[appgvpk1@server5050 security]$ ls -ltr
total 40
-rw-r----- 1 appgvpk1 oinstall   486 May  7 15:29 decrypt.py
-rw-r----- 1 appgvpk1 oinstall 22654 May  7 15:29 XACMLRoleMapperInit.ldift
-rw-r----- 1 appgvpk1 oinstall    64 May  7 15:29 SerializedSystemIni.dat
-rw-r----- 1 appgvpk1 oinstall  2398 May  7 15:29 DefaultRoleMapperInit.ldift
-rw-r----- 1 appgvpk1 oinstall  3301 May  8 17:50 DefaultAuthenticatorInit.ldift
[appgvpk1@server5050 security]$

[appgvpk1@server5050 security]$ java weblogic.WLST decrypt.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Paste encrypted password ({AES}fk9EK...): {AES}RL4vuk2Y1rreNBi0EmKNt0x8zY10ckmKxmv+j64CGak\=
Decrypted string is: weblogic123

Different adop Patching Modes in Oracle E-Business Suite release 12.2.x

Test Mode

In test mode, adop does not apply the patch. Instead, it lists each file it would have copied, relinked, executed, or generated, and shows exactly what actions it would have performed had it applied the patch. It also runs AutoConfig in test mode to determine any impending changes to the configuration files. This allows you to see the effects of a patch on your system before you apply it.

To run adop in test mode, add the apply=no parameter to the adop command you would use if you were actually going to apply the patch. In test mode, adop will go through the process of applying the patch but will not perform any of the following actions:

• Copy files from the patch directory to the Oracle E-Business Suite file system
• Archive object modules into the product libraries
• Relink executables
• Generate forms, reports, PL/SQL libraries, or menu files
• Run SQL or EXEC commands (commands that change the database)
• Instantiate new configuration files
• Update the patch information files
• Update patch information and release version in the database

Downtime Mode

To optimize the process of upgrading to Oracle E-Business Suite Release 12.2, support is provided for the capability to apply Oracle E-Business Suite patches in downtime mode. When applying patches in this mode, adop will first confirm that the application tier services are down, and will then proceed to apply the patch to the run edition of the Oracle E-Business Suite database and file system. Downtime mode patching does not use an online patching cycle. The process of applying a patch in downtime mode completes more quickly than in online mode, but at the cost of increased system downtime.

To run adop in downtime mode, you use the following command line options. In this example, patch 123456 is applied in downtime mode:

$ adop phase=apply patches=123456 apply_mode=downtime

Preinstall Mode

Preinstall mode is generally used during the upgrade process to update AD utilities, apply pre-upgrade patches, or work around other patching issues. adop asks all startup questions except those relating to the database.

To run adop in preinstall mode, include preinstall=y on the adop command line. It performs the following actions:

• Compares version numbers
• Copies files
• Relinks FND and AD executables
• Saves patch information to the file system

Because adop does not read driver files in preinstall mode, it copies all product files in the patch to the APPL_TOP directory. Additionally, even if a file in the patch should be both in the APPL_TOP and in another directory (such as in $OA_HTML), adop copies the file only to the APPL_TOP.

In preinstall mode, adop validates codelevels against the files Preinstall_Codelevel_AD.txt and Preinstall_Codelevel_MP.txt. These files are located in the $APPL_TOP/admin directory, and contain codelevel information about AD and other products registered in the database tables.

Since no database connection is available in preinstall mode, adop tries to validate whether the current patch should be applied based on the codelevel information in these two files, as follows:

• If Preinstall_Codelevel_AD.txt is missing from the APPL_TOP, adop will apply the patch in preinstall mode without validating the patch for codelevel compatibility.
• If Preinstall_Codelevel_MP.txt is missing from the APPL_TOP, adop will proceed with patch application without validating the patch for codelevel compatibility of the entities.
• If both files are missing, adop will not validate codelevels in preinstall mode.

Note the following restrictions when applying a patch in preinstall mode:

• NLS patches cannot be applied on the instance.
• Baseline or codelevel-introducing patches cannot be applied on the instance.
• adop will not check to see if the patch is already applied on the system.

Important note - Run adop in preinstall mode only if the patch readme instructs you to do so.

vncserver: couldn't find "Xvnc" on your PATH

vncserver: couldn't find "Xvnc" on your PATH
=============================================

gv005# su - oracle

$ vncserver
vncserver: couldn't find "Xvnc" on your PATH.
$ vncserver :1
vncserver: couldn't find "Xvnc" on your PATH.
$ PATH=$PATH:/usr/X/bin:/usr/X11/bin
$ export PATH
$ vncserver

You will require a password to access your desktops.

Password:
Verify:
xauth:  creating new authority file /export/home/oracle/.Xauthority

New 'gv005:5 ()' desktop is gv005:5

Creating default startup script /export/home/oracle/.vnc/xstartup
Starting applications specified in /export/home/oracle/.vnc/xstartup
Log file is /export/home/oracle/.vnc/gv005:5.log

$

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL> startup mount
ORACLE instance started.

Total System Global Area 6265438208 bytes
Fixed Size 2169816 bytes
Variable Size 5167383592 bytes
Database Buffers 1073741824 bytes
Redo Buffers 22142976 bytes
Database mounted.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +ARCH
Oldest online log sequence 180
Current log sequence 182
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL>
SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


Cause:

The database was crashed itself or you issued shutdown abort or startup force commands.

--->you must shutdown your database gracefully/cleanly(normal,transaction or immediate)

oratest@ctsst $ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 23 19:23:31 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 6265438208 bytes
Fixed Size 2169816 bytes
Variable Size 5167383592 bytes
Database Buffers 1073741824 bytes
Redo Buffers 22142976 bytes
Database mounted.
Database opened.
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 6265438208 bytes
Fixed Size 2169816 bytes
Variable Size 5167383592 bytes
Database Buffers 1073741824 bytes
Redo Buffers 22142976 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.
SQL>

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ARCH
Oldest online log sequence 180
Next log sequence to archive 182

Oracle Apps R12.2 Online Patching – Part II

The Online Patching Cycle- Step by Step

Click here to read previous first part of this blogpost - Click here

All patches to an editioned system are applied in below explained sequence of an Online Patching Cycle.

1)    Prepare - creates the patch edition.

“ADOP” is the new ad utility given by Oracle for patching the Oracle Apps R12.2 environments.

ADOP is similar to our old adpatch.

What actually happens during “PREPARE” phase?

Before applying a patch, you must start an Online Patching Cycle. This is done using the adop "prepare" command. Connect to the primary application-tier node of your target system and source the run edition environment file. Then execute the prepare command.

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env run

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

[ajithpathiyil1:ajithebs:applmgr]> adop phase=prepare

Note:- In case, the previous patch cycle did not complete its cleanup phas, The new adop prepare phase will execute the clean-up phase of previous cycle, if needed, and then proceed with preparing the new patch edition for new Online patching cycle.

• Create a new database patch edition
• Synchronize the file system patch edition with the run edition
• Configure the patch edition for use by the patching tools

File system synchronization may be done by applying the delta (changes) from the previous patching cycle, or by re-creating the entire patch edition file system as a fresh copy of the run edition (called "fs_clone"). When complete, check the exiting status code (success is '0'):

adop exiting with status = 0 (Success)

  

Once prepare phase is complete, the database and file system patch edition will contain a copy of the run edition code and seed data.

Now the instance is ready for ARU or Manual Patching to the Patch Edition

2)    Apply - apply ARU or manual patches to the patch edition.

Below steps can be followed for ARU Patches

• Download the patch bundle from ARU from metalink.
• SCP the ZIP files in the "fs_ne/EBSapps/patch" directory on the Middle-tier
• Use "adop phase=apply" command for patch application.

[ajithpathiyil1:ajithebs:applmgr]> adop phase=apply patches=12345678

    ...

[ajithpathiyil1:ajithebs:applmgr]> adop phase=apply patches=12345678,12312345

    ...

Below steps can be followed for Manual Patches.

Manual patching is similar to our old R12.1 or 11i methods but the only difference being that the update happens in the patch edition only.

• Copy patch files to their destination directories in the patch edition.
• Execute any commands necessary to deploy changes to the file system.
• Execute any commands necessary to deploy changes to the database.
• Update the custom synchronization driver to include any file system actions that must be executed again on the next prepare phase, in order to synchronize the alternate file system.  

Note:- The above steps can be automated using code migration tools like HP PPM Kintana or Dell Stat or Any other tools. My Favourite is HP PPM tool  :) (Its deployment module).

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env patch

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the PATCH File System ...

[ajithpathiyil1:ajithebs:applmgr]> cd $NE_BASE/EBSapps/patch/mypatch

[ajithpathiyil1:ajithebs:applmgr]> apply_fs.sh

    # apply patch to file system

    cp fnd/patch/115/sql/* $FND_TOP/patch/115/sql

[ajithpathiyil1:ajithebs:applmgr]> apply_db.sh

    # apply patch to database

    sqlplus apps/apps @$FND_TOP/patch/115/sql/ajith_pks.sql

    sqlplus apps/apps @$FND_TOP/patch/115/sql/ajith_pkb.sql

After applying an ARU patch or a manual patch you can look at the patch edition file system or database status to verify that the patching actions were successful and that the resulting patch edition code and seed data are as expected.

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env patch

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the PATCH File System ...

[ajithpathiyil1:ajithebs:applmgr]> sqlplus apps/*****

SQL> show errors package ajith_pks

SQL> show errors package body ajith_pkb

SQL> quit

3)    Finalize - perform any actions required to prepare for cutover.

ü        The finalize phase is used by the Online Patching system to perform any final actions needed to make the system ready for the fastest possible cutover. 

      The finalize command is run as follows:

    [ajithpathiyil1:ajithebs:applmgr]> adop phase=finalize 

      The finalize command should not have any error, In case of error, the system is not ready for cutover

ü      After successful completion of the finalize phase, the system is ready for cutover

      Note:- We do not need to execute the cutover immediately; Cutover can be delayed until a convenient time in the future. In the meantime, We may also apply additional patches if needed, but you will need to run the finalize phase again after doing so.

4)   Cutover - Promote Patch Edition to be the new Run Edition.

ü        The cutover phase will configure the patch edition to become the new run edition, and restart the application on this new run edition.

Note:- Since the run/patch designation of the dual file systems are swapped during cutover, you must re-source the run edition environment directly after cutover.

  

[ajithpathiyil1:ajithebs:applmgr]> adop phase=cutover

...

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env run

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

5)   Cleanup - remove obsolete code and data from old editions.

• The cleanup phase will remove unnecessary code and data from old editions that are no longer needed by the running application.
• Cleanup should be run after cutover, at any time before the next prepare phase.
• It is best to run cleanup immediately after cutover so that there is no delay when preparing the next online patching cycle.

There are two levels of cleanup available:

• quick - the minimal cleanup required before starting the next patching cycle.
• full - removes all obsolete code and data to recover maximum free space.

Quick cleanup is the default, and is all that is necessary after normal patching.

[ajithpathiyil1:ajithebs:applmgr]> adop phase=cleanup

[ajithpathiyil1:ajithebs:applmgr]> adop phase=cleanup cleanup_mode=full

   Note:- Use full cleanup periodically or after major updates to restore the system to optimal space usage.     

    Warning: full cleanup can take many hours and should only be done when there is no immediate need to   start a new patching cycle.

Oracle Apps R12.2 Online Patching(ADOP) Part 1

Introduction

Oracle E-Business Suite Release 12.2 installation comes with a new feature called “Online Patching” with two editions (versions) of the application code and seed data. The file system contains two complete copies of the Oracle E-Business Suite and technology files. In the database, we use the Edition-based Redefinition feature to create a new database edition for each online patching cycle.

The "Run Edition" is the code and data used by the running application. As a developer, you will connect to the Run Edition whenever you are engaged in normal development activity on the system.

The "Patch Edition" is an alternate copy of Oracle E-Business Suite code and seed data that is updated by Online Patching.

The Oracle E-Business Suite application-tier files are installed in a root directory of the customer's choosing. Within that root directory you will now find three important sub-directories:

     1)    How to see, the RUN, PATCH & NON-EDITIONED Filesystems?

• fs1 - file system 1 (either run or patch edition)
• fs2 - file system 2 (alternate of file system 1)
• fs_ne - non-editioned file system, for data files

By just setting the ebs environment, shows the filesystems.

[ajithpathiyil1::applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

[ajithpathiyil1:ajithebs:applmgr]>

     2)    How to identify the PATCH & RUN edition of filesystem?

[ajithpathiyil1:ajithebs:applmgr]>pwd

/u02/app/applmgr/120/ajithebs

[ajithpathiyil1:ajithebs:applmgr]> grep FILE_EDITION= */EBSapps/appl/*.env

fs1/EBSapps/appl/ajithebs_ajithpathiyil1.env:FILE_EDITION="run"

fs2/EBSapps/appl/ajithebs_ajithpathiyil1.env:FILE_EDITION="patch"

[ajithpathiyil1:ajithebs:applmgr]>     

     3)    How to connect to the PATCH & RUN edition of filesystem?

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env run

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

[ajithpathiyil1:ajithebs:applmgr]> echo $FILE_EDITION

run

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env patch

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the PATCH File System ...

[ajithpathiyil1:ajithebs:applmgr]> echo $FILE_EDITION

patch

[ajithpathiyil1:ajithebs:applmgr]>

     4)    How to display edition status ?

To help keep track of what environment and edition you are connected to, it can be helpful to set the TWO_TASK or FILE_EDITION environment variable as your shell prompt. 

[ajithpathiyil1:ajithebs:applmgr]>  . /u02/app/applmgr/120/ajithebs/EBSapps.env patch

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the PATCH File System ...

[ajithpathiyil1:ajithebs:applmgr]> PS1='$TWO_TASK> '

ajithebs_patch>

     5)    How to find out whether a system is in an Online Patching cycle using the "adop -status" command.

[ajithpathiyil1:ajithebs:applmgr]> adop -status

Enter the APPS username: apps

Enter the APPS password:

Current Patching Session ID: 7

Node Name       Node Type       Phase       Status          Started                        Finished                       Elapsed

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

ajithpathiyil1        master          PREPARE     COMPLETED       05-APR-14 03:57:39 +00:00      07-APR-14 04:55:57 +00:00     36:58:18

                                FINALIZE    COMPLETED       07-APR-14 06:09:01 +00:00      07-APR-14 06:54:41 +00:00      0:45:40

                                CUTOVER     COMPLETED       07-APR-14 06:59:47 +00:00      07-APR-14 07:26:32 +00:00      0:26:45

                                CLEANUP     COMPLETED       07-APR-14 11:26:07 +00:00      07-APR-14 11:28:53 +00:00      0:02:46

                                APPLY       COMPLETED

File System Synchronization Used in this Patching Cycle: Full

For more information, run ADOP Status Report by using -detail option

Generating ADOP Status Report at location: /u02/app/applmgr/120/ajithebs/fs_ne/EBSapps/log/status_20140529_163313/adzdshowstatus.out

Please wait...

Done...!

adop exiting with status = 0 (Success)

[ajithpathiyil1:ajithebs:applmgr]>

     6)    How to find names and status of past and present database editions using the ADZDSHOWED.sql script.

The below lists the existing database editions and identifies the OLD, RUN, and PATCH editions.
  

[ajithpathiyil1:ajithebs:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 29 16:37:18 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

=========================================================================

=                             Editions

=========================================================================

Edition Name    Type     Status   Current?

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

ORA$BASE                 RETIRED

V_20140202_0749 OLD      RETIRED

V_20140405_2132 RUN      ACTIVE   CURRENT

V_20140508_1528 PATCH    ACTIVE

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[ajithpathiyil1:ajithebs:applmgr]>

     7)    How to change to the patch edition of database using SQL*Plus?

[ajithpathiyil1:ajithebs:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 29 16:37:18 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec ad_zd.set_edition('PATCH');

     

     8)    What are the Tools and Scripts for Edition-based Development?

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env run

  E-Business Suite Environment Information

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

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

[ajithpathiyil1:ajithebs:applmgr]> which adop

/u02/app/applmgr/120/ajithebs/fs_ne/EBSapps/appl/ad/bin/adop

[ajithpathiyil1:ajithebs:applmgr]> which xdfgen.pl

/u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl/fnd/12.0.0/bin/xdfgen.pl

[ajithpathiyil1:ajithebs:applmgr]> which xdfcmp.pl

/u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl/fnd/12.0.0/bin/xdfcmp.pl

[ajithpathiyil1:ajithebs:applmgr]>

  

     9)    Useful SQL*Plus scripts that provides information about the state of your editioned development environment. All ADZD* scripts are found under $AD_TOP/sql. 

    

     Add this directory to the SQLPATH environment variable so that you can refer to the scripts by simple name.

[ajithpathiyil1:ajithebs:applmgr]> SQLPATH=$AD_TOP/sql; export SQLPATH

[ajithpathiyil1:ajithebs:applmgr]> echo $SQLPATH

/u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl/ad/12.0.0/sql

[ajithpathiyil1:ajithebs:applmgr]>

1. ADZDDBCC - database compliance checker, shows violations of the database object development standards documented in the Oracle E-Business Suite Developer's Guide, Part No. E22961. Warning: this script takes a long time to run.
2. ADZDSHOWED - Show database editions and current edition.
3. ADZDSHOWLOG - Show full diagnostic log for online patching infrastructure
4. ADZDSHOWLOGEVT - Show only event and error messages from online patching diagnostic log (a useful summary, without the detailed statement text).
5. ADZDSHOWLOGERR - Show only error messages from online patching diagnostic log.
6. ADZDSHOWEV TABLE_SYNONYM_NAME - Show editioning view column mapping for table.
7. ADZDSHOWTAB TABLE_SYNONYM_NAME - Show table information and related objects.
8. ADZDSHOWMV MVIEW_NAME - Show materialized view information and related objects.
9. ADZDSHOWTS - Show important tablespace status. Ensure that you have enough SYSTEM tablespace.
10. ADZDCMPED - Compare Patch Edition with Run Edition. Warning: this script may take a long time to run.
11. ADZDSHOWDDLS - Show stored DDL summary by phase.
12. ADZDALLDDLS - Show stored DDL statement text and status.
13. ADZDDDLERROR - Show stored DDL execution errors and messages.
14. adutlrcmp - Recompile all objects, with before/after status report. Warning: this script may take a long time to run.

The following scripts are for experts:

15. ADZDSHOWOBJS - Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.
16. ADZDSHOWAOBJS - Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.
17. ADZDSHOWIOBJS - Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the Patch Edition.
18. ADZDSHOWCOBJS - Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition.
19. ADZDSHOWCOBJX - Show Covered Object List. List of objects in old editions that have a replacement in the run edition.
20. ADZDSHOWSM - Show Seed Manager status.
21. ADZDSHOWTM - Show Table Manager status.
22. ADZDSHOWAD - AD (online patching) database object status
23. ADZDSHOWSES - Show sessions connected to the database (by edition).
24. ADZDSHOWDEP OBJECT_NAME - Show objects that OBJECT_NAME depends on.
25. ADZDSHOWDEPTREE OBJECT_NAME - Show full dependency tree of objects that OBJECT_NAME depends on.

Click here to read previous Second part of this blogpost click here