Simple Oracle Practices

Simple Oracle Practices

Sometimes, it's the simple things that get us into trouble -- or can save us big time.

Any experienced UNIX admin will tell you to always do an "ls" (list structure) command with your wildcards before you use them in a "rm"  (remove) command.  Similarly, you should test your WHERE criteria in a SELECT statement (perhaps with COUNT) before you use them with a DELETE.

Here are some more very simple practices to develop as a habit, and which will save you at some future time.

1.  After you set ORACLE_HOME manually, test it by changing your current directory to it.  In Unix/Linux, the command is "cd $ORACLE_HOME"; in Windows, it's "cd %ORACLE_HOME%".  If the cd command succeeds and you list the contents of the directory, you should see a standard ORACLE_HOME.  If the cd command fails, or doesn't have the usual subdirectories in it, you've mistyped the pathname and can fix it before relying on it in other commands.

2.  After you connect to the database using "/ AS SYSDBA", test it by the following query:

SELECT instance_name, status FROM v$instance;

You'd be surprised how many times you THINK you connected to one instance, but in reality, you've connected to a different one. This only takes a second, and can save you a LOT of grief.

3.  After a SHUTDOWN ABORT for any reason, immediately follow with STARTUP RESTRICT.  This lets the SMON process perform instance recovery, so that the database is consistent; but it doesn't allow non-privileged users into the database.  Then do a SHUTDOWN IMMEDIATE and continue with whatever corrective action you need.

Three simple things, which if made into a habit, can help prevent gunshot wounds in your feet.