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

From: David Barbour <david.barbour1_at_gmail.com>
Date: Sun, 9 Nov 2014 15:55:08 -0600
Message-ID: <CAFH+iffycRNPWrsnCrBPeYMGaGLQPG-SxEw1WEM0s6bbDp3Z8A_at_mail.gmail.com>



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_at_gmail.com> 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?
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Nov 09 2014 - 22:55:08 CET

Original text of this message