Oracle Database – How to Export Without Data

backupdatabase-design

I want export my Oracle database with its structure such as tables, schema, users, tablespaces, datafiles and everything but without the contained data.

Best Answer

Judging by the answer you gave in response to @Ozzie, I'm not sure you can do what you want.

You can export the metadata of an 11gR2 database using expdp (Data Pump) and the METADATA_ONLY flag. See http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm

This will export the structure of your data. This won't include redologs, controlfiles, the supporting files required for the instance.

To recover using this export, you would need to create a new database instance using dbca which has all the logs and supporting structure set up, and then import the metadata using impdp.

If you had a copy of the data files, you could also use transportable tablespaces to move the data to the new instance.

The normal method for backing up the entire database is RMAN. This will include everything that you need to restore the database. I don't believe that RMAN provides the option to exclude the data, though.

It may help if you can explain what you are trying to achieve, then better options may be available.

Related Solutions

Oracle XE: Transfer Database from Linux to Windows

You should be able to solve this with a full export and import of the database.

Run a full export of the database on the linux VM
Copy the dump file to your Windows machine
Do a full import to the database on the Windows machine.

I believe Oracle reccomends the use of Oracle Data Pump: expdp and impdp on Oracle 11g and up, rather than the traditional imp and exp tools.

Oracle Data Pump is well documented: http://docs.oracle.com/cd/E11882_01/server.112/e16536/part_dp.htm

Restore Oracle tablespace to its state from 2 days ago

You probably tried to restore and then recover the database. When you recover the database all the files should be in sync before you can open the database for work. The error shown tells you that some of your data files are not in sync with the rest of the database.

What your really need to use to recover a tablespace or a set of tablespaces is Tablespace Point-in-Time Recovery (TSPITR). You can do it manually or with RMAN. In the latter case the command might look like:

recover tablespace app_data
until SCN 1234567
auxiliary destination '/tmp/auxdb';

And you wouldn't need to configure the auxiliary database manually. With user-managed TSPITR, on the other hand, you have to configure the auxiliary database first, and then use Transportable Tablespaces feature (see the section "Introduction to Transportable Tablespaces" in Database Administrator's Guide) to move the data files along with metadata to the target database.

For more details read "RMAN Tablespace Point-in-Time Recovery (TSPITR)" and "Performing User-Managed TSPITR" sections in Database Backup and Recovery Advanced User's Guide, and also "Tablespace Point-in-Time Recovery" section in Concepts.

Best Answer

Related Solutions

Oracle XE: Transfer Database from Linux to Windows

Restore Oracle tablespace to its state from 2 days ago

Related Question