 |
|
Database Deletion
Oracle Tips by Burleson
|
Databases are deleted by shutting them down
and then removing all of their associated files from the system. No
command to perform this is provided by Oracle prior to Oracle. In
Oracle, the DROP TABLESPACE command enables the DBA to drop the
operating system files, as well as the logical database structures.
The Database Creation Assistant (DBCA) does allow a DBA to remove a
database in 8i and 9i. Let's look at an example using the DBCA on
Linux.
The DBCA is started by issuing the DBCA
command at the Oracle user in Linux or UNIX (or selecting it from the
appropriate menu in Windows or NT.) The first screen that is displayed
is shown in Figure 2.21. Select the Next button to display the
Operations screen (see Figure 2.22). There, select the option Delete a
Database and then select the Next button.
Figure 2.21 Welcome Screen of the Database
Configuration Assistant
Figure 2.22 Operations screen of the Database
Configuration Assistant.
The DBCA will display the Database screen next
(shown in Figure 2.23). On the Database screen, select the database
you wish to delete and then select the Next button.
Figure 2.23 Database screen of the Database
Configuration Assistant.
Once you have made a database selection, the
DBCA will show a Summary screen (shown in Figure 2.24) to give you a
first chance to abort the deletion, just in case you chose the wrong
database SID on the previous screen. Once you have verified that the
database being deleted is in fact the one you want deleted, select the
OK button to continue with the deletion process.
Figure 2.24 Summary Screen of the Database
Configuration Assistant
After you select OK on the Summary screen, the
DBCA program gives you one last chance to decide not to delete the
instance. This screen isn't titled, so I call it the "Are you really,
really sure" screen; it’s shown in Figure 2.25.. If you are really,
really sure you want to delete the database, select Yes, and the
delete operation will commence. This is the point of no return.
Figure 2.25 The "Are you really, really sure?"
screen of the Database Configuration Assistant.
The next screen shown in the DBCA for the
database deletion process displays the actual deletion status (Figure
2.26), that is, the progression through the various steps of deleting
a database: connect, delete the instance and datafiles, and update
network files. Following the final step, the DBCA automatically goes
to the completion screen.
Figure 2.26 Database deletion status screen of
the Database Configuration Assistant.
The final screen of the deletion process is
the Database Deletion Completed screen )shown in Figure 2.27). It asks
the question "Do you want to perform another operation?" The choices
are Yes, to return to DBCA, or No, exit from DBCA. If you are done
deleting databases, choose No.
Figure 2.27 Database deletion completion
screen of the Database Configuration Assistant.
Manual Database Deletion
If you must manually delete a database, first
log in as a DBA account, then use the following commands to get a
listing of all the datafiles associate with the database:
Spool db_files.lis
Select file_name from dba_data_files;
Select file_name from v$log_files;
Select value from v$parameter where name='control_files';
Spool off
Once you have your file listing, shut down the
database. And I suggest doing a complete, final backup (rest assured,
someone will require something from this database sometime in the
future) of the database before deleting it.
Once you have backed up the database that you
wish to delete, delete the physical files from your list. There are
several files that contain data concerning the database and the
instance. If deletion is required of both, it is suggested that a UNIX
“grep” or “find” command be used to locate the files that contain the
instance or database name. On UNIX, the files of concern are oratab,
listener.ora, tnsnames.ora, and any user-created scripts or files. On
NT, you will need to remove the services and delete the database names
from the listener.ora and tnsnames.ora files. The name and related
data must be removed from these files, or the files deleted, if the
user wishes to reuse the instance name. Generally, the files on UNIX
or Linux are in the /etc or /var/opt/oracle directory or (in the case
of the network files) in $ORACLE_HOME/network/ admin. If only the
database needs to be deleted, and then re-created, edit the CREATE
script and rerun it under SVRMGR or SQLPLUS with the database shut
down. This will reinitialize the database.
Changing the “dbname” for a Database
Follow these steps to change the dbname for a
database:
1. At the operating system command line enter:
%
svrmgrl or sqlplus /nolog
2. Once the server manager or sqlplus
command line appears, enter:
connect internal (for svrmgrl) or connect / as sysdba or connect sys
as sysdba (for sqlplus)
3. When the server manager or sqlplus
indicates that you are connected, enter the command:
alter
database backup controlfile to trace resetlogs;
This will write in a trace file, the CREATE
CONTROLFILE command that would re-create the control file as it
currently exists. This trace file will be located in the
USER_DUMP_DEST location specified in the v$parameter table (select
value from v$parameter where name='user_dump_dest').
4. Exit and go to the directory where your
trace files are located. They are usually in the $ORACLE_HOME/admin/<sid./udump
directory, if you haven’t explicitly set the location in your
initialization file. If user_dump_dest is set in the init<SID>.ora,
then go to the directory listed in the user_dump_dest variable. The
trace file will have the form “ora_NNNN.trc,” with NNNN being a number
that will correspond (either in decimal or hexadecimal) to your system
process ID.
5. Get the “CREATE CONTROLFILE” command
from the trace file and put it in a new file called something like
crt_cf.sql.
6. Edit the “crt_cf.sql” file and modify
the “CREATE CONTROLFILE” command. Change the word “REUSE” to “SET”,
and “NORESETLOGS” to “RESETLOGS”, and modify the “dbname.”
Old line:
CREATE
CONTROLFILE REUSE DATABASE "old_name" NORESETLOGS ...
New line:
CREATE
CONTROLFILE SET DATABASE "new_name" RESETLOGS ...
Then save the “crt_cf.sql” file.
7. Rename the old control files for backup
purposes and so they are not in the way of creating the new ones.
8. Edit initSID.ora so that db_name = ”new_name”.
9. At the operating system prompt type:
%
svrmgrl(pre-8i) or sqlplus /nolog (post 8i)
10. Once the server manager or sqlplus
prompt appears, enter:
connect
internal (pre-8i) or connect / as sysdba or connect sys as sysdba
(post-8i)
11. Once you are connected as INTERNAL or
SYS, enter:
startup nomount
12. Type:
@crt_cf
13. Once the file stops executing, enter:
alter database open;
14. Verify that the database is functional;
you can check that the database name has been reset with the
command:
select
name from v$database;
15. Shut down and back up the database. Make
sure any local variables are also changed to reflect the SID
(for example, ORACLE_SID).
Other Database Administration Tasks
Let’s look at some of the other operations
that may need to be performed against a database.
See
Code Depot for Full Scripts
 |
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
