Oracle command line Utilities – I
Posted by decipherinfosys on June 3, 2008
We have covered many Oracle utilities in our blog posts before. One of the client DBAs had asked them to be summarized in one place and include some that we had not covered so far, so here it goes…we will write up a series of posts and in each one of them cover the utilities pertaining to a particular function like database administration utilities (dbshut, dbstart, dbverify, orakill, csscan etc.), data copy/load utilities (like exp, imp, expdp, impdp, sqlldr), networking utilities (like )tnsping, lsnrctl, trcroute), diagnostics and performance tuning utilities (like tkprof, oradebug, 10053 trace etc.)
We will start this series covering some of the Database Administration utilities.
dbshut: As the name suggests, it can be used to shut down the Oracle database. It essentially performs a shutdown immediate to shut down the instance. This utility is not available for Oracle installations on Windows. And in order to be able to use this utility to shutdown an instance of Oracle, that instance should be in the oratab file and another important thing to remember is that the last field should be set to Y. So, what and where is the oratab file you ask? oratab file is on the database server is it’s default location is in the /etc directory or the /var/opt/oracle directory. Here is the output from our environment:
$ echo $ORACLE_SID
$ grep $ORACLE_SID /etc/oratab
As you can see from above, the line has three distinct pieces of information: the SID of the instance: dec10g, the ORACLE_HOME directory and then the key one: a Y/N which indicates that dbshut will be applicable on this instance. All instances flagged with a Y will get shutdown when dbshut is run.
dbstart: Just like dbshut, dbstart is used to startup an Oracle instance. Everything mentioned above for oratab, not being available for windows, the Y/N flag etc. is true for dbstart as well. Most common usage of dbstart is to startup the Oracle instance(s) automatically when the system starts up.
dbhome: While on the topic of oratab, let’s also cover dbhome – you can use this to get the Oracle Home directory for a given Oracle SID. And just like dbshut or dbstart, this is not available for Oracle installations on Windows. Example:
$ dbhome dec10g
oerr: This is used to view error messages corresponding to an error code, their cause as well as any recommendations to resolve those errors. We had covered this before – you can read more on this here.
dbv: The command to run db verify is dbv. It is used to validate the physical structure of the data files of a database. One can use it to ensure that the backup is valid or to check data block corruption. It is used only to check the validity of the data files and cannot be used for redo logs or control files. A typical usage of dbv is to use them against cold backups of datafiles since if run against any open files currently in use, it can return incorrect results for blocks that are being updated. You can lookup the syntax in the Oracle docs here but one thing that we want to point out here is that if the block size of the data file is not the default value, then you need to specify the block size in the command line otherwise you will get the DBV-00103 error.
relink: We can use relink shell script to manually relink the Oracle product executables. Before using relink, you should set the ORACLE_HOME environment variable. The script is available under $ORACLE_HOME/bin directory. You should relink the product executables every time that you apply an OS patch or an OS upgrade. And before relinking the executables, you should shut down all the executables that you are relinking and also shut down applications linked with Oracle shared libraries.
We will discuss some more database administration command line utilities in an upcoming post and then go ahead and discuss some diagnostics utilities – some of them we have already discussed before – tkprof and Trace Analyzer and 10053 event trace.