My favourite book for Oracle SOA governance 11g implementation

Hi,
   SOA is always my favourite technical area,got interest in reading and Understanging Oracle SOA Governance.I devoted my time in studying Oracle SOA Suite 11g implementation,this time again I was  lucky to get book from packt publication "Oracle SOA governance 11g implementation for Oracle SOA developers.

To buy this book follow the below link provided below:

http://www.packtpub.com/oracle-soa-governance-11g-implementation/book


Brief about Author: Luis is an expert in SOA and also is a thought leader in this field. Having always
had a natural talent for software, computers, and engineering in general, Luis's
career in software started from an early age.
Andrew first started working in the SOA space
10 years ago and has successfully delivered many Oracle Middleware projects for
large and blue chip clients. He currently works at CSC, where he is employed as a
BPM/SOA Solution Architect for the Oracle Global Practice

Brief about Book:
This books covers various the following topics which will help you work in Oracle SOA suite 11g:

1)SOA Governance, describes in detail the key concepts around SOA
Governance and the Oracle SOA Governance Solution infrastructure.
2)Implementation Case Study, highlights the typical problems and
requirements addressed by a typical SOA Governance implementation and
highlights the key critical success factors for the implementation.
3)Introduction to Oracle Enterprise Repository, describes the core concepts,
management consoles, and capabilities supported by OER.
4)Initial Configuration, describes the prerequisites and illustrates the steps to
be considered when configuring OER for the first time.
5)Harvesting, describes the prerequisites and steps for bootstrapping and
harvesting assets into OER.
6)Asset Lifecycle and Workflow, describes how to implement policies,
compliance templates, and workflows in OER in support of the different asset
lifecycle stages.
7)Oracle Service Registry

It is really nice book for SOA Develpers,really love to read this book lots of times to understand more about Oracle SOA Suite 11g implementation and handling in details from practical point

of view.


Happy Oracle SOA Implementaion works...


Best regards,

Rafi.

Advantage of AFPASSWD utility in Oracle EBS R 12.1.2

Advantage of AFPASSWD utility in Oracle EBS R 12.1.2

   I was just experimenting with some test cases and got chance to work with AFPASSWD,which is advantageous  in comparison with FNDCPASS.

Below are the advantages:

1)We can run this utility from DB tier and Apps tier.

2)We can make sure the password is hidden(invisible to users),while changing,which is very good security option.

Below are some of the syntax to use it:

AFPASSWD [-c APPSUSER[@TWO_TASK]] [-f FNDUSER]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-o DBUSER]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-a]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-l ORACLEUSER [TRUE] |
[FALSE]]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-L [TRUE] | [FALSE]]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-s] APPLSYS

Eg:When you login with 'applmgr' and after setting environment,we can run this utility as below:

AFPASSWD -c apps@TWO_TASK -s APPLSYS

Enjoy apps DBA tasks....


Thanks,

Rafi.

Oracle Apps DBA open position

Hi Friends,

Apps DBA position open in Bangalore location.Below are more details on this job

Job Description:

• Minimum of 4 years of experience as Oracle Applications 11i/R12 DBA & Oracle DBA, providing 24x7 support of both Production & Development on Various Platforms.
• Exposure to RAC, ASM, GRID/OEM, partitioning, Data Guard or replication a plus.
• Must have experience with Unix servers -AIX, HP-UX, SOLARIS, LINUX
• Demonstrated experience with high levels of accountability
• Demonstrated initiative, innovation, flexibility, and ability to manage workload; achieving desired results with minimal supervision
• Must be proficient in the English language and have strong communication skills.

Principle accountabilities include:

• Assist in tool evaluation, integration and support
• Database cloning and 11i/R12 Apps Cloning
• Assist in perform Oracle Apps Administration and Sysadmin tasks
• Assist in troubleshooting Forms server, Reports server, Apache server, Jserv, Jinitiator and Workflow.
• Assist in applying patches, upgrades, frameworks for 11i/R12 apps.
• Ensuring high availability, backup and recovery of production databases.
• Database planning, scaling and capacity planning.

