RMAN: restore database skip tablespace

Sometimes we want to restore full database except a certain tablespace (data not needed, or a point in time recovery omiting a big tablespace, etc.). That means we need to skip the tablespace(s) for restore operation. This way, the restore operation time can be much quicker.

Remember that the RESTORE DATABASE command restores all the datafiles, except the offline or read-only tablespaces.

Note that RESTORE DATABASE is not the same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.

To omit a certain tablespace for restore operation use:

RESTORE DATABASE SKIP TABLESPACE tablespace_name.

Suppose I want to omit the restore of indexes tablespace INDX01,INDX02. Then my restore command will be:

RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02;

If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore.

The DROP option indicates that RMAN does not intend to recover these files and instead remove their tablespaces from the database's rman operations after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.

Suppose you want to skip forever the restore of the tablespaces EXAMPLE, INDX01 and INDX02 then your command will be:

RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;

Reinstall datapump on 10g

Sometimes, very often due to miss use, your Datapump setup is all messy. One thing left, clean it up and reinstall it.


To quickly cleanup datapump objects:

Identify datapump objects :

select * from dba_objects where object_name like 'SYS_EXPORT%'

Next identify object currently accessed :

select * from v$access where object like 'SYS_EXPORT%'

The objects that are present on dba_objects but not on v$access can be dropped


And the Datapump installation procedure

Catdph.sql will Re-Install DataPump types and views=

SQL> @?/rdbms/admin/catdph.sql

Note: If XDB is installed the it is required to run "catmetx.sql" script also.

prvtdtde.plb will Re-Install tde_library packages

SQL> @?/rdbms/admin/prvtdtde.plb

Catdpb.sql will Re-Install DataPump packages

SQL> @?/rdbms/admin/catdpb.sql

Dbmspump.sql will Re-Install DBMS DataPump objects

SQL> @?/rdbms/admin/dbmspump.sql

To recompile invalid objects, if any

SQL> @?/rdbms/admin/utlrp.sql

Install Oracle 11g on REHL 6

Step by step Oracle database 11g install (Part 2)

If you're here, you've already have a Linux system setup and ready for the proper Oracle Install (users, group, pre-require packages, etc). If not, you can find it here.

The 2 necessary zip files are available on the Oracle Database Software Downloads page.

Change the SELINUX value to permissive in the /etc/selinux/config

Install the ODBC drivers:

yum install unixODBC-devel unixODBC

Unzip the files and run ./runInstaller from the database directory. Follow the steps.


Then, there is a bit more configuration to do:

In the oracle user .bash_profile file:

ORACLE_BASE="/u01/app/oracle"
ORACLE_HOME=$ORACLE_BASE"/product/11.2.0/dbhome_1"
ORACLE_SID=orcl

PATH=$PATH:$ORACLE_HOME/bin

export PATH
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID

The database is up and running. Check with sqlplus or the Oracle Enterprise Manager (By default https://localhost:1521/em)