TestMigr1 < PSSGroup

WARNING: This web is not used anymore. Please use PDBService.TestMigr1 instead!

PSSGroup Web>PhysicsDatabasesSection>DbaArea>CompassToRac>TestMigr1 (2006-03-09, LucaCanali)

TARGET - activate ftp over xdb on port 3100 and open port with iptables:

     $ sqlsys_DB
     SQL> exec dbms_xdb.setftpport(3100);
     SQL> exit
     $ ...

Note: to change ftp params such as session timeout see: http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb22pro.htm#sthref2260

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

BOTH - verify whether all objects and all privileges have been migrated correctly:

     SQL> DEFINE period=02P2F;
     SQL> COL OBJECT_NAME FOR a30;

     SQL> select owner, object_name, object_type, status from dba_objects where owner LIKE '%&period%' order by 3,1,2;

     SQL> select r.granted_role, p.privilege from dba_role_privs r, dba_sys_privs p where r.granted_role=p.grantee(+) and r.grantee LIKE '%&period%' ORDER BY 1,2;

     SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS from dba_constraints where owner LIKE '%&period%' ORDER BY 1,2,3;

     SQL> select grantee, privilege, table_name, grantor from dba_tab_privs where grantor LIKE '%&period%' order by 4,3,2;

Eliminate differenced if needed.

TARGET - make tablespaces READ WRITE:

     SQL> @make_ts_read_write_gen.sql
     SQL> @make_ts_read_write.sql

Topic revision: r2 - 2006-03-09 - LucaCanali

PSSGroup

PSSGroup

On Shift

PSSGroup Wiki
Last Changes
Pages Index
Search web

- Cern Search
- TWiki Search
- Google Search
PSSGroup All webs

Copyright &© 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback