Loss of non system datafile which not available in last night backup (Complete and Online recovery)
Note:
(Eg: We have taken the backup 10:00 AM and added the datafile at 11:00 AM, this newly added datafile is lossed at 3:00 PM on same day)
1. USERS tablespace is having only one datafile and I have added the new datafile to users tablespace.
This newly added datafile is not available in last night backup.
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name=’USERS’;
FILE_NAME TABLESPACE_NAME
—————————————————————————————- ——————————
/restore1/oracle/SID/data/SID_users01.dbf USERS
SQL> alter tablespace users add datafile ‘/restore1/oracle/SID/data/SID_users02.dbf’ size 100M;
Tablespace altere
d.
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name=’USERS’;
FILE_NAME TABLESPACE_NAME
—————————————————————————————- ——————————
/restore1/oracle/SID/data/SID_users01.dbf USERS
/restore1/oracle/SID/data/SID_users02.dbf USERS
1A. After adding datafile, I did manual log switches.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
2. I have created one table on users table space and inserted the data.
SQL> create table dba_segments_users tablespace users as select * from dba_segments;
Table created.
SQL> select segment_name,segment_type,owner,tablespace_name from dba_segments where segment_name=’DBA_SEGMENTS_USERS’;
SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME
—————————————————————- —————— —————————— ——————————
DBA_SEGMENTS_USERS TABLE SYS USERS
SQL> insert into dba_segments_users select * from dba_segments_users;
4489 rows created.
SQL> insert into dba_segments_users select * from dba_segments_users;
8978 rows created.
SQL> insert into dba_segments_users select * from dba_segments_users;
17956 rows created.
SQL> insert into dba_segments_users select * from dba_segments_users;
35912 rows created.
SQL> insert into dba_segments_users select * from dba_segments_users;
71824 rows created.
SQL> insert into dba_segments_users select * from dba_segments_users;
143648 rows created.
SQL> commit;
Commit complete.
2A. After inserting the data I have deleted the newly added datafiiles at OS level.
ls -ltr
total 4520144
-rw-r–r– 1 oracle dba 5017 Oct 26 13:25 abcd
-rw-r—– 1 oracle dba 209723392 Oct 29 13:59 SID_undotbs01.dbf
-rw-r—– 1 oracle dba 734011392 Oct 29 13:59 SID_system01.dbf
-rw-r—– 1 oracle dba 524296192 Oct 29 13:59 SID_sysaux01.dbf
-rw-r—– 1 oracle dba 104865792 Oct 29 13:59 SID_autoindexes01.dbf
-rw-r—– 1 oracle dba 524296192 Oct 29 13:59 SID_autodata01.dbf
-rw-r—– 1 oracle dba 10486272 Oct 29 13:59 SID_redo01b.log
-rw-r—– 1 oracle dba 10486272 Oct 29 13:59 SID_redo01a.log
-rw-r—– 1 oracle dba 104865792 Oct 29 13:59 SID_users02.dbf
-rw-r—– 1 oracle dba 52436992 Oct 29 13:59 SID_users01.dbf
-rw-r—– 1 oracle dba 10486272 Oct 29 13:59 SID_redo02b.log
-rw-r—– 1 oracle dba 10486272 Oct 29 13:59 SID_redo02a.log
-rw-r—– 1 oracle dba 5881856 Oct 29 13:59 SID_control03.ctl
-rw-r—– 1 oracle dba 5881856 Oct 29 13:59 SID_control02.ctl
-rw-r—– 1 oracle dba 5881856 Oct 29 13:59 SID_control01.ctl
# rm SID_users02.dbf
2B. After that I did the manual log swiches and checkpoint to get the error, but I didn’t get.
SQL> alter system switch logfile;
System altered.
3. I have inserted the some more records into the table…. while inserting the data tablespace got filled and got the below error.
SQL> insert into dba_segments_users select * from dba_segments_users;
insert into dba_segments_users select * from dba_segments_users
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.DBA_SEGMENTS_USERS by 1024 in tablespace USERS
SQL> select count(1) from dba_segments_users;
COUNT(1)
———-
1149184
SQL> commit;
Commit complete.
3A. I tried to resize the datafile which deleted at OS then got the below error.
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name=’USERS’;
FILE_NAME TABLESPACE_NAME
—————————————————————————————- ——————————
/restore1/oracle/SID/data/SID_users01.dbf USERS
/restore1/oracle/SID/data/SID_users02.dbf USERS
SQL> alter database datafile ‘/restore1/oracle/SID/data/SID_users02.dbf’ resize 200M;
alter database datafile ‘/restore1/oracle/SID/data/SID_users02.dbf’ resize 200M
*
ERROR at line 1:
ORA-01565: error in identifying file ‘/restore1/oracle/SID/data/SID_users02.dbf’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
SQL> select file_name,file_id,sum(bytes)/1024/1024,status from dba_data_files where tablespace_name=’USERS’ group by file_name,file_id,status;
FILE_NAME FILE_ID SUM(BYTES)/1024/1024 STATUS
—————————————————————————————- ———- ——————– ———
/restore1/oracle/SID/data/SID_users01.dbf 4 93.75 AVAILABLE
/restore1/oracle/SID/data/SID_users02.dbf 7 100 AVAILABLE
4. put the datafile offline which lossed.
SQL> alter database datafile 7 offline;
Database altered.
SQL> ;
1* select name,status from v$datafile
SQL> /
NAME STATUS
—————————————————————————————- ——-
/restore1/oracle/SID/data/SID_system01.dbf SYSTEM
/restore1/oracle/SID/data/SID_undotbs01.dbf ONLINE
/restore1/oracle/SID/data/SID_sysaux01.dbf ONLINE
/restore1/oracle/SID/data/SID_users01.dbf ONLINE
/restore1/oracle/SID/data/SID_autodata01.dbf ONLINE
/restore1/oracle/SID/data/SID_autoindexes01.dbf ONLINE
/restore1/oracle/SID/data/SID_users02.dbf RECOVER
7 rows selected.
4A. Create the datafile at database level using below command and Make sure give the datafile name and location same as old one(which is lossed).
(it will create the MT datafile at OS Lelvel).
SQL> alter database create datafile ‘/restore1/oracle/SID/data/SID_users02.dbf’;
Database altered.
4B. After creating the datafile using above command, check the datafile is created or not at OS level.
ls -ltr /restore1/oracle/SID/data/SID_users02.dbf
-rw-r—– 1 oracle dba 104865792 Oct 29 14:20 /restore1/oracle/SID/data/SID_users02.dbf
5. Recover datafile (Make sure all the required archive logs are available on archive destination for recovery)
SQL> recover datafile 7;
ORA-00279: change 227047952 generated at 10/29/2009 13:47:21 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_168_700066042.dbf
ORA-00280: change 227047952 for thread 1 is in sequence #168
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227047985 generated at 10/29/2009 13:47:50 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_169_700066042.dbf
ORA-00280: change 227047985 for thread 1 is in sequence #169
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_168_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227047991 generated at 10/29/2009 13:48:05 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_170_700066042.dbf
ORA-00280: change 227047991 for thread 1 is in sequence #170
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_169_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227048564 generated at 10/29/2009 13:58:56 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_171_700066042.dbf
ORA-00280: change 227048564 for thread 1 is in sequence #171
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_170_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227048691 generated at 10/29/2009 13:58:59 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_172_700066042.dbf
ORA-00280: change 227048691 for thread 1 is in sequence #172
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_171_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227048825 generated at 10/29/2009 13:59:02 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_173_700066042.dbf
ORA-00280: change 227048825 for thread 1 is in sequence #173
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_172_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227048946 generated at 10/29/2009 13:59:05 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_174_700066042.dbf
ORA-00280: change 227048946 for thread 1 is in sequence #174
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_173_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227049232 generated at 10/29/2009 14:00:28 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_175_700066042.dbf
ORA-00280: change 227049232 for thread 1 is in sequence #175
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_174_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227049348 generated at 10/29/2009 14:00:32 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_176_700066042.dbf
ORA-00280: change 227049348 for thread 1 is in sequence #176
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_175_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227049460 generated at 10/29/2009 14:00:35 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_177_700066042.dbf
ORA-00280: change 227049460 for thread 1 is in sequence #177
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_176_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227049579 generated at 10/29/2009 14:00:38 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_178_700066042.dbf
ORA-00280: change 227049579 for thread 1 is in sequence #178
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_177_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227049696 generated at 10/29/2009 14:00:41 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_179_700066042.dbf
ORA-00280: change 227049696 for thread 1 is in sequence #179
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_178_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227049914 generated at 10/29/2009 14:04:03 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_180_700066042.dbf
ORA-00280: change 227049914 for thread 1 is in sequence #180
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_179_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050020 generated at 10/29/2009 14:04:06 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_181_700066042.dbf
ORA-00280: change 227050020 for thread 1 is in sequence #181
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_180_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050112 generated at 10/29/2009 14:04:09 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_182_700066042.dbf
ORA-00280: change 227050112 for thread 1 is in sequence #182
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_181_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050200 generated at 10/29/2009 14:04:11 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_183_700066042.dbf
ORA-00280: change 227050200 for thread 1 is in sequence #183
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_182_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050309 generated at 10/29/2009 14:04:15 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_184_700066042.dbf
ORA-00280: change 227050309 for thread 1 is in sequence #184
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_183_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050399 generated at 10/29/2009 14:04:18 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_185_700066042.dbf
ORA-00280: change 227050399 for thread 1 is in sequence #185
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_184_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050489 generated at 10/29/2009 14:04:20 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_186_700066042.dbf
ORA-00280: change 227050489 for thread 1 is in sequence #186
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_185_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050597 generated at 10/29/2009 14:04:24 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_187_700066042.dbf
ORA-00280: change 227050597 for thread 1 is in sequence #187
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_186_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050688 generated at 10/29/2009 14:04:27 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_188_700066042.dbf
ORA-00280: change 227050688 for thread 1 is in sequence #188
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_187_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050760 generated at 10/29/2009 14:05:27 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_189_700066042.dbf
ORA-00280: change 227050760 for thread 1 is in sequence #189
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_188_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050862 generated at 10/29/2009 14:05:48 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_190_700066042.dbf
ORA-00280: change 227050862 for thread 1 is in sequence #190
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_189_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227050953 generated at 10/29/2009 14:05:50 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_191_700066042.dbf
ORA-00280: change 227050953 for thread 1 is in sequence #191
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_190_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227051066 generated at 10/29/2009 14:05:54 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_192_700066042.dbf
ORA-00280: change 227051066 for thread 1 is in sequence #192
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_191_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227051154 generated at 10/29/2009 14:05:57 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_193_700066042.dbf
ORA-00280: change 227051154 for thread 1 is in sequence #193
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_192_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227051251 generated at 10/29/2009 14:05:59 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_194_700066042.dbf
ORA-00280: change 227051251 for thread 1 is in sequence #194
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_193_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227051364 generated at 10/29/2009 14:06:03 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_195_700066042.dbf
ORA-00280: change 227051364 for thread 1 is in sequence #195
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_194_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227051463 generated at 10/29/2009 14:06:06 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_196_700066042.dbf
ORA-00280: change 227051463 for thread 1 is in sequence #196
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_195_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 227051572 generated at 10/29/2009 14:06:09 needed for thread 1
ORA-00289: suggestion : /restore1/oracle/SID/admin/a01/1_197_700066042.dbf
ORA-00280: change 227051572 for thread 1 is in sequence #197
ORA-00278: log file ‘/restore1/oracle/SID/admin/a01/1_196_700066042.dbf’ no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
6. Once the recovery is completed check the status of datafile and put the datafile in online.
SQL> select name,file#,status from v$datafile;
NAME FILE# STATUS
—————————————————————————————- ———- ——-
/restore1/oracle/SID/data/SID_system01.dbf 1 SYSTEM
/restore1/oracle/SID/data/SID_undotbs01.dbf 2 ONLINE
/restore1/oracle/SID/data/SID_sysaux01.dbf 3 ONLINE
/restore1/oracle/SID/data/SID_users01.dbf 4 ONLINE
/restore1/oracle/SID/data/SID_autodata01.dbf 5 ONLINE
/restore1/oracle/SID/data/SID_autoindexes01.dbf 6 ONLINE
/restore1/oracle/SID/data/SID_users02.dbf 7 OFFLINE
7 rows selected.
SQL>alter database datafile 7 online.
Database altered.
SQL> select name,file#,status from v$datafile;
NAME FILE# STATUS
—————————————————————————————- ———- ——-
/restore1/oracle/SID/data/SID_system01.dbf 1 SYSTEM
/restore1/oracle/SID/data/SID_undotbs01.dbf 2 ONLINE
/restore1/oracle/SID/data/SID_sysaux01.dbf 3 ONLINE
/restore1/oracle/SID/data/SID_users01.dbf 4 ONLINE
/restore1/oracle/SID/data/SID_autodata01.dbf 5 ONLINE
/restore1/oracle/SID/data/SID_autoindexes01.dbf 6 ONLINE
/restore1/oracle/SID/data/SID_users02.dbf 7 ONLINE
7 rows selected.
SQL> select count(1) from dba_segments_users;
COUNT(1)
———-
1149184
8. Take the backup of database if it is required.
Recent Posts