I need to restore an account in the 1

[SirCharles]

I need to restore an account in the 9.2 DB to point before user/developer deleted tables in the account. Can the redo logs or archive logs be used to do this? The accident occured this morning (12/24).

The last time I exported the account was a couple weeks ago. And the last full backup was on Nov 13.

I looked for some archive/redo logs and found the following files under .../oradata/instance_name:
-rwxrwxr-x 1 oracle dba 2023424 Dec 24 12:18 control01.ctl
-rwxrwxr-x 1 oracle dba 2023424 Dec 24 12:18 control02.ctl
-rwxrwxr-x 1 oracle dba 2023424 Dec 24 12:18 control03.ctl
-rwxrwxr-x 1 oracle dba 20979712 Dec 24 03:40 cwmlite01.dbf
-rwxrwxr-x 1 oracle dba 20979712 Dec 24 03:40 drsys01.dbf
-rwxrwxr-x 1 oracle dba 144842752 Dec 24 03:40 example01.dbf
-rwxrwxr-x 1 oracle dba 26222592 Dec 24 03:40 indx01.dbf
-rwxrwxr-x 1 oracle dba 20979712 Dec 24 03:40 odm01.dbf
-rwxrwxr-x 1 oracle dba 104858112 Dec 24 12:17 redo01.log
-rwxrwxr-x 1 oracle dba 104858112 Dec 23 04:26 redo02.log
-rwxrwxr-x 1 oracle dba 104858112 Dec 24 03:09 redo03.log
-rwxrwxr-x 1 oracle dba 440410112 Dec 24 12:17 system01.dbf
-rwxrwxr-x 1 oracle dba 41951232 Dec 24 11:15 temp01.dbf
-rwxrwxr-x 1 oracle dba 10493952 Dec 24 03:40 tools01.dbf
-rwxrwxr-x 1 oracle dba 209723392 Dec 24 12:17 undotbs01.dbf
-rw-r----- 1 oracle dba 104865792 Dec 24 12:13 user02.dbf
-rwxrwxr-x 1 oracle dba 129769472 Dec 24 12:13 users01.dbf
-rwxrwxr-x 1 oracle dba 39985152 Dec 24 03:40 xdb01.dbf

 

[SantaMufasa]

SirCharles,

The best method I can think of is the following:

1) On another server with the same operating-system type as your Original Instance, restore your Nov. 13 backup files EXCEPT YOUR BACKUP CONTROL FILES. We'll refer to this new, temporary database as the Restore Instance. On this Restore Instance that uses the Nov. 13 backup files, restore the control files from today's Original Instance.
2) On the Restore Instance, do a STARTUP MOUNT;
3) RECOVER DATABASE UNTIL '<target date and time>'; (which is just before you lost your tables)
4) ALTER DATABASE OPEN RESETLOGS;
5) Then export the missing tables;
6) ftp the just-exported dump files to the original server.
7) import the dump file.

Let us know if this strategy works for you.

 

[SirCharles]

Oops. I should have said the last full export was done on Nov 19, however on closer look was done on Oct 23. No full backups of the disk sub-system are available.

 

[SantaMufasa]

SirC,

If you do not have full backups, just export dump files, then you cannot hope for a recovery of the missing tables, only a restore of the table to their state as of October 23. Sorry, man.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:24 (28Dec03) GMT, 23:24 (27Dec03) Mountain Time)

 

[SirCharles]

I copied the original oracle dirs from $ORACLE_BASE down to another server, without shutting down the original database first, set the SID on the temp server to the original database after making sure the tnsnames entry did not point to the original. The DB would not startup without error. So, tried STARTUP MOUNT; followed by RECOVER DATABASE UNTIL '<24-Dec-03>'; This gave error like invalid option '<24-Dec-03>' on until, so I tried a number of other syntaxes. Finally, RECOVER DATABASE; did appear to recover data, but I wonder if I'm just missing the syntax to give the date of 24-Dec-03 as an option on the SQL line? And if this wouldn't work?

 

[SirCharles]

I was able to find the syntax.
It would seem that if we had had a disk backup, we may have been able to restore the data in the tables.

SQL> recover database;
Seemed to work fine in restoring the database using the copied files.

However, using the current control files, and the 'time' option with the recover command,
I got following error:

SQL> recover database until time '2003-12-24:08:00:00';
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/ora/app/oracle/oradata/instance_name/system01.dbf'

 

[SirCharles]

What Oracle files, minimum, need to be backed up in order to do a restore/recover.
Maybe just stuff under oradata?

 

[SantaMufasa]

SirCharles,

You need

1) the Control files:
select name from v$controlfile;

