Posted in 2016

TRANSPORT TABLESPACE problem

OK so I am preparing for the certification, it’s in 2 days so I am finishing with all the things and check features I have not used and it’s not working..

  1. prepare the user
    SYS> create tablespace peter ;
    
    Tablespace created.
    
    SYS> create user peter identified by peter default tablespace peter quota unlimited on peter ;
    
    User created.
    
    SYS> grant connect, resource to peter ;
    
    Grant succeeded.
    
    SYS> create table peter.test as select dbms_random.string('u',1000) as id from dual connect by level<=1000 ;
    
    Table created.
    
    SYS> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  2. Backup the database
    oracle@test:/u02/oradata/backup/transport/ [RCAT] rman target=/
    
    Recovery Manager: Release 12.1.0.2.0 - Production on Thu Nov 17 13:43:01 2016
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: RCAT (DBID=700135354)
    
    RMAN> backup as compressed backupset database plus archivelog delete input ;
    
    
    Starting backup at 17.11.2016 13:43:20
    current log archived
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=30 device type=DISK
    channel ORA_DISK_1: starting compressed archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in backup set
    input archived log thread=1 sequence=18 RECID=27 STAMP=928157900
    input archived log thread=1 sequence=19 RECID=28 STAMP=928158200
    channel ORA_DISK_1: starting piece 1 at 17.11.2016 13:43:21
    channel ORA_DISK_1: finished piece 1 at 17.11.2016 13:43:22
    piece handle=/u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_annnn_TAG20161117T134321_d2v9h9f4_.bkp tag=TAG20161117T134321 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    channel ORA_DISK_1: deleting archived log(s)
    archived log file name=/u02/fast_recovery_area/RCAT_SITE1/archivelog/2016_11_17/o1_mf_1_18_d2v95w6l_.arc RECID=27 STAMP=928157900
    archived log file name=/u02/fast_recovery_area/RCAT_SITE1/archivelog/2016_11_17/o1_mf_1_19_d2v9h8sc_.arc RECID=28 STAMP=928158200
    Finished backup at 17.11.2016 13:43:22
    
    Starting backup at 17.11.2016 13:43:22
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00001 name=/u02/oradata/RCAT_SITE1/datafile/o1_mf_system_d2p20rbv_.dbf
    input datafile file number=00002 name=/u02/oradata/RCAT_SITE1/datafile/o1_mf_sysaux_d2p20rcd_.dbf
    input datafile file number=00003 name=/u02/oradata/RCAT_SITE1/datafile/o1_mf_sys_undo_d2p20rcg_.dbf
    input datafile file number=00004 name=/u02/oradata/RCAT_SITE1/datafile/o1_mf_peter_d2v9cpd1_.dbf
    channel ORA_DISK_1: starting piece 1 at 17.11.2016 13:43:22
    channel ORA_DISK_1: finished piece 1 at 17.11.2016 13:43:37
    piece handle=/u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_nnndf_TAG20161117T134322_d2v9hbrp_.bkp tag=TAG20161117T134322 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    including current control file in backup set
    channel ORA_DISK_1: starting piece 1 at 17.11.2016 13:43:38
    channel ORA_DISK_1: finished piece 1 at 17.11.2016 13:43:39
    piece handle=/u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_ncnnf_TAG20161117T134322_d2v9httt_.bkp tag=TAG20161117T134322 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 17.11.2016 13:43:39
    
    Starting backup at 17.11.2016 13:43:39
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting compressed archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in backup set
    input archived log thread=1 sequence=20 RECID=29 STAMP=928158219
    channel ORA_DISK_1: starting piece 1 at 17.11.2016 13:43:40
    channel ORA_DISK_1: finished piece 1 at 17.11.2016 13:43:41
    piece handle=/u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_annnn_TAG20161117T134340_d2v9hw2o_.bkp tag=TAG20161117T134340 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    channel ORA_DISK_1: deleting archived log(s)
    archived log file name=/u02/fast_recovery_area/RCAT_SITE1/archivelog/2016_11_17/o1_mf_1_20_d2v9hvxc_.arc RECID=29 STAMP=928158219
    Finished backup at 17.11.2016 13:43:41
    

  3. Execute the transport tablespace command, which finishes successfull if you are not interrested in reading it
    RMAN> transport tablespace peter
     auxiliary destination '/u02/oradata/backup'
     dump file 'save.dmp'
     import script 'impdp.sql'
    datapump directory DATA_PUMP_DIR
     TABLESPACE DESTINATION '/u02/oradata/backup/transport' ;2> 3> 4> 5> 6>
    
    RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
    
    List of tablespaces expected to have UNDO segments
    Tablespace SYSTEM
    Tablespace SYS_UNDOTS
    
    Creating automatic instance, with SID='Fqeu'
    
    initialization parameters used for automatic instance:
    db_name=RCAT
    db_unique_name=Fqeu_pitr_RCAT
    compatible=12.1.0.2.0
    db_block_size=8192
    db_files=200
    diagnostic_dest=/u00/app/oracle
    _system_trig_enabled=FALSE
    sga_target=1000M
    processes=200
    db_create_file_dest=/u02/oradata/backup
    log_archive_dest_1='location=/u02/oradata/backup'
    #No auxiliary parameter file used
    
    
    starting up automatic instance RCAT
    
    Oracle instance started
    
    Total System Global Area    1048576000 bytes
    
    Fixed Size                     2932336 bytes
    Variable Size                276824464 bytes
    Database Buffers             763363328 bytes
    Redo Buffers                   5455872 bytes
    Automatic instance created
    Running TRANSPORT_SET_CHECK on recovery set tablespaces
    TRANSPORT_SET_CHECK completed successfully
    
    contents of Memory Script:
    {
    # set requested point in time
    set until  scn 800719;
    # restore the controlfile
    restore clone controlfile;
    
    # mount the controlfile
    sql clone 'alter database mount clone database';
    
    # archive current online log
    sql 'alter system archive log current';
    }
    executing Memory Script
    
    executing command: SET until clause
    
    Starting restore at 17.11.2016 13:44:23
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=12 device type=DISK
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: reading from backup piece /u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_ncnnf_TAG20161117T134322_d2v9httt_.bkp
    channel ORA_AUX_DISK_1: piece handle=/u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_ncnnf_TAG20161117T134322_d2v9httt_.bkp tag=TAG20161117T134322
    channel ORA_AUX_DISK_1: restored backup piece 1
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    output file name=/u02/oradata/backup/RCAT_SITE1/controlfile/o1_mf_d2v9k7qp_.ctl
    Finished restore at 17.11.2016 13:44:24
    
    sql statement: alter database mount clone database
    
    sql statement: alter system archive log current
    
    contents of Memory Script:
    {
    # set requested point in time
    set until  scn 800719;
    # set destinations for recovery set and auxiliary set datafiles
    set newname for clone datafile  1 to new;
    set newname for clone datafile  3 to new;
    set newname for clone datafile  2 to new;
    set newname for clone tempfile  1 to new;
    set newname for datafile  4 to
     "/u02/oradata/backup/transport/o1_mf_peter_%u_.dbf";
    # switch all tempfiles
    switch clone tempfile all;
    # restore the tablespaces in the recovery set and the auxiliary set
    restore clone datafile  1, 3, 2, 4;
    
    switch clone datafile all;
    }
    executing Memory Script
    
    executing command: SET until clause
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    renamed tempfile 1 to /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_temp_%u_.tmp in control file
    
    Starting restore at 17.11.2016 13:44:29
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_system_%u_.dbf
    channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_sys_undo_%u_.dbf
    channel ORA_AUX_DISK_1: restoring datafile 00002 to /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_sysaux_%u_.dbf
    channel ORA_AUX_DISK_1: restoring datafile 00004 to /u02/oradata/backup/transport/o1_mf_peter_%u_.dbf
    channel ORA_AUX_DISK_1: reading from backup piece /u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_nnndf_TAG20161117T134322_d2v9hbrp_.bkp
    channel ORA_AUX_DISK_1: piece handle=/u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_nnndf_TAG20161117T134322_d2v9hbrp_.bkp tag=TAG20161117T134322
    channel ORA_AUX_DISK_1: restored backup piece 1
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
    Finished restore at 17.11.2016 13:44:54
    
    datafile 1 switched to datafile copy
    input datafile copy RECID=5 STAMP=928158294 file name=/u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_system_d2v9kfbt_.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=6 STAMP=928158294 file name=/u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_sys_undo_d2v9kfcq_.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=7 STAMP=928158294 file name=/u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_sysaux_d2v9kfco_.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=8 STAMP=928158294 file name=/u02/oradata/backup/transport/o1_mf_peter_d2v9kfd6_.dbf
    
    contents of Memory Script:
    {
    # set requested point in time
    set until  scn 800719;
    # online the datafiles restored or switched
    sql clone "alter database datafile  1 online";
    sql clone "alter database datafile  3 online";
    sql clone "alter database datafile  2 online";
    sql clone "alter database datafile  4 online";
    # recover and open resetlogs
    recover clone database tablespace  "PETER", "SYSTEM", "SYS_UNDOTS", "SYSAUX" delete archivelog;
    alter clone database open resetlogs;
    }
    executing Memory Script
    
    executing command: SET until clause
    
    sql statement: alter database datafile  1 online
    
    sql statement: alter database datafile  3 online
    
    sql statement: alter database datafile  2 online
    
    sql statement: alter database datafile  4 online
    
    Starting recover at 17.11.2016 13:44:54
    using channel ORA_AUX_DISK_1
    
    starting media recovery
    
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=20
    channel ORA_AUX_DISK_1: reading from backup piece /u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_annnn_TAG20161117T134340_d2v9hw2o_.bkp
    channel ORA_AUX_DISK_1: piece handle=/u02/fast_recovery_area/RCAT_SITE1/backupset/2016_11_17/o1_mf_annnn_TAG20161117T134340_d2v9hw2o_.bkp tag=TAG20161117T134340
    channel ORA_AUX_DISK_1: restored backup piece 1
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    archived log file name=/u02/oradata/backup/1_20_927987633.dbf thread=1 sequence=20
    channel clone_default: deleting archived log(s)
    archived log file name=/u02/oradata/backup/1_20_927987633.dbf RECID=29 STAMP=928158295
    media recovery complete, elapsed time: 00:00:00
    Finished recover at 17.11.2016 13:44:56
    
    database opened
    
    contents of Memory Script:
    {
    # make read only the tablespace that will be exported
    sql clone 'alter tablespace  PETER read only';
    }
    executing Memory Script
    
    sql statement: alter tablespace  PETER read only
    
    Performing export of metadata...
       EXPDP> Starting "SYS"."TSPITR_EXP_Fqeu_ipfg":
       EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
       EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
       EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
       EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
       EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
       EXPDP> Master table "SYS"."TSPITR_EXP_Fqeu_ipfg" successfully loaded/unloaded
       EXPDP> ******************************************************************************
       EXPDP> Dump file set for SYS.TSPITR_EXP_Fqeu_ipfg is:
       EXPDP>   /u00/app/oracle/product/12.1.0.2/rdbms/log/save.dmp
       EXPDP> ******************************************************************************
       EXPDP> Datafiles required for transportable tablespace PETER:
       EXPDP>   /u02/oradata/backup/transport/o1_mf_peter_d2v9kfd6_.dbf
       EXPDP> Job "SYS"."TSPITR_EXP_Fqeu_ipfg" successfully completed at Thu Nov 17 13:45:15 2016 elapsed 0 00:00:17
    Export completed
    
    /*
       The following command may be used to import the tablespaces.
       Substitute values for <logon> and <directory>.
    
       impdp <logon> directory=<directory> dumpfile= 'save.dmp' transport_datafiles= /u02/oradata/backup/transport/o1_mf_peter_d2v9kfd6_.dbf
    */
    
    --
    --
    --
    --
    CREATE DIRECTORY STREAMS$DIROBJ$1 AS  '/u02/oradata/backup/transport/';
    /* PL/SQL Script to import the exported tablespaces */
    DECLARE
    --
      tbs_files     dbms_streams_tablespace_adm.file_set;
      cvt_files     dbms_streams_tablespace_adm.file_set;
    
    --
      dump_file     dbms_streams_tablespace_adm.file;
      dp_job_name   VARCHAR2(30) := NULL;
    
    --
      ts_names       dbms_streams_tablespace_adm.tablespace_set;
    BEGIN
    --
      dump_file.file_name :=  'save.dmp';
      dump_file.directory_object := 'DATA_PUMP_DIR';
    
    --
      tbs_files( 1).file_name :=  'o1_mf_peter_d2v9kfd6_.dbf';
      tbs_files( 1).directory_object :=  'STREAMS$DIROBJ$1';
    
    --
      dbms_streams_tablespace_adm.attach_tablespaces(
        datapump_job_name      => dp_job_name,
        dump_file              => dump_file,
        tablespace_files       => tbs_files,
        converted_files        => cvt_files,
        tablespace_names       => ts_names);
    
    --
      IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
        FOR i IN ts_names.first .. ts_names.last LOOP
          dbms_output.put_line('imported tablespace '|| ts_names(i));
        END LOOP;
      END IF;
    END;
    /
    
    --
    DROP DIRECTORY STREAMS$DIROBJ$1;
    --------------------------------------------------------------
    -- End of sample PL/SQL script
    --------------------------------------------------------------
    
    Removing automatic instance
    shutting down automatic instance
    Oracle instance shut down
    Automatic instance removed
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_temp_d2v9l8sv_.tmp deleted
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/onlinelog/o1_mf_3_d2v9l8kz_.log deleted
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/onlinelog/o1_mf_2_d2v9l8fn_.log deleted
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/onlinelog/o1_mf_1_d2v9l896_.log deleted
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_sysaux_d2v9kfco_.dbf deleted
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_sys_undo_d2v9kfcq_.dbf deleted
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/datafile/o1_mf_system_d2v9kfbt_.dbf deleted
    auxiliary instance file /u02/oradata/backup/RCAT_SITE1/controlfile/o1_mf_d2v9k7qp_.ctl deleted
    auxiliary instance file save.dmp deleted
    
    
    RMAN> exit
    
    
    Recovery Manager complete.
    

Where is the save.dmp??? Why was it deleted???

oracle@test:/u02/oradata/backup/transport/ [RCAT] ls -l /u00/app/oracle/product/12.1.0.2/rdbms/log/save.dmp
ls: cannot access /u00/app/oracle/product/12.1.0.2/rdbms/log/save.dmp: No such file or directory

funny…

when I start it like:

RMAN> transport tablespace peter
2> tablespace destination '/u02/oradata/backup/transport'
3> auxiliary destination '/u02/oradata/backup' ;
...Could not delete auxiliary instance file dmpfile.dmp
ORACLE error from target database:
ORA-29280: invalid directory path

it finishes with an error, but when I drop the tablespace see what happens:

SYS> drop tablespace peter including contents and datafiles ;
Tablespace dropped.

impdp system/oracle dumpfile=dmpfile.dmp transport_datafiles='/u02/oradata/backup/transport/o1_mf_peter_d2vhn98n_.dbf' directory=datapump

Import: Release 12.1.0.2.0 - Production on Thu Nov 17 15:33:17 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=dmpfile.dmp transport_datafiles=/u02/oradata/backup/transport/o1_mf_peter_d2vhn98n_.dbf directory=datapump
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Nov 17 15:33:34 2016 elapsed 0 00:00:16

SYS> select count(*) from peter.test ;

  COUNT(*)
----------
      1000

I think that the save.dmp which I defined explicitly should be kept and they delete it! Or am I doing something wrong??

To have it complete we can move it to the correct destination

         1  /u02/oradata/RCAT_SITE1/datafile/o1_mf_system_d2p20rbv_.dbf
         2  /u02/oradata/RCAT_SITE1/datafile/o1_mf_sysaux_d2p20rcd_.dbf
         3  /u02/oradata/RCAT_SITE1/datafile/o1_mf_sys_undo_d2p20rcg_.dbf
         4  /u02/oradata/backup/transport/o1_mf_peter_d2vhn98n_.dbf

db_create_file_dest                  string                            /u02/oradata

 alter database move datafile 4 ;

        4 /u02/oradata/RCAT_SITE1/datafile/o1_mf_peter_d2vjjt9l_.dbf
Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Tagged:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s