orawin.info

Niall's Oracle Pages – Oracle Opinion since 2004

Archive for May 13th, 2010

Refreshing a Test Database using exp/imp

with 2 comments

I’ve just taken a quick look at the Calendar and it appears to be 2010. However I’m still seeing, and being asked, to refresh a number of test databases using schema mode exports. This can present a challenge because there are a number of database objects that typically aren’t created by a schema mode import. In addition a number of the applications I see utilize various schemas and so there can be dependency issues when imports are done. I thought it might be useful therefore to set out my approach to this problem.

The list of objects that are not created by a schema mode import is as follows:

  • Tablespaces
  • Profiles
  • Database Links
  • Public Synonyms
  • Roles

In addition if the users are dropped and recreated we also need the following information

  • User information including password and tablespaces
  • Role Grants
  • System Grants
  • Object Grants

Since Oracle 9i we can take advantage of the supplied package DBMS_METADATA to obtain all this information from the source database. I therefore run the script shown below to generate 2 scripts at the source, one to be run before the schema mode import (handily called preImport.sql) and one to be run after the import has finished. This script needs to be run as a privileged user (I usually use SYSTEM). The generated scripts may need to be edited for pathnames for tablespaces if the tablespaces needed do not exist on the target. The sequence then becomes

  • drop the affected users
  • run the preImport.sql
  • run the import
  • run postImport. sql

These days however I tend to recommend moving to datapump wherever possible which has the following advantages.

  1. It’s faster
  2. It’s resumable
  3. It doesn’t require a dumpfile at all if you use the network option.

/*

Script to create sql scripts
for a user mode export to succeed

Requirements
Execute on dbms_metadata,utl_file
Create any directory

Version 1.0
Niall Litchfield 8/7/2008
based on extract_schema schema compare utility 2003.

*/

accept prescript default 'preimport.sql' prompt 'Enter Filename for preimport ddl [preimport.sql] '
accept sqldir default '/u02/support/dba/sql' prompt 'Enter directory on db server to write ddl file to 1'
accept postscript default 'postimport.sql' prompt 'Enter Filename for post import ddl [postimport.sql] '

set verify off

create or replace directory scripts
as
'&sqldir';

declare
preScriptHandle UTL_FILE.FILE_TYPE;
postScriptHandle UTL_FILE.FILE_TYPE;
DDL clob;
strDDL varchar2(32767);
line varchar2(2000);
NO_GRANT_FOUND exception;
OBJECT_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(NO_GRANT_FOUND,-31608);
PRAGMA EXCEPTION_INIT(OBJECT_NOT_FOUND,-31603);

begin
preScriptHandle := UTL_FILE.FOPEN('SCRIPTS','&prescript','w');
postScriptHandle := UTL_FILE.FOPEN('SCRIPTS','&postscript','w');

/*+ set session transforms */

DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,
'STORAGE',
false);

DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,
'SQLTERMINATOR',
true);

/*+ get tablespace details */
utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'----- Start Tablespaces -----');
utl_file.put_line(preScriptHandle,'-----------------------------');

for t in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX')
and contents not in ('UNDO','TEMPORARY')) loop
DDL := dbms_metadata.get_ddl('TABLESPACE',t.tablespace_name);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end loop;
utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------ End Tablespaces ------');
utl_file.put_line(preScriptHandle,'-----------------------------');
BEGIN

/*+ Profiles */

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------ Start Profiles -------');
utl_file.put_line(preScriptHandle,'-----------------------------');

for pr in (select distinct profile from dba_profiles) loop -- multiple rows per profile
DDL := dbms_metadata.get_ddl('PROFILE',pr.profile);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end loop;
exception
when OBJECT_NOT_FOUND THEN NULL; -- no profile information extracted.
WHEN OTHERS THEN
utl_file.fclose(preScripthandle);
utl_file.fclose(postScripthandle);
RAISE;
end;

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------- End Profiles --------');
utl_file.put_line(preScriptHandle,'-----------------------------');

/*+ Public Database Links */
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ Start db links -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

for dbl in (select db_link from dba_db_links where owner = 'PUBLIC') loop
DDL := dbms_metadata.get_ddl('DB_LINK',dbl.db_link,'PUBLIC');
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(postScriptHandle,strDDL);
end loop;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------- End db links --------');
utl_file.put_line(postScriptHandle,'-----------------------------');

/*+ Public synonyms */

utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ Start of Synms -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

for syn in (select synonym_name from dba_synonyms where owner = 'PUBLIC' and table_owner not in (
'ANONYMOUS'
, 'CTXSYS'
, 'DBSNMP'
, 'DIP'
, 'DMSYS'
, 'EXFSYS'
, 'LBACSYS'
, 'MDDATA'
, 'MDSYS'
, 'MGMT_VIEW'
, 'ODM'
, 'ODM_MTR'
, 'OLAPSYS'
, 'ORDPLUGINS'
, 'ORDSYS'
, 'OUTLN'
, 'PERFSTAT'
, 'SI_INFORMTN_SCHEMA'
, 'SYS'
, 'SYSMAN'
, 'SYSTEM'
, 'TRACESVR'
, 'TSMSYS'
, 'WKPROXY'
, 'WKSYS'
, 'WK_TEST'
, 'WMSYS'
, 'XDB'
)) loop
DDL := dbms_metadata.get_ddl('SYNONYM',syn.synonym_name,'PUBLIC');
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(postScriptHandle,strDDL);
end loop;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------- End of Synms --------');
utl_file.put_line(postScriptHandle,'-----------------------------');

