Datafiles named 'MISSING'

If you come across a datafile that has been named 'MISSING' with a five digit extension, this means that you have a datafile that the Data Dictionary knows about but which is not in your control file.
Example:

SVRMGR> select file_name from dba_data_file where tablespace_name ='TEST01';
FILE_NAME

-----------------------------------------

/database/804/vcrobins/data/test1.dbf

/oracle/8.0.5/dbs/MISSING00009

This can happen if you recreate your control file and you leave out or remove the datafile.

This can be fixed by either recreating your control file and adding the datafile with the correct name (if you know what it was before) or dropping and recreating your tablespace. (Make sure you have an export of all of the objectsin the tablespace before dropping it.)

SVRMGR> select file_name from dba_data_files where tablespace_name ='TEST01';
FILE_NAME

-----------------------------------------

/database/804/vcrobins/data/test2.dbf

/database/804/vcrobins/data/test1.dbf

Useful FAQ ON Oracle DBA

1. How you recover when one datafile of system tablespace is corrupted?
2. How to clone the database?
3. Tell me about rollforward and rollback?
4. Which command is used in linux to change the owner of the file?
5. To see the process which is running, which command you use in linux?
6. From where you find which datafiles are corrupted and how do you recover?
7. How you spread your datafiles in 4 layouts (4 different disks)?
8. What is the command to resize the datafile?
9. If you add one datafile by mistaken, how you drop that datafile?
10. How you connect with the database as a sysdba with out listener?
11. What is the easiest way to get the execution plan?
12. What is syntax for expain plan?
13. What is PGA_AGGREGATE_TARGET?
14. What are the diagnostic tools and about statspack(its level)?
15. Recovery scenario if data file is lost without a backup how u recover?
16. What sort of tuning have u done?
17. I took a clod back up at mid night 12.00 the data base is running in archive log mode, i created a new tablespace at morning 7.00 am the data base crashed at 9.00 am can I recover the data base?

18. There is a user schema which is 8gb in size I need to export it to a disc that has 2 gb of free space. How will you do that?
19. What is your database size?
20. What is your RMAN backup size?
21. What is your sga component size like db_cache_size, sga_max_size and shared_pool_size?
22. How can we recover the database if only available contolfile is lost/dropped?
23. What will happen if archive log is full?
24. How do u connect your remote database?
25. What is your hard disk size?
26. What are the optimizers available?
27. How do u find the table size?
28. How do u find out how many sessions are connected?
29. Which view helps you to get the instance information?
30. How do u get the datafile is auto extend or not?
31. How do u get the tablespace contents is temporary / permanent?
32. You issue SHUTDOWN IMMEDIATE command, but DATABASE hang at that time,what could be the reason?
33. How to get the temporary tablespace temp file information?
34. What is the use of index and its contents?
35. When will we use the temporary segment?
36. What is OPATCH?
37. What is the use of undotablespace?
38. What is the difference between temporary and permanent tablespace?
39. Shall we move the permanent to temporary tablespace?
40. Today, we have a well tunned query running daily usually will take 10minutes, after 10 days user telling its still running for the past 40 minutes? What is the problem? How to tune the statement?
41. How to increase the db_cache_size? What are the steps to follow?
42. We have a two online redo log file? Our database is closed. One redo log file is corrupted? When we start next time, we will get redo log file is corrupted? How to recover?
43. What is the functionality of RDA and what it does?

44. How do u diagnose the oracle database?
45. Where will u get the scn number?
46. What are the types of point in time recovery?
47. What is deadlock?

48. Which parameter is used to specify the size of buffer in exp utility?
49. How to add a datafile to tablespace?
50. What is the difference between cold backup and hot backup?

Enabling Trace in the Database

Instructions for running a SQL Trace for a single session

1. Verify DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure is available. If not, DBA must install it.
2. Clean out any old *.trc files in the udump directory so it is easier to find the generated .trc file. To determine the udump directory run this SQL connected as system:
Select value from v$parameter where name = ‘user_dump_dest’;
3. Have the user performing the test logon to database and proceed to the point just prior to executing the function that needs tracing. Make sure there is only one occurrence of this user connected to Oracle.
4. Have the DBA set timed statistics on. Use this SQL, connect as system:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
5. Run this SQL, connected as system, to determine the sid and serial#. Replace USERNAME with correct username.
select sid', 'serial#', 'username', 'machine
from v$session where username = ‘USERNAME’;
6. Have the DBA set SQL Trace on for the user session. Use this SQL, using the sid and serial# from the previous step:
Execute dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);
7. Have the user perform the desired function.
8. Have the user logoff EMPAC. This will inactivate SQL Trace.
9. Locate the new file in the udump directory.
10. Have the DBA run tkprof against the new file. This is the format:
Tkprof filename.trc filename.lst explain=username/password@instance sys=no
11. Support will need both the filename.trc file and the filename.lst file.
12. Turn timed statistics off. Use this SQL:
ALTER SYSTEM SET TIMED_STATISTICS = FALSE;

How to Generate an Explain Plan

The BEST way to generate an explain plan is via SQL*PLUS. This is because it gives all the important information: explain plan formatted nicely, statistics about the query, and timing of the query.

Quick & Easy way to Generate An Explain Plan Using Autotrace
Log into SQL*PLUS (not TOAD or any other Tool)
Issue (copy & paste)
set autotrace traceonly explain statistics;
set timing on
set linesize 250
column PLAN_PLUS_EXP format A200
execute the query as normal
Turn off autotrace be issuing after you are done
set autotrace off