How to Resolve ORA-01565: error in identifying file

In this post, I'll describe two error patterns for ORA-01565.

Missing SPFILE
Multitenant DB

A. ORA-01565 for Missing SPFILE

To create a plain-text parameter file, we seldom 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

?=/dbs/spfile@.ora

Some symbols like ? and @ in the above error message are used by Oracle 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 to Missing SPFILE

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.

You can always create PFILE 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.

B. ORA-01565 in Multitenant DB

Sometimes, you may see ORA-01565 in your multitenant database.

 SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ERPCDB/ERPPDB7/TEMP01.dbf' KEEP 10M;
 ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ERPCDB/ERPPDB7/TEMP01.dbf' KEEP 10M
 *
 ERROR at line 1:
 ORA-01565: error in identifying file '2003'

I know you're pretty sure that the file is existing, but we should check current container of this session.

 SQL> show con_name;
 
 CON_NAME
 ------------------------------
 CDB$ROOT

OK, we're in the root container. In this case, the file that we want to operate with belongs to a PDB, not the root container.

 SQL> show pdbs;
 
     CON_ID CON_NAME                       OPEN MODE  RESTRICTED
 ---------- ------------------------------ ---------- ----------
          2 PDB$SEED                       READ ONLY  NO
          3 ERPPDB7                        READ WRITE NO
          4 ERPPDB7D                       READ WRITE NO
          5 ERPPDB7E                       READ WRITE NO

Solution

The solution is simple, just get into the right PDB for further operation.

 SQL> alter session set container=ERPPDB7;
 
 Session altered.
 
 SQL> show con_name;
 
 CON_NAME
 ------------------------------
 ERPPDB7

We may also use an environment variable ORACLE_PDB_SID to get into the same PDB.

Let's do it again.

 SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ERPCDB/ERPPDB7/TEMP01.dbf' KEEP 10M;
 
 Tablespace altered.