I am running 10G on Windows XP, I edited my SPFILE by mistake, and now I believe it is corrupt.
The DB will not startup, all I can do is SQLPLUS /NOLOG.
How can I start my DB back up?
Thanks
Daniel
Printable View
I am running 10G on Windows XP, I edited my SPFILE by mistake, and now I believe it is corrupt.
The DB will not startup, all I can do is SQLPLUS /NOLOG.
How can I start my DB back up?
Thanks
Daniel
create spfile from pfile;
that is if you still have one around.
Um, at least you know to take regular backups now ;)
I have to admit I fell foul of this when I did some tesing ages back, but at the moment we don't use spfiles in production.
I guess someone may have a bright idea, I'm just wondering if you could recreate it manually using info from the alert log from the last time the database was started up, it should list all the non default parameters.
open it with wordpad and create pfile from it's contents to get you back in business
why on earth would you edit an spfile, open it up and you see loads of junk at the top - isnt that enough to at least think about what you are doing
Yes it contains junks but it also coantains relevent parameters and their values (somewhere doen below)Quote:
why on earth would you edit an spfile, open it up and you see loads of junk at the top - isnt that enough to at least think about what you are doing
copy--> paste
this also a good ideaQuote:
I'm just wondering if you could recreate it manually using info from the alert log from the last time the database was started up, it should list all the non default parameters.
the best is, of course , if still have the init.ora...:cool:
Hi,
Thanks for all your help.
I ended up solving my problem by using another XP machine, installing 10G again with exactly the same folders and set up as my problem machine, I then copied over the SPFILE to the problem machine and it started working again.
Daniel
wow major overkill for such a simple thingQuote:
Originally posted by danielrc15
Hi,
Thanks for all your help.
I ended up solving my problem by using another XP machine, installing 10G again with exactly the same folders and set up as my problem machine, I then copied over the SPFILE to the problem machine and it started working again.
Daniel
I know whats in it, my point was why anyone would go editting it when it clearly looks like it shouldnt be editedQuote:
Originally posted by simply_dba
Yes it contains junks but it also coantains relevent parameters and their values (somewhere doen below)
copy--> paste
the best is, of course , if still have the init.ora...:cool:
Absolute stupidity..Quote:
Originally posted by danielrc15
I ended up solving my problem by using another XP machine, installing 10G again with exactly the same folders and set up as my problem machine, I then copied over the SPFILE to the problem machine and it started working again.
Daniel
And you wonder why companies don't do a more through check on the people whom they trust to run the heart beat of the company.Quote:
Originally posted by abhaysk
Absolute stupidity..
You know, I really don't know if having people who know nothing about Oracle database administration being in such a position is good for us (the people who know wtf they're doing). If it will hurt us in the long run or benifit us (at least financially)?
I think some of you are being a bit over critical, the poster does'nt claim to be an expert or running this on behalf of a company so why the attack. Sure thier way of solving the problem was not perfect but "absolute stupidity" I think not!
Remeber all of us have to start somewhere and all of us at some time have overworked a solution.
And to be fair it is running on Windoz where basically the answer to most problems is to reinstall :D
It was not the question of editing the spfile .Rather copying parameters from it and creating a new pfile since the spfile was not workingQuote:
I know whats in it, my point was why anyone would go editting it when it clearly looks like it shouldnt be edited
he edited it, he wasnt copying contents from it - he was editing it, which is stupidity as it clearly shouldnt be editedQuote:
Originally posted by danielrc15
I am running 10G on Windows XP, I edited my SPFILE by mistake, and now I believe it is corrupt.
Daniel
Read what is written next time
I may be way off the mark here (its been a while since I played with SPfiles) but isn't it possible to corrupt them if you alter a parameter online and do a typo on the parameter value whilst doing it?Quote:
Originally posted by davey23uk
he edited it, he wasnt copying contents from it - he was editing it, which is stupidity as it clearly shouldnt be edited
Read what is written next time
Apologies in advance if I'm talking crap.
Think here we are talking about editing the actual file, perfectly possible to corrupt it then :)Quote:
Originally posted by bazza
I may be way off the mark here (its been a while since I played with SPfiles) but isn't it possible to corrupt them if you alter a parameter online and do a typo on the parameter value whilst doing it?
Apologies in advance if I'm talking crap.
No, not possible to corrupt it using alter system = ... as any invalid parameter wont be written to the file.
although you can make it so your instance doesnt start that way - but the file itself is still valid
Right fair enough, I was a tad hazy on that. Better rtfm if I ever use a SPFILE ;)Quote:
Originally posted by davey23uk
Think here we are talking about editing the actual file, perfectly possible to corrupt it then :)
No, not possible to corrupt it using alter system = ... as any invalid parameter wont be written to the file.
although you can make it so your instance doesnt start that way - but the file itself is still valid
Quote:
Originally posted by stmontgo
open it with wordpad and create pfile from it's contents to get you back in business
He edited it alright.Quote:
he edited it, he wasnt copying contents from it - he was editing it, which is stupidity as it clearly shouldnt be edited
Read what is written next time
It was stupidity alright.
But he has already done it and the talk was going on how to create pfile from a busted spfile.
You should read the posts and not only the mesgs
Quote:
Originally posted by simply_dba
He edited it alright.
It was stupidity alright.
But he has already done it and the talk was going on how to create pfile from a busted spfile.
You should read the posts and not only the mesgs
YOU replied to MY posts about him being stupid - which were nothing to do about recovering the file, therefore you were questioning what I had said
No I wasn't .I just gave an idea to recreate the pfile from the spfile.Does that make u angry?Quote:
YOU replied to MY posts about him being stupid - which were nothing to do about recovering the file, therefore you were questioning what I had said
This thread is wanting to be moved to OBF?? :rolleyes: :rolleyes: ( Getting too personel i guess )
OK, someone made a newbie mistake...well I know of some seasoned professionals who made stupider mistakes at 3 am.
Shocking but they formatted all the disks for the database and all the back ups and didn't get fired.....and then they tried to lie and say they weren't logged into the system.
As long as you have a few ideas about what the database essential parameters are and the rest of the database files exist (control files), this is a recoverable error. This basic procedure will work on databases up through 12i, assuming this isn't production and you have a smallish development database on a laptop or something similar. DON'T DO THIS IN PRODUCTION!!!! This is advice for a person with a local development DB.
Before you start, back up the entire database directory someplace. MAKE A BACK UP!!!!
On Windows:
1. open a command prompt and login as sysdba. In the command window, type:
sqlplus / as sysdba
2. shutdown the database if it is running. Before you do this, if you changed the SPFILE and the instance is still running, try to dump the database parameters to a PFILE....last ditch effort:
create pfile='C:/pfile_dump.ora' from spfile;
3. Shutdown the database:
shutdown immediate
4. find the SPFILE you edited and save it to some new name like: SPFILEORCL - Corrupt.ORA
5. Go to C:\app\oracle\product\12.2.0\dbhome_1\dbs or find your 'dbs' directory. in there, there should be a template init.ora file.
6. make a copy of the init.ora file
7. open the init.ora file in a text editor and open the SPFILEORCL - Corrupt.ORA file in another window (to READ form it)
If the SPFILEORCL - Corrupt.ORA was not too badly mangled, there should be enough readable information to help you build a SPFILE file to
get the database to start
8. Here is a very basic init.ora for a dev database:
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='C:\app\oracle\admin\orcl\adump'
audit_trail ='db'
db_block_size=8192
db_recovery_file_dest_size=2G
diagnostic_dest='C:\app\oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
enable_pluggable_database=true
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = ('C:\app\oracle\oradata\orcl\control01.ctl', C:\app\oracle\oradata\orcl\control02.ctl)
compatible ='12.2.0'
local_listener='LISTENER_ORCL'
nls_language='AMERICAN'
nls_territory='AMERICA'
8. look at the values from your corrupted SPFILE and update the values in the init.ora, you will need patience but this will work!
save your changes outside of the Oracle directory structure...let's say C:\init.ora
9. go to the command window
sqlplus / as sysdba
create spfile from pfile='C:\init.ora'
The SPFile should write to your 'database' directory. Keep the command window open.
10. As SYSDBA, try to start the DB. In the command window:
startup open
If the startup looks like this, you are good to go to step 12:
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8927144 bytes
Variable Size 742393944 bytes
Database Buffers 314572800 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
If you see ANY errors, you need to fix them
11. If you get errors, shutdown the database (that is if it started)
shutdown immediate
Go back through steps 7-10, tweaking the contents of the init.ora
This could take a few hours but it is better than having to falsely tell someone the database is broken and I have to re-install becuase you don't unless you also deleted datafiles or all control files and have no backups).
Be patient.
12. when your database opens (you get no errors from 'startup open')
open a Command Prompt window. Stop and start the listener:
lsnrctl stop
lsnrctl start
Now, use tnsping to see if your instance responds, my DB is named ORCL:
tnsping ORCL
If it looks like this and last like says "OK", then your hacked up init.ora is good enough to start your DEVELOPMENT database
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (1480 msec)
13. Assuming you have no remaining errors, open a Command prompt window and see if you can connect as a regular usr, for example:
sqlplus scott/tiger@ORCL
select SYSDATE from dual;
select count(*) from scott.emp;
Good luck!