Oracle tablespace export expdp - Tips and Restrictions

Introduction

In a previous post, we have explained about Oracle tablespace export using the export data pump utility (expdp).  In this post, we will add more details to provide information on some restrictions and tips using the tablespace export data pump option.

Oracle does not export tablespace metadata

If you unload the tablespace using the tablespace export mode, Oracle does not export the metadata about the tablespace even if the expdp was run by a user with the DATAPUMP_EXP_FULL_DATABASE role.  This is unlike a schema mode export which exports even the schema metadata (schema creation definition, system and role grants to the schema etc.) if you perform such an export using a database user with the DATAPUMP_EXP_FULL_DATABASE role. But there is a workaround to obtain the tablespace metadata as well using the DBMS_METADATA package.

For example, if you want to perform a tablespace export data pump operation on HRTS tablespace, you can save its metadata (tablespace creation definitions) first using the below command.

select dbms_metadata.get_ddl('TABLESPACE','HRTS') from dual;

(Tip: This functions returns output as CLOB datatype, so if you use in SQL*Plus , it may truncate the returned output. One option is to use a bigger output buffer option such as "set long 100000")

Oracle does not export PL/SQL procedures dependent on tables in the tablespace

Tablespace export data pump operation only unloads tables only. Though Oracle mentions in  the utilities guide that the table's dependent objects are also unloaded, this seems to be only for indexes.  If you have a procedure which refers these tables in the tablespace, they are not exported. This could be because procedures are stored in the SYSTEM tablespace and not in the tablespace in question that is being exported out of the database.

Oracle expects tablespaces created already during import data pump operation.

Because Oracle does not export tablespace metadata (tablespace creation definitions), you have to create the tablespaces with adequate size (atleast equal to the source tablespace size) in your destination database.  If your plan is to use different tablespace names, you can use the REMAP_TABLESPACE option in the import data pump (impdp) command. The below example import data pump (impdp) command demonstrates how to import the EMP table only into a HRTS_NEW tablespace in the destination database.

impdp tables=EMP remap_tablespace=(HRTS:HRTS_NEW) dumpfile=DATA_PUMP_DIR:ts_dumps_%U.dmp logfile=DATA_PUMP_DIR:impdp_orders_log.log

Conclusion

Use the tablespace export mode if your sole aim is to only export all tables and their dependent indexes in a tablespace. A related use case for a tablespace export is for taking  a backup of tables and indexes in the tablespace prior to dropping the tablespace.  The examples used in this post are tested on a 11g R2 Oracle database and should work on 12c R1 and 11g R1 databases as well.

ORA-01501: CREATE DATABASE failed ORA-01519 sql.bsq

Introduction

If you are still supporting Oracle 10g R2 databases and are encountering the error ORA-01092: ORACLE instance terminated. Disconnection forced instance termination while running the CREATE DATABASE statement using the Oracle provided syntax in the administrator's guide, then you may be hitting this bug. After further investigation of the alert log file, you may notice the below errors.

ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 6268
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name

Problem

Most Oracle DBAs use the Oracle's example CREATE DATABASE command in the administrator's guide.

There is a documentation bug in Oracle 10g R2 administrator's guide (Part No B14231-02).  Specifically, the CREATE DATABASE statement in Page 77 of the PDF

If you observe closely, these errors are caused by the line "DEFAULT TABLESPACE tbs_1". There is no data file specified for this tablespace in the CREATE DATABASE command.


CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Solution

Just remove the line DEFAULT TABLESPACE tbs_1 in the CREATE DATABASE statement and retry. This time your database will be created successfully.

How to configure and start VNCServer on Linux

How to configure and start VNCServer on Linux 6

Please follow the below guidelines to successfully configure and start VNCServer service on Linux operating system versions 6.x (Red Hat Enterprise Linux, Oracle Enterprise Linux, CentOS etc.). If your operating system has version 7.x, then please follow this article instead.

1) As root, edit /etc/sysconfig/vncservers and add the below line at the end of the file.
VNCSERVERS=”1:root”

This is to start the vncserver process automatically after a server reboot using root as the vncserver process owner. If you want a different operating system user, then replace the user root with the other operating system user name

2) As root, run the command chkconfig vncserver on


3) Run the vncpasswd command to set the password to connect to the vncserver service.


4) If you want to terminate the vncserver process, execute the vncserver -kill :1.0 command. The :1.0 is the display setting.