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

Performing an Oracle application backup: What is the best method?

Trying to perform an Oracle application backup? Learn about your options for Oracle application backups, including putting schemas in tablespaces and using exp/imp, in this tip from Oracle database expert Brian Peasland.

What is the best way to back up and restore individual schemas within a single Oracle 10G instance without using export/import utilities?

Each application developed schema is assigned its own tablespace(s). I would like to back up/recover by tablespace(S) for each application rather than using export/import. What are the risks ? Is this easy to do? Can the application tablespace(s) be restored at any time? Does the database have to be shutdown and restarted?

Any thoughts for the best way to handle an Oracle application backup would be appreciated.  I already have full Oracle instance backups (RMAN, etc.) but I would like to handle the applications in a much more efficient manner and independent of the rest of the database restore.

For example:

Database instance named ProdA has three in-house developed application schemas:

Schema            Tablespace
---------             --------------

Schema1         TablespaceA
Schema2         TablespaceB
Schema3         TablespaceC


You could put each schema in its own tablespace and back up that tablespace. But keep in mind that the tablespace only contains segments  and not all objects are segments. Segments are objects that require space like tables and indexes. The schema could also contain views, stored procedures, sequences, etc. These objects are not segments and are stored in the Data Dictionary. So backing up the tablespace will miss non-segment objects. If I have to back up just one schema away from the others, then I use exp/imp or the newer Data Pump. However, even those have shortcomings so I rarely use them without having other backup methods in place like full database backups with RMAN.

If you do backup want to back up tablespace by tablespace, you can restore just a tablespace and perform point-in-time recovery on it. But since you already have a full RMAN database backup, there is no big reason to do the tablespaces separately. RMAN can restore just one tablespace from a full database backup if you want to do that.

One option I have employed quite often is to use exp/imp or Data Pump to export just the DDL for the schema. I would already have backups of my tablespaces so I can restore a schema’s tablespace at any time. But then I export the DDL to recreate the schema objects should I need to restore a view or a procedure for example. To do that, I use the ROWS=N parameter for exp. It exports the CREATE statements but no table data. Alternatively, if you do not want to use exp/imp to get your DDL, use the DBMS_METADATA.GET_DDL procedure similar to the following:

SPOOL schema1_ddl.sql
SELECT DBMS_METADATA.GET_DDL(owner,object_name,object_type)
FROM dba_objects WHERE owner=’SCHEMA1’;

Then repeat for each of the schemas.


Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.