v$datafile now shows me status shows me recover , dba studioshows me offline(recover) status. recover database gives me an error saying nothing to recover. if i give drop tablespace XXXX including contents, it will delete the entire tablespace, right ? is it okay if i let the datafile be as it is and continue with my normal work ? or will it give me problems in the future. this is not a permanent database. and i can change it sometime in the future. waiting for ur coooments)
well if itīs in recover state and you cant recover it then basically you will have to drop the entire tablespace, how many datafiles makes up that tablespace, are you in ARCHIVED or NOT ARCHIVED
my tablespace is drsys tablespace with 4 tablespaces. out of 4 , 2 were created by me while trying, 1 is the erroneous one and one is dr01 datafile. i think i will drop the tablespace, recreate one with the same name and add dr01 to it. is that ok ? i am in NOARCHIVELOG mode. do u think if i continue with the same tablespase, i will face any problem ?
you cant jsut drop a tablespace recreate one and add a datafile from previous tablespace, things arent that simple.
You better drop the whole tablespace delete all datafiles belonging to that tablespace and recreate, you will lose everything in that tablespace.
Since you are in NOARCHIVE log you can recover a single datafile
guess i have to learn a lot. u say i can recover 1 datafile. if drsys01 is that datfile, can u tell me the procedure ? actually i dont mind droping the tablespace. but i fear there might be some system tables in dr01 datafile. if that is not true i can drop the table as i have a dump of my data ? can u suggest something ? thanks
sorry I meant you cannot recover a datafile in no archive
i deleted the tablespace and recreated it. it seems to work fine. i am now importing my data. thanks a lot for help. :-) bye and have a nice time.
I could not dind alert file in bdump folder
SQL> startup nomount pfile=C:\oracle\admin\arundb\pfile\initarundb.ora
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
SQL> SHOW PARAMETER DUMP
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
background_core_dump string partial
background_dump_dest string C:\oracle\admin\arundb\bdump
core_dump_dest string C:\oracle\admin\arundb\cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string C:\oracle\admin\arundb\udump
SQL> alter database mount;
now when i try to run alter database open i find error
ORA-01092: ORACLE instance terminated. Disconnection forced
i could not locate alert file to trouble shoot
Click Here to Expand Forum to Full Width