Corrupt SPFILE & no back up - now what? - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 22 of 22

Thread: Corrupt SPFILE & no back up - now what?

  1. #21
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    This thread is wanting to be moved to OBF?? ( Getting too personel i guess )
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #22
    Join Date
    Dec 2017
    Posts
    1
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width