- TARGET - changed character set to WE8ISO8859P9 (must be the same as source)
...
- SOURCE - decide which period to migrate and identify schema names, tablespace names and datafile names related to this period:
SQL> SET PAGESIZE 100;
SQL> SET LINESIZE 120;
SQL> SET VERIFY OFF;
SQL> DEFINE period=02P2F;
SQL> SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE '%&period%' ORDER BY 1;
USERNAME
------------------------------
COMPANL_02P2F
COMPASS_02P2F
COMPDST_02P2F
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME LIKE '%&period%' ORDER BY 1;
TABLESPACE_NAME
------------------------------
02P2F_ANL_DATA01
02P2F_ANL_INDX01
02P2F_DATA01
02P2F_DATA02
02P2F_DST_DATA01
02P2F_DST_INDX01
02P2F_INDX01
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%&period%' ORDER BY TABLESPACE_NAME, FILE_NAME;
FILE_NAME
------------------------------------------------------------------------------
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_DATA01_02.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_DATA01_03.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_DATA01_04.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_DATA01_05.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_INDX01_02.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_INDX01_03.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_INDX01_04.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_ANL_INDX01_05.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DATA01_02.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DATA01_03.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DATA01_04.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DATA01_05.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DATA02_01.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_DATA01_02.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_DATA01_03.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_DATA01_04.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_DATA01_05.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_INDX01_02.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_INDX01_03.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_INDX01_04.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_DST_INDX01_05.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_INDX01_02.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_INDX01_03.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_INDX01_04.dbf
/ORA/dbs03/oradata/compdb1/compdb1_02P2F_INDX01_05.dbf
25 rows selected.
- SOURCE - make selected tablespaces READ ONLY:
SQL> @make_ts_read_only_gen.sql
SQL> @make_ts_read_only.sql
- SOURCE - check if the selected period is self-contained:
SQL> EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('02P2F_DST_DATA01, 02P2F_DST_INDX01, 02P2F_INDX01,02P2F_ANL_DATA01, 02P2F_ANL_INDX01, 02P2F_DATA01, 02P2F_DATA02', TRUE);
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
*** Write a PL/SQL procedure that generates a script containing commands above.
- SOURCE - identify settings of schemas related to transported period:
SQL> SELECT USERNAME, PASSWORD, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME LIKE '%&period%';
USERNAME PASSWORD DEFAULT_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEMPORARY_TABLESPACE PROFILE ACCOUNT_STATUS
------------------------------ ------------------------------ --------------------------------
COMPASS_02P2F D26C596A4C2783CE 02P2F_DATA01
TEMP DEFAULT OPEN
COMPDST_02P2F EBCB20A2CA8F4E76 02P2F_DST_DATA01
TEMP DEFAULT OPEN
COMPANL_02P2F 8D03AA91902EA10E 02P2F_ANL_DATA01
TEMP DEFAULT OPEN
- SOURCE - identify users' roles:
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE '%&period%' ORDER BY GRANTEE;
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
COMPANL_02P2F COMPASS_ANALYSIS NO YES
COMPASS_02P2F COMPASS_MIGRATION NO YES
COMPDST_02P2F COMPASS_DST_PROD NO YES
- SOURCE - generate script that will create appropriate roles and schemas in the target database:
SQL> @roles_schemas_ddl_gen.sql
SQL> !scp ./roles_schemas_ddl.sql oracle@itrac03:work/jwojcies
- TARGET - execute script generated in the previous step:
$ cd work/jwojcies
$ sqlsys_DB
SQL> @roles_schemas_ddl.sql
SQL> exit
$ cd work/jwojcies
$ sqlsys_DB
SQL> @roles_schemas_ddl.sql
SQL> exit
- TARGET - create a directory to be used to store COMPASS files.
$sqlsys_ASM
SQL> ALTER DISKGROUP TEST1_DATADG1 ADD DIRECTORY '+TEST1_DATADG1/test1/compass';
SQL> exit
- TARGET - create a user to be used for COMPASS files transfer:
$ sqlsys_DB
SQL> CREATE USER asm_ftp IDENTIFIED BY .... DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
SQL> GRANT connect, resource, dba TO asm_ftp;
SQL> exit
- SOURCE - perform export of tablespaces belonging to the chosen period:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
$ exp "'/ as sysdba'" parfile=parfile.txt
Where parfile.txt contains:
file=exp_tts.dmp
statistics=none
transport_tablespace=y tablespaces=02P2F_DST_DATA01,02P2F_DST_INDX01,02P2F_INDX01,02P2F_ANL_DATA01,02P2F_ANL_INDX01,02P2F_DATA01,02P2F_DATA02
$ scp exp_tts.dmp oracle@itrac03:work/jwojcies
- SOURCE - ftp datafiles belonging to exported tablespaces to the target ASM diskgroup:
$ cd /ORA/dbs03/oradata/compdb1
$ ftp -i
ftp> open itrac03 3100
Name (itrac03:oracle): asm_ftp
Password: XXXXXXX
ftp> bin
ftp> cd /sys/asm/test1_datadg1/test1/compass
ftp> bin
ftp> mput *02P2F*.dbf
ftp> ls
ftp> bye
- TARGET - import copied tablespaces:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
$ imp "'/ as sysdba'" parfile=parfile.txt
where parfile.txt contatins:
file=exp_tts.dmp
transport_tablespace=y
datafiles=+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_ANL_DATA01_02.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_ANL_DATA01_03.dbf,+TEST1_DATADG1/TEST1
/compass/compdb1_02P2F_ANL_DATA01_04.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_ANL_DATA01_05.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_ANL_IND
X01_02.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_ANL_INDX01_03.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_ANL_INDX01_05.dbf,+TEST1_DATADG1/TEST
1/compass/compdb1_02P2F_ANL_INDX01_04.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DATA01_02.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DATA01_03.
dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DATA01_04.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DATA01_05.dbf,+TEST1_DATADG1/TEST1/compass/compd
b1_02P2F_DATA02_01.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DST_DATA01_02.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DST_DATA01_03.dbf,+TEST1_
DATADG1/TEST1/compass/compdb1_02P2F_DST_DATA01_04.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DST_DATA01_05.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_
02P2F_DST_INDX01_02.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DST_INDX01_03.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_DST_INDX01_05.dbf,+TEST1
_DATADG1/TEST1/compass/compdb1_02P2F_DST_INDX01_04.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_INDX01_02.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P
2F_INDX01_03.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_INDX01_04.dbf,+TEST1_DATADG1/TEST1/compass/compdb1_02P2F_INDX01_05.dbf
*** The datafiles parameter can be generated in the source database
with use of the following SQL statement:
SELECT file_name || ',' FROM dba_data_files WHERE tablespace_name LIKE '%&period%';
- SOURCE - create a directory to store in the file with exported metadata and execute the procedure 'EXPORT_METADATA' from the 'COMPASS_METADATA_MIGRATION' package to export metadata related to the migrated period:
SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp';
SQL> exec COMPASS_METADATA_MIGRATION.EXPORT_METADATA('%&period%');
Edit the generated SQL script ('/tmp/compass_metadata_ddl.sql'
) prefixing trigger's condition clauses with appropriate schema name:
e.g. In the "COMPDST_02P2F"."ALL_FILES_TRG" trigger change:
instead of update on all_files
to
instead of update on COMPDST_02P2F.all_files
Remove also the following grant:
GRANT SELECT ON "SYS"."USER_SEGMENTS" TO PUBLIC;
Scp modified script to the target system:
$ scp /tmp/compass_metadata_ddl.sql oracle@itrac03:work/jwojcies
- TARGET - execute twice the script generated in the previous step.
$ sqlsys_DB
SQL> @compass_metadata_ddl.sql
SQL> @compass_metadata_ddl.sql