Interested DBAs  can mail resume to rsahu@teksystems.com 


Best regards,

Rafi.

Tricky interview questions for Oracle DBAs and Apps DBAs

Hi  DBAs,

It is long time I didn't wrote and share my knowledge with you all,so thought of sharing some interesting interview question.I'm sure it will help everyone.


Q 1)How to get the list of patches applied to database if lsinventory is corrupted?
Ans:        we have folder under $ORACLE_HOME  called db_storage,we can do 'ls' to get the list of patches if our inventory is corrupted.(In case of windows you can just view by going to that path).
Note: we always have to backup the oracle home and inventory to be on safe side.

Below command will help you on it.

tar cvf – $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

I find the below link useful one:
http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/

Q 2)If we want CRS(Cluster Ready Service) services to not get detected,what we should do?
Ans:     $crsctl disable crs

Q 3)How to list the nodes in your cluster by running the following command on one node?
Ans:   We can list the nodes in our cluster by running the following command on one node:
          $ olsnodes

Q 4) How to schedule a existing concurrent request to run on specific day,all the times?
Ans :   Let us say i have a gather stats concurrent request for a schema and I will it to run every weekend once(i.e every sunday),I can do it by following simple steps:

Step 1:Open the Cocurrent request in form window by specifying the specific request id(eg 4126398)

Step 2:Click on Detail Tab of the Concurrent request,we will be seeing schedule tab,click on it

Step 3:Schedule it on Sunday and Choose On specific days on the left side of the form page as shown in below screenshot

Q 5)How to recover corrupt OCR without backup?
Ans:     We all know Oracle takes backups of OCR after every 4 hours,but what if we don't have backup  and OCR is corrupted,we cannot go for installing whole clusterware which could be a lengthy process.

If we are on 10g R2 and later version then this can be done without re-installing Clusterware ( if you have backup of root.sh or it’s not overwritten by any
subsequent patch ) I tested this on my test machines.

We can get Current Voting Disk location

[root@racnode1 ~]# crsctl query css votedisk
 0. 0 /OCFS/VOT

located 1 votedisk(s).

Current OCR Files location

[root@racnode1 ~]# ocrcheck

Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 4344
Available space (kbytes) : 257800
ID : 601339441

Device/File Name : /OCFS/OCR
Device/File integrity check succeeded

Device/File Name : /OCFS/OCR2
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Output of CRS_STAT

[root@racnode1 ~]# crs_stat -t

Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE racnode1
ora….C1.lsnr application ONLINE ONLINE racnode1
ora.racnode1.gsd   application ONLINE ONLINE racnode1
ora.racnode1.ons   application ONLINE ONLINE racnode1
ora.racnode1.vip   application ONLINE ONLINE racnode1
ora….SM2.asm application ONLINE ONLINE racnode2
ora….C2.lsnr application ONLINE ONLINE racnode2
ora.racnode2.gsd   application ONLINE ONLINE racnode2
ora.racnode2.ons   application ONLINE ONLINE racnode2
ora.racnode2.vip   application ONLINE ONLINE racnode2
ora.test.AP.cs application ONLINE ONLINE racnode1
ora….st1.srv application ONLINE ONLINE racnode1
ora.test.db    application ONLINE ONLINE racnode2
ora….t1.inst application ONLINE ONLINE racnode1
ora….t2.inst application ONLINE ONLINE racnode2

I stopped clusterware on both nodes and removed OCR & Voting Disks.

[root@racnode1 ~]# ls -lrt /OCFS/*

-rw-r–r– 1 root root 399507456 Jun 29 14:05 /OCFS/OCR2
-rw-r—– 1 root oinstall 10485760 Jun 29 14:05 /OCFS/OCR
-rw-r–r– 1 oracle oinstall 10240000 Jun 29 14:05 /OCFS/VOT

