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

Imagine upgrading your database with the export-import method, and getting hundreds of errors on the import.

Why would that happen?

You review the import log, and notice errors on user creation, some create table errors, and many grants errors at the end. When you check, the last line of the import log, reports that your import completed with 995 errors. This is very discouraging.

Why is this happening and what can you do to prevent it?

First of all, the user creation can fail due to multiple reasons: the user’s default tablespace or the user’s temporary tablespace or the user’s profile is missing. A table creation can fail due to the owner not getting created in the first place, the table’s tablespace is missing, or the owner has no quota on the tablespace. The grant errors usually happen on the role grants, because the roles are missing.

To avoid all of these errors, there are solutions: create the tablespaces, roles and profiles AHEAD of time!

In the first part of this mini-series we looked at the steps to consider when upgrading the database. Last week we looked at the scripts to determine the character set, the installed components and memory size of the source database. Today we are going to look at generating the DDL for tablespaces, roles, profiles, public synonyms and db links!

Once you generate the DDL for the tablespaces, roles and profiles in the source database (the old version), you save these into a script (spool the output to a file). Then, you review the scripts and edit them, especially the tablespace DDL. You want to ensure the file location and file sizes are valid in the new database. Also this could be an opportunity to resize some tablespaces, especially the ones that have lots of free space allocated to them. I recommend when you create the tablespaces in the new database, to start with a small size 100M, and have auto extend on. This way the file will grow as much as needed during the import.
Once your scripts are good, you can run them in the new, target, database, prior to the import and create the tablespaces, roles, profiles.
After that you are all set to start the import!

The public synonyms and the public db links should be applied after the import, as these will not come across during the import. The best way to extract the db links is through export datapump.

See scripts below for getting the DDL. You will need to replace the tablespace names, the role names, and profile names to the ones that you want to extract.

SET LONG 20000 
SET LONGCHUNKSIZE 20000 
SET PAGESIZE 0 
SET LINESIZE 1000 
SET FEEDBACK OFF 
SET VERIFY OFF 
SET TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

--extract tablespaces. Replace TBS1, TBS2

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
FROM   dba_tablespaces
WHERE  upper(tablespace_name) in ('TBS1','TBS2')
--role DDL

--extract roles. Replace ROLE1, ROLE2

select dbms_metadata.get_ddl('ROLE', role) AS ddl
from   dba_roles 
where  upper(role) in
( 'ROLE1','ROLE2')

--role grants
select 'grant ' || granted_role || ' to ' || GRANTEE || ';' 
from dba_role_privs 
where grantee in 
('ROLE1','ROLE2');
order by granted_role;

--system grants to roles
select ' grant ' || privilege || ' to ' || grantee || ';'  as system_grants
from dba_sys_privs where grantee in ('ROLE1','ROLE2');

--extract profile DDL
select dbms_metadata.get_ddl('PROFILE', profile) as profile_ddl
from   (select distinct profile from   dba_profiles)
where  upper(profile) in ('DEFAULT');

--extract public synonyms
select 'create or replace public synonym ' || SYNONYM_NAME || 
' for ' || table_owner || '.' || table_name || ' ;'
from dba_synonyms 
where owner='PUBLIC' 
and table_owner in ('USER1','USER2')
order by table_owner, synonym_name;

--extract db links through expdp; parameter file
expdp_public_db_links.par

DIRECTORY=UPG_DIR
REUSE_DUMPFILES=YES
DUMPFILE=expdp_public_db_links.dmp
LOGFILE=expdp_public_db_links.log
SCHEMAS=PUBLIC
INCLUDE=DB_LINK

This completes this mini series!

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!