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