 | | | Applying redo - recovery fundamentals | Applying redo - recovery fundamentals 2005-11-28 - By Shannon St. Dennis
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
> show parameter archive
NAME TYPE VALUE
-- ---- ---- ---- ---- ---- ---- --- -- ---- ---
-- ---- ---- ---- ---- ---- --
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
LOCATION=J:\ARCHIVE_DEST\ORCL
log_archive_duplex_dest string
log_archive_format string ORCL_%t_%s_%r.arc
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
remote_archive_enable string true
standby_archive_dest string %ORACLE_HOME%\RDBMS
>
---
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
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>
|
|
|