Q
Problem solve Get help with specific problems with your technologies, process and projects.

Restoring to a backup instance on a backup server for a disaster recovery plan

In this Oracle database and backup recovery tip, expert Brian Peasland helps a reader restore a backup instance on a backup server in order to develop a disaster recovery plan.

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
spool d:\exp\rebuild_output\syspisa_schema_rebuild_matson.sql
connect syspisa/****@MATSON;
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;
spool off;

2) Capture existing objects from production (assuming your production database is accessible) to check afterward

 Set pages 1000
Col object_name for a30
spool d:\exp\rebuild_output\before_exp
Select object_name, object_type
From user_objects
Where object_type In ('TABLE', 'INDEX','TRIGGER')
Union all
Select constraint_name, constraint_type
From user_constraints
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 editor@searchoracle.com.

This was last published in January 2011

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close