Drop Oracle 9i Database

Drop Oracle 9i Database

As many of us might expect dropping database would be quite straight forward and intuitively the command should be something like this -

DROP DATABASE <INSTANCE_NAME>;

Well, even I was of the same opinion and tried it on Oracle database version 9i. I was surprised to see 9i wont support DROP DATABASE statement. However, I am glad to figure out how to deal with such situation. 
To proceed further we will need to query few in built dynamic views to find out exact location of the physical database files (Datafiles, Redo Logs, Archive Logs, and Control Files). 

To find out exact location of the datafiles we need to query v$datafile -

SQL> select name, status from v$datafile

NAME                                                         STATUS
------------------------------------------------------------ -------
/database/oradata/TOYDB/system/SYS_TOYDB_001.dbf              SYSTEM
/database/oradata/TOYDB/system/SYS_TOYDB_002.dbf              SYSTEM
/database/oradata/TOYDB/rbs/RBS_001_TOYDB_001.dbf             ONLINE
/database/oradata/TOYDB/rbs/RBS_001_TOYDB_002.dbf             ONLINE
/database/oradata/TOYDB/temp/TEMP_001_TOYDB_001.dbf           ONLINE
/database/oradata/TOYDB/users/TOOL_001_TOYDB_001.dbf          ONLINE
/database/oradata/TOYDB/users/DRSYS_001_TOYDB_001.dbf         ONLINE
/database/oradata/TOYDB/users/TABLES_TS_001_TOYDB.DBF         ONLINE
/database/oradata/TOYDB/users/INDEXES_TS_001_TOYDB.DBF        ONLINE
/database/oradata/TOYDB/temp/TEMP_TS_001_TOYDB.DBF            ONLINE

10 rows selected.
SQL>

To find out location of redo logs -

SQL> desc v$logfile
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 TYPE                                               VARCHAR2(7)
 MEMBER                                             VARCHAR2(513)
 SQL> ;
  1* select group#, member, type, status from v$logfile
SQL> /
    GROUP# MEMBER                                             TYPE    STATUS
---------- -------------------------------------------------- ------- -------
         2 /database/oradata/TOYDB/redo/LOG_002_TOYDB_001.rdo  ONLINE  STALE
         2 /database/oradata/TOYDB/redo/LOG_002_TOYDB_002.rdo  ONLINE  STALE
         1 /database/oradata/TOYDB/redo/LOG_001_TOYDB_001.rdo  ONLINE
         1 /database/oradata/TOYDB/redo/LOG_001_TOYDB_002.rdo  ONLINE
SQL>

To find out the location of control file -

SQL> desc v$controlfile
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATUS                                             VARCHAR2(7)
 NAME                                               VARCHAR2(513)
SQL> select name, status from v$controlfile;
NAME                                                         STATUS
------------------------------------------------------------ -------
/database/oradata/TOYDB/control/CTRL_001_TOYDB.ctl
/database/oradata/TOYDB/control/CTRL_002_TOYDB.ctl
SQL>

Finally confirm if database is archive log mode -

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /product/oracle/base/9.2.0-Std-64b/dbs/arch
Oldest online log sequence     7
Current log sequence           8
SQL>

If database is in archive log mode then we will further need the location of archive log files. Since the database is running in no archive log mode, we can proceed directly with the dropping database. 
Below are the steps to follow --- Shutdown the database instance, if not already done- Go to the locations of datafiles and delete them using OS delete command- Go to the locations of redo logs and delete them using OS delete command- Go to the location of control files and delete them using OS delete command- Finally go to $ORACLE_HOME/dbs and delete parameter file and password file using OS delete command

Supraj:/database/oradata% ls -lrt TOYDB
total 8
-rw-r--r--   1 oracle     dba           1152 Mar 11  2010 create_TOYDB.sql
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 control
drwxr-xr-x   2 oracle     dba           1024 Jan 15  2011 users
drwxr-xr-x   2 oracle     dba           1024 Jan 15  2011 redo
drwxr-xr-x   7 oracle     dba             96 Jan 15  2011 adm
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 system
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 rbs
drwxr-xr-x   2 oracle     dba             96 Jan 15  2011 temp


