Biggest Doubt On Alter Database Create Datafile Command
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Biggest Doubt On Alter Database Create Datafile Command

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    Biggest Doubt On Alter Database Create Datafile Command

    Env:
    Win2000 Ad Server Oracle 8.1.6 Standard Edition Archivelog Mode
    Procedure :
    Case4: Lost File Without Backup
    Lost Data File is not a system or rollback segment datafile
    Old Control File is used in the backup
    1. alter tablespace tsoff add datafile 'E:\ORACLE\ORADATA\ORCL\TSOFF2.ORA' 1M
    2 Shutdown Database
    3 Delete TSOFF2.ORA and TSOFF1.ORA
    4 Apply the old backup datafile TSOFF1.ORA and old control file (overwrite)
    5 Startup mount Database
    6 SVRMGR> recover database using backup controlfile ;
    ORA-00279: change 645739 generated at 05/15/2003 17:21:59 needed for thread 1 ORA-00289: suggestion : E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00025.001 ORA-00280: change 645739 for thread 1 is in sequence #25 Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. ORA-00279: change 645858 generated at 05/15/2003 17:29:48 needed for thread 1 ORA-00289: suggestion : E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00026.001 ORA-00280: change 645858 for thread 1 is in sequence #26 ORA-00278: log file 'E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00025.001' no longer need ed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. ORA-00279: change 645912 generated at 05/15/2003 17:36:39 needed for thread 1 ORA-00289: suggestion : E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00028.001 ORA-00280: change 645912 for thread 1 is in sequence #28 ORA-00278: log file 'E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00027.001' no longer need ed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log 'E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00028.001'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.
    7 SVRMGR> recover database using backup controlfile ;
    ORA-00279: change 645912 generated at 05/15/2003 17:36:39 needed for thread 1
    ORA-00289: suggestion : E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00028.001
    ORA-00280: change 645912 for thread 1 is in sequence #28
    Specify log: {=suggested | filename | AUTO | CANCEL}
    E:\ORACLE\ORADATA\ORCL\REDO01.LOG
    ORA-00310: archived log contains sequence 27; sequence 28 required
    ORA-00334: archived log: 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'
    Specify log: {=suggested | filename | AUTO | CANCEL}
    E:\ORACLE\ORADATA\ORCL\REDO03.LOG
    ORA-00283: recovery session canceled due to errors
    ORA-01244: unnamed datafile(s) added to controlfile by media recovery
    ORA-01110: data file 13: 'E:\ORACLE\ORADATA\ORCL\TSOFF2.ORA'

    8 Query V$DATAFILE
    The Missing File Name is set as E:\ORACLE\ORA81\DATABASE\UNNAMED00013 by Oracle
    9 alter database create datafile 'E:\ORACLE\ORA81\DATABASE\UNNAMED00013' as 'e:\oracle\oradata\orcl\tsoff2.ora' ;
    10 SVRMGR> recover database using backup controlfile ;
    ORA-00279: change 646245 generated at 05/16/2003 11:32:30 needed for thread 1 ORA-00289: suggestion : E:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00028.001 ORA-00280: change 646245 for thread 1 is in sequence #28 Specify log: {=suggested | filename | AUTO | CANCEL} E:\ORACLE\ORADATA\ORCL\REDO03.LOG Log applied. Media recovery complete.
    11 SVRMGR> alter database open resetlogs; Statement processed.
    12 Query V$DATAFILE The File Name is still tsoff2.ora
    What's the meaning of
    alter database create datafile 'E:\ORACLE\ORA81\DATABASE\UNNAMED00013' as 'e:\oracle\oradata\orcl\tsoff2.ora' ;

    I notice It actually create the missing file tsoff2.ora not the one
    specified in V$datafile dictionary .
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    I notice My previous understanding is wrong .
    Now I am clear about it

    Creating a New Datafile

    The following statement creates a new datafile 'DISK2B1.DAT' based on the file 'DISK1B1.DAT':

    ALTER DATABASE
    CREATE DATAFILE 'disk1:db1.dat' AS 'disk2:db1.dat';
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width