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!
