Oracle DBA – A lifelong learning experience

Recovering old spfile from autobackup

Posted by John Hallas on January 18, 2010

This post is about a problem I had today when trying to restore a spfile from a previous autobackup.  I did not want the latest one but one from earlier in the week. The database was a RAC one but I don’t think that is relevant to the problems I had.

Recovering a spfile from autobackup is relatively easy – startup mount and recover spfile from autobackup.  I set my date format and started the database up in mount mode and then tried to recover the spfile.

[oracle@server][EBSPRD1A]/home/oracle $srvctl start database -d ebsprd1a -o mount

[oracle@server][EBSPRD1A]/home/oracle $rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 17 13:23:55 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: EBSPRD1A (DBID=1343018800, not open)

using target database control file instead of recovery catalog

run {
set until time "to_date('15-JAN-2010','DD-MON-YYYY')";
restore spfile from autobackup;
}

executing command: SET until clause
Starting restore at 17-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=473 instance=EBSPRD1A device
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/17/2010 13:26:20
<span style="color: #ff0000;">RMAN-06564: must use the TO clause when the instance is started with SPFILE</span>
exit

Recovery Manager complete.

So after a failure stating ‘file already exists’ I figured I had better remove the spfile first by setting the SID to be ASM and using asmcmd to go to the folder containing the spfile.

ASMCMD [+DATA/EBSPRD1A/PARAMETERFILE] > rm spfileEBSPRD1A.ora

ASMCMD [+DATA/EBSPRD1A/PARAMETERFILE] > exit

[oracle@server][+ASM1]/home/oracle $setsid

Please select a SID from the list below by entering  the associated number.
1.      EBSPRD1A
2.      +ASM1
3.      agent10g
Select the Oracle SID : 1
Setting ORACLE_SID = EBSPRD1A
The Oracle base for ORACLE_HOME=/app/oracle/product/11.1.0/db_1 is /app/oracle
[oracle@server][EBSPRD1A]/home/oracle $rmant

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 17 13:28:54 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: EBSPRD1A (DBID=1343018800, not open)

using target database control file instead of recovery catalog

run {
set until time "to_date('15-JAN-2010','DD-MON-YYYY')";
restore spfile to '+DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora' from autobackup;
}
executing command: SET until clause
Starting restore at 17-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=500 instance=EBSPRD1A device
recovery area destination: +FRA
database name (or database unique name) used for search: EBSPRD1A

channel ORA_DISK_1: AUTOBACKUP +fra/EBSPRD1A/AUTOBACKUP/2010_01_14/s_708269575.4081.708269577 found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100115

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100114

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +fra/EBSPRD1A/AUTOBACKUP/2010_01_14/s_708269575.4081.708269577

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/17/2010 13:31:37
<span style="color: #ff0000;">ORA-32011: cannot restore SPFILE to location already being used by the instance</span>

So I thought about restoring to a filesystem file and copying it in later, which proved impossible

run {
set until time "to_date('15-JAN-2010','DD-MON-YYYY')";
restore spfile to '/home/oracle/spfilejohn.ora' from autobackup;
}
executing command: SET until clause
Starting restore at 17-JAN-10
using channel ORA_DISK_1
recovery area destination: +FRA
database name (or database unique name) used for search: EBSPRD1A
channel ORA_DISK_1: AUTOBACKUP +fra/EBSPRD1A/AUTOBACKUP/2010_01_14/s_708269575.4081.708269577 found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100115
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100114
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +fra/EBSPRD1A/AUTOBACKUP/2010_01_14/s_708269575.4081.708269577
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-JAN-10
Recovery Manager complete.

[oracle@server][EBSPRD1A]/home/oracle $srvctl stop database -d ebsprd1a -o immediate

[oracle@server][EBSPRD1A]/home/oracle $setsid

[oracle@server][+ASM1]/home/oracle $asmcmd -p

ASMCMD [+] > cd +DATA/EBSPRD1A/PARAMETERFILE/

ASMCMD [+DATA/EBSPRD1A/PARAMETERFILE] > cp /home/oracle/spfilejohn.ora ./spfileEBSPRD1A.ora

ASMCMD-08012: can not determine file type for file->'/home/oracle/spfilejohn.ora'

ORA-15056: additional error message

ORA-19762: invalid file type DGGetFileAttr20

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 228

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

copying /home/oracle/spfilejohn.ora -> +DATA/EBSPRD1A/PARAMETERFILE/./spfileEBSPRD1A.ora

ASMCMD-08016: copy source->'/home/oracle/spfilejohn.ora' and target->'+DATA/EBSPRD1A/PARAMETERFILE/./spfileEBSPRD1A.ora' failed

ORA-00600: internal error code, arguments: [1866], [0x9FFFFFFFFD5B4FE0], [4252995008], [0x60000000003C6140], [], [], [], [], [], [], [], []

ORA-31093: null or invalid value specified for parameter : dbms_diskgroup.copy:SRC_FSIZ

ORA-31093: null or invalid value specified for parameter : dbms_diskgroup.copy:SRC_BLKSZ