2) Database datafiles (that represent the tablespaces):
select file_name from dba_data_files;

3) On-line redo log files:
select member from v$logfile;

Although technically not part of the database, to get the database started, you also need the init<SID>.ora parameter file.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:12 (02Jan04) GMT, 20:12 (01Jan04) Mountain Time)

 

[SirCharles]

Thanks Dave, and Merry New Year.

 

[SirCharles]

Are the archive log files necessary? It doesn't appear that they would be, if you have file backups. Perhaps they would also be necessary to run the 'recover until time' command. Or is there a different purpose for archive files? Maybe, if you don't have file backups, the archives would be useful?

Is there a different syntax to use the archive files to restore DB?

I understand that only the archive files since the last full export are needed to be able to use them? Even if export done as hot rather than cold export (cold being with DB shutdown?) Or is this not correct?

 

[SantaMufasa]

Whoa, Sir Charles, you are scrambling three different Oracle &quot;restore/recovery&quot; mechanisms.

1) Exports: a logical point-in-time dump of contents of a table, schema, or database. You cannot use the contents of a dump file to &quot;recover&quot; data, only &quot;restore&quot; data to the point at which you took the export. Exports and Imports can only occur &quot;hot&quot; (i.e., while the database is up and running). To restore dump data, you use the &quot;imp&quot; command.

Note: If your database is in &quot;ARCHIVELOG&quot; mode, then you have the option to issue the command &quot;recover database...&quot; (with options to recover completely or to some point short of complete).

If your database is NOT in &quot;ARCHIVELOG&quot; mode, then you cannot &quot;recover&quot; the database...you can only &quot;restore&quot; the database, using a full database restore to the point in time that you took a full, cold backup; or import dump files of logical exports.

2) Database recovery: Available only if database is in ARCHIVELOG mode. Provided you have an unbroken chain of (archive or on-line) redo log files from the date and time of the oldest backup file, you can use any combination of backup files and as recent as up-to-the-minute datafiles that can be recovered either up to some intervening point in time or up to the most recently commited transaction.

3) Database restore: Available in either with NOARCHIVELOG or ARCHIVELOG modes. In this case, the database reverts to the point in time of the last backup. It must use ALL the files that I mentioned in my posting 3 posts ago.

I hope this helps and is clear to you. If there is anything that is not clear, please reply.

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:58 (02Jan04) GMT, 22:58 (01Jan04) Mountain Time)

 

[SirCharles]

I guess I am still a little confused. The post you mention (Your post of Dec. 25 - BTW thanks for the Christmas day post) used the RECOVER command. So, one does a restore with the RECOVER command?

Also, where I was able to issue the recover command on the restore instance machine after copying the oracle dirs, without shutting down Oracle on original machine, and actually got the DB back to the point is was when I copied the files, I'm confused as your post implies what this might not work. Or was this a 'restore in noarchivelog mode'? Archiving appears to have been turned off - at least there is not entry for log_archive_start=true in the initinstance.ora file, and the database on the restore instance appears to have been 'recovered' up to the point in time I took the files from the original server.

So, it would appear that to get the DB restored/recovered (I'm not sure of the correct terminology here - ie. which word to use - recover or restore) back to a point in time would be a set of files copied from that time, with no archiving and no exports needed. Maybe I'm missing something here. Perhaps it's in the definition of cold backup or something. Again, I copied the files from the original machine while Oracle was running and was able to issue the 'recover' command to get the copy up and running on the restore instance. Was I not supposed to be able to do this? I'm being told by others here at work that to get a set of backup files, the Oracle instance needs to be shut down. That does not appear to be the case with a 'restore in noarchivelog mode'. Or was this a 'recover'?

 

[SirCharles]

My last post was a bit scattered. I'll try and get to the point here, though please address questions in last post if they make any sense.

Otherwise, ideally, so disaster does not occur, and to provide what I would term best practices, I would think following might work:

1. Implement Archiving so that one may specify a point in time to which DB should be recovered. Turn archiving on with the log_archive_start=true in the initinstance.ora file. Be sure and shut down and restart Oracle after this flag in place.

And

2. Have periodic backups of files you mention that are needed. Or just backup all of them. Can these be done
while DB is running, or should DB be shut down while these files are being backed up? Ie. for archiving to work.


Contingency plan: Periodic exports may or may not be needed, but nice in the event something goes wrong with file backup procedure. Can you use an import in combination with recover command where archiving is turned on to get db recovered to a specific point in time? If so, this would cut down on the number and size of archive files needed during backups. And would you only need archive files since the last full export? partial export? or am I completely off base here?