What is the best way to back up and restore individual schemas within a single Oracle 10G instance without using export/import utilities?
Requires Free Membership to View
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
Thanks.
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’; SPOOL OFF
Then repeat for each of the schemas.
This was first published in November 2010
Join the conversationComment
Share
Comments
Results
Contribute to the conversation