Friday 29 June 2012

How to Convert Number to Word in Oracle Report Builder


Function CF_11Formula return Char is

   NumberIN float:=round(:CF_1,2); 

   InvalidNumberFormatModel                     EXCEPTION;

     PRAGMA EXCEPTION_INIT(InvalidNumberFormatModel,-1481);

     InvalidNumber                                   EXCEPTION;

     PRAGMA EXCEPTION_INIT(InvalidNumber,-1722);

     TYPE GroupTableType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

     ConversionType        CHAR(6)                  := '';

     GroupTable                                     GroupTableType;

     GroupIndex                                     NUMBER;

   Words                                           VARCHAR2(2000);

     WholePart                                     NUMBER;

     FractionalPart                                 NUMBER;

     FractionalDigits                             NUMBER;

     Remainder                                     NUMBER;

     Remainder1                                     NUMBER;

     Remainder2                                     NUMBER;

     Suffix                                           VARCHAR2(50);

   BEGIN

      GroupTable(0)    := '';

        GroupTable(1)    := ' ten';

        GroupTable(2)    := ' hundred';

        GroupTable(3)    := ' thousand';

        GroupTable(4)    := ' ten thousand';

        GroupTable(5)    := ' lakh ';

        GroupTable(6)    := ' ten lakh ';

        GroupTable(7)    := ' crore ';

        GroupTable(8)    := ' ten crore ';

        GroupTable(9)    := ' hundred crore ';

        GroupTable(10)    := ' thousand crore ';

        GroupTable(11)    := ' ten thousand crore ';

        GroupTable(12)    := ' lakh crore ';

        GroupTable(13)    := ' ten lakh crore ';

        GroupTable(14)    := ' hundred lakh crore ';

        GroupTable(15)    := ' thousand lakh crore ';

        GroupTable(16)    := ' ten thousand lakh crore ';

        GroupTable(17)    := ' lakh lakh crore ';

        GroupTable(18)    := ' ten lakh lakh crore ';

        GroupTable(19)    := ' crore crore ';

           WholePart    := ABS(TRUNC(NumberIN));  -- Calculate whole and fractional parts

           FractionalPart    := ABS(NumberIN) - WholePart;

           IF FractionalPart = 0 THEN         -- Check if fractional part is 0

              Words    := 'zero paise';

                     Suffix        := ' and ';

           ELSE

              IF ConversionType = 'N' THEN

                   FractionalDigits    := LENGTH(TO_CHAR(FractionalPart)) - 1;

                IF FractionalDigits > 15 THEN

                   RAISE InvalidNumber;

                END IF;

                Suffix            := GroupTable(FractionalDigits) || 'th';

                FractionalPart        := FractionalPart *    POWER(10,FractionalDigits);

           ELSE

                IF LENGTH(TO_CHAR(FractionalPart)) > 3 THEN

                   RAISE InvalidNumber;

                 END IF;

                       FractionalPart        := FractionalPart * 100;

                   IF FractionalPart = 1 THEN

                      Suffix        := ' Paise';

               ELSE

                  Suffix        := ' Paise';

                   END IF;

           END IF;

              IF FractionalPart <= 99999 THEN

                   Words    := TO_CHAR(TO_DATE(FractionalPart,'j'),'Jsp') ||Suffix;

              ELSE

                  GroupIndex    := 0;

                      WHILE FractionalPart != 0

                 LOOP

                     Remainder    := MOD(FractionalPart,1000);

                        IF Remainder != 0 THEN

                           Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||    GroupTable(GroupIndex) || Words;

                        END IF;

                        GroupIndex    := GroupIndex + 3;

                        FractionalPart:= TRUNC(FractionalPart / 1000);

                    END LOOP;

                    Words    := Words || Suffix;

              END IF;

              Suffix        := ' and ';

          END IF;

             IF WholePart = 0  THEN

                  IF ConversionType = '' THEN

                     Words    := 'zero ' || Suffix || Words;

                  ELSE

                  Words    := 'zero' || Suffix || Words;

                  END IF;

             ELSE

                IF WholePart = 1 THEN

                     Suffix    := ' Rupee' || Suffix;

                  ELSE

                     Suffix    := '' || Suffix;

                  END IF;

                IF WholePart <= 99999 THEN

                     Words    := TO_CHAR(TO_DATE(WholePart,'j'),'Jsp') ||Suffix || Words;

                ELSE

                     IF LENGTH(TO_CHAR(WholePart)) > 15 THEN

                        RAISE InvalidNumber;

                     END IF;

                     GroupIndex    := 0;

                     Words        := Suffix || Words;

                     WHILE WholePart != 0

                    LOOP

                           IF WholePart < 10000000 THEN

                       Remainder    := MOD(WholePart,100000);

                              IF Remainder != 0 THEN

                        

                              Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;

                                END IF;

                              GroupIndex    := GroupIndex + 5;

                              WholePart    := TRUNC(WholePart / 100000);

                       ELSE

                       Remainder    := MOD(WholePart,10000000);

                              IF Remainder != 0 THEN

                          IF Remainder >= 100000 THEN

                          Remainder2 := MOD(Remainder,100000);

                          Words    :=  TO_CHAR(TO_DATE(Remainder2,'j'),'Jsp') || words;

                          Remainder1 := trunc(Remainder/100000);

                              Words    := TO_CHAR(TO_DATE(Remainder1,'j'),'Jsp') ||' lakh '|| Words;

                          ELSE

                          Words    := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;

                          END IF;

                          END IF;

                              GroupIndex    := GroupIndex + 7;

                              WholePart    := TRUNC(WholePart / 10000000);

                       END IF;

                        END LOOP;

                END IF;

             END IF;

             IF Words IS NULL THEN

                Words    := 'zero';

             END IF;

             IF SIGN(NumberIN) = -1 THEN

                Words    := 'minus ' || Words;

             END IF;

             return Words||' Only';

   EXCEPTION

     WHEN OTHERS THEN

       RETURN ' ';                  

   END;