[root@racnode1 ~]# rm -fr /OCFS/*

Tried again to start Cluster

[root@racnode1 ~]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly



Clusterware could not startup.

[root@racnode1 ~]# crsctl check crs

Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

Thrown error in /tmp/crsct.* file about OCR

[root@racnode1 ~]# cat /tmp/crsc*

OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]

 Here, I lost all my OCR & Voting disk.

Below procedure can be used for recovery.

1) Execute rootdelete.sh script from All Nodes.
2) Execute rootdeinstall.sh from Primary Node.
3) Run root.sh from Primary node.
4) Run root.sh from all remaining nodes.
5) Execute remaining configurations (ONS,netca,register required resources)

1) Executing rootdelete.sh on all nodes, this script can be found under $ORA_CRS_HOME/install/

[root@racnode1 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

[root@racnode2 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

OCR initialization error can be safely ignored.

2) Execute rootdeinstall.sh on Primary Node, this script can also be found under $ORA_CRS_HOME/install

[root@racnode1 ~]# /u01/app/oracle/product/crs/install/rootdeinstall.sh
Removing contents from OCR mirror device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.031627 seconds, 332 MB/s
Removing contents from OCR device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.029947 seconds, 350 MB/s

3) Run root.sh on Primary node, this will create VOT & OCR files.

[root@racnode1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
“/OCFS/VOT” does not exist. Create it before proceeding.
Make sure that this file is shared across cluster nodes.
1

I had to touch this file to proceed

[root@racnode1 ~]# touch /OCFS/VOT
[root@racnode1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
assigning default hostname racnode1 for node 1.
assigning default hostname racnode2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: racnode1 racnode1-priv racnode1
node 2: racnode2 racnode2-priv racnode2
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Now formatting voting device: /OCFS/VOT
Format of 1 voting devices complete.
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
racnode1
CSS is inactive on these nodes.
racnode2
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.

4) Run root.sh from all remaining nodes.

[root@racnode2 crs]# ./root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
assigning default hostname racnode1 for node 1.
assigning default hostname racnode2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: racnode1 racnode1-priv racnode1
node 2: racnode2 racnode2-priv racnode2
clscfg: Arguments check out successfully.

NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
racnode1
racnode2
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps

Creating VIP application resource on (2) nodes…
Creating GSD application resource on (2) nodes…
Creating ONS application resource on (2) nodes…
Starting VIP application resource on (2) nodes…
Starting GSD application resource on (2) nodes…
Starting ONS application resource on (2) nodes…

Done.

Clusterware is up and running

[root@racnode2 crs]# crs_stat -t

Name Type Target State Host
————————————————————
ora.racnode1.gsd application ONLINE ONLINE racnode1
ora.racnode1.ons application ONLINE ONLINE racnode1
ora.racnode1.vip application ONLINE ONLINE racnode2
ora.racnode2.gsd application ONLINE ONLINE racnode2
ora.racnode2.ons application ONLINE ONLINE racnode2
ora.racnode2.vip application ONLINE ONLINE racnode2

5) Remaining Configuration

       a) Configuring Server side ONS

[root@racnode1 crs]# $ORA_CRS_HOME/bin/racgons add_config racnode1:6200 racnode2:6200

         b) Listener Configuration usint netca

we might want to remove listener.ora from both nodes as entries may exist already. Take backup or orignial listener.ora  and use netca to configure &
register with OCR. Till 10g, we can not register listener using srvctl

Renaming orginal listener.ora

[oracle@racnode1 ~]$ mv $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.orig
[oracle@racnode1 ~]$ ssh racnode2 mv $ORACLE_HOME/network/admin/lstener.ora $ORACLE_HOME/network/admin/listener.ora.orig

          c)Adding ASM, Instance, Database

