Author: Daniel Overby Hansen

Disstinguished Product Manager for Cloud Migrations at Oracle. I help customers make the most out of Oracle Database.

Why Are You Not Using Bigfile Tablespaces?

Recently, I worked on two cases that could be solved using bigfile tablespaces.

Bigfile tablespaces have been around for ages but are typically not the default. If you use the CREATE TABLESPACE command and don’t specify a tablespace type, you will probably get a smallfile tablespace.

In a smallfile tablespace in an 8k block size database, each data file can only be 32 GB. For a huge tablespace – double-digit TB – you end up with many files. In a bigfile tablespace, there is only one data file, but it can grow up to 32 TB. Further, Oracle Autonomous Database comes with bigfile tablespaces.

Frustrated over the problems with smallfile tablespaces, I wanted to understand the reluctance to use bigfile tablespace.

I asked around: Why are you not using bigfile tablespaces?

The Answers

They Take a Long Time to Back Up

True, unless you use multisection backups. If so, multiple channels can work on the same data file and you should not see performance bottlenecks on bigfile tablespaces.

It’s very simple to perform multisection backups. Simply add the section size clause to the RMAN backup command.

RMAN> backup database ... section size 30G ...

Recovering a Bigfile Data File Is a Pain

If you accidentally delete a bigfile data file, it is very time-consuming to restore it. It could be huge – potentially 32 TB. In contrast, if you delete a smallfile data file, it is much smaller – maximum 32 GB.

How often do you accidentally delete a data file? OK – some of us might know a horror story from back in the days – but it is very rare.

Should it happen, you can switch to your standby database while restoring the data file.

Plus, if you use Oracle Automatic Storage Management (ASM), you can’t accidentally delete a data file. If a data file is in use by a database, you can’t delete the file in asmcmd.

Incompatibility With File System

I know at least one operating system, which, in older versions, had a maximum limit of files of 16 TB. This is no longer a problem in modern operating systems. Nor is it a problem if you use ASM.

Someone even claimed it was the case in certain tape systems, although I couldn’t verify it.

It’s Always Been Like This

In the old days, I understand why you would want to use smallfile tablespaces. But not anymore. The downsides don’t exist anymore.

But, it’s hard to migrate to bigfile tablespaces!

No, it is not.

In recent versions of Oracle Database, you can move tables into a new tablespace without downtime. The same applies to indexes which you can rebuild into a new tablespace. It’s all online operations. No downtime, no interruptions!

LOB segments might be a little more tricky. DBMS_REDEFINITION could be a solution.

Some of these options require additional licenses. Always check the license guide before you start to use a new feature.

Still, I do understand that for existing databases, you don’t want to migrate. But for new tablespaces and new databases, I’d always go with bigfile tablespaces.

Let Me Know

I don’t see a reason for using smallfile tablespaces anymore.

But I’m curious. Why are you not using bigfile tablespaces? Please leave a comment with your reason or concern.

By the way – usually, an Oracle Database can hold only a certain amount of data files. When you start to go into Petabytes, you’ll need bigfile tablespaces.

Updates to Blog Post

The More Data Files, the Longer Switchover Time

Ludovico Caldara, the Data Guard PM, pointed out on LinkedIn, that the more data files you have, the longer a Data Guard switchover takes.

Contention When File Expands

dbroots left a comment referencing this MOS note: Alter Tablespace Resize on Bigfile Tablespace Causes Sessions to Hang with ‘Enq: TX – contention’ and ‘Buffer busy waits’ (Doc ID 2089689.1).

For very busy systems, you might see a short wait event while the data file expands in a bigfile tablespace. If it becomes too big a problem, you need to expand your data file in off-peak periods.

Further Reading

Applying Parallelism to Data Pump Exports and Imports

When you use Oracle Data Pump, you often want it to happen fast. Applying parallelism is the obvious choice. Here are a few things you might not be aware of.

Parallel and Export

During export, when a worker needs to write to a file, it needs an exclusive lock on a dump file. If your dump file specification does not allow creating multiple files, then the workers will queue up waiting for a lock on that single dump file.

You should always allow Data Pump to create as many dump files as it wants:

expdp ... dumpfile=my_exp_%L.dmp

The wildcard operator, %L, allows the creation of many files by adding a number. Previously, you would use %U, but %L allows more files. Check the documentation for other substitution variables.

Import From a Single Dump File

Even if you made the export with no parallel or just have one dump file, you can still import in parallel.

During an import, Data Pump only needs to read from the dump file, and multiple workers can read from the same file without problems. The only issue would be how much I/O a single file can handle for a large database.

Does import parallelism has to match export parallelism, and the answer is no. The two are independent. This would work fine:

expdp ... parallel=4
impdp ... parallel=64

Import From a Previous Release

In Oracle Database 12.2, Data Pump starts to support parallel metadata import during a regular import. If you import from a dump file from a previous version (that does not support it), you can still import metadata in parallel in Oracle Database 12.2 and newer. There are no specific requirements for the dump file to perform parallel operations.

The same applies to transportable tablespace jobs, which you can do in parallel in Oracle Database 21c.

Want to Know More?

How to Export Database Scheduler Jobs Using Data Pump

Which options do you have to move scheduler jobs (DBMS_SCHEDULER) to a different Oracle Database?

Data Pump

In Data Pump, scheduler jobs are referred to as PROCOBJ. You can get just the scheduler jobs using:

expdp ... include=procobj
impdp ... include=procobj

If you need just a subset of the scheduler jobs (works for impdp as well):

expdp ... include=procobj:" IN ('JOB1', 'JOB2')"

Data Pump and SQLFILE

If you have a Data Pump dump file, you can extract the DDL of the scheduler jobs using the sqlfile option:

impdp ... include=procobj \
   sqlfile=my_scheduler_jobs.sql

The DDL is written to a text file specified by sqlfile. This allows you to review the job defintions and, optionally, change them. Then, you execute the commands.

DBMS_METADATA

If you need to export just a few jobs, it might be easier to simply get the defintions using the metadata API:

select
   dbms_metadata.get_ddl('PROCOBJ','<name>','<owner>')
from 
   dual;

SYS Jobs

In older releases of Oracle Database, you could not get the definition of a job owned by SYS. A simple workaround is to copy the job into another schema and then generate the DDL:

exec dbms_scheduler.copy_job('SYS.MY_JOB','APPUSER.MY_JOB');
select
   dbms_metadata.get_ddl('PROCOBJ','MY_JOB','APPUSER')
from 
   dual;

Appendix

Credits

Thanks to my colleague, Klaus Gronau. He has done more migrations than I’ll ever do and he maintains a remarkable set of notes. From his notes, he dug out many good pointers.

It’s a Wrap – Real World Database Upgrade and Migration to 19c and 23c

Here is a short post about the workshops I did with Mike Dietrich in Brussels and Utrecht on Real World Oracle Database Upgrade and Migrations 19c and 23c.

The Slides

The slide deck is a goodie bag full of tech content. Even if you didn’t participate, there is something of interest.

Slide deck from Real World Oracle Database Upgrade and Migrations 19c & 23c

Questions

The audiences in our workshops were amazing, and we got many good (and challenging) questions. Here is a follow-up on those we couldn’t answer.

SQL Plan Management on Standby Databases

On an Active Data Guard, SQL Plan Management (SPM) is active and will help the optimizer select only validated execution plans.

However, SPM cannot create new baselines nor perform any form of evolution. SPM stores its information in the SQL Management Base in the SYSAUX tablespace. Since a standby database is open in read-only mode, such features are disabled.

You need to capture and evolve plans on the primary database.

Exporting BasicFile LOBs

I just blogged about a trick that you can use to speed up export of BasicFile LOBs.

Refreshable Clone PDBs

We also explained how to use refreshable clone PDBs to migrate non-CDBs into PDBs. Here is a video which shows a little more details.

Hands-On Labs

Remember, you can try many of the features mentioned in our Hands-On Labs.

  • It’s free
  • It runs in a browser
  • Nothing to install

It’s better to fail in our lab than in production!

Thanks

Thanks to the excellent audience in Brussels and Utrecht. It was a pleasure talking tech with you.

My favorite moment was when someone wanted Mike to sign an original, sealed Oracle7 box. That’s cool.

If you would like a similar workshop in your neighborhood, take hold of your local Oracle office.

Workshop in Brussels and Utrecht Real World Oracle Database Upgrade and Migrations 19c & 23c

Data Pump and Faster Export of BasicFile LOBs

Exporting BasicFile LOBs can be quite a pain. If your maintenance window is too short, here is a way to make the export faster.

The following applies to BasicFile LOBs:

select owner, table_name, column_name 
from   dba_lobs 
where  securefile='NO';

Background

The old BasicFile LOBs do not offer any parallel access methods. The new, improved SecureFile LOBs offer superior functionality, including full parallel access to the LOB. This is a good reason to migrate any old BasicFile LOBs in your database.

When Data Pump starts to export a table with a BasicFile LOB, only one worker will be assigned (due to the limited parallel access methods). If you have a large table, it will take a lot of time for that one worker to process the table.

Solution

Instead of one Data Pump job with one worker processing the table with the LOB, the idea is to start multiple Data Pump exports. Each export has one worker working on a dedicated part of the table.

Imagine you have a table with 100 rows. You start four concurrent Data Pump sessions:

Job Rows
Data Pump job 1 1-25
Data Pump job 2 26-50
Data Pump job 3 51-75
Data Pump job 4 76-100

To do this, you need to use the Data Pump query parameter.

Generating the Predicates

Now, you need a way to split the table into chunks and generate a predicate for each of the concurrent Data Pump sessions. It is important that:

  • All rows are exported.
  • No rows are exported more than once.

Further, you should ensure that:

  • The rows are spread evenly across the Data Pump jobs.

ROWIDs

An elegant solution is to use modulus and the block number that holds the row. You can get the block number using rowid and dbms_rowid:

Job Predicate
Data Pump job 1 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 0
Data Pump job 2 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 1
Data Pump job 3 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 2
Data Pump job 4 where mod(dbms_rowid.rowid_block_number(rowid), 4) = 3

This might not be the fastest option, but it is a generic one that works without any knowledge of the table structure you are exporting.

Keys

You can also split the table on a primary/unique key or any other column that offers an even distribution of values:

Job Predicate
Data Pump job 1 where mod(--primary_key_column--, 4) = 0
Data Pump job 2 where mod(--primary_key_column--, 4) = 1
Data Pump job 3 where mod(--primary_key_column--, 4) = 2
Data Pump job 4 where mod(--primary_key_column--, 4) = 3

More Workers

If you need more sessions working on the table, you can change the modulus operation and add more Data Pump jobs.

How to Export With ROWID

Here is the first of the Data Pump parameter files:

logfile=exp_lob_0.log
dumpfile=exp_lob_0.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 0"

The second one. Notice how the serial number changes in the logfile and dumpfile parameters. Also, the modulus operation changes:

logfile=exp_lob_1.log
dumpfile=exp_lob_1.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 1"

The third one:

logfile=exp_lob_2.log
dumpfile=exp_lob_2.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 2"

The fourth one:

logfile=exp_lob_3.log
dumpfile=exp_lob_3.dmp
tables=appuser.table_with_lob
query="where mod(dbms_rowid.rowid_block_number(rowid), 4) = 3"

Now, you can start four concurrent Data Pump jobs. You must execute the commands in different terminals:

expdp ... parfile=exp_lob_0.par
expdp ... parfile=exp_lob_1.par
expdp ... parfile=exp_lob_2.par
expdp ... parfile=exp_lob_3.par

How to Import

When importing, you change the LOB to SecureFile LOB, which allows parallel access. Then, take one Data Pump job at a time.

  • The first job does a regular schema import of the first dump file:

    impdp ... \
   dumpfile=exp_lob_0.dmp \
   logfile=imp_lob_0.log
   transform=lob_storage:securefile \
   parallel=4
    • It also creates the table itself.
    • You should always convert the LOB to SecureFile LOB during import. Conversion happens on-the-fly and allows for parallel import of data. Even if the LOB originally was a BasicFile LOB.

  • Now, proceed with the other dump files in serial. Since you already converted the LOB to a SecureFile (previous step), you can now perform a Data Pump import using native parallelism:

    impdp ... \
   dumpfile=exp_lob_1.dmp \
   logfile=imp_lob_1.log
   parallel=4 \
   table_exists_action=append
impdp ... \
   dumpfile=exp_lob_2.dmp \
   logfile=imp_lob_2.log
   parallel=4 \
   table_exists_action=append
impdp ... \
   dumpfile=exp_lob_3.dmp \
   logfile=imp_lob_3.log
   parallel=4 \	  
   table_exists_action=append
    • Notice how the dumpfile and logfile changes for each job.
    • Run the jobs in serial. Each job will use Data Pump parallelism.

That’s it!

Few Words of Caution

  • You must figure out how you create indexes – if they are present on the table. Ideally, you want to postpone index creation until the last job has loaded its rows. Otherwise, you’ll have expensive index maintenance happening when you load data.

  • Data Pump uses Advanced Queueing, which relies on the streams pool in the SGA. Be sure that streams_pool_size is set high enough when you start multiple data pump sessions. In most situations, setting it to 2G should be more than enough:

    alter system set streams_pool_size=2G scope=memory;

  • Do this in a maintenance window only with no users connected.

How to Migrate from Oracle v5 to Oracle Database 23c

More often than you think, I get mails about migration options from ancient Oracle Database releases. Typically, it is from Oracle 8i or 9i. My high score is an inquiry about Oracle 7.2.

My good colleague, Rodrigo, calls it migrations from Jurassic Park data centers.

At one point, our team got so many questions about it that we decided to create a presentation. It premiered at Oracle DatabaseWorld at CloudWorld 2023. We named it:

Help! My Database Is Still On 8i

Migration Options From Oracle v5 and Newer

In Oracle Database 10g, Oracle introduced Data Pump – replacing the original export and import utilities. If possible, use Data Pump.

In older versions, you must use original export, exp. In recent versions of Oracle Database, Oracle desupported original export, but they still support original import utility, imp, for migrations from really old Oracle Database releases.

Oracle released original export back in Oracle v5. You can export data in Oracle v5 (or newer) and import it directly into an Oracle Database 23c PDB. This is 40 years of backward compatibility. This is rather impressive!

From Oracle 8i Database, you can use same-platform transportable tablespaces. From Oracle Database 10g, you can even do cross-platform transportable tablespaces.

To Which Version Should You Migrate?

If you have such old databases in your environment, I strongly recommend migrating to Oracle Autonomous Database. In Autonomous Database, Oracle will take care of the database for you. Oracle will patch it, maintain it, and upgrade it. You will never end up with a legacy database again. Note that the original import utility does not meet the security standards of Autonomous Database, so it is impossible to import directly into Autonomous Database. In such a situation, you first import into a staging database that supports Data Pump, and then into Autonomous Database.

Otherwise, I recommend going to the latest long-term support release. Also, you should migrate to the multitenant architecture and import directly into a PDB.

Client/Server Interoperability

Before you migrate the database, you must come up with a plan for your clients and applications. Older database clients can’t connect to modern databases.

With each Oracle Database release, the authentication mechanism changes to become more secure and support newer features. This means that clients must be of a certain version to communicate with the database.

If you migrate to Oracle Database 23c, your clients must be on 19c or newer. Check Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1) for details.

How to Migrate From 8i to Autonomous Database

Here’s a video showing you such a migration step-by-step.

The biggest pitfall in such migration is the loss of characters due to incorrect character set conversion. You must set the NLS_LANG environment variable to the character set of the database. Otherwise, you’ll use the default language setting of your operating system and that might lead to character loss.

Only in specific situations do you set the NLS_LANG to something else.

Appendix

Fun Fact

Here’s a list of Oracle Database releases, including their year of release, marque features, and a mobile phone of the same year.

Year of release Release Feature Mobile phone
1985 Oracle v5 Support for OS/2 Motorola DynaTAC – retail price in today’s prices $12.000
1988 Oracle v6 Oracle Parallel Server OPS) Motorola MicroTAC
1996 Oracle 7.3 Cost based optimizer Motorola StarTAC – popular amongst trekkis because it looks like a communicator
1998 Oracle 8i Database RMAN Nokia 5110 – you could change the cover (XpressOn covers)
2001 Oracle 9i Database Real Application Cluster (RAC) Sony Ericsson T68 – first phone with color screen
2003 Oracle Database 10g Data Pump Nokia 6600 – still one of the most sold units
2007 Oracle Database 11g Exadata iPhone
2013 Oracle Database 12c Multitenant Blackberry Q10
2019 Oracle Database 19c Automatic Indexing Huawai Mate X – foldable screen