Note : For INR currency of India

Thursday 28 June 2012

COMPLETE RECOVERY


Complete Recovery

Recovery in Noarchivelog Mode


Steps
Explanation and Commands
1
Restore all datafiles, control files, and redo log files from the backup to the original location.
 $ cp   -r   /u01/backup/*    /u02/oracle/data

2
Restart the database
 SVRMGR> startup open

3
Notify users that they will need to reenter data since the time of the last backup.

Advantages

·         Easy to perform, with low risk of error.
·         Recovery time = Time to restore all files.

Disadvantages

·         The entire database is restored to the point of the last whole closed backup.
·         Data is lost and must be reapplied manually.

Example :             The hard disk 1 is damaged and you have one Oracle datafile named user_01.dbf in this disk. You need to recover the database and restore user_01.dbf to another hard disk.

$ cp  <backup directory>  <data directory>

SVRMGR> connect / as sysdba
Connected.

SVRMGR> startup mount
Oracle instance started.

SVRMGR> alter database rename file
     2> ‘/disk1/data/user_01.dbf’
     3> to ‘/disk2/data/user_01.dbf’;
Statement Processed.

SVRMGR> alter database open;
Statement Processed.

Complete Recovery in Archivelog Mode


Steps
Explanation
1
Restore only lost or damaged datafiles.
2
Do not restore the control file, redo logs,  password, or parameter files.
3
Recover the datafiles.

Advantages
 ·         Only need to restore lost files
·         Recovers all data to the time of failure
·         Recovery time = Time to restore lost files and apply all archived logs

Disadvantages
 ·         Must have all archived logs since the  backup from which you are restoring

Cases Study

 CASE 1 : Further investigation reveals that corrupt blocks were found on disk 2 where datafile 2 is stored. From your well documented database records, you have determined that datafile 2 is one of the files belonging to the system tablespace.

Steps
Explanation and Commands
1
Restore the damaged file from backup (the most recent if available):

$ cp /disk1/backup/df2.dbf /disk2/data/

2
Start the instance in mount mode

SVRMGR> startup mount

3
To bring the datafile to the point of failure, all needed archived logs and redo logs are applied.

SVRMGR> recover datafile ‘/disk2/data/df2.dbf‘;
or
SVRMGR> recover database;

4
When recovery is finished, all datafiles are synchronized. Open the database.

SVRMGR> alter database open;


CASE 2 : Your training DBA immediately informs you that it was not media failure he accidentally removed datafile number 2 using operating system commands. The database is currently open and you don’t want to shut down the database.

Steps
Explanation and Commands
1
The database is currently open, so to determine which tablespace the datafile belongs to, use the following command:

SQL > select file_id f#, file_name,
  2 > tablespace_name tablespace, status
  3 > from dba_data_files;

F#   FILE_NAME                   TABLESPACE  STATUS
---  --------------------------  ----------  ----------
1    /disk1/data/system_01.dbf   SYSTEM      AVAILABLE
2    /disk2/data/df2.dbf         USER_DATA   AVAILABLE
3    /disk1/data/rbs01.dbf       RBS         AVAILABLE
...
2
Determine whether we need to take datafile 2 offline (in this case, Oracle has already taken the file offline):

SQL > select d.file# f#, d.name, d.status, h.status
  2 > from v$datafile d, v$datafile_header h
  3 > where d.file# = h.file#;

F#   D.NAME                       D.STATUS   H.STATUS
---  --------------------------   ---------  ----------
1    /disk1/data/system_01.dbf    SYSTEM     ONLINE
2    /disk2/data/df2.dbf          RECOVER    OFFLINE
3    /disk1/data/rbs_01.dbf       ONLINE     ONLINE
...
3
Since the file is offline, the file can now be restored successfully:

cp /disk1/backup/df2.dbf /disk2/data/

4
Use the “recover” commands to apply the archives and the redo logs to the restored data file.

SVRMGR> recover datafile ‘/disk2/backup/df2.dbf‘;
or
SVRMGR> recover tablespace USER_DATA;

5
When recovery is finished, all datafiles are synchronized. Bring the datafile online:

SVRMGR> alter database datafile ‘/disk2/data/df2.dbf‘ online;
or
SVRMGR> alter tablespace USER_DATA online;


Case 3 : From your investigation, you have known that datafile 2 is damaged. From your familiarity with the database, you know datafile 2 is not a system or rollback segment datafile, nor will it prevent users running their end-of-month reports. You want to recover the database and minimize the down time also.

Steps
Explanation and Commands
1
Mount the database. It will not open because datafile 2 cannot be opened.

SVRMGR > startup mount
Database mounted.

2
If the datafile is not offline, the database will not open. Therefore the file must be taken offline. You have queried V$DATAFILE and determined that the file is online. The following command must therefore be issued:

SVRMGR > alter database datafile ‘/disk2/data/df2.dbf‘ offline;
Statement processed.

Note: The “alter tablespace” command cannot be used here since the database is not opened yet.

3
The database can now be opened:

SVRMGR > alter database open;
Statement processed.

4
Now that users can access the system, you have minimized the down time. Then you restore the file from the backup directory.

$ cp /disk1/backup/df2.dbf /disk3/data/




5
Use the “recover” commands to start applying the archives and the redo logs to the restored datafile.

SVRMGR> recover datafile ‘/disk3/data/df2.dbf‘;
or
SVRMGR> recover tablespace USER_DATA;

6
When recovery is finished, all datafiles are synchronized. Bring the datafile online:

SVRMGR> alter database datafile ‘/disk3/data/df2.dbf‘ online;
or
SVRMGR> alter tablespace USER_DATA online;


Thursday 21 June 2012

Clone Database & Application using Hot Backup Technique


Please Visit http://www.conacent.com/?page_id=218

Clone Database & Application using Hot Backup Technique

1. Take the database to Archivelog Mode on the Source DB.
2. Run adpreclone.pl on both the tier (DBTier & ApplTier).
3. Login to source Database Server
    $su - oracle
    $sqlplus '/as sysdba'
4. Backup the control file using following command
    SQL> alter database backup controlfile to trace;
    [This file .trc will be created on $ORACLE_HOME/admin/SID_HOSTNAME/diag/rdbms/SID/SID/trace with a name SID_ora_number.trc]

5. Put the database on Backup Mode using following command
    SQL> alter database begin backup;

6. Copy all the .dbf from source to target
7. After copying the .dbf to target remove the database from Backup Mode.
    SQL> alter database end backup;

8. On Source DB, Make the archvie logs current
    SQL> alter system archive log current;

9. Copy tech_st directory from source DB to target DB. Alone with the tracefile.
   Remove control file from target DB (cntrl*.dbf)

10. Configuring the target database
    # chown -R oracrp:dba /d02/crpappl
    # chmod -R 777 /d02/crpappl
    # su - oracrp
    $ cd $ORACLE_HOME/appstuil/clone/bin
    $ perl adcfgclone.pl dbTechStack
        [Fill up all the question answer]

11. Setup the environment file of the targeted DB.
12. Edit the trace file name which was copied from source to target DB
    [SID_ora_<Number>.trc]
    a. Remove all the line before Startup nomount
    b. Replace REUSE to SET
    c. Replace source DB SID to target DB SID
    d. Replace NORESTLOGS to RESTLOGS
    e. Replace ARCHIVELOG to NOARCHIVELOG
    f. Remove all the line after CHARACTER SET statement
    g. Replace the source mount point to target server mount point.

13. Login to target server database O/S user
    # su - oracrp
    $ sqlplus '/as sysdba'
    SQL> startup nomount pfile=$ORACLE_HOME/dbs/initCRP.ora;
    SQL> @SID_ora_<number>.trc    [Modified control tracefile]
        {It will create new control file them database will be mounted}
    SQL> recover database using backup controlfile until cancel;
    SQL> alter database open resetlogs;

14. Create temporary tablespace if not created in target
    a. Check if TEMP tablespace had tempfiles or datafiles
        SQL> select file_name, tablespace_name, status, autoextensible
            from DBA_TEMP_FILES
            where tablespace_name like 'TEMP%';

    b. Add Temp files
        SQL> alter tablespace TEMP1 add tempfile '<path of .dbf file temp01.dbf>'
            size 1024M reuse autoextend off;
        SQL> alter tablespace TEMP1 add tempfile '<path of .dbf file temp02.dbf>'
            size 1024M reuse autoextend off;

   c.  Set the temporary tablespace to TEMP1
    SQL>alter database default temporary tablespace TEMP1;


15. Run the library update script against the database
    # su - oracrp
    $ cd $ORACLE_HOME/appsutil/install/SID_hostname
    $ sqlplus '/as sysdba'
    SQL> @adupdlib.sql so;

16. Configure the target database (the database must be open)
    $ cd $ORACLE_HOME/appsutil/clone/bin
    $ perl adcfgclone.pl dbconfig <Path of the context file>

17. Configure the target Application Tier
    a. Copy all the filesystem from source to target server.
    b. Change owner & mode
        # chown -R applcrp:dba <Path where it is kept>
        # chown -R 777 applcrp:dba <Path where it is kept>
    c. Run the post clone of application
        # cd $COMMON_TOP/clone/bin
        # perl adcfgclone.pl appsTier
       


How to find Components version & Patchset level in R12


Please Visit http://www.conacent.com/?page_id=218

Apache Version
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v

You will see output like:

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19

Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server
Above is output If you have installed 10g Application Server with 11i


Jinitiator Version

11i
grep jinit_ver_name $OA_HTML/bin/appsweb_SID_HOSTNAME.cfg

R12
grep jinit_ver_name $OA_HTML/bin/appsweb.cfg

You will see output like:

jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ;
if your version is 1.3.1.18 you will see entry like 1,3,1,18



Perl Version
$IAS_ORACLE_HOME/perl/bin/perl -v|grep built


Java Version

sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"


Jre version
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35


Forms Version
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version



Plsql Version
$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version


Forms Communication mode
cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"


Oracle Applications R12 Patchset level
As applmgr or environment owner
1. Source environment
2. login as apps
3. run $AD_TOP/sql/adutconf.sql


column BUG format a8;
 column PATCH format a60;
 set linesize 100;
 set pagesize 200;
 select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
 4440000, 'Oracle Applications Release 12 Maintenance Pack',
 5082400, '12.0.1 Release Update Pack (RUP1)',
 5484000, '12.0.2 Release Update Pack (RUP2)',
 6141000, '12.0.3 Release Update Pack (RUP3)',
 6435000, '12.0.4 RELEASE UPDATE PACK (RUP4)',
 5907545, 'R12.ATG_PF.A.DELTA.1',
 5917344, 'R12.ATG_PF.A.DELTA.2',
 6077669, 'R12.ATG_PF.A.DELTA.3',
 6272680, 'R12.ATG_PF.A.DELTA.4',
 7237006, 'R12.ATG_PF.A.DELTA.6',
 8919491, 'R12.ATG_PF.B.delta.3',
 6728000, '12.0.6 RELEASE UPDATE PACK (RUP6)',
 9239089, 'R12.AD.B.delta.3',
 9114911, 'R12.HR_PF.B.delta.3',
 13418800, 'R12.HR_PF.B.delta.5',
 16000686, 'R12.HR_PF.B.delta.6',
 9239090, '12.1.3',
 9239089, 'R12.AD.B.delta.3',
 17884289, 'R12.AD.B.delta.4',
 19278976, 'R12.AD.B.DELTA.5',
 19277598, 'R12.ADO.B.DELTA.6',
 18004477, 'Release 12.1 HRMS RUP7 ',
 19429224, 'R12.OPM_PF.B.Delta.5 ',
 20363553, 'R12.OPM_PF.B.delta.6',
 18618946, 'R12.WMS.B.delta.9',
 19447782, 'R12.WMS.B.delta.10',
 7651091, 'R12.ATG_PF.B.delta.2',
 8502056, 'R12.AD.B.Delta.2',
 9245674, 'R12.SCM_PF.B.delta.3',
 20745242, 'R12.AD.C.delta.7',
 20784380, 'R12.TXK.C.DELTA.7 '
 ) PATCH
 from AD_BUGS b
 where b.BUG_NUMBER in ('4440000','5082400','5484000','6141000','6435000', '9239090', '9239089', '19278976', '19277598','8919491', '5907545','5917344','6077669','6272680','7237006','6728000', '9239089', '9114911','13418800', '18004477', '16000686', '19429224','18618946','7651091','8502056','19447782', '20363553', '17884289','9245674', '20745242', '20784380')
 order by patch;



http://www.oracle-base.com/dba/scripts.php

Wednesday 20 June 2012

Oracle EBS Application and Database (11.1.0.7) Running Slow


Please Visit http://www.conacent.com/?page_id=218


 Problem :-
 Application and Database Slows down for 15-30 mins.
I have observed that one of the M00N background process taking huge resources. The Load increases to 6-7. I have found in the trace file of M00N some error. Error found in M00N trace file. Error is as follows
" kewrpanp - Clearing the error, continue to next tableDDE: Problem Key 'ORA 12751' was flood controlled (0x6) (no incident)
ORA-12751: cpu time or run time policy violation
*** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or run time policy violation
)
*** SQLSTR: total-len=350, dump-len=240,
STR={delete from WRH$_MEM_DYNAMIC_COMP tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and }
DDE: Problem Key 'ORA 12751' was flood controlled (0x6) (no incident)
ORA-12751: cpu time or run time policy violation
kewrpanp - Failed to purge non-partitioned table, tbid=103, errcode=13509"


Solution :-
These are the post installation steps. (after you have applied patch 10279045)

Post-install steps:
-------------------------
Apply the patch using OPatch (the normal way you would apply the patch)
( Note : Steps 1 and 5 to be followed for RAC environment only)
.
1. For RAC environment only,
set cluster_database=false in the init.ora
.
2. Startup the database in upgrade mode
SQL> startup upgrade
.
3. After the patch has been applied please reload the packages into
the database. To do this connect as SYSDBA and execute the following;
(Note: Order is important)
.
SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstai.plb
SQL> @?/rdbms/admin/prvtstat.plb
.
4. Shutdown the database
.
5. For RAC environment only,
set cluster_database=true in the init.ora
.
6. Startup the database in normal mode
SQL> startup



Could you please confirm that you implemented the below things.
1. Check from SYSADMIN for IO throughput of the host.

2. Manual purging of old Optimizer STATS.
REFER : SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
REFER : Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging (Doc ID 454678.1)
REFER : Suggestions if your SYSAUX Tablespace grows rapidly or too large (Doc ID 1292724.1)

Wednesday 13 June 2012

Scheduler/Prereleaser Manager is showing status System Hold, Fix Manager before resetting counters


Please Visit http://www.conacent.com/?page_id=218

Problem description -
"Scheduler/Prereleaser Manager" is showing status "System Hold, Fix Manager before resetting counters".

Problem solution -
To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any dead process is
running.

2. Stop the database.

3. Start the database.

4. Go to cd $FND_TOP/bin
$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDFS"
$ adrelink.sh force=y link_debug=y "fnd FNDCRM"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit).
Note 134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

6. Execute the following SQL:
select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where
CONCURRENT_QUEUE_NAME like 'FNDSM%';

7. Start the middle tier services including your concurrent manager.

8. Retest the issue.