Question: How do I recreate an spfile for a RAC database that is stored in ASM when the RAC instances cannot be started using the parameters currently held in that spfile?
The spfile has a parameter set which is not allowing the database instance to start using the spfile. The parameter settings used to start the instance can be located from the alert.log for the instances. These entries can be used to created a valid spfile. If you have a valid pfile then you will NOT need to perform step 1 of the following process.
1. Open the alert log in an editor, go to the last startup or a startup where the parameter settings represent those required for the database. Copy these parameters from each instances’ alert log and use these to build a new text based parameter file.
For example, from Instance 1:
System parameters with non-default values:
processes = 150 __shared_pool_size = 234881024 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 0 spfile = +RACDATA1/rac2/spfilerac2.ora sga_target = 536870912 control_files = +RACDATA1/rac2/controlfile/current.276.638749483, +RACOFRA1/rac2/controlfile/current.257.638749485 db_block_size = 8192 __db_cache_size = 285212672 compatible = 10.2.0.3.0 db_file_multiblock_read_count= 16 cluster_database = TRUE cluster_database_instances= 2 db_create_file_dest = +RACDATA1 db_recovery_file_dest = +RACOFRA1 db_recovery_file_dest_size= 2147483648 thread = 1 instance_number = 1 undo_management = AUTO undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = itnabit.net dispatchers = (PROTOCOL=TCP) (SERVICE=rac2XDB) remote_listener = LISTENERS_RAC2 job_queue_processes = 10 background_dump_dest = /opt/app/oracle/admin/rac2/bdump user_dump_dest = /opt/app/oracle/admin/rac2/udump core_dump_dest = /opt/app/oracle/admin/rac2/cdump audit_file_dest = /opt/app/oracle/admin/rac2/adump db_name = rac2 open_cursors = 300 pga_aggregate_target = 268435456
From Instance 2:
System parameters with non-default values: processes = 150 __shared_pool_size = 260046848 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 0 cluster_interconnects = 192.168.3.120 sga_target = 536870912 control_files = +RACDATA1/rac2/controlfile/current.276.638749483, +RACOFRA1/rac2/controlfile/current.257.638749485 db_block_size = 8192 __db_cache_size = 260046848 compatible = 10.2.0.3.0 db_file_multiblock_read_count= 16 cluster_database = TRUE cluster_database_instances= 2 db_create_file_dest = +RACDATA1 db_recovery_file_dest = +RACOFRA1 db_recovery_file_dest_size= 2147483648 thread = 2 instance_number = 2 undo_management = AUTO undo_tablespace = UNDOTBS2 remote_login_passwordfile= EXCLUSIVE db_domain = itnabit.net dispatchers = (PROTOCOL=TCP) (SERVICE=rac2XDB) remote_listener = LISTENERS_RAC2 job_queue_processes = 10 parallel_instance_group = GROUP_RAC22 background_dump_dest = /opt/app/oracle/admin/rac2/bdump user_dump_dest = /opt/app/oracle/admin/rac2/udump core_dump_dest = /opt/app/oracle/admin/rac2/cdump audit_file_dest = /opt/app/oracle/admin/rac2/adump db_name = rac2 open_cursors = 300 pga_aggregate_target = 268435456
Add the appropriate instance prefixes to each parameter that has an instance specific setting. The new init.ora will look something like the following:
rac22.__db_cache_size=260046848 rac21.__db_cache_size=285212672 rac21.__java_pool_size=4194304 rac22.__java_pool_size=4194304 rac21.__large_pool_size=4194304 rac22.__large_pool_size=4194304 rac22.__shared_pool_size=260046848 rac21.__shared_pool_size=234881024 rac21.__streams_pool_size=0 rac22.__streams_pool_size=0 *.audit_file_dest='/opt/app/oracle/admin/rac2/adump' *.background_dump_dest='/opt/app/oracle/admin/rac2/bdump' *.cluster_database_instances=2 *.cluster_database=true rac22.cluster_interconnects='192.168.3.120' *.compatible='10.2.0.3.0' *.control_files='+RACDATA1/rac2/controlfile/current.276.638749483','+RACOFRA1/rac2/controlfile/curre nt.257.638749485' *.core_dump_dest='/opt/app/oracle/admin/rac2/cdump' *.db_block_size=8192 *.db_create_file_dest='+RACDATA1' *.db_domain='itnabit.net' *.db_file_multiblock_read_count=16 *.db_name='rac2' *.db_recovery_file_dest='+RACOFRA1' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=rac2XDB)' rac21.instance_number=1 rac22.instance_number=2 *.job_queue_processes=10 *.open_cursors=300 rac22.parallel_instance_group='GROUP_RAC22' *.pga_aggregate_target=268435456 *.processes=150 *.remote_listener='LISTENERS_RAC2' *.remote_login_passwordfile='exclusive' *.sga_target=536870912 rac22.thread=2 rac21.thread=1 *.undo_management='AUTO' rac22.undo_tablespace='UNDOTBS2' rac21.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/app/oracle/admin/rac2/udump'
In this case the parameter file was saved to /home/oracle/rac22.ora
2. Using this text based format of the parameter file after having corrected the parameters that have caused the issue, start one of the RAC instances to the mount phase.
SQL> startup mount pfile=/home/oracle/rac22.ora
3. The previous location that was used to hold the spfile was +RACDATA1/RAC2/spfilerac2.ora so the new file will need to replace this file. ASM itself stores the spfile in +RACDATA1/RAC2/PARAMETERFILE/spfile.298.647081589 and links or aliases the spfile in the location +RACDATA1/RAC2/spfilerac2.ora
You can check where the spfile is in the ASM diskgroup by using the ASMCMD command line tool. To use ASMCMD you will have to set your ORACLE_HOME environment variable to the home containing the ASM binaries.
For example:
ASMCMD> pwd +RACDATA1/RAC2
ASMCMD> ls -l Type Redund Striped Time Sys Name Y CONTROLFILE/ Y DATAFILE/ Y ONLINELOG/ Y PARAMETERFILE/ Y TEMPFILE/ N spfilerac2.ora => +RACDATA1/RAC2/PARAMETERFILE/spfile.298.647081589
4. Ensure one of the database instances is mounted before attempting to recreate the spfile.
SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance; INSTANCE_NAME HOST_NAME STATUS ---------------- ---------------- ------------ rac22 elle.itnabit.net MOUNTED
5. Create the new spfile
SQL> create spfile='+RACDATA1/rac2/spfilerac2.ora' from pfile='/home/oracle/rac22.ora'; File created.
6. ASMCMD will show that a new spfile has been created as the alias spfilerac2.ora is now pointing to a new spfile under the PARAMETER directory in ASM.
ASMCMD> pwd +RACDATA1/RAC2
ASMCMD> ls -l Type Redund Striped Time Sys Name Y CONTROLFILE/ Y DATAFILE/ Y ONLINELOG/ Y PARAMETERFILE/ Y TEMPFILE/ N spfilerac2.ora => +RACDATA1/RAC2/PARAMETERFILE/spfile.298.647083581
7. Shutdown the instance and restart the database using srvctl using the newly created spfile.
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> exit
$ srvctl start database -d rac2
$ srvctl status database -d rac2 Instance rac21 is running on node halle Instance rac22 is running on node elle
8. ASMCMD will now show a number of spfiles exist in the PARAMETERFILE directory for this database. The spfile containing the parameter preventing startups should be removed from ASM.
In this case the files spfile.284.647081527 and spfile.297.647081495 can be removed because spfile.298.647083581 is the current spfile.
ASMCMD> pwd +RACDATA1/RAC2 ASMCMD> cd PARAMETERFILE ASMCMD> ls -l Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE FEB 19 08:00:00 Y spfile.284.647081527 PARAMETERFILE UNPROT COARSE FEB 19 08:00:00 Y spfile.297.647081495 PARAMETERFILE UNPROT COARSE FEB 19 09:00:00 Y spfile.298.647083581 ASMCMD> rm spfile.284.647081527 ASMCMD> rm spfile.297.647081495 ASMCMD> ls spfile.298.647083581
Conclusion
If the spfile alias in ASM links to an spfile that looks something like spfilerac2.ora => +RACDATA1/DB_UNKNOWN/PARAMETERFILE/SPFILE.284.647080599, it means the new spfile was created while the database instance was down and YOU WILL NEED TO RECREATE IT while the instance has been started and mounted using the pfile created from the alert.log entries for each instance. In RAC environment to start instances using sqlplus you will need to have an init.ora in $ORACLE_HOME/dbs with a single spfile parameter entry pointing to the location of the ASM based spfile. When using srvctl to start the database or instances the OCR resource should have the spfile pointer associated with the resource set using srvctl modify database.
For example:
ASMCMD> ls -l Type Redund Striped Time Sys Name Y CONTROLFILE/ Y DATAFILE/ Y ONLINELOG/ Y PARAMETERFILE/ Y TEMPFILE/ N spfilerac2.ora => +RACDATA1/DB_UNKNOWN/PARAMETERFILE/SPFILE.284.647080599