ORA-31093: null or invalid value specified for parameter : dbms_diskgroup.copy:SRC_FTYPE

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 258

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

ASMCMD [+DATA/EBSPRD1A/PARAMETERFILE] > cp /home/oracle/spfilejohn.ora '+DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora'

ASMCMD-08012: can not determine file type for file->'/home/oracle/spfilejohn.ora'

ORA-15056: additional error message

ORA-19762: invalid file type DGGetFileAttr20

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 228

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

copying /home/oracle/spfilejohn.ora -> +DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora

ASMCMD-08016: copy source->'/home/oracle/spfilejohn.ora' and target->'+DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora' failed

ORA-03113: end-of-file on communication channel

Process ID: 1186

Session ID: 115 Serial number: 1625 (DBD ERROR: OCIStmtExecute)

ASMCMD [+DATA/EBSPRD1A/PARAMETERFILE] > cp /home/oracle/spfilejohn.ora +DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora

ASMCMD-08012: can not determine file type for file->'/home/oracle/spfilejohn.ora'

ORA-15056: additional error message

ORA-19762: invalid file type DGGetFileAttr20

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 228

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

copying /home/oracle/spfilejohn.ora -> +DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora

ASMCMD-08016: copy source->'/home/oracle/spfilejohn.ora' and target->'+DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora' failed

ORA-03113: end-of-file on communication channel

So finally I decided to start up using a pfile created earlier, restore the spfile from autobackup and hopefully have no problems and that was the way it worked out.

SYS@EBSPRD1A SQL>STARTUP MOUNT PFILE='/app/oracle/product/11.1.0/db_1/dbs/initEBSPRD1A.ora.1601_2';

ORACLE instance started.
Total System Global Area 3.2068E+10 bytes  (yes that is a 32Gb SGA)
Fixed Size                  2120432 bytes
Variable Size            1.8902E+10 bytes
Database Buffers         1.3153E+10 bytes
Redo Buffers               10752000 bytes
Database mounted.
SYS@EBSPRD1A SQL>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options

[oracle@server][EBSPRD1A]/app/oracle/product/11.1.0/db_1/dbs $rmant
Recovery Manager: Release 11.1.0.6.0 - Production on Sun Jan 17 13:46:15 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: EBSPRD1A (DBID=1343018800, not open)
using target database control file instead of recovery catalog

run {
set until time "to_date('15-JAN-2010','DD-MON-YYYY')";
restore spfile to '+DATA/EBSPRD1A/PARAMETERFILE/spfileEBSPRD1A.ora' from autobackup;
}

executing command: SET until clause
Starting restore at 17-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=995 instance=EBSPRD1A device
recovery area destination: +FRA
database name (or database unique name) used for search: EBSPRD1A
channel ORA_DISK_1: AUTOBACKUP +fra/EBSPRD1A/AUTOBACKUP/2010_01_14/s_708269575.4081.708269577 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100115
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20100114
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +fra/EBSPRD1A/AUTOBACKUP/2010_01_14/s_708269575.4081.708269577
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-JAN-10

[oracle@server][EBSPRD1A]/app/oracle/product/11.1.0/db_1/dbs $srvctl stop database -d ebsprd1a -o immediate

[oracle@server][SID]/app/oracle/product/11.1.0/db_1/dbs $srvctl start database -d SID

I could have recovered from tape (we use Data Protector as the MML) but at least I got it all working. Should have been much easier though and I think I will be playing around tomorrow in a less important system.

Advertisements

3 Responses to “Recovering old spfile from autobackup”

  1. coskan said

    I think limitation is already documented John

    http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/backup.102/b14192/recov004.htm#CFAGFDJC

    But overwriting can be done after you use “to” clause on normal filesystem other than ASM because i can’t see any file handler on spfile on my test instance and I did an overwrite from the restored spfile. Maybe Oracle has got a file handler when you put it in ASM.

    • John Hallas said

      Thanks Coskan,
      Looking at the document I think the mistake I made was in startup up MOUNT rather than NOMOUNT.
      It is odd that there seems to be no examples documented of restoring an spfile that is not the latest one. so this blog make come in useful anyway.
      We have been having a discussion about this at work and tomorrow we have an opportunity to test out the exact scenario I had so hopefully we can get our documentation correct and I can update this entry to show exactly what I should have done.

      John

      To restore the server parameter file:

      1.

      If the database is up at the time of the loss of the SPFILE, connect to the target database. For example, run:

      % rman TARGET /

      If the database is not up when the SPFILE is lost, and you are not using a recovery catalog, then you must set the DBID of the target database. See “Determining your DBID” for details on determining your DBID.
      2.

      Shut down the instance and restart it without mounting. When the SPFILE is not available, RMAN starts the instance with a dummy parameter file. For example:

      RMAN> STARTUP FORCE NOMOUNT;

      3.

      Restore the server parameter file. If restoring to the default location, then run:

      RMAN> RESTORE SPFILE FROM AUTOBACKUP;

  2. fouedgray said

    Thank you for this article and comments.
    Foued

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: