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!
