Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Mimic incomplete recovery - Noarchivelog Mode

Re: Mimic incomplete recovery - Noarchivelog Mode

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 21 Oct 2002 08:35:54 +1000
Message-ID: <g1Gs9.57802$g9.165396@newsfeeds.bigpond.com>


Hi Koert,

No your flippen' right !!

Believe me, a life full of white wine, fast cars and silicon mounted table dancers can't possibly be wrong ;)

Glad I could clarify.

Richard
"koert54" <nospam_at_spam.com> wrote in message news:yhus9.170194$8o4.26816_at_afrodite.telenet-ops.be...
> 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 bytes
> Database mounted.
> Database opened.
> SQL> show parameter archive
>
> 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/arch
> SQL> shutdown immediate
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
> SQL> ll
> SP2-0042: unknown command "ll" - rest of line ignored.
> SQL> exit
> Disconnected
> [oracle_at_oraserv admin]$ ll
> total 28
> 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
> [oracle_at_oraserv oracle]$ cd oradata
> [oracle_at_oraserv oradata]$ ll
> total 32
> 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
> [oracle_at_oraserv P15]$ ll
> total 518944
> -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 *
> bash: fuser: command not found
> [oracle_at_oraserv P15]$ rm *.log
> [oracle_at_oraserv P15]$ sqlplus internal
>
> 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 bytes
> Database mounted.
> 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>
>
>
> "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 - 17:35:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US