Manually drop and create database

I am using VMware for playing with Oracle databases or OS'es. It is a great tool for practicing whatever comes on your mind.

Yesterday I've decided to manually destroy testing database and then recreate it manually. Oracle has supplied GUI tool for creating databases, such as DBCA, and it is very easy to create database using DBCA tool, but you as a DBA should know how to create database manually for situations when GUI isn't working cause of some reason. Creating database using just command line, you will find out steps which Oracle executes during database creation using DBCA. Besides that, you can destroy database using DBCA, but it can be done manually also. I will show that too.




Manually Drop Database

It is very easy to drop database. One way is just to remove all datafiles using OS specific command like "rm" or you can do it from SQL command line what will I do in this example.

Frist I will set ORACLE_SID and shutdown database.

[oracle@ciciban ~]$ export ORACLE_SID=test11
[oracle@ciciban ~]$ echo $ORACLE_SID
test11
[oracle@ciciban ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Sep 7 20:16:19 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Then I'll startup database in "mount exclusive restrict" mode and execute command "drop database".

SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area  313860096
Fixed Size                  1299624
Variable Size             197135192
Database Buffers          109051904
Redo Buffers                6373376
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

This is all you have to do.
If you check directory where you're datafiles were located, you'll see that all datafiles and logfiles are dropped.



Manually Create Database

Now I want to manually create database. I will use just SQL command line.

First I will decide what will be ORACLE_SID for my new database and set it.

export ORACLE_SID=test
[oracle@ciciban ~]$ echo $ORACLE_SID
test

Next I will create init.ora file located in "$ORACLE_HOME/dbs" directory because Oracle will try to use that parameter file during instance startup.
I will add parameters:

db_name='test'
control_files='/oracle/oradata/control01.ctl','/oracle/oradata/control02.ctl','/oracle/oradata/control03.ctl'
db_block_size=8192
undo_tablespace='UNDO'
undo_management=auto

You could add other parameters also like db_domain, sga_target, memory_target, process or others. But remember that it is important to create basic init.ora parameter file with db_name specified.

Now I'll start instance in nomount mode, which starts all processes and allocates memory for SGA, but doesn't mount database.

[oracle@ciciban dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Sep 7 20:38:04 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1299624 bytes
Variable Size             180357976 bytes
Database Buffers          125829120 bytes
Redo Buffers                6373376 bytes
SQL>

Now it's time to issue CREATE DATABASE statement.

SQL> CREATE DATABASE test
DATAFILE '/oracle/oradata/system.dbf' size 200M
autoextend on next 20M maxsize unlimited
extent management local
SYSAUX DATAFILE
'/oracle/oradata/sysaux.dbf' size 100M
UNDO TABLESPACE UNDO DATAFILE
'/oracle/oradata/undo01.dbf' size 100M
LOGFILE
GROUP 1 ('/oracle/oradata/redo01.dbf') size 50M,
GROUP 2 ('/oracle/oradata/redo02.dbf') size 50M,
GROUP 3 ('/oracle/oradata/redo03.dbf') size 50M
CHARACTER SET "WE8ISO8859P1"
NATIONAL CHARACTER SET "UTF8"
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE
'/oracle/oradata/temp01.dbf' size 75M
NOARCHIVELOG
MAXDATAFILES 500
MAXLOGFILES 10
/  

Database created.

I've specified some options for creation.
On top I use "test" as my database name (db_name). Then I define location and size of datafiles, logfiles and controlfiles. My database will be in NOARCHIVE log mode. All options are pretty easy to figure out so I will not explain further.

After creating database I have to create data dictionary views and stored procedures. To do that I have to execute script "catalog.sql":

SQL> @?/rdbms/admin/catalog.sql

. To create objects required to use PL/SQL I have to execute "catproc.sql" script:

SQL> @?/rdbms/admin/catproc.sql

.

At the end I'll just create SPFILE from INIT.ORA file:

SQL> create spfile from pfile;

File created.

Now I have fully functional database for testing purposes.

P.S.
If something goes wrong during database creation you should check alert.log file for errors.