Home » RDBMS Server » Backup & Recovery » Restore database from Backup. (Solaris, 10.2g)  () 2 Votes
Restore database from Backup. [message #342914] Mon, 25 August 2008 08:41 Go to next message
dulal66
Messages: 1
Registered: July 2008
Location: Bangladesh
Junior Member
I have rman backupset of database and have a controlfile snapshot. Can I restore my database?


Without controlfile autobackup can I restore my database?


Whenever I give command RESTORE SPFILE or RESTORE CONTROLFILE from snapshot controlfile it errors me no autobackup found. So is there any way I perform get back database from backup?
Re: Restore database from Backup. [message #342918 is a reply to message #342914] Mon, 25 August 2008 09:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post the exact screenshot/session logs instead of explaining what you did.

Re: Restore database from Backup. [message #342981 is a reply to message #342914] Mon, 25 August 2008 13:34 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
RMAN>set dbid Xxxxxxxxx
RMAN>startup nomount
RMAN> restore spfile to pfile '/oracl.ora' from '/oradata2/1_.bkp';
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Re: Restore database from Backup. [message #343001 is a reply to message #342981] Mon, 25 August 2008 17:01 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
from your RMAN prompt can you type in the command

show all;

and post the result back.

regards

Alan
Re: Restore database from Backup. [message #343002 is a reply to message #342914] Mon, 25 August 2008 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

6172, 1, "no autobackup found or specified handle is not a valid copy or piece"
// *Cause:  A restore could not proceed because no autobackup was found or
//          specified handle is not a valid copy or backup piece.
//          In case of restore from autobackup, it may be the case that a
//          backup exists, but it does not satisfy the criteria specified in
//          the user's restore operands.
//          In case of restore from handle, it may be the handle is not a backup
//          piece or controflile copy. In may be that it does not exist.
// *Action: Modify autobackup search criteria or verify the handle.


>I have rman backupset of database and have a controlfile snapshot
you believe this to be true, but offer no proof.
Re: Restore database from Backup. [message #343063 is a reply to message #342914] Tue, 26 August 2008 02:47 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
Could not get you. Can I restore database with snapshot control file and backupset?

Note dont have autobackup of controlfile.
Re: Restore database from Backup. [message #343332 is a reply to message #342914] Wed, 27 August 2008 00:06 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

As far as I know if you don't have autobackup of controlfile you can't perform disaster recovery.
Re: Restore database from Backup. [message #343620 is a reply to message #343332] Wed, 27 August 2008 12:00 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Arju wrote on Wed, 27 August 2008 01:06
As far as I know if you don't have autobackup of controlfile you can't perform disaster recovery.

Not true !!

It's possible to perform disaster recovery without any backup of a controlfile.
Re: Restore database from Backup. [message #343649 is a reply to message #342914] Wed, 27 August 2008 14:10 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Then you have to create manually controlfile as well as spfile?
Re: Restore database from Backup. [message #343650 is a reply to message #343649] Wed, 27 August 2008 14:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Arju wrote on Wed, 27 August 2008 12:10
Then you have to create manually controlfile as well as spfile?


no to both.
Both are created by Oracle from a process initiated by person doing the recovery.

For me a "manually created" file is made with EDITOR of your choosing.
Re: Restore database from Backup. [message #343762 is a reply to message #342914] Thu, 28 August 2008 01:29 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

From documentation,
Quote:
To perform a disaster recovery, the minimum required set of backups is backups of some datafiles, some archived redo logs generated after the time of the backup, and at least one autobackup of the control file.
Re: Restore database from Backup. [message #343808 is a reply to message #343762] Thu, 28 August 2008 04:13 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Oracle Documentation is correct but when autocontrolfile backup is "OFF" then we still perform disater recovery.



RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

consider:

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: TEST (DBID=1962090068)

RMAN> run
2> {
3> backup database;
4> sql 'alter system switch logfile';
5> }

Starting backup at 28-AUG-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBF

input datafile fno=00003 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBF

input datafile fno=00002 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DB
F
input datafile fno=00004 name=F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 28-AUG-08
channel ORA_DISK_1: finished piece 1 at 28-AUG-08
piece handle=F:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\01JP5UKE_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
[B]including current controlfile in backupset
including current SPFILE in backupset[/B]
channel ORA_DISK_1: starting piece 1 at 28-AUG-08
channel ORA_DISK_1: finished piece 1 at 28-AUG-08
piece handle=F:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\02JP5UKT_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-AUG-08

sql statement: alter system switch logfile


-------------------xxxxxxxxxxxxxxxxx--------------

C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Aug 28 12:51:17 2008

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

SQL> conn / as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> host del F:\oracle\product\10.1.0\oradata\test\*.*
F:\oracle\product\10.1.0\oradata\test\*.*, Are you sure (Y/N)? Y

SQL> host del F:\oracle\product\10.1.0\db_1\database\SPTEST*.ora

--------------------------xxxxxxxxxxxxxxxxxxxxxxxxx----------

RMAN> set dbid=1962090068

executing command: SET DBID

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'F:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE
\INITTEST.ORA'

trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area      88080384 bytes

Fixed Size                      788116 bytes
Variable Size                 69466476 bytes
Database Buffers              16777216 bytes
Redo Buffers                   1048576 bytes


RMAN> restore SPFILE from "F:\oracle\product\10.1.0\Db_1\database\02JP5UKT_1_1";


Starting restore at 28-AUG-08
using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: F:\oracle\product\10.1.0\Db_1\database\02J
P5UKT_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28-AUG-08

RMAN> restore CONTROLFILE from "F:\oracle\product\10.1.0\Db_1\database\02JP5UKT_
1_1";

Starting restore at 28-AUG-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=F:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\CTL1TEST.ORA
Finished restore at 28-AUG-08

RMAN> shutdown immediate;

Oracle instance shut down

-----------------xxxxxxxxxxxxxx--------------

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create pfile from spfile;

File created.

SQL> --EDIT INIT.ORA FILE AND UPDATE CONTROLFILE PARAMETER
SQL> create spfile from pfile;

File created.
--------------------xxxxxxxxxxxxxxxxx-----------

RMAN> restore database;

Starting restore at 28-AUG-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBF
restoring datafile 00002 to F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF
restoring datafile 00003 to F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBF
restoring datafile 00004 to F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=F:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\01JP5UKE_1_1 tag=TAG20080828
T125022
channel ORA_DISK_1: restore complete
Finished restore at 28-AUG-08

-----------------------xxxxxxxxxxxxxxxxxx------------------
SQL> conn / as sysdba
Connected.
SQL> set autorecovery on
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 441017 generated at 08/28/2008 12:50:22 needed for thread 1
ORA-00289: suggestion :
F:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00005_0663943637.001
ORA-00280: change 441017 for thread 1 is in sequence #5


ORA-00279: change 441028 generated at 08/28/2008 12:50:40 needed for thread 1
ORA-00289: suggestion :
F:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00006_0663943637.001
ORA-00280: change 441028 for thread 1 is in sequence #6
ORA-00278: log file
'F:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00005_0663943637.001' no longer needed
for this recovery


ORA-00308: cannot open archived log
'F:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00006_0663943637.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> alter database open resetlogs ;

Database altered.


-----------xxxxxxxxxxxxxxxxxx---------------------------

[Updated on: Thu, 28 August 2008 04:15]

Report message to a moderator

Re: Restore database from Backup. [message #343817 is a reply to message #342914] Thu, 28 August 2008 04:39 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
It's possible to perform disaster recovery without any backup of a controlfile.


I argue this point and doubt. Taj you showed an example in which there is backup of controlfile. I am telling only backupset available but no backup of controlfile.
Re: Restore database from Backup. [message #343870 is a reply to message #343817] Thu, 28 August 2008 06:49 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

As per OP doubt
Quote:
Without controlfile autobackup can I restore my database?

Yes it is possible. See above example.

Quote:
Quote:
It's possible to perform disaster recovery without any backup of a controlfile.

As per your doubt.

Yes still it is possible to recover the database and perform disater recovery.

[Updated on: Thu, 28 August 2008 06:50]

Report message to a moderator

Re: Restore database from Backup. [message #344054 is a reply to message #343817] Thu, 28 August 2008 12:43 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Arju wrote on Thu, 28 August 2008 05:39
Quote:
It's possible to perform disaster recovery without any backup of a controlfile.


I argue this point and doubt. Taj you showed an example in which there is backup of controlfile. I am telling only backupset available but no backup of controlfile.

You can argue this point all you want. I've performed several disaster recoveries WITHOUT ANY controlfile backup.
Re: Restore database from Backup. [message #344596 is a reply to message #342914] Sat, 30 August 2008 22:39 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
Hi Guys

I have one doubt here.
How can we get the DBID as mentioned in the example.
If we loose everything except datafile backup set.
Can we get the dbid from backupset?


RMAN> set dbid=1962090068

executing command: SET DBID

thanks
Mehtab
Re: Restore database from Backup. [message #344598 is a reply to message #342914] Sat, 30 August 2008 23:09 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Ok Brian I agree. But I am surprised about how database restoring can be done without any controlfile where I don't know about datafile location, database name, dbid etc.
Re: Restore database from Backup. [message #344610 is a reply to message #344598] Sun, 31 August 2008 00:30 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

...But I am surprised about how database restoring can be done without any controlfile where I don't know about datafile location, database name, dbid etc....

Don't be surprised.

If you have backupset of datafile then you can get...
1. Controlfile 2.SPFILE or 3. DBID.

And above three thing required to mount the database.

and backupset is for open.

Re: Restore database from Backup. [message #344614 is a reply to message #342914] Sun, 31 August 2008 00:54 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
And above three thing required to mount the database.

But how I can get controlfile and spfile?
I only have backupset of datafile. Previously I told I have lost backupset of controlfile and spfile.

More specifically Taj I have lost F:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\02JP5UKT_1_1 piece as you shown in your example.
Re: Restore database from Backup. [message #345501 is a reply to message #344614] Wed, 03 September 2008 21:04 Go to previous messageGo to next message
andydba
Messages: 34
Registered: September 2008
Member

Hi,

You said, you have autobackup, which means your system01.dbf must be in the backupset.

Your backupset will have control file as oracle takes backup of control file whenever it is backing up datafile 1 (system01.dbf) even if the controlfile autobackup is off.

Try following:

SET DBID=<yourdbid>

startup nomount;

restore controlfile from <give backupset filename with complete path >

restore spfile from <give backupset filename with complete path >

shtudown;

startup mount;

restore database;

recover database (if you have archive logs) otherwise open database wiht resetlogs;
Re: Restore database from Backup. [message #346156 is a reply to message #342914] Sat, 06 September 2008 12:04 Go to previous message
ranishoura
Messages: 1
Registered: September 2008
Junior Member
Hi,

I had a bad disaster and I was able to recover only one user.dbf file, is it possible to restore data from that point??

Please note that I'm using 8i, I'm not an oracle techie but I'm in urgent need for a help

[Updated on: Sat, 06 September 2008 12:06]

Report message to a moderator

Previous Topic: RMAN CROSS PLATFORM DUPLICATE
Next Topic: Check RMAN Catalog
Goto Forum:
  


Current Time: Fri Dec 09 04:24:33 CST 2016

Total time taken to generate the page: 0.16197 seconds