OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » invalid datafile (Oracle 7.3.4.5.0, Solaris 8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

invalid datafile [message #675484]

Tue, 02 April 2019 05:19

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Gurus,

Good day.

Need your expertise on how to resolve an issue with invalid datafile name.
This was caused by erroneous "add and rename datafile" command.
Please refer below:

SQL> select file_name, file_id, tablespace_name, status
  2  from dba_data_files 
  3  order by tablespace_name;

FILE_NAME                                                 FILE_ID TABLESPACE_NAME    STATUS
--------------------------------------------------------- ------- ------------------ ---------
/VOL03/oradata/abc/ndex/ctm_idx_lrg31.dbf                     389 CTM_IDX_LRG        AVAILABLE
MISSING0466 **                                                466 CTM_IDX_LRG        AVAILABLE
/VOL03/oradata/abc/ndex/ctm_idx_lrg32.dbf                     409 CTM_IDX_LRG        AVAILABLE
...
/VOL02/oradata/abc/data/tas_data_lrg40.dbf                    470 TAS_DATA_LRG       AVAILABLE
MISSING0467 **                                                467 TAS_DATA_LRG       AVAILABLE
/VOL02/oradata/abc/data/tas_data_lrg15.dbf                    237 TAS_DATA_LRG       AVAILABLE

SQL> select FILE_NAME, TABLESPACE_NAME, STATUS
  2  from DBA_DATA_FILES
  3  where FILE_NAME like 'MISSING0467';

FILE_NAME                                                    TABLESPACE_NAME                STATUS
------------------------------------------------------------ ------------------------------ ---------
MISSING0467                                                  TAS_DATA_LRG                   AVAILABLE

SQL> select FILE_NAME, TABLESPACE_NAME, STATUS
  2  from DBA_DATA_FILES
  3  where FILE_NAME like 'MISSING0466';

FILE_NAME                                                    TABLESPACE_NAME                STATUS
------------------------------------------------------------ ------------------------------ ---------
MISSING0466                                                  CTM_IDX_LRG                    AVAILABLE

Thank you very much.

*BlackSwan added {codetags}
Please do so yourself in the future.

Attachment: 2019-04-02_orafaq.jpg
(Size: 184.78KB, Downloaded 3301 times)

[Updated on: Tue, 02 April 2019 11:51] by Moderator

Report message to a moderator

Re: invalid datafile [message #675503 is a reply to message #675484]

Tue, 02 April 2019 11:45

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Format your output this is unreadable.

Report message to a moderator

Re: invalid datafile [message #675516 is a reply to message #675503]

Wed, 03 April 2019 04:12

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

Oracle 7.3.4.5.0, Solaris 8

[Updated on: Wed, 03 April 2019 04:12]

Report message to a moderator

Re: invalid datafile [message #675518 is a reply to message #675484]

Wed, 03 April 2019 04:38

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

What was the command that was used to add this data file?

In my experience, having a data file with a FILE_NAME of 'MISSING%' means that the database was started up with a control file that was created before the data file was added.

Report message to a moderator

Re: invalid datafile [message #675533 is a reply to message #675518]

Wed, 03 April 2019 20:40

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@gazzag

Sir thanks for this.
Below were excerpts from the alert.log:

Tue Feb 26 10:01:47 2019
ORA-1013 signalled during: alter database rename file '/VOL02/oradata/abc/da...
...
Tue Feb 26 10:15:46 2019
ORA-600 signalled during: alter database backup controlfile to '/u3arch/arch...
Tue Feb 26 10:15:46 2019
alter database backup controlfile to trace
Tue Feb 26 10:19:48 2019
alter database datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' offline drop
...
Tue Feb 26 11:34:07 2019
Starting ORACLE instance (normal)
Tue Feb 26 11:40:26 2019
alter tablespace CTM_IDX_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orada
ta/abc/ndex/ctm_idx_lrg37.dbf'
Tue Feb 26 11:40:26 2019
ORA-1525 signalled during: alter tablespace CTM_IDX_LRG rename datafile '/VOL...
Tue Feb 26 11:40:54 2019
alter tablespace CTM_IDX_LRG offline
Tue Feb 26 11:46:32 2019
ORA-1115 signalled during: alter tablespace CTM_IDX_LRG offline
...
Tue Feb 26 11:46:39 2019
Thread 1 advanced to log sequence 377817
Current log# 1 seq# 377817 mem# 0: /u101/oradata/abc/redo/redo1a.dbf
Current log# 1 seq# 377817 mem# 1: /u201/oradata/abc/redo/redo1b.dbf
Tue Feb 26 12:00:12 2019
alter tablespace TAS_DATA_LRG add datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' size 200M
Tue Feb 26 12:16:24 2019
Errors in file /appl1/home/oracle734/admin/abc/udump/abc_ora_18997.trc:
ORA-00600: internal error code, arguments: [2103], [1], [0], [1], [900], [], [], []
Tue Feb 26 12:16:36 2019
ORA-600 signalled during: alter tablespace TAS_DATA_LRG add datafile '/VOL02...
...
Tue Feb 26 13:01:54 2019
ORA-1122 signalled during: alter tablespace CTM_IDX_LRG offline normal
...
Tue Feb 26 13:09:40 2019
alter database datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' resize 400M
Tue Feb 26 13:17:26 2019
ORA-1122 signalled during: alter database datafile '/VOL02/oradata/abc/data/...
Tue Feb 26 13:19:49 2019
Thread 1 advanced to log sequence 377820
Current log# 2 seq# 377820 mem# 0: /u301/oradata/abc/redo/redo2a.dbf
Current log# 2 seq# 377820 mem# 1: /u401/oradata/abc/redo/redo2b.dbf
Tue Feb 26 13:57:53 2019
ALTER TABLESPACE CTM_IDX_LRG OFFLINE NORMAL
...
Tue Feb 26 14:37:40 2019
ORA-1122 signalled during: ALTER TABLESPACE CTM_IDX_LRG OFFLINE NORMAL
...
Tue Feb 26 14:39:35 2019
alter tablespace TAS_DATA_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orad
ata/abc/ndex/tas_data_lrg38.dbf'
Tue Feb 26 14:39:35 2019
ORA-1525 signalled during: alter tablespace TAS_DATA_LRG rename datafile '/VO...
Tue Feb 26 14:40:11 2019
alter tablespace CTM_IDX_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orada
ta/abc/ndex/tas_data_lrg38.dbf'
Tue Feb 26 14:40:11 2019
ORA-1525 signalled during: alter tablespace CTM_IDX_LRG rename datafile '/VOL...
Tue Feb 26 14:40:51 2019
alter tablespace CTM_IDX_LRG offline normal
Tue Feb 26 14:40:54 2019
ORA-1013 signalled during: alter tablespace CTM_IDX_LRG offline normal
...
Tue Feb 26 14:52:02 2019
alter tablespace CTM_IDX_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orada
ta/abc/ndex/tas_data_lrg38.dbf'
Tue Feb 26 14:59:15 2019
ORA-1525 signalled during: alter tablespace CTM_IDX_LRG rename datafile '/VOL...
Tue Feb 26 14:59:15 2019
Thread 1 advanced to log sequence 377822
Current log# 4 seq# 377822 mem# 0: /u302/oradata/abc/redo/redo4a.dbf
Current log# 4 seq# 377822 mem# 1: /u402/oradata/abc/redo/redo4b.dbf
Tue Feb 26 08:45:09 2019
alter tablespace CTM_IDX_LRG add datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' size 200M
Tue Feb 26 08:59:24 2019
Completed: alter tablespace CTM_IDX_LRG add datafile '/VOL02/...
Tue Feb 26 09:02:24 2019
alter tablespace CTM_IDX_LRG add datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' size 200M
...
Tue Feb 26 09:48:42 2019
alter tablespace TAS_DATA_LRG add datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' size 200M
Tue Feb 26 09:48:42 2019
ORA-1537 signalled during: alter tablespace TAS_DATA_LRG add datafile '/VOL02...
Tue Feb 26 09:52:32 2019
alter database rename file '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL02/oradata/abc/data/tas_d
ata_lrg38.dbf'
Tue Feb 26 09:52:38 2019
ORA-1013 signalled during: alter database rename file '/VOL02/oradata/abc/da...
Tue Feb 26 09:53:09 2019
alter database rename file '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/oradata/abc/ndex/tas_d
ata_lrg38.dbf'
Tue Feb 26 10:00:06 2019
alter database backup controlfile to '/u3arch/archive/abc/abc022619100001.ctl'
Tue Feb 26 10:01:47 2019
ORA-1013 signalled during: alter database rename file '/VOL02/oradata/abc/da...
...
Tue Feb 26 10:15:46 2019
ORA-600 signalled during: alter database backup controlfile to '/u3arch/arch...
Tue Feb 26 10:15:46 2019
alter database backup controlfile to trace
Tue Feb 26 10:19:48 2019
alter database datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' offline drop
...
Tue Feb 26 11:34:07 2019
Starting ORACLE instance (normal)
Tue Feb 26 11:40:26 2019
alter tablespace CTM_IDX_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orada
ta/abc/ndex/ctm_idx_lrg37.dbf'
Tue Feb 26 11:40:26 2019
ORA-1525 signalled during: alter tablespace CTM_IDX_LRG rename datafile '/VOL...
Tue Feb 26 11:40:54 2019
alter tablespace CTM_IDX_LRG offline
Tue Feb 26 11:46:32 2019
ORA-1115 signalled during: alter tablespace CTM_IDX_LRG offline
...
Tue Feb 26 11:46:39 2019
Thread 1 advanced to log sequence 377817
Current log# 1 seq# 377817 mem# 0: /u101/oradata/abc/redo/redo1a.dbf
Current log# 1 seq# 377817 mem# 1: /u201/oradata/abc/redo/redo1b.dbf
Tue Feb 26 12:00:12 2019
alter tablespace TAS_DATA_LRG add datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' size 200M
Tue Feb 26 12:16:24 2019
Errors in file /appl1/home/oracle734/admin/abc/udump/abc_ora_18997.trc:
ORA-00600: internal error code, arguments: [2103], [1], [0], [1], [900], [], [], []
Tue Feb 26 12:16:36 2019
ORA-600 signalled during: alter tablespace TAS_DATA_LRG add datafile '/VOL02...
...
Tue Feb 26 13:01:54 2019
ORA-1122 signalled during: alter tablespace CTM_IDX_LRG offline normal
...
Tue Feb 26 13:09:40 2019
alter database datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' resize 400M
Tue Feb 26 13:17:26 2019
ORA-1122 signalled during: alter database datafile '/VOL02/oradata/abc/data/...
Tue Feb 26 13:19:49 2019
Thread 1 advanced to log sequence 377820
Current log# 2 seq# 377820 mem# 0: /u301/oradata/abc/redo/redo2a.dbf
Current log# 2 seq# 377820 mem# 1: /u401/oradata/abc/redo/redo2b.dbf
Tue Feb 26 13:57:53 2019
ALTER TABLESPACE CTM_IDX_LRG OFFLINE NORMAL
...
Tue Feb 26 14:37:40 2019
ORA-1122 signalled during: ALTER TABLESPACE CTM_IDX_LRG OFFLINE NORMAL
...
Tue Feb 26 14:39:35 2019
alter tablespace TAS_DATA_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orad
ata/abc/ndex/tas_data_lrg38.dbf'
Tue Feb 26 14:39:35 2019
ORA-1525 signalled during: alter tablespace TAS_DATA_LRG rename datafile '/VO...
Tue Feb 26 14:40:11 2019
alter tablespace CTM_IDX_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orada
ta/abc/ndex/tas_data_lrg38.dbf'
Tue Feb 26 14:40:11 2019
ORA-1525 signalled during: alter tablespace CTM_IDX_LRG rename datafile '/VOL...
Tue Feb 26 14:40:51 2019
alter tablespace CTM_IDX_LRG offline normal
Tue Feb 26 14:40:54 2019
ORA-1013 signalled during: alter tablespace CTM_IDX_LRG offline normal
...
Tue Feb 26 14:52:02 2019
alter tablespace CTM_IDX_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orada
ta/abc/ndex/tas_data_lrg38.dbf'
Tue Feb 26 14:59:15 2019
ORA-1525 signalled during: alter tablespace CTM_IDX_LRG rename datafile '/VOL...
Tue Feb 26 14:59:15 2019
Thread 1 advanced to log sequence 377822
Current log# 4 seq# 377822 mem# 0: /u302/oradata/abc/redo/redo4a.dbf
Current log# 4 seq# 377822 mem# 1: /u402/oradata/abc/redo/redo4b.dbf
Tue Feb 26 15:04:31 2019
alter tablespace TAS_DATA_LRG add datafile '/VOL02/oradata/abc/data/tas_data_lrg39.dbf' size 200M
Tue Feb 26 15:05:58 2019
Completed: alter tablespace TAS_DATA_LRG add datafile '/VOL02...
Tue Feb 26 15:11:02 2019
alter tablespace CTM_IDX_LRG rename datafile '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' to '/VOL03/orada
ta/abc/ndex/tas_data_lrg38.dbf'
Tue Feb 26 15:12:19 2019
alter database datafile '/VOL03/oradata/abc/ndex/ctm_idx_lrg36.dbf' resize 1600M
Tue Feb 26 15:14:16 2019
alter database datafile '/VOL02/oradata/abc/data/tas_data_lrg39.dbf' resize 400M
Tue Feb 26 15:14:30 2019
ORA-1525 signalled during: alter tablespace CTM_IDX_LRG rename datafile '/VOL...
Tue Feb 26 15:14:59 2019
Completed: alter database datafile '/VOL02/oradata/abc/data/...
Tue Feb 26 15:15:35 2019
Completed: alter database datafile '/VOL03/oradata/abc/ndex/...
Tue Feb 26 15:16:03 2019
alter tablespace CTM_IDX_LRG offline
...
Wed Feb 27 09:45:20 2019
File #466 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING0466' in the controlfile.
File #467 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING0467' in the controlfile.
Dictionary check complete
Wed Feb 27 09:45:20 2019

I also attached the script to recreate the control file
excluding the "missing" datafiles executed last Feb. 27 after
the dB had error upon startup of "failed verification check".

Thanks for your time.

Attachment: abc_db-crea-ctl.txt
(Size: 22.96KB, Downloaded 3343 times)

Report message to a moderator

Re: invalid datafile [message #675534 is a reply to message #675533]

Thu, 04 April 2019 03:10

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

As I suspected:

Wed Feb 27 09:45:20 2019
File #466 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING0466' in the controlfile.
File #467 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING0467' in the controlfile.
Dictionary check complete

The database was started with a control file that was created before you added the two "MISSING%" data files. You will need to recreate the control file using the CREATE CONTROLFILE script that you have attached. You have to ensure that it has entries for the the two "MISSING%" data files too. The control file is what tells Oracle what data files the database comprises of. However, I would shut the database down and take a cold backup first so you can get back to where you are now if needed. Also, you have an ORA-600 error being thrown:

Tue Feb 26 10:15:46 2019
ORA-600 signalled during: alter database backup controlfile to '/u3arch/arch...

I would open a SR with Oracle. Although seeing as you are on a version of Oracle that is almost thirty years old I wonder if you even have support or how important this database really is.

[edit: typo]

[Updated on: Thu, 04 April 2019 05:48]

Report message to a moderator

Re: invalid datafile [message #675702 is a reply to message #675534]

Sun, 14 April 2019 06:16

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@gazzag,

Sir, I have done what you've suggesting using re-create control file script.

But still those "MISSING" data files were considered as non-existent.
They cannot be renamed, recovered, re-created and cannot be brought ONLINE.
Please refer below:

SQL> select name from v$datafile;

NAME
-------------------------------------------------------
...
/VOL02/oradata/abc/data/dbcon_data_45.dbf
MISSING0466
MISSING0467
/VOL03/oradata/abc/ndex/rps_idx_lrg44.dbf

471 rows selected.

=============
SQL> select * from v$recover_file;

FILE# ONLINE ERROR CHANGE# TIME
----- ------- ------------ ------- -----------------
466 OFFLINE FILE MISSING 0 01/01/88 00:00:00
467 OFFLINE FILE MISSING 0 01/01/88 00:00:00

=============
SQL> alter database recover datafile '/VOL02/oradata/abc/data/MISSING0467';
alter database recover datafile '/VOL02/oradata/abc/data/MISSING0467'
*
ERROR at line 1:
ORA-01179: file /VOL02/oradata/abc/data/MISSING0467 does not exist

=============
SQL> alter database recover datafile 467;
alter database recover datafile 467

ERROR at line 1:
ORA-02236: invalid file name

=============
SQL> alter database datafile 'MISSING0467' online;
alter database datafile 'MISSING0467' online
*
ERROR at line 1:
ORA-01157: cannot identify data file 467 - file not found
ORA-01111: name for data file 467 is unknown - rename to correct file
ORA-01110: data file 467: 'MISSING0467'

=============
SQL> alter tablespace TAS_DATA_LRG rename datafile '/VOL02/oradata/abc/data/MISSING0467'
2 to '/VOL02/oradata/abc/data/tas_data_lrg39.dbf';
alter tablespace TAS_DATA_LRG rename datafile '/VOL02/oradata/abc/data/MISSING0467'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01522: file '/VOL02/oradata/abc/data/MISSING0467' to be renamed does not
exist

=============
SQL> alter database create datafile '/VOL02/oradata/abc/data/MISSING0467'
2 as '/VOL04/oradata/abc/temp/tas_data_lrg39.dbf';
alter database create datafile '/VOL02/oradata/abc/data/MISSING0467'
*
ERROR at line 1:
ORA-01516: nonexistent log/data file '/VOL02/oradata/abc/data/MISSING0467'

We tried to insert new record to the affected table re: TAS_TP_LEDGER_TRANS but
this was the error:

Error at line 1:
ORA-00376: file 467 cannot be read at this time
ORA-01111: name for data file 467 is unknown - rename to correct the file
ORA-01110: data file 467: 'MISSING0467'

Thank you for your patience.

[Updated on: Sun, 14 April 2019 06:17]

Report message to a moderator

Re: invalid datafile [message #675703 is a reply to message #675702]

Sun, 14 April 2019 08:19

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Sir, I have done what you've suggesting using re-create control file script.

But still those "MISSING" data files were considered as non-existent.
They cannot be renamed, recovered, re-created and cannot be brought ONLINE.

This tells me that your CREATE CONTROLFILE command was incomplete, it did not include the missing files. You must edit it to include them. The point is that file names are not stored in the data dictionary, they exist only in the controlfile. So you have to put all of them in there. When you open the database, Oracle finds references to the file numbers in the dictionary but cannot determine their names because they were not included in your CREATE CONTROLFILE command. So it names them MISSINGnnnn.

Report message to a moderator

Re: invalid datafile [message #675712 is a reply to message #675534]

Mon, 15 April 2019 04:37

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@gazzag,

Sir, I have done what you've suggesting using the re-create control file script.
Below are part of that script:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ABC" NORESETLOGS ARCHIVELOG
....
'/VOL03/oradata/abc/ndex/tas_idx_lrg35.dbf',
'MISSING0466',
'MISSING0467',
'/VOL02/oradata/abc/data/tas_data_lrg40.dbf',
;
ALTER DATABASE DATAFILE 'MISSING0466' OFFLINE;
ALTER DATABASE DATAFILE 'MISSING0467' OFFLINE;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;

I noticed that the database cannot be restarted if the 'ALTER DATABASE DATAFILE OFFLINE'
parameter was not included.

Still those 'MISSING' datafiles were considered as non-existent.
They cannot be recreated, be brought ONLINE, recovered nor renamed.

==========================
SQL> select * from v$recover_file where error like '%FILE%';

FILE# ONLINE ERROR CHANGE# TIME
----- ------- ------------ ---------- --------
466 OFFLINE FILE MISSING 0 01/01/88 00:00:00
467 OFFLINE FILE MISSING 0 01/01/88 00:00:00

SQL> select file#,name from v$datafile where file#=467;

FILE# NAME
----- ----------------------------------------
467 MISSING0467

==========================
SQL> alter database create datafile '/VOL02/oradata/abc/data/MISSING0467'
2 as '/VOL04/oradata/abc/temp/tas_data_lrg39.dbf';
alter database create datafile '/VOL02/oradata/abc/data/MISSING0467'
*
ERROR at line 1:
ORA-01516: nonexistent log/data file '/VOL02/oradata/abc/data/MISSING0467'

==========================
SQL> ALTER DATABASE DATAFILE 'MISSING0467' ONLINE;
ALTER DATABASE DATAFILE 'MISSING0467' ONLINE
*
ERROR at line 1:
ORA-01157: cannot identify data file 467 - file not found
ORA-01111: name for data file 467 is unknown - rename to correct file
ORA-01110: data file 467: 'MISSING0467'

==========================
SQL> alter database recover datafile '/VOL02/oradata/abc/data/MISSING0467';
alter database recover datafile '/VOL02/oradata/abc/data/MISSING0467'
*
ERROR at line 1:
ORA-01179: file /VOL02/oradata/abc/data/MISSING0467 does not exist

SQL> alter database recover datafile 467;
alter database recover datafile 467
*
ERROR at line 1:
ORA-02236: invalid file name

==========================
SQL> alter tablespace TAS_DATA_LRG rename datafile '/VOL02/oradata/abc/data/MISSING0467'
2 to '/VOL02/oradata/abc/data/tas_data_lrg39.dbf';
alter tablespace TAS_DATA_LRG rename datafile '/VOL02/oradata/abc/data/MISSING0467'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01522: file '/VOL02/oradata/abc/data/MISSING0467' to be renamed does not
exist

Also, we had this error whenever we simulate to insert new data:

inserting TAS_TP_LEDGER_TRANS....
insert into TAS_TP_LEDGER_TRANS
*
ERROR at line 1:
ORA-00376: file 467 cannot be read at this time
ORA-01111: name for data file 467 is unknown - rename to correct file
ORA-01110: data file 467: 'MISSING0467'

Thank you for your patience.

Report message to a moderator

Re: invalid datafile [message #675713 is a reply to message #675712]

Mon, 15 April 2019 04:43

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Think, man! You have to include the names of the missing files in the CREATE CONTROLFILE script. What are their names?

Report message to a moderator

Re: invalid datafile [message #675714 is a reply to message #675712]

Mon, 15 April 2019 04:46

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@John Watson,

Sir, thanks for this.
Currently, I'm waiting for a window where I can try it.

Best regards,

** I'm sorry I had it posted twice (I thought my previous post were missing). **

Report message to a moderator

Re: invalid datafile [message #675716 is a reply to message #675714]

Mon, 15 April 2019 05:16

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@John Watson,

Sir, sorry I misinterpret your point and gazzag.

This is my dilemma I can't just pinpoint the exact names of this 2 'MISSING' files as this was executed by a new DBA without our knowledge.
Based on the the alert.log, he implemented to add a new datafile which was created in a different directory and tablespace.
After realizing his fault, he tried to move that same datafile to a correct tablespace but a with different file name,
then he also executed resizing it.

Checking the OS files, it created 3 datafiles.
Are these infos below enough for my basis for its filenames?

419438592 Feb 26 17:42 tas_data_lrg39.dbf (400 MB) -> /VOL02/oradata/abc/data (TAS_DATA_LRG)
20979712 Feb 27 09:27 tas_data_lrg38.dbf (20 MB) -> /VOL02/oradata/abc/data (TAS_DATA_LRG)
20979712 Feb 27 09:30 tas_data_lrg38.dbf (20 MB) -> /VOL03/oradata/abc/ndex (CTM_IDX_LRG)

SQL> select FILE_NAME, TABLESPACE_NAME, STATUS
from DBA_DATA_FILES
where FILE_NAME like 'MISSING0467';

FILE_NAME TABLESPACE_NAME STATUS
----------- --------------- ----------
MISSING0467 TAS_DATA_LRG AVAILABLE

SQL> select FILE_NAME, TABLESPACE_NAME, STATUS
from DBA_DATA_FILES
where FILE_NAME like 'MISSING0466';

FILE_NAME TABLESPACE_NAME STATUS
----------- --------------- ----------
MISSING0466 CTM_IDX_LRG AVAILABLE

Thank you very much.

Report message to a moderator

Re: invalid datafile [message #675806 is a reply to message #675534]

Sun, 21 April 2019 20:38

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@gazzag

Sir, I re-run my recreate control file scripts, eliminating the
problematic "MISSING" datafiles and included 1 file,
re: "/VOL02/oradata/abc/data/tas_data_lrg39.dbf"
Please refer below:

1st run
==============================================================
SVRMGR> @abc_recreate_ctlf_20190416.sql
ORACLE instance started.

CREATE CONTROLFILE REUSE DATABASE "ABC" NORESETLOGS ARCHIVELOG
*
ORA-01503: CREATE CONTROLFILE failed
ORA-01167: two files are the same file/group number or the same file
ORA-01110: data file 250: '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' **
ORA-01110: data file 250: '/VOL02/oradata/abc/data/tas_data_sml01.dbf'
ORA-01507: database not mounted
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ORA-01507: database not mounted
SVRMGR> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

2nd run- (remove from controlfile script: tas_data_lrg38.dbf **)
================================================================
SVRMGR> @abc_recreate_ctlf_20190416a.sql
ORACLE instance started.

CREATE CONTROLFILE REUSE DATABASE "ABC" NORESETLOGS ARCHIVELOG
*
ORA-01503: CREATE CONTROLFILE failed
ORA-01167: two files are the same file/group number or the same file
ORA-01110: data file 250: '/VOL03/oradata/abc/ndex/tas_data_lrg38.dbf' ***
ORA-01110: data file 250: '/VOL02/oradata/abc/data/tas_data_sml01.dbf'
ORA-01507: database not mounted
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ORA-01507: database not mounted
SVRMGR> shutdown immediate;

3rd run -(remove from controlfile script: tas_data_lrg38.dbf ***)
================================================================
SVRMGR> @abc_recreate_ctlf_20190416b.sql
ORACLE instance started.

ORA-00279: Change 353161828602 generated at 02/26/19 17:27:24 needed for thread 1
ORA-00289: Suggestion : /u1arch/archive/abc/abcarch_377825.arc
ORA-00280: Change 353161828602 for thread 1 is in sequence #377825
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u1arch/archive/abc/abcarch_377825.arc'
ORA-07360: sfifi: stat error, unable to obtain information about file.
SVR4 Error: 2: No such file or directory

ORA-00279: Change 353161828602 generated at 02/26/19 17:27:24 needed for thread 1
ORA-00289: Suggestion : /u1arch/archive/abc/abcarch_377825.arc
ORA-00280: Change 353161828602 for thread 1 is in sequence #377825
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u1arch/archive/abc/abcarch_377825.arc'
ORA-07360: sfifi: stat error, unable to obtain information about file.
SVR4 Error: 2: No such file or directory

CANCEL

Statement processed.
ALTER DATABASE OPEN
*
ORA-01113: file 467 needs media recovery
ORA-01110: data file 467: '/VOL02/oradata/abc/data/tas_data_lrg39.dbf'
SVRMGR> alter database recover datafile '/VOL02/oradata/abc/data/tas_data_lrg39.dbf';
alter database recover datafile '/VOL02/oradata/abc/data/tas_data_lrg39.dbf'
*
ORA-00279: Change 353161828602 generated at 02/26/19 17:27:24 needed for thread 1
ORA-00289: Suggestion : /u1arch/archive/abc/abcarch_377825.arc
ORA-00280: Change 353161828602 for thread 1 is in sequence #377825
SVRMGR>
SVRMGR> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR>

The ABC database cannot be opened and I just ran another controlfile script to exclude
the "/VOL02/oradata/abc/data/tas_data_lrg39.dbf" datafile in order that users can continue
to utilize our system. But the same issue was still unresolved.

My another dilemma was the required archive logs (re: abcarch_377825.arc) was not available anymore.
Those missing archived logs were from dates of 2/25/2019 to 3/8/2019, and the rest from 3/9/2019
up to the latest are still intact.

Can we recover from a minimal data loss-- given there were only 2 datafiles had problems while
the rest are still up-to-date?

Thank you very much.

Report message to a moderator

Re: invalid datafile [message #675808 is a reply to message #675806]

Mon, 22 April 2019 01:13

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Please use [code] tags,
How to use code tags and make your code easier to read

Report message to a moderator

Re: invalid datafile [message #675811 is a reply to message #675808]

Mon, 22 April 2019 01:55

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@John Watson
Sir how about the codes below:

1st run
==============================================================
SVRMGR> @abc_recreate_ctlf_20190416.sql
ORACLE instance started.

CREATE CONTROLFILE REUSE DATABASE "ABC" NORESETLOGS ARCHIVELOG
*
ORA-01503: CREATE CONTROLFILE failed
ORA-01167: two files are the same file/group number or the same file
ORA-01110: data file 250: '/VOL02/oradata/abc/data/tas_data_lrg38.dbf' **
ORA-01110: data file 250: '/VOL02/oradata/abc/data/tas_data_sml01.dbf'
ORA-01507: database not mounted
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ORA-01507: database not mounted
SVRMGR> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

2nd run- (remove from controlfile script: tas_data_lrg38.dbf **)
================================================================
SVRMGR> @abc_recreate_ctlf_20190416a.sql
ORACLE instance started.

CREATE CONTROLFILE REUSE DATABASE "ABC" NORESETLOGS ARCHIVELOG
*
ORA-01503: CREATE CONTROLFILE failed
ORA-01167: two files are the same file/group number or the same file
ORA-01110: data file 250: '/VOL03/oradata/abc/ndex/tas_data_lrg38.dbf' ***
ORA-01110: data file 250: '/VOL02/oradata/abc/data/tas_data_sml01.dbf'
ORA-01507: database not mounted
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ORA-01507: database not mounted
SVRMGR> shutdown immediate;

3rd run -(remove from controlfile script: tas_data_lrg38.dbf ***)
================================================================
SVRMGR> @abc_recreate_ctlf_20190416b.sql
ORACLE instance started.

ORA-00279: Change 353161828602 generated at 02/26/19 17:27:24 needed for thread 1
ORA-00289: Suggestion : /u1arch/archive/abc/abcarch_377825.arc
ORA-00280: Change 353161828602 for thread 1 is in sequence #377825
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u1arch/archive/abc/abcarch_377825.arc'
ORA-07360: sfifi: stat error, unable to obtain information about file.
SVR4 Error: 2: No such file or directory

ORA-00279: Change 353161828602 generated at 02/26/19 17:27:24 needed for thread 1
ORA-00289: Suggestion : /u1arch/archive/abc/abcarch_377825.arc
ORA-00280: Change 353161828602 for thread 1 is in sequence #377825
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u1arch/archive/abc/abcarch_377825.arc'
ORA-07360: sfifi: stat error, unable to obtain information about file.
SVR4 Error: 2: No such file or directory

CANCEL

Statement processed.
ALTER DATABASE OPEN
*
ORA-01113: file 467 needs media recovery
ORA-01110: data file 467: '/VOL02/oradata/abc/data/tas_data_lrg39.dbf'
SVRMGR> alter database recover datafile '/VOL02/oradata/abc/data/tas_data_lrg39.dbf';
alter database recover datafile '/VOL02/oradata/abc/data/tas_data_lrg39.dbf'
*
ORA-00279: Change 353161828602 generated at 02/26/19 17:27:24 needed for thread 1
ORA-00289: Suggestion : /u1arch/archive/abc/abcarch_377825.arc
ORA-00280: Change 353161828602 for thread 1 is in sequence #377825
SVRMGR>
SVRMGR> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR>

Report message to a moderator

Re: invalid datafile [message #675818 is a reply to message #675811]

Mon, 22 April 2019 03:19

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

It looks as though you have no backup and missing archivelogs, so restore and recover is impossible. I see two possbilities:

Either you could take the file offline:
ALTER DATABASE DATAFILE 467 OFFLINE DROP;
open the database and drop the tablespace.

Or you could
ALTER SYSTEM SET "_ALLOW_RESETLOGS_CORRUPTION"=TRUE;
and open the database with RESETLOGS.

I can't remember if those work in release 7. And be sure to research them first.

Report message to a moderator

Re: invalid datafile [message #675874 is a reply to message #675484]

Fri, 26 April 2019 20:25

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@John Watson

Sir, please see below:

SQL> alter database datafile 467 offline drop;
alter database datafile 467 offline drop
*
ERROR at line 1:
ORA-02236: invalid file name

SQL> alter database datafile MISSING0467 offline drop;
alter database datafile MISSING0467 offline drop
*
ERROR at line 1:
ORA-02236: invalid file name

SQL> alter database datafile '/VOL02/oradata/abc/data/MISSING0467' offline drop;
alter database datafile '/VOL02/oradata/abc/data/MISSING0467' offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log/data file '/VOL02/oradata/abc/data/MISSING0467'

Report message to a moderator

Re: invalid datafile [message #675875 is a reply to message #675874]

Fri, 26 April 2019 21:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://lmgtfy.com/?q=oracle+drop+missing+datafile

Report message to a moderator

Re: invalid datafile [message #675876 is a reply to message #675818]

Fri, 26 April 2019 22:17

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@John Watson,

Sir, please see below:

SQL> alter database datafile 467 offline drop;
alter database datafile 467 offline drop
*
ERROR at line 1:
ORA-02236: invalid file name

SQL> alter database datafile MISSING0467 offline drop;
alter database datafile MISSING0467 offline drop
*
ERROR at line 1:
ORA-02236: invalid file name

SQL> alter database datafile '/VOL02/oradata/abc/data/MISSING0467' offline drop;
alter database datafile '/VOL02/oradata/abc/data/MISSING0467' offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log/data file '/VOL02/oradata/abc/data/MISSING0467'

SQL> alter database datafile 467 offline drop;
alter database datafile 467 offline drop
*
ERROR at line 1:
ORA-02236: invalid file name

Thank you.

[Updated on: Fri, 26 April 2019 23:01]

Report message to a moderator

Re: invalid datafile [message #675877 is a reply to message #675876]

Sat, 27 April 2019 00:34

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

John Watson wrote on Mon, 22 April 2019 08:13

Please use [code] tags,
How to use code tags and make your code easier to read

Report message to a moderator

Re: invalid datafile [message #675878 is a reply to message #675875]

Sat, 27 April 2019 00:53

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@Blackswan

Sir, thanks for the links.
Is it ok to just drop the 'missing'/problematic datafiles
without recreating its corresponding tablespaces?

Thanks you.

Report message to a moderator

Re: invalid datafile [message #675879 is a reply to message #675876]

Sat, 27 April 2019 02:50

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Your childish behaviour in refusing to use [code] tags is pathetic.

You need to think about what you are doing. It seems that offlining files by number is not possible, so you have to do it by name. So what name is in the controlfile? You are the person who knows, because you created it.

Have you tried my suggestion of _allow_resetlogs_corruption?

Perhaps your only remaining option is to engage with Oracle Support, and ask them to extract the data with the Data Unloader. If your dictionary is intact, that will give you a set of export files with all your tables. This will not be cheap.

Report message to a moderator

Re: invalid datafile [message #675884 is a reply to message #675879]

Sat, 27 April 2019 04:07

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@John Watson/Michel Cadot

Sirs, I'm very sorry for this.
Too busy for the "code" tagging right now.

Thank you very much for your time and patience.
We can't just force open the database if there are substantial data loss.
That's why we have not tried it yet.

Actually I'm not the one who executed the creation and renaming of those datafiles.
Also, in our organization setup, we're not the group responsible for backup of those
archive logs to tape but our systems administrators.

If in case we identified the datafiles' actual names and offline drop it,
do we still need to recreate the associated tablespace?

Thank you so much your undying assistance, its a big boost for us newbie DBAs.

Rest assured that we'll do the required 'coding' on our future log issues.

Regards,

Report message to a moderator

Previous Topic:	large table
Next Topic:	TNS-01151 - problem with starting the listener for standby database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 08:33:37 CST 2024