by copying over the redo logs.... you've lost all records of that table being created,.
you need the current control files, current redo logs, and old data files in order to recover to a point before the table was created, but after the cold backup was taken.
by recovering all files,including redo logs, you've essentially put the entire database back to where it was when the cold backup was taken.
>>> "Ruth Gramolini" <rgramolini@(protected)> 28/11/2005 10:48 am >>>
Is your 'until time' before you dropped the table? Just a thought. It has to be set to a time when the table existed for it to be recreated from redo or archivelogs.
HTH, Ruth
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]On Behalf Of orcl Sent: Sunday, November 27, 2005 9:56 AM To: oracle-l Subject: Applying redo - recovery fundamentals
All, Im having big trouble in a recovery scenario, here is what I have
1, A small shell of a database with all datafiles copied to a backup location (cold backup) 2. Database is in archivelog mode 3. startup up the database and create a table with 6K records. I can see all the redo getting generated wait a few min and drop the table . redo logs are 5mb 4. Shutdown the database, delete all datafiles and copy over all database files (from step 1) including redo logs 5. Startup mount and issue recover database until cancel 6. I *dont * get prompted for cancel and in 2 seconds i get "media recovery complete" and my table *is not* restored
Basically the same problem if I use recover database until time '2005-11-27:09:25:00';
What the heck am I doing wrong?
> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination J:\ARCHIVE_DEST\ORCL Oldest online log sequence 0 Next log sequence to archive 1 Current log sequence 1 > @(protected)
Table dropped.
Table created.
PL/SQL procedure successfully completed.
Commit complete.
COUNT(*) -- ---- -- 60000
> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination J:\ARCHIVE_DEST\ORCL Oldest online log sequence 25 Next log sequence to archive 27 Current log sequence 27
>drop table test_reco Table dropped
> shutdown immediate; <-- then copy over files from cold backup
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 26 08:56:28 2005
> conn sys/***@(protected) as sysdba; Connected to an idle instance.
> startup mount ORACLE instance started.
Total System Global Area 289406976 bytes Fixed Size 1248576 bytes Variable Size 83886784 bytes Database Buffers 197132288 bytes Redo Buffers 7139328 bytes Database mounted.
> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination J:\ARCHIVE_DEST\ORCL Oldest online log sequence 0 Next log sequence to archive 1 Current log sequence 1
> recover database until cancel; <--- I dont get prompted here at all and I have aprox 30 *new* redologs of 5mb each Media recovery complete.
!! This comes back in 2 seconds without my dropped table
DISCLAIMER: The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you received this in error, please contact the sender and delete or destroy this message and any copies. <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=windows-1252"> <META content="MSHTML 6.00.2800.1522" name=GENERATOR></HEAD> <BODY style="MARGIN: 4px 4px 1px; FONT: 10pt Tahoma"> <DIV>by copying over the redo logs.... you've lost all records of that table being created,.</DIV> <DIV> </DIV> <DIV>you need the current control files, current redo logs, and old data files in order to recover to a point before the table was created, but after the cold backup was taken.</DIV> <DIV> </DIV> <DIV>by recovering all files,including redo logs, you've essentially put the entire database back to where it was when the cold backup was taken.<BR><BR>> ;>> "Ruth Gramolini" <rgramolini@(protected)> 28/11/2005 10:48 am >>><BR></DIV> <DIV style="COLOR: #000000">Is your 'until time' before you dropped the table? Just a thought. It has<BR>to be set to a time when the table existed for it to be recreated from redo<BR>or archivelogs.<BR><BR>HTH,<BR>Ruth <BR><BR>-- --Original Message-- --<BR>From: oracle-l-bounce@(protected)<BR>[<A href="mailto:oracle-l-bounce@(protected)]On">mailto:oracle-l-bounce@(protected) .org]On</A> Behalf Of orcl<BR>Sent: Sunday, November 27, 2005 9:56 AM<BR>To: oracle-l<BR>Subject: Applying redo - recovery fundamentals<BR><BR><BR>All, Im having big trouble in a recovery scenario, here is what I have<BR> <BR>1, A small shell of a database with all datafiles copied to a backup <BR>location (cold backup)<BR>2. Database is in archivelog mode<BR>3. startup up the database and create a table with 6K records. I can<BR>see all the redo getting generated wait a few min and drop the table .<BR>redo logs are 5mb<BR>4. Shutdown the database, delete all datafiles and copy over all<BR >database files (from step 1) including redo logs<BR>5. Startup mount and issue recover database until cancel<BR>6. I *dont * get prompted for cancel and in 2 seconds i get "media<BR>recovery complete" and my table *is not* restored<BR><BR>Basically the same problem if I use recover database until time<BR>'2005-11-27:09:25:00';<BR><BR>What the heck am I doing wrong?<BR><BR><BR>> archive log list<BR>Database log mode Archive Mode <BR>Automatic archival Enabled<BR>Archive destination J:\ARCHIVE_DEST\ORCL<BR>Oldest online log sequence 0<BR>Next log sequence to archive 1<BR>Current log sequence 1<BR>> @(protected)<BR><BR>Table dropped.<BR><BR><BR>Table created. <BR><BR><BR>PL/SQL procedure successfully completed.<BR><BR><BR>Commit complete. <BR><BR><BR>COUNT(*)<BR>-- ---- --<BR> 60000<BR><BR>> archive log list;<BR>Database log mode   ; Archive Mode<BR>Automatic archival Enabled<BR >Archive destination J:\ARCHIVE_DEST\ORCL<BR>Oldest online log sequence   ; 25<BR>Next log sequence to archive 27<BR>Current log sequence  ; 27<BR><BR>>drop table test_reco<BR>Table dropped<BR><BR>> shutdown immediate; <-- then copy over files from cold backup<BR><BR>C:\>sqlplus /nolog<BR><BR>SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 26 08:56:28 2005<BR><BR>> conn sys/***@(protected) as sysdba;<BR>Connected to an idle instance.<BR><BR>> startup mount<BR>ORACLE instance started.<BR><BR>Total System Global Area 289406976 bytes<BR>Fixed Size 1248576 bytes<BR>Variable Size 83886784 bytes<BR>Database Buffers 197132288 bytes<BR>Redo Buffers 7139328 bytes<BR >Database mounted.<BR><BR>> archive log list;<BR>Database log mode   ; Archive Mode <BR>Automatic archival Enabled<BR>Archive destination J:\ARCHIVE_DEST\ORCL<BR>Oldest online log sequence 0<BR>Next log sequence to archive 1<BR>Current log sequence 1<BR><BR>> recover database until cancel; <--- I dont get prompted here at all<BR>and I have aprox 30 *new* redologs of 5mb each<BR>Media recovery complete.<BR><BR>!! This comes back in 2 seconds without my dropped table<BR><BR><BR>> show parameter archive<BR><BR>NAME   ; TYPE VALUE<BR>-- ---- --- -- ---- ---- ---- ---- -- -- ---- ---<BR>-- ---- ---- ---- ---- ---- --<BR >archive_lag_target integer 0<BR>log_archive_config string<BR>log_archive _dest string<BR>log_archive_dest_1 string<BR>LOCATION=J:\ARCHIVE_DEST\ORCL<BR>log _archive_duplex_dest string<BR>log_archive_format string ORCL_%t_%s_%r.arc<BR>log_archive_local _first boolean TRUE<BR>log_archive_max_processes integer 2<BR>log_archive_min_succeed_dest integer 1<BR>log_archive_start boolean FALSE<BR>log _archive_trace integer 0<BR>remote_archive_enable   ; string true <BR>standby_archive_dest string %ORACLE_HOME%\RDBMS<BR><BR>><BR><BR>---<BR><BR><BR>--<BR><A href="http://www .freelists.org/webpage/oracle-l">http://www.freelists.org/webpage/oracle-l</A> <BR><BR><BR>--<BR><A href="http://www.freelists.org/webpage/oracle-l">http://www .freelists.org/webpage/oracle-l</A><BR><BR><BR>--<BR><A href="http://www .freelists.org/webpage/oracle-l">http://www.freelists.org/webpage/oracle-l</A> <BR><BR><BR></DIV></BODY></HTML>
<table><tr><td bgcolor=#ffffff><font color=#000000><br> DISCLAIMER: The information transmitted is intended only<br> for the addressee and may contain confidential,<br> proprietary and/or privileged material. Any<br> unauthorized review, distribution or other use<br> of or the taking of any action in reliance upon<br> this information is prohibited. If you received<br> this in error, please contact the sender and <br> delete or destroy this message and any copies. <br> </font></td></tr></table>
