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

Registering The Database to RMAN catalog database:

Registering The Database to RMAN catalog database: Need to start RMAN as follows: RMAN target=sys/password@database_to_backup rcvcat=sys/password@recovery_catalog_database Another variation on the command, if the recovery catalog and the database were on the same server, might be as shown: oraenv ORACLE_SID = [KKUY] ? KKUY RMAN rcvcat=sys/password@recovery_catalog_database RMAN> connect target Recovery Manager: Release 8.0.5.1.0 - Production RMAN-06005: connected to target database: KKUY RMAN-06008: connected to recovery catalog database Use the below command to register the database. RMAN>register database; Want to verify if a database is registered in the recovery catalog. To do this, connect to RMAN and issue the command LIST INCARNATION OF DATABASE. RMAN> list incarnation of database; RMAN-03022: compiling command: list RMAN-06240: List of Database Incarnations RMAN-06241: DB Key Inc Key DB Name DB ID      CUR Reset SCN   Reset Time RMAN
Post a Comment
Read more

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