Monday, August 6, 2012

RMAN Transportable tablespace bug

I was a long time since I last used Transportable Tablespaces and I decided to refresh my memory and setup a test environment for Streams using TTS feature. I was going to use example script from Oracle Streams Documentation which is creating necessary data files for specified point of time (no need to switch source tablespace into read only mode) and also is running Data Pump creating meta data file. I have done necessary  changes and run it.
[oracle@testdb1 oracle]$ rsp / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 6 12:50:04 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  until_scn NUMBER;
BEGIN
  until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
      DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);
SYS@testa AS SYSDBA >   2    3    4    5    6  END;
  7  /
Until SCN: 6300731

PL/SQL procedure successfully completed.
SYS@testa AS SYSDBA > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@testdb1 oracle]$ rlwrap rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 6 12:50:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTA (DBID=243207980)

RMAN>  RUN
2>     {
        TRANSPORT TABLESPACE 'SOE_TS'
        UNTIL SCN 6300731
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
        DATAPUMP DIRECTORY SOURCE_DIR
        AUXILIARY DESTINATION '/u01/app/oracle/aux_files'
        DUMP FILE 'soe_ts.dmp'
        EXPORT LOG 'soe.log' 
        IMPORT SCRIPT 'soe_ts.sql'
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
       }

using target database control file instead of recovery catalog
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 UNDOTBS2
...
Unfortunately there is a RMAN bug related to export log file. Due to it first attempt end up with following error
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "SOE" read only';
}
executing Memory Script

sql statement: alter tablespace  "SOE" read only

Performing export of metadata...

Removing automatic instance
shutting down automatic instance
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of transport tablespace command at 08/05/2012 23:18:47
RMAN-06136: ORACLE error from auxiliary database: ORA-01097: cannot shutdown while in a transaction - commit or rollback first
RMAN-06136: ORACLE error from auxiliary database: ORA-01460: unimplemented or unreasonable conversion requested

RMAN>
At first sight error message has nothing to do with log file but I know that RMAN error messaging is well .. different is a good word here. I search for solution and I found out that workaround is quite simple - EXPORT LOG clause has to be removed from script. So let's run it again
[oracle@testdb1 oracle]$ rlwrap rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 6 12:50:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTA (DBID=243207980)

RMAN>  RUN
2>       {
        TRANSPORT TABLESPACE 'SOE_TS'
        UNTIL SCN 6300731
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
        DATAPUMP DIRECTORY SOURCE_DIR
        AUXILIARY DESTINATION '/u01/app/oracle/aux_files'
        DUMP FILE 'soe_ts.dmp'
        #EXPORT LOG 'soe.log' - bug
        IMPORT SCRIPT 'soe_ts.sql'
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
      }

using target database control file instead of recovery catalog
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 UNDOTBS2
...
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "SOE_TS" read only';
}
executing Memory Script

sql statement: alter tablespace  "SOE_TS" read only

Performing export of metadata...
   EXPDP> FLASHBACK automatically enabled to preserve database integrity.
   EXPDP> Starting "SYS"."TSPITR_EXP_khar":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_khar" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_khar is:
   EXPDP>   /u01/app/oracle/dest/soe_ts.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace SOE_TS:
   EXPDP>   /u01/app/oracle/dest/soe_ts01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_khar" successfully completed at 12:55:35
Export completed
Now looks much better - tablespace has been exported and import script has been generated as well. Now I'm ready for next steps and can continue my work with Streams.

regards,
Marcin

0 comments: