Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Mimic incomplete recovery - Noarchivelog Mode
Dude - you're flippen' right ... the mimic'ing is necessary - a life full of
Oracle databases, white wine, fast cars and
silicon mounted table-dansers must be messing with my mind !
I removed the redologs after a shutdown and the basterd told me to recover
the DB :-)
Oh well - again something I won't forget again !
cheers
koert
SQL> startup
ORACLE instance started.
Total System Global Area 24256672 bytes
Fixed Size 73888 bytes Variable Size 7233536 bytes Database Buffers 16777216 bytes Redo Buffers 172032 bytesDatabase mounted.
NAME TYPE VALUE ------------------------------------ ------- ------------------------------ log_archive_dest string log_archive_dest_1 string log_archive_dest_2 string log_archive_dest_3 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_state_1 string enable log_archive_dest_state_2 string enable log_archive_dest_state_3 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable NAME TYPE VALUE ------------------------------------ ------- ------------------------------ log_archive_duplex_dest string log_archive_format string %t_%s.dbf log_archive_max_processes integer 1 log_archive_min_succeed_dest integer 1 log_archive_start boolean FALSE log_archive_trace integer 0 standby_archive_dest string ?/dbs/archSQL> shutdown immediate
drwxrwxr-x 6 oracle dba 4096 Sep 29 20:02 . -rw-r--r-- 1 oracle dba 127 Sep 29 20:02 afiedt.buf drwxr-xr-x 27 oracle dba 4096 Sep 29 19:59 .. drwxr-xr-x 10 oracle dba 4096 Sep 15 16:11 P15 drwxr-xr-x 6 oracle dba 4096 Sep 4 2001 ORA9 drwxr-xr-x 10 oracle dba 4096 Apr 6 2001 ORA81 drwxrwxr-x 10 oracle dba 4096 Jan 13 2001 ORA8 [oracle_at_oraserv admin]$ cd
drwxr-xr-x 2 oracle dba 4096 Sep 29 20:02 P15 drwxr-xr-x 23 root root 4096 Sep 22 13:54 .. drwxr-xr-x 5 oracle dba 4096 Sep 15 16:11 . drwxrwxr-x 2 oracle dba 4096 Jun 11 2001 ORA8 drwxr-xr-x 2 root root 16384 Jan 13 2001 lost+found[oracle_at_oraserv oradata]$ cd P15
-rw-r----- 1 oracle dba 4431872 Oct 20 11:00 control01.ctl -rw-r----- 1 oracle dba 4431872 Oct 20 11:00 control02.ctl -rw-r----- 1 oracle dba 4431872 Oct 20 11:00 control03.ctl -rw-r----- 1 oracle dba 5251072 Oct 20 11:00 indx01.dbf -rw-r----- 1 oracle dba 209723392 Oct 20 11:00 perfstat01.dbf -rw-r----- 1 oracle dba 53485568 Oct 20 11:00 rbs01.dbf -rw-r----- 1 oracle dba 512512 Oct 20 11:00 redo01.log -rw-r----- 1 oracle dba 157294592 Oct 20 11:00 system01.dbf -rw-r----- 1 oracle dba 71311360 Oct 20 11:00 temp01.dbf -rw-r----- 1 oracle dba 8396800 Oct 20 11:00 tools01.dbf -rw-r----- 1 oracle dba 10493952 Oct 20 11:00 users01.dbf -rw-r----- 1 oracle dba 512512 Oct 20 10:59 redo02.log -rw-r----- 1 oracle dba 512512 Oct 20 10:59 redo03.log drwxr-xr-x 2 oracle dba 4096 Sep 29 20:02 . drwxr-xr-x 5 oracle dba 4096 Sep 15 16:11 ..[oracle_at_oraserv P15]$ fuser *
SQL*Plus: Release 8.1.7.0.0 - Production on Sun Oct 20 11:05:15 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 24256672 bytes
Fixed Size 73888 bytes Variable Size 7233536 bytes Database Buffers 16777216 bytes Redo Buffers 172032 bytesDatabase mounted.
SQL> recover database until cancel ;
Media recovery complete.
SQL> alter database open resetlogs ;
Database altered.
SQL>
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:etos9.57019$g9.163793_at_newsfeeds.bigpond.com...
> Comments embedded.
>
> "koert54" <nospam_at_spam.com> wrote in message
> news:qdZr9.168297$8o4.26243_at_afrodite.telenet-ops.be...
> > I would like to comment on this.
>
> Hi Koert54,
>
> Cool, more the merrier !!
>
> > For closed/cold DB backups (after clean shutdown - not shutdown abort)
it
> is
> > not necessary to backup the redolog files. (This used to be documented
in
> > the Oracle Backup & Recovery docs)
>
> Quite correct.
>
> Backing up the redo logs is somewhat redundant as they contain nothing of
> value. However, by restoring them as well, it does simplify the recovery
of
> the database (by a debatable teeny weeny amount).
>
> >
> > When restoring from such a backup the DB is opened using 'open
resetlogs'.
> > The redolog
> > files are not necessary because no instance recovery is needed.
>
> Correct
>
> > When the DB is open with resetlogs the missing logfiles are created -
the
> > only thing is, the DB
> > is reset to a new incarnation.
>
> Correct
>
> >
> > So - a database in noarchivelog mode which is backed up every night by
> > performing a clean
> > shutdown and putting every datafile & control file on tape can be
restored
> > and opened
> > with a resetlogs without mimic'ing an incompleted recovery and without
> > dataloss.
>
> OK. Most databases I have the pleasure to work on are archivelog mode
> databases so this has never been much of an issue for me. However the
> exceptions to this rule are my own personal databases that I use for
> research, play and for being a total plonker on (new term that I love more
> every time I hear it).
>
> So I restored from my noarchivelog database just the data files and
controls
> files from a clean backup and gave it a whirl using 9.2 on XP:
>
> SQL> startup
> ORACLE instance started.
>
> Total System Global Area 51452388 bytes
> Fixed Size 453092 bytes
> Variable Size 33554432 bytes
> Database Buffers 16777216 bytes
> Redo Buffers 667648 bytes
> Database mounted.
> ORA-00313: open failed for members of log group 1 of thread 1
> ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ZIGGY\REDO01.LOG'
>
> SQL> alter database open resetlogs;
> alter database open resetlogs
> *
> ERROR at line 1:
> ORA-01139: RESETLOGS option only valid after an incomplete database
recovery
>
> SQL> recover database until cancel;
> Media recovery complete.
> SQL> alter database open resetlogs;
>
> Database altered.
>
> SQL>
>
> So as you can see from the above, the database (specifically the control
> file) doesn't like it when you attempt to open a database with resetlogs
> that has not had a successful incomplete recovery performed. Note also
that
> when performing the incomplete recovery, it immediately completes as all
the
> data files and controls files are in sync. However now, the open database
> with resetlogs completes successfully.
>
> Cheers
>
> Richard
>
>
Received on Sun Oct 20 2002 - 04:01:18 CDT