How to Export/Import A Single Queue Using Data Pump

In a recent project, a customer used Advanced Queueing (AQ), and I had to move a single queue from one database to another.

In Oracle AI Database, you can use Data Pump to move queues. According to the documentation, exporting and importing a queue is straightforward, but there are some caveats to be aware of.

How to Export a Queue

I want to export the ORDER_Q queue in the APPUSER schema.

Under the hood, a queue consists of a queue table. Often, the queue uses an object type for its payload. I start by finding the queue table name and payload type:

select q.queue_table, qt.object_type
from   dba_queues q, dba_queue_tables qt 
where  q.owner='APPUSER' and q.name='ORDER_Q'
and    q.owner=qt.owner and q.queue_table =qt.queue_table;

   QUEUE_TABLE        OBJECT_TYPE
______________ __________________
ORDER_QT       APPUSER.ORDER_T

I prepare a Data Pump parfile named queue_table_export.par. It exports the queue table:
```
directory=dpdir
dumpfile=order_qt.dmp
logfile=order_q_export.log
tables=APPUSER.ORDER_QT
exclude=statistics
```
- The tables parameter is the queue table for the queue I want to export. I find it using the query above.

Then, I export the queue table:

expdp ... parfile=queue_table_export.par

Next, I create a new parfile named payload_type_export.par. It exports the object_type or payload_type:
```
directory=dpdir
dumpfile=queue_types.dmp
logfile=queue_types_export.log
schemas=appuser
include=type:"IN ('ORDER_T')"
```
- To export types, I must perform a schema export.
- I use the include parameter to include only types and further limit the export to the ORDER_T type.

Then, I export the type:

expdp ... parfile=payload_type_export.par

How to Import

I start by importing the type:
```
impdp ... dumpfile=queue_types.dmp
```
Then, I import the queue table:
```
impdp ... dumpfile=order_qt.dmp
```
- Data Pump imports the queue table and all messages.
- It also calls the AQ API to create the queue infrastructure.
I connect to the database and start the queue:
```
BEGIN
DBMS_AQADM.START_QUEUE(
	queue_name => 'APPUSER.ORDER_Q'
);
END;
/
```
- Note that I start the queue by its name ORDER_Q. It was created automatically during the import of the queue table.
That’s it. I’ve moved the queue and can start using it.

What Happens During Import

Let’s see how Data Pump uses the AQ API when it imports the queue table.

I start Data Pump to extract the DDL from the dump file:
```
impdp ... dumpfile=order_qt.dmp sqlfile=order_qt.sql
```
- Data Pump reads the dump file and extracts the statements needed to perform this import.
- It doesn’t perform the import.
- The statements are written to the text file specified by sqlfile.

I examine the text file. This is what Data Pump would do during an import.

Data Pump starts by creating the queue table:

CREATE TABLE "APPUSER"."ORDER_QT"
   (    "Q_NAME" VARCHAR2(128 BYTE),
        "MSGID" RAW(16),
        "CORRID" VARCHAR2(128 BYTE),
        "PRIORITY" NUMBER,
        ...

Next come a few indexes:

CREATE INDEX "APPUSER"."AQ$_ORDER_QT_I"
   ON "APPUSER"."ORDER_QT" ("Q_NAME", "STATE", "ENQ_TIME", "STEP_NO", "CHAIN_NO", "LOCAL_ORDER_NO")
   ...

Finally, the call to the AQ API to create the queue:

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE_TABLE('ORDER_QT',1,16801800,2,0,0,'', SYS.DBMS_AQ_IMP_INTERNAL.DBVER_10i, '00:00');
COMMIT;
END;
/

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('598D4E9609226B60E0635301000A0A08'),'ORDER_QT','AQ$_ORDER_QT_E',1,0,0,0,0,'exception queue');
COMMIT;
END;
/

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('598D4E9609236B60E0635301000A0A08'),'ORDER_QT','ORDER_Q',0,5,0,0,0,'');
COMMIT;
END;
/

You can’t see the rows being loaded into the queue, but that happens too. All messages in the queue are also exported.

The DBMS_AQ_IMP_INTERNAL is an internal API. Don’t call it yourself.

What About a Full or Schema Export

If you perform a full or schema mode export, it’s simple. Data Pump exports all the required objects, including the types.

The only thing you must do is to start the queues after the import.

What If

