Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Showing results for 
Search instead for 
Did you mean: 

Still struggling with DATABase

Go to solution
Deanna Tran
Frequent Advisor

Still struggling with DATABase

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
Esteemed Contributor

Re: Still struggling with DATABase

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.
enjoy any little thing in my life
T G Manikandan
Honored Contributor

Re: Still struggling with DATABase

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.


Re: Still struggling with DATABase

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.
My house is the bank's, my money the wife's, But my opinions belong to me, not HP!
Victor Geere
Frequent Advisor

Re: Still struggling with DATABase

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.
I thought that I was because I had thought.
Andreas D. Skjervold
Honored Contributor

Re: Still struggling with DATABase

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.
What you hear you forget,
what you see you remember
and what you DO you understand.
(Quote from someone)

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Deanna Tran
Frequent Advisor

Re: Still struggling with DATABase

I have a question in regards to your procedure
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
** again, should i just edited the oracle_SID ?

Jeanine Kone
Trusted Contributor

Re: Still struggling with DATABase

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.