Solution for ORA-01033 due to accidental deletion of tablespace files

Solution for ORA-01033 due to accidental deletion of tablespace files

This type of problem is usually caused by improper tablespace operations.
Solution:

SQL * Plus cannot be connected. The following error is displayed:
ORA-01033: Oracle initialization or shutdown in progress, the same error is reported in the Enterprise Manager Console.

Run cmd to enter the DOS environment.

C: \ Users \ Administrator> sqlplus

SQL * Plus: Release 10.2.0.1.0-Production on Friday April 22 09:28:44 2016

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Enter the User name: sys/sys as sysdba

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-01113: file 6 media recovery required
ORA-01110: data file 6: 'e: \ TEST. ORA'

SQL> alter database datafile 6 offline drop;

The database has been changed.

SQL> alter database open;

The database has been changed.

SQL> drop user TEST cascade; // Note: This TEST corresponds to TEST in 'e: \ TEST. ORA 'above.

The user has been deleted.

SQL> drop tablespace TEST including contents;

The tablespace has been deleted.

So far, SQL * Plus and Enterprise Manager Console can be logged on normally.

The next step is to recreate the tablespace and users deleted during the problem resolution process.

SQL> create tablespace TEST datafile 'e: \ test. dbf' size 100 m reuse autoextend on next 10 m maxsize unlimited extent management
Local;
SQL> create user TEST default tablespace TEST indentified by windows;