Website Design, Website Development, Shopping Carts, Content Management Systems, Custom Programming, Custom Applications Appsdba: January 2008

Oracle Applications, Middleware, Database, DB RAC & HA

It is place where anyone grab more knowledge about Oracle Applications, middleware etc.

  
APPS R12
  • File System
  • Tech Stack
  • Admin Scripts
  • Forms Mode
    • APPS 11i
  • Know more
  • Maintenance Mode
  • Maintenance Mode(New)
  • Patching
  • APPS Scripts
  • Database Scripts
  • Protect APPS Password
  • Concurrent Manager
  • Database Refersh
  • Patchset Utility
  • Forms Data Loader
  • Web Server
  • Space Required
  • Responsibilities Of APPSDBA
    • OAS 9i/10gR2
  • Software Load Balancer
    • OAS 10gR3
  • Components
  • HTTP Server as Port 80
    • Database
  • Enable Archive
  • Clear Temp Tablespace
  • Drop DB (New in 10g)
  • Database Performance
  • Oracle Process
    		•
    Advanced Topic
  • Database RAC

  • Do you know
  • Clusterware
  • Manage RAC Environment
  • High Availability
    		•
    Certifications
  • EBS R12 Beta Exam
  • APPS 11i - 1z0-233
  • RAC 10gR2 - 1z0-048
  • Oracle Certifications
    • DBA Tools
  • TOAD
  • KEEP TOOL
  • ORACLE's SQL DEVELOPER
  • Spotlight on Oracle E-Business
  • DBDiff For Oracle

    • What Products Are Installed in Applications 11i !!!!!
    Tuesday, January 29, 2008
    There are are several ways to find out which products are installed, shared or
    inactive for choosen product family and products. Please use any of the options
    listed below.

    1. Run Licence Manager

    2. Run the following script in Sqlplus as apps user:

    cd $AD_TOP/sql/adutconf.sql
    Sqlplus> @adutconf.sql

    This creates a text file adutconf.lst in the current working directory.

    3. View the file $APPL_TOP/admin/applprod.txt file

    4. Run the following queries in Sqlplus as apps user:

    a) Find all products and statuses:

    select application_id, status
    from fnd_product_installations;

    b) Find the status of a particular product:

    E.g. Application_id:
    PER (Human Resources) = 800
    PAY (Payroll) = 801

    select application_id, status
    from fnd_product_installations;
    where application_id = '%80%';

    Status:
    I = Installed
    S = Shared
    N = Not Installed

    OR

    1) Download patchsets.sh from
    ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh
    Copy all contents
    And create file patchsets.sh

    2) FTP this file to server the application server.

    3) Change the permission of this file.

    4) Logon Application User
    i.e su – applmgr

    5) Go to the file location and run patchsets.sh utility.
    i.e cd /oracle1/patchset/
    $ ls –lrt
    -rwxrwxrwx 1 root root 693509 Jan 29 15:00 patchsets.sh
    6) Run the Patchset Comparison Utility - patchsets.sh
    ./patchsets.sh connect=apps/apps
    It will proceed like this …….
    OUT_DIR set to: /tmp by default (set CUST_TEMP if you want to customize this)
    Connecting to database with parameters passed using tool version: 4.39
    Getting Appsversion Version from the Database
    ... DETERMINED DB VERSION: 11.5.10.2
    Finished: Setup_Env
    ... Extracting from AD_BUGS & AD_APPLIED_PATCHES into: Patch.csv
    Running query on ad_patch_driver_minipks adds all patchsets applied.
    ... FOUND: 2 patchset in AD_PATCH_DRIVER_MINIPKS see: Applied_Ad_Minipks.csv
    ... Converting Patch Names to Patch Numbers. (using XX_patchsets.txt)
    ... Running Applied_Patches_Report.sql 11i sql: Applied_Patches_11i.sql
    ... Created Patches Applied Report: Applied_Patches_11i.txt
    ... Appending Minipks bug numbers to Patch.csv from ad_patch_driver_minipks table
    ... FOUND: 88585 records in AD_BUGS & AD_APPLIED_PATCHES
    ... Generated csv Patch datafile from the ad_xyz tables or Database: applptch=Patch.csv
    Finished: Get_Version (Next Step is slowest)
    ... USING DB Patch Information: Patch.csv (From Database)
    Done matching 980 patchsets(aru) to patchlist(Patch.csv)
    Finished: Compare_Patches (Report.txt)
    Starting: Report_11i.txt

    FAMILY PACK PATCHES
    Product Baseline Version Running Version Latest Available, Status
    ------- ----------------- --------------- -----------------------
    atg_pf 11i.ATG_PF.H(3438354)11i.ATG_PF.H.6(5903765), Rel- By_Metal
    bis_pf 11i.BIS_PF.D.1(4054609) 11i.BIS_PF.H(5565583), Rel- By_Metal
    ………………..

    It will take 15-30 minutes to complete.

    7) In the current directory path it will create file called Report_11i.txt.


    Reference :
    Note : 146139.1
    posted by Jaswinder Singh @ 2:08 AM   0 comments
    Check Database Performance by these queries !!!!
    Monday, January 28, 2008
    SELECT * FROM v$log;


    SELECT COUNT(*) FROM v$process;


    SELECT COUNT(*) FROM v$session WHERE status='ACTIVE'


    SELECT COUNT(*) FROM dba_locks WHERE blocking_others='Blocking';


    SELECT COUNT(*) FROM dba_objects WHERE status='INVALID' AND owner IN ('ABC','DEF') GROUP BY owner;


    SELECT A.Tablespace_Name,TRUNC((SUM(A.Tots)/1024/1024),3) Tot_Size,
    TRUNC(((SUM(A.Tots)/1024/1024)-(SUM(A.Sumb)/1024/1024)),3) Tot_Used
    FROM (
    SELECT Tablespace_Name,0 Tots,SUM(Bytes) Sumb,
    MAX(Bytes) Largest,COUNT(*) Chunks
    FROM Sys.Dba_Free_Space A
    GROUP BY Tablespace_Name
    UNION
    SELECT Tablespace_Name,SUM(Bytes) Tots,0,0,0
    FROM Sys.Dba_Data_Files
    GROUP BY Tablespace_Name) A, V$instance B
    GROUP BY A.Tablespace_Name
    ORDER BY A.Tablespace_Name


    SELECT (1-(SUM(DECODE(name,'physical reads', value, 0))/
    (SUM(DECODE(name,'db block gets', value, 0)) +
    SUM(DECODE(name,'consistent gets', value, 0))))) * 100 "Read Hit Ratio"
    FROM v$sysstat


    SELECT d.status "Status",
    d.tablespace_name "Name",
    d.CONTENTS "Type",
    d.extent_management "Extent Management",
    TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
    TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)",
    TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %"
    FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
    FROM dba_data_files GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) bytes
    FROM dba_free_space GROUP BY tablespace_name) f
    WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
    AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
    UNION ALL
    SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type", d.extent_management "Extent Management",
    TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
    TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)",
    TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %"
    FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
    FROM dba_temp_files GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes_cached) bytes
    FROM v$temp_extent_pool GROUP BY tablespace_name) t
    WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
    AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY'
    ORDER BY 7


    SELECT COUNT(*) FROM tab
    posted by Jaswinder Singh @ 2:04 AM   1 comments
    Some Steps To Do Database Refresh in APPS....
    Thursday, January 17, 2008
    Now I do database refresh From Clone System To Source B'coz.

    1) Start the database and Applications Tier for testing purpose whether it is working.
    **** Log on Database user In my Case :
    su - dbtier
    check the value of $ORACLE_HOME
    echo $ORACLE_HOME
    cd $ORACLE_HOME/appsutil/scripts/clone_apps/
    ./addlnctl.sh start clone
    lsnrctl status clone
    ./addbctl.sh start
    sqlplus '/as sysdba'
    select status from v$instance
    **** Log on Applications user In my Case
    su - apptier
    check the value $COMMON_TOP or $OAD_TOP
    cd $COMMON_TOP/admin/scripts/clone_apps/
    ./adstrtal.sh apps/apps --- This script will start the applications Tier including all the servers.
    2) Test the applications
    http://host_name:port --- Yes It is working Fine Now we need to shutdown the Applications Tier as well as Database Tier.

    3) Shutdown
    **** Firstly Stop the Applications Tier.
    Log on Applications user In my Case
    su - apptier
    check the value $COMMON_TOP or $OAD_TOP
    cd $COMMON_TOP/admin/scripts/clone_apps/
    ./adstpall.sh apps/apps
    *** Now Shutdown the Database Neatly and cleanly.
    Log on Database user In my Case :
    su - dbtier
    check the value of $ORACLE_HOME
    echo $ORACLE_HOME
    cd $ORACLE_HOME/appsutil/scripts/clone_apps/
    ./addlnctl.sh stop clone
    lsnrctl status clone
    ./addbctl.sh stop
    sqlplus '/as sysdba'
    select status from v$instance
    Note -- Now follow the document 7 steps for database refreshment.......

    4) Startup the database in restrict mode.
    sqlplus '/as sysdba'
    startup restrict
    5) Create a Control file to trace.
    alter database backup contolfile to trace;
    check out latest trace file under the
    $ORACLE_HOME/admin/clone_apps/udump
    In my Case that is clone_ora_8880.trc
    nad rename it as .sql format
    i.e mv clone_ora_8880.trc ctrclone.sql
    6) Shutdown the Database.
    sqlplus '/as sysdba'
    shutdown immediate;
    7) Copy initclone,ctrlclone.sql and datafiles to VIS system.
    In My Case
    Copy Initialize parameter file.....
    cd clone/visdb/9.2.0/dbs/
    cp -Rf initclone.ora /u01/oracle/visdb/9.2.0/dbs/
    cp -Rf clone_apps_ifile.ora /u01/oracle/visdb/9.2.0/dbs/
    Copy Ctrlclone.sql file
    cd admin/clone_apps/udump/
    cp -Rf ctrclone.sql /u01/oracle/visdb/9.2.0/admin/
    Copy datafiles
    cd /u01/clone/
    cp -Rf visdata /u01/oracle/
    8) Change the permission at OS level
    In my case
    As root user issues these commands....
    chmod -Rf 755
    9) Now edit the init parameter file
    change the parameter
    db_name = vis
    control_files= /u01/oracle/visdata/cntrl01.dbf,/u01/oracle/visdata/cntrl02.dbf,/u01/oracle/visdata/cntrl03.dbf
    user_dump_dest = /u01/oracle/visdb/9.2.0/admin/vis_apps/udump
    background_dump_dest = /u01/oracle/visdb/9.2.0/admin/vis_apps/bdump
    core_dump_dest = /u01/oracle/visdb/9.2.0/admin/vis_apps/cdump
    utl_file_dir = /usr/tmp,/usr/tmp,/u01/oracle/visdb/9.2.0/appsutil/outbound/vis_apps,/usr/tmp
    IFILE=/u01/oracle/visdb/9.2.0/dbs/vis_apps_ifile.ora
    10 Make changes in the ctrlclone.sql
    In my case which is located at /u01/oracle/visdb/9.2.0/admin/
    vi ctrlclone.sql
    STARTUP NOMOUNT pfile=/u01/oracle/visdb/9.2.0/dbs/initVIS.ora
    CREATE CONTROLFILE set DATABASE "VIS" RESETLOGS NOARCHIVELOG
    -- SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 3630
    LOGFILE
    GROUP 1 '/u01/oracle/visdata/log3.dbf' SIZE 50M,
    GROUP 2 '/u01/oracle/visdata/log2.dbf' SIZE 50M,
    GROUP 3 '/u01/oracle/visdata/log1.dbf' SIZE 50M
    -- STANDBY LOGFILE
    DATAFILE
    '/u01/oracle/visdata/sys1.dbf',
    '/u01/oracle/visdata/sys2.dbf',
    '/u01/oracle/visdata/sys3.dbf',
    '/u01/oracle/visdata/sys4.dbf',
    '/u01/oracle/visdata/sys5.dbf',
    '/u01/oracle/visdata/sys6.dbf',
    '/u01/oracle/visdata/sys7.dbf',
    '/u01/oracle/visdata/undo01.dbf',
    '/u01/oracle/visdata/undo02.dbf',
    '/u01/oracle/visdata/undo03.dbf',
    '/u01/oracle/visdata/undo04.dbf',
    '/u01/oracle/visdata/archive1.dbf',
    '/u01/oracle/visdata/archive2.dbf',
    '/u01/oracle/visdata/media1.dbf',
    '/u01/oracle/visdata/media2.dbf',
    '/u01/oracle/visdata/media3.dbf',
    '/u01/oracle/visdata/nologging1.dbf',
    '/u01/oracle/visdata/queues1.dbf',
    '/u01/oracle/visdata/queues2.dbf',
    '/u01/oracle/visdata/reference1.dbf',
    '/u01/oracle/visdata/reference2.dbf',
    '/u01/oracle/visdata/summary1.dbf',
    '/u01/oracle/visdata/summary2.dbf',
    '/u01/oracle/visdata/summary3.dbf',
    '/u01/oracle/visdata/summary4.dbf',
    '/u01/oracle/visdata/summary5.dbf',
    '/u01/oracle/visdata/tx_data1.dbf',
    '/u01/oracle/visdata/tx_data2.dbf',
    '/u01/oracle/visdata/tx_data3.dbf',
    '/u01/oracle/visdata/tx_data4.dbf',
    '/u01/oracle/visdata/tx_data5.dbf',
    '/u01/oracle/visdata/tx_data6.dbf',
    '/u01/oracle/visdata/tx_data7.dbf',
    '/u01/oracle/visdata/tx_data8.dbf',
    '/u01/oracle/visdata/tx_data9.dbf',
    '/u01/oracle/visdata/tx_data10.dbf',
    '/u01/oracle/visdata/tx_data11.dbf',
    '/u01/oracle/visdata/tx_idx1.dbf',
    '/u01/oracle/visdata/tx_idx2.dbf',
    '/u01/oracle/visdata/tx_idx3.dbf',
    '/u01/oracle/visdata/tx_idx4.dbf',
    '/u01/oracle/visdata/tx_idx5.dbf',
    '/u01/oracle/visdata/tx_idx6.dbf',
    '/u01/oracle/visdata/tx_idx7.dbf',
    '/u01/oracle/visdata/tx_idx8.dbf',
    '/u01/oracle/visdata/tx_idx9.dbf',
    '/u01/oracle/visdata/tx_idx10.dbf',
    '/u01/oracle/visdata/tx_idx11.dbf',
    '/u01/oracle/visdata/apps_ts_tx_interface.dbf',
    '/u01/oracle/visdata/ctx1.dbf',
    '/u01/oracle/visdata/odm.dbf',
    '/u01/oracle/visdata/olap.dbf',
    '/u01/oracle/visdata/owa1.dbf',
    '/u01/oracle/visdata/portal.dbf',
    '/u01/oracle/visdata/mobile01.dbf'
    CHARACTER SET UTF8
    ;

    11) Open the database in resetlog mode.
    alter database open resetlogs
    12) Shutdown the database in normal mode & Take a cold backup as per Oracle recommendation.

    13) Startup database and listener.

    14 Now login as application user
    In my Case su - applmgr
    and run adautocfg.sh
    Go to $COMMON_TOP/admin/scripts/VIS_apps/
    ./adautocfg.sh
    It will ask you APPS Password give APPS password.Wait for result.In My case it gives error,i checked log file then i come to know listener is not running on database.I started lsnrctl start again it gives error then again i checked logfile i got ORACLE error (ORA-25153: Temporary Tablespace is Empty) then i add datafile in temp tablespace as following.
    SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/visdata/tmp1.dbf'
    SIZE 2000M REUSE AUTOEXTEND OFF;
    Tablespace altered.
    Now again i run adautocfg.sh,it runs successfully.
    15) Run AutoConfig on Database tier
    logon Database tier user
    su - oracle
    cd $ORACLE_HOME/appsutil/scripts/VIS_apps/
    ./adautocfg.sh
    It runs successfully.
    16) Now start the application Tier
    logon su - applmgr
    cd $OAD_TOP/admin/scripts/VIS_apps/
    ./adstrtal.sh apps/apps
    17) Test the applications.
    http://host_name:port_no
    login as sysadmin/sysadmin_password.
    posted by Jaswinder Singh @ 10:06 PM   0 comments
    About Me

    Name: Jaswinder Singh
    Home: Bangalore, Karnataka, India
    Certifications: RAC Certified Expert, OCP 9i, 10g & 11i
    About Me: I am working as APPSDBA, have hands on experience on DB RAC, HA on OAS 10g and Oracle Applications.
    See my complete profile
    Previous Post
    Archives
    Links
    Powered by

    Free Blogger Templates

    BLOGGER
    ADVERTISEMENT
     click here
    © 2006 Appsdba .