I am trying to restore to a backup instance on a backup server in order to develop a disaster recovery plan. When I try to recreate the tables I keep getting "ORA-01659: unable to allocate MINEXTENTS" which I know is caused by "Failed to find sufficient contiguous space". The tablespaces and datafiles on both servers are the same size.
Once I start importing the main tablespace fills up. After the script to recreate all tables runs, the tablespace is full, more full than on the production machine. I have also tried "ALTER TABLESPACE xxx COALESCE;" on each tablespace listed in Oracle Enterprise Manager to reclaim free space. Why is it full? I’ve only dropped and created the tables, there shouldn’t be any data in them yet.
Here’s the complete DRP process I'm following. The backup instance was already there, all I did was drop the tables.
1) Capture current schema from production (assuming your production database is accessible
set pagesize 0
set long 90000
set feedback off
set echo off
SELECT 'DROP TABLE '||u.table_name||' CASCADE CONSTRAINTS;'
FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) ||'; '
FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) ||'; '
FROM USER_INDEXES u;
2) Capture existing objects from production (assuming your production database is accessible) to check afterward
Set pages 1000
Col object_name for a30
Select object_name, object_type
Where object_type In ('TABLE', 'INDEX','TRIGGER')
Select constraint_name, constraint_type
Where constraint_type IN ('P', 'R')
Order by 2, 1
3) Export with the script we have (if production database is accessible) or find latest available export from backups. Transfer export file to backup server.
4) Drop all tables. (Checked OEM that all tablespaces were 0% used). Re-create the table/indexes on failover server by importing schema data from Step 1.
When you import objects, Oracle first tries to create the table or index in the tablespace that originally held the object. For instance, if the table was in the USERS tablespace in the source database, Oracle will attempt to create that table in the USERS tablespace in the destination database if that tablespace exists. If the tablespace does not exist in the destination database, Oracle will create the table or index in the object owner's DEFAULT tablespace. It looks like the destination database has the SYSTEM tablespace as the default tablespace for this and possibly other users, which is a very bad idea. You can either create the tablespaces as named in the source database or create other tablespaces in the destination database and make sure the table/index owner's default tablespace points to the new tablespace you created.
Have a question for Brian Peasland? Send an e-mail to firstname.lastname@example.org.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading