Re: Restoring SPFILE on RAC with RMAN - Not working - Solved

  • From: David Barbour <david.barbour1@xxxxxxxxx>
  • To: oracle-l mailing list <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 9 Nov 2014 15:55:08 -0600

Even a blind squirrel .......

What bothers me is why the catalog still couldn't connect. It's not
critical right now, but it could be at any time and if someone can figure
out what I was missing, it would be much appreciated.  Luckily I has a
backup on disk.  I did try the recover from autobackup to get the file from
this morning, but it was on tape and I got the message RMAN-06172: no
AUTOBACKUP found or specified handle is not a valid copy or piece. Thinking
about this as I write, I probably should have configured an auxiliary
channel? But it didn't find the disk-based backup either:channel
ORA_DISK_1: no AUTOBACKUP in 7 days found <- This is a lie, I know there
are disk backups newer than 7 days.   Anyway, here's what ended up
happening.

I changed the listener.ora host definitions to IP  addresses and pointed
them all to the vip with the corresponding port number instead of the
scan.  When I started RMAN this time around, I got a slightly different
message:
oracle:rchr1t01  D23 /oracle/D23/112/network/admin>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Nov 9 15:24:51 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights
reserved.

connected to target database: D23 (not mounted)

But after connecting to the catalog I ended up with the same TNS failure.
I did have the thought that perhaps the connection failure was FROM the
catalog TO the database, so I had put the database definitions into the
catalogs tnsnames.ora file, but evidently that wasn't the problem.

So I totally eschewed the catalog and

oracle:rchr1t01  D23 /oracle/D23/112/network/admin>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Nov 9 15:27:00 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights
reserved.

connected to target database (not started)

RMAN> set dbid = 452985673

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+D23DATA01/D23/spfileD23.ora'
ORA-17503: ksfdopn:2 Failed to open file +D23DATA01/D23/spfileD23.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +D23DATA01/d23/spfiled23.ora
ORA-15173: entry 'spfiled23.ora' does not exist in directory 'd23'
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     367439872 bytes

Fixed Size                     2228464 bytes
Variable Size                205524752 bytes
Database Buffers             155189248 bytes
Redo Buffers                   4497408 bytes

RMAN> restore spfile from
'/oracle/D23/112/dbs/D23xxxxx_Fc-452985673-20141105-05';

Starting restore at 09-NOV-14 15:28:05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=662 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP
/oracle/D23/112/dbs/D23xxxxx_Fc-452985673-20141105-05
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 09-NOV-14 15:28:06



On Sun, Nov 9, 2014 at 1:30 PM, David Barbour <david.barbour1@xxxxxxxxx>
wrote:

> Undoubtedly something I've mis-typed, which caused the problem in the
> first place.
>
> Oracle 11.2.0.3, RHEL 6.3
>
> I messed up the spfile on a database.  No problem, I have a backup.  But
> ....
>
> RMAN> startup force nomount;
>
> startup failed: ORA-01078: failure in processing system parameters
> ORA-01565: error in identifying file '+D23DATA01/D23/spfileD23.ora'
> ORA-17503: ksfdopn:2 Failed to open file +D23DATA01/D23/spfileD23.ora
> ORA-15056: additional error message
> ORA-17503: ksfdopn:2 Failed to open file +D23DATA01/d23/spfiled23.ora
> ORA-15173: entry 'spfiled23.ora' does not exist in directory 'd23'
> ORA-06512: at line 4
>
> starting Oracle instance without parameter file for retrieval of spfile
> Oracle instance started
>
> Total System Global Area     367439872 bytes
>
> Fixed Size                     2228464 bytes
> Variable Size                205524752 bytes
> Database Buffers             155189248 bytes
> Redo Buffers                   4497408 bytes
>
> RMAN> RESTORE SPFILE FROM AUTOBACKUP;
>
> Starting restore at 09-NOV-14 13:13:52
> RMAN-00571: ===========================================================
> RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
> RMAN-00571: ===========================================================
> RMAN-03002: failure of restore command at 11/09/2014 13:13:52
> RMAN-12001: could not open channel ORA_DISK_1
> RMAN-10008: could not create channel context
> RMAN-10003: unable to connect to target database
> ORA-12514: TNS:listener does not currently know of service requested in
> connect descriptor
>
>
> Here's the tnsnames.ora for the instance:
>
> D23 =
>   (DESCRIPTION =
>     (ADDRESS = (PROTOCOL = TCP)(HOST = rchr1t-scan.lennoxintl.com)(PORT =
> 1525))
>     (CONNECT_DATA = (SERVICE_NAME = D23)(GLOBAL_NAME = D23)(UR=A)
>                     (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))
>     )
>   )
>
> D23001 =
>   (DESCRIPTION =
>     (ADDRESS = (PROTOCOL = TCP)(HOST = rchr1t01-vip.lennoxintl.com)(PORT
> = 1521))
>     (CONNECT_DATA = (SERVICE_NAME = D23001)(GLOBAL_NAME = D23)(SERVER =
> DEDICATED)(UR=A))
>   )
>
>
> When I nomount the DB, the listerner services show:
>
> Service "D23" has 1 instance(s).
>   Instance "D23", status UNKNOWN, has 1 handler(s) for this service...
>     Handler(s):
>       "DEDICATED" established:0 refused:0
>          LOCAL SERVER
> Service "DUMMY" has 1 instance(s).
>   Instance "D23001", status BLOCKED, has 1 handler(s) for this service...
>     Handler(s):
>       "DEDICATED" established:0 refused:0 state:ready
>          LOCAL SERVER
>
> What the heck?  I've even tried putting a SID_LIST_LISTENER stanza in the
> ASM listener.ora file(with lots of variations):
>
> SID_LIST_LISTENER=
> (SID_LIST=
>  (SID_DESC=
>   (GLOBAL_DBNAME=D23)
>   (SID_NAME=D23)(UR=A)
>   (SERVICE_NAME=D23001)(UR=A)
>   (ORACLE_HOME=/oracle/D23/112)))
>
> Any ideas?
>

Other related posts:

  • » Re: Restoring SPFILE on RAC with RMAN - Not working - Solved - David Barbour