• 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 Modify an Existing ASM Spfile in a RAC Environment

by admin

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.

Note: Before proceeding with the following steps, it is assumed that the databases using ASM will have been shutdown on each node.

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'
Note: Both $ASM_HOME/dbs/init+ASM1.ora and $ASM_HOME/dbs/init+ASM2.ora, or each init_ASM[n] in multiple node RAC, have to point to the same ASM spfile location.

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.

Filed Under: oracle, Oracle 10g, Oracle 11g

Some more articles you might also be interested in …

  1. How to Check if a table is Indexed in Oracle
  2. Beginners Guide to Oracle Password Management Policy
  3. Oracle database 12c : How to Drop Partition(s)
  4. Oracle Golden gate GGSCI commands quick reference (Cheat Sheet)
  5. Define redo log files in an Oracle RAC environment
  6. Deleting duplicate records from a table
  7. Retrieving data with PL/SQL
  8. Troubleshooting Common ORA-1157 Errors (cannot identify/lock data file)
  9. Oracle Database Environment Variables and Their Functions
  10. Oracle Database – Configuring Secure Application Roles

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright