Full Database Export and Import in Oracle 12c

Understanding Full Database Export and Import

Oracle 12c full database export and import are critical operations that enable the transfer of all database objects, data, and metadata from one Oracle database to another. The utilities responsible for these operations are the traditional exp and imp commands, as well as the faster and more advanced Data Pump expdp and impdp commands. Data Pump, available from Oracle 10g onwards, is the recommended method for these operations due to its enhanced performance and features.

Prerequisites

Prior to performing a full database export or import, ensure that the necessary roles are assigned. The DATAPUMP_EXP_FULL_DATABASE role is required for full database exports, while the DATAPUMP_IMP_FULL_DATABASE role is needed for imports. It's important to note that when importing into an empty database, errors are expected and should be planned for.

Performing a Full Database Export with Data Pump

To initiate a full database export using Data Pump, use the expdp command with the FULL parameter set to 'Y'. This operation unloads all data and metadata into a dump file set, which can include multiple disk files containing table data and object metadata. For performance optimization, consider running Data Pump Export in parallel or using the COMPRESSION parameter to reduce the dump file size.

Performing a Full Database Import with Data Pump

For importing a full database, the impdp command is utilized. This tool allows you to import the entire dump file set or specific schemas using the SCHEMAS parameter. If the tablespaces differ in the target database, use the REMAP_TABLESPACE parameter. The SQLFILE parameter can provide insights into the dump's contents before executing the import. To minimize resource usage during import, adjust the PARALLEL parameter accordingly.

Recommendations and Best Practices

While the older exp and imp utilities are still available, using expdp and impdp is highly recommended due to their superior speed and functionality, such as the ability to filter specific data or metadata for export and import operations. Additionally, for databases where the source and target differ by more than two versions, network exports are not supported, and certain objects like TRIGGER and VIEW cannot be exported in parallel.

Monitoring and Troubleshooting

Data Pump provides interactive commands to monitor and modify ongoing jobs. Use these commands to stop or adjust the job if needed. Be mindful of the limitations, such as the inability to export certain data types or the restriction of parallel export for some database objects. Address any errors encountered during imports into empty databases as part of the post-import process.

Frequently Asked Questions

How do I perform a full database export in Oracle 12c?

You can perform a full database export in Oracle 12c using the Data Pump utility with the expdp command. An example command is: 'expdp system/manager@xyz FULL=Y DUMPFILE=FULL.DMP DIRECTORY=your_directory'. Ensure you have access to the 'your_directory' directory object pointing to a valid path on the server.

What is the command for a full database import in Oracle 12c?

For a full database import in Oracle 12c, you can use the Data Pump utility with the impdp command. An example command is: 'impdp system/manager@abc FULL=Y DUMPFILE=FULL.DMP DIRECTORY=your_directory LOGFILE=import.log'. Replace 'your_directory' with the directory object you have access to, and ensure the DUMPFILE is accessible from that path.

Can I import only specific schemas from a full database export in Oracle 12c?

Yes, you can import specific schemas from a full database export by using the SCHEMAS parameter in the impdp command. For example: 'impdp system/manager@abc SCHEMAS=schema1,schema2 DUMPFILE=FULL.DMP DIRECTORY=your_directory'. This will import only the specified schemas 'schema1' and 'schema2'.

What should I do if I encounter character set issues during import in Oracle 12c?

If you encounter character set issues during an import in Oracle 12c, ensure that the NLS_LANG environment variable is set correctly to match the character set of the source database. Oracle's Data Pump may also automatically map the source database's character set to the target database's character set.

Is it possible to see the DDL that would be executed during an import without actually performing the import?

Yes, you can preview the DDL by using the SQLFILE option with the impdp command. For example: 'impdp system/manager@abc FULL=Y DUMPFILE=FULL.DMP DIRECTORY=your_directory SQLFILE=ddl_preview.sql'. This will not perform the import but will generate a file 'ddl_preview.sql' containing the DDL statements.

Common Use Cases

• 
  Data migration to a new Oracle 12c server
• 
  Backup and restoration of the entire database for disaster recovery
• 
  Cloning databases for testing and development purposes
• 
  Upgrading the Oracle database from an older version to 12c
• 
  Consolidating multiple databases into a single Oracle 12c instance

Why Choose Sourcetable Over Oracle 12c Exports?

Unlock real-time data access and streamline workflows with Sourcetable, a powerful alternative to the cumbersome process of full database exports and imports in Oracle 12c. By integrating directly with your databases, Sourcetable delivers up-to-the-minute data at your fingertips.

Sourcetable simplifies data interaction, offering a user-friendly spreadsheet-like interface that eliminates the complexity of traditional database management. This approach saves time and enhances productivity for users requiring immediate data manipulation and analysis without deep technical expertise.

Maximize efficiency and reduce dependency on database administrators with Sourcetable. Its intuitive platform empowers users to query, extract, and manage data seamlessly, bypassing the need for extensive export and import operations associated with Oracle 12c.

Embrace the agility of Sourcetable for a nimble data management experience. Adapt quickly to business changes by accessing and modifying data in real-time, ensuring your organization stays ahead in a data-driven world.