Supraj:/database/oradata% rm -r TOYDB
Supraj:/database/oradata% ls -lrt TOYDB
TOYDB not found
Supraj:/database/oradata%

Rename Tablespace and Datafile 

Rename tablespace is quite straight forward, as you could expect

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

Once the tablespace is rename, many a times we would require to change the datafiles associated with the tablespace name. This could be because of organization policy or may be because of senior DBA insists on that.

To rename a datafile we have to follow below logical steps in given sequence --

- Take tablespace offline
    ALTER TABLESPACE tablespace_name OFFLINE;
 
- Physically rename the file using operating system command prompt
    mv /database/oracle/CLYP/data/DAT_001.dbf /database/oracle/CLYP/data/DAT_005_CLYP_001.dbf
 
- Rename the datafiles internally on SQL command prompt. This will make oracle to access the underlying datafile when the particular tablespace is addressed
    ALTER TABLESPACE tablespace_name RENAME DATAFILE '/database/oracle/CLYP/data/DAT_001.dbf' to '/database/oracle/CLYP/data/DAT_005_CLYP_001.dbf';
 
- Take tablespace online
    ALTER TABLESPACE tablespace_name ONLINE;

SQL used to genereate the script is as follows --

select 'ALTER TABLESPACE '
       ||tablespace_name
       ||' OFFLINE; '
       ||'!mv '||file_name||' '||replace(file_name,'REC1','CLYP')||'; '
       ||'ALTER TABLESPACE '
       ||tablespace_name
       ||' RENAME DATAFILE '''
       ||file_name
       ||''' to '''
       ||replace(file_name,'REC1','CLYP')
       ||''';'
       ||' ALTER TABLESPACE '
       ||tablespace_name
       ||' ONLINE;'
 from dba_data_files
where tablespace_name like '%CLYP'
order by tablespace_name;


Please note, the above script need to be altered before using it. This script was developed considering certain granules and environment details. The whole purpose of this SQL script is for the illustration that generating shell/scripts using the SQL queries can save repetitive work and will help in reducing typo errors.

Create and Alter Tablespace

Check exitsing tablespaces, their type and status -
====================================================
SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE

Creating a new tablespace with two datafile of different sizes -
================================================================
SQL> create tablespace demo_tbs
  2  datafile '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf' size 10M,
  3  '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf' size 5M;

Tablespace created.

SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE
DEMO_TBS               PERMANENT ONLINE

6 rows selected.

Renaming a tablespace -
=======================
SQL> alter tablespace demo_tbs rename to renamed_tbs;

Tablespace altered.

Renaming the datafiles -
=========================
Renaming datafiles includes two parts: rename on operating system level and renaming on database level. Rename operation on OS level needs physical rename of the datafile. For that we can use rename command on windows and mv command on linux/unix environment.

[oracle@localhost ~]$ cd $ORACLE_BASE/oradata
[oracle@localhost oradata]$ cd TOYDB/df
[oracle@localhost df]$ ls -lrt
total 938340
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:13 users_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:14 temp_01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 25 02:19 undo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:19 sysaux_01.dbf
-rw-r-----. 1 oracle oinstall 419438592 Aug 25 02:19 systems_01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Aug 25 02:20 demo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Aug 25 02:20 demo_tbs_02.dbf
[oracle@localhost df]$ mv demo_tbs_01.dbf renamed_tbs_01.dbf
[oracle@localhost df]$ mv demo_tbs_02.dbf renamed_tbs_02.dbf
[oracle@localhost df]$ ls -lrt
total 938340
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:13 users_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:14 temp_01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 25 02:19 undo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:19 sysaux_01.dbf
-rw-r-----. 1 oracle oinstall 419438592 Aug 25 02:19 systems_01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Aug 25 02:20 renamed_tbs_01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Aug 25 02:20 renamed_tbs_02.dbf
[oracle@localhost df]$ exit


Renaming the datadile on database level includes alter database command. We need to fire this once for each file. Please note that even though the datafiles are now renamed on OS level, at database level we still hold older names of these datafiles.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/systems_01.dbf
SYSTEM

/u01/app/oracle/oradata/TOYDB/df/sysaux_01.dbf
SYSAUX

/u01/app/oracle/oradata/TOYDB/df/undo_tbs_01.dbf
UNDO_TBS


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/users_01.dbf
USERS

/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf
RENAMED_TBS

/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf
RENAMED_TBS


6 rows selected.



After rename done on OS level we have to rename those datafiles on database levels else at runtime whenever any object belonging to these renamed datafiles
is referred, an error will be thrown. To rename datafile take tablespace offline.



SQL> alter database rename file '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf' to '/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf' to '/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_02.dbf';

Database altered.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/systems_01.dbf
SYSTEM

/u01/app/oracle/oradata/TOYDB/df/sysaux_01.dbf
SYSAUX

/u01/app/oracle/oradata/TOYDB/df/undo_tbs_01.dbf
UNDO_TBS


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/users_01.dbf
USERS

/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_01.dbf
RENAMED_TBS

/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_02.dbf
RENAMED_TBS


6 rows selected.


Taking tablespace online -
==========================

SQL> alter tablespace renamed_tbs online;

Tablespace altered.

SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE
RENAMED_TBS               PERMANENT ONLINE

6 rows selected.

Creating Database using sql script -

#################################################################
# Setup environment
# Typically configured in .profile (Unix) or .bash_profile (Linux)
#################################################################
export ORACLE_SID=TOYDB
export ORACLE_BASE=/database/oracle
export ORACLE_HOME=/product/oracle/base_dir/version_no
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/LIB:$LD_LIBRARY_PATH

#################################################################
# Parameter File - initTOYDB.ora
# Copy and paste the below lines in seperate file and rename the file with above mentioned name
# initSID.ora - where SID=service identifier (TOYDB) in our case
#################################################################
db_name=TOYDB
compatible=11.2.0
memory_target=400M
control_files='/database/oracle/RMCLY9P1/ctl/CTL_001.ctl','/database/oracle/RMCLY9P1/ctl/CTL_002.ctl'
undo_tablespace=undo_tbs
undo_management=auto

#################################################################
# Once the file is set then startup database to nomount stage using below command
# In nomount stage, the instance is created
#################################################################
startup no_mount pfile='absolute path of the above file'


#################################################################
# Create Database script - 
# Copy and paste the below lines as a seperate sql script
# Make sure before you execute this script, the instance is created
# Without instance created we cant execute this script
# Below script will create database with the name of TOYDB and will create controlfile as specified in parameter file
# Once done with DB creation, this script will take DB to mount stage
#################################################################
create database "TOYDB"
datafile '-- path of system tablespace datafile --' size 400M
sysaux datafile '-- path of sysaux tablespace datafile --' size 200M
default temporary tablespace temp tempfile ' -- path og temp tablespace tempfile -- ' size 400M
default undo tablespace undo_tbs datafile '-- path of undo tablespace datafile --'
default tablespace users datafile '-- path of users datafile --' size 400M
logfile
group 1 ('--path of member1_01.log file --','--path of member1_02.log file --') size 20M,
group 2 ('--path of member2_01.log file --','--path of member2_02.log file --') size 20M,
group 3 ('--path of member3_01.log file --','--path of member3_02.log file --') size 20M
user sys identified by oracle
user system identified by manager;


#################################################################
# Create Data Dictionary views
# Before executing these scripts make sure the DB is in mount stage
# Also these scripts needs to be execute in the same mentioned sequence -
# catalog.sql --> creates required/mandatory data dictionary tables and view
# catproc.sql --> creates required/mandatory PLSQL procedures, functions and packages
#################################################################

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql;
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql;