RMAN - Restore spfile from memory on RAC 12.1.0.2.0(ORA-01565: error in identifying file, ORA-17503: ksfdopn:2 Failed to open file )

Scenario Preview:

• Spfile located in ASM diskgroup
• We have deleted spfile
• Check spfile working or not by changing db file parameter

Solution

• create pfile from memory
• create spfile from pfile
• Using SRVCTL utility modify spfile as previous spfile information still present on database
• Using SRVCTL stop and start database 
• Check spfile infomation

[oracle@srv1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 19 12:45:04 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/RAC/PARAMETERFILE/spfile
                                                 .268.1016215445
SQL>

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576     30708    21562            10236            5663              0             Y  CRS/
MOUNTED  EXTERN  N         512   4096  1048576     20473    15506                0           15506              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     20473    19263                0           19263              0             N  FRA/
ASMCMD> cd DATA/RAC/PARAMETERFILE
ASMCMD> ls
spfile.268.1016215445
ASMCMD> rm -f spfile.268.1016215445
ASMCMD> ls
ASMCMD-8002: entry 'PARAMETERFILE' does not exist in directory '+DATA/RAC/'
ASMCMD>

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     91068

SQL> ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' SCOPE=SPFILE;

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' SCOPE=SPFILE

*

ERROR at line 1:

ORA-01565: error in identifying file

'+DATA/RAC/PARAMETERFILE/spfile.268.1016215445'

ORA-17503: ksfdopn:2 Failed to open file

+DATA/RAC/PARAMETERFILE/spfile.268.1016215445

ORA-15012: ASM file '+DATA/RAC/PARAMETERFILE/spfile.268.1016215445' does not

exist

SQL> create pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/intracpfile.ora' from memory;

File created.

SQL>

SQL> create spfile='+DATA/RAC/PARAMETERFILE/' from pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/intracpfile.ora';

create spfile='+DATA/RAC/PARAMETERFILE/' from pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/intracpfile.ora'

*

ERROR at line 1:

ORA-17502: ksfdcre:4 Failed to create file +DATA/RAC/PARAMETERFILE/

ORA-15005: name "+DATA/RAC/PARAMETERFILE/" is already used by an existing alias

SQL> create spfile='+DATA/RAC/PARAMETERFILE1/' from pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/intracpfile.ora';

File created.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +DATA/RAC/PARAMETERFILE/spfile

                                                 .268.1016215445

srvctl modify database -d rac -p +DATA/RAC/PARAMETERFILE/spfile.268.1016715759

SQL> sho parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +DATA/RAC/PARAMETERFILE/spfile

                                                 .268.1016215445

[root@srv1 trace]# srvctl stop database -d rac

[root@srv1 trace]# srvctl start database -d rac

[oracle@srv1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 19 13:32:29 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> sho parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +DATA/RAC/PARAMETERFILE/spfile

                                                 .268.1016715759

SQL>