OTN Discussion Forums : restore spfile ...

John Carew

Posts: 1,614
Registered: 06/13/07

restore spfile
Posted: May 27, 2008 3:11 AM

Reply

Hi
How can I restore spfile from rman backupset?

Paul M.

Posts: 8,652
Registered: 12/26/02

Re: restore spfile
Posted: May 27, 2008 4:14 AM

in response to: John Carew

Reply

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/recov004.htm#BRBSC15339

AF

Posts: 106
Registered: 01/22/08

Re: restore spfile
Posted: May 27, 2008 12:39 PM

in response to: John Carew

Reply

Have you read the link above?

Have a question yet?

You had tried to read the documentation at least?

AF

John Carew

Posts: 1,614
Registered: 06/13/07

Re: restore spfile
Posted: May 28, 2008 3:19 AM

in response to: AF

Reply

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> RESTORE SPFILE FROM AUTOBACKUP;

Starting restore at 28/05/2008
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/28/2008 13:23:22
RMAN-06171: not connected to target database

RMAN> startup nomount;

Oracle instance started

Total System Global Area 209715200 bytes

Fixed Size 1286580 bytes
Variable Size 71306828 bytes
Database Buffers 134217728 bytes
Redo Buffers 2904064 bytes

RMAN> RESTORE SPFILE FROM AUTOBACKUP;

Starting restore at 28/05/2008
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=123 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/28/2008 13:16:45
RMAN-06564: must use the TO clause when the instance is started with SPFILE

I dont want to use TO clause, I want to restore spfile to default location.

piontekdd_2

Posts: 522
Registered: 05/16/00

Re: restore spfile
Posted: May 28, 2008 4:55 AM

in response to: John Carew

Reply

To do that, you can't start up nomount using the default spfile. Your database is in NOMOUNT using the spfile you are trying to restore over.

Either remove the default spfile (rename if you prefer.), or start up the instance with a pfile.

AF

Posts: 106
Registered: 01/22/08

Re: restore spfile
Posted: May 28, 2008 4:57 AM

in response to: John Carew

Reply

What use is usually direct the local restoring controlfile by using the clause TO.
Know otherwise, I hope that helps head.

RMAN> startup nomount

instance oracle start

Total da -rea Global do Sistema 327155712 bytes

Fixed Size 1290424 bytes
Variable Size 155193160 bytes
Database Buffers 167772160 bytes
Redo Buffers 2899968 bytes

RMAN> restore spfile to pfile 'C:\oracle\product\10.2.0\admin\orcl3\pfile\INITor
cl.ORA' from 'C:\bkp_oracle\controlfile\CONTROL_C-1148085835-20080214-00';

AF

John Carew

Posts: 1,614
Registered: 06/13/07

Re: restore spfile
Posted: May 28, 2008 5:57 AM

in response to: piontekdd_2

Reply

When I restore spfile from autobackup I cannot open the database with spfile

SQL> startup nomount pfile= 'C:\oracle\app\oracle\admin\XE\pfile\initXE.ora';
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1286580 bytes
Variable Size 71306828 bytes
Database Buffers 134217728 bytes
Redo Buffers 2904064 bytes

RMAN> restore spfile from autobackup;

Starting restore at 28/05/2008
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK

recovery area destination: C:\oracle\app\oracle\flash_recovery_area
database name (or database unique name) used for search: XE
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\AUTOBACKUP\2008_05_28\O1_MF_S_655905960_43T8
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28/05/2008

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Ãar May 28 16:05:30 2008

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

Connected to an idle instance.

SQL> startup nomount;
ORA-03113: end-of-file on communication channel
SQL> startup;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

SQL> startup nomount pfile= 'C:\oracle\app\oracle\admin\XE\pfile\initXE.ora';
ORACLE instance started.

hkchital

Posts: 4,974
Registered: 11/06/98

Re: restore spfile
Posted: May 28, 2008 6:02 PM

in response to: John Carew

Reply

After restoring the SPFILE, did you issue a SHUTDOWN.

The next startup should then attempt to read the restorted SPFILE as long
as you do not specify a wrong "pfile=...."

John Carew

Posts: 1,614
Registered: 06/13/07

Re: restore spfile
Posted: May 28, 2008 10:40 PM

in response to: hkchital

Reply

yes
I issued a shutdown immediate.

orawarebyte

Posts: 3,644
Registered: 10/02/00

Re: restore spfile
Posted: May 28, 2008 11:01 PM

in response to: John Carew

Reply

what happened if you try

startup nomount spfile=<yoursspfile>

Check yours alert log file what it says?

Khurram

John Carew

Posts: 1,614
Registered: 06/13/07

Re: restore spfile
Posted: May 28, 2008 11:05 PM

in response to: orawarebyte

Reply

SQL> startup nomount spfile='C:\oracle\app\oracle\product\10.2.0\server\dbs\SPFILEXE.ORA';

SP2-0714: invalid combination of STARTUP options

orawarebyte

Posts: 3,644
Registered: 10/02/00

Re: restore spfile
Posted: May 28, 2008 11:30 PM

in response to: John Carew

Reply

sorry my mistake database start with the spfile by default

stratup nomount pfile=<yourspfile>
create pfile from spfile
shutdown immediate
startup pfile=<yourspfile>

After that check the alert log file if you across the same error.

Khurram

John Carew

Posts: 1,614
Registered: 06/13/07

Re: restore spfile
Posted: May 28, 2008 11:37 PM

in response to: orawarebyte

Reply

I can open the database by new pfile that is created from spfile.
The problem is solved.

orawarebyte

Posts: 3,644
Registered: 10/02/00

Re: restore spfile
Posted: May 29, 2008 12:03 AM

in response to: John Carew

Reply

The problem is solved.

The problem is not solved until you dont able to open yours database with spfile.After opening the database with newly created pfile from spfile you should also be able to open the database with spfile.

You will not always open the database by specifying pfile cause by default database opened with spfile.

Try again after opening the database with newly created pfile

SQL>shutdown immediate
SQL>startup

Please also check yours alert log file for the error ORA-03113.

Khurram