This post provides an example on how to modify an existing Automatic Storage Management (ASM) spfile in a two-node Real Application Clusters (RAC) Environment. These steps can be applied to a multiple node Real Application Clusters (RAC) Environment.
Applies To:
Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
If a spfile is used in Clustered ASM environments, then the spfile must be on a shared Clusterware device, i.e. shared spfile.
1. Shutdown +ASM2 instance on second node, or if multiple node RAC, all ASM instances on each node except node 1:
The default locations are based on environment variable settings when the ASM instance is started.
$ export $ORACLE_SID=+ASM2 $ export $ORACLE_HOME=[full path of the asm home]
10g and 11gR1:
$ sqlplus '/ as sysdba'
SQL> shutdown immediate;
2. Then, on node 1, connect to +ASM1 instance
$ export $ORACLE_SID=+ASM1 $ export $ORACLE_HOME=[full path of the asm home]
10g and 11gR1:
$ sqlplus '/ as sysdba'
SQL> show parameter spfile ---> make a note of spfile location SQL> create pfile =[the full pathname of init+ASM1.ora] from spfile;
SQL> shutdown immediate;
3. Create a modified ASM pfile
For example, modify the ‘init+ASM1.ora’ to include both +ASM1 and +ASM2 diskgroups parameter and save ‘init+ASM1.ora’:
+ASM1.asm_diskgroups='DAT'#Manual Mount
+ASM2.asm_diskgroups='DAT'#Manual Mount
*.asm_diskgroups='DAT'
*.asm_diskstring='ORCL:*'
*.background_dump_dest='/app/oracle/admin/+ASM/bdump'
*.cluster_database=true
*.core_dump_dest='/app/oracle/admin/+ASM/cdump'
+ASM1.instance_number=1
+ASM2.instance_number=2
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='exclusive'
*.user_dump_dest='/app/oracle/admin/+ASM/udump'
4. Start up the +ASM1 instance on the first node:
$ export $ORACLE_SID=+ASM1 $ export $ORACLE_HOME=[full path of the asm home]
10g and 11gR1:
$ sqlplus '/ as sysdba'
SQL> startup pfile=[the full pathname of init+ASM1.ora]
The ASM instance should start up properly.
5. Check if the ASM diskgroups are mounted properly or not:
SQL> select name, total_mb, free_mb from v$asm_diskgroup; NAME TOTAL_MB FREE_MB ---------- ---------- ---------- ACFS 97660 316 DAT 9773 9720 RECO 390640 366400 SPFILE 35824 35765 ------> SPFILE is just an example diskgroup. The ASM spfile can be located on any diskgroup. 4 rows selected.
6. Create the spfile:
SQL> create spfile='+SPFILE' from pfile=[the full pathname of init+ASM1.ora]; File created.
7. Check the new spfile location through ASMCMD as well:
$ export $ORACLE_SID=+ASM1 $ export $ORACLE_HOME=[full path of the asm home]
ASMCMD> ls ACFS/ DAT/ RECO/ SPFILE/ ASMCMD> cd SPFILE ASMCMD> ls ASM/ ASMCMD> cd ASM ASMCMD> ls ASMPARAMETERFILE/ ASMCMD> cd ASMPARAMETERFILE/ ASMCMD> ls REGISTRY.228.769874562 ASMCMD> ls -l Type Redund Striped Time Sys Name ASMPARAMETERFILE UNPROT COARSE OCT 01 11:00:00 Y REGISTRY.228.769874562 ASMCMD> exit
8. Shutdown +ASM1:
$ export $ORACLE_SID=+ASM1 $ export $ORACLE_HOME=[full path of the asm home]
10g and 11gR1:
$ sqlplus '/ as sysdba' SQL> shutdown immediate;
9. Check the $ASM_HOME/dbs/init+ASM1.ora to ensure that it points to new location of spfile:
For example:
$ cd $ASM_HOME/dbs $ cat init+ASM1.ora SPFILE='+SPFILE/asm/asmparameterfile/registry.228.769874562'
If $ASM_HOME/dbs/init+ASM1.ora does not point to new location of spfile, then:
$ vi $ASM_HOME/dbs/init+ASM1.ora
modify $ASM_HOME/dbs/init+ASM1.ora:
$ SPFILE='+SPFILE/asm/asmparameterfile/registry.228.769874562'
save init+ASM1.ora. Re-check $ASM_HOME/dbs/init+ASM1.ora:
$ cat init+ASM1.ora SPFILE='+SPFILE/asm/asmparameterfile/registry.228.769874562'
10. Restart the +ASM1 instance using the new spfile:
$ export $ORACLE_SID=+ASM1 $ export $ORACLE_HOME=[full path of the asm home]
10g and 11gR1:
$ sqlplus '/ as sysdba' SQL> startup
11. Check the new spfile location through SQL*Plus:
SQL> sho parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +SPFILE/asm/asmparameterfile/registry.228.769874562
12. Check if the ASM diskgroups are mounted properly or not:
SQL> select name, total_mb, free_mb from v$asm_diskgroup; NAME TOTAL_MB FREE_MB ------------------------------ ---------- ---------- ACFS 97660 316 DAT 9773 9720 RECO 390640 366400 SPFILE 35824 35765 4 rows selected.
13. Go to node 2, or each node in multiple node RAC, and check the $ASM_HOME/dbs/init+ASM2.ora to ensure that it points to new location of spfile:
For example:
$ cd $ASM_HOME/dbs $ cat init+ASM2.ora SPFILE='+SPFILE/asm/asmparameterfile/registry.228.769874562'
If $ASM_HOME/dbs/init+ASM2.ora does not point to new location of spfile, then:
$ vi $ASM_HOME/dbs/init+ASM2.ora
modify $ASM_HOME/dbs/init+ASM2.ora:
$ SPFILE='+SPFILE/asm/asmparameterfile/registry.228.769874562'
save init+ASM2.ora and re-check $ASM_HOME/dbs/init+ASM2.ora:
$ cat init+ASM2.ora SPFILE='+SPFILE/asm/asmparameterfile/registry.228.769874562'
14. Restart the +ASM2 instance using the new spfile, or follow this step, step 15, and step 16 in sequential order, for each ASM instance in multiple node RAC (This is a safe startup process for the ASM instances in RAC, rather then using srvctl to startup the rest of the ASM instances.):
$ export $ORACLE_SID=+ASM2 $ export $ORACLE_HOME=[full path of the asm home]
10g and 11gR1:
$ sqlplus '/ as sysdba' SQL> startup
15. Check the new spfile location through SQL*Plus, or on each ASM instance in multiple node RAC:
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +SPFILE/asm/asmparameterfile/registry.228.769874562
16. Check if the ASM diskgroups are mounted properly or not, or for each ASM instance in multiple node RAC:
SQL> select name, total_mb, free_mb from v$asm_diskgroup; NAME TOTAL_MB FREE_MB ------------------------------ ---------- ---------- ACFS 97660 316 DAT 9773 9720 RECO 390640 366400 SPFILE 35824 35765 4 rows selected.