How to Resolve ORA-01565: error in identifying file '?=/dbs/spfile@.ora'

  • by

ORA-01565

To create a plain-text parameter file, I usually don't specify both locations of the parameter file (PFILE) or the server parameter file (SPFILE).

SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?=/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Some symbols like ? and @ are used by Oracle in the above error message to represent different environment variables.

ORA-01565 means that the instance can't find a proper SPFILE at the default location to create PFILE. This could be one of the following situations:

  • The database is shutdown and no SPFILE is at the default location.
  • The database is running, but it startup with a PFILE. Furthermore, no SPFILE is at the default location.
  • The database is running, it startup with a SPFILE, but no SPFILE is at the default location.

Those situations mean that there's no SPFILE at the default location. For a RAC database, it's very normal, because SPFILE is in ASM and shared among several nodes. But for a single-instance database, it's not very normal, the location of SPFILE may have been changed or less likely, the database has no SPFILE at all.

Solutions

There're 2 options to do:

1. Specify SPFILE location in the statement

You have to know where the proper SPFILE is. For example:

SQL> create pfile from spfile='/u08/parameter_files/spfile.20180912';

File created.

No matter the instance is running or stop.

2. Startup the instance with SPFILE.

Sometimes, the location of SPFILE is too complex to remember like +DATA/ORCLCDB/PARAMETERFILE/spfile.275.1053776653, you may like to not to specify the location. The solution is to make sure the instance is started up with a proper SPFILE, not PFILE. Then try again.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCLCDB   READ WRITE

SQL> create pfile from spfile;

File created.

The running instance knows where SPFILE is.

Leave a Reply

Your email address will not be published. Required fields are marked *