Wednesday, January 18, 2012

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_<INSTANCE_NAME>.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