[oracle@racnode1 ~]$ srvctl add asm -i +ASM1 -n racnode1 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add asm -i +ASM2 -n racnode2 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add database -d test -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add instance -d test -i test1 -n racnode1
[oracle@racnode1 ~]$ srvctl add instance -d test -i test2 -n racnode2

I restarted both nodes, got everything back. Yes, Services can be re-created.

[oracle@racnode1 ~]$ crs_stat -t

Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE racnode1
ora….C1.lsnr application ONLINE ONLINE racnode1
ora.racnode1.gsd   application ONLINE ONLINE racnode1
ora.racnode1.ons   application ONLINE ONLINE racnode1
ora.racnode1.vip   application ONLINE ONLINE racnode1
ora….SM2.asm application ONLINE ONLINE racnode2
ora….C2.lsnr application ONLINE ONLINE racnode2
ora.racnode2.gsd   application ONLINE ONLINE racnode2
ora.racnode2.ons   application ONLINE ONLINE racnode2
ora.racnode2.vip   application ONLINE ONLINE racnode2
ora.test.db    application ONLINE ONLINE racnode2
ora….t1.inst application ONLINE ONLINE racnode1
ora….t2.inst application ONLINE ONLINE racnode2

Note : That’s why it’s recommended to take backup of root.sh after fresh install as subsequent patches can
overwrite root.sh script.

This is described on Metalink Note: 399482.1

Q 6)If we don't specify any location while adding ASM based datafile where it will get created?
Ans   It will get created in $ORACLE_HOME/dbs path  in Unix environment or $ORACLE_HOME/database in Windows environment

Q 7)If we missed the '+' sign while adding datafile to ASM what is the effect?
Ans  The effect is that it creates Non-ASM based datafile in $ORACLE_HOME/dbs and database will be affected badly,in this case we need to use RMAN to convert from Non-ASM to ASM,so never try or do this any time.this is just for information purpose.

Q 8)Name 2 scenarios where your skill were challeged as DBA?
Ans   This question is often repeated in the interview,so please prepare and plan to answer this question.Remember we need to answer 2 situations where we are tested as DBA,so if you have done some kind of restore and recovery in past for database it will ideal to express that experience,if you have done Oracle RAC installation and faced the issues make points and ellaborate it or if you have done any kind of Disaster recovery setup it will be good or If you have done upgrade from Oracle 11i to R12 and you remember the situation which made you struggle along with resolution it would be one more great choice to influence the interview.

Q 9)How many recoveries of Database have you done in your Career?
Ans  This question is again to test how you react when you encounter a issue with your Database,so better to explain a scenario where you have actually involved in doing restore or recovery.

Eg: We lost one critical table of our production environment,so the steps involve while doing RMAN restore until time or SCN will be useful in this case,again make sure you provide the valide reason why you had choosen specific method for doing restore or recovery of table.So my sincere suggestion is to plan the steps for this question along with reasons,to answer point to point and help interview to pick you for that position.


Enjoy Challeges working as DBA and Apps DBA.

Best regards,

Rafi.

Applying patch to Oracle EBS 11i/R12 Applications

Applying patch to  Oracle EBS 11i/R12 Applications:
Below are the steps which I follow to apply patch to Oracle EBS 11i/R12,in the below scenario I'm using Oracle EBS 11i applications,but the same steps can be use for Oracle EBS R12 applications also

Step 1: Before applying patch:(check invalid objects in DB):

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                     1
TEST_USER1                     2
TEST_USER2                     5
APPS                                  23

Step 2:Enable Maintainance mode

 ebstest:applmgr::/home/applmgr>echo $TWO_TASK
TESTDB
Use 'adadmin' to enable maintainance mode before patching.

 5.    Change Maintenance Mode

Change Maintenance Mode
   ----------------------------------------

Maintenance Mode is currently: [Enabled].

Backing up restart files, if any......Done.

             Change Maintenance Mode
   ----------------------------------------

Maintenance Mode is currently: [Enabled].


Step 3:Applying patch using 'adpatch'(auto patch utility):
=====================================================
 Use: adpatch by going to patch directory

Go to the patch top directory,where all driver and required ldt files are present with the application
filesystem owner,makes sure all files have read,write and execute permission.
As described below:

Note:It is very important to review the readme.txt in unix vi editor before applying applying patch and follow the instruction as given in the readme.txt file and apply any pre-requistics patch if required.autoconfig run also not required unless it is specified after patch installation in readme.txt file.


ebstest:applmgr::/stage/patches/5522470>ls -altr
total 12432
-rwxrwxrwx    1 applmgr  dba            2931 Oct 29 2007  u5522470.drv
-rwxrwxrwx    1 applmgr  dba            1173 Oct 29 2007  f5522470.ldt
-rwxrwxrwx    1 applmgr  dba         6323692 Oct 29 2007  b5522470.ldt
-rwxrwxrwx    1 applmgr  dba              37 Oct 29 2007  marker1.txt
-rwxrwxrwx    1 applmgr  dba            2852 Oct 29 2007  README.txt
-rwxrwxrwx    1 applmgr  dba            5502 Oct 29 2007  README.html
drwxrwxrwx    5 applmgr  dba             256 Feb 21 01:21 po
drwxrwxrwx   54 root     dba           12288 Feb 21 01:21 ..
drwxr-xr-x    3 applmgr  dba             256 Feb 21 01:21 .
ebstest:applmgr::/stage/patches/5522470>adpatch

Review the README for pre-requisite information.

Your default directory is '/d21/TESTDB/apps/appl'.
Is this the correct APPL_TOP [Yes] ?

Verify the log file after applying patch

Step 4: Disable Maintainance mode again by using 'adadmin' utility:
=======================================================

Backing up restart files, if any......Done.

             Change Maintenance Mode
   ----------------------------------------

Maintenance Mode is currently: [Disabled].


Step 5: Bounce application after applying patch :
===================================================

Step a:Stop listener using adalnctl.sh

ebstest:applmgr::/u01/TESTDB/apps/comn/admin/scripts/TESTDB_ebstest>./adalnctl.sh stop

adalnctl.sh version 115.17

Shutting down listener process APPS_TESTDB.

adalnctl.sh: exiting with status 0

Step b:Stop applications using adstpall.sh

ebstest:applmgr::/u01/TESTDB/apps/comn/admin/scripts/TESTDB_ebstest>./adstpall.sh

You are running adstpall.sh version 115.22


Enter the APPS username: apps

Step c:Start listener using adalnctl.sh

ebstest:applmgr::/u01/TESTDB/apps/comn/admin/scripts/TESTDB_ebstest>./adalnctl.sh start

adalnctl.sh version 115.17

Starting up listener process APPS_TESTDB.

adalnctl.sh: exiting with status 0

Step d:Start applications using adstrtal.sh

ebstest:applmgr::/u01/TESTDB/apps/comn/admin/scripts/TESTDB_ebstest>./adstrtal.sh

You are running adstrtal.sh version 115.22

Step 6:After applying PATCH:
========================

Step a:Verify the patch is applied successfully:

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select bug_number,creation_date from ad_bugs where bug_number='5522470';

BUG_NUMBER                     CREATION_DATE
------------------------------ ---------------
5549427                        12-FEB-13

Step b:Run cmclean.sql
Run cmclean.sql from application node by going to $COMMON_TOP/admin/scipts/TESTDB_ebstest in 11i,where as in R12 goto $ADMIN_SCRIPTS_HOME or $INST_TOP/admin/scripts
commit;
Note:We run cmclean after clonning also to make sure the node name is updated in the FND_NODES Table correctly

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                     1
TEST_USER1                    2
TEST_USER2                    5
APPS                                 22

Step c:Check the file versions got changed successfully after applying patch:

strings -a POXWARMB.pls|grep Header =>Can be used to check the file version.

ebstest:applmgr::/d21/TESTDB/apps/appl/po/11.5.0/sql>strings -a POXWARMB.pls|grep Header
 strings -a POXWARMB.pls|grep Header
/* $Header: POXWARMB.pls 115.27.11510.7 2009/02/11 11:08:59 krsethur ship $*/
/* $Header: POXWARMB.pls 115.27.11510.7 2009/02/11 11:08:59 krsethur ship $*/

Step d:Do the Health Check of Oracle EBS Application

Login TESTDB application by using Home page URL

Note :We can get HOME page URL by using below query:
SQL>Select Home_URL from icx_parameter;

 http://ebstest.abccomp.com:8035    with sysadmin/welcome123

Health check completed successfully by submitting active user Concurrent request REQUEST ID 28758820.

Step e:Intimate end User 

Make sure you intimate the end User or release your application to the end User

Enjoy Apps DBA tasks...


Best regards,

Rafi

WARNING: inbound connection timed out ORA-3136

Hi,

Recently I was troubleshootiong for this error,finally resolved.First,let me explain what exactly it mean, the cause of this error,possible method of diagnosis and finally the resolution.

INBOUND_CONNECT_TIMEOUT:
 The INBOUND_CONNECT_TIMEOUT is the parameter used to specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.
i.e The default timeout for a new connection to be established. This setting is called SQLNET.INBOUND_CONNECT_TIMEOUT.

 If the listener does not receive the client request in the time specified, then it terminates the connection.Check the listener.log for details.

In Oracle 9i,the value is set to unlimited and the new value in 10g is 60 seconds.In Oracle 11g also the default value is 60 seconds.

Causes:

1) Some Malicious client connections. 
2)  Connection takes a long time.
3)Database is heavily loaded and cannot process request in allotted time.

Diagnosis:

1)Check the alert log file and check from where the connection comes.
2)Check the listener is up & running.
3)Ping the server,make sure tnsping is working.

Resolution:

To identify the listener name and ORACLE_HOME,we can use the below command.

$ ps -eaf|grep tns
oracle    1643     1  0  2012 ?        04:51:49 /data01/home/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
oracle   31682 16935  0 04:54 pts/0    00:00:00 grep tns


Check if the sqlnet.ora file is existing in $ORACLE_HOME/network/admin path,if not create one.

Adjusted the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora and reloaded the the listener configuration:

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=60
listener.ora: INBOUND_CONNECT_TIMEOUT_LISTENER=60
where:Listener_name=>LISTENER
Default value for these is 60 seconds.

Below are the details:

LSNRCTL>set  INBOUND_CONNECT_TIMEOUT_LISTENER=60
[oracle@hostname admin]$ cat sqlnet.ora
#SQLNET.ORA Network Configuration File: /u01/home/oracle/product/10.2.0/network/admin/sqlnet.ora
#To eliminate inbound connection timeout
SQLNET.INBOUND_CONNECT_TIMEOUT=60

#sqlnet.authentication_services = (NONE)

#SQLNET.EXPIRE_TIME = 0

#SQLNET.ENCRYPTION_SERVER = requested

#SQLNET.ENCRYPTION_CLIENT = requested

NAMES.DIRECTORY_PATH= (TNSNAMES,hostname)
[oracle@s259722ch3el19 admin]$

[oracle@s259722ch3el19 admin]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JAN-2013 05:01:25

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.1.511)(PORT=1521)))
The command completed successfully

Keep  monitoring - alerts have stopped for now.

If the alerts continous to come,than you have to increase the value of  INBOUND_CONNECT_TIMEOUT as follows:

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=300
listener.ora: INBOUND_CONNECT_TIMEOUT_LISTENER=300
 where:Listener_name=>LISTENER

 Hope it help.

