New Version of AutoUpgrade

Our developers just published a new version of AutoUpgrade. Head over to My Oracle Support and download version 21.1.1.

The version says 21, but it can still upgrade your databases to lower release. So, you should use this new version to upgrade to Oracle Database 19c as well.

The supported target Oracle Database versions that are supported by the new version of AutoUpgrade

What’s New

A total of 48 enhancements and bug fixes made it into the new release. It has been 35 working days since the last release (and that includes Thanksgiving week) which means that the team has put in – on average – close to 1,5 changes into AutoUpgrade every, single working day. I find that quite impressive. And it really supports our message that you should always use the latest version of AutoUpgrade.

In My Oracle Support note 2485457.1 you can find a complete change log. Also, you can also visit the 21c New Feature Guide for additional information.

Unplug/plug Upgrade

The first things that I would like to highlight is unplug/plug upgrade. Instead of upgrading an entire CDB, you can now choose to unplug one or many PDBs, plug them into a higher release CDB, and upgrade the individual PDBs.

How an unplug/upgrade works of an Oracle Database PDB

How do you specify to upgrade only one PDB? Use target_cdb and pdbs?

upg1.sid=CDB1
upg1.pdbs=PDB3
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.target_cdb=CDB2

When doing unplug/plug upgrades you must be aware of the COMPATIBLE setting in the higher release CDB. If COMPATIBLE is higher, when the PDB plugs in, COMPATIBLE will silently and automatically be raised by the CDB. This means that you lose the capability of doing downgrades and flashback database.

You can read more about unplug/plug upgrades in the documentation

AutoUpgrade and Oracle Data Guard

In addition, AutoUpgrade now detects that you are upgrading a primary database in a Data Guard setup. In that case, it will automatically defer redo log transport to the standby databases and disable the Data Guard broker. After the upgrade, the broker is automatically restarted, but you need to manually reconfigure the standby databases, and eventually re-enable redo log transport.

You might ask: Why don’t AutoUpgrade handle it all for me? This is to preserve your fallback capabilities. Even after a successful upgrade, you still might want to revert back to the old version. Let’s say your testing finds a critical issue. You might still have a restore point on the primary database, but what if – for some reason – flashing back fails? Then you still have your standby database which was disabled right before the upgrade.

You can read more about AutoUpgrade and Data Guard in the documentation.

More

A few hints for the interested reader to explore. Have a look at the config file option catctl_options. You can use that to control the level of parallelism for a specific upgrade.

Also, if you don’t want AutoUpgrade to handle your network files (e.g. sqlnet.ora, tnsnames.ora) you disable it using manage_network_files.

You can read more about those options in the documentation.

Conclusion

Get the latest version of AutoUpgrade and benefit from the many new features. Keep an eye out for additional blog posts that will dig deeper into the new features.

Is AutoUpgrade resumable?

Short answer: Yes!

Recently, I have been asked a few similar questions:

  • What happens if my SSH session with AutoUpgrade is lost? (see appendix)
  • What happens if AutoUpgrade crashes?
  • What happens if I exit the console by mistake?

First, don’t panic. Second, just restart AutoUpgrade using the same command line. During startup, AutoUpgrade will figure out that it should recover the lost session, and will restart the upgrades.

AutoUpgrade can automatically detect a previous session, and restart from where it left

When AutoUpgrade dies or is terminated, the database upgrades that it started, dies with it. This could happen if you lost your SSH session. The database upgrade stops, but the database is still running, most likely in UPGRADE mode. If you exit AutoUpgrade by mistake (typing exit in the job console), it will first stop the upgrade, and then shutdown the database. In any case, when you afterwards restart AutoUpgrade, it will figure out that a previous AutoUpgrade session was running. It will recover information from the previous session, and if needed restart the database. After that, it will restart the upgrade. If the previous database upgrade was at phase 54, AutoUpgrade will restart from phase 54. This means that all previous work in the upgrade is preserved, and you can resume as if nothing had happened.

Don’t Recover Previous Session

If you for some reason don’t want AutoUpgrade to recover the previous session. Let’s imagine that AutoUpgrade crashed, and you decided to restore the database. Now you want to start all over. In that case, you need to clear the recovery data, otherwise, AutoUpgrade will get confused.

You can read more about the parameters in the documentation.

The Little Hammer (Preferred)

You can clear the recovery for a specific job by adding clear_recovery_data on the command line and use jobs parameter to specific exactly for which jobs recovery data must be cleared.

$ java -jar autoupgrade.jar -config PROD.cfg -mode analyze -clear_recovery_data -jobs 100,101,102

Now, AutoUpgrade will start right from the beginning again but only for the specified jobs.

The Big Hammer

If you don’t specify jobs parameter then AutoUpgrade will clear recovery data for all jobs:

$ java -jar autoupgrade.jar -config PROD.cfg -mode analyze -clear_recovery_data

Be advised, that this will happen for all the upgrades that are specified in the config file. Remember, that one of the big benefits of AutoUpgrade is that one config file can be used to upgrade 10s or 100s of databases.

The Sledgehammer

I would recommend the previous hammers but use this approach as the last solution: Delete all files that are used by AutoUpgrade.

First, delete the directory specified in global.autoupg_log_dir. Next, delete the directory specified in <prefix>.log_dir. Typically and by default, the second directory is a subdirectory to the first one, so in most cases you just have to delete the first directory. If you have multiple upgrades specified in the same config file you potentially need to delete multiple directories for prefix1.log_dir, prefix2.log_dir and so forth.

Be aware that you are clearing out all information that is used by AutoUpgrade. If you use the same global logging directory for multiple AutoUpgrade sessions (which I would not recommend), then you will be seriously messing things up. But if you are only upgrading this specific database on the server, then you can safely delete the directories to start all over.

Restoring a Test Database – Starting All Over

Very often a test database is upgraded multiple times. Even after a successful upgrade, you might want to retry the upgrade with different settings. If you use AutoUpgrade you must clear the recovery data as specified above. AutoUpgrade doesn’t know that you have restored the database. For all it know, the previous upgrade was successful.

Conclusion

Resuming an AutoUpgrade session is very simple. Just start AutoUpgrade with the same command line. It identifies the previous AutoUpgrade session, and resumes automatically. All the previous work is recovered, and the upgrade will resume from where it was stopped.

Appendix

Lost SSH Session

I heard from several people that they experienced the SSH session timing out because AutoUpgrade didn’t produce any screen output while the upgrade took place. We have put into a our plans to make some sort of regular screen output, so this should be avioded.

Before it is implemented, I would suggest that you look at the keep alive options in SSH:

$ man ssh

Personally, I always start SSH this way, and you can put it into your SSH config:

ssh -o ServerAliveInterval=300

How to Migrate a Database Using Full Transportable Export Import and Incremental Backups

These steps will guide you through a migration of a database using Full Transportable Export Import (FTEX) and incremental backups. I covered the concept in a previous blog post, which you should read to understand the basics. Remember Transportable Tablespaces and Full Transportable Export/Import requires Enterprise Edition.

My demo environment looks like this: Overview of demo environment for migrating using FTEX and incremental backups

I have an 12.1.0.2 database that I want to migrate to a PDB in a new CDB that runs 19c.

Check Prerequisites

Create a new PDB called SALES in the target CDB:

TARGET/CDB1 SQL> create pluggable database sales admin user admin identified by admin;
TARGET/CDB1 SQL> alter pluggable database sales open;
TARGET/CDB1 SQL> alter pluggable database sales save state;

Prepare the database to use TDE Tablespace Encryption:

TARGET/CDB1 SQL> alter session set container=sales;
TARGET/CDB1 SQL> administer key management set key force keystore identified by <keystore-pwd> with backup;

Verify SQL*Net connectivity from source host to target PDB:

[oracle@source]$ sqlplus system@<target ip>/<pdb-service-name>

Verify database character set and national character set are the same:

SOURCE/SALES SQL> select property_name, property_value from database_properties where property_name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

TARGET/SALES SQL> select property_name, property_value from database_properties where property_name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Ensure the source database is in ARCHIVELOG mode:

SOURCE/SALES SQL> select log_mode from v$database;

Enable block change tracking on source database. Requires Enterprise Edition (on-prem), DBCS EE-EP (cloud) or Exadata. Although strictly speaking not required, it is strongly recommended:

SOURCE/SALES SQL> select status, filename from v$block_change_tracking;
SOURCE/SALES SQL> alter database enable block change tracking;

Ensure that you can connect from the source to the target host as oracle:

[oracle@source]$ ssh <target ip> date

Identify Tablespaces

Identify all the tablespaces that you will migrate. With FTEX you should transport all the tablespaces, except those that contain Oracle maintained data, like SYSTEM, SYSAUX, UNDO and so forth:

SOURCE/SALES SQL> select tablespace_name from dba_tablespaces;

Save the list of tablespaces for later. In my demo, I only have the tablespace SALES except the Oracle maintained ones.

Next, on the target database ensure that any of the existing tablespaces doesn’t conflict with the ones you are transporting:

TARGET/SALES SQL> select tablespace_name from dba_tablespaces;

If there is a conflict of names, you have to drop or rename the tablespaces in the target database.

Download and Configure Perl Scripts

Create a folder to hold the perl scripts, download the scripts from MOS doc ID 2471245.1, and unzip:

[oracle@source]$ rm -rf /home/oracle/xtts
[oracle@source]$ mkdir /home/oracle/xtts
[oracle@source]$ cd /home/oracle/xtts
[oracle@source]$ --Download file from MOS
[oracle@source]$ unzip rman_xttconvert_VER4.3.zip

Create a working directory (aka. scratch location) which will hold the backups. Ensure that you have enough space at this location at both source and target database.

[oracle@source]$ rm -rf /u01/app/oracle/xtts_scratch
[oracle@source]$ mkdir -p /u01/app/oracle/xtts_scratch

Create the same location on the target host:

[oracle@target]$ rm -rf /u01/app/oracle/xtts_scratch
[oracle@target]$ mkdir -p /u01/app/oracle/xtts_scratch

Configure your migration in xtt.properties. In this demo the file looks like this:

