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

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

  • Single Client Access Name (SCAN) in Oracle RAC 11gr2 !!!!!
    Wednesday, August 31, 2011
    1. Single client access name (SCAN) is the virtual hostname to provide for all clients connecting to the cluster (as opposed to the vip hostnames in 10g and 11gR1).
    2. SCAN is a domain name registered to at least one and up to three IP addresses, either in the domain name service (DNS) or the Grid Naming Service (GNS).
    3. By default, the name used as the SCAN is also the name of the cluster and must be globally unique throughout your enterprise. The default value for the SCAN is based on the local node name. SCAN name must be at least one character long and no more than 15 characters in length, must be alphanumeric - cannot begin with a numeral and may contain hyphens (-). If you require a SCAN that is longer than 15 characters, then select an Advanced installation.
    4. SCAN VIP addresses must be on the same subnet as virtual IP addresses and public IP addresses.
    5. Oracle strongly recommends that you do not configure SCAN VIP addresses in the hosts file. But if you use the hosts file to resolve SCAN name, you can have only one SCAN IP address.
    6. Oracle recommends using 3 SCAN listeners for RAC. If the RAC has 3 nodes, each node will have its own SCAN listener. If there is only 1 SCAN listener configured, then only 1 node would have the SCAN Listener running.

    Status of SCAN IP

    grid@rac1 ~]$ srvctl status scan
    SCAN VIP scan1 is enabled
    SCAN VIP scan1 is running on node rac2

    Status of SCAN Listener

    [grid@rac1 ~]$ srvctl status scan_listener
    SCAN Listener LISTENER_SCAN1 is enabled
    SCAN listener LISTENER_SCAN1 is running on node rac2

    Configure SCAN IP

    [grid@rac1 ~]$ srvctl config scan
    SCAN name: rac-scan, Network: 1/192.168.2.0/255.255.255.0/eth0
    SCAN VIP name: scan1, IP: /rac-scan.localdomain/192.168.2.201

    Configure SCAN Listener

    [grid@rac1 ~]$ srvctl config scan_listener
    SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

    Display the entries in etc host file

    [grid@rac1 ~]$ more /etc/hosts
    # Public
    192.168.2.101 rac1.localdomain rac1
    192.168.2.102 rac2.localdomain rac2
    # Private
    192.168.0.101 rac1-priv.localdomain rac1-priv
    192.168.0.102 rac2-priv.localdomain rac2-priv
    # Virtual
    192.168.2.111 rac1-vip.localdomain rac1-vip
    192.168.2.112 rac2-vip.localdomain rac2-vip
    # SCAN
    192.168.2.201 rac-scan.localdomain rac-scan

    [grid@rac1 ~]$
    Validating SCAN on second Node
    [grid@rac2 ~]$ srvctl status scan
    SCAN VIP scan1 is enabled
    SCAN VIP scan1 is running on node rac2
    [grid@rac2 ~]$ srvctl status scan_listener
    SCAN Listener LISTENER_SCAN1 is enabled
    SCAN listener LISTENER_SCAN1 is running on node rac2
    [grid@rac2 ~]$ srvctl config scan
    SCAN name: rac-scan, Network: 1/192.168.2.0/255.255.255.0/eth0
    SCAN VIP name: scan1, IP: /rac-scan.localdomain/192.168.2.201
    [grid@rac2 ~]$ srvctl config scan_listener
    SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521



    Stop SCAN and SCAN_LISTENER

    [grid@rac1 ~]$ srvctl stop scan_listener
    [grid@rac1 ~]$ srvctl stop scan
    [grid@rac1 ~]$ srvctl status scan
    SCAN VIP scan1 is enabled
    SCAN VIP scan1 is not running
    [grid@rac1 ~]$ srvctl status scan_listener
    SCAN Listener LISTENER_SCAN1 is enabled
    SCAN listener LISTENER_SCAN1 is not running
    [grid@rac1 ~]$ srvctl config scan
    SCAN name: rac-scan, Network: 1/192.168.2.0/255.255.255.0/eth0
    SCAN VIP name: scan1, IP: /rac-scan.localdomain/192.168.2.201
    [grid@rac1 ~]$ srvctl config scan_listener
    SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
    [grid@rac1 ~]$

    Restarting SCAN and SCAN_LISTENER

    [root@rac1 ~]# srvctl start scan
    [root@rac1 ~]# srvctl start scan_listener


    References
    11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained [ID 887522.1]
    posted by Jaswinder Singh @ 9:00 AM   0 comments
    Steps to Stop, Start Oracle Real Application Cluster Services !!!!!
    Monday, January 31, 2011
    1. Login as orale user and Stop the Database
    2. Stop ASM instance
    3. Stop Node App
    4. Login as root and stop Oracle Cluster
    ./crsctl stop crs

    ==========================================
    Startting Oracle Cluster
    ==========================================

    - Login as root user and start the cluster, it will start the rest. of services
    ./crsctl start crs
    - Check the status
    ./crsctl check crs
    ./crs_stat -t
    posted by Jaswinder Singh @ 10:06 AM   0 comments
    Practical Steps of Applying 11g database Oneoff Patch 11.1.0.7.4 !!!!!
    Tuesday, November 30, 2010
    These steps have been followed on RHEL 5.3 after applying Database Patch Set 11.1.0.7.

    --> Download Oneoff patch 9654987

    Issue : there was lower version of Opatch then we have applied latest available patch of Opatch utility.

    [oracle@testdbserver 9654987]$ /oraapp/TESTDB/product/11.1.0/db_1/OPatch/opatch apply
    Invoking OPatch 11.2.0.1.3

    Oracle Interim Patch Installer version 11.2.0.1.3
    Copyright (c) 2010, Oracle Corporation. All rights reserved.


    Oracle Home : /oraapp/TESTDB/product/11.1.0/db_1
    Central Inventory : /oraapp/TESTDB/oraInventory
    from : /etc/oraInst.loc
    OPatch version : 11.2.0.1.3
    OUI version : 11.1.0.7.0
    OUI location : /oraapp/TESTDB/product/11.1.0/db_1/oui
    Log file location : /oraapp/TESTDB/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2010-09-23_09-54-32AM.log

    Patch history file: /oraapp/TESTDB/product/11.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt

    ApplySession applying interim patch '9654987' to OH '/oraapp/TESTDB/product/11.1.0/db_1'

    Running prerequisite checks...
    Provide your email address to be informed of security issues, install and
    initiate Oracle Configuration Manager. Easier for you if you use your My
    Oracle Support Email address/User Name.
    Visit http://www.oracle.com/support/policies.html for details.
    Email address/User Name: jaswinder.singh@wipro.com
    Provide your My Oracle Support password to receive security updates via your My Oracle Support account.
    Password (optional):

    Unable to establish a network connection to Oracle. If your systems require a
    proxy server for outbound Internet connections, enter the proxy server details
    in this format:
    [@][:]
    If you want to remain uninformed of critical security issues in your
    configuration, enter NONE

    Proxy specification: NONE


    OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/oraapp/TESTDB/product/11.1.0/db_1')


    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files and inventory (not for auto-rollback) for the Oracle Home
    Backing up files affected by the patch '9654987' for restore. This might take a while...
    Backing up files affected by the patch '9654987' for rollback. This might take a while...
    Execution of 'sh /oraapp/Oracle_Media/9654987/custom/scripts/pre -apply 9654987 ':


    Return Code = 0

    Patching component oracle.network.listener, 11.1.0.7.0...
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglgr.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglro.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsgcs.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglsn.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib32/libnlsnr11.a" with "network/lib32/libnlsnr11.a/nsglgr.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib32/libnlsnr11.a" with "network/lib32/libnlsnr11.a/nsglro.o"
    ....

    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kghu.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcsj.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcodfdef.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcs.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcto.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgsk.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgi.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgl.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgl2.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgh.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/koll.o"

    Patching component oracle.network.rsf, 11.1.0.7.0...
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libnro11.a" with "lib/libnro11.a/ncrfidsa.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libnro11.a" with "lib/libnro11.a/ncrfgp.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libnro11.a" with "lib32/libnro11.a/ncrfidsa.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libnro11.a" with "lib32/libnro11.a/ncrfgp.o"

    Patching component oracle.rdbms.plsql, 11.1.0.7.0...

    Patching component oracle.rdbms.rman, 11.1.0.7.0...

    Patching component oracle.sdo.locator, 11.1.0.7.0...
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/md/admin/sdomigrt.sql"

    Patching component oracle.sysman.console.db, 11.1.0.7.0...
    Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistoryBean.class"
    Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistoryController.class"
    Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistorySearch.class"
    Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/config/compare/CompareWizSecondConfigBean.class"

    Patching component oracle.rdbms.dbscripts, 11.1.0.7.0...
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/catexp.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/dbmsaqds.plb"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtaqds.plb"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtaqiu.plb"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/catmetx.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtlsib.plb"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtlsby.plb"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtlmd.plb"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/e1001000.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/c1001000.sql"

    Patching component oracle.javavm.server, 11.1.0.7.0...
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/javavm/install/initjvm.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/javavm/install/jvmursc.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/javavm/install/jvm_exp.sql"

    Patching component oracle.precomp.common, 11.1.0.7.0...

    Patching component oracle.rdbms, 11.1.0.7.0...
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/libperfsrv11.a" with "rdbms/lib/libperfsrv11.a/qcodfdef_PERF.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/libknlopt.a" with "rdbms/lib/libknlopt.a/ktd.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/libknlopt.a" with "rdbms/lib/libknlopt.a/jox.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/atb.o"
    Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/aud.o"
    .........

    Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/jlib/CDC.jar" with "/rdbms/jlib/CDC.jar/oracle/CDC/PublishApi.class"
    Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/jlib/CDC.jar" with "/rdbms/jlib/CDC.jar/oracle/CDC/Purge.class"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/view_recompile/recompile_precheck_jan2008cpu.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/view_recompile/view_recompile_jan2008cpu.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/sdo_oct2009.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/bug9016295.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/bug9371993.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/amdcpu.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/apscpu.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/xoqcpu.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/psu/11.1.0.7.4/catpsu.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/psu/11.1.0.7.4/catpsu_rollback.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/catbundle.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/bundledata_PSU.xml"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/incremental_compress.sql"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/ktd.o"
    Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/jox.o"
    Running make for target itnslsnr
    Running make for target client_sharedlib
    Running make for target iwrap
    Running make for target irman
    Running make for target proc
    Running make for target client_sharedlib
    Running make for target iextproc
    Running make for target ioracle
    ApplySession adding interim patch '9654987' to inventory

    Verifying the update...
    Inventory check OK: Patch ID 9654987 is registered in Oracle Home inventory with proper meta-data.
    Files check OK: Files from Patch ID 9654987 are present in Oracle Home.
    Execution of 'sh /oraapp/Oracle_Media/9654987/custom/scripts/post -apply 9654987 ':


    Return Code = 0

    The local system has been patched and can be restarted.


    OPatch succeeded.
    [oracle@testdbserver 9654987]$


    [oracle@testdbserver 9654987]$ cd $ORACLE_HOME/rdbms/admin
    [oracle@testdbserver admin]$ sqlplus /nolog

    SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 23 10:12:14 2010

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

    SQL> CONNECT / AS SYSDBA
    Connected to an idle instance.
    SQL> STARTUP
    SQL> @catbundle.sql psu apply

    1 row created.

    It takes 5-10 mintues to complete.

    SQL> COMMIT;

    Commit complete.

    SQL> SPOOL off
    SQL> SET echo off
    Check the following log file for errors:
    /oraapp/TESTDB/product/11.1.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_TESTDB_APPLY_2010Sep23_10_15_02.log
    SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';

    COUNT(*)
    ----------
    0

    1 row selected.

    SQL> QUIT


    SQL> SELECT NAMESPACE,VERSION,ID,BUNDLE_SERIES,COMMENTS FROM dba_registry_history;

    NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
    ------------------------------ ------------------------------ ---------- ------------------------------ ----------------------------------------
    SERVER 11.1.0.7.0 Upgraded from 11.1.0.6.0
    SERVER 11.1.0.7 4 PSU PSU 11.1.0.7.4

    SQL>


    [oracle@testdbserver admin]$ cd $ORACLE_HOME/cpu/view_recompile
    [oracle@testdbserver view_recompile]$ sqlplus /nolog

    SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 23 10:27:06 2010

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

    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> @recompile_precheck_jan2008cpu.sql

    Running precheck.sql...

    Number of views to be recompiled :2838
    -----------------------------------------------------------------------

    Number of objects to be recompiled :5658
    Please follow the README.txt instructions for running viewrecomp.sql

    PL/SQL procedure successfully completed.

    SQL>





    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> shu immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup upgrade
    ORACLE instance started.

    Total System Global Area 2137886720 bytes
    Fixed Size 2161400 bytes
    Variable Size 939525384 bytes
    Database Buffers 1191182336 bytes
    Redo Buffers 5017600 bytes
    Database mounted.
    Database opened.
    SQL> @view_recompile_jan2008cpu.sql

    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    1 row created.


    Commit complete.

    No. of Invalid Objects is :51
    Please refer to README.html to for instructions on validating these objects

    PL/SQL procedure successfully completed.

    Logfile for the current viewrecomp.sql session is : vcomp_TESTDB_23Sep2010_10_33_13.log
    SQL>


    --> Check the log file for any errors. The log file is in the current directory and is named: vcomp__.log

    [oracle@testdbserver view_recompile]$ cat vcomp_TESTDB_23Sep2010_10_33_13.log

    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.


    1 row created.


    Commit complete.

    No. of Invalid Objects is :51
    Please refer to README.html to for instructions on validating these objects

    PL/SQL procedure successfully completed.

    Logfile for the current viewrecomp.sql session is : vcomp_TESTDB_23Sep2010_10_33_13.log
    [oracle@testdbserver view_recompile]$


    SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';

    COUNT(*)
    ----------
    51

    SQL>

    [oracle@testdbserver view_recompile]$ cd $ORACLE_HOME/rdbms/admin
    [oracle@testdbserver admin]$
    [oracle@testdbserver admin]$
    [oracle@testdbserver admin]$ sqlplus /nolog

    SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 23 10:40:02 2010

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

    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> @utlrp.sql

    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_BGN 2010-09-23 10:40:14

    DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
    DOC> objects in the database. Recompilation time is proportional to the
    DOC> number of invalid objects in the database, so this command may take
    DOC> a long time to execute on a database with a large number of invalid
    DOC> objects.
    DOC>
    DOC> Use the following queries to track recompilation progress:
    DOC>
    DOC> 1. Query returning the number of invalid objects remaining. This
    DOC> number should decrease with time.
    DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
    DOC>
    DOC> 2. Query returning the number of objects compiled so far. This number
    DOC> should increase with time.
    DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
    DOC>
    DOC> This script automatically chooses serial or parallel recompilation
    DOC> based on the number of CPUs available (parameter cpu_count) multiplied
    DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
    DOC> On RAC, this number is added across all RAC nodes.
    DOC>
    DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
    DOC> recompilation. Jobs are created without instance affinity so that they
    DOC> can migrate across RAC nodes. Use the following queries to verify
    DOC> whether UTL_RECOMP jobs are being created and run correctly:
    DOC>
    DOC> 1. Query showing jobs created by UTL_RECOMP
    DOC> SELECT job_name FROM dba_scheduler_jobs
    DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
    DOC>
    DOC> 2. Query showing UTL_RECOMP jobs that are running
    DOC> SELECT job_name FROM dba_scheduler_running_jobs
    DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
    DOC>#

    PL/SQL procedure successfully completed.


    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_END 2010-09-23 10:40:53


    PL/SQL procedure successfully completed.

    DOC> The following query reports the number of objects that have compiled
    DOC> with errors (objects that compile with errors have status set to 3 in
    DOC> obj$). If the number is higher than expected, please examine the error
    DOC> messages reported with each object (using SHOW ERRORS) to see if they
    DOC> point to system misconfiguration or resource constraints that must be
    DOC> fixed before attempting to recompile these objects.
    DOC>#

    OBJECTS WITH ERRORS
    -------------------
    0

    DOC> The following query reports the number of errors caught during
    DOC> recompilation. If this number is non-zero, please query the error
    DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
    DOC> are due to misconfiguration or resource constraints that must be
    DOC> fixed before objects can compile successfully.
    DOC>#

    ERRORS DURING RECOMPILATION
    ---------------------------
    0


    PL/SQL procedure successfully completed.

    Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
    Ultra Search VALIDATE_WK done with no error

    PL/SQL procedure successfully completed.

    SQL>

    SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';

    COUNT(*)
    ----------
    0

    SQL>


    ===> Patchset and OneOff patch is applied successfully to the TEST instance;

    SQL> SELECT * FROM v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for Linux: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production

    SQL>

    SQL> column COMMENTS format a30
    SQL> SELECT NAMESPACE,VERSION,ID,COMMENTS FROM dba_registry_history;

    NAMESPACE VERSION ID COMMENTS
    ------------------------------ ------------------------------ ---------- ------------------------------
    SERVER 11.1.0.7.0 Upgraded from 11.1.0.6.0
    SERVER 11.1.0.7 4 PSU 11.1.0.7.4
    6452863 view recompilation

    SQL>

    SQL> SELECT ACTION_TIME,ACTION,NAMESPACE,VERSION,ID,COMMENTS,BUNDLE_SERIES FROM registry$history where ID = '6452863';

    ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES
    ------------------------------ ---------- ------------------------------ ---------- ---------- -------------------- ------------------------------
    23-SEP-10 10.34.56.655633 AM CPU 6452863 view recompilation

    SQL> SELECT ACTION_TIME,ACTION,NAMESPACE,VERSION,ID,COMMENTS,BUNDLE_SERIES FROM registry$history where ID ='4';

    ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES
    ------------------------------ ---------- ------------------------------ ---------- ---------- -------------------- ------------------------------
    23-SEP-10 10.18.30.433026 AM APPLY SERVER 11.1.0.7 4 PSU 11.1.0.7.4 PSU
    posted by Jaswinder Singh @ 3:57 AM   0 comments
    Oracle Certified Master !!!!!
    Tuesday, August 31, 2010
    OCM is the final stage of mastering ORACLE in specific version .There are some restrictions for OCM

    -> You must be Oracle certified professional.
    -> You must took 2 advance courses prior to OCM.
    -> Recommended but not optional 3 to 4 years of real time experience.

    Heres the list of advance courses.Each course duration is 5 days and course fee is US$ 3000.

    After passing OCM Oracle Corporation host your resume on there website!!!

    Exam Details
    ================
    Exam price : $2000 USD (INR. 1,00,000 approx.)
    Number of Questions : Performance Based Exam
    Duration: 2 days Approx. 17Hrs.

    Note : The exam starts promptly at 9:00 AM on the first day. Plan on finishing no sooner than 6:00 PM. The second day is scheduled from 8:30 AM to 5:00 PM.

    Course Topics
    ==============
    1. Server Configuration
    2. Enterprise Manager Grid Control
    3. Managing Database Availability
    4. Data Management
    5. Data Warehouse Management
    6. Performance Management
    7. Real Application Clusters
    8. Data Guard

    Recommended Experience and Preparation
    =======================================
    • 3-4 years of professional enterprise-level Oracle experience
    ________________________________________
    • Extensive experience with backup, restore and recovery operations
    ________________________________________
    • Proficient with SQL
    ________________________________________
    • Working knowledge of LINUX command language that includes:
    Formatting and executing basic OS command
    Creating and navigating through directory structures
    File management using copy, move, and delete
    Linux environment text editors
    Setting environment variables
    ________________________________________
    • The ability to locate and launch Oracle executables that include:
    RMAN utility
    Oracle Net Manager
    Oracle Net Configuration Assistant
    OEM
    Listener Utility
    OMS
    Oracle Password Utility
    Database Creation Assistant
    DGMGRL (for 11g OCM)
    ________________________________________
    • Proficient with Oracle Enterprise Manager
    ________________________________________
    • Proficient in using Oracle Net Manager and the Oracle Net Configuration Assistant to configure networking
    ________________________________________
    • Advanced knowledge and use of Oracle Enterprise Server technology and features
    ________________________________________
    • Familiarity navigating through online Oracle documentation
    ________________________________________
    • Proficient with using Mozilla 1.6 browser software
    ________________________________________

    Note : There is a 14-day waiting period to retake a failed exam. Passed exams may not be taken again.

    OCM additional benefits
    ========================
    • Distinguished industry-recognized credential, backed by Oracle’s brand equity.
    • OCM professional profile hosted on Oracle Technology Network (OTN) website. Profiles are viewable by over 2.5 million members within the Oracle community.
    • Exclusive use of the OCM logo for business cards, literature, etc.
    • Complimentary OCM Logo embroidered apparel items
    posted by Jaswinder Singh @ 12:23 AM   0 comments
    Steps to Setup Oracle 11g TDE (Transparent Data Encryption)
    Thursday, May 6, 2010
    Introduction

    The Transparent Data Encryption (TDE) feature introduced in Oracle 10g Database Release 2 allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system.

    Oracle Database 11g encrypts data using a master key, which is stored in a secure location called a wallet, which is a file on the database server. Table keys are placed in the data dictionary. Oracle Database 11g generates a single encrypted table key for the table and uses it to encrypt those columns.

    Steps:

    1. Add the following entry in sqlnet.ora under $ORACLE_HOME/network/admin

    ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=file)
    (METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallets)))

    2. Before attempting to create a table with encrypted columns, a wallet must be created to hold the encryption key.

    Creates the wallet in the location specified in sqlnet.ora i.e. /u01/app/oracle/wallets

    Set the password of the wallet as "passwd".

    Open the wallet for TDE

    [oracle@db wallets]$ pwd
    /u01/app/oracle/wallets

    [oracle@db wallets]$ ls -lrt
    total 12
    -rw------- 1 oracle oinstall 8757 May 6 12:56 ewallet.p12

    3. You need to open the wallet and create the master encryption key. Open a SQL*Plus session and execute the following commands:
    connect / as sysdba
    SQL> alter system set key identified by "finnet1";

    4. Open the wallet as below :
    CONN sys/passwd@dbname AS SYSDBA
    SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "myPassword";
    Or
    The wallet must be opened explicitly, after the database instance starts.
    SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "passwd";
    SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "passwd";

    5. The status of the wallet can be viewed using the view v$encryption_wallet:

    SQL> SELECT * FROM v$encryption_wallet;

    WRL_TYPE WRL_PARAMETER STATUS
    -------------------- ------------------------------ ---------
    file /u01/app/oracle/wallets OPEN


    Encrypted Column
    --------------------
    6. Create a test table with an encrypted column and insert some data. Using the ENCRYPT clause on its own is the same as using the ENCRYPT USING 'AES192' clause, as AES192 is the default encryption method.

    For encrypting the data, we can simply use

    SQL> ALTER TABLE EMP MODIFY (SAL ENCRYPT);
    SQL> ALTER TABLE EMP MODIFY (SAL DECRYPT);

    Details of the encrypted columns are stored in DBA_ENCRYPTED_COLUMNS

    SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;
    SQL> SELECT * FROM user_encrypted_columns;

    7. We can explicitly close the wallet using
    SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
    posted by Jaswinder Singh @ 4:19 AM   0 comments
    New Features Introduces in 11g Database !!!!!
    Wednesday, March 31, 2010
    -> Oracle RAC 1, Active-Passive configuration of Oracle RAC
    -> We can configure OCR and Voting Disk On ASM Instance
    -> Introduce the enhanched features of Oracle XML DB

    -- Rest Of The New Features Coming Soon -----
    posted by Jaswinder Singh @ 11:17 AM   0 comments
    RE-CREATE WORKFLOW QUEUE !!!!! (New Year Gift)
    Friday, January 1, 2010
    ===================================
    Wish Happy New Year To ALL Readers
    ===================================

    Check the following :

    SQL> Select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OBJECT_NAME='WF_DEFERRED';

    RE-CREATE WORKFLOW QUEUE
    ========================
    => Bring down the Workflow Services of DMYFAI instance.
    => Add the Subscribers to The WF_DEFERRED queue

    =>Logon to SQLPLUS as APPLSYS schema.

    declare
    lagent sys.aq$_agent;
    begin
    lagent := sys.aq$_agent('WF_DEFERRED',null,0);
    dbms_aqadm.remove_subscriber(queue_name=>'WF_DEFERRED', subscriber=>lagent);
    end;
    /
    commit;

    declare
    lagent sys.aq$_agent;
    begin
    lagent := sys.aq$_agent('WF_DEFERRED',null,0);
    dbms_aqadm.add_subscriber(queue_name =>'WF_DEFERRED',subscriber=>lagent,rule=>'1=1');
    end;
    /
    commit;

    => Bring up the Workflow Services of DMYFAI instance.



    => Take an invalid count
    => Add the missing subscriber/rule to the queue.

    - Run $FND_TOP/patch/115/sql/wfquec2.sql

    sqlplus apps/ @wfquec2.sql


    - For other missing subscriber/rule,

    run $FND_TOP/patch/115/sql/wfmqsubc.sql

    sqlplus apps/ @$FND_TOP/patch/115/sql/wfmqsubc.sql APPLSYS

    => Check for new invalids
    posted by Jaswinder Singh @ 1:24 AM   2 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 .