We will restore the spfile from the backup.
We will recover the the spfile from the offline backup first.Create an offline backup with the script:
rman target bkpuser/testOra1 nocatalog
run
{
shutdown immediate;
startup mount;
backup format='/backup/database/offline_%d_%U.bkp' tag='offline backup' database;
alter database open;
}
You can check, that the backup contains the spfile:
RMAN> list backup of spfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
74 Full 7.48M DISK 00:00:06 16-FEB-12
BP Key: 77 Status: AVAILABLE Compressed: NO Tag: TAG20120216T104134
Piece Name: /backup/controlfile/c-4236471253-20120216-01
SPFILE Included: Modification time: 05-DEC-11
SPFILE db_unique_name: BTEST
Now, shutdown the database and delete the spfile (or better rename it:) ). Be sure, that there is no init.ora file in the directory.
[oracle@btest oracle]$ cd $ORACLE_HOME
[oracle@btest db_1]$ cd dbs/
[oracle@btest dbs]$ rm spfilebtest.ora
If I will try to start the DB, I will receive an error:
[oracle@btest dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 10:46:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
Now it's time to restore the spfile from the backup. If you use default path to store the controlfile autobackups, it's simple:
rman target bkpuser/testOra1 nocatalog
setd dbid=4236471253
startup nomount;
restore spfile from autobackup;
shutdown immediate;
startup;
If you dont use the default path, you shold run:
[oracle@btest dbs]$ rman target bkpuser/testOra1 nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 10:55:04 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid=4236471253
executing command: SET DBID
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1343612 bytes
Variable Size 75501444 bytes
Database Buffers 79691776 bytes
Redo Buffers 2482176 bytes
RMAN> run
{
set controlfile autobackup format for device type disk TO '/backup/controlfile/%F';
restore spfile from autobackup;
}
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120216
channel ORA_DISK_1: AUTOBACKUP found: /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Restoring the SPFILE from a Specific Backup Set:
[oracle@btest controlfile]$ rman target bkpuser/testOra1 nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:07:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid=4236471253
executing command: SET DBID
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1343612 bytes
Variable Size 75501444 bytes
Database Buffers 79691776 bytes
Redo Buffers 2482176 bytes
RMAN> restore spfile from '/backup/controlfile/c-4236471253-20120216-01';
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Restoring the SPFILE using a Recovery Catalog:
When you are using the rman catalog, you dont need to specify the DBID before starting the database.
You event dont need to specify the path to autobackup event if you are not using the default path, because all is stored in the catalog.
[oracle@btest dbs]$ rman target bkpuser/testOra1 catalog rcat/testOra1@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:15:36 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1343612 bytes
Variable Size 75501444 bytes
Database Buffers 79691776 bytes
Redo Buffers 2482176 bytes
RMAN> restore spfile from autobackup;
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120216
channel ORA_DISK_1: AUTOBACKUP found: /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Restoring the backed up SPFILE when DB is up and running:
check that the DB is running:
[oracle@btest dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 11:22:18 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
OPEN
delete the spfile:
[oracle@btest dbs]$ cd $ORACLE_HOME
[oracle@btest db_1]$ cd dbs
[oracle@btest dbs]$ rm spfilebtest.ora
Now, restore the spfile:
[oracle@btest dbs]$ rman target bkpuser/testOra1 nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:26:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BTEST (DBID=4236471253)
using target database control file instead of recovery catalog
RMAN> restore spfile to '/backup/restore.ora' from autobackup;
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120216
channel ORA_DISK_1: AUTOBACKUP found: /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
Copy the restored file to the $ORACLE_HOME/dbs and try to restart the db:
[oracle@btest backup]$ cp restore.ora /oracle/product/11.2/db_1/dbs/spfilebtest.ora
[oracle@btest backup]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 11:29:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/11.2/db_1/dbs/
spfilebtest.ora
Restoring the spfile using catalog:
[oracle@btest backup]$ rman target bkpuser/testOra1 catalog rcat/testOra1@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:32:38 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BTEST (DBID=4236471253)
connected to recovery catalog database
RMAN> restore spfile to '/backup/restore.ora';
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=/backup/restore.ora
channel ORA_DISK_1: reading from backup piece /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: piece handle=/backup/controlfile/c-4236471253-20120216-01 tag=TAG20120216T104134
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-FEB-12
[oracle@btest backup]$ cp restore.ora /oracle/product/11.2/db_1/dbs/spfilebtest.ora
[oracle@btest backup]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 11:34:09 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/11.2/db_1/dbs/
spfilebtest.ora
rman target bkpuser/testOra1 nocatalog
run
{
shutdown immediate;
startup mount;
backup format='/backup/database/offline_%d_%U.bkp' tag='offline backup' database;
alter database open;
}
You can check, that the backup contains the spfile:
RMAN> list backup of spfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
74 Full 7.48M DISK 00:00:06 16-FEB-12
BP Key: 77 Status: AVAILABLE Compressed: NO Tag: TAG20120216T104134
Piece Name: /backup/controlfile/c-4236471253-20120216-01
SPFILE Included: Modification time: 05-DEC-11
SPFILE db_unique_name: BTEST
Now, shutdown the database and delete the spfile (or better rename it:) ). Be sure, that there is no init.ora file in the directory.
[oracle@btest oracle]$ cd $ORACLE_HOME
[oracle@btest db_1]$ cd dbs/
[oracle@btest dbs]$ rm spfilebtest.ora
If I will try to start the DB, I will receive an error:
[oracle@btest dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 10:46:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
Now it's time to restore the spfile from the backup. If you use default path to store the controlfile autobackups, it's simple:
rman target bkpuser/testOra1 nocatalog
setd dbid=4236471253
startup nomount;
restore spfile from autobackup;
shutdown immediate;
startup;
If you dont use the default path, you shold run:
[oracle@btest dbs]$ rman target bkpuser/testOra1 nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 10:55:04 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid=4236471253
executing command: SET DBID
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1343612 bytes
Variable Size 75501444 bytes
Database Buffers 79691776 bytes
Redo Buffers 2482176 bytes
RMAN> run
{
set controlfile autobackup format for device type disk TO '/backup/controlfile/%F';
restore spfile from autobackup;
}
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120216
channel ORA_DISK_1: AUTOBACKUP found: /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Restoring the SPFILE from a Specific Backup Set:
[oracle@btest controlfile]$ rman target bkpuser/testOra1 nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:07:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid=4236471253
executing command: SET DBID
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1343612 bytes
Variable Size 75501444 bytes
Database Buffers 79691776 bytes
Redo Buffers 2482176 bytes
RMAN> restore spfile from '/backup/controlfile/c-4236471253-20120216-01';
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Restoring the SPFILE using a Recovery Catalog:
When you are using the rman catalog, you dont need to specify the DBID before starting the database.
You event dont need to specify the path to autobackup event if you are not using the default path, because all is stored in the catalog.
[oracle@btest dbs]$ rman target bkpuser/testOra1 catalog rcat/testOra1@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:15:36 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2/db_1/dbs/initbtest.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1343612 bytes
Variable Size 75501444 bytes
Database Buffers 79691776 bytes
Redo Buffers 2482176 bytes
RMAN> restore spfile from autobackup;
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120216
channel ORA_DISK_1: AUTOBACKUP found: /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Restoring the backed up SPFILE when DB is up and running:
check that the DB is running:
[oracle@btest dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 11:22:18 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
OPEN
delete the spfile:
[oracle@btest dbs]$ cd $ORACLE_HOME
[oracle@btest db_1]$ cd dbs
[oracle@btest dbs]$ rm spfilebtest.ora
Now, restore the spfile:
[oracle@btest dbs]$ rman target bkpuser/testOra1 nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:26:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BTEST (DBID=4236471253)
using target database control file instead of recovery catalog
RMAN> restore spfile to '/backup/restore.ora' from autobackup;
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120216
channel ORA_DISK_1: AUTOBACKUP found: /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-12
Copy the restored file to the $ORACLE_HOME/dbs and try to restart the db:
[oracle@btest backup]$ cp restore.ora /oracle/product/11.2/db_1/dbs/spfilebtest.ora
[oracle@btest backup]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 11:29:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/11.2/db_1/dbs/
spfilebtest.ora
Restoring the spfile using catalog:
[oracle@btest backup]$ rman target bkpuser/testOra1 catalog rcat/testOra1@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 16 11:32:38 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BTEST (DBID=4236471253)
connected to recovery catalog database
RMAN> restore spfile to '/backup/restore.ora';
Starting restore at 16-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=/backup/restore.ora
channel ORA_DISK_1: reading from backup piece /backup/controlfile/c-4236471253-20120216-01
channel ORA_DISK_1: piece handle=/backup/controlfile/c-4236471253-20120216-01 tag=TAG20120216T104134
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-FEB-12
[oracle@btest backup]$ cp restore.ora /oracle/product/11.2/db_1/dbs/spfilebtest.ora
[oracle@btest backup]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 11:34:09 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 234356736 bytes
Fixed Size 1344088 bytes
Variable Size 146804136 bytes
Database Buffers 83886080 bytes
Redo Buffers 2322432 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/11.2/db_1/dbs/
spfilebtest.ora