2

I'm trying to set up a database on a Solaris 10 platform that had its database corrupted before. Due to the lack of backups, I was forced to reinstall Oracle server. Currently I'm facing an issue where answers I have found keep sending me in loops.

I opened sqlplus, and saw that it was connected to an idle instance, so I performed the STARTUP command. This led me to the following errors:

ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist 
           [/export/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/<ORACLE_BASE>]

So after some googling, I was told to try changing the diagnostic_dest via the following: ALTER SYSTEM SET diagnostic_dest='u01/app/oracle';, which was equivalent to setting the diagnostic_dest to my ORACLE_BASE.

However, the error below occurred:

ORA-01034: ORACLE not available

After some googling again, I found that the solution was to start the database (which I can't)! I have no idea how I should proceed from here. Any help will be much appreciated. If any other information is required, I will gladly update it in the OP.

I have tried restarting lsnrctl, but it doesn't seem to help.

I would change all the default parameters within the file locations, but from what I found, the init.ora file which was supposed to be used already has diagnostic_dest='<ORACLE_BASE>', the spfile.ora file also has diagnostic_dest='/u01/app/oracle'. I'm really lost as to how I should carry on.

1
  • You need to verify that every directory referenced in your spfile/pfile exists. If one does not exist create it, if you can't create it because a volume is named differently on this server compared to another server than either use strings <spfilename> > <pfilename> and edit the pfile to have the correct paths or just edit the pfile to have the correct paths. You can't start an Oracle database is the diag, audit, archive log, etc directories do not exist.
    – Gandolf989
    Jan 23, 2017 at 14:57

1 Answer 1

3

You can not use ALTER SYSTEM when the instance is not started, but you can not start the instance because of an incorrect parameter value. Create a PFILE (if you do not already have one), edit that, then start the instance.

$ sqlplus / as sysdba
SQL> create pfile from spfile;
SQL> exit
$ vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

Set the value of diagnostic_dest as required, then save the file.

$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup

Note: the directory must exist.

Answer to comment:

For UNIX or Linux, the platform-specific default location (directory) for the SPFILE and text initialization parameter file is:

$ORACLE_HOME/dbs

Oracle Database locates your initialization parameter file by examining filenames in the following order:

  1. spfile$ORACLE_SID.ora
  2. spfile.ora
  3. init$ORACLE_SID.ora

Or, for a test, you can startup the instance by manually specifying the pfile, for example:

SQL> startup pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora
4
  • I'm not sure if i'm looking at the wrong spfile, but I got ORA-48140 again at the startup command...How should I try to find the file which the system is looking at? Jan 23, 2017 at 12:54
  • I also tried adding a new value for diagnostic_dest in the pfile, but it didn't work. Is there a specific location for the pfile to be saved in? Jan 23, 2017 at 12:56
  • @QwertyForever Added this to the answer. Jan 23, 2017 at 13:43
  • Found the final reason after a bit of trial and error. Turns out i needed to change the $ORACLE_SID to similar case characters for all the files as well. I have no idea why they were created in lower case by the installer, while the system keeps looking for an upper case one (i.e. all my files were created as *orcl.ora instead of *ORCL.ora). Thank you all very much for the help! Jan 24, 2017 at 2:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.