Re: alter tablespace ... end backup.... OR not?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 25 Dec 2002 00:18:24 +1000
Message-ID: <7VYN9.9406$jM5.26683@newsfeeds.bigpond.com>

"Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message news:v0g0cv8v427e3b_at_corp.supernews.com...
> This morning I experienced this funny feeling that you think to understand
> something and still are puzzled by this something not following your
logic.
>
> Scenario:
> ---------
> We're in the process of migrating one of our databases (7.3.4 --> 8.1.7,
at
> last). Yesterday we had severe problems with the HP-UX box the database
> resides on, and one of the consequences was that we didn't have any
backups
> made on this server.
> Our sysadmin asked me if we should take the backup after having solved our
> problems. I said OK, I'll put the tablespaces in backup mode, so you can
> take the backup.
>
> This morning, riding on my bicycle back to work, I suddenly realized I had
> forgotten to use the "alter tablespace ... end backup" statements, so that
> would have been the first thing to do.
> After arriving I soon discovered that the midnight backup had caused the
> database to go down (abort, startup restrict, shutdown). On startup the
> database complained about file 1, it needed media recovery.
>
> OK, I can understand that, so I fullfilled it's needs, and the process
ended
> as one hopes: log files applied, database open.
>
> But now comes the funny part, as I see it: my tablespaces were no longer
in
> backup mode. Nobody ever supplied the "end backup" statements, so I
suppose
> it was implicitly done by the recovery. Is this expected behaviour?
>
> regards,
> Peter

Hi Peter,

It's after midnight and here I am, waiting for the kids to go to sleep so I can put their presents under the tree !!

In answer to your question, yes, a recovery will put a data file out of backup mode. I must say, I've never been entirely happy with the way Oracle handles all this. When starting up a database with data files left previously in backup mode, Oracle's "error" message suggests that media recovery is required which Oracle, if only it looked in it's control file, can quite clearly see is probably not the case. An "error" message that says "data file 69 might need media recovery but then again might just need to be taken out of recovery mode with the appropriate alter database command" would be a step forward.

However, if the unsuspecting DBA (it was his mate who's now nicked off on holidays that put the data files in backup mode) simply issues a recovery command, Oracle will eventually re-sync the datafile and take the file out of recovery mode. At least Oracle now won't let you just shutdown a database (expect with abort) if any file is currently in backup mode.

Simple demo (I know no others ;)

SQL> alter tablespace fred begin backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE                  0
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0
         8 NOT ACTIVE                  0
         9 NOT ACTIVE                  0
        10 NOT ACTIVE                  0
        11 NOT ACTIVE                  0

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
        12 NOT ACTIVE                  0
        13 NOT ACTIVE                  0
        14 ACTIVE               20711628 24/DEC/02
        15 NOT ACTIVE                  0
        16 NOT ACTIVE                  0
        17 NOT ACTIVE                  0
        18 NOT ACTIVE                  0
        19 NOT ACTIVE                  0

SQL> alter system switch logfile;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
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-01113: file 14 needs media recovery
ORA-01110: data file 14: 'C:\BOWIE\FRED01.DBF'

SQL> recover datafile 14;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from v$backup where file# = 14;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
        14 NOT ACTIVE           20711628 24/DEC/02

So yes, it's expected behaviour.

Have a great Xmas !!

Richard Received on Tue Dec 24 2002 - 08:18:24 CST