restored spfile in DB_UNKNOWN Directory
In the below scenario, I am placing the spfile correctly after the RMAN restored the spfile in the DB_UNKNOWN directory in the diskgroup.
This is oracle bug( Bug 5370663: RMAN RESTORES SPFILE IN ASM TO DB_UNKNOWN’)
Note: 1) we have to enable the autobackup mode in the RMAN to restore the spfile from the backup. If autobackup mode is not enabled then we can’t restore the spfile from backup
2) I have started with one instance on the RAC database. Till I completed the restore, I will be performing all the activity from the same node. Keep the second instance down.
3) Remove the init_.ora parameter file from the $ORACLE_HOME/dbs folder. This file will contain the old spfile details. When you start the database with the srvctl command, this file will be created automatically by the oracle agent in the $ORACLE_HOME/dbs folder.
We need to identify the DB_NAME, DBID & DB_UNIQUE_NAME details for recovering the database using RMAN
To start the database in the force mode, only DB_NAME is sufficient
oracle BHU_1 bhuora01> cat initBHU.ora
*.db_name='BHU'
*.db_unique_name='BHU_A'
Start the instance with the force option to the nomount stage
oracle BHU_1 bhuora01> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:28:28 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount force pfile='/home/oracle/initBHU.ora';
ORACLE instance started.
Total System Global Area 304861184 bytes
Fixed Size 2225872 bytes
Variable Size 159385904 bytes
Database Buffers 134217728 bytes
Redo Buffers 9031680 bytes
SQL> exit
Note: 1) you can identify the DBID from the level 0 or 1 backup log file
2) Some time oracle writes the DBID in the alert log file
We have to connect to the database using rman and provide the DBID for the database. If you have the recovery catalog then there is no need to specify the DBID.
oracle BHU_1 bhuora01> rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:28:59 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BHU (not mounted)
RMAN> set dbid=263762951
executing command: SET DBID
RMAN> run {
ALLOCATE CHANNEL 'dev_1' type 'SBT_TAPE';
restore spfile to '+BHU_DATA1' from autobackup;
}
2> 3> 4>
using target database control file instead of recovery catalog
allocated channel: dev_1
channel dev_1: SID=175 device type=SBT_TAPE
channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
Starting restore at 17-JAN-2012
channel dev_1: looking for AUTOBACKUP on day: 20120117
channel dev_1: AUTOBACKUP found: c-263762951-20120117-00
channel dev_1: restoring spfile from AUTOBACKUP c-263762951-20120117-00
channel dev_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-JAN-2012
released channel: dev_1
Spfile has been restored to the DB_UNKNOWN directory under the diskgroup which we specified in the restore
ASMCMD [+BHU_DATA1] > ls -lt
Type Redund Striped Time Sys Name
Y BHU_A/
Y DB_UNKNOWN/
ASMCMD [+BHU_DATA1] > cd D*
ASMCMD [+BHU_DATA1/DB_UNKNOWN] > cd p*
ASMCMD [+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE] > ls -lt
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE JAN 17 15:00:00 Y SPFILE.271.772818033
Once the spfile is restored. We have create a new pfile from the restore spfile from the ASM diskgroup
oracle BHU_1 bhuora01> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:42:08 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> create pfile='/home/oracle/BHU_afterbackup.ora' from spfile='+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE/SPFILE.271.772818033';
File created.
Now, I am removing the DB_UNKNOWN directory from the diskgroup
Don’t remove before create a pfile from it.
ASMCMD [+BHU_DATA1] > ls -lt
Type Redund Striped Time Sys Name
Y BHU_A/
Y DB_UNKNOWN/
ASMCMD [+BHU_DATA1] > ls
DB_UNKNOWN/
BHU_A/
ASMCMD [+BHU_DATA1] > rm -rf db*
We are stopping the database and Re-start the database with the newly created pfile.
oracle BHU_1 bhuora01> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:43:30 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount pfile='/home/oracle/zs1_afterbackup.ora';
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 1.4431E+10 bytes
Fixed Size 2240272 bytes
Variable Size 3892314352 bytes
Database Buffers 1.0503E+10 bytes
Redo Buffers 34148352 bytes
SQL> create spfile='+BHU_DATA1' from pfile='/home/oracle/bhu_afterbackup.ora';
File created.
SQL>
We could see that the spfile has been created in the right directory structure.
ASMCMD [+BHU_DATA1/ZS2_A/PARAMETERFILE] > ls
spfile.268.772818375
+BHU_DATA1/ZS2_A/PARAMETERFILE/spfile.268.772818375
Now we are changing the configuration details in the server control
oracle BHU_1 bhuora01> srvctl modify database -d BHU_A -p +BHU_DATA1/BHU_A/PARAMETERFILE/spfile.268.772818375
oracle BHU_1 bhuora01> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:48:07 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
If you start the database with sqlplus, Oracle can’t able to identify the spfile or pfile in the $ORACLE_HOME/dbs directory.
So we have to start the database with the srvctl command, which has the right spfile details.
If you start the database with the srvctl command, oracle creates pfile for the respective instance on the database.
SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/BHU/112_64/dbs/initBHU_1.ora'
SQL> exit
oracle BHU_1 bhuora01> srvctl start database -d BHU_A -o nomount
oracle BHU_1 bhuora01> sqlplus / as sysdba
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +BHU_DATA1/BHU_a/parameterfile
/spfile.268.772818375
No comments:
Post a Comment