Search This Blog

Total Pageviews

Sunday 15 January 2017

Recover Tablespace

Oracle Recover Tablespace . 



ERROR at line 1:
ORA-00376: file 5  cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/oraeed/anuj01.dbf'

for search key !!
ORA-00376: file   cannot be read at this time
ORA-01110: data file : 


Taking Backup .. 


RUN {
configure controlfile autobackup on;
set command id to 'ORCLEEDBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCLEED_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag ORCLEED_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup tag ORCLEED_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
}



RMAN>
RUN {
configure controlfile autobackup on;

RMAN> 2> set command id to 'ORCLEEDBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCLEED_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag ORCLEED_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup tag ORCLEED_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
}
3> 4> 5> 6> 7> 8> 9> 10>
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

allocated channel: c1
channel c1: SID=42 device type=DISK

Starting backup at 15-JAN-17
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oraeed/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oraeed/users01.dbf
channel c1: starting piece 1 at 15-JAN-17



channel c1: finished piece 1 at 15-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEED_20170115_18_1_FULL tag=ORCLEED_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:05:16
Finished backup at 15-JAN-17

Starting Control File and SPFILE Autobackup at 15-JAN-17
piece handle=/u01/app/oracle/RmanBackup/c-2939668799-20170115-08.bck comment=NONE
Finished Control File and SPFILE Autobackup at 15-JAN-17

sql statement: alter system archive log current

Starting backup at 15-JAN-17
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=9 STAMP=933362415
input archived log thread=1 sequence=10 RECID=10 STAMP=933367281
input archived log thread=1 sequence=11 RECID=11 STAMP=933367281
channel c1: starting piece 1 at 15-JAN-17
channel c1: finished piece 1 at 15-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEED_20170115_20_1_ARCHIVE tag=ORCLEED_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEED/1_9_933325760.dbf RECID=9 STAMP=933362415
archived log file name=/u01/app/oracle/ArchiveEED/1_10_933325760.dbf RECID=10 STAMP=933367281
archived log file name=/u01/app/oracle/ArchiveEED/1_11_933325760.dbf RECID=11 STAMP=933367281
Finished backup at 15-JAN-17

Starting backup at 15-JAN-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 15-JAN-17
channel c1: finished piece 1 at 15-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEED_20170115_21_1_CONTROL tag=ORCLEED_CONTROL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JAN-17

Starting Control File and SPFILE Autobackup at 15-JAN-17
piece handle=/u01/app/oracle/RmanBackup/c-2939668799-20170115-09.bck comment=NONE
Finished Control File and SPFILE Autobackup at 15-JAN-17

released channel: c1



=============

set linesize 200
col FILE_NAME for a70
SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;


TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------------------------------------
SYSAUX                         /u01/app/oracle/oradata/oraeed/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/oraeed/system01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/oraeed/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/oraeed/users01.dbf




Created tablespace ....

SQL> CREATE TABLESPACE anuj DATAFILE '/u01/app/oracle/oradata/oraeed/anuj01.dbf' SIZE 10M  AUTOEXTEND ON;

Tablespace created.


set linesize 200
col FILE_NAME for a70
SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

SQL> SQL>
TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------------------------------------
ANUJ                           /u01/app/oracle/oradata/oraeed/anuj01.dbf  ----<<<<<<
SYSAUX                         /u01/app/oracle/oradata/oraeed/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/oraeed/system01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/oraeed/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/oraeed/users01.dbf




SQL> create table anuj_table tablespace anuj as select * from dual;

Table created.

SQL> select * from anuj_table;

D
-
X


SQL> !rm /u01/app/oracle/oradata/oraeed/anuj01.dbf

SQL> !ls -ltr /u01/app/oracle/oradata/oraeed/
total 1367728
drwxr-x--- 3 oracle oinstall      4096 Jan 15 09:09 ORAEED
-rw-r----- 1 oracle oinstall  20979712 Jan 15 10:09 temp01.dbf
-rw-r----- 1 oracle oinstall  31465472 Jan 15 20:36 undotbs01.dbf
-rw-r----- 1 oracle oinstall 492838912 Jan 15 20:36 sysaux01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jan 15 20:36 users01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jan 15 20:41 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jan 15 20:41 redo02.log
-rw-r----- 1 oracle oinstall 702554112 Jan 15 20:53 system01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jan 15 20:54 redo03.log
-rw-r----- 1 oracle oinstall  10076160 Jan 15 20:55 control01.ctl




SQL> insert into anuj_table values('t');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into anuj_table values('t');
insert into anuj_table values('t')
            *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/oraeed/anuj01.dbf'


Starting recovery .... 


SQL> alter tablespace anuj offline immediate;

Tablespace altered.



SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

[oracle@ora RmanBackup]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 15 21:05:44 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORAEED (DBID=2939668799)


RMAN> restore datafile 5;

Starting restore at 15-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

creating datafile file number=5 name=/u01/app/oracle/oradata/oraeed/anuj01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 15-JAN-17



RMAN> recover datafile 5;

Starting recover at 15-JAN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 15-JAN-17



[oracle@ora RmanBackup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 15 21:08:45 2017

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


Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> select * from anuj_table ;
select * from anuj_table
              *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/oraeed/anuj01.dbf'


SQL> alter tablespace anuj online;

Tablespace altered.

SQL> select * from anuj_table ;

D
-
X
t

Oracle DBA

anuj blog Archive