oracle 9i RMAN restoration question

[Patrick_129]

guys,

I created a table and input some customers informations (abt 10 records) into this table and backup it via Netbackup successfully.
I backup using the tempplate created by Netbackup, it backup the archive logs and controlfile too.

Then i deleted the 10 records away using sqlplus command and tried a restore via Netbackup, the Netbackup status reflected the restoration was successful but when i checked, all the 10 records did not restored...

This is how i wrote the restore script....
run{
connect target ...bla bla bla
connect catlago ...bla bla bla
allocate channel ....bla bla bla
send 'NB_ORA_SERV...'bla bla bla

restore database;
recover database;
release channle t1;
}

Any idea why???

thnaks

[Patti Johnson · Accepted solution]

You need to restore the database to a point in time before you deleted the rows. But it would take the entire database back to that point. Oracle does not allow you to just restore rows deleted from one table.
If you have the need to recover data from one table and not roll your entire database back to a point in time before a delete or corruption then you need to follow the procedure for Tablespace Point In Time Recovery documented in Metalink note 109979.1.

Patti

[Chris Xu]

To solve your problem, you need to use oracle' export/import utility to backup in addition to the Netbackup, which can be a table, all tables of a user or all tables in the database. With the export dump backup, you can restore a particular table in need later on. That will not change the rest of the db.

Chris.

[Yogeeraj_1]

hi patrick!

the only way out is to do a point in time recovery.

you may wish to also have a look at the metalink note: 94114.1 (Backup and Recovery Scenarios)

if you need any further help, please do let us know...

regards
Yogeeraj

[Edgar_8]

Hi Yogee,

Do you know of any good Oracle material one could peruse that shows one how to implement an RMAN Backup/Recovery strategy? With all due respect to Oracle Corp., but the documentation is long winded.

Thanks in advance!

[Yogeeraj_1]

hi edgar,

i still find the Oracle "Recovery Manager Userâ s Guide and Reference" the best documentation available.

Metalink also provides a lot of interesting documents. The latest is used is the "73974.1 - RMAN: Restoring an RMAN backup to another note"

Backup and recovery is something very serious! You can't afford to take any chances and MUST make sure that you test your recovery. If possible, you should regularly practice your recovery.

My advise is to work with the RMAN experts found at Oracle Metalink. They have helped me several times and are very professional.

regards
Yogee

[Augusto Castro]

Here is an idea: assuming that you explicitly committed the transaction, it is possible that the REDO LOGS that recorded your transaction are not in your backup. RMAN doesnâ t backup current REDO LOG files. You need to include in your backup script a SQL statement to switch the REDO LOGS. This will ensure that the transaction is archived and included in your backup.

I have found very convenient to restore data using FLASHBACK QUERIES when it is a few records or a given table and you donâ t have an export dump file. This is easier than restoring with RMAN. The limitation is that the transaction must be recent enough to still be in the UNDO areas (rollback segments). If you try to restore a transaction that occurred 24 hours ago most likely you will not be able. The older the transaction the less chances to recover with FLASHBACK QUERY. This feature is available in Oracle 9i.
A

[Patrick_129]

Thanks Guys...

Let me get this right...

RMAN can only restore tablespace, datafile and database, it can't do table or record restoration...
So in order for me to get back the 10 records which i created in the table, i must restore the entire tablespace or even database and do a point in time restoration before i drop the records... is that right???

:)

[Volker Borowski]

Yes, your assumption is correct.

BTW, do not consider an export to be any kind of backup.
You can not recover forward from an import.
Most likely an export will be inconsistent, if taken while the DB is up and running.

You can use an export only to retrive specific data, that might need postprocessing.

Volker

[Brian Crabtree]

You can get a consistant export using the "CONSISTENT=Y" parameter, however this will require that your database be able to store all changes made to the tables since the start of the export, or you will get ORA-01555 errors. Exports are not generally a good tool to use as a complete backup solution, however they can be useful for doing single table restores in the event of user error, or when dealing with static data.

Thanks,

Brian

[Yogeeraj_1]

hi,

yes.

Basically, the TSPITR starts a mini instance (aux instance it calls it) and does a PITR of
the affected tablespace. Then, "Recover" the 10 records. Finally, trash this mini instance.

Should not be that complicated...

regards
yogeeraj

[Patrick_129]

Hi guys,

I managed to restore all the 10 rows i created using point in time recovery.
Thanks for being so helpful.

My next challenge will be restoring from a Oracle RAC environment to a standalone database environment.

Thanks

patrick