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

Labels:

Comments

Post a Comment

Popular posts from this blog

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more

Process m000 died, see its trace file

Alert Log: [oracle@ bdump]$ view alert_HDRTG12.log Thu May 28 22:05:42 PDT 2015 Process P021 died, see its trace file Thu May 28 22:05:47 PDT 2015 Process m000 died, see its trace file Thu May 28 22:05:47 PDT 2015 ksvcreate: Process(m000) creation failed Thu May 28 22:06:00 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:10 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:43 PDT 2015 [oracle@bdump]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 28 23:38:59 2015 Copyright (c) 1982, 2010, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------------------------- HDRTG12 SQL> select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions'); RESOURCE...
Post a Comment
Read more