Keyword: MISSING
The control file or views v$datafile and sys.dba_data_files contain the
file names "MISSING9999",
where 9999 = [no] is the 4-digit ORACLE file ID (with leading zeros).
Subsequently an error can occur when saving data with BRBACKUP and the
warning "BR274W File MISSING9999 not found" is displayed.
An ONLINE backup will then terminate with the following messages:
"BR301E SQL error -1128 at location BrTsAlter-1
ORA-01128: cannot start online backup - file [no] is offline.
ORA-01111: name for data file [no] is unknown - rename to correct file
ORA-01110: data file [no]: 'MISSING9999'
BR316E Alter tablespace [tspname] begin backup failed."
When a table is exported/selected/changed the following errors occur:
"ORA-00376: file %s cannot be read at this time
ORA-01111: name for data file %s is unknown - rename to correct file
ORA-01110: data file %s: 'MISSING9999'".
Cause and prerequisites
Either a "CREATE CONTROLFILE ..." was performed at some time after the creation of the DB in which a data file was forgotten, or after a structure change (tablespace created or extended), the old control file (which had been saved before the structure change) was reloaded and recovered to a point after the structure change (complete or incomplete recovery).
The "MISSING9999" entry in the control file is issued the moment the DB is opened. In the process the control file is adjusted on the basis of the file IDs in the ORACLE Data Dictionary. For each missing data file an entry of the type "'MISSING9999' SIZE nK,"(as represented in the
trace file) is generated in the control file. Here 9999 is substituted with the four-digit file ID (possibly with leading zeros) and n with the file size from the Data Dictionary.
The missing data file has the status 'RECOVER' (in v$datafile) or 'AVAILABLE' (in dba_data_files) or 'OFFLINE' (in v$recover_file).
The tablespace that it belongs to is 'ONLINE' or 'OFFLINE' (in dba_tablespaces).
A SYSTEM tablespace file can never be 'MISSING...' as the database could then not be opened.
For the same reason no data file with ONLINE rollback segments can be affected (e.g. from tablespace PSAPROLL).
If the error is noticed during an OFFLINE backup with BRBACKUP due to
a BR274W warning, the backup is not affected by the error, is continued up to the end and will be correct (the error status is saved with the backup. However an ONLINE backup of the tablespace that the missing file belongs to cannot be performed manually or with BRBACKUP.
The faulty state of the DB should be corrected as soon as possible. Above all, you have to check immediately whether any data is already missing in the current DB (see "Information", point b below).
If the error occurs during export/selection/changing of a table, then the table is completely or partially contained in the missing file. Its contents cannot be regained without a restore/recovery. Data of this table from extents in other still existing files is also usually no longer readable. The database is inconsistent!
The problem can be solved in two ways. Either
1. Transport the forgotten data file back to the DB -or-
2. Delete the tablespace that contains the missing data file--if
possible, by losing as little data as possible.
Option 1 is possible without loss of data, however, you must either have a backup of the missing file and all the redo logs created since that file was backed up.
Option 2 is possible without the missing file and without a recovery.
However, all data of tables partially or entirely contained in the missing data file are lost. This is often insignificant, for example, when the missing data file contains only indexes, empty tables, rollback segments or no objects at all. The tablespace contents are exported and imported again when the tablespace has been deleted and restored.
Information on the missing data file:
sqldba lmode=y
SQLDBA> connect internal
a) Which tablespace does the file belong to, file size:
SQLDBA> select * (-> tablespace_name,file_name,status,bytes)
from dba_data_file:
where file_id = [no];
other files of the tablespace:
SQLDBA> select file_name,bytes
from dba_data_files
where tablespace_name='PSAP....';
b) previous contents of the missing files:
SQLDBA> select * (-> owner, segment_name, segment_type)
from dba_extents
where file_id = [no];
or with sapdba (Main menu -> d:Reorg. -> a:Check -> c:)
c) Status, for example, tablespace parameters (selected under a):
SQLDBA> select * (-> status, default storage parameter)
from dba_tablespaces
where tablespace_name ='PSAP...';
d) Status, data file size:
SQLDBA> select * (-> name, status, bytes)
from v$datafile
where file# = [no];
to 1.:
sqldba lmode=y
SQLDBA> connect internal
SQLDBA> alter database rename file 'MISSING9999' to 'filename';
Here, 'filename' is the complete file name with path.
It does not necessarily have to be the previous name and path
of the file.
The backup of the missing file has to be stored under
'filename' again.
Caution: It is not possible to restore the file with
"SQLDBA> alter database create datafile 'filename';"
unless a backup of the previous control file exists which was
up to date when the file in question was created.
(In this case the "rename" above is not required. Instead the
appropriate control file has to be reloaded again, the
"create datafile" executed and then in the next step it is
recovered with the option "using backup controlfile".
SQLDBA> recover datafile 'filename'; or
recover database; (see Note 4161)
(For an incomplete recovery, all DB data files from the backup
have to be reloaded (see Note 4164).
All redo logs of the missing file generated since the backup
have to be available (for "create datafile" all redo logs since
the initial creation).
SQLDBA> alter database open [resetlogs];
(resetlogs only for an incomplete recovery or
"using backup controlfile")
SQLDBA> shutdown
SQLDBA> exit
Backup of the affected tablespace or the complete DB depending on the type of recovery performed (see Notes mentioned above).
to 2.:
A reorganization of the tablespace has to be carried out "with data files." This cannot be performed completely with SAPDBA as SAPDBA only reorganizes tablespaces where all the files are 'ONLINE'.
- Stop the R/3 System with "stopsap r3". The DB remains open.
- Caution:
Do not set the tablespace to which the data file belongs 'OFFLINE'!
It can not subsequently be set to 'ONLINE' because of the
missing data file, i.e., only empty structures and no tablespaces
could be restored, not even from files that still exist in the
tablespace!
- Export of the tablespace structure that the missing file belongs to.
This can be performed with sapdba (Main menu -> e:Export ->
b:Structures), if the tablespace contains no rollback segments.
(Otherwise note the rollback parameters and delete them manually.)
Structures i.e. indexes, empty tables and rollback segments, also of
the missing file can be restored in this way.
- If the tablespace contains tables that are completely included in the
missing data file, their contents are lost.
However, when tables are only partially included in missing files
you can try to regain as much of the still accessible contents as
possible. With luck, you can regain even all the data of the tables
if the missing file's extents were empty.
Caution: The DB will become inconsistent if any individual table
is not recreated completely!
Contacting SAP is essential!
Tables that have no extents in the missing file can always be restored
completely.
The data export can be performed manually or with sapdba
(Main menu -> e:Export -> a:Data export).
- Determine tablespace parameters as described above in "Information"
under a) and c).
- sqldba lmode=y
SQLDBA> connect internal
SQLDBA> drop tablespace psap.... including contents;
(Afterwards either delete the physical data files at the
operating system level (see "information" a!)
or use the reuse-option in the following statement, if the
size remains the same.)
SQLDBA> create tablespace psap....
datafile 'filename1' size ...M [reuse],
'filename2' size ...M [reuse],
...
default storage (initial ...K next ...K
minextents ... maxextents ... pctincrease 0);
Use the initially noted parameters
(see "Information" a and c).
At this point you can also adjust the number and size of the
data files e.g. combine several smaller files to form one
large file.
- Import the data export again manually or with sapdba, if the export
was performed with sapdba. Main menu -> e:Export/import -> d:).
- Restore the remaining structures (indexes, empty tables) with sapdba
(Main menu -> e:Export/import -> f:),
Restore the rollback segments manually on the basis of the previously
noted values.
- Backup the affected tablespace and the contol file
(manually or with sapdba or brbackup)
- Restart R/3.
No comments:
Post a Comment