oracle asm spfie丢失

蓝晶灵

　　ORA-1078 ORA-1565 Errors while starting database with SPFILE on ASM
　　I was getting following error’s while starting a database using spfile on ASM. Actually this was a cloned RAC environment.
SQL> create SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora' from pfile;　　
File created.
　　
SQL> shut immediate;
　　
ORA-01109: database not open
　　
Database dismounted.
　　
ORACLE instance shut down.
　　
SQL> exit
　　
SQL> startup mount
　　
ORA-01078: failure in processing system parameters
　　
ORA-01565: error in identifying file '+ASM_GROUP/PORTALDB/spfileportaldb.ora'
　　
ORA-17503: ksfdopn:2 Failed to open file +ASM_GROUP/PORTALDB/spfileportaldb.ora
　　
ORA-01000: maximum open cursors exceeded

　　Error first states that it has failed to process the parameter. Second states that it has failed to>　　Problem here was that I had started the database with pfile from non-default location and pfile located in $ORACLE_HOME/dbs had following entries
SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora'　　In this case spfile too was created with same contents. Correct method is to specify the non-default pfile location in ‘create spfile’ syntax.
SQL> CREATE SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora' from pfile=\'/home/oracle/portaldb.ora\';　　

　　
File created.
　　

　　
SQL> shut immediate;
　　
ORA-01109: database not open
　　

　　
Database dismounted.
　　
ORACLE instance shut down.
　　
SQL> exit
　　
SQL> sqlplus \"/ as sysdba\"
　　
Connected to an idle instance.
　　

　　
SQL> startup mount
　　
ORACLE instance started.
　　

　　
Total System Global Area 3674210304 bytes
　　
Fixed Size                  2088384 bytes
　　
Variable Size            2197815872 bytes
　　
Database Buffers         1459617792 bytes
　　
Redo Buffers               14688256 bytes
　　
Database mounted.
　　
SQL> alter database open;
Move SPFILE from ASM to File System
　　The following is an example of how to move your SPFILE from ASM to your file system or vice versa
　　ASM to File System
　　In this case, we have our spfile in the +DATA diskgroup and we want to move it to the default location on our file system. The default location will be in our $ORACLE_HOME/dbs directory. In order to accomplish this, we need to create a pfile from our existing spfile and then create the new spfile from this temporary pfile. Afterwords, we bounce the database instance for the change to take effect.
SQL> show parameter spfile　　

　　
NAME                                 TYPE        VALUE
　　
------------------------------------ ----------- ------------------------------
　　
spfile                               string      +DATA/orcl/spfileorcl.ora
　　

　　
SQL> create pfile='/tmp/orcl.pfile' from spfile;
　　

　　
File created.
　　

　　
SQL> create spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora'
　　
from pfile='/tmp/orcl.pfile';
　　

　　
File created.
　　

　　
SQL> startup force
　　
ORACLE instance started.
　　
...
　　
Database mounted.
　　
Database opened.
　　

　　

　　
SQL> show parameter spfile
　　

　　
NAME                                 TYPE        VALUE
　　
------------------------------------ ----------- ------------------------------
　　
spfile                               string      /u01/app/oracle/product/10.2.0
　　
                                                /db_1/dbs/spfileorcl.ora
Cleaning up the old ASM spfile

　　At this point you may want to clean up the old spfile within ASM. To do that, use the asmcmd utility to remove the file. The file system spfile will take precedence over the ASM spfile, but it’s not a bad>$ . oraenv　　
ORACLE_SID= [orcl] ? +ASM
　　
$ asmcmd
　　
ASMCMD> cd DATA/orcl
　　
ASMCMD> rm spfileorcl.ora
　　
ASMCMD> exit
File System to ASM
　　The reverse procedure is similar. However, because Oracle will use the file system spfile first if it sees one, we need to remove or rename that file system spfile before bouncing the database instance.
SQL> show parameter spfile　　

　　
NAME                                 TYPE        VALUE
　　
------------------------------------ ----------- ------------------------------
　　
spfile                               string      /u01/app/oracle/product/10.2.0
　　
                                                /db_1/dbs/spfileorcl.ora
　　

　　

　　
SQL> create pfile='/tmp/orcl.pfile' from spfile;
　　

　　
File created.
　　

　　
SQL> create spfile='+DATA/orcl/spfileorcl.ora'
　　
from pfile='/tmp/orcl.pfile';
　　

　　
File created.
　　

　　
$ cd $ORACLE_HOME/dbs
　　
$ mv spfileorcl.ora old_spfileorcl.ora
　　

　　

　　
SQL> startup force
　　
ORACLE instance started.
　　
...
　　
Database mounted.
　　
Database opened.
　　

　　

　　
SQL> show parameter spfile
　　
NAME                                 TYPE        VALUE
　　
------------------------------------ ----------- ------------------------------
　　
spfile                               string      +DATA/orcl/spfileorcl.ora
Fixing an OMF spfile name
　　One common error you may run into is if you didn’t provide a full path to the spfile within your ASM diskgroup (i.e., you created the spfile as ‘+DATA’ instead of ‘+DATA/orcl/spfileorcl.ora’). If that happens, then Oracle is going to assign an Oracle Managed File (OMF) name to your spfile and all you need to do is create an alias for it using the asmcmd tool.
SQL> create pfile='/tmp/orcl.pfile' from spfile;　　
File created.
　　

　　
SQL> create spfile='+DATA'
　　
from pfile='/tmp/orcl.pfile';
　　
File created.
　　

　　
$ cd $ORACLE_HOME/dbs
　　
$ mv spfileorcl.ora old_spfileorcl.ora
　　

　　
SQL> startup force
　　
ORA-01078: failure in processing system parameters
　　
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'
　　
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
　　
ORA-15056: additional error message
　　
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/orcl/spfileorcl.ora
　　
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
　　
ORA-15173: entry 'spfileorcl.ora' does not exist in directory 'orcl'
　　
ORA-06512: at line 4
　　

　　
$ . oraenv
　　
ORACLE_SID= [orcl] ? +ASM
　　
$ asmcmd
　　
ASMCMD> cd DATA/orcl/
　　
ASMCMD> ls PARAMETERFILE
　　
spfile.277.756994081
　　
ASMCMD> mkalias +DATA/orcl/parameterfile/spfile.277.756994081
　　
+DATA/orcl/spfileorcl.ora
　　
ASMCMD> exit
　　

　　
SQL> startup force
　　
ORACLE instance started.
　　
...
　　
Database mounted.
　　
Database opened.
　　

　　

　　
SQL> show parameter spfile
　　
NAME                                 TYPE        VALUE
　　
------------------------------------ ----------- ------------------------------
　　
spfile                               string      +DATA/orcl/spfileorcl.ora
　　This code was tested on an Oracle 10g Standard Edition database.
　　ORA-01565: error in>

　　When we’re going to create pfile from spfile while spfile running on ASM instance, We can>　　SQL> create pfile=’/u01/inittest.ora’ from spfile;
　　create pfile=’/u01/inittest.ora’ from spfile
　　*
　　ERROR at line 1:

　　ORA-01565: error in>　　ORA-27037: unable to obtain file status
　　Linux Error: 2: No such file or directory
　　Additional information: 3
　　Solution :
　　We need to give ASM path of SPFILE.
　　SQL>  create pfile=’/u01/inittest.ora’ from spfile=’+DATA/dbasm/spfiledbasm.ora';
　　File created.