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 1 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