2 Replies Latest reply: Jun 30, 2009 8:54 AM by 694352 RSS

    alter database create datafile as


      T1-)shutdown immediate;
      T2-)Take full backup
      T4-)move the objects in tablespace test1 to some other tablespace
      T5-)drop tablespace test1;
      T6-)create tablespace test1
      datafile 'C:\oraclexe\oradata\XE\test1.dbf' reuse;
      T7-)alter system switch logfile;

      In some other server;
      startup nomount;
      restore the backupset which was taken at T2.
      restore controlfile at T7.

      startup mount;
      alter database create datafile 'C:\oraclexe\oradata\XE\test1.dbf' as 'C:\oraclexe\oradata\XE\test1.dbf';

      ALTER DATABASE RECOVER automatic database until cancel using backup controlfile;

      Recovery performed succefully.

      What I wanna ask is;
      Since First I issue:
      "alter database create datafile as ...."
      The contents of this datafile should be deleted with this statement.
      Why oracle doesnt give error in recovery, during appliying the statement at T4 ?
      I have already deleted the contents of the datafile, how come oracle move the objects?

      I hope I am clear
        • 1. Re: alter database create datafile as
          although it is beyound my imagination what kind of real world scenario you are trying to simulate with your test, I can tell you why no error gets returned:

          You create a new tablespace t1 after you dropped the old one. That gets stored in the current controlfile. The previous backup of the datafile of the old tablespace that incidentally has the same name is now useless for the new tablespace.

          You then restore that controlfile and create the datafile manually as it looked after you created the new tablespace (empty) and then recover that. RMAN does that (without complaining even about the uselessness of your doing). If you look into that tablespace, you will see that it is empty as it was after you created it - unless you have put any objects into it after the second creation of that tablespace t1.

          Kind regards

          • 2. Re: alter database create datafile as
            In step T4:
            "T4: I move the objects in tablespace test1 to some other tablespace"

            So this step should be stored in archivelogs.
            In the standby database, I first cleared the datafile with "alter database create datafile as ..", so there will be no objects in the datafile.
            Then I started media recovery;
            How come oracle apply the archivelogs of T4 without any error ( I have already cleared the datafile)???