By Brian Fitzgerald
Introduction
Here is a simple technique for relocating the Oracle Database spfile from the file system to ASM in a single RMAN step in Oracle Restart. No direct ASM access is required. The technique applies to Oracle Database 12c and up.
tl;dr
RMAN> restore from service 'EQTRD' spfile to '+DATA01';
SQL> startup force
Initial condition
Note the Restart configuration
Note the db_unique_name and the spfile location.
[oracle@ip-172-31-88-93 ~]$ srvctl config database -database EQTRD Database unique name: EQTRD Database name: EQTRD Oracle home: /u01/app/oracle/product/19.3.0/dbhome_1 Oracle user: oracle Spfile: /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: RECO01,DATA01 Services: OSDBA group: OSOPER group: Database instance: EQTRD
ASM
Check that no spfile exists in ASM for your db_unique_name:
[grid@ip-172-31-88-93 ~]$ asmcmd ls +DATA01/EQTRD/PARAMETERFILE ASMCMD-8002: entry 'PARAMETERFILE' does not exist in directory '+DATA01/EQTRD/'
No spfile was found in ASM.
Check Restart
The initial condition should be a working Oracle Restart.
[oracle@ip-172-31-88-93 ~]$ srvctl stop database -database EQTRD [oracle@ip-172-31-88-93 ~]$ srvctl start database -database EQTRD [oracle@ip-172-31-88-93 ~]$
Restore
Restore the spfile using a single RMAN command. You must connect to oracle via a service. “rman target /” will not work in this case. You only need to specify the target disk group. You do not need to specify the full ASM directory path.
[oracle@ip-172-31-88-93 ~]$ rman target sys/Zystm.22@EQTRD Recovery Manager: Release 19.0.0.0.0 - Production on Sun Oct 13 18:12:25 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: EQTRD (DBID=625173207) RMAN> restore from service 'EQTRD' spfile to '+DATA01'; Starting restore at 13-OCT-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=282 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service EQTRD channel ORA_DISK_1: restoring SPFILE output file name=+DATA01 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 13-OCT-19 RMAN> Recovery Manager complete.
The RMAN technique uses the “restore from service” syntax, available in Oracle Database 12c and up.
Check Restart
Notice that the Restart configuration got updated:
[oracle@ip-172-31-88-93 ~]$ srvctl config database -database EQTRD Database unique name: EQTRD Database name: EQTRD Oracle home: /u01/app/oracle/product/19.3.0/dbhome_1 Oracle user: oracle Spfile: +DATA01/EQTRD/PARAMETERFILE/spfile.280.1021572757 Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: RECO01,DATA01 Services: OSDBA group: OSOPER group: Database instance: EQTRD
Notice we moved spfile and learned its new location without direct access to ASM. In an organization with segregation of duties, the RDMBS DBA can move spfile to ASM without coordination with the ASM DBA.
Check ASM
For information, we may check ASM.
[grid@ip-172-31-88-93 ~]$ asmcmd ls +DATA01/EQTRD/PARAMETERFILE/spfile.280.1021572757 spfile.280.1021572757
Remove old spfile
Remove the old spfile from the file system (if you dare!).
The Oracle base has been set to /u01/app/oracle [oracle@ip-172-31-88-93 ~]$ rm /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora [oracle@ip-172-31-88-93 ~]$
Restart the database
You must restart the database instance now. You may use sqlplus or srvctl.
SQL> startup force ORACLE instance started. Total System Global Area 2365586088 bytes Fixed Size 8899240 bytes Variable Size 520093696 bytes Database Buffers 1828716544 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL>
Done!
Only one RMAN command was needed to move the spfile to ASM.
Reversal
You can move spfile in the reverse direction, from ASM to the file system.
Check the file system
Note that the spfile does not exist on the file system
[oracle@ip-172-31-88-93 ~]$ ls /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora ls: cannot access /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora: No such file or directory
Restore spfile to file system
[oracle@ip-172-31-88-93 ~]$ rman target sys/Zystm.22@EQTRD Recovery Manager: Release 19.0.0.0.0 - Production on Sun Oct 13 18:40:05 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: EQTRD (DBID=625173207) RMAN> restore from service 'EQTRD' spfile to '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora'; Starting restore at 13-OCT-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=55 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service EQTRD channel ORA_DISK_1: restoring SPFILE output file name=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 13-OCT-19 RMAN>
Moving the spfile to the file system required no access to ASM and no knowledge of the ASM file name.
Check that spfile exists on the file system
[oracle@ip-172-31-88-93 ~]$ ls -l /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora -rw-r-----. 1 oracle dba 3584 Oct 13 18:40 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora
Check that Restart is updated
Remove old spfile from ASM
[ec2-user@ip-172-31-88-93 ~]$ sudo -i -u grid The Oracle base has been set to /u01/app/grid [grid@ip-172-31-88-93 ~]$ asmcmd rm +DATA01/EQTRD/PARAMETERFILE/spfile.280.1021572757 [grid@ip-172-31-88-93 ~]$
Restart the database
Instead of using sqlplus, you may use srvctl.
[oracle@ip-172-31-88-93 ~]$ srvctl stop database -database EQTRD -stopoption ABORT [oracle@ip-172-31-88-93 ~]$ srvctl start database -database EQTRD [oracle@ip-172-31-88-93 ~]$
Conclusion
Previously, moving spfile TO ASM required five steps:
- Create pfile from spfile
- Create spfile from pfile
- Connect to ASM and identify the file name.
- Update CRS by running srvctl modify database
- Restart the instance
By the way, some DBAs pre-create an ASM directory. I don’t know why. Some DBAs specify an ASM alias name for the spfile. I just let ASM generate a random, unique name.
spfile was moved from the file system in a single step. It was unnecessary to create an intermediate pfile, nor was it necessary to know the path to the source spfile. You only need to specify the ASM disk group. The technique did not require the DBA to access ASM to learn the location of the spfile. There is no separate srvctl modify database step. RMAN takes care of that.
In RAC, spfile centralization is an administrative win. Multiple instance settings can be stored in a single file. Initialization parameter settings applicable to all instances, or only one instance can be made even if a RAC node is down. Parameters follow an instance even if an instance is relocated. The same goes for RAC One Node.
Remember that a Restart instance can be relocated, too. With spfile in ASM, there is no need to move spfile during relocation. If you upgrade the Oracle version, you don’t have to be concerned about moving the spfile or reconfiguring Restart.