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!