Oracle’s latest technology to export and import data from databases is the Data Pump utility. It is a more refined version of earlier export and import utilities. The older utilities continue to be available until oracle 12c, still Datapump is the Oracle recommended utility to perform exports and imports owing to its sophisticated features.
Oracle database 12c offers the new Data Pump technology, a server-side infrastructure for fast data movement between Oracle databases. The Datapump enables DBA transfer large amounts of data and metadata at very high speeds compared with traditional export and import utility. Data Pump enables higher speed data transfers by using palatalization techniques.
Data Pump exports are twice faster while import jobs are 15 to 30 times faster than the traditional utilities.
Datapump the data export and import technology that has been a part of oracle database ever since its earlier versions is finding lots of improvement, interesting feature addition starting Oracle database 12c.
Here is a simple list of new features and feature enhancement in oracle database 12c datapump:
1) Unique feature of oracle database 12c is the multitenancy. Oracle database 12 has Container database, pluggable database concept. Datapump does support CDB, PDB export and import activity
1.1) Using datapump in 12c it is possible to migrate portion (or) all of non-CDB to PDB
1.2) Migration is supported across PDB’s in same CDB’s
1.3) Migration is supported across PDB’s in different container databases
1.4) Migration from pluggable database to non-CDB is supported
2) Transportable tablespace can be used with FULL database export and import option
3) Now there is a provision to perform export and import of views as tables in oracle 12c. Perform export using parameter views_as_tables. It is possible to perform non-network as well as network import using views_as_tables
4) In prior versions whenever there is a need to import the data we need to disable archive logging following traditional approach. Now it is possible to perform import using TRANSFORM parameter as follows
impdp ‘username/password’ transform=dis_archive_logging:Y
The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.
A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur.The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.There are several types of available baselines in Oracle Database:
1) Fixed Baselines
2) Moving Window Baseline
3) Baseline Templates
Fixed Baselines :
1) A fixed baseline corresponds to a fixed, contiguous time period in the past that we specify.
2) Before creating a fixed baseline, carefully consider the time period we choose as a baseline, because the baseline should represent the system operating at an optimal level.
3) In the future, we can compare the baseline with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time
Moving Window Baseline :
1) A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the AWR data in the entire AWR retention period can be used to compute metric threshold values.
2) Oracle Database automatically maintains a system-defined moving window baseline.
3) The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days.
4) If we are planning to use adaptive thresholds, consider using a larger moving window—such as 30 days—to accurately compute threshold values.
5) We can resize the moving window baseline by changing the number of days in the moving window to a value that is equal to or less than the number of days in the AWR retention period.
6) To increase the size of a moving window, we will first need to increase the AWR retention period .
Baseline Templates :
We can create baselines for a contiguous time period in the future using baseline templates.
There are two types of baseline templates:
A single baseline template can be used to create a baseline for a single contiguous time period in the future. This is useful if we know beforehand of a time period that we want to capture in the future. For example, we may want to capture the AWR data during a system test that is scheduled for the upcoming weekend. In this case, we can create a single baseline template to automatically capture the time period when the test will take place.
A repeating baseline template can be used to create and drop baselines based on a repeating time schedule. This is useful if we want Oracle Database to automatically capture a contiguous time period on an ongoing basis. For example, we may want to capture the AWR data during every Monday morning for a month. In this case, we can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval such as one month.
Oracle logminer tool helps to get more insight on ongoing transactions by dissecting the online redo logs. Upon enabling Oracle Log miner, querying v$logmnr_contents, the operation shows unsupported with a message that says object or datatype not supported
The reason behind this message is that some datatypes are not supported in log miner. Following are the datatypes that are not supported until latest oracle version 11g
1) Abstract datatypes called ADT
3) LOB and BFILE
5) Collections like nested tables
6) Object references