Oracle DBA experience: restored spfile in DB

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

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

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

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

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

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

Oracle DBA experience

Wednesday, January 18, 2012

restored spfile in DB_UNKNOWN Directory

1 comment: