Subject: RECREATING DATABASE OBJECTS
Author: PMILANI
Last Revision Date: 18 September 1995
RECREATING DATABASE OBJECTS
INTRODUCTION
------------
One of the most common tasks in database maintenance is the recreation
of database objects. Such objects may have to be recreated for
various reasons: to provide for database growth, to reduce
fragmentation, to improve performance, and so forth. In the process
of recreating database objects, certain precautions must be taken
to prevent data loss or damage to the database. Whereas the
procedures described in this bulletin are not unique, they are safe
and effective for recreating database objects.
A. ROLLBACK SEGMENTS
--------------------
A typical situation in which a rollback segment may have to be
recreated is when the rollback is too small and users run into
ORA-1555 ("snapshot too old (rollback segment too small)") or ORA-1628
("max # extents (%s) reached for rollback segment %s"). The key concern
in recreating a rollback segment is to make sure that there are no active
transactions in the rollback before dropping it. The simplest way to
do that is to first make sure the rollback is offline, and only then
drop it. It may then be recreated. The complete sequence of steps is:
1. Go into SQL*DBA and connect internal.
2. Take the rollback offline.
ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;
3. Check the current status of the rollback segment.
SELECT STATUS FROM DBA_ROLLBACK_SEGS
WHERE SEGMENT_NAME = '<rollback_segment>';
If the query returns 'OFFLINE', go to step 4.
If it returns 'ONLINE'or 'PARTLY AVAILABLE', it means that there
are still pending transaction entries in the rollback. You
must wait until the corresponding transactions are either
committed or rolled back. Repeat the above query again until
the status becomes 'OFFLINE'.
An 'INVALID' status implies that the rollback has already
been dropped.
A 'NEEDS RECOVERY' status means that there are problems with
the rollback.
4. Drop the rollback segment.
DROP ROLLBACK SEGMENT <rollback_segment>;
If you receive the message 'Statement processed', the rollback
has been dropped and can now be recreated.
5. Recreate the rollback segment.
CREATE ROLLBACK SEGMENT <rollback_segment>
TABLESPACE tablespace
STORAGE ( <storage parameters> )
For the syntax and options of the storage clause, see the
Oracle7 Server SQL Language Reference Manual.
6. Bring the rollback segment online.
ALTER ROLLBACK SEGMENT <rollback_segment> ONLINE;
B. REDO LOGS
------------
Small redo logs can be a major bottleneck in a database. If log switches
are taking place at a very fast rate, it is a good idea to recreate
the redo log files with a larger size, and perhaps even add one or
more redo log files. Since it is recommended that redo log files
be all of the same size, you should recreate them all at once with
the new, bigger size.
At any given time, the database must have at least two redo
log groups. Therefore, if you currently have only two redo log
groups, you need to create a third one to be able to drop and recreate
the log files. To do that,
- Go into SQL*DBA and connect internal.
- Create the third redo log group with the desired size and in the
desired location.
ALTER DATABASE ADD LOGFILE GROUP <group_x>
'file1' SIZE <size>;
where <group_x> must be a non-existing group number and
'file1' is the full path name of the new redo log file.
If you are mirroring your redo log files, the statement would be:
ALTER DATABASE ADD LOGFILE GROUP <group_x>
('file1', ..., 'fileN') SIZE <size>;
Once you have at least three redo log groups, you are ready to recreate
your redo logs. For simplicity, the procedure below assumes the
database has a single redo thread. If you are using the Oracle Parallel
Server and have more than one redo thread, you should apply this procedure
to each one of the threads. Here are the steps:
1. Go into SQL*DBA and connect internal.
2. Shut down the database (normal or immediate).
3. Mount the database in restricted mode.
STARTUP RESTRICT MOUNT
4. If the database is in archivelog mode, force all filled redo log
groups to be archived.
ARCHIVE LOG ALL
5. Find out which is the current redo log group.
SELECT GROUP#, STATUS FROM V$LOG;
One of the groups will have status 'CURRENT'. That will be the
last one to be recreated. At least one of the groups should have
status 'INACTIVE'. If not, repeat the above query until that
is the case.
6. Pick one of the inactive redo groups and drop it.
ALTER DATABASE DROP LOGFILE GROUP <group_x>;
7. Recreate that redo log group with the desired size and in the desired
location.
ALTER DATABASE ADD LOGFILE GROUP <group_x>
'file1' SIZE <size> REUSE;
where 'file1' is the full path name of the redo log file.
If you are mirroring your redo log files, the statement would be:
ALTER DATABASE ADD LOGFILE GROUP <group_x>
('file1', ..., 'fileN') SIZE <size> REUSE;
8. Repeat steps 6 and 7 for all inactive redo log groups.
9. Open the database.
ALTER DATABASE OPEN;
10. Force a log switch to make the current redo log group inactive.
ALTER SYSTEM SWITCH LOGFILE;
11. Check the status of that redo group to make sure it is inactive.
SELECT STATUS FROM V$LOG
WHERE GROUP# = <group_y>;
When the status shows as 'INACTIVE', move on to step 12.
If the database is in archivelog mode, you should check
both the redo group's status and whether it has already been
archived:
SELECT ARCHIVED, STATUS FROM V$LOG
WHERE GROUP# = <group_y>;
When both the status shows as 'INACTIVE' and archived
is 'YES', move on to step 12.
12. Repeat steps 6 and 7 for this particular redo log group that just
became inactive. If you originally had only two redo log groups
and do not want a third one added, simply apply step 6 to drop
this newly inactivated redo log group.
13. Shut down the database (normal or immediate) and take a full backup.
C. TABLES
---------
Tables may be recreated for a number of reasons. The most common are
to change their storage parameters, to eliminate fragmentation, to move
them from one tablespace to another, or to change their ownership. There
are three main methods for recreating a table: export/import, the
CREATE AS SELECT command, and the SQL*Plus COPY command.
EXPORT/IMPORT
-------------
The central idea in this method is to:
1. Export the table.
2. Doublecheck the validity of the export file by faking an import with
SHOW=Y.
3. Drop the table.
4. Import it back.
Depending on the objective of recreating the table, the details may vary:
- TO CHANGE STORAGE PARAMETERS OR ELIMINATE FRAGMENTATION
Tables are exported with their current storage parameters. However,
by default export lumps all table data into the table's initial
extent, so that when the table is imported, all of its rows will be
stored in its initial extent. Thus, after the import is completed,
the table's INITIAL will be equal to the sum of the sizes of all its
original extents, and fragmentation will have been taken care of.
This behavior can be changed by setting COMPRESS=N at export time. If
you want the table to have different values for NEXT, MINEXTENTS,
MAXEXTENTS, or PCTINCREASE, you may modify them after the table is
imported with an ALTER TABLE statement. If you wish to set the value
of INITIAL manually, you have to pre-create the table with the desired
value for INITIAL (or any other of the storage parameters) before
importing it back and specify IGNORE=Y at import time.
- TO MOVE THE TABLE FROM ONE TABLESPACE TO ANOTHER
Two approaches are possible here. The simplest one is to pre-create
the table in the new tablespace and then import it back with IGNORE=Y.
The other one involves modifying the default tablespace of the table's
owner and manipulating his or her resource privileges to prevent the table
from being created in its original tablespace.
- TO CHANGE THE OWNERSHIP OF THE TABLE
In this case, all you have to do is to use the parameters FROMUSER
and TOUSER when importing. The user doing the import must have
the IMP_FULL_DATABASE role enabled. So, if you want to make
user JOE the new owner of table EMP, currently belonging to SCOTT,
you may enter:
IMP system/manager FROMUSER=scott TOUSER=joe TABLES=emp
Notice that by default import will try to import the table into its
original tablespace, even if that does not happen to be JOE's default
tablespace.
CREATE AS SELECT
----------------
The idea here is to issue a CREATE TABLE statement with an AS subquery
which contains a SELECT * FROM the original table. For syntax
details, see the Oracle7 Server SQL Language Reference Manual. With
this approach, you can specify a new tablespace for the table, as well
as new storage parameters. If you want to defragment the table, you
will have to manually add up the sizes of all of its extents and
create the new table with an INITIAL greater than or equal to that
total. To do that, you may execute the following query:
SELECT COUNT(DISTINCT(SUBSTR(ROWID,1,8) ||
SUBSTR(ROWID,15,4))) + 1 BLOCKS FROM table;
Then multiply the number of blocks by the size of your database
blocks. You may find out what that size is by going into SQL*DBA
as internal, and then doing
SHOW PARAMETER DB_BLOCK_SIZE
The new INITIAL of the table should be set to a value greater
than or equal to BLOCKS times DB_BLOCK_SIZE.
To change the ownership of the table, the new owner must have the select
privilege on the table. The CREATE TABLE statement should be issued
by the new owner.
COPY
----
This option is the simplest one for changing the ownership of a table.
To use it, you must have SQL*Net configured in your system. For
syntax details, see the SQL*Plus User's Guide and Reference. With
the COPY command, the storage parameters of the table cannot be
changed. The table will be recreated in the default tablespace of the
user to whose schema the copy is made. For example, to copy table EMP
belonging to SCOTT into JOE's schema, the statement is:
COPY FROM scott/tiger@ny to joe/lion@ny
CREATE JOE_EMP USING SELECT * FROM EMP
COMPARISON BETWEEN THE THREE APPROACHES
---------------------------------------
Export/import is the only approach that will preserve
all of the table's triggers, grants, indexes, constraints,
and comments. If you use create as select or copy, such
table properties must be manually recreated, since these
methods merely transfer the data from one table to another.
On the other hand, export/import is the most time-consuming of
the three approaches. Create as select and copy are generally faster,
but keep in mind that these methods require that you have
enough space to hold both tables. When using create as
select, make sure you have enough redolog and rollback
space to cope with the amount of redo and rollback that the
statement will generate. This is not a major issue with export/import
or copy because these methods allow you to specify an insert array
to be committed once the corresponding number of rows has been
inserted into the table.
If your goal is to defragment the table,
- Export/import is the simplest approach.
- Create as select is faster than export/import.
- Copy will not defragment the table at all.
If your goal is to change the storage parameters,
- Create as select is the easiest way to go. Again, make sure
you have enough redo and rollback space.
- Copy cannot be used to change the storage parameters.
If your goal is to move the table from one tablespace to another,
- Create as select is the simplest solution.
If your goal is to change the ownership of the table,
- Copy is the best approach. The only drawback is that you need
to know the passwords of both the original and the new owner of the
table.
If the table contains a column of type LONG,
- Copy is the simplest approach.
- Create as select cannot be used.
D. TABLESPACES
--------------
The actions required for recreating a particular tablespace depend on
its type. The SYSTEM tablespace can only be recreated by recreating
the whole database. For the steps to do that, see one of the
following bulletins, depending on your platform:
- UNIX: 102866.23
- OS/2: 102031.43
- VMS : 102543.64
Recreating a rollback segment tablespace requires extra caution. It
is strongly recommended that you do it with the database open in
restricted mode, so as to prevent user transactions from grabbing
rollback segments while the tablespace is being recreated. These are
the steps to be followed:
1. Go into SQL*DBA and connect internal.
2. Shutdown the database (normal or immediate).
3. Startup the database in restricted mode.
STARTUP RESTRICT
4. Repeat steps 1 through 4 of section A ("ROLLBACK SEGMENTS") for all
the rollback segments in the tablespace in order to drop them.
5. Offline the rollback tablespace.
ALTER TABLESPACE <rbs_tablespace> OFFLINE;
6. Check the current status of the tablespace.
SELECT STATUS FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '<rbs_tablespace>';
Repeat the above query until you obtain status 'OFFLINE'.
7. Drop the tablespace.
DROP TABLESPACE <rbs_tablespace> INCLUDING CONTENTS;
8. Recreate the tablespace with the desired parameters.
CREATE TABLESPACE <rbs_tablespace>
DATAFILE <filespec>
DEFAULT STORAGE <storage_clause> ;
For the syntax of the filespec and the storage clause, see
the Oracle7 Server SQL Language Reference Manual.
9. Recreate the rollback segments in the tablespace.
10. Shut down the database (normal or immediate) and take a full backup.
Recreating temporary, index, or user tablespaces is simpler. It is
still recommended that you do it with the database in restricted mode.
This will ensure that no user is accessing the tablespace as you
work through its recreation. The individual segments in a temporary
tablespace obviously need not be recreated. Indexes
may be recreated manually or by running a script. The only
issue of concern with user tablespaces is restoring the data
once the tablespace is recreated. In general, the easiest solution
for that is to use export/import. The steps for recreating a non-rollback,
ie, a temporary, index, or user tablespace are:
1. If the tablespace is a user or data tablespace, make sure you can
recreate its contents through an export file, SQL*Loader, etc.
2. Go into SQL*DBA and connect internal.
3. Offline the tablespace.
ALTER TABLESPACE <tablespace> OFFLINE;
4. Check the current status of the tablespace.
SELECT STATUS FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '<tablespace>';
Repeat the above query until you obtain status 'OFFLINE'.
5. Drop the tablespace.
DROP TABLESPACE <tablespace> INCLUDING CONTENTS
CASCADE CONSTRAINTS;
6. Recreate the tablespace with the desired parameters.
CREATE TABLESPACE <tablespace>
DATAFILE <filespec>
DEFAULT STORAGE <storage_clause> ;
For the syntax of the filespec and the storage clause, see
the Oracle7 Server SQL Language Reference Manual.
7. If the tablespace is a data tablespace, restore its contents. If
it is an index tablespace, recreate the indexes.
Once the tablespace is recreated, it is recommended that you
take a full backup of the database.
E. CONTROL FILES
----------------
You should only need to recreate your control file under very special
circumstances:
- All current copies of the control file have been lost or are
corrupted and the database is in NOARCHIVELOG mode.
- The database cannot be started because of an error in the range
ORA-201 through ORA-221.
- You are restoring from a backup in which the control file was
corrupted or lost.
- You need to change a "hard" database parameter that was set when the
database was first created, such as MAXDATAFILES (see bulletin
105578.079) or MAXLOGFILES.
In a mirrored control file configuration, if at least one of the
mirrored copies of the control file is intact, all you need to do is
shut the database down with the ABORT option, copy the good copy of
the control file onto the corrupted or lost mirrored ones, and then
restart the database. For more details on how to proceed in this
particular scenario, see the Oracle7 Server Administrator's Guide.
In addition, even if all current copies of the control file have been
lost or are corrupted, you may still recover it from a backup if the
database is in ARCHIVELOG mode. In that case, restore the control
file and the datafiles from a backup, mount the database, and then do
a RECOVER DATABASE USING BACKUP CONTROLFILE. For more details on how
to proceed in this particular scenario, see the Oracle7 Server
Administrator's Guide. This approach is only recommended if
the database is small to medium sized, and thus the restore and roll
forward procedure can be done in a few minutes.
If, however, you need to recreate the control file, this is the procedure:
1. Go into SQL*DBA and connect internal.
2. If the database is not already down, shut it down (normal or
immediate).
3. Mount the database.
STARTUP MOUNT
4. Generate a backup trace of the control file.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This statement will generate a new trace file in the
user_dump_dest directory. To find out where that directory
is, simply type SHOW PARAMETER USER. Once in that directory,
search by timestamp for a newly created user trace file with
extension "trc".
5. Shut down the database (normal or immediate).
6. Take a full backup of the database (including all data files, redo
log files, and control file, if any).
7. If there is any copy of the current control file out there in
the operating system, remove it now.
8. Edit the new trace file to convert it into a SQL script.
If properly edited, the new trace file becomes an executable
SQL script, the execution of which will create a new control
file.
8.1 Remove the header of the file.
Delete from the first line of the file, which should
read "Dump file <trace file name>", through the line
immediately preceding the STARTUP NOMOUNT statement,
which should read "# only if the current version of
all online logs are available," so that the first
line of the file now contains STARTUP NOMOUNT.
8.2 Make any other necessary changes to the file.
If you are recreating the control file to bump up
hardcoded database parameters such as MAXDATAFILES or
MAXINSTANCES, replace the current values with new ones.
9. In the same directory where the trace file is, go into SQL*DBA and
connect internal.
10. Run the edited trace file as if it were a SQL script.
For example, if the trace file is called ora_7489.trc, you
should enter
@ora_7489.trc
At this point, if you get the message "Statement processed," the database
should be open with a brand new control file. You should shut
the database down (normal or immediate) next, and take a full backup.