By Brian Fitzgerald

Problem

Question: How do you create a spfile in ASM? If you issue “create spfile” too early, you will not know the path, so you would have to search ASM for it. The situation could arise while you are setting up a standby database.

Solution

Register the database with CRS first. Then, as the manual mentions, the create spfile statement “automatically updates the SPFILE in the database resource.”

Register the db with CRS

$ srvctl add database -database orcl -role physical_standby -startoption MOUNT -stopoption ABORT -instance orcl -oraclehome /u01/app/oracle/product/1212/db_1 -diskgroup DATA1,RECO1,
REDO1,REDO2
$ srvctl start database -database orcl
$ srvctl config database -database orcl
Database unique name: orcl
Database name:
Oracle home: /u01/app/oracle/product/1212/db_1
Oracle user: oracle
Spfile:
Password file:
Start options: mount
Stop options: abort
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA1,RECO1,REDO1,REDO2
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: orcl

Create a text file

You could create a pfile from memory, for example.

SQL> create pfile='initorcl.memory' from memory;
File created.

Edit the file

$ vi initorcl.memory

Delete extraneous underscore parameters.For example, here is a file with the basic parameters that you need for restoring and recovering a duplicate database:

audit_trail='DB'
cluster_database=FALSE
compatible='12.1.0.2.0'
control_files='+REDO1/ORCL/CONTROLFILE/current.258.1067960587'
control_files='+REDO2/ORCL/CONTROLFILE/current.258.1067960589' # Restore Controlfile
core_dump_dest='/u01/app/oracle/diag/rdbms/orcl/orcl/cdump'
db_cache_size=16G
db_create_file_dest='+DATA1'
db_create_online_log_dest_1='+REDO1'
db_create_online_log_dest_2='+REDO2'
db_files=1000
db_name='ORCL'
db_recovery_file_dest='+RECO1'
db_recovery_file_dest_size=1000G
db_unique_name='orcl'
local_listener='ORCL'
remote_login_passwordfile='EXCLUSIVE'
sga_max_size=31G # internally adjusted
sga_target=31G
use_large_pages='only'

Create the spfile

SQL> create spfile = '+DATA1' from pfile='initorcl.memory';

Check:

$ srvctl config database -database orcl
Database unique name: orcl
Database name:
Oracle home: /u01/app/oracle/product/1212/db_1
Oracle user: oracle
Spfile: +DATA1/ORCL/PARAMETERFILE/spfile.585.1068023283
Password file:
Start options: mount
Stop options: abort
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA1,RECO1,REDO1,REDO2
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: orcl

Notice that there was no need to create an ASM alias or log in to ASM to search for the file.

Conclusion

Register your database in CRS first, then create your spfile.