Data Management

Oracle Tip: Use DBCA to clone a database

Scott Stephens explains that, once the DBCA utility starts, you can create a new database, configure database INIT.ORA options, delete an existing database, or manage database templates.

This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

DBCA is a utility that allows you to configure, manage, and create a database. It's usually run during an initial database installation when you select the Create A Database option. You can either create a database based on database templates that ship with the software, or you can load a template from a previous session.

Many administrators don't realize that they can go back to this utility at any time by typing dbca at the command line. Once the utility starts, you can create a new database, configure database INIT.ORA options, delete an existing database, or manage database templates. These database templates are the same as the ones at installation time (i.e., General Purpose, Data Warehouse, and Transaction Processing).

During installation, an alert window asked if you wanted to save your template. Don't worry if you clicked No, or if you manually tuned your database since installation. The DBCA utility is able to examine your database settings and generate a template based on your current system—even optionally exporting the current data. This makes it a good utility to move a database to another machine or to ship starter databases for value-added products.

The DBCA utility stores templates in a file with a .dbc extension. A .dbc file contains a listing of init.ora parameters, location of data files, redo log files, and control files in your database in XML format. If you choose to include your databases data, it will also create a file with a .dbj extension, which is a ZIP file that contains images of your data files. Some users might want to create a backup copy of all these settings in case they need to recreate a database from scratch on a new machine.

There are variables such as {ORACLE_BASE} and {DB_NAME} to allow DBCA to install files into the appropriate directory for an Oracle software installation. Since it's simple XML, it's quick to look up the differences between the templates with a diff function. For example, the difference between the General Purpose and Data Warehouse database templates are in the Data Warehouse template for Oracle 9i 9.2.0.0:

  • The TEMP tablespace is smaller by 1M.
  • The init.ora parameters:

—star_transformation_enabled is turned on.

—pga_aggregate_target is larger.

—query_rewrite_enabled is turned on.

—db_file_multiblock_read_count is doubled.

—sort_area_size is doubled.

—It doesn't set the value of hash_area_size.

—The db_cache_size is smaller.

For the Transaction Processing template, the differences are:

  • The Oracle OLAP option is disabled.
  • The init.ora parameters:

—db_block_size is half.

—undo_retention is smaller.

—pga_aggregate_target is smaller.

—db_file_multiblock_read_count is half.

—hash_join_enabled is turned off.

—the db_cache_size is larger.

  • It doesn't create a CWMLITE tablespace (because the OLAP option is turned off).
  • The EXAMPLE and SYSTEM tablespaces are slightly smaller.
  • The TEMP tablespace is twice as large.

When you export the data from your database using the DBCA utility, it creates a file with the extension .dfj, which appears to be a ZIP file of raw images of each data file. This means that the data can be somewhat secured during shipping. These files, however, aren't as well documented and supported as EXP and IMP. Also, these images are most likely written directly to disk rather than via SQL like IMP, so it's only appropriate to use .dfj files with database creation and not as a backup.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.

Editor's Picks