Thursday, December 17, 2009

Dropping a Database in Oracle

In my last post, I documented the syntax for creating a database using as brief a syntax as possible. Beginning in Oracle 10g, it is now possible to merely enter "create database" in sqlplus, and successfully create an instance. Along with this sparse syntax comes a new, even more esoteric concept: The ability to DROP a database in the same manner.

In prior versions of Oracle, removing a database was something of a manual endeavor, and was therefore error-prone and often incomplete. It involved removing files and directories tied to an instance by hand, and might leave tattered evidence of a previous database's existence that Grid Control and other monitoring tools might pick up and report on. In a worst case scenario, I've seen these databases come to life in a zombie-like fashion after certain system events like reboots, simply because someone failed to remove a parameter from a file which was setup as the "root" user during installation, and therefore not addressed during the manual drop.

The new command attempts to address all such issues. By merely saying "drop database" while connected to the appropriate software and with the ORACLE_SID setting to match, a database "goes away". This is, of course, a destructive operation! It requires the database to be in a restricted mode, and it cannot easily be recovered from, once executed (which leaves it a good time to mention that before ANY command is executed against an Oracle database, a recovery plan should always be considered).

Command script:

SQLPLUS> connect / as sysdba
SQLPLUS> shutdown abort;
SQLPLUS> startup mount restrict;
SQLPLUS> drop database;

It's as simple as that!

Check around your system for the last vestiges of the database you just dropped. While this command will address everything directly attached (datafiles, online logs, controlfiles, alert logs, etc), it will only do so for locations defined in the database at the time the command was entered. This might mean that logs or scripts that were relocated at different points of time would not be removed.

While I think this command is an excellent tool for cleaning up, at least a cursory look around the system after execution is certainly a must. And don't forget grid control, if you're using it. Monitor this server after a period of time and all remnants of it should be gone.

Monday, October 5, 2009

DBA Minimalism

DBA Minimalism

I went to an Oracle seminar once, and the instructor told us that as of version 10g, it was possible to create a database in Oracle from the sqlplus command line with the sparse verbiage “create database;” and nothing else. He then challenged us to figure out how it could be done. Keep in mind, that as an Oracle DBA for about 20 years, I have seen “create database” clauses, on certain platforms, with certain options, that ran for pages of SQL in a single statement. Specifications for the command in the oracle manual are here:

The complete description, including all options for CREATE DATABASE covers 21 pages of the manual!!!

There is also a new command called “drop database;” and both of these were likely implemented to make oracle configuration simpler and more consistent with the other RDBMS. So I took up the challenge, and was successful in creating a database in this manner. I did this as an exercise, and because I believe it teaches some interesting concepts as to exactly how oracle works. It could also be useful to quickly stand up an instance to verify the software install, meet a tight deadline, or validate available resources for a more complex build later on.


Starting an Oracle instance still requires, at a minimum, a valid initialization file, with certain specifications, sufficient system memory and disk space, and system rights to create files and access system memory. Your user session (usually called “oracle” on most systems, needs ENV settings as well. Many shops use the “oratab” file to set ORACLE_HOME and ORACLE_SID and other settings, so be sure to add this database to that file, which is something that the Oracle installer does anyway.

I called my database, for these purposes “mynewdb”. Here is the sparsest initmynewdb.ora initialization file possible to create a database:


Some of these parameters are required, depending upon your system specifications, and others just make the database more useful and self documenting (such as the bdump, cdump and udump directories). The above assumes that directories specified for control_files and db_create_file_dest already exist. Before trying this, create them and check carefully that they are spelled correctly. If not, your startup will fail.

After running “create database;” with the above, a running database exists. However, the catalog and dictionary still need to be updated to make it truly useful. This is done by running the traditional catalog and catproc scripts:

Sqlplus > @?/rdbms/admin/catalog
Sqlplus > @?/rdbms/admin/catproc

In the log above, I also create a temporary tablespace, a users tablespace (both considered minimal best practices), and make a list of users. As you can see, 7 are created by the Oracle binaries. An additional step would be to make those tablespaces the temporary and default respectively for each of thoese users. This can be easily scripted.
Of course, if you have requirements for options or features such as partitioning or statspack, scripts to create those optional objects supporting them will also need to be run. Your default database is also minimally secure, and should probably have default passwords for users changed, but that is considered out of the scope of this discussion. Even though more work may need to be done, you can say your database is up and running!


I’d be lying if I said this worked the first time. On most systems, the storage parameter defaults are insufficient to support opening the database. That is why parameters such as sga_max_size and shared_pool_size are specified for the solaris 64-bit example above. If your first attempt fails, be sure to remove controlfiles and any directories generated by the failed creation attempt.

My next document will highlight the new “DROP DATABASE” syntax. While it sounds as simple as pie, like anything else Oracle, there are caveats and a moderately difficult recipe to follow as well. Maybe you can even figure it out on your own?

Good luck!

PROOF THAT IT WORKS (please do try this at home!):

> sqlplus / as sysdba

SQL*Plus: Release - Production on Mon Sep 14 14:47:00 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 503316480 bytes
Fixed Size 1268340 bytes
Variable Size 427820428 bytes
Database Buffers 67108864 bytes
Redo Buffers 7118848 bytes
SQL> create database;

Database created.

SQL> set termout off
SQL> @?/rdbms/admin/catalog
SQL> @?/rdbms/admin/catproc
SQL> set termout on
SQL> select file_name from dba_data_files;


3 rows selected.

SQL> create temporary tablespace temp1 tempfile;

Tablespace created.

SQL> select username from dba_users;


7 rows selected.

SQL> create tablespace users datafile;

Tablespace created.

SQL> exit

Saturday, September 12, 2009

On The Road Again

For the second time in a year, to keep food on the table, I have taken a short-term consulting job out of town. While I have traveled for work in the past, I had forgotten what a real pain it could be. While my kids are grown now, I still miss my wife and home, and the normal daily routines. Furthermore, the government and the airlines have conspired to remove any of the joy and excitement involved in the trip itself, with rules for security that have morphed into revenue enhancing moves by the airlines. For example - reducing the amounts and types of items that can be carried on board, and then charging for checked bags.

While I still enjoy the challenges of my job, being in an unfamiliar environment can cause difficulties in concentration. I almost wonder how people who travel all the time for a living (including professional athletes) can do it.

That's all for today. Back to work!