• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to recreate the spfile for RAC instances where the spfile is stored in ASM

by admin

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

Filed Under: ASM, oracle, RAC

Some more articles you might also be interested in …

  1. Oracle Database 18c new feature – Scalable Sequences
  2. Oracle RMAN: Monitoring Recovery Manager Jobs
  3. Automatic Undo Management and Oracle RAC
  4. SQL script to find tables that are fragmented
  5. How to Change SYS and SYSTEM Passwords in Oracle Database
  6. How to check which options or features are used in the Oracle database
  7. Basics of PL/SQL LOOPs
  8. Oracle RMAN : Block-Level Media Recovery (Basics and Example)
  9. How To Find When The Spfile Was Created On Linux Server
  10. How to move spfile from ASM to filesystem

You May Also Like

Primary Sidebar

Recent Posts

  • Chezmoi: A multi-machine dotfile manager, written in Go
  • cheat: Create and view interactive cheat sheets on the command-line
  • chars: Display names and codes for various ASCII and Unicode characters and code points
  • chafa: Image printing in the terminal

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright