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

  1. 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
  1. TARGET - changed character set to WE8ISO8859P9 (must be the same as source)
         ...
       
  2. 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.
       
  3. SOURCE - make selected tablespaces READ ONLY:
         SQL> @make_ts_read_only_gen.sql
         SQL> @make_ts_read_only.sql
       
  4. 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.
       
  5. 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
       
  6. 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
       
  7. 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
       
  8. 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
       
  9. 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
       
  10. 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
       
  11. 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
       
  12. 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
       
  13. 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%';
       
  14. 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
       
  15. TARGET - execute twice the script generated in the previous step.
         $ sqlsys_DB
         SQL> @compass_metadata_ddl.sql
         SQL> @compass_metadata_ddl.sql
       

  1. 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.
  2. TARGET - make tablespaces READ WRITE:
         SQL> @make_ts_read_write_gen.sql
         SQL> @make_ts_read_write.sql
       
Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions...
Topic revision: r1 - 2006-03-01 - LucaCanali
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 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