Is user equivelance really required at the OS level after RAC installation?

Posted on December 18, 2008. Filed under: Database, DBA/Admin Tips, Operating System, RAC, TroubleShooting, Uncategorized | Tags: , , , |

A wild though came to my head late this evening about the user equivelance in RAC. What will happen if i remove the user equivelance for the oracle home owner between the RAC nodes. What do you think – will this mess up the normal operation of the database ???

BTW, User Equivelance = Ability to ssh between rac nodes without password.


DB Name : opitstop
DB Owner : orapstop


1. srvctl stop database -d OPITSTOP
2. cd $HOME/.ssh && mv authorized_keys authorized_keys_old [ authorized_keys file holds the rsa/dsa keys, which enables ssh to other boxes without password]

At this stage, opitstop DB is down and RAC_NODE_2 cannot ssh to RAC_NODE_1 since RAC_NODE_1 authorized_keys file had been moved.


cd $HOME/.ssh && mv authorized_keys authorized_keys_old

Now both the nodes cannot do ssh between them without password.


Lets start the database, using srvctl to see if it errors out

srvctl start database -d OPITSTOP

NOPE, it doesnt … it works as before, started the instances on both RAC_NODE_1 and RAC_NODE_2.

At this stage in the alert log file, the database was able to get information about the other node and a select on gv$instance shows both instances too. I let the db to run for sometime … still there was no error.

Now lets revert back the user equivelance on both nodes.

orapstop@RAC_NODE_1: cd $HOME/.ssh && mv authorized_keys_old authorized_keys
orapstop@RAC_NODE_2: cd $HOME/.ssh && mv authorized_keys_old authorized_keys

So based on this excercise my conclusion is, user equivelance is required only for RAC installation’s / RAC binary upgrades viz., to or 4 / patching using opatch option napply and not for normal operations of the database.

Oh yeah, its a good feeling to mess up with the setup and to get it back working.

Happy Messing !!!


Read Full Post | Make a Comment ( None so far )

dbca – Creating RAC Database using dbca in silent mode

Posted on December 16, 2008. Filed under: Database, DBA/Admin Tips, Linux, RAC, TroubleShooting | Tags: , , |

During the course of the DBA life, we all know how irritating it is to create a DB or to install a Oracle Software on a UNIX Flavour box from a remote machine using a X РServer. Each click would take minutes to react (My favourite is the tab button on the keyboard) but still at-times we have to make use of the mouse. Anyways, recently we installed 10203 on a HP server which already has cluster setup. Everything went good in terms of 10201 installation, 10203 upgrade (both binaries) and even dbca 14 screens. But nothing happened when clicked on the finish button in dbca (14th screen). No useful information in the log files. I pressed cancel and re-launched it again and did the dbca exercise all the way till the end, still no luck. By this time my patience level was minus 25.  It is at this stage i thought how about creating the database in silent mode and you know what, creating the DB in silent mode is 200 times faster than the GUI and the db creation completed in less than 5 minutes.

Syntax to create a General Purpose RAC Database on a Cluster File System.

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName opitstop -sid opitstop -sysPassword opitstop123 -systemPassword opitstop123 -emConfiguration NONE -datafileDestination /opt/oracle/oradata/opitstop -storageType CFS -nodelist RAC_NODE_1,RAC_NODE_2 -characterSet WE8IS08859P15 -listeners opitstop -memoryPercentage 30 -continueOnNonFatalErrors true

Place above command in a .sh file (shell script) say createRACDB.sh and just run it. Before running the script make sure the cluster services (crs) are running and also you have user equivelance between the rac nodes for the oracle owner ie., eg. if the oracle owner username is oraracdb in RAC_NODE_1 and RAC_NODE_2 then when you do a ssh from RAC_NODE_1 to RAC_NODE_2 it should go without asking for the password.

There are more options available with dbca. Some of them are -createDatabase, -createTemplateFromDB, -createCloneTemplate, -generateScripts, -deleteDatabase, -configureASM, -deleteInstance. For a complete list of the usage execute dbca -help.

DBCA Log Location: $ORACLE_HOME/cfgtoollogs/dbca/[SID]

Happy DB creation(s) !!!


Read Full Post | Make a Comment ( 3 so far )