/*+ Roles */
utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------ Start of Roles -------');
utl_file.put_line(preScriptHandle,'-----------------------------');

for rl in (select role from dba_roles where role not in (
'CONNECT'
, 'RESOURCE'
, 'DBA'
, 'SELECT_CATALOG_ROLE'
, 'EXECUTE_CATALOG_ROLE'
, 'DELETE_CATALOG_ROLE'
, 'EXP_FULL_DATABASE'
, 'IMP_FULL_DATABASE'
, 'LOGSTDBY_ADMINISTRATOR'
, 'AQ_ADMINISTRATOR_ROLE'
, 'AQ_USER_ROLE'
, 'DATAPUMP_EXP_FULL_DATABASE'
, 'DATAPUMP_IMP_FULL_DATABASE'
, 'GATHER_SYSTEM_STATISTICS'
, 'RECOVERY_CATALOG_OWNER'
, 'SCHEDULER_ADMIN'
, 'HS_ADMIN_ROLE'
, 'GLOBAL_AQ_USER_ROLE'
, 'OEM_ADVISOR'
, 'OEM_MONITOR'
, 'WM_ADMIN_ROLE'
, 'JAVAUSERPRIV'
, 'JAVAIDPRIV'
, 'JAVASYSPRIV'
, 'JAVADEBUGPRIV'
, 'EJBCLIENT'
, 'JMXSERVER'
, 'JAVA_ADMIN'
, 'JAVA_DEPLOY'
, 'CTXAPP'
, 'XDBADMIN'
, 'XDB_SET_INVOKER'
, 'AUTHENTICATEDUSER'
, 'XDB_WEBSERVICES'
, 'XDB_WEBSERVICES_WITH_PUBLIC'
, 'XDB_WEBSERVICES_OVER_HTTP'
, 'SPATIAL_WFS_ADMIN'
, 'ORDADMIN'
, 'OLAPI_TRACE_USER'
, 'OLAP_XS_ADMIN'
, 'OLAP_DBA'
, 'CWM_USER'
, 'OLAP_USER'
, 'WFS_USR_ROLE'
, 'SPATIAL_CSW_ADMIN'
, 'CSW_USR_ROLE'
, 'WKUSER'
, 'MGMT_USER'
, 'SPREPORT_ROLE'
, 'OWB$CLIENT'
, 'OWB_DESIGNCENTER_VIEW'
, 'OWB_USER'
, 'APEX_ADMINISTRATOR_ROLE')
)loop
DDL := dbms_metadata.get_ddl('ROLE',rl.role);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end loop;

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------- End of Roles --------');
utl_file.put_line(preScriptHandle,'-----------------------------');

/*+ User Information */
for u in
(select username from dba_users where username not in
( 'ADAMS'
, 'ANONYMOUS'
, 'BLAKE'
, 'CLARK'
, 'CTXSYS'
, 'DBSNMP'
, 'DIP'
, 'DMSYS'
, 'EXFSYS'
, 'HR'
, 'JONES'
, 'LBACSYS'
, 'MDDATA'
, 'MDSYS'
, 'MGMT_VIEW'
, 'ODM'
, 'ODM_MTR'
, 'OE'
, 'OLAPSYS'
, 'ORDPLUGINS'
, 'ORDSYS'
, 'OUTLN'
, 'PERFSTAT'
, 'PM'
, 'QS'
, 'QS_ADM'
, 'QS_CB'
, 'QS_CBADM'
, 'QS_CS'
, 'QS_ES'
, 'QS_OS'
, 'QS_WS'
, 'SCOTT'
, 'SH'
, 'SI_INFORMTN_SCHEMA'
, 'SYS'
, 'SYSMAN'
, 'SYSTEM'
, 'TRACESVR'
, 'TSMSYS'
, 'WKPROXY'
, 'WKSYS'
, 'WK_TEST'
, 'WMSYS'
, 'XDB'
) ) loop

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'-- Start of User '||u.username);
utl_file.put_line(preScriptHandle,'-----------------------------');

begin
-- GET USER info
DDL := dbms_metadata.get_ddl('USER',u.username);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end;

-- grants
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'----- Start Role Grnts ------');
utl_file.put_line(postScriptHandle,'-----------------------------');

begin
ddl := DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',u.username);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(postScriptHandle,strDDL);
exception
when NO_GRANT_FOUND THEN NULL;
WHEN OTHERS THEN
utl_file.fclose(preScripthandle);
utl_file.fclose(postScripthandle);
RAISE;
end;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ End Role Grnts -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'----- Start Sys Grnts ------');
utl_file.put_line(preScriptHandle,'-----------------------------');
begin
ddl := DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',u.username);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
exception
when NO_GRANT_FOUND THEN NULL;
WHEN OTHERS THEN
utl_file.fclose(preScripthandle);
utl_file.fclose(postScripthandle);
RAISE;
end;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ End Sys Grnts -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'-- End of User '||u.username);
utl_file.put_line(preScriptHandle,'-----------------------------');
end loop;

utl_file.fclose(preScriptHandle);
utl_file.fclose(postScriptHandle);

end;
/

Possibly Related Posts:

  • No Related Posts found

Written by Niall Litchfield

May 13th, 2010 at 9:29 am

Posted in Uncategorized

Tagged with , ,