Scripts to Upgrade DB using Export/Import Datapump – Part 1

In my last blog post, I was showing you one way to upgrade a database to 19c (or to any other version), using the export and import datapump method.
I presented you with a 15 step methodology. Many of you either left a comment or replied to my email, and asked me to provide the scripts for some of these steps.

So, you asked, I listened!

Today, I will walk you through in more detail for the pre-requisites of Step#3. This step is dedicated to create a new empty database with DBCA. I am not going to walk you through those steps. What I am going to show you, is the information you will need prior to creating the new database: character set, memory sizing and installed components.

Why are these important?

Well, if you change the character set of the database, you could possibly face data loss on the import, if the source and target database are not the same character set. If you choose on purpose different character sets, then research ahead of time the implications of it.
For memory, you want to ensure that you are allocating at least if not more memory to the database as your source database has.
As for the database components, you want to confirm you install all the components that you need, and don’t install the ones that you don’t. It is always good to know what components you have in your database.

Let’s get to the scripts!

1) How do you check the character set and the national character set of a database? Look at the database_properties view.

col property_name for A25
col property_value for A20
col description for A20
set linesize 150

select * from database_properties 
where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET')

PROPERTY_NAME             PROPERTY_VALUE       DESCRIPTION
------------------------- -------------------- --------------------
NLS_NCHAR_CHARACTERSET    AL16UTF16            NCHAR Character set
NLS_CHARACTERSET          WE8MSWIN1252         Character set

2) How much memory is assigned to the database? More precisely, how big is the SGA and the PGA?

show parameter sga_max_size
show parameter sga_target
show parameter pga_aggregate_target

The safest method would be to create a copy of the spfile in a readable version, aka pfile, and review all the parameters. If you have any underscore parameters, review and confirm if they should be migrated to 19c.

create pfile='/home/oracle/init_source_db.ora' from spfile;

3) What database components are installed in the database? You can check dba_registry view. There are lots of other columns of interest in this view.

col comp_name for A35
set linesize for A150
select comp_name from dba_registry order by 1;

COMP_NAME                           STATUS
----------------------------------- -----------
JServer JAVA Virtual Machine        VALID
Oracle Database Catalog Views       VALID
Oracle Database Java Packages       VALID
Oracle Database Packages and Types  VALID
Oracle Multimedia                   VALID
Oracle Text                         VALID
Oracle Workspace Manager            VALID
Oracle XDK                          VALID
Oracle XML Database                 VALID

With this information at your fingertips, start creating your own script and deployment plan, by putting together these bits and pieces of information.

Next week we will look at generating the DDL for tablespaces, roles, profiles, public synonyms and db links. Make sure to tune in!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the September Back To School Special, only $16.99 CAD!