Database

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.

Assumptions

RAC Servers: RAC_NODE_1, RAC_NODE_2
DB Name : opitstop
DB Owner : orapstop

orapstop@RAC_NODE_1

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.

orapstop@RAC_NODE_2

cd $HOME/.ssh && mv authorized_keys authorized_keys_old

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

orapstop@RAC_NODE_1

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., 10.2.0.1 to 10.2.0.3 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 !!!

cheers,
OraclePitStop

Advertisements
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) !!!

cheers,
OraclePitStop.

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

R12 on 11g Anyone yet?

Posted on November 8, 2008. Filed under: Database, R12, Upgrade | Tags: , , , |

All,

We did a fresh R12.0.4 installation and applied Rup5 on Fin and Hrms. Now, we are upgrading this db to 11g. Later, we have plans to put the datafiles to ASM and also RAC the instance. So, we sure will have many posts on this. (Already i have few related to 11g crs and asm but planning to arrange the posts in some order)

OK. Now for this !!

Has anyone upgraded a fresh install R12.0.4 database from 10.2.0.3 to 11.1.0.6 ?
We are performing this upgrade on solaris 64 bit using DBUA.

DBUA throws the following error performing Post Upgrade:
+++++++++++++++++
ORA-00904: “E”.”OBJNUM”: Invalid identifier
ORA-06512: at line 31
+++++++++++++++++

I checked the dbua log files and the error is because there is no column called ‘objnum’ in the sys.enabled$indexes table which is aliased as “E”. Looks like a bug. I have raised an SR on this. Lets see what they say !!.

I looked up in metalink and google. No help !!  Looks like we are the first to perform this upgrade?

Thanks,

OraclePitStop team

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

OPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred

Posted on November 4, 2008. Filed under: Apps Technology Stack, Database, DBA/Admin Tips, opatch, Patch, R12, TroubleShooting | Tags: , , |

While trying to apply a patch or listing inventory, using opatch utility (be it on the Database node or for the Applications Tech Stack) below error is encountered:

—————————————————————————
LsInventory: OPatch Exception while accessing O2O

OPATCH_JAVA_ERROR  : An exception of type “OPatchException” has occurred:
OPatch Exception:
OUI found no such ORACLE_HOME set in the environment
Can not get details for given Oracle Home
An exception occurs
null

There is no Interim Patch

OPATCH_JAVA_ERROR  : An exception of type “OPatchException” has occurred:
Can not get a list of inventory on this home.

ERROR: OPatch failed because of Inventory problem.
—————————————————————————

Scenario

Imagine you are trying to apply a patch to 10.1.3 Tech Stack Oracle Home on R12.

What did i do wrong ?

1. Did i source the corrent environment file ?

Yes, i did, my echo $ORACLE_HOME command points to 10.1.3 home instead of the default 10.1.2 home. hmmm ok

2. Am i using the correct opatch utility?

yes, which opatch is pointing to $ORACLE_HOME(10.1.3)/Opatch/opatch

3. Is the inventory location accessible ?

Yes, oraInst.loc under Oracle Home (10.1.3) is pointing to a valid directory.

$ cat oraInst.loc
inventory_loc=/var/opt/oracle/oraInventory

4. Does the inventory has information about the Oracle Home you are trying to patch?

How do i check that ….

$ cd /var/opt/oracle/oraInventory

$ ls -latr
total 112
drwxrwxr-x   3 username    dba  ..
drwxrwxr-x   2 username    dba  oui
-rwxrwxr-x   1 username    dba  sessionContext.ser
drwxrwxr-x   2 username    dba  ContentsXML
-rwxrwxr-x   1 username    dba  oraInstaller.properties
drwxrwxr-x   2 username    dba  Contents
drwxrwx—   3 username    dba  logs
-rwxrwxr-x   1 username    dba  install.platform
drwxrwxr-x   6 username    dba  .

$ cd ContentsXML
$ vi inventory.xml

<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>10.1.0.5.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”SID_TOOLS__masked_oracle_home_tech_st_10_1_2″ LOC=”[masked_oracle_home]/tech_st/10.1.2″ TYPE=”O” IDX=”1″/>
</HOME_LIST>
</INVENTORY>

[[[[DO YOU SEE AN ENTRY FOR THIS ORACLE HOME (10.1.3)]]]]

ooops … i dont …. THIS IS THE ISSUE

So what should i do now?

Pretty simple, add an entry for this Oracle Home just above or below HOME_LIST tag.

<HOME NAME=”SID_TOOLS__masked_oracle_home_tech_st_10_1_3″ LOC=”[masked_oracle_home]/tech_st/10.1.3″ TYPE=”O” IDX=”2″/>

After making changes,

<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>10.1.0.5.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”SID_TOOLS__masked_oracle_home_tech_st_10_1_2″ LOC=”[masked_oracle_home]/tech_st/10.1.2″ TYPE=”O” IDX=”1″/>
<HOME NAME=”SID_WEBOH__masked_oracle_home_tech_st_10_1_3″ LOC=”[masked_oracle_home]/tech_st/10.1.3″ TYPE=”O” IDX=”2″/>
</HOME_LIST>
</INVENTORY>

Save the file.

Now you retry the same command it should go through.

If you dont want to update your inventory.xml which is under the global inventory location, you can pass a parameter to the opatch utility to use a different inventory location provided that inventory has information about this oracle home.

opatch -invPtrLoc [New .loc file location]

Happy troubleshooting !!!!

cheers,
OraclePitStop.

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

Database Replace Function to change profile option values

Posted on July 30, 2008. Filed under: Configuration, Database, DBA/Admin Tips, Discoverer, R12, Scripts, TroubleShooting | Tags: , , |

Heres an example of using the replace() to update profile_option_value column in fnd_profile_option_values table.

Scenario: Imagine you did a clone from DEV to TEST and in DEV there were lot of profile option values set at the responsibility and user level for the discoverer viewer url which came as is to TEST. Now you want to change the values in fnd_profile_option_values to refer to TEST.

DEV Discoverer Viewer URL: https://discodev.mydomain.com/discoverer/viewer?Connect=%5BAPPS_
SECURE]

TEST Discoverer VIewer URL: https://discotest.mydomain.com/discoverer/viewer?Connect=%5BAPPS_SECURE%5D

SQL to change the values

1. create table fnd_povbk as select * from fnd_profile_option_values; [  backup just in case you mess up ]

update fnd_profile_option_values set profile_option_value = replace(profile_option_value,’https://discodev.mydomain.com&#8217;,’https://discotest.mydomain.com&#8217;) where profile_option_value like ‘https://discodev.mydomain.com%&#8217;;

TADA !!! values replaced.

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None 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.

cheers,
OraclePitStop

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.

audit_syslog_level=local1.warning

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

eg:
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.

cheers,
OraclePitStop.

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

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