tablespaces=SALES
platformid=13
src_scratch_location=/u01/app/oracle/xtts_scratch
dest_scratch_location=/u01/app/oracle/xtts_scratch
dest_datafile_location=+DATA
asm_home=/u01/app/19.0.0.0/grid
asm_sid=+ASM1
parallel=4
rollparallel=4
getfileparallel=4
metatransfer=1
dest_user=oracle
dest_host=<target_ip>
desttmpdir=/u01/app/oracle/xtts_scratch
srcconnstr=sys/<password>@sales
destconnstr=sys/<password>@newsales
usermantransport=1

A little explanation:

  • platformid is set to 13 because this is a Linux migration. You can get the number by querying v$transportable_platform.
  • Adjust the parallel options according to the capabilities of the source and target system.
  • When you are using ASM disk group in dest_datafile_location you must also set asm_home and asm_sid.

Finally, copy the scripts (and the configuration) to your target system:

[oracle@source]$ scp -r /home/oracle/xtts/ <target_ip>:/home/oracle/

Initial Backup and Restore

Now, you can start the first initial backup of the database. You take it while the source database is up and running, so it doesn’t matter if the backup/restore cycle take hours or days to complete:

[oracle@source]$ export TMPDIR=/home/oracle/xtts
[oracle@source]$ cd /home/oracle/xtts
[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

The perl script has been configured in such a way that it automatically transfers the backups to the target system. In addition to that, a small text file must be transferred as well:

[oracle@source]$ scp res.txt oracle@<target_ip>:/home/oracle/xtts

Now, on the target system, you can restore the backup that was just taken. If needed, the data files are automatically converted to the proper endian format. If conversion is needed, you need space for a copy of all the data files:

[oracle@target]$ export TMPDIR=/home/oracle/xtts
[oracle@target]$ cd /home/oracle/xtts
[oracle@target]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

Incremental Backup and Restore

You can – and should – run the incremental backup and restores as many times as possible. The more frequent you run them, the faster they will run because there will be fewer changes. At least, close to the migration downtime window starts you should run them often, to minimize the time it will take to perform the final backup and restore:

[oracle@source]$ export TMPDIR=/home/oracle/xtts
[oracle@source]$ cd /home/oracle/xtts
[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

Transfer res.txt:

[oracle@source]$ scp res.txt oracle@<target_ip>:/home/oracle/xtts

And restore on the target system:

[oracle@target]$ export TMPDIR=/home/oracle/xtts
[oracle@target]$ cd /home/oracle/xtts
[oracle@target]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

Final Incremental Backup and Restore

Now downtime starts! Set the tablespaces read-only:

SOURCE/SALES SQL> alter tablespace SALES read only;

Perform the final incremental backup:

[oracle@source]$ export TMPDIR=/home/oracle/xtts
[oracle@source]$ cd /home/oracle/xtts
[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup

You will receive an error because the tablespace is read-only. This is ignorable: This error is ignorable because the tablespace was set read-only on purpose

Transfer res.txt:

[oracle@source]$ scp res.txt oracle@<target_ip>:/home/oracle/xtts

And restore on the target system:

[oracle@target]$ export TMPDIR=/home/oracle/xtts
[oracle@target]$ cd /home/oracle/xtts
[oracle@target]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore

Import Metadata Using FTEX

Create a directory object that points to the xtts folder:

TARGET/SALES SQL> create directory LOGDIR as '/home/oracle/xtts';

Next, create a database link to the source database that can be used to import the metadata. If the source database is already a PDB, ensure that the database link points directly into the PDB:

TARGET/SALES SQL> create public database link SRCLNK connect to system identified by <password> using '//<source_ip>:1521/<service_name>';

Test that it works:

TARGET/SALES SQL> select * from dual@srclnk;

Next, create a par file (sales_imp.par) that you can use for the Data Pump import (see appendix below for explanation):

network_link=SRCLNK
full=y
transportable=always
metrics=y
logtime=all
exclude=TABLE_STATISTICS,INDEX_STATISTICS
exclude=SYS_USER
exclude=TABLESPACE:"IN('TEMP')"
exclude=SCHEMA:"IN('SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')"
directory=logdir
logfile=sales_imp.log
transport_datafiles='+DATA/CDB1_FRA2VD/B2D617FCB79B0684E053AF01000A6DCE/DATAFILE/SALES.281.105552609'

Start Data Pump and perform the import. newsales is a TNS alias that points into the SALES PDB in the target CDB. If you have encrypted tablespaces, you should use the option encryption_pwd_prompt. It allows you to input the TDE password. It can be omitted if there are no encrypted tablespaces.

$ impdp system@newsales parfile=sales_imp.par encryption_pwd_prompt=yes

Once the import has completed, you should examine the Data Pump log file for any critical errors. Check the appendix (see below) for ignorable errors:

[oracle@target]$ vi /home/oracle/xtts/sales_imp.log

That’s it! Your data has been migrated. Now would be a good time to:

  • Test your application.
  • Start a backup.
  • Gather statistics – they were excluded from the export.
  • Drop the database link that points to the source database.
  • Cleanup the file system:
    • /home/oracle/xtts
    • /u01/app/oracle/xtts_scratch

Conclusion

Even huge, TB-sized, databases can be migrated with very little downtime by using incremental backups. By using the perl script from My Oracle Support and combined with Full Transportable Export/Import it is a simple process. In addition, you can even migrate to a new endian format, to a higher release and into a PDB in one operation. It requires Enterprise Edition and you must have plenty of disk space – potentially twice the size of your database.

There is a video on our YouTube channel that you can watch. It demos the entire process. I suggest that you subscribe to our channel and get notified whenever there are new videos.

Thanks to my good colleague, Robert Pastijn, for supplying a runbook that was used as inspiration.

Appendix

If Source Database Is in OCI and Automatic Backup Is Enabled

If the source database is running in OCI and you have enabled automatic backup, you must make a few changes.

In xttprep.tmpl around line 319 change:

cp('backup for transport allow inconsistent ' ||

to

cp('set encryption off for all tablespaces;set compression algorithm "basic";backup for transport allow inconsistent ' ||

In xttdriver.pl around line 4268 change:

my $rman_str1 = "set nocfau;";

to

my $rman_str1 = "set nocfau;".
                "set encryption off for all tablespaces ;".
                "set compression algorithm 'basic' ;" ;

ORA-02085

If you get ORA-02085 when querying over the database link:

TARGET/SALES SQL> alter system set global_names=false;

Data Pump Parameters

Use network_link to specify the name of the database link that points back to the source database.

full=y and transportable=always instructs Data Pump to perform a full transportable export/import.

exclude=TABLE_STATISTICS,INDEX_STATISTICS exclude statistics from the import. It is better and faster to gather new, fresh statistics on the target database. If you insist on importing your statistics, you should use DBMS_STATS.

exclude=SYS_USER excludes the import of the SYS user. In a PDB that is not even allowed, and most likely you are not interested in importing the definition of the SYS user.

exclude=TABLESPACE:"IN('TEMP')" excludes the temporary tablespace from the import. Most likely there is already a temporary tablespace in the new, target PDB. It is faster to create a TEMP tablespace in advance – and name it the same as in the source database.

A change was made to Spatial in 19c and some Spatial admin users are removed. To avoid errors/noise in the log file you can safely exclude them from the import by specifying exclude=SCHEMA:"IN('SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')".

transport_datafiles is used to specify the data files that make you the tablespace you are transporting. Specify the parameter multiple times to specify more data files. You can use asmcmd to get the data file paths and names.

Data Pump Ignorable Errors

Multimedia desupported in 19c, but code is still there. You can safely disregard this error:

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
ORA-39342: Internal error - failed to import internal objects tagged with ORDIM due to ORA-00955: name is already used by an existing object.

Package is removed in 12.2. See ORA-39083 And ORA-04042 Errors On DBMS_DEFER_SYS When Importing Into 12.2 Database (Doc ID 2335846.1):

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:ORA-04042: procedure, function, package, or package body does not exist

Failing sql is:
BEGIN
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON DBMS_DEFER_SYS TO "DBA"');COMMIT; END;

Upgrade & Plug In: With ASM, Data Guard, TDE and no Keystore Password

I was helping a customer the other day together with Mike. They were upgrading from 18c to 19c and had to convert the database to a PDB as well. At first glance, it seemed pretty straightforward, but things got complicated because:

  • They have standby databases and want the Data Guard setup to survive the plug-in operation.
  • They are using ASM.
  • They are using TDE Tablespace Encryption and have also encrypted their SYSTEM and SYSAUX tablespace.
  • The DBA that will carry out the upgrade and plug-in is not allowed to have the TDE Keystore password. They have separation of duties, so only the security admins have the keystore password.

Can you do that? Yes, you can! Let me tell you how.

Upgrade

First, upgrade the database. You can easily maintain the Data Guard setup during an upgrade. I wrote a blog post about a little while ago. In addition, to upgrade a database with encrypted tablespaces you don’t need the keystore password. You must configure the database to use an auto login keystore, and that’s it. If you are concerned about the use of an auto-login keystore, you can simply remove it again after the upgrade.

External Store for a Keystore Password

The plug-in operation will require the keystore password. But the DBA doesn’t know it – so we need to find a solution for that. The solution is to store the keystore password in an external store. I also wrote a blog post about that a while ago. When you have it configured you can exchange the commands that require a keystore password, like:

SQL> ADMINISTER KEY MANAGEMENT ... KEYSTORE IDENTIFIED BY "S3c3tPassw0rd";

With this:

SQL> ADMINISTER KEY MANAGEMENT ... KEYSTORE IDENTIFIED BY EXTERNAL STORE;

The database will get the keystore password from an external store, which is basically a file in the file system which is encrypted with a password that only the database know.

The security admins would need to do this in the CDB that will receive the non-CDB database. They can do it in advance, so they can relax while the DBA carries out the operation in a maintenance window. If the TDE keystore is already configured using the WALLET_ROOT parameter, you can use the feature right away. Otherwise, you need a database restart to configure it.

Like with the auto-login keystore, if you are concerned about the security, you can simply disable it again after the operation.

Plug In

Now things get complicated. When you plug in your non-CDB database the manifest file contains information on where the data files are located – but only on the primary database. This is an extract of a manifest file (the one you create with DBMS_PDB.DESCRIBE):

<PDB>
  ...
  <tablespace>
    <name>SYSTEM</name>
    ...
	<file>
      <path>+DATA/SALES1/DATAFILE/system.311.1058127529</path>

After plug-in, the CDB can start to use the data files right away. It reads from the manifest files where the data files are located. But there is no information on where files are located on the standby database. To overcome this you must create aliases in the ASM instance on the standby host. The aliases will point back to the original data files (used by the standby database). So, when the plug-in happens and redo start to flow to the standby database, it will know which data files to recover. If you are storing data files in a regular file system, you could use soft links to serve the same purpose.

The procedure is already very well described:

I won’t repeat the procedure as the above articles are really good. But these articles don’t consider the situation where your SYSTEM and/or SYSAUX tablespace is encrypted.

If that is the case, you must import your encryption keys into CDB$ROOT before you execute the CREATE PLUGGABLE DATABASE command. In Reusing the Source Standby Database Files When Plugging a non-CDB as a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273304.1) it should happen right before step 17.2.2:

SQL> alter session set container=CDB$ROOT;
SQL> administer key management import keys ... keystore identified by external store ... ;
SQL> --Continue with step 17.2.2
SQL> create pluggable database .... ;

Dots and Underscores

When you follow the MOS notes you might wonder why the dots in the ASM aliases are replaced with underscores. At first glance, I had no idea, but it worked. I later learned the following:

The format for an ASM filename is [filetype|tablespacename].[ASM file number].[file incarnation], but basically it is three pieces of name separated by periods. We can’t create any filename or alias that mimics that format. So the scripts change those periods to underscores (‘_’). That is allowed.

ORA-15032 and ORA-15046

Most likely you get this error because there are already existing aliases on the ASM file. Only one alias is allowed per file.

  1. You can verify that by using the ls command in ASMCMD. If it is an alias the Name column will look similar to this alias1 => +DATA/......
  2. Ensure the database is not using the alias. If it does, rename the file in the database.
  3. Remove the alias from ASM. It is strongly recommended to use rmalias. Although also possible with rm I consider it much safer to use rmalias.

Conclusion

You can upgrade and convert your database to a PDB without comprising your standby database. In addition to that, you can configure your database in such a way that you don’t even need to type in the TDE keystore password.

How to Upgrade with Data Guard

You can upgrade your database to a new release, and keep the data guard setup intact. The standby database(s) can be upgraded implicitly via the redo from the primary database, and there is no need to rebuild the standby database after upgrade.

The process: Overview of upgrade with a data guard

In the following I will be using this setup: Overview of the environment that is used for this procedure

In advance, you should install the new Oracle Home on both primary and standby host. The two Oracle Homes should have the same patches applied, and I recommend that you always apply the latest Release Update.

Before Upgrade

The procedure starts right before you run the preupgrade fixups. Downtime has started and users are no logged connected to the database.

Disable Data Guard Broker

If you don’t use Data Guard Broker, you can skip this chapter and go to the chapter Stop Data Guard. Connect to the broker and disable Fast Start Failover:

DGMGRL SYS@PROD1> disable fast_start failover

Next, you disable the broker configuration:

DGMGRL SYS@PROD1> disable configuration

Then, you can shut down the broker in the primary. Make a copy of the broker configuration files. Use the below SQL to generate commands to copy the files. Remember to execute the commands generated:

PROD1 SQL> alter system set dg_broker_start=false scope=both;
PROD1 SQL> select 'host cp ' || value || ' /tmp' as cmd from v$parameter where name like 'dg_broker_config_file%';
PROD1 SQL> --Now, execute the commands
PROD1 SQL> host ls /tmp/dr*.dat

Finally, you do the same for the standby database:

PROD2 SQL> alter system set dg_broker_start=false scope=both;
PROD2 SQL> select 'host cp ' || value || ' /tmp' as cmd from v$parameter where name like 'dg_broker_config_file%';
PROD1 SQL> --Now, execute the commands
PROD2 SQL> host ls /tmp/dr*.dat

Stop Data Guard

On the primary database, defer the redo log transport to the standby database. Strictly speaking, this is not necessary, but I do it from a "better-safe-than-sorry" aspect. Be sure to verify that log_archive_dest_state_2 is the actual archive destination for your standby database:

PROD1 SQL> show parameter log_archive_dest_2
PROD1 SQL> alter system set log_archive_dest_state_2='defer' scope=both;

Next, you cancel redo apply on the standby database:

PROD2 SQL> alter database recover managed standby database cancel;

Finally, you shut down the database:

PROD2 SQL> shutdown immediate

If you are using Grid Infrastructure (GI) to manage the database, you should stop and disable the database. Disabling the database is strictly speaking not necessary, but again a "better-safe-than-sorry" approach:

[oracle@bm2]$ $ORACLE_HOME/bin/srvctl stop database -d PROD2
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl disable database -d PROD2

Upgrade

Now you can upgrade the primary database using the method you prefer. Complete all the post-upgrade tasks and perform the necessary tests to validate the new database release.

If something happens during upgrade and you want to revert, you can flash back the database (or restore on Standard Edition) and simply undo the before upgrade steps (start by enabling database, starting database, starting redo apply and so forth).

Remember that the standby databases was left behind before we started touching anything, so if all other fails, simply restart the standby database, and connect your users to it.

After Upgrade

Restart Data Guard

When you are happy with the upgrade, and your tests validate the new database release, you can proceed.

Update the listener on the standby host. Be sure to update the Oracle Home information in the listener.ora entry. Note, that your listener.ora might be stored in a non-default location, so use lsnrctl status to get the location. Finally, reload the listener:

[grid@bm2]$ $GRID_HOME/bin/lsnrctl status
[grid@bm2]$ vi $GRID_HOME/network/admin/listener.ora
[grid@bm2]$ $GRID_HOME/bin/lsnrctl reload

For the next commands, I will be used the same prompt, and I will need the following environment variables:

[oracle@bm2]$ export OLD_HOME=/u01/app/oracle/product/18.0.0.0/dbhome_1
[oracle@bm2]$ export NEW_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@bm2]$ export ORACLE_HOME=$NEW_HOME
[oracle@bm2]$ export ORACLE_SID=PROD
[oracle@bm2]$ #Set ORACLE_UNQNAME to DB_UNIQUE_NAME
[oracle@bm2]$ export ORACLE_UNQNAME=PROD2 

Next, if the standby database is using TNS_ADMIN in the default location ($ORACLE_HOME/network/admin), then be sure to copy the relevant TNS aliases into the new tnsnames.ora. There should be TNS aliases to the primary and standby database. Or, if there are no other databases in the same Oracle Home, you can simply copy the files:

[oracle@bm2]$ #Back up files
[oracle@bm2]$ cp $NEW_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin/sqlnet.ora.backup
[oracle@bm2]$ cp $NEW_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin/tnsnames.ora.backup
[oracle@bm2]$ #Copy from old to new home
[oracle@bm2]$ cp $OLD_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin
[oracle@bm2]$ cp $OLD_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin

Now, you can edit /etc/oratab and update the information about the Oracle Home to match the new Oracle Home:

[oracle@bm2]$ vi /etc/oratab

Copy SPFile and password file to the new Oracle Home:

[oracle@bm2]$ cp $OLD_HOME/dbs/orapw$ORACLE_SID $ORACLE_HOME/dbs
[oracle@bm2]$ cp $OLD_HOME/dbs/spfile$ORACLE_SID.ora $ORACLE_HOME/dbs

If you are using GI to manage the database, you must upgrade the database, meaning updating the Oracle Home information, so GI will start the database in the correct Oracle Home. Next, re-enable and start the database:

[oracle@bm2]$ $ORACLE_HOME/bin/srvctl upgrade database -db $ORACLE_UNQNAME -oraclehome $ORACLE_HOME
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl modify database -db $ORACLE_UNQNAME -startoption MOUNT -role PHYSICAL_STANDBY
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl enable database -d $ORACLE_UNQNAME
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME

Or, if you are not using GI, simply start the database:

PROD2 SQL> startup mount

Re-enable Redo Log Transport and Apply

On the primary database re-enable redo log transport to standby database:

PROD1 SQL> alter system set log_archive_dest_state_2='enable' scope=both;

On the standby database restart redo apply

PROD2 SQL> alter database recover managed standby database disconnect from session;

Re-enable Data Guard Broker

First, we need to copy the broker config files into the new Oracle Home. If you store your broker config files outside of the Oracle Home this might not be necessary to you:

[oracle@bm1]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@bm1]$ export ORACLE_UNQNAME=PROD1
[oracle@bm1]$ cp /tmp/dr1$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
[oracle@bm1]$ cp /tmp/dr2$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs

Do the same on the standby database host:

[oracle@bm2]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@bm2]$ export ORACLE_UNQNAME=PROD2 
[oracle@bm2]$ cp /tmp/dr1$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
[oracle@bm2]$ cp /tmp/dr2$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs

Now, you can restart the Data Guard Broker on both primary and standby database:

PROD1 SQL> alter system set dg_broker_start=true scope=both;

PROD2 SQL> alter system set dg_broker_start=true scope=both;

Finally, enable the broker configuration and fast start failover:

DGMGRL SYS@PROD1> show configuration
DGMGRL SYS@PROD1> enable configuration
DGMGRL SYS@PROD1> enable fast_start failover

Test

Use the broker to ensure everything is fine:

DGMGRL SYS@PROD1> show configuration
DGMGRL SYS@PROD1> show database prod1
DGMGRL SYS@PROD1> show database prod2

You should have SUCCESS listed for both databases Use Data Guard Broker to verify data guard setup after upgrade

Let’s try to make a switchover:

DGMGRL SYS@PROD1> switchover to prod2

If you don’t use Data Guard Broker, you use regular SQLs and SQLPlus to verify the data guard environment.

Conclusion

It is actually not that complicated to upgrade your database, even if it is part of a data guard setup. A little extra legwork is needed to take care of the standby database. But the good thing is that your DR setup is maintained althroughout the process.

I made a video on YouTube that shows the procedure. And while you are there, I suggest that you subscribe to our channel.

Keep an eye out for coming versions of AutoUpgrade. At time of writing our developers are working on streamlining the process. We want upgrade with data guard to be 100 % automated (or as close to as possible).

Further Reading

Webinar: Upgrade Your Databases to 19c

Danish Oracle User Group - DOUG

Danish Oracle User Group is hosting a talk tomorrow on Upgrade Your Databases to 19c. I will be presenting and talk about:

Oracle Database 19c is the long term release and this talk is about how to get there. The emphasis is on the AutoUpgrade tool which is the new, recommended way for upgrades. There will be demos that describe how you can get started and the options, you have. In addition, we will cover upgrade best practices and deep insights into the process.

You need to upgrade to Oracle Database 19c and this talk will prepare you.

Time: 25 November 2020 15:00 CET/Copenhagen

The session is open to anyone, so if you are interested simply go their webpage and get the connection details. There is an English version of the text a little down the page. I will give the talk in English.

If you want to have a sneak peek on the slides, there are already published.

I hope to see you tomorrow.

Minimal Downtime Migration with Full Transportable Export Import and Incremental Backups

If you need to migrate a database to the cloud or anywhere else for that matter, you should consider using Full Transportable Export Import (FTEX) and incremental backups. Even for really large databases – 10s or 100s of TB – you can still migrate with minimal downtime. And it works across different endian formats.

FTEX uses transportable tablespaces and the solution has these benefits:

  • You can implicitly upgrade the database as part of the migration
  • You can migrate from a non-CDB and into a PDB
  • You can keep downtime at a minimum by using frequent incremental backups
  • You can migrate across endianness – e.g. from AIX or Solaris to Oracle Linux

How Does It Work

To concept is explained in this video on our YouTube Channel and it includes a demo:

To make the backup and convert process really easy, Oracle is providing a perl script that can automate the entire process. You download the scripts from My Oracle Support: V4 PERL Scripts to reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup.

What You Need to Be Aware Of

Transportable Tablespaces

To get a complete list of limitations on transporting data, you should have a look in the documentation. Also, there are some specific to transportable tablespaces. The most notable are:

  • Character set and national character set should be the same. If not, there are a few options available, however.
  • No columns can be encrypted with TDE Column Encryption. Only option is to remove the encryption before migration, and re-encrypt afterwards.
  • TDE Tablespace Encryption is supported for same-endian migration if the source database is 12.1.0.2 or newer. If you need to go across endianness, you must decrypt the tablespaces, and re-encrypt after migration. Remember in Oracle Database 12.2 you can encrypt tablespaces online.
  • If you are migrating across endianness, you must convert the data files. You must have disk space to hold a copy of all the data files. In addition, you should perform the convert on the platform that has the best I/O system and most CPUs. Typically, this is the cloud platform, which also offers scaling possibilities.
  • The database timezone and timezone file version must be identical.
  • Requires Enterprise Edition.

Full Transportable Export Import

  • Source database must be 11.2.0.3 or higher
  • Target database must be 12.1.0.1 or higher
  • It is recommended to import directly into the target database using the NETWORK_LINK option.
  • Requires Enterprise Edition.

If you can’t meet these requirements, you can still use this solution. But instead of doing a FTEX, you need to use Data Pump in another way.

Incremental Backups Using Perl Scripts

  • Source database must be 10.2.0.3 or higher
  • Target database must be 11.2.0.4 or higher

In addition, it is strongly recommended to use Block Change Tracking (BCT) on the source database. Note, that this is an Enterprise Edition feature (in OCI: DBCS EE-EP or ExaCS). If you don’t enable BCT the incremental backups will be much slower, because RMAN has to scan every single data block for changes. With BCT the database keeps track of changes in a special file. When RMAN backs up the database, it will just get a list of data blocks to include from the change tracking file.

The scripts will create a level 0 image file backup, and you must have room to accomodate this on your file system.

Conclusion

By using a combination of Full Transportable Export Import and incremental backups, you can migrate even huge databases to the cloud. And it even works for cross-endian migrations, like AIX or Solaris to Oracle Linux.

If you want to learn more about endianness and transportable tablespaces, you should watch this video on our YouTube Channel:

Update 30 November 2020

Thanks to Mark for his comment (see below). I have added some additional useful information.

Further Reading

How to Stop Hardcoding Your TDE Keystore Password

When you encrypt your databases, you will often end up needing the keystore password to perform certain operations. For instance, cloning an encrypted PDB will require the keystore password:

CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY S3cr3t;

This is not very secure because the keystore password is now visible in clear text. Further, if you have the command in a script, ansible, cron job or the like, you will also have it there in clear text. Or, if your organization has implemented separation of duties, and the operational DBAs don’t have access to the keystore password.

Enter Secure External Password Store (SEPS)

Using the example above, what if you could just write the following:

CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY EXTERNAL STORE;

And the database would get the secret keystore password without involving you! Your problems would be solved.

That is what you can do with Secure External Password Store. Whenever you need to specify the keystore password using KEYSTORE IDENTIFIED BY you can use the EXTERNAL STORE clause, and avoid typing the password.

SEPS was introduced in 10g, however, since Oracle Database 12.2 you can store keystore credentials in it. SEPS is similar to an auto-login keystore. It is an encrypted file stored in the file system that is encrypted by a password that only the database knows. You as a DBA can’t extract the information from the database.

Configuration

First, tell the database where SEPS is located:

SQL> ALTER SYSTEM 
     SET EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION = '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps'
     SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Next, add the keystore password (in this case S3cr3t) into SEPS as a secret for the client TDE_WALLET:

SQL> ADMINISTER KEY MANAGEMENT
     ADD SECRET 'S3cr3t' FOR CLIENT 'TDE_WALLET'
     USING TAG 'TDE keystore password' 
     TO LOCAL AUTO_LOGIN KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps’;

Finally, replace the keystore password in your commands with SEPS using the EXTERNAL STORE clause:

SQL> --No longer need this
SQL> CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY S3cr3t;
SQL> --Now you can do this
SQL> CREATE PLUGGABLE DATABASE ... KEYSTORE IDENTIFIED BY EXTERNAL STORE;

You can use KEYSTORE IDENTIFIED BY EXTERNAL STORE on most ADMINISTER KEY MANAGEMENT commands, like exporting and importing encryption keys, but for security reasons some ADMINISTER KEY MANAGEMENT commands still require that you type in the keystore password.

When you specify the location of SEPS, I strongly recommend that you stick to the default location, $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps. If you are also using WALLET_ROOT parameter, then SEPS must be stored in the default location.

If you are using Oracle Key Vault or a dedicated Hardware Security Module instead of a software keystore, you can store those credentials in SEPS as well. Respectively, use CLIENT set to OKV_PASSWORD or HSM_PASSWORD.

Conclusion

You can simplify commands that involve the keystore by using SEPS. In addition, it will be more secure because you can avoid typing or hardcoding the keystore password in cleartext.

I have produced a small demo that you can watch on YouTube:

If you like the video and want more, remember to hit the subscribe button on our YouTube channel.

References

Enabling Local Undo With AutoUpgrade

If you have databases that run in shared undo mode you should switch to local undo mode. Starting from 12.2 this is the default and recommended undo mode, and it offers a lot of cool functionality. Obviously, at the expense of having multiple undo tablespaces. There are already many good blog posts out there that the benefits of local undo and how to enable it.

AutoUpgrade To The Rescue

If you are planning an upgrade with AutoUpgrade, you can also enable local undo during the upgrade. You should simply just configure it in the config file:

upg1.enable_local_undo=YES

And AutoUpgrade will take care of the rest for you. It will even create undo tablespaces in PDB$SEED and all of the PDBs. Once again AutoUpgrade can make your life easier.

What Happens

Before creating the new undo tablespaces, the database will determine the attributes of the tablespace. By default, it will use a ratio of 30% compared to CDB$ROOT:

  • Initial data file size (calculated from current file size)
  • Maximum data files size (MAXBYTES)
  • Grow by (INCREMENT_BY)

Example (default ration 30 %):

Attribute CDB$ROOT PDB
Initial data file size current file size, 500M 150M
Maximum data file size 32G 9.6G
Grow by 5M 1.5M

You can control the ratio using the parameter _seed_root_undo_ratio in CDB$ROOT. To set the percentage to 10% use the following command before the upgrade:

ALTER SYSTEM SET "_seed_root_undo_ratio"=10 SCOPE=SPFILE;

With this knowledge you can now calculate the space you need for all those extra undo tablespaces. Thus, you can avoid to run out of disk space, or have the tablespace set at undesirable size.

After the upgrade, I would advice you to review the undo tablespace size per PDB. Different workload requires different amount of undo. And especially the increment_by attribute can become so low that it will lead to too frequent data file grow operations.

If your database is already in local undo mode then the parameter _seed_root_undo_ratio has no effect at all. The database will not start a grow or shrink operation to meet the ratio defined by the parameter. Once you have switched to local undo mode, you are in full control yourself.

Conclusion

Be sure to enable local undo for all CDBs databases when you upgrade to 12.2 or later releases. If you have many PDBs and you use AutoUpgrade to enable it, be aware of disk space needed for all those tablespaces. And review the settings afterwards.

Mike Dietrich wrote about a similar situation when you create CDBs using DBCA.

Resources

Move to the Cloud – Webinar

Yesterday, Mike Dietrich and I gave the final webinar in the Oracle Database 19c Upgrade Virtual Classroom series. It was about Move to the Cloud – not only for techies. Now, I say final – but we all know you should never, say never. And in this case, it applies to final as well. We are already talking about subjects for a seventh webinar. If you have any interesting topic, that you think we should cover, get in touch with me.

Oracle Database 19c Upgrade Virtual Classroom

Unfortunately, due to a technical glitch we skipped the part about migrating using transportable tablespaces and full transportable export/import. We uploaded the missing part to YouTube, so you can watch it.

For those interested, you can now download the slides. We had really much information to share, so browse through the deck to find a lot of hidden slides. Typically, there are references and links to more information about a specific topic.

Within a week it should be possible to watch a recording of the webinar.

The Demos and Videos

This presentation we gave, was a brand new one. We used as many demos and videos as we could – or rather had time to prepare. We will post them on our YouTube channel as soon as possible. I suggest that you subscribe to it, so you can receive word as soon as new contents arrives. Further, we want to enhance the presentation even more, so we will be putting in more videos and demos. Let me know, if there was a topic, that could improve with a video or demo.

Thank You

Thanks to everyone that participated yesterday. Happy migrating!