Further Reading

It’s a Wrap – Danish Oracle User Group Day

Yesterday, I had the pleasure of talking at the annual Danish Oracle User Group event. I want to share the slides and a few thoughts with you.

The event is part of the EMEA Community Tour, which continues today in Finland.

In Denmark, we had the pleasure of welcoming two guests from Italy. Traveling in Europe is easy, so why don’t you do like the two gentlemen from Italy? Find a user group event with an agenda of interest, book a flight, and enjoy the knowledge offered by our European communities.

Slides

Help! My Database Is Still On 8i!

Get the slides here.

This talk partly talks about the risk of old databases and how you can modernize; partly it is a walk down memory lane. If you don’t have any vintage database, it’s still worth flipping through the slides.

Patch Me If You Can

This was a no-slide zone, so there are no slides to share. But we did have an interesting talk about Datapatch and the ability to patch online.

Thank You!

The organizers of the event did a good job. Thank you for taking the time and effort pulling this together. Kudos!

Thanks to the sponsors:

And to Oracle Denmark for hosting the event.

Become an ACE

We had several Oracle ACEs at the event in Denmark. If you also love to share knowledge, you should apply for the community. If you know somebody who deserves a nomination, you should nominate them for the Oracle ACE program.

If you need help getting started, many seasoned Oracle ACEs are offering assistance. The MASH program engages with new speakers and helps them get started – FOR FREE!

Jeannette Holland welcomes everyone to the event Julian Dontcheff on why you want to stay with Oracle Database Jeff Smith with insights into using Oracle ORDS and REST APIs

Things to Consider When Importing Advanced Queues using Oracle Data Pump

Oracle Data Pump supports moving Advanced Queues (AQ) using export/import and full transportable export/import. But there are some things to be aware of.

Data Pump Does Not Start Queues

Data Pump creates the queues during import but does not start the queues. At the end of the import, you must manually start the queues:

exec dbms_aqadm.start_queue(queue_name => ... );

Be sure to start all queue:

select owner, name, queue_table, enqueue_enabled, dequeue_enabled 
from   dba_queues;

This behavior is intentional. During a migration, you typically don’t want to use the queues. Depending on your use case, it can have unintended side effects. After the migration, when you confirm a successful migration and are ready to go live, you can manually start the queues.

If you forget to start the queues, your application will start to receive the following error:

ORA-25207: enqueue failed, queue string.string is disabled from enqueueing
ORA-25226: dequeue failed, queue string.string is not enabled for dequeue

The Database Does Not Create All Queue Objects

Data Pump creates the queues using the Advanced Queuing administration API DBMS_AQADM:

exec dbms_aqadm.create_queue_table( ...

The database then creates the essential parts of the underlying queue infrastructure: tables, views, IOTs, etc. This should include:

  • <queue_table_name>
  • AQ$_<queue_table_name>_E
  • AQ$_<queue_table_name>_I
  • AQ$_<queue_table_name>_T
  • AQ$_<queue_table_name>_F

In the source database, depending on your configuration and use of Advanced Queueing, you might also see the following objects:

  • AQ$_<queue_table_name>_C
  • AQ$_<queue_table_name>_D
  • AQ$_<queue_table_name>_G
  • AQ$_<queue_table_name>_H
  • AQ$_<queue_table_name>_L
  • AQ$_<queue_table_name>_P
  • AQ$_<queue_table_name>_S
  • AQ$_<queue_table_name>_V

This is intentional. The database creates those objects when needed. You should never create them or otherwise touch the underlying queue objects.

Typically, as part of a migration, you compare the count of objects in the source and target database to ensure nothing is lost. This comparison must consider the above.

For queue tables, it is sufficient to compare using the following query:

SQL> select count(*) 
     from dba_objects
     where owner='<schema>' 
           and object_type = 'QUEUE';

Definition Of Queue Tables Might Change

As stated, you should not worry about the underlying queue objects. However, if you compare the underlying queue objects, you might see that the definition of the object changes. I describe this situation in a different blog post.

Migrate Old Queues

If the queues are REALLY old, the queue compatibility setting might also be old. If so, I strongly recommend migrating the old queues to the newest version. You can learn more about that in this blog post.

Appendix

Further Reading

How to Upgrade to Oracle Database 19c and Migrate to a PDB Using Refreshable Clone PDBs

At the recent Oracle DatabaseWorld at CloudWorld I spoke to several customers that had to upgrade to Oracle Database 19c and convert their non-CDB into the multitenant architecture.

Here is how to do it using Refreshable Clone PDBs.

My source database is:

  • A non-CDB
  • On Oracle Database 12.2 or newer

I want to:

  • Upgrade to Oracle Database 19c
  • Convert the database to a PDB
  • Plug it into an existing CDB

The Problem With PDB Conversion

The conversion to multitenant does not offer the same rollback options as an upgrade. Normally, when you upgrade a database, you rely on Flashback Database as the primary rollback option. However, that does not work for conversion to multitenant.

When you plug your non-CDB into a CDB, the CDB makes changes to the data file headers. Those changes are irreversible and prevents you from ever using those data files in a non-CDB. Not even Flashback Database can revert the changes.

So, what are your rollback options?

  • Restore a backup It might take longer than your organization can accept.
  • Make a copy of the data files before conversion It requires disk space and a longer downtime window to copy the data files.

This is where Refreshable Clone PDBs come into play.

Refreshable Clone PDBs

Here is an overview of what AutoUpgrade does for you:

Overview of the process

  1. AutoUpgrade creates a PDB in the target CDB as a refreshable clone PDB of the source non-CDB.
  2. The target CDB starts to copy the data files from the source non-CDB.
  3. The target CDB refreshes the PDB. In other words, it rolls forward the data files using the redo from the source non-CDB.
  4. Now, downtime starts. AutoUpgrade issues a final refresh to bring over the latest changes.
  5. AutoUpgrade disconnects the refreshable clone PDB from its source. Now, the PDB is a real, stand-alone PDB. AutoUpgrade upgrades the PDB and converts it into a proper PDB.

If something happens during the upgrade or conversion and you want to roll back, simply start the original non-CDB. It is left completely untouched.

You can learn about the concept in detail in our AutoUpgrade 2.0 webinar:

Refreshable clone PDBs does not work for cross-endian migrations (like AIX to Linux), but cross-platform should work fine (like Windows to Linux).

How To

  1. In the source non-CDB, I create a user:
    create user dblinkuser identified by ... ;
grant create session, 
   create pluggable database, 
   select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;
  2. In my target CDB, I create a database link connecting to my source non-CDB:
    create database link clonepdb 
   connect to dblinkuser identified by ...
   using 'source-db-alias';
    You can drop the database link after the migration.
  3. I create an AutoUpgrade config file called noncdb1.cfg:
    upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.sid=NONCDB1
upg1.target_cdb=CDB1
upg1.source_dblink.NONCDB1=CLONEPDB 600
upg1.target_pdb_name.NONCDB1=PDB1
upg1.start_time=25/09/2023 06:30:00
    • source_home and target_home is the Oracle Home of the source non-CDB and target CDB respectively.
    • sid is the source non-CDB that I want to upgrade and convert.
    • target_cdb is the CDB into which I want to plug in the non-CDB. You must create the CDB in advance or use an existing one.
    • source_dblink has the name of the database link (CLONEPDB) and the rate at which the target CDB brings over redo and rolls forward the copy (600 seconds or 10 minutes).
    • target_pdb_name specifies that I want to rename the non-CDB to PDB1 when I plug it in. You can leave this out if you want to keep the name.
    • start_time specifies when downtime starts. At this point, AutoUpgrade refreshes the PDB for the last time and then moves on with upgrade and PDB conversion.
  4. Start AutoUpgrade in analyze mode on the source system:
    java -jar autoupgrade.jar -mode analyze -config noncdb1.cfg
  5. Run AutoUpgrade in fixups mode on the source system:
    java -jar autoupgrade.jar -mode fixups -config noncdb1.cfg
    • This runs the fixups identified by AutoUpgrade in analyze mode. You can run this task even after you start AutoUpgrade in deploy mode. Just ensure that the fixups complete before the final refresh (as specified in the start_time paramter).
  6. If there are no errors found in the analysis, I start AutoUpgrade in deploy mode:
    java -jar autoupgrade.jar -mode deploy noncdb1.cfg
    • AutoUpgrade copies the data files over the database link.
    • Rolls the copies of the data files forward with redo from the source non-CDB.
    • At one point, issues a final refresh and disconnects the PDB from the source non-CDB.
    • Upgrades and converts the database to a PDB.

Here’s a demo of it:

Words of Caution

Disconnect Users from Source Database

Right before the upgrade and conversion starts, AutoUpgrade executes a final refresh. The last redo from the source non-CDB is applied to ensure no data is lost. You must ensure that no users are connected to the source non-CDB after this time. Otherwise, that data will be lost.

AutoUpgrade starts the final refresh at the start time specified in the config file:

upg1.start_time=25/09/2023 06:30:00

You must be careful about disconnecting users from the source non-CDB. Remember, AutoUpgrade connects to the source non-CDB over a database link as a regular user (not SYS). This means the listener must be available, and you can’t enable restricted session or similar means.

Data Guard

If the target CDB is protected by Data Guard, special attention is needed to handle the standby databases. I explain the details in our AutoUpgrade 2.0 webinar:

Redo

The procedure relies on redo from the source non-CDB. Ensure that redo is kept in the Fast Recovery Area of the source non-CDB until it has been applied on the target PDB. Either postpone your archive backups or change the archive log deletion policy so the archive logs remain on disk.

Final Refresh

Check this blog post if you want to be in control over when the final refresh happens.

Services

You must recreate the services used in your connect strings.

Appendix

Further Reading

Honey, I Shrunk the Database! Why Is My Database Smaller after Migration?

In a recent migration, a customer noticed that the size of the database decreased dramatically after the import. The size of the data files went from 500 GB to only 300 GB. Data Pump didn’t report any errors. Nevertheless, the customer was afraid that data was lost during migration.

The customer asked:

Am I missing data?

Why Would a Database Shrink During Import?

First, the following applies to Data Pump imports. If you migrate with transportable tablespaces or Data Guard, things are different.

Fragmentation

If you perform a lot of DML on a table, it will become fragmented over time. A fragmented table will use much more space, because the database will only re-use a data block with free space, once the free space reaches a certain limit (PCTUSED). Often, this leads to blocks that are never filled and waste of space.

When you import, the database neatly packs all blocks, and the table is completed defragmented.

Depending on the nature of your DML statements, you can see a dramatic impact on space usage for a defragmented table.

Indexes

After importing the data, Data Pump rebuilds the indexes. In many cases, an index has some level of fragmentation, but a rebuild removes all that, resulting in a neatly packed and efficient index. Most often, an index rebuild ends up using less space.

PCTFREE

If you change PCTFREE for an existing object, it applies to new blocks only. The existing blocks are left untouched. Any space saving from the lower setting applies to new data blocks only.

However, during an import (or index rebuild) the database builds the object from scratch and applies the setting to all the blocks. Now, all blocks have the new settings.

Connor’s Words

Connor McDonald was part of the original conversation with the customer. He phrased it like this:

Create a 100 GB table, and a 1 GB table. Your database is now (at least) 101 GB. Drop the 100 GB table and do an export/import. You now have a 1 GB database.

How Can You Know No Data Is Lost?

If you can’t use the database’s size to indicate whether data is lost, what do you do?

We covered this in one of our webinars, Migrating Very Large Databases.

Can the Database Become Bigger?

The headline of this blog post is a reference to Honey, I shrunk the kids. The sequel is called Honey, I Blew Up the Kid.

Then, can the database become bigger as part of an import? Yes, it may.

  • If you move from a singlebyte character set to Unicode, then characters stored in your database may take up more space. In particular, LOBs now take up much more space.
  • PCTFREE setting of your segments may also cause them to increase in size. Opposite of the case above.