Search This Blog

Total Pageviews

Tuesday 27 December 2016

Moving SPFILE from file system to ASM (+DATA)

Moving SPFILE from file system to ASM (+DATA)



[oracle@oraasm11g ~]$ srvctl config database -d anuj
Database unique name: anuj
Database name: anuj
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:                          ------- No spfile in configration 
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: anuj
Disk Groups: DATA
Services:


[oracle@oraasm11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 27 19:43:16 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfileanuj.ora


full path !!

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora

SQL> create spfile='+DATA' from spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora'
                           *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora';  ----this is not a pfile 
create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileanuj.ora'
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00123: invalid character 0 found in the input file


This error was expected !!!! ( there is no command to create  from spfile to spfile )

SQL> create pfile from spfile;

File created.

SQL> !ls -ltr /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
total 40
-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r-----. 1 oracle asmadmin   24 Jun 12  2016 lkVIHAAN
-rw-r-----. 1 oracle oinstall 1536 Jun 12  2016 orapwvihaan
-rw-r-----. 1 oracle oinstall   39 Jun 12  2016 initvihaan.ora
-rw-r-----  1 oracle oinstall 1536 Dec 25 16:27 orapwanuj
-rw-r-----  1 oracle oinstall   24 Dec 25 16:28 lkANUJ
-rw-rw----  1 oracle oinstall 1544 Dec 27 18:59 hc_anuj.dat
-rw-rw----. 1 oracle asmadmin 1544 Dec 27 19:40 hc_vihaan.dat
-rw-r-----  1 oracle oinstall 2560 Dec 27 19:41 spfileanuj.ora
-rw-r--r--  1 oracle asmadmin  917 Dec 27 19:48 initanuj.ora <<<<<--- 


Now creating pfile to spfile in diskgroup ...

SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initanuj.ora' ;

File created.


to check the path !! go to diskgroup 

[root@oraasm11g ~]# su - grid
[grid@oraasm11g ~]$ asmcmd -p


ASMCMD [+] > pwd
+

ASMCMD [+] > ls -s
Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
   512   4096  1048576     15358    11438                0           11438              0             N  DATA/

ASMCMD [+] > cd data

ASMCMD [+data] > ls -s
Block_Size  Blocks  Bytes  Space  Name
                                  ANUJ/
                                  ASM/
                                  VIHAAN/


ASMCMD [+data] > cd ANUJ

ASMCMD [+data/ANUJ] > ls -s
Block_Size  Blocks  Bytes  Space  Name
                                  BACKUPSET/
                                  CONTROLFILE/
                                  DATAFILE/
                                  ONLINELOG/
                                  PARAMETERFILE/
                                  TEMPFILE/
ASMCMD [+data/ANUJ] > cd PA*


ASMCMD [+data/ANUJ/PARAMETERFILE] > pwd
+data/ANUJ/PARAMETERFILE

ASMCMD [+data/ANUJ/PARAMETERFILE] > ls -s
Block_Size  Blocks  Bytes    Space  Name
       512       5   2560  1048576  spfile.278.931722561


or 

from find command  

 ASMCMD [+] > find +data sp*
+data/ANUJ/PARAMETERFILE/spfile.278.931722561


su - oracle 


[oracle@oraasm11g ~]$ srvctl modify database -d anuj -p +DATA/ANUJ/PARAMETERFILE/spfile.278.931722561

[oracle@oraasm11g ~]$ srvctl config database -d anuj
Database unique name: anuj
Database name: anuj
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ANUJ/PARAMETERFILE/spfile.278.931722561
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: anuj
Disk Groups: DATA
Services:



===============



http://anuj-singh.blogspot.com/2023/    --- Oracle Create spfile from memory

Oracle DBA

anuj blog Archive