Skip to main content

LOST DATAFILE RECOVERY IN ORACLE DATABASE



Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\computer>sqlplus

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
ORACLE

------------------------------------------------------------------------------
NOTE - DATABASE SHOULD BE IN ARCHIVE LOG MODE
------------------------------------------------------------------------------

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10
SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORACLE\SYSTEM01.DBF
D:\ORACLE\ORACLE\SYSAUX01.DBF
D:\ORACLE\ORACLE\UNDOTBS01.DBF
D:\ORACLE\ORACLE\USERS01.DBF

SQL> create tablespace test datafile 'D:\ORACLE\ORACLE\TEST.01.dbf' size 10M;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORACLE\SYSTEM01.DBF
D:\ORACLE\ORACLE\SYSAUX01.DBF
D:\ORACLE\ORACLE\UNDOTBS01.DBF
D:\ORACLE\ORACLE\USERS01.DBF
D:\ORACLE\ORACLE\TEST.01.DBF

SQL> alter system switch logfile;

System altered.

SQL> create table test_tab tablespace test
  2  as
  3    select rownum id, a.*
  4    from all_objects a
  5    where 1=0;

Table created.

SQL> select tablespace_name, segment_name from dba_segments where segment_name = 'TEST_TAB';

TABLESPACE_NAME
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST
TEST_TAB


SQL> select count(*) from test_tab;

  COUNT(*)
----------
         0


SQL> create user TEST identified by TEST default tablespace TEST account unlock;

User created.

SQL>
SQL> grant dba to test;

Grant succeeded.

SQL> conn test
Enter password:
Connected.
SQL>
SQL>
SQL> show user;
USER is "TEST"
SQL>
SQL> create table test1 as select * from scott.emp;

Table created.

SQL> select count(*) from test1;

  COUNT(*)
----------
        14

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\computer>
C:\Users\computer>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 30 21:29:21 2016

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             171969636 bytes
Database Buffers          356515840 bytes
Redo Buffers                5828608 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

--------------------------------------------------------
NOTE - STOPPED DATABASE AND DELETED DATAFILE 5 MANUALLY.
--------------------------------------------------------

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             171969636 bytes
Database Buffers          356515840 bytes
Redo Buffers                5828608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE\ORACLE\TEST.01.DBF'


SQL> alter database datafile 5 offline;

Database altered.


SQL> alter database create datafile 'D:\ORACLE\ORACLE\TEST.01.dbf' as 'D:\ORACLE\ORACLE\TEST.01.dbf';

Database altered.

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.

SQL> alter database open;

Database altered.

SQL> conn test
Enter password:
Connected.
SQL>
SQL>
SQL> SELECT COUNT(*) FROM TEST1;

  COUNT(*)
----------
        14

SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Labels:

Comments

Post a Comment

Popular posts from this blog

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes ...
2 comments
Read more