Oracle RAC – granting SYSDBA or SYSOPER is instance specific

Posted on January 9, 2012 by marcel

The default location for the password file is $ORACLE_HOME/dbs which in most cases (if you don’t have your ORACLE_HOME on a shared disk) is on a non-shared filesystem. Whenever you grant SYSDBA or SYSOPER to a database user Oracle will add the user to the password file. What the Oracle documentation doesn’t tell you is that the user will only be added to the password file for the instance where you are connected to, not to the password files for all other RAC instances running on other nodes.

So to make sure you can connect as a user (with for example AS SYSDBA) on all RAC database instances, you have todo the GRANT SYSDBA and/or GRANT SYSOPER on ALL RAC instances separately.

Continue reading →

Posted in RAC | Tagged , , , , , , , , , , , , | Leave a comment

ORA-00245: control file backup operation failed

Posted on January 9, 2012 by marcel

A snapshot controlfile is a read consistent copy of a database controlfile which RMAN creates because the controlfile is changed constantly.

As of Oracle 11.2 (11.2.0.2 to more precise) the snapshot controlfile must be on a location shared by all RAC nodes in a cluster (it must be accessable by all nodes on which instances for an RAC database can run). If you do not do so, you most likely will receive an ORA-00245 (ORA-00245: control file backup operation failed) when running an RMAN backup.

Although this is an requirement as of 11.2, when you create a RAC database using dbca the default location will still be the $ORACLE_HOME/dbs which in most cases is a non-shared location. Possible shared locations: cluster filesystems like OCFS and GPFS, ACFS and ASM.

Continue reading →

Posted in RAC | Tagged , , , , , , , , , , | 1 Comment

Oracle Database Appliance – Installing patch 2.1.0.1.0

Posted on January 6, 2012 by marcel

Just recently (31 December 2012) the first Patch Bundle (MOS patch name 13539664 – LNX64-112-CMT: PLACE HOLDER BUG FOR OAK PATCH 2.1.0.1.0) for the ODA was released. This Patch Bundle only contains firmware updates for the shared disks (HDD) and a new version of OAK (Oracle Appliance Kit).

Because the only information about patching the ODA came from the documenation library (where it is stated that rolling-updates are not supported) I was curious what this rather small patch (no Oracle software patches, only firmware updates and an new release of OAK) would impact the availability of the complete ODA system. In this post I will share my experience with this first ODA patch bundle.

Continue reading →

Posted in Database Appliance | Tagged , , , , , , , , , , , , | Leave a comment

Oracle Database Appliance – Inventory and Package Repostitory

Posted on January 2, 2012 by marcel

An Oracle Database Appliance has its own inventory to keep track of all installed firmware and software component versions and patches. ODA patches are released in the form of patch bundles. ODA patch bundles can contain patches for:

  • System Firmware
  • Operating System
  • Oracle Grid Infrastructure
  • Oracle Database
  • Oracle Appliance Manager

The Oracle Database Appliance inventory in fact is a xml file (/opt/oracle/oak/pkgrepos/System/system_repos_metadata.xml) containing an entry for every ODA “component” that is updatables through patches or firmware upgrades like: disks (local and shared), ILO, BIOS, operating system, Oracle software, etc.

ODA OAK repository structure
Continue reading →

Posted in Database Appliance | Tagged , , , , , | Leave a comment

Oracle Database Appliance – Appliance Manager

Posted on January 2, 2012 by marcel

The Oracle Database Appliance is sold to allow customers to quickly deploy and Oracle RAC environment using Oracle’s best practices for deployment. And it is true that in less than 2 hours (thats excluding all things that needs to be done in preparation like physically getting the ODA in an data center, connecting cables, configuring network switches and firewalls and getting all the required IP addressess), you have a 2-node Grid Infrastructure cluster running with a RAC database running on it. The deployment process is done using the Oracle Appliance Manager, which is a java based wizard (GUI) which lets you enter all required information to get an ODA deployed.

Unfortunately we (at least administrators and people that implement things like this) know that these kind of one-button promises always come with some predefined settings that may be not what we want. In this post I will tell you what the Appliance Manager is doing in the background and what can (and as far as I understand is allowed by Oracle) be changed to have the deployed 2-node RAC configuration on an ODA comply with your company’s standards.

ODA Appliance Manager

Continue reading →

Posted in Database Appliance | Tagged , , , , , , , , , | 2 Comments

Oracle Database Appliance – Deployment Steps

Posted on January 1, 2012 by marcel

The installation (deployment) of an Oracle Database Appliance (ODA) is done by the ODA configuration manager. After you filled out all the information in the various screens (or you have loaded a parameter file and clicked next on all the subsequent screens) after you click the “install” button, the deployment progress is started which consists of 25 steps. Each step executes some scripts and you can see the status of each of the 25 steps in the configuration manager progress screen. You can click the “detail” button to see logging information (what you see is the logfile that you can find in /opt/oracle/oak/onecmd/tmp and with a name that starts with STEPS-…) and what the steps is actually doing and more important what went wrong when the status of a step is set to “failed”.

