Still struggling with DATABase

[Deanna Tran]

Hello,
I'm still struggling with the oracle 9i databse. Druing the installation i have installed the sample database, now i want to remove that database. i tried to drop the database, but it said it is not open, so i did
alter database open command. However, i still do the drop command...since this is a sample database i don't know what tablespace have been created, and i can't use show database command or tablespace...I apprecieate your help

[Printaporn_1 · Accepted solution]

Hi,
To drop a database, remove its datafiles, redo log files, and all other associated files
(control files, parameter files, archived log files).
To view the names of the database???s datafiles and redo log files, query the data
dictionary views V$DATAFILE and V$LOGFILE.

[T G Manikandan]

check your ORACLE_SID and ORACLE_HOME.
Make sure that you are pointing the right ORACLE_SID

01. $svrmgrl
02. startup the database if it's not already started. The database must at least mounted.
svrmgrl>startup mount
03. spool /databasefiles.lst
svrmgrl>spool databasefiles.lst
04. select name from v$datafile; (This will get all the datafiles; alternatively, you can select file_name from dba_data_files)
svrmgrl>select name from v$datafile;
05. select member from v$logfile;
svrmgrl>select * from v$logfile;
06. select name from v$controlfile;
svrmgrl>select * from v$controlfile;
07. archive log list (archive_log_dest is where the archived destination is)
svrmgrl>archive log list;
Go to the destination directory of archive_log_dest parameter and remove all the files.
08. locating ifile by issuing show parameter ifile (alternatively, check the content of init.ora)
09. spool off
svrmgrl>spool off
10. Delete in O/S level the files listed in databasefiles.lst
11. remove all the entries which refer to the deleted database in tnsnames.ora and listener.ora (located in $ORACLE_HOME/network/admin)
12. remove all database links referring to the deleted database.
13. check "/var/opt/oracle/oratab" to make sure there is no entry of the database deleted. If yes, remove it.

Thanks

[melvyn burnard]

To be honest, from all of your postings I would suggest you seriously consider getting some training on HP-UX and Oracle.
It may save you some of the problems/headaches you seem to be experiencing.

[Victor Geere]

If the real solution sounds a bit hairy, you could recycle the database, by deleting all the sample tables and renaming the database to the next database you would have created.

Buy a good book before you spend a fortune on training. Hang in there.

[Andreas D. Skjervold]

Hi Deanna

Training is good, but my experience is that its better to learn some of the basics yourself before attending classes as you get more out of the short time spent with the tutor.

And struggling as it seems you are doing is the best way of learning even if it feels frustrating at times.
Remember;
What you hear you forget,
what you see you remember
and what you DO you understand.
(Quote from someone)

Andreas

[Deanna Tran]

Hi TG
I have a question in regards to your procedure
.lst
11. remove all the entries which refer to the deleted database in tnsnames.ora and listener.ora (located in $ORACLE_HOME/network/admin)
-- is this mean i should delete the listener.ora and tnsnames.ora file?
Where does these file come from?

12. remove all database links referring to the deleted database.
13. check "/var/opt/oracle/oratab" to make sure there is no entry of the database deleted. If yes,

I don't have the oratab subdirectory,
but when i do cd /var/opt/oracle
I got this oraIns#Oracle Installer Location File Location
#Tue Feb 19 16:04:02 MST 2002
inventory_loc=/home/oracle/oraInventory
inst_group=dba
ORACLE_SID=dia2
** again, should i just edited the oracle_SID ?

[Jeanine Kone]

The tnsnames.ora and listener.ora files are networking files that allow others to connect to the database and you to connect to other databases. They should be in the ORACLE_HOME/network/admin directory. They were probably created with a wizard when you created the starter database. The tnsnames.ora defines where databases are (for example when you use the connect string 'mydb' it looks in this file to see what server it is located on, what port to use, and wha the actual sid is). The listener.ora defines what databases this server will "listen" for connect attempts on. You will need these files for furture databases. I would not delete the files, just edit them to remove the current entires (or change the currrent entries to reflect the data from the new instance you will be creating).

oratab is a file located usually lcoated in /etc. It should have one entry for every database on the server. It lists the oracle home for the database and a Y or N to indicate if the database should be started/stopped on server reboot.

Hope this helps.