Exporting from the Source Database
To export from the source non-container database (non-CDB), complete the following steps:
Export INFRA Schemas
To export INFRA schemas from the source database:
- Sign in to the source environment. For example,
soa-host01.example.com
. - Change to the
oracle
user:sudo su - oracle
- Create a directory to store the exported files. For
example:
mkdir -p /u01/db_exp/INFRA_EXPORTS cd /u01/db_exp/INFRA_EXPORTS
- Set the
ORACLE_HOME
,ORACLE
, andPATH
environment variables. For example:export ORACLE_HOME=/opt/oracle/product/12.1.0.2/db_1 export ORACLE_SID=orclsoa export PATH=$PATH:$ORACLE_HOME/bin
- Change to the
sys
user, or any user withsysdba
privileges, and connect to the source database:sqlplus "/ as sysdba"
- Create the export directory. For
example:
CREATE DIRECTORY DUMP_INFRA AS '/u01/db_exp/INFRA_EXPORTS';
- Check if the export directory was properly
created:
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_INFRA';
- Grant
READ
andWRITE
permissions to the respective schemas:GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_IAU; GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_IAU_APPEND; GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_IAU_VIEWER; GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_MDS; GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_OPSS; GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_STB; GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_WLS; GRANT READ,WRITE ON DIRECTORY DUMP_INFRA TO DEV_WLS_RUNTIME;
- Verify that there are no connections from the
INFRA
users above in the database:SELECT username FROM v$session WHERE username IS NOT NULL ORDER BY username ASC; quit
- Change to the
oracle
user. - In the
INFRA_EXPORTS
directory, use a text editor to create a bash script to run the Data Pump Export utility (expdp
) on all schemas. For example, create a script namedexport_infra_schema.sh
with the following contents:expdp DEV_IAU/password schemas=DEV_IAU directory=DUMP_INFRA dumpfile=DEV_IAU_export.dmp logfile=DEV_IAU_export.log PARALLEL=4 expdp DEV_IAU_APPEND/password schemas=DEV_IAU_APPEND directory=DUMP_INFRA dumpfile=DEV_IAU_APPEND_export.dmp logfile=DEV_IAU_APPEND_export.log PARALLEL=4 expdp DEV_IAU_VIEWER/password schemas=DEV_IAU_VIEWER directory=DUMP_INFRA dumpfile=DEV_IAU_VIEWER_export.dmp logfile=DEV_IAU_VIEWER_export.log PARALLEL=4 expdp DEV_MDS/password schemas=DEV_MDS directory=DUMP_INFRA dumpfile=DEV_MDS_export.dmp logfile=DEV_MDS_export.log PARALLEL=4 expdp DEV_OPSS/password schemas=DEV_OPSS directory=DUMP_INFRA dumpfile=DEV_OPSS_export.dmp logfile=DEV_OPSS_export.log PARALLEL=4 expdp DEV_STB/password schemas=DEV_STB directory=DUMP_INFRA dumpfile=DEV_STB_export.dmp logfile=DEV_STB_export.log PARALLEL=4 expdp DEV_WLS/password schemas=DEV_WLS directory=DUMP_INFRA dumpfile=DEV_WLS_export.dmp logfile=DEV_WLS_export.log PARALLEL=4 expdp DEV_WLS_RUNTIME/password schemas=DEV_WLS_RUNTIME directory=DUMP_INFRA dumpfile=DEV_WLS_RUNTIME_export.dmp logfile=DEV_WLS_RUNTIME_export.log PARALLEL=4
- As the
sys
user, run the script in the source environment:@export_infra_schema.sh quit
- Export the
SCHEMA_VERSION_REGISTRY
view and its underlying tables from theSYSTEM
schema:expdp \"/ as sysdba\" SCHEMAS=system INCLUDE=VIEW:"IN('SCHEMA_VERSION_REGISTRY')" TABLE:"IN('SCHEMA_VERSION_REGISTRY$')" directory=DUMP_INFRA dumpfile=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp logfile=SYSTEM_SCHEMA_VERSION_REGISTRY.log
Note:
If you encounter an error, make sure you are using a Bash shell to run this command - In the
INFRA_EXPORTS
directory, use a text editor to create a script to export users. For example, create a script namedexport_users.sql
with the following contents:set long 10000; set lines 200; set pages 400; set longchunksize 10000; spool create_users.sql SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_IAU') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_MDS') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_OPSS') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_STB') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_WLS') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV_WLS_RUNTIME') FROM dual; spool off
- As the
sys
user, run the script in the source environment:sqlplus sys/password as sysdba @export_users.sql quit
- Edit the output file (in this example,
create_users.sql
), and verify that it contains only lines withCREATE USER
statements, then add a semicolon at the end of theCREATE
statement. For example:CREATE USER "DEV_IAU" IDENTIFIED BY VALUES 'S:7C9B0ECE4641D7409A0CBF67505591DD4E437FDDF7524FDBE61051FC3663;H:4A0E99B6CA492BF37AA2177A0D0043F3;T:093C0D3F6BA909A2B2A9077F4AC1B92B6CF59A5C6C6404B7E07CDAA652947C0593FB2EB3D7AC886D9F3164A5F98D25004E6C7A1F7277E2491F1AB7AC26AF4FEAA58561E489AB3CD17BACE090E17A2A52;38D2C7CEB79AC568' DEFAULT TABLESPACE "DEV_IAU" TEMPORARY TABLESPACE "DEV_IAS_TEMP";
- Enter the following commands to identify the tablespaces in the
INFRA
schemas:
set long 10000; set lines 200; set pages 400; set longchunksize 10000; select DISTINCT tablespace_name,owner from dba_segments WHERE OWNER like 'DEV\_%' ESCAPE '\';
The command output should look similar to the following:
- Enter the following command to identify the temporary tablespaces in
the INFRA
schemas:
select DISTINCT temporary_tablespace from dba_users WHERE username like 'DEV\_%' ESCAPE '\';
The command output should look similar to the following:
- In the
INFRA_EXPORTS
directory, use a text editor to create a script to export the tablespaces identified in the prior steps. For example, create a script namedexport_tablespaces.sql
with the following contents:set long 10000; set lines 200; set pages 400; set longchunksize 10000; spool create_tablespaces.sql SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_IAU') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_STB') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_MDS') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_IAS_OPSS') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_WLS') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV_IAS_TEMP') FROM DUAL; spool off
- As the
sys
user, run the script in the source environment:sqlplus sys/password as sysdba @export_tablespaces.sql quit
- Edit the output file (in this example,
create_tablespaces.sql
), and verify that it contains only lines withCREATE TABLESPACE
statements, then add a semicolon at the end of theCREATE
statement. For example:CREATE USER "DEV_IAU" IDENTIFIED BY VALUES CREATE TABLESPACE "DEV_IAU" DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/DEV_iau.dbf' SIZE 62914560 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
- In the
INFRA_EXPORTS
directory, use a text editor to create a script to export roles and grants. For example, create a script namedexport_role_grant.sql
with the following contents:set long 6000; set longchunksize 6000; set linesize 120; spool create_role_grant.sql BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_IAU') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_IAU') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_IAU') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_MDS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_MDS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_MDS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_OPSS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_OPSS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_OPSS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_STB') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_STB') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_STB') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_WLS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_WLS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_WLS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV_WLS_RUNTIME') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV_WLS_RUNTIME') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV_WLS_RUNTIME') FROM dual; spool off
- As the
sys
user, run the script in the source environment:sqlplus sys/password as sysdba @export_role_grant.sql quit
- Edit the output file (in this example,
create_role_grant.sql
), and verify that it contains only lines withGRANT
statements. For example:GRANT "CONNECT" TO "DEV_IAU"; GRANT "RESOURCE" TO "DEV_IAU"; GRANT "SELECT_CATALOG_ROLE" TO "DEV_IAU"; ...
- In the
INFRA_EXPORTS
directory, use a text editor to create a script to grantSELECT
privileges to theSCHEMA_VERSION_REGISTRY
table to all users. For example, create a script namedcreate_select_grant_system.sql
with the following contents:GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_IAU"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_IAU_APPEND"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_IAU_VIEWER"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_MDS"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_OPSS"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_STB"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV_WLS";
You will use this script later in Importing to the Target Database to validate the data.
Export SOA Schemas
To export SOA schemas from the source database:
- Sign in to the source environment. For example,
soa-host01.example.com
. - Change to the
oracle
user:sudo su - oracle
- Create a directory to store the exported files. For
example:
mkdir -p /u01/db_exp/SOA_EXPORTS cd /u01/db_exp/SOA_EXPORTS
- Set the
ORACLE_HOME
,ORACLE
, andPATH
environment variables. For example:export ORACLE_HOME=/opt/oracle/product/12.1.0.2/db_1 export ORACLE_SID=orclsoa export PATH=$PATH:$ORACLE_HOME/bin
- Change to the
sys
user, or any user withsysdba
privileges, and connect to the source database:sqlplus "/ as sysdba"
- Create the export directory. For
example:
CREATE DIRECTORY DUMP_SOA AS '/u01/db_exp/SOA_EXPORTS';
- Check if the export directory was successfully
created:
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DUMP_SOA;
- Grant
READ
andWRITE
permissions to the respective schemas:GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_IAU; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_IAU_APPEND; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_IAU_VIEWER; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_MDS; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_OPSS; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_SOAINFRA; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_STB; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_UMS; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_WLS; GRANT READ,WRITE ON DIRECTORY DUMP_SOA TO DEV2_WLS_RUNTIME;
- Enter the following commands to verify if any SOA queues are
stopped:
sqlplus / as sysdba connect DEV2_SOAINFRA; set lines 200; set pages 400; COLUMN name format A25; COLUMN enqueue_enabled format A15; COLUMN dequeue_enabled format A15; SELECT name,enqueue_enabled,dequeue_enabled FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE'; NAME ENQUEUE_ENABLED DEQUEUE_ENABLED ------------------------- --------------- --------------- B2B_BAM_QUEUE YES YES OSB_REPORTING_PURGE YES YES OSB_REPORTING_ERROR YES YES OSB_REPORTING_PROVIDER YES YES OSB_FILE_TRANSPORT YES YES OSB_EMAIL_TRANSPORT YES YES OSB_SFTP_TRANSPORT YES YES OSB_FTP_TRANSPORT YES YES TASK_NOTIFICATION_Q YES YES EDN_AQJMS_TOPIC YES YES EDN_OAOO_QUEUE YES YES EDN_EVENT_QUEUE YES YES IP_OUT_QUEUE YES YES IP_IN_QUEUE YES YES AIA_CAVSCALLBACKJMSQ YES YES
- Enter the following commands to stop the SOA database queues and
confirm they are
stopped:
sqlplus / as sysdba connect DEV2_SOAINFRA; set lines 200; set pages 400; COLUMN name format A25; COLUMN enqueue_enabled format A15; COLUMN dequeue_enabled format A15; --Stop the SOA database queues : BEGIN DBMS_AQADM.STOP_QUEUE ('AIA_CAVSCALLBACKJMSQ'); DBMS_AQADM.STOP_QUEUE ('B2B_BAM_QUEUE'); DBMS_AQADM.STOP_QUEUE ('EDN_AQJMS_TOPIC'); DBMS_AQADM.STOP_QUEUE ('EDN_EVENT_QUEUE'); DBMS_AQADM.STOP_QUEUE ('EDN_OAOO_QUEUE'); DBMS_AQADM.STOP_QUEUE ('IP_IN_QUEUE'); DBMS_AQADM.STOP_QUEUE ('IP_OUT_QUEUE'); DBMS_AQADM.STOP_QUEUE ('OSB_EMAIL_TRANSPORT'); DBMS_AQADM.STOP_QUEUE ('OSB_FILE_TRANSPORT'); DBMS_AQADM.STOP_QUEUE ('OSB_FTP_TRANSPORT'); DBMS_AQADM.STOP_QUEUE ('OSB_REPORTING_ERROR'); DBMS_AQADM.STOP_QUEUE ('OSB_REPORTING_PROVIDER'); DBMS_AQADM.STOP_QUEUE ('OSB_REPORTING_PURGE'); DBMS_AQADM.STOP_QUEUE ('OSB_SFTP_TRANSPORT'); DBMS_AQADM.STOP_QUEUE ('TASK_NOTIFICATION_Q'); END; / SELECT name,enqueue_enabled,dequeue_enabled FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE' order by name asc; NAME ENQUEUE_ENABLED DEQUEUE_ENABLED ------------------------- --------------- --------------- AIA_CAVSCALLBACKJMSQ NO NO B2B_BAM_QUEUE NO NO EDN_AQJMS_TOPIC NO NO EDN_EVENT_QUEUE NO NO EDN_OAOO_QUEUE NO NO IP_IN_QUEUE NO NO IP_OUT_QUEUE NO NO OSB_EMAIL_TRANSPORT NO NO OSB_FILE_TRANSPORT NO NO OSB_FTP_TRANSPORT NO NO OSB_REPORTING_ERROR NO NO OSB_REPORTING_PROVIDER NO NO OSB_REPORTING_PURGE NO NO OSB_SFTP_TRANSPORT NO NO TASK_NOTIFICATION_Q NO NO
- Verify that there are no connections from the
SOA
users above in the database:SELECT username FROM v$session WHERE username IS NOT NULL ORDER BY username ASC; quit
If there are any connections, stop the SOA domain.
- Change to the
oracle
user. - In the
SOA_EXPORTS
directory, use a text editor to create a bash script to run the Data Pump Export utility (expdp
) on all schemas. For example, create a script namedexport_soa_schema.sh
with the following contents:expdp DEV2_IAU/password schemas=DEV2_IAU directory=DUMP_SOA dumpfile=DEV2_IAU_export.dmp logfile=DEV2_IAU_export.log PARALLEL=4 expdp DEV2_IAU_APPEND/password schemas=DEV2_IAU_APPEND directory=DUMP_SOA dumpfile=DEV2_IAU_APPEND_export.dmp logfile=DEV2_IAU_APPEND_export.log PARALLEL=4 expdp DEV2_IAU_VIEWER/password schemas=DEV2_IAU_VIEWER directory=DUMP_SOA dumpfile=DEV2_IAU_VIEWER_export.dmp logfile=DEV2_IAU_VIEWER_export.log PARALLEL=4 expdp DEV2_MDS/password schemas=DEV2_MDS directory=DUMP_SOA dumpfile=DEV2_MDS_export.dmp logfile=DEV2_MDS_export.log PARALLEL=4 expdp DEV2_OPSS/password schemas=DEV2_OPSS directory=DUMP_SOA dumpfile=DEV2_OPSS_export.dmp logfile=DEV2_OPSS_export.log PARALLEL=4 expdp DEV2_SOAINFRA/password schemas=DEV2_SOAINFRA directory=DUMP_SOA dumpfile=DEV2_SOAINFRA_export.dmp logfile=DEV2_SOAINFRA_export.log PARALLEL=4 expdp DEV2_STB/password schemas=DEV2_STB directory=DUMP_SOA dumpfile=DEV2_STB_export.dmp logfile=DEV2_STB_export.log PARALLEL=4 expdp DEV2_UMS/password schemas=DEV2_UMS directory=DUMP_SOA dumpfile=DEV2_UMS_export.dmp logfile=DEV2_UMS_export.log PARALLEL=4 expdp DEV2_WLS/password schemas=DEV2_WLS directory=DUMP_SOA dumpfile=DEV2_WLS_export.dmp logfile=DEV2_WLS_export.log PARALLEL=4 expdp DEV2_WLS_RUNTIME/password schemas=DEV2_WLS_RUNTIME directory=DUMP_SOA dumpfile=DEV2_WLS_RUNTIME_export.dmp logfile=DEV2_WLS_RUNTIME_export.log PARALLEL=4
- As the
sys
user, run the script in the source environment:@export_soa_schema.sh quit
- Export the
SCHEMA_VERSION_REGISTRY
view and its underlying tables from theSYSTEM
schema:expdp \"/ as sysdba\" SCHEMAS=system INCLUDE=VIEW:"IN('SCHEMA_VERSION_REGISTRY')" TABLE:"IN('SCHEMA_VERSION_REGISTRY$')" directory=DUMP_SOA dumpfile=SYSTEM_SCHEMA_VERSION_REGISTRY.dmp logfile=SYSTEM_SCHEMA_VERSION_REGISTRY.log
Note:
If you encounter an error, make sure you are using a Bash shell to run this command - In the
SOA_EXPORTS
directory, use a text editor to create a script to export users. For example, create a script namedexport_users.sql
with the following contents:set long 10000; set lines 200; set pages 400; set longchunksize 10000; spool create_users.sql SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_IAU') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_MDS') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_OPSS') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_SOAINFRA') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_STB') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_UMS') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_WLS') FROM dual; SELECT DBMS_METADATA.GET_DDL('USER', 'DEV2_WLS_RUNTIME') FROM dual; spool off
- Change to the
sys
user, and run the script in the source environment:sqlplus sys/password as sysdba @export_users.sql quit
- Edit the output file (in this example,
create_users.sql
), and verify that it contains only lines withCREATE USER
statements, then add a semicolon at the end of theCREATE
statement. For example:CREATE USER "DEV_IAU" IDENTIFIED BY VALUES 'S:7C9B0ECE4641D7409A0CBF67505591DD4E437FDDF7524FDBE61051FC3663;H:4A0E99B6CA492BF37AA2177A0D0043F3;T:093C0D3F6BA909A2B2A9077F4AC1B92B6CF59A5C6C6404B7E07CDAA652947C0593FB2EB3D7AC886D9F3164A5F98D25004E6C7A1F7277E2491F1AB7AC26AF4FEAA58561E489AB3CD17BACE090E17A2A52;38D2C7CEB79AC568' DEFAULT TABLESPACE "DEV2_IAU" TEMPORARY TABLESPACE "DEV2_IAS_TEMP";
- Enter the following commands to identify the tablespaces in the SOA
schema:
set long 10000; set lines 200; set pages 400; set longchunksize 10000; SELECT DISTINCT tablespace_name,owner from dba_segments WHERE OWNER like 'DEV2\_%' ESCAPE '\';
The command output should look similar to the following:
- Enter the following command to identify the temporary tablespaces
in the SOA
schemas:
SELECT DISTINCT temporary_tablespace from dba_users WHERE username like 'DEV2\_%' ESCAPE '\';
The command output should look similar to the following:
- In the
SOA_EXPORTS
directory, use a text editor to create to export the tablespaces identified in the prior steps. For example, create a script namedexport_tablespaces.sql
with the following contents:set long 10000; set long 10000; set lines 200; set pages 400; set longchunksize 10000; spool create_tablespaces.sql BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAU') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_STB') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAS_UMS') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_SOAINFRA') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_MDS') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAS_OPSS') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_WLS') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLESPACE','DEV2_IAS_TEMP') FROM DUAL; spool off
- As the
sys
user, run the script in the source environment:sqlplus sys/password as sysdba @export_tablespaces.sql quit
- Edit the output file (in this example,
create_tablespaces.sql
), and verify that it contains only lines withCREATE TABLESPACE
statements, then add a semicolon at the end of theCREATE
statement. For example:CREATE TABLESPACE "DEV2_IAU" DATAFILE '/opt/oracle/oradata/DEV2_iau.dbf' SIZE 62914560 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
- In the
SOA_EXPORTS
directory, use a text editor to create a script to export roles and grants. For example, create a script namedexport_role_grant.sql
with the following contents:set long 6000; set longchunksize 6000; set linesize 120; spool create_role_grant.sql BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_IAU') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_IAU') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_IAU') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_IAU_APPEND') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_IAU_VIEWER') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_MDS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_MDS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_MDS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_OPSS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_OPSS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_OPSS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_SOAINFRA') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_SOAINFRA') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_SOAINFRA') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_STB') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_STB') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_STB') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_UMS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_UMS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_UMS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_WLS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_WLS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_WLS') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DEV2_WLS_RUNTIME') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','DEV2_WLS_RUNTIME') FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DEV2_WLS_RUNTIME') FROM dual; spool off
- As the
sys
user, run the script in the source environment:sqlplus sys/password as sysdba @export_role_grant.sql quit
- Edit the output file (in this example,
create_role_grant.sql
), and verify that it contains only lines withGRANT
statements. For example:GRANT "CONNECT" TO "DEV2_IAU"; GRANT "RESOURCE" TO "DEV2_IAU"; GRANT "SELECT_CATALOG_ROLE" TO "DEV2_IAU"; ...
- In the
SOA_EXPORTS
directory, use a text editor to create a script to grantSELECT
privileges to theSCHEMA_VERSION_REGISTRY
table to all users. For example, create a script namedcreate_select_grant_system.sql
with the following contents:GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_IAU"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_IAU_APPEND"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_IAU_VIEWER"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_MDS"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_OPSS"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_SOAINFRA"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_STB"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_UMS"; GRANT SELECT ON "SYSTEM"."SCHEMA_VERSION_REGISTRY" TO "DEV2_WLS";
You will use this script later in Importing to the Target Database to validate the data.
- Enter the following commands to restart the SOA queues and confirm
they are
started:
sqlplus / as sysdba connect DEV2_SOAINFRA; set lines 200; set pages 400; COLUMN name format A25; COLUMN enqueue_enabled format A15; COLUMN dequeue_enabled format A15; BEGIN DBMS_AQADM.START_QUEUE ('AIA_CAVSCALLBACKJMSQ'); DBMS_AQADM.START_QUEUE ('B2B_BAM_QUEUE'); DBMS_AQADM.START_QUEUE ('EDN_AQJMS_TOPIC'); DBMS_AQADM.START_QUEUE ('EDN_EVENT_QUEUE'); DBMS_AQADM.START_QUEUE ('EDN_OAOO_QUEUE'); DBMS_AQADM.START_QUEUE ('IP_IN_QUEUE'); DBMS_AQADM.START_QUEUE ('IP_OUT_QUEUE'); DBMS_AQADM.START_QUEUE ('OSB_EMAIL_TRANSPORT'); DBMS_AQADM.START_QUEUE ('OSB_FILE_TRANSPORT'); DBMS_AQADM.START_QUEUE ('OSB_FTP_TRANSPORT'); DBMS_AQADM.START_QUEUE ('OSB_REPORTING_ERROR'); DBMS_AQADM.START_QUEUE ('OSB_REPORTING_PROVIDER'); DBMS_AQADM.START_QUEUE ('OSB_REPORTING_PURGE'); DBMS_AQADM.START_QUEUE ('OSB_SFTP_TRANSPORT'); DBMS_AQADM.START_QUEUE ('TASK_NOTIFICATION_Q'); END; / SELECT name,enqueue_enabled,dequeue_enabled FROM USER_QUEUES where queue_type = 'NORMAL_QUEUE' order by name asc; NAME ENQUEUE_ENABLED DEQUEUE_ENABLED ------------------------- --------------- --------------- AIA_CAVSCALLBACKJMSQ YES YES B2B_BAM_QUEUE YES YES EDN_AQJMS_TOPIC YES YES EDN_EVENT_QUEUE YES YES EDN_OAOO_QUEUE YES YES IP_IN_QUEUE YES YES IP_OUT_QUEUE YES YES OSB_EMAIL_TRANSPORT YES YES OSB_FILE_TRANSPORT YES YES OSB_FTP_TRANSPORT YES YES OSB_REPORTING_ERROR YES YES OSB_REPORTING_PROVIDER YES YES OSB_REPORTING_PURGE YES YES OSB_SFTP_TRANSPORT YES YES TASK_NOTIFICATION_Q YES YES