Corrupt SPFILE & no back up - now what?

[abhaysk]

This thread is wanting to be moved to OBF?? ( Getting too personel i guess )

[las911]

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!