Ora 01565 error in identifying file

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

1. Missing SPFILE
2. 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.

Starting Database Using Spfile Located on ASM Fails With ORA-01078, ORA-01565, ORA-17503 Errors (Doc ID 1553901.1)

Last updated on FEBRUARY 04, 2022

Applies to:

Symptoms

Starting up a database with spfile located on ASM storage fails with:

ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+ORCL_DATA/ORCL/spfileORCL.ora’
ORA-17503: ksfdopn:2 Failed to open file +ORCL_DATA/ORCL/spfileORCL.ora
ORA-15056: additional error message

Starting the database with pfile works fine

Changes

New Spfile is created in New/Another ASM diskgroup.

spfile is created from pfile and the location specified is new/another ASM diskgroup

CREATE SPFILE=’+DATA/ORCL/orclspfile.ora’ FROM PFILE=’$ORACLE_HOME/dbs/initorcl.ora’;

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-01565: error in identifying file /dbs/spfile@.ora

When we’re going to create pfile from spfile while spfile running on ASM instance, We can face the below errors,

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

We need to give ASM path of SPFILE.

SQL> create pfile=’/u01/inittest.ora’ from spfile=’+DATA/dbasm/spfiledbasm.ora’;

Like this:

Hi Azar,
i am getting the below error message while creating pfile –
SQL> create pfile=’/u01/app/oracle/initram1.ora’ from spfile=’+DATA/ram/spfileram.ora’;
create pfile=’/u01/app/oracle/initram1.ora’ from spfile=’+DATA/ram/spfileram.ora’
*
ERROR at line 1:
ORA-01565: error in identifying file ‘+DATA/ram/spfileram.ora’
ORA-17503: ksfdopn:2 Failed to open file +DATA/ram/spfileram.ora
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1000 (oinstall), current egid
= 1200 (dba)

Is anything wrong in the permission. Your advise is must appreciated.

Источник

How to Resolve ORA-01565: error in identifying file

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

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

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′;

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;

SQL> create pfile from spfile;

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;

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.

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;

SQL> show con_name;

Let’s do it again.

SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE ‘+DATA/ERPCDB/ERPPDB7/TEMP01.dbf’ KEEP 10M;

Источник

ORA-01565: error in identifying file ‘/dbdata/eris/system01.dbf’

I’m trying to set up a database (11gR2) for our company’s program. The SEC (Software Engineering Center) has developed a script to run for creating database.
When I try to set up the program, I get the error ORA-01033. so I try to start up the db step by step. When I use the command «alter database mount» gives me two oracle errors:
ORA-01565: error in identifying file ‘/dbdata/eris/system01.dbf’
ORA-27037: unable to obtain file status

And also a linux error:
Linux-x86_64 Error: 2: No such file or directory

I see two other errors, too, When I lookup the alert_log:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/dbapp/oracle/flash_recovery_area/eris/control02.ctl’

I assume it should be a ownership/privileges problem, but there isnt any. Any clue of what really happened?
Plzzzz helpppp.

Edited by: 883677 on Dec 24, 2011 11:47 PM

Answers

883677 wrote:
I’m trying to set up a database (11gR2) for our company’s program. The SEC (Software Engineering Center) has developed a script to run for creating database.
When I try to set up the program, I get the error ORA-01033. so I try to start up the db step by step. When I use the command «alter database mount» gives me two oracle errors:
ORA-01565: error in identifying file ‘/dbdata/eris/system01.dbf’
ORA-27037: unable to obtain file status

And also a linux error:
Linux-x86_64 Error: 2: No such file or directory

Any clue of what really happened?
Plzzzz helpppp.

Источник

ORA-01565: error in identifying spfile / ORA-12547: TNS:lost contact

just installed 2 node rac 11.2 rac but the instance B is failing to mount !

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+DATA/db/spfile/mydb.ora’
ORA-17503: ksfdopn:2 Failed to open file +DATA/db/spfile/mydb.ora
ORA-12547: TNS:lost contact
SQL>

spfile exists and the asm is mounted and up. same pfile/spfile works fine on the other instance

Best Answer

In which group is the grid user?
Which group did you choose as ASM Administrator group?
What permissions have your oracle executables (in grid Home and Oracle Home)?

If you cloned the Oracle Home, then it is very likely that the Oracle Home oracle executable is owned by oracle:oinstall, but it should be owned by oracle:asmadim (or prabably dba) in your case.

Use setasmgidwrap on the oracle executable in the Oracle Home, and that should fix your issue.
(DBCA imlicitly calls it).

Answers

Hi,
a.) Is all running of the clusterstack? Please paste the output of «$CRS_HOME/bin/crsctl stat res -t»
b.) The SPFILE looks strange. It should be +DATA/mydb/spfile/spfilemydb.ora and not what yours is showing.
Could you please paste the contents from both nodes of
$ORACLE_HOME/dbs/initmydb.ora

sorry the name and patch is just an example.. looks like asm permission issue my grid infrastructure is owned by grid and oracle_home by oracle user.

resulf or oracleasm configure is

ORACLEASM_ENABLED=true
ORACLEASM_UID=grid
ORACLEASM_GID=dba
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=»»
ORACLEASM_SCANEXCLUDE=»»
on both nodes. however when i do diskgroup dismount is comes back with permission issue ( loged in as grid)

SQL> alter diskgroup FRA dismount;
alter diskgroup FRA dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

diskgroups seem to have mounted by default on the startup! i can do asmcmd and locate the spfile .

In which group is the grid user?
Which group did you choose as ASM Administrator group?
What permissions have your oracle executables (in grid Home and Oracle Home)?

If you cloned the Oracle Home, then it is very likely that the Oracle Home oracle executable is owned by oracle:oinstall, but it should be owned by oracle:asmadim (or prabably dba) in your case.

Use setasmgidwrap on the oracle executable in the Oracle Home, and that should fix your issue.
(DBCA imlicitly calls it).

result of crsctl stat is .

NAME TARGET STATE SERVER STATE_DETAILS
———————————————————————————
Local Resources
———————————————————————————
ora.CRS.dg
ONLINE ONLINE node1
ONLINE ONLINE node2
ora.DATA.dg
ONLINE ONLINE node1
ONLINE ONLINE node2
ora.FRA.dg
ONLINE ONLINE node1
ONLINE ONLINE node2
ora.LISTENER.lsnr
ONLINE ONLINE node1
ONLINE ONLINE node2
ora.asm
ONLINE ONLINE node1 Started
ONLINE ONLINE node2 Started
ora.eons
ONLINE ONLINE node1
ONLINE ONLINE node2
ora.gsd
OFFLINE OFFLINE node1
OFFLINE OFFLINE node2
ora.net1.network
ONLINE ONLINE node1
ONLINE ONLINE node2
ora.ons
ONLINE ONLINE node1
ONLINE ONLINE node2
ora.registry.acfs
ONLINE ONLINE node1
ONLINE ONLINE node2
———————————————————————————
Cluster Resources
———————————————————————————
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE node1
ora.node1.vip
1 ONLINE ONLINE node1
ora.node2.vip
1 ONLINE ONLINE node2
ora.oc4j
1 OFFLINE OFFLINE
ora.mydb.db
1 ONLINE ONLINE node1 Open
2 ONLINE OFFLINE
ora.scan1.vip
1 ONLINE ONLINE node1
[[email protected]

uid=1100(grid) gid=1000(oinstall) groups=1000(oinstall),1100(asmadmin),1200(dba),1300(asmdba),1301(asmoper)

uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1200(dba),1300(asmdba)

when i did oracleasm lib configration i set grid:dba

cd /u01/app
drwxrwxr-x 7 grid oinstall 4096 Sep 30 11:46 grid01_base
drwxrwxr-x 64 grid oinstall 4096 Sep 29 12:22 grid01_home
drwxrwxr-x 6 oracle oinstall 4096 Sep 29 17:09 oracle
drwxrwxr-x 5 grid oinstall 4096 Sep 29 13:03 oraInventory

oracle home was copied as part of rac cinstallation by the installer but there was user gorups set wront etc which it didnt complain about .
i generated the scripts using dbca and ran them manually.

ASMLIB needs to be grid and asmadmin

Oracle executable from database home should be oracle:asmadmin

Check with the working host.

asm lib is set to grid:dba on the working node ( but set to asmadmin on my other clusters)

on the owkring node only /u01/app/oracle/diag is set to asmadmin the rest are oracle:oinstall

[[email protected] oracle]# ll
total 3567772
drwxr-x— 3 oracle oinstall 4096 Sep 29 16:35 admin
drwxr-xr-x 5 oracle oinstall 4096 Sep 29 16:33 cfgtoollogs
drwxr-xr-x 2 oracle oinstall 4096 Sep 29 13:05 checkpoints
drwxr-xr-x 8 oracle oinstall 4096 Aug 20 2009 database
drwxrwxr-x 4 oracle asmadmin 4096 Sep 29 17:22 diag
drwxr-xr-x 3 oracle oinstall 4096 Sep 29 12:44 product

node with issue

[[email protected] oracle]# ll
total 16
drwxrwxr-x 3 oracle oinstall 4096 Sep 29 16:35 admin
drwxrwxr-x 3 grid oinstall 4096 Sep 29 15:24 cfgtoollogs
drwxrwxr-x 3 oracle asmadmin 4096 Sep 29 17:09 diag
drwxrwxr-x 3 oracle oinstall 4096 Sep 29 12:51 product

how can i change asmlib from dba to asmadmin , i think its worth trying .

please compare the settings on $ORACLE_HOME/bin/oracle on both nodes.

To change ASMlib use asmlib configure -i

[[email protected] ]$ ls -ltr | grep bin
drwxr-xr-x 2 oracle oinstall 12288 Sep 29 13:04 bin

[[email protected] ]$ ls -ltr | grep bin
drwxrwxr-x 2 oracle oinstall 12288 Sep 29 13:04 bin

its more accessible on node2. i have changed asmlib to asmadmin and trying

crsctl stop cluster -n inode2

crsctl start cluster -n inode2

withing the dbca scripts there was this line

host /u01/app/grid01_home/bin/setasmgidwrap o=/u01/app/oracle/product/db01/bin/oracle

which i also executed on the second node

]$ ls -ltr /u01/app/oracle/product/db01/bin | grep asm
-rwxrwxr-x 1 oracle oinstall 0 Sep 29 13:02 setasmgid0
-rwxrwxr-x 1 oracle oinstall 2216180 Sep 29 13:02 setasmgid
-r-xr-sr-x 1 oracle asmadmin 210824720 Sep 29 13:02 oracle
-rwxrwxr-x 1 oracle oinstall 29487 Sep 29 13:02 asmcmdcore
-rwxrwxr-x 1 oracle oinstall 5311 Sep 29 13:02 asmcmd

[[email protected] grid]# ls -ltr /u01/app/oracle/product/db01/bin | grep asm
-rwxr-x— 1 oracle oinstall 5311 Mar 17 2009 asmcmd
-rwxr-xr-x 1 oracle oinstall 29487 Jun 13 2009 asmcmdcore
-rwxr-x— 1 oracle oinstall 0 Aug 15 2009 setasmgid0
-rwxr-x—x 1 oracle oinstall 2216180 Sep 29 12:50 setasmgid
-r-sr-s—x 1 oracle asmadmin 210824720 Sep 29 12:51 oracle

its still the same thing. is there a utility that would check permission on all oracle files etc?

i can see one small diffrence one says -r-xr-sr-x and the other one -r-sr-s—x

Источник