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

CHANGING DBID FOR ORACLE DATABASE 11G

C:\Users\computer>sqlplus SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 21:03:33 2013 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> select dbid from v$database;       DBID ---------- 2188161033 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>nid DBNEWID: Release 11.1.0.7.0 - Production on Thu Sep 10 21:04:44 2013 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET ...
Post a Comment
Read more

Difference between AWR, ADDM and ASH reports

AWR: Automatic Workload Repository gathers, processes and maintains performance stats used for problem detection and self-tuning the databases. Different Components that uses AWR are: Automatic Database Diagnostic Monitor Undo Advisor SQL Tuning Advisor Segment Advisor Different types of AWR Reports for different purposes: For Single Instance Environment: @$ORACLE_HOME/rdbms/admin/awrrpt.sql For Oracle RAC Environment : @$ORACLE_HOME/rdbms/admin/awrgrpt.sql For a particular SQL Statement : @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql For Comparing the reports : @$ORACLE_HOME/rdbms/admin/awrddrpt.sql ADDM: Automatic Database Diagnostic Monitoring Report analyzes the AWR data on a regular basis, to give you overview of the root cause of the problem which is affecting your database’s performance. It also provides suggestions or recommendations for rectifying any problem identified and lists the areas which are having no issues. ADDM recommends multiple solutions for the DBA to choose from which...
Post a Comment
Read more