WHAT TO DO UNTIL THE DBA COMES

Some procedures for recovering from various offenses

against the sensibilities of an Oracle8 database.

2/13/2002

I. DELETED DATAFILES

This procedure recovers from an oracle datafile having been deleted. This is taken as a serious offense; the DB becomes extremely upset, and simple-minded efforts to make up to it are scornfully rejected. Here's what to do:

1. Run the Oracle Server Manager (svrmgr30 on Windows 2000 or svrmgrl on UNIX).

2. From within Server Manager:

a. Execute "connect internal". If it complains about the ORACLE_SID not being set, then check to make sure that the ORACLE_SID environment variable is properly set. With the default setup, you could issue a command like "setenv ORACLE_SID eqs" from the csh prompt. On Windows 2000 use the environment variable modification tool under Control Panel -> System. If you have this problem on , Win2000 you should probably redo the entire Oracle installation. On UNIX you will have to run as the account that own oracle.

b. Execute "shutdown abort" to make sure the database is shutdown.

c. Execute "startup nomount". (This is the most primitive way of starting the database.)

d. Execute "alter database mount" (This should mount the database, and server manager should reply with the string "Statement Processed")

e. Execute "alter database datfile <DATAFILE> offline drop" where <DATAFILE> is the name of the operating system file that you have deleted or otherwise butchered. It should be surrounded by single quotes ( ' ).

f. If the last statement executed, you should be in the clear. Execute "shutdown immediate" to shutdown the database.

g. Exit server manager.

3. Restart the database in the usual manner (stop and start the Start_<ORACLE>) service on Win2000, or run $(ORACLE_HOME)/bin/dbshut (as the oracle owner) or etc/.../init.d/dbora start (as root) on UNIX.

OR Simply reboot the computer.

4. Connect via SQL_PLUS using the "sys" or "internal" account and drop the TableSpace that contained the deleted or molested datafile. Issue a "drop tablespace <TABLESPACE>" command to drop the tablespace. You may have to get honery (sp?) with it, and include syntax such as "drop tablespace <TABLESPACE> including contents" or other forceful language.

5. Now as reward for all your hard work, you can reinstall the portion of the EW DB that you just deleted by wiping out the <TABLESPACE>, and hopefully you will remember to never delete a datafile on your own again. :-)

So wrote Dave K. A more cowardly approach is to do the whole delete-create drill. A word of caution there is that the deletion script ("p3s2_destroy_schema2_db_hex_hex.sql") seems to bomb when it tires to drop the non-existing table space. One should thus edit this script to drop the existing ones first...

II. TOO MUCH DATA

This is usually manifested by lack of trace data snippets in the database after some point in time. There are two solutions: Give Oracle more data files to play with, and allow the data files to grow:

II.a. Creating more data files

Each Oracle Tablespace is kept on one or more operating system files. Oracle is smar enough to go from file to file as they fill up. Here's how to create more files on Win2000:

1. Run the Storage Manager:

Select Start->Programs->Oracle Enterprise Manager->Storage Manager

2. Log in as user "internal".

3. Click on the "+" of Tablespaces.

4. Click on the "+" of the tablesapce to be enlarged.

5. Click on "Datafiles"

6. Right-click on one of the files shown in the right window.

7. Select "Create like..."

8. Enter the complete path and filename of the new file. Fear and superstision has it that it should have an.ora extention.

9. Select "Create".

II.b. Allowing the data files to grow

This is controlled by setting the 'extents' property of a data file, which determines how large a data file may become. The procedure here will allow it to grow to the limit set by the operating system:

1. Run the Storage Manager:

Select Start->Programs->Oracle Enterprise Manager->Storage Manager

2. Log in as user "internal".

3. Click on the "+" of Tablespaces.

4. Click on the "+" of the tablesapce to be enlarged.

5. Click on "Datafiles".

6. Right-click on one of the files shown in the right window.

7. Select "quick edit".

8. Select the "Auto Extend" tab.

9. In the "Maximum Extent" panel, select "unlimited".

The basics of monitoring an Oracle Database....

All of the above mentioned storage issues can be proactively monitored. Oracle does a good job watching and recording what it does. Most Oracle databases encounter some type of storage related issues over time and most Oracle DBAs know how to look for the onset of these issues. A basic blurb of what happens in the database needs to happen here. The Oracle database consists of a collection of data files. The database also has the concept of tablespaces.
A tablespace can hold many database objects including tables and indexes. A tablespace uses at least one datafile to store the tablespace info onto the OS.

Understanding this, one can see that it would be wise to understand the relationship between tablespaces, datafiles and database objects. It is even better to understand the growth patterns within. Entire books are written on this subject and I will not try to gloss over here.
In general, If a database object has outgrown it's environment, there will be issues.

This reflects the bare minimum needed to succeed in the Oracle environment. Included below is a nice sql query that can show you very important information about the database objects. It must be run as an Oracle DBA. Do not run this or anything as SYS.

Here is the sql. Cut and paste into notepad if desired.
/*******************check_earthworm_db.sql **********************/
/**/

rem Name: check_earthworm_db.sql
rem Date: Feb 5, 2002
rem Useage: sqlplus system/password@check_earthworm_db.sql
rem Output Ascii file: check_earthworm_db.log
rem
rem
rem Description: See below
rem This script is designed to look at specific and important
rem sections of the earthworm database.
rem The script creates an output file names check_earthworm_db.log in the sqlplus local directory
rem The things that are checked are:
rem
rem Fragmitation.
rem Objects that have a large number of extents are often considered fragmented.
rem Fragmentation should be considered in earthworm but is not the most important consideration.
rem
rem Remaining Space in the tablespace in size and percent.
rem
rem The next part of the report shows database objects along with the current storage requirements. It also shows rem the objects that cannot extend for several reasons. From this information the DBA may decide
rem to adjust storage rem parameters of the tablespace or the object to allow the object to grow.
rem
rem The last part of the report looks for database objects that need to be compiled.
spool check_earthworm_db.log
prompt Checking For Fragmented Database Objects
rem
rem Fragmentation Report - Multiple Extents Bad , Single Extents Good.
rem
column owner noprint new_value owner_var
column segment_name format a20 heading "Object name"
column segment_type format a9 heading "Tabl/Indx"
column sum(bytes) format 999,999,999,999 heading "Bytes Used"
column count(*) format 999999 heading "Extent"
column sum(blocks) format 999,999,999 heading "Blocks"
break on owner skip page 2
rem - the report
select owner , segment_name , segment_type ,
sum(bytes) , sum(blocks) , count(*)
from dba_extents
having count(*) >20
group by owner , segment_name , segment_type
order by owner , segment_name , segment_type
/
set pagesize 300
set lines 200
column sumb format 9,999,999,999
column extents format 9999
column bytes format 9,999,999,999
column largest format 9,999,999,999
column Tot_Size format 9,999,999,999,999
column Tot_Free format 99,999,999,999
column Pct_Free format 99,999
column Chunks_Free format 9,999,999,999
column next_extent format 9,999,999,999
column nextext format 9,999,999,999
column Max_Free format 9,999,999,999
PROMPT REMAINING SPACE IN TABLESPACES AND THE LARGEST CHUNK
select a.tablespace_name,sum(a.tots) Tot_Size,
sum(a.sumb) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free, autoext
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a,
(select distinct tablespace_name, AUTOEXTENSIBLE autoext
from dba_data_files
where autoextensible='YES'
) ddf
where a.tablespace_name=ddf.tablespace_name(+)
group by a.tablespace_name, autoext
/
PROMPT OBJECTS WHERE THERE'S NOT ENOUGH ROOM FOR THE NEXT EXTENT
select a.owner, a.segment_name, b.tablespace_name,
decode(ext.extents,1,b.next_extent,
a.bytes*(1+b.pct_increase/100)) nextext,
freesp.largest, ddf.autoext
from dba_extents a,
dba_segments b,
(select owner, segment_name, max(extent_id) extent_id,
count(*) extents
from dba_extents
group by owner, segment_name
) ext,
(select tablespace_name, max(bytes) largest
from dba_free_space
group by tablespace_name
) freesp,
(select distinct tablespace_name, AUTOEXTENSIBLE autoext
from dba_data_files
where autoextensible='YES'
) ddf
where a.owner=b.owner
and a.tablespace_name=ddf.tablespace_name (+)
and a.segment_name=b.segment_name
and a.owner=ext.owner
and a.segment_name=ext.segment_name
and a.extent_id=ext.extent_id
and b.tablespace_name = freesp.tablespace_name
and decode(ext.extents,1,b.next_extent,
a.bytes*(1+b.pct_increase/100)) > freesp.largest
/
spool off

/**************** File complete *******************************/