1

I just installed Oracle database 12cR2 and am trying to startup a database.

I am trying to create a pfile from spfile using following query.

create pfile from spfile;

Then I get the following errors.

create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?=/dbs/spfile@.ora'
ORA-27077: too many files open

Can anybody tell me what's going on here or what I am doing wrong?

  • Try specifying the output: create pfile = '/home/oracle/pfile.ora' from spfile – a_horse_with_no_name Jun 13 '18 at 13:40
  • I tried that. But the same error is shown. since the error indicates spfile@.ora, do I have to specify spfile too? – amsandun Jun 13 '18 at 13:54
2
ORA-27077: too many files open

Based on that error I guess you have reached the limit of maximum open files.

You can check the soft and hard limits with ulimit -Sn and ulimit -Hn. Configure it in /etc/security/limits.conf or a seperate file under /etc/security/limits.d/. Check the list of actual open files with lsof -u oracle (if the user running the instance is oracle).

0

If you take a look at the official documentation for CREATE PFILE (Oracle) then you will find a syntax diagram.

                                                        +- = '<spfile_name>' -+
                                                        |                     |
              +- = '<pfile_name>' -+         +-> SPFILE -+--------------------+
CREATE PFILE -+                    +-> FROM -+                                +-> ;
              +--------------------+         +-> MEMORY ----------------------+

If you don't specify a filename for either pfile_name or spfile_name then the system will use default values as described in the documentation.

If you do not specify pfile_name, then Oracle Database uses the platform-specific default initialization parameter file name.

and

If you do not specify spfile_name, then the database uses the spfile that is currently associated with the instance, usually the one that was used a startup. If no spfile is associated with the instance, then the database looks for the platform-specific default server parameter file name. If that file does not exist, then the database returns an error.

(emphasis mine)

Reference: CREATE PFILE (Oracle)

Solution

Try supplying a file name for pfile_name and spfile_name and see what happens.

Example

create pfile='/u00/app/oracle/admin/YOUR_SID/pfile/initYOUR_SID.ora' 
from spfile='/u00/app/oracle/admin/YOUR_SID/pfile/spfileYOUR_SID.ora';
  • 1
    thanks for the input. I read this documentation and it was helpful. When I restarted the VM and tried again, it worked. my default location for pfile and spfile was $ORACLE_HOME/dbs – amsandun Jun 13 '18 at 15:02
  • @amsandun You might want to post your findings as an answer. Then you can self-answer your question and even accept it as the answer. – John aka hot2use Jun 13 '18 at 19:46
  • @hot2use - given that there was nothing wrong with with OP's original and most simple version of the syntax, as well as the very explicit error message, as well as the fact that the problem cleared with a machine restart, it would appear there was something more fundamental going on than just a syntax issue. And given the very explicit error message, I doubt that simply using more explicit syntax (not relying on defaults) would have solved the problem. – EdStevens Jun 13 '18 at 21:57
  • I would object on the basis that if the environment has not been set correctly, that if you specify the actual paths, you can create the files. If the environment is not set correctly, then you can't rely on the the SID, ORACLE_HOME, ORACLE_BASE having been set correctly. – John aka hot2use Jun 14 '18 at 7:05
  • @hot2use Actually I tried all the solutions suggested by the members, including adding path and name for pfile, spfile and then for both. Yet the same two errors came. I had placed my spfile (spfileORCL.ora) in $ORACLE_HOME/dbs. I also used lsof -u oracle as suggested but couldn't understand the result as I am new to linux and oracle. Because the error mentioned 'too many files open', I just thought a restart might clear any memory or process related issue. And it worked. – amsandun Jun 14 '18 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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