The payload type must exist when you import the queue table. If you try to import the queue table without the type, Data Pump throws an error:

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "APPUSER"."ORDER_QT" ("Q_NAME" VARCHAR2(128 BYTE), "MSGID" RAW(16), "CORRID" VARCHAR2(128 BYTE), "PRIORITY" NUMBER, "STATE" NUMBER, "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO" TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN" NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" VARCHAR2(128 BYTE), "ENQ_TID" VARCHAR2(30 BYTE), "DEQ_TIME" TIMESTAMP (6), "DEQ_UID" VARCHAR2(128 BYTE), "DEQ_TID" VARCHAR2(30 BYTE), "RETRY_COUNT" NUMBER, "EXCEPTION_QSCHEMA" VARCHAR2(128 BYTE), "EXCEPTION_QUEUE" VARCHAR2(128 BYTE), "STEP_NO" NUMBER, "RECIPIENT_KEY" NUMBER, "DEQUEUE_MSGID" RAW(16), "SENDER_NAME" VARCHAR2(128 BYTE), "SENDER_ADDRESS" VARCHAR2(1024 BYTE), "SENDER_PROTOCOL" NUMBER, "USER_DATA" "APPUSER"."ORDER_T" , "USER_PROP" "SYS"."ANYDATA" ) USAGE QUEUE SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  OPAQUE TYPE ("USER_PROP") STORE AS SECUREFILE LOB (ENABLE STORAGE IN ROW CHUNK 8192 CACHE  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

That’s It

You can use Data Pump to move queues around. For table-mode exports, remember to export the payload type separately.

A few interesting links:

Happy queueing!

Using Data Pump Exports As Long-Term Storage

I recently helped a customer get the most out of Data Pump. Here’s how it all started.

For legal reasons, we must keep a backup of our data for five years. We want to use Data Pump exports. Do you have any recommendations?

Before someone flames me, I know that an export is not a backup, but we’re not talking about disaster recovery here.

Data Pump Export

Here are some of the Data Pump export parameters I would use.

Log File

logtime=all
metrics=yes
logfile=dataexport.log

Whenever you store data for the long term, I think it’s a good idea to store relevant metadata too. So, be sure to save the log file together with the dump files, and include diagnostic information using logtime and metrics.

Dump Files

dumpfile=dataexport_%T_%L.dmp
filesize=10000M
reuse_dumpfiles=yes

The dumpfile specification contains the %T wildcard, which translates into YYYYMMDD at runtime. It’s handy to have the date in the file names. Using %L allows the creation of multiple dump files.

I recommend breaking the dump files into smaller pieces using filesize. Smaller files are easier to handle and transport, and avoid some issues I’ve seen with object storage and huge files.

Content

schemas=myapp
tables=mytab
exclude=statistics

I would advise against doing a full export. It contains far too much information that you won’t need, like tablespace definitions, SQL management objects, and audit trail. A schema or table export is more suitable.

Be sure to exclude statistics. They take time to export and have no value. To narrow down the objects you export, you can also use the include parameter.

The parameters include and exclude are mutually exclusive in Oracle Database 19c. That’s no longer the case in later releases.

Compression

compression=all
compression_algorithm=high

Use compression to make the dump files smaller. I think it’s worth spending a few more CPU cycles on export and compressing as much as possible to reduce the dump file size, so use the high algorithm.

Creating a compressed export requires the Advanced Compression Option. However, importing a compressed backup does not require the same option. Always check the license guide for up-to-date information.

If you don’t have a license for Advanced Compression Option, you can compress the dump file using OS utilities. Unless you also encrypt the dump file. Compressing an encrypted dump file gives no benefit.

Integrity

data_options=verify_stream_format
checksum=yes

Since you are storing data for long-term storage, it’s good to double-check that there’s no corruption in the data. Data Pump writes into the dump file in a streaming manner, and you can check it along the way using data_options.

I’d also recommend generating a checksum and storing that in the dump file using the checksum parameter.

You can periodically check your dump for corruption by using impdp ... verify_only=yes. It will re-calculate the dump file checksum and match that against what is stored in the dump file.

Note that checksum is a feature of Oracle Database 21c.

Usually, using the default checksum_algorithm is fine for checking data integrity. If you want to guard against someone tampering with the dump file, it’s better to use checksum_algorithm=sha512. But for proper protection of your data, use encryption.

Encryption

encryption=all
encryption_mode=password

You can encrypt the data in the dump using the encryption parameter.

I prefer using a separate passphrase to protect the dump file, instead of using the database master encryption key. If you use the latter, you also need to have a backup of the database keystore.

Using Data Pump encryption requires a license for Advanced Security Option. Always check the license guide for up-to-date information.

Miscellaneous

flashback_scn=systimestamp
parallel=<n>

Remember to make a consistent export using flashback_scn. Alternatively, export from a quiesced database or a snapshot standby.

Use whatever parallel degree that you can afford. Normally, on x86-64 architectures, the optimal setting is twice the number of physical cores.

Using OCI Object Storage

The customer also stated:

We plan on storing the dumps in OCI object storage.

To reduce the storage costs, use either:

Archive storage
Standard storage and assign to the infrequent access tier.

Your Opinion

How would you store data for the long term? Do you have any experiences with the above Data Pump parameters?

Drop a comment and let me know.

Happy exporting!

Copy Data Pump Files Before the End of the Export

In Oracle Database 23ai, you can copy the dump files even before the export completes.

This saves time during your migration because you can start moving files to the target host while the export is in progress. Additionally, it potentially saves disk space because you can move the files away from the source host.

Which Files Can You Move

After starting the Data Pump export, you must connect to the database using:
- The same user who started the export
- A user with DATAPUMP_EXP_FULL_DATABASE role

Replace the job owner and name, and execute:

set serverout on
declare
   l_job_owner    varchar2(30) := 'DPUSER';
   l_job_name     varchar2(30) := 'SYS_EXPORT_FULL_01';
   l_handle       number;
   l_stsmask      integer := dbms_datapump.ku$_status_job_status;
   l_job_state    varchar2(30);
   l_status       ku$_status;
   l_dump_file    ku$_dumpfile;
begin
   l_handle := dbms_datapump.attach(l_job_name, l_job_owner);
   dbms_datapump.get_status(l_handle, l_stsmask, NULL, l_job_state, l_status);
   dbms_datapump.detach(l_handle);
   
   for i in l_status.job_status.files.first..l_status.job_status.files.last() loop
      
      l_dump_file := l_status.job_status.files(i);

      if l_dump_file.file_type = dbms_datapump.ku$_dumpfile_type_template then
        continue;
      end if;

      if (l_dump_file.file_bytes_written = l_dump_file.file_size) then
         dbms_output.put_line('DONE: ' || l_dump_file.file_name);
      end if;

    end loop; 
end;
/

The code lists all the files that Data Pump has marked as completed. Data Pump no longer writes to those files, and you can safely start moving them to the target system.

Prerequisites

You must use multiple dump files. You do that by including %L in your DUMPFILE specification.
```
DUMPFILE=exp%L.dmp
```
You must specify a file size that allows Data Pump to rotate into multiple files when they are full. I suggest using 5G, which is also a good setting for most cloud migrations.
```
FILESIZE=5G
```

What About

The code examines the max file size and bytes written to determine if the file is full. This is not the same as the physical size of the file in the operating system. You can’t use the file size information from the file system.
What about using rsync or similar tools? I guess that could work; however, we didn’t test that.
You could query the Data Pump control table for the information:
```
SELECT file_name
FROM <control_table>
WHERE process_order = -21
AND file_max_size = completed_bytes
AND file_max_size <> 0
ORDER BY file_name;
```
- However, querying the control table is not a supported approach. Use the above PL/SQL.

Happy exporting!

Oracle Data Pump and Compression – Also Without a License

Whenever you use Data Pump to export from Oracle Database, you should use compression. It’s conveniently built into Data Pump.

Pros:

The dump file is much smaller:
- Less disk space is needed.
- Easier to transfer over the network.
Often it is faster to use compression when you measure the entire workflow (export, transfer, and import).
Imports are often faster because less data needs to be written from disk.

Cons:

You need a license for Advanced Compression Option.
Compression requires CPU.

How Do I Enable Data Pump Compression

You simply set COMPRESSION option:

$ expdp ... compression=all

You use COMPRESSION option only for exports. When you import, Data Pump handles it automatically.

You only need a license for Advanced Compression Option when you use compression during export. You don’t need a license to import a compressed dump file.

Medium Is a Good Compression Algorithm

I recommend you use the medium compression algorithm:

$ expdp ... compression=all compression_algorithm=medium

Our experience and tests show that it best balances between compression ratio and CPU.

Here are the results of a test my team did:

Algorithm	File Size (MB)	Compression Ratio	Elapsed Time
NONE	5.800	1,0	2m 33s
BASIC	705	8,2	3m 03s
LOW	870	6,6	3m 11s
MEDIUM	701	8,2	3m 01s
HIGH	509	11,3	12m 16s

I would recommend high algorithm only if you need to transfer over a really slow network.

But I Don’t Have a License

gzip

You can still compress the dump file but not using Data Pump. Use OS utilities. In this case, I recommend splitting the dump file into pieces. It is easier to handle, and you can start transferring the dump files as they are compressed:

$ expdp ... filesize=5G dumpfile=myexp%L.dmp
$ gzip -r /u01/app/oracle/dpdir

Now, you transfer the files, uncompress and import:

[target]$ gunzip -r /u01/app/oracle/dpdir
[target]$ impdp ...

rsync

Another option is to use rsync. It has the option to compress the dump file over the network only:

$ expdp ... filesize=5G dumpfile=myexp%L.dmp
$ rsync -z ...

Cheatsheet

If you have the proper license, use Data Pump compression during export:

$ expdp ... compression=all compression_algorithm=medium

If you don’t have a license, compress the dump file over the wire only:

$ rsync -z ....

Don’t combine Data Pump compression and gzip/rsync! Compressing compressed stuff is not a good idea.