Home » RDBMS Server » Backup & Recovery » RMAN Backup and Restore (Full)
RMAN Backup and Restore (Full) [message #306319] Thu, 13 March 2008 11:50 Go to next message
Catscratch
Messages: 10
Registered: March 2008
Junior Member
Hi,

I'm an oracle newbie and got some questions about rman backup and restore and hope that you could help me. Smile

I'm using the following RMAN file for backup:
run {
  allocate channel t1 type disk;
  backup incremental level 0 database format '<pathToMyBackupFile>' ( database );
  release channel t1;
}


There I get a backupfile. Now I drop some table in the database and want to restore the backup with the following code:
run {
  allocate channel t1 type disk format '<pathToTheCreatedFile>';
  shutdown immediate;
  startup mount;
  restore database;
  recover database;
  shutdown normal;
  startup;
  release channel t1;
}


Now the console tells me a lot of information. Wink
Quote:

Recovery Manager: Release 11.1.0.6.0 - Production on Do Mrz 13 17:46:42 2008

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

Mit Ziel-Datenbank verbunden: TEST (DBID=1944543728)
Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet

RMAN> run { allocate channel t1 type disk format 'c:\temp\dbbackup\13_3_2008_16_
19_882.BAK'; shutdown immediate; startup mount; restore database; recover databa
se; shutdown normal; startup; release channel t1; }
2>
Zugewiesener Kanal: t1
Kanal t1: SID=120 Device-Typ=DISK

Datenbank geschlossen
Datenbank nicht angeschlossen
Oracle-Instance heruntergefahren

mit Zieldatenbank verbunden (nicht gestartet)
Oracle-Instance gestartet
Datenbank angeschlossen

Gesamte System Global Area 535662592 Byte

Fixed Size 1334380 Byte
Variable Size 310379412 Byte
Database Buffers 218103808 Byte
Redo Buffers 5844992 Byte

Starten restore um 13.03.08
Zugewiesener Kanal: ORA_DISK_1
Kanal ORA_DISK_1: SID=154 Device-Typ=DISK

Kanal ORA_DISK_1: Zur³ckschreiben von Datendatei-Backup Set beginnt
Kanal ORA_DISK_1: Datendatei(en) werden zum Wiederherstellen aus Backup Set ange
geben
Kanal ORA_DISK_1: Datendatei 00001 wird zu C:\PROGRAMME\ORACLE\ORADATA\TEST\SYST
EM01.DBF wiederhergestellt
Kanal ORA_DISK_1: Datendatei 00002 wird zu C:\PROGRAMME\ORACLE\ORADATA\TEST\SYSA
UX01.DBF wiederhergestellt
Kanal ORA_DISK_1: Datendatei 00003 wird zu C:\PROGRAMME\ORACLE\ORADATA\TEST\UNDO
TBS01.DBF wiederhergestellt
Kanal ORA_DISK_1: Datendatei 00004 wird zu C:\PROGRAMME\ORACLE\ORADATA\TEST\USER
S01.DBF wiederhergestellt
Kanal ORA_DISK_1: Datendatei 00005 wird zu C:\PROGRAMME\ORACLE\ORADATA\TEST\TEST
1 wiederhergestellt
Kanal ORA_DISK_1: Lesen aus Backup Piece C:\PROGRAMME\ORACLE\FLASH_RECOVERY_AREA
\TEST\BACKUPSET\2008_03_13\O1_MF_NNND0_TAG20080313T171939_3XLO4TLV_.BKP
Kanal ORA_DISK_1: Piece Handle=C:\PROGRAMME\ORACLE\FLASH_RECOVERY_AREA\TEST\BACK
UPSET\2008_03_13\O1_MF_NNND0_TAG20080313T171939_3XLO4TLV_.BKP Tag=TAG20080313T17
1939
Kanal ORA_DISK_1: Backup Piece 1 wurde wiederhergestellt
Kanal ORA_DISK_1: Restore abgeschlossen, abgelaufene Zeit: 00:02:46
Beendet restore um 13.03.08

Starten recover um 13.03.08
Kanal ORA_DISK_1 wird benutzt

Media Recovery starten
Media Recovery abgeschlossen, abgelaufene Zeit: 00:00:03

Beendet recover um 13.03.08

Datenbank nicht angeschlossen
Oracle-Instance heruntergefahren

mit Zieldatenbank verbunden (nicht gestartet)
Oracle-Instance gestartet
Datenbank angeschlossen
Datenbank ge÷ffnet

Gesamte System Global Area 535662592 Byte

Fixed Size 1334380 Byte
Variable Size 310379412 Byte
Database Buffers 218103808 Byte
Redo Buffers 5844992 Byte

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: Fehler bei release Befehl auf 03/13/2008 17:51:16
RMAN-06012: Kanal: t1 nicht zugewiesen

Recovery Manager abgeschlossen.



But after the restore is completed, the dropped table isn't in the database. So nothing in the database changed. Everything looks like before.

I'm sure, that I've got the wrong approach. But how should I do it right?

Thanks a lot for help.
Best regards,
Ronny.
Re: RMAN Backup and Restore (Full) [message #306322 is a reply to message #306319] Thu, 13 March 2008 11:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Your restore basically played back the drop table command. If you are on 10g, you can do a FLASHBACK TABLE and/or if on 10g and below, you can do a Point-in-time recovery.

Sorry, just noticed you are on 11g. The same holds true for 10g & 11g, however, 11g may have additional features. I haven't had a chance to do B&R with 11g yet.

[Updated on: Thu, 13 March 2008 11:56]

Report message to a moderator

Re: RMAN Backup and Restore (Full) [message #306324 is a reply to message #306319] Thu, 13 March 2008 12:03 Go to previous messageGo to next message
Catscratch
Messages: 10
Registered: March 2008
Junior Member
Hi,

but I want to backup and restore the whole database without any knowlegde of the tablespaces. With FLASHBACK TABLE I have to specify the table I want to use...does I'm right?
Re: RMAN Backup and Restore (Full) [message #306340 is a reply to message #306324] Thu, 13 March 2008 15:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Catscratch wrote on Thu, 13 March 2008 13:03
but I want to backup and restore the whole database without any knowlegde of the tablespaces.

Don't understand what you mean by that? In your original post, you said "the dropped table isn't in the database", so I assume you wanted the table to be there.

Catscratch wrote on Thu, 13 March 2008 13:03
With FLASHBACK TABLE I have to specify the table I want to use...does I'm right?

Of course with FLASHBACK TABLE you have to specify the table name.

What is it that you are trying to accomplish?
Re: RMAN Backup and Restore (Full) [message #306442 is a reply to message #306319] Fri, 14 March 2008 04:13 Go to previous messageGo to next message
Catscratch
Messages: 10
Registered: March 2008
Junior Member
Ok, I try to explain it a little bit more. Wink

E.g. I have a database. Let's call it "test". In this database there are different tablespaces. Like "users", "products" and so on. Now I want to backup the whole database. That means all system tablespaces and my own ones (system, sysaux, users, test, ...).

So I use rman and the scriptfile from above to backup all these tablespaces into one backupfile. Now I want to test the restore process. So I remove one table or one entry from a tablespace to "simulate" a crash or another error. And then I do a restore with the script above. The restore process looks good, but at the end of it, the database is like before. That means the removed entry isn't restored. It is still not present in the tablespace. So for me it looks like the restore process changed nothing in the database.

I hope you understood. Smile

Best regards.
Re: RMAN Backup and Restore (Full) [message #306468 is a reply to message #306442] Fri, 14 March 2008 07:22 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
YEP...and RMAN performed exactly as it should. As I mentioned above, the drop table action you performed BEFORE the restore was replayed with the RESTORE. For example, you dropped the table at 10:00am and do the restore at 11:00am, then without a point-in-time recovery, RMAN is going to replay all the database actions that occurred up till 11:00am and therefore, the drop table will get replayed. To get the dropped table backup, you have to do a point-in-time recovery or FLASHBACK TABLE.
Check Recovering from a Dropped Table for the details.

HTH
Re: RMAN Backup and Restore (Full) [message #306487 is a reply to message #306468] Fri, 14 March 2008 08:45 Go to previous messageGo to next message
Catscratch
Messages: 10
Registered: March 2008
Junior Member
At first: Thanks for your patience.

ebrian wrote on Fri, 14 March 2008 07:22
YEP...and
RMAN performed exactly as it should.


Yeah, I think so. Smile

ebrian wrote on Fri, 14 March 2008 07:22

As I mentioned above, the drop table action you performed BEFORE the restore was replayed with the RESTORE. For example, you dropped the table at 10:00am and do the restore at 11:00am, then without a point-in-time recovery, RMAN is going to replay all the database actions that occurred up till 11:00am and therefore, the drop table will get replayed.


Ok, I understand this. But why RMAn don't only use my backupfile? For example, I created the fullbackupfile at 9:00am and drop the table at 10:00am. Then at 11:00am I want to restore die backupfile from 9:00am. But RMAN restores all the data until 11:00am. But it only should use my backupfile. With point-in-time recovery I have to specify a timepoint. But I don't know when the backupfile was created. Ok, I could read the file information like creation date and so on, but is there a way to tell rman only to use this one backupfile?

If RMAN restores all the data until 11:00am, so it stores backupinformation otherwhere too (not in my backupfile). RMAN shouldn't use this additional information.

I want to realize something like the MySQLDump function where I can create a full dump. Later I can set the complete database to the state of a given dump. There I don't have to specify a timepoint. I only use the mysqldump function and the dumpfile as parameter and the result is the database which was frozen in the dumpfile.

[Updated on: Fri, 14 March 2008 08:46]

Report message to a moderator

Re: RMAN Backup and Restore (Full) [message #306502 is a reply to message #306487] Fri, 14 March 2008 09:00 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Catscratch wrote on Fri, 14 March 2008 09:45

Ok, I understand this. But why RMAn don't only use my backupfile? For example, I created the fullbackupfile at 9:00am and drop the table at 10:00am. Then at 11:00am I want to restore die backupfile from 9:00am. But RMAN restores all the data until 11:00am. But it only should use my backupfile. With point-in-time recovery I have to specify a timepoint. But I don't know when the backupfile was created. Ok, I could read the file information like creation date and so on, but is there a way to tell rman only to use this one backupfile?

That would be a point-in-time recovery and if you do this, you'll essentially be rolling back the whole database to recover the one dropped table. An alternative would be to do a point-in-time recovery on another system (auxiliary database) and export the table and then import the table in the primary database.

Catscratch wrote on Fri, 14 March 2008 09:45

If RMAN restores all the data until 11:00am, so it stores backupinformation otherwhere too (not in my backupfile). RMAN shouldn't use this additional information.

I want to realize something like the MySQLDump function where I can create a full dump. Later I can set the complete database to the state of a given dump. There I don't have to specify a timepoint. I only use the mysqldump function and the dumpfile as parameter and the result is the database which was frozen in the dumpfile.

Without doing a point-in-time recovery, RMAN is going to utilize the archive & redo logs to bring the database current. For a logical "backup" of your database, you can consider doing an export. An export can be configured to provide you a consistent snapshot of your database at particular time. An export should really only be used as a supplement to a physical backup of your database.
Re: RMAN Backup and Restore (Full) [message #306993 is a reply to message #306502] Mon, 17 March 2008 08:07 Go to previous messageGo to next message
Catscratch
Messages: 10
Registered: March 2008
Junior Member
Hi,

thanks for your help. I gathered some information about the export tool and most of the topics I found, say that the export tool should only be used for smaller databases and that rman is the better alternative. Smile

So I tried following:
At first I created the full backup with rman. Than I removed the database and created a new one to simulate a reinstallation of oracle. At the next step I did a restore with rman, but got the following error:

Recovery Manager: Release 11.1.0.6.0 - Production on Mo Mrz 17 14:06:32 2008

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

Bei Zieldatenbank angemeldet: TEST (DBID=1947751245, nicht ge÷ffnet)
Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet

RMAN> run { allocate channel t1 type disk format 'c:\temp\dbbackup\13_3_2008_16_
19_882.BAK'; shutdown immediate; startup mount; restore database; recover databa
se; shutdown normal; startup; release channel t1; }
2>
Zugewiesener Kanal: t1
Kanal t1: SID=156 Device-Typ=DISK

Datenbank nicht angeschlossen
Oracle-Instance heruntergefahren

mit Zieldatenbank verbunden (nicht gestartet)
Oracle-Instance gestartet
Datenbank angeschlossen

Gesamte System Global Area     535662592 Byte

Fixed Size                     1334380 Byte
Variable Size                155190164 Byte
Database Buffers             373293056 Byte
Redo Buffers                   5844992 Byte

Starten restore um 17.03.08
Zugewiesener Kanal: ORA_DISK_1
Kanal ORA_DISK_1: SID=152 Device-Typ=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: Fehler bei restore Befehl auf 03/17/2008 14:07:05
RMAN-06026: Einige Ziele nicht gefunden - Recovery wird abgebrochen
RMAN-06023: Kein Backup bzw. keine Kopie der Datendatei 4 zum Wiederherstellen gefunden
RMAN-06023: Kein Backup bzw. keine Kopie der Datendatei 3 zum Wiederherstellen gefunden
RMAN-06023: Kein Backup bzw. keine Kopie der Datendatei 2 zum Wiederherstellen gefunden
RMAN-06023: Kein Backup bzw. keine Kopie der Datendatei 1 zum Wiederherstellen gefunden

Recovery Manager abgeschlossen.


What's my fault?

Thx again.
Re: RMAN Backup and Restore (Full) [message #307011 is a reply to message #306993] Mon, 17 March 2008 09:06 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If your intentions are to practice disaster recovery, you should read over Performing Disaster Recovery.

This will walk you through all the necessary steps.
Re: RMAN Backup and Restore (Full) [message #307614 is a reply to message #306319] Wed, 19 March 2008 05:09 Go to previous message
Catscratch
Messages: 10
Registered: March 2008
Junior Member
Thank you.

Exp/Imp is working fine for me.
Only for differential and incremental backups I have to us another strategie. But there I'll try rman again. Smile

Best regards.
Previous Topic: the way about rac recovery
Next Topic: How does RMAN recovery with its retention policy?
Goto Forum:
  


Current Time: Sat Dec 14 17:36:09 CST 2024