Happy DBA tasks and troubleshooting.


Best regards,

Rafi.

Restoring a restore point in Oracle EBS Database

Restoring a restore point in Oracle EBS Database

Restoring a restore point in  Oracle EBS Database configured with PCP running on two nodes Apps Tier and two nodes DB Tier.


At a high level the steps to flashback an Oracle EBS Database are as follows:

1)Shutdown applications
2)Shutdown database (all nodes)
3)Startup mount one instance
4)Execute flashback command
5)alter database open resetlogs;
6)Startup all database nodes (typically I use srvctl start database –d and then check CRS to make sure everything is up).
7)Restart Applications

Additionally ,when starting the CM’s we can run cmclean first,
then start the CM’s, and then execute the CM purge job with a retention of 1 day or
something to attempt to clear out log files.

This is a “nice to have” as we’ve seen sometimes after a flashback the log files don’t match the process that was executed in CM.

Finally,do the Health check up by submitting the concurrent active user request.


TESTDB:
=======

srvctl disable disables target, meaning CRS will not consider it for automatic startup, failover,
or restart. This option is useful to ensure an object that is down for maintenance is not
accidentally automatically restarted.


Syntax:

srvctl disable database -d db_unique_name

srvctl disable database -d TESTDB

$srvctl status database –d TESTDB


>srvctl status database -d TESTDB
Instance TESTDB1 is running on node node1
Instance TESTDB2 is running on node node2


Step 1:Shut both the instances in the database Database normally
 
Node 1:
============

$srvctl stop database –d TESTDB


>srvctl status database -d TESTDB
Instance TESTDB1 is not running on node node1
Instance TESTDB2 is not running on node node2

(OR)

SQL>shut immediate


SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TESTDB

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Node 2:
==========

SQL>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2:Startup mount and Roll Database back to restore point

SQL>startup mount


SQL> startup mount
ORACLE instance started.

Total System Global Area 1.3434E+10 bytes
Fixed Size                  2160552 bytes
Variable Size            4697622616 bytes
Database Buffers         8589934592 bytes
Redo Buffers              143785984 bytes
Database mounted.


SQLPLUS>FLASHBACK DATABASE TO RESTORE POINT 'someLOGICALNAME';

SQLPLUS>FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE_11DEC12;                                                                                                         ';

FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE;

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
1.2621E+13                     6 YES   3.6739E+10
19-JUL-12 02.27.07.000000000 PM
                                                                            YES
BASELINE_RESTORE


SQL> SHUT IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1.3434E+10 bytes
Fixed Size                  2160552 bytes
Variable Size            4697622616 bytes
Database Buffers         8589934592 bytes
Redo Buffers              143785984 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE;
FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE


Step 5:Monitor progress of a flashback operation:

SQL> select opname,to_char(start_time,'MM-DD-YYYY HH24:MI:SS'),time_remaining,elapsed_seconds from v$session_longops;

OPNAME
----------------------------------------------------------------
TO_CHAR(START_TIME, TIME_REMAINING ELAPSED_SECONDS
------------------- -------------- ---------------
Flashback Database
12-14-2012 01:22:51           1395             744


Step 6 a:Open the Database with resetlogs option

SQL>alter database open resetlogs;


Step 6 b:Start and check both the Database instances are up & running.



$srvctl start database -d TESTDB

$srvctl enable database -d TESTDB


$srvctl status database –d TESTDB


>srvctl status database -d TESTDB
Instance TESTDB1 is running on node node1
Instance TESTDB2 is running on node node2


Step 6 c :Start all the Applications and Do Health Check of the Application by submitting active user request

Start the Application from the Primary node in PCP setup,first start listeners on both the nodes ,than start the concurrent managers and than finally start the Applications on both the node.







Step 7:Informed the team flashback is completed to restore point.



Hope it helps.

Happy Apps DBA learning.

 Best regards,

Rafi.