SQL Query to view sql of a session

Posted on August 25, 2008. Filed under: DBA/Admin Tips, Diagnostics, R12, RAC, Scripts, TroubleShooting | Tags: , , , , |

v$session S,
v$sqltext_with_newlines T
S.sid = &sid
AND S.serial# = &serial
AND S.sql_address = T.address
AND S.sql_hash_value = T.hash_value
ORDER BY T.piece

Replace v$session with gv$session if the database is running on RAC configuration.


Read Full Post | Make a Comment ( 1 so far )

SQL Query to list running concurrent requests

Posted on August 25, 2008. Filed under: DBA/Admin Tips, R12, RAC, Scripts, TroubleShooting | Tags: , , , |

col user_name format a20 word_wrapped
col ProgName format a25 word_wrapped
col requestId format 99999999
col StartDate format a20 word_Wrapped
col OS_PROCESS_ID format a6
col ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
set lines 200

oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,’DD-MON-YYYY HH24:MI:SS’) StartDate,
request_id RequestId,
(sysdate – actual_start_date)*24*60*60 ETime
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = ‘US’
and fcr.phase_code = ‘R’
and fcr.status_code = ‘R’
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC

Replace v$session with gv$session if the database is RAC.

Click here to extract the sql being executed by a concurrent program


Read Full Post | Make a Comment ( 2 so far )

Shell Script to verify and start database, listener and wallet

Posted on July 11, 2008. Filed under: DBA/Admin Tips, RAC, Scripts, TDE | Tags: , |

If Wallet is being used on your RAC or Single Node environment, opening the wallet after starting the database is very critical, failing which users trying to access encrypted data will get ORA-28365: wallet is not open error.

Things to remember while maintaining a wallet on a RAC and Single Node environment

1. The wallet password is case sensitive

2. Wallet should be opened on both the instances. Click here for more information on this.

We had the situation of not opening the wallet after starting the database which resulted in lot of access issues. To overcome this i wrote this script to verify and start the listeners, databases (Single Node and RAC) and Wallet.

Heres what the script does:

1. If opted for RAC option, the script will gather the rac information (nodes), cluster status – if down exit, listener status – if down, start, database/instance status – if down start and finaly wallet – if not open, open it.

2. If option for Single Node option, the script will perform all the steps as mentioned in Option 1 except for nodes and cluster.

You can also use this script to open just the wallets.

Heres a sample of the help message:

Usage : startdbwall.sh [-h|-t|-o] [R|S|W|RW] [-p] <walletpwd>

Example :
1. To Display this help message
startdbwall.sh -h

2. To Start RAC DB and to Open Wallet
startdbwall.sh -t R

3. To Start Single Node DB and to Open Wallet
startdbwall.sh -t S

4. To Open just the Wallet in single DB mode
startdbwall.sh -o W

5. To Open just the Wallet in RAC DB mode
startdbwall.sh -o RW

6. To Open just the Wallet with password (CASE SENSITIVE)
startdbwall.sh -o W -p <walletpwd>

Download script here. startdbwallsh


Read Full Post | Make a Comment ( 1 so far )

RAC and Wallet

Posted on July 10, 2008. Filed under: Database, DBA/Admin Tips, RAC, TDE | Tags: , |

Is it ok to open the wallet on only one instance in a 2 Node or multi node RAC Architecture?

The answer is NO. You can open the wallet only on one instance(RAC1), but any user who gets connected to the other instance (RAC2), will get the error message ORA-28365: wallet is not open, if they try to access encrypted columns.

As of now, opening and closing of the database wallet is instance specific, even if you place the wallet file in a common location which is accessible to both the instances.

So if you had implemented wallet on a RAC setup, make sure you open the wallet on all individual instances.

You can also use this link to download a script which does this automatically for you.


Read Full Post | Make a Comment ( 1 so far )

How to make changes to spfile / pfile or generate spfile / pfile on a RAC database

Posted on June 30, 2008. Filed under: Configuration, Database, DBA/Admin Tips, RAC | Tags: , , , |

Not all init.ora parameters can be changed using ‘alter system set’ commands. For example audit_syslog_level parameter

SQL> alter system set audit_syslog_level=local0.warning scope=both;
alter system set audit_syslog_level=local0.warning scope=both
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

In this scenario you need to bounce the database after making the changes in init.ora file.

Here are the detailed steps to generate pfile from spfile and vice-versa.

1. Connect to a database instance (any instance of the rac database) as sysdba user
2. Generate pfile from spfile

sql> create pfile=’/tmp/initSID1.ora’ from spfile;

—Make sure you mention a location for the pfile else it will overwrite the initSID1.ora file under $ORACLE_HOME/dbs

3. Make necessary changes eg.


4. Shutdown instance 1
sql> shutdown immediate

5. Shutdown instance 2
sql> shutdown immediate

You can also use srvctl command to stop the database by issuing

srvctl stop database -d SID

6. Backup existing spfile.

In RAC architecture the spfile which is common to both the instances will be usually in a location (viz., nfs or nas filesystem ) which is accessible to both the instances. Its a good practice to maintain single copy of spfile for instances that are part of RAC database, instead of having individual copies of spfile.ora under $ORACLE_HOME/dbs.

When the spfile is in a common location for the instances, the spfileSID[instance_num].ora eg: spfileSID1.ora under $ORACLE_HOME/dbs will be a soft link to the common location where the file name would be spfileSID.ora

cd /common_location
mv spfileSID.ora spfileSID.ora.lastbutone

7. Startup the database on instance 1 with the new pfile
8. sqlplus ‘/ as sysdba’
sql> startup pfile=’/tmp/initSID1.ora’

9. After the instance comes up, generate spfile from this pfile.
sql> create spfile=’location of existing spfile.ora with filename’ from pfile=’/tmp/initSID1.ora’;

eg: create spfile=’/common_location/spfileSID.ora’ from pfile=’/tmp/initSID1.ora’;

10. Verify that spfile is created on the mentioned location.
11. After confirmation, shutdown the instance
sql> shutdown immediate

12. sqlplus ‘/ as sysdba’ on instance 1
sql> startup [ this startup will start the instance using the spfile]

13. You can also issue show parameter spfile to verify that the instance had been started with the spfile.
14. After instance 1 comes up, start instance 2.


Read Full Post | Make a Comment ( None so far )

srvctl command line options

Posted on May 22, 2008. Filed under: RAC | Tags: , , |

Thought this might be helpful.

Usage: srvctl [-V]
Usage: srvctl add database -d -o [-m ] [-p ] [-A /netmask] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s ] [-n ] [-y {AUTOMATIC | MANUAL}]
Usage: srvctl add instance -d -i -n
Usage: srvctl add service -d -s -r “” [-a “”] [-P ]
Usage: srvctl add service -d -s -u {-r “” | -a “”}
Usage: srvctl add nodeapps -n -o -A /netmask[/if1[|if2|…]]
Usage: srvctl add asm -n -i -o [-p ]
Usage: srvctl config database
Usage: srvctl config database -d [-a] [-t]
Usage: srvctl config service -d [-s ] [-a] [-S ]
Usage: srvctl config nodeapps -n [-a] [-g] [-o] [-s] [-l]
Usage: srvctl config asm -n
Usage: srvctl config listener -n
Usage: srvctl disable database -d
Usage: srvctl disable instance -d -i “”
Usage: srvctl disable service -d -s “” [-i ]
Usage: srvctl disable asm -n [-i ]
Usage: srvctl enable database -d
Usage: srvctl enable instance -d -i “”
Usage: srvctl enable service -d -s “” [-i ]
Usage: srvctl enable asm -n [-i ]
Usage: srvctl getenv database -d [-t “”]
Usage: srvctl getenv instance -d -i [-t “”]
Usage: srvctl getenv service -d -s [-t “”]
Usage: srvctl getenv nodeapps -n [-t “”]
Usage: srvctl modify database -d [-n <db_name] [-o ] [-m ] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s ] [-y {AUTOMATIC | MANUAL}]
Usage: srvctl modify instance -d -i -n
Usage: srvctl modify instance -d -i {-s | -r}
Usage: srvctl modify service -d -s -i -t [-f]
Usage: srvctl modify service -d -s -i -r [-f]
Usage: srvctl modify service -d -s -n -i [-a ] [-f]
Usage: srvctl modify asm -n -i [-o ] [-p ]
Usage: srvctl relocate service -d -s -i -t [-f]
Usage: srvctl remove database -d [-f]
Usage: srvctl remove instance -d -i [-f]
Usage: srvctl remove service -d -s [-i ] [-f]
Usage: srvctl remove nodeapps -n “” [-f]
Usage: srvctl remove asm -n [-i ] [-f]
Usage: srvctl setenv database -d {-t =[,=,…] | -T =}
Usage: srvctl setenv instance -d [-i ] {-t “=[,=,…]” | -T “=”}
Usage: srvctl setenv service -d [-s ] {-t “=[,=,…]” | -T “=”}
Usage: srvctl setenv nodeapps -n {-t “=[,=,…]” | -T “=”}
Usage: srvctl start database -d [-o ] [-c | -q]
Usage: srvctl start instance -d -i “” [-o ] [-c | -q]
Usage: srvctl start service -d [-s “” [-i ]] [-o ] [-c | -q]
Usage: srvctl start nodeapps -n
Usage: srvctl start asm -n [-i ] [-o ] [-c | -q]
Usage: srvctl start listener -n [-l ]
Usage: srvctl status database -d [-f] [-v] [-S ]
Usage: srvctl status instance -d -i “” [-f] [-v] [-S ]
Usage: srvctl status service -d [-s “”] [-f] [-v] [-S ]
Usage: srvctl status nodeapps -n
Usage: srvctl status asm -n
Usage: srvctl stop database -d [-o ] [-c | -q]
Usage: srvctl stop instance -d -i “” [-o ] [-c | -q]
Usage: srvctl stop service -d [-s “” [-i ]] [-c | -q] [-f]
Usage: srvctl stop nodeapps -n
Usage: srvctl stop asm -n [-i ] [-o ] [-c | -q]
Usage: srvctl stop listener -n [-l ]
Usage: srvctl unsetenv database -d -t “”
Usage: srvctl unsetenv instance -d [-i ] -t “”
Usage: srvctl unsetenv service -d [-s ] -t “”
Usage: srvctl unsetenv nodeapps -n -t “”


Read Full Post | Make a Comment ( None so far )

How to identify to which mid-tier you are/will be connected to on a multi-node R12 environment?

Posted on May 16, 2008. Filed under: Apps Technology Stack, R12, RAC | Tags: , , , |

Imagine you have a multi-node Oracle Apps R12 setup with 2 node RAC. How would you identify to which server you are/will be connected to when you issue the url. Obviously when you have multi-node setup, you will be accessing the login page using a virtual URL.

1. Open Internet Explorer or Firefox
2. Type in the url eg: https://my.apps.com
3. This should bring the login page (AppsLogin)
4. Click on “About this page” — bottom left
5. Click on “Page Context” (3rd tab)

Here you will be able to see the following details:

1. Database HostName
2. DBC file being accessed
3. JDBC Port
4. SID being used to connect to the database

The DBC file being accessed proivdes the expanded full path of the dbc file (ie., $FND_SECURE or $INST_TOP/appl/fnd/12.0.0/.dbc). The expanded path will have the hostname. That is the mid-tier you are connected to.

You can also click on “Technology Components” & “Java System Properties” to gather more information.


Read Full Post | Make a Comment ( None so far )

CLUVFY utility will fail if ssh banner is enabled

Posted on April 30, 2008. Filed under: RAC | Tags: , , , , , |

The cluster verification utility will fail in User Equivalence check between RAC nodes, if ssh banner is enabled on the boxes. According to Oracle (Note ID: 4692561.8 – Bug ID: 4692561) this issue is fixed in and

Whereas in our case, we did download the latest cluvfy utility (11g) but we still faced this issue.

11g cluvfy utility can be run against 10g databases, by adding the option -r 10gR1 or 10gR2.

Sample cluster pre-install verification command

runcluvfy.sh stage -pre crsinst -n node1,node2 -r 10gR2 -verbose
— No space between node1, and node2 (node1,node2 holds good – node1, node2 doesnt)

Before running runcluvfy.sh make sure you had set CV_HOME and CV_JDKHOME.


Disable the ssh login banner and re-run the cluvfy utility.


Read Full Post | Make a Comment ( None so far )

Liked it here?
Why not try sites on the blogroll...