In this post I’ve created a table with all the steps that are executed during the deployment and the durtion of each step (could be a little different when different things are selected, but I guess the timing is the same for most deployments). In an update of this post I will give a more detailed description what exactly is done in each step, but for now I think the names of the steps tell you what is done.

Continue reading →

Posted in Database Appliance | Tagged , , , , , , , , , , | 9 Comments

Where does SQL*Plus look for configuation files on Solaris

Posted on September 4, 2011 by marcel

This post will show you the order in which SQL*Plus for Solaris reads the configuration files like the tnsnames.ora.

tnsnames.ora

  1. $HOME/.tnsnames.ora
  2. $TNS_ADMIN/tnsnames.ora
  3. /var/opt/oracle/tnsnames.ora
  4. $ORACLE_HOME/network/admin/tnsnames.ora

If the specified alias is not found in (or the file does not exists) in $HOME/.tnsnames.ora it will try to find the alias in the next available file using the above order. If such a file exists, but the alias is not available there you’ll receive an error message. So SQL*Plus only reads the next file when .tnsnames.ora is available  and the alias cannot be found there.

Posted in Database | Tagged , , | Leave a comment

sqlnet.expire_time – to restart or not to restart

Posted on September 4, 2011 by marcel

We had a situation where we had to set the sqlnet.expire_time parameter in the sqlnet.ora file to avoid firewall dropping “inactive” sessions. The only problem was that we were not allowed to restart the listener or the database instance. Searching through the Oracle documentation and google just didn’t give a solid answer what needs to be restarted to start using this setting. Trying to figure out what needs to done to get this setting being used, got me to find out the following:

When you reload the listener (re-reading the configuration, not actually restarting the listener process, lsnrctl reload …) the sqlnet.ora file gets read, but only if it existed when the listener was started! If an sqlnet.ora file existed when the listener was started (and you removed the sqlnet.ora file while the listener is running) and you reload the listener, it will find out that the sqlnet.ora file does not exist anymore and will not try to read the sqlnet.ora file the next time you reload the listener. The only option you’ll have is to stop and start the listener again.

So do you need to restart?

However to actually let the oracle server process use the sqlnet.expire_time parameter, you just have to set the parameter in the sqlnet.ora. When you open a new session to your database a new Oracle dedicated server process gets started (if your database is running in dedicated server mode of course) which will read the sqlnet.ora file and use the specified sqlnet.expire_time. So whenever you change this parameter the new value gets used by all Oracle processed created after the change. All running dedicated server processes keep using the value for the parameter as it was at the time they were created.

So do you need to restart anything to start using the sqnlet.expire_time parameter? No you do not for new sessions!

If you want to use the new value for existing sessions you have to make these sessions reconnect.

 

Posted in Database | Tagged , , , , , , | 6 Comments

Solaris SMF Oracle Grid agent service killing Oracle instance

Posted on August 22, 2011 by marcel

In the last couple of months we had some strange problems with all processes of Oracle database and ASM instances just disappearing. The shared memory segment was still there and you could connect internally to the instance, even execute some queries until you give some statement where it actually needed to do something, resulting in some kind of a “ghost” instance. We had to shutdown the instance using ”shutdown abort” and start it up again. There was nothing logged in any logfile, so we didn’t know what killed the instance. We knew the killing had to be done in a very nasty way, because when you kill the processes of an Oracle instance one by one, something gets logged and Oracle will be “terminated” by the leftover processes.

A couple of days ago we accidently reproduced the above problem (all Oracle processes suddenly gone, but the shared memory segment still there) when we patched the Oracle Grid Control agent on one of our database servers. We use(d) to have an Solaris SMF service defined for starting/stopping/restarting the Oracle Grid Control agent on all of our database servers, so before patching the Oracle Grid Control agent we stopped the agent by using the svcadm -v disable command. When we tried to restart the agent again after patching, we noticed that the service was being placed in maintenance mode, meaning something went wrong when the agent was stopped.

Continue reading →

Posted in Enterprise Manager | Tagged , , , , , , , , , , | Leave a comment

Creating database links for another schema

Posted on August 5, 2011 by marcel

There are situations (like after cloning databases) where you, as a DBA, want to create database links in other schema’s, for which you don’t have the password. Unfortunately the security of Oracle doesn’t allow (even SYS) you to drop/create database links for other schema’s.

One solution for this is to get the password hash value, temporarily change the password for the schema, connect as the user, create the database link, change the password for the schema back to its original value (using the password hash value of the original password).

In some situations (on a running database where users connect to the database) you cannot temporarily change the password and you’re stuck. There are however some tricks you can use to make Oracle execute the CREATE DATABASE LINK or DROP DATABASE LINK as another user. This post will describe both and you can choose the one you like the most (they will work both).

Continue reading →

Posted in Database | Tagged , , , , , , , , | 4 Comments