Guenadi N Jilevski's Oracle BLOG

Transportable database and transportable tablespace(s) from backup, Tablespace point in time recovery TSPITR

Transportable database and transportable tablespace(s) from backup, Tablespace point in time recovery TSPITR

Transportable tablespace feature introduced since Oracle 10g allowed a tablespace placed in a read only mode to be transported to another database and plugged in. However system and sysaux tablespaces were not transportable. Transportable database feature allows a database to be transported from one platform to another if the following conditions are met.

• Both databases are within the list of platforms in v$trasportable_platform
  
• Both platforms are with the same byte ordering. This further restricts the list of platforms.
  

Using the transportable database feature Oracle creates copies of the datafiles and recreates the control file, redo log files and creates the temporary tablespace file.

Transportable tablespace from backup avoids placing a tablespace in read only mode prior to the transportation and uses a backup for this purpose. TSPIRT enable us to recover a tablespace to a point in time in the past. TPSPITR is similar to the transportable tablespace from backup. In both cases an auxiliary instance is created. Using the previous target database backup an auxiliary database instance is created and restored to an auxiliary location and recovered to a point in time in the past. The auxiliary database is opened with reset logs. The tablespace set is exported with transportable tablespace mode and the files are copied to the location in case of transportable tablespace from a backup. In case of a TSPITR the tablespaces from the Auxiliary instance are plugged into the target database. In both cases the auxiliary instance is destroyed automatically upon successful completion.

The article will look at

• Transportable database.
  
• Transportable Tablespace from backup
  
• Tablespace point in time Recovery (TSPITR)
  

1. Trasportable Database
   

   Purpose and prerequisites: Transport databases across a list of supported platforms listed in v$transportable_platform provided that the endianess is the same that is, byte ordering is the same.
   

   How it works: The database is opened in read only mode. DBMS_TDS package is used to determine if the database can be transported and identify
   

• Directory objects
  
• External tables
  
• BFILE objects
  

RMAN command convert database is issued on the target database in two forms

• CONVERT DATABASE TRANSPORT SCRIPT ‘crdb1.sql’NEW DATABASE ‘nora10g’ TO PLATFORM ‘Linux x86 64-bit’ FORMAT ‘E:\test1\%U’;
  

• CONVERT DATABASE ON TARGET PLATFORM CONVERT SCRIPT ‘cnvt.sql’ TRANSPORT SCRIPT ‘crdb.sql’ NEW DATABASE ‘nora10g’ FORMAT ‘E:\test1\%U’;
  

In first case the database is to be transported to the specified destination platform and the command is executed on the source platform to generate the

• Transport script
  
• Pfile from the spfile
  
• Database file
  

DBA copies the pfile, database files and the transport script to the destination platform. DBA recreates on the target the password file as its name is dependent on the platform, starts the instance on the target with the copied pfile and invokes the transport script to recreate the control file and redo log files. The content of the transport script and generated pfile are in ANNEX A.1.

In the second case database is to be transported to a target platform and the conversion is executed on the target platform. Thus, executing the command on the source database generates

• Transport script
  
• Convert script
  
• Pfile from the spfile
  
• Database file
  

   

DBA copies both convert and transport scripts, datafiles and pfile to the platform. After that DBA runs the convert script on the target using RMAN and after that runs the transport script to recreate the control file and redo logs on the target. The contents of the generated transport script, convert script and pfile are in ANNEX A.2

In both cases the procedure is as follows.

1. Open the target database in read only mode.
   
2. Execute against the target database dbms_tdb.check_db to verify that the database can be transported.
   
3. Execute against the target database dbms_tdb.external to identify directory objects, external tables and BFILES. The directory objects and external tables need to reflect new directory structure on the target database and the BFILE needs to be copied from the source.
   
4. Issue the CONVERT DATABASE on the source database.
   
5. Copy the datafile, pfile and generated scripts to the target database.
   
6. Create password file on the target database.
   
7. Start the target database instance using the copied pfile.
   
8. Convert the database file if the conversion is to be done on the target. Skip this step if the conversion is already done.
   
9. Recreate the control file and redo log files executing the transport script.
   
10. Backup the database on the target.
    

Example of trasportin a MS Windows database to ‘Linux x86 64-bit’ platform: Execute dbms_tbd.check_db to verify that database can be transported.

SQL>

SQL> set serveroutput on

SQL> DECLARE

2 b boolean;

3 BEGIN

4 b:= DBMS_TDB.CHECK_DB(‘Linux x86 64-bit’);

5 IF b THEN dbms_output.put_line(‘YES’);

6 ELSE dbms_output.put_line(‘NO’);

7 END IF;

8 END;

9 /

Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and

retry.

NO

PL/SQL procedure successfully completed.

SQL>

The function returns an error if the database is not open in read only mode. After database is in read only mode the output is as follows.

SQL> alter database open read only;

Database altered.

SQL> set serveroutput on

SQL> DECLARE

2 b boolean;

3 BEGIN

4 b:= DBMS_TDB.CHECK_DB(‘Linux x86 64-bit’);

5 IF b THEN dbms_output.put_line(‘YES’);

6 ELSE dbms_output.put_line(‘NO’);

7 END IF;

8 END;

9 /

YES

PL/SQL procedure successfully completed.

SQL>

Execute the dbms_tdb.external function to determine external tables, directories and bfile that needs to be addressed on the target database.

SQL> DECLARE

2 b boolean;

3 BEGIN

4 b:= DBMS_TDB.CHECK_EXTERNAL;

5 END;

6 /

The following external tables exist in the database:

SH.SALES_TRANSACTIONS_EXT

The following directories exist in the database:

SYS.EM_TTS_DIR_OBJECT, SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.MEDIA_DIR,

SYS.SUBDIR, SYS.XMLDIR, SYS.ORACLECLRDIR, SYS.WORK_DIR, SYS.ADMIN_DIR,

SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR

The following BFILEs exist in the database:

PM.PRINT_MEDIA

PL/SQL procedure successfully completed.

SQL>

Execute the either of convert database command. The output is in the annex A.1 or Annex A.2

RMAN> CONVERT DATABASE TRANSPORT SCRIPT ‘crdb1.sql’NEW DATABASE ‘nora10g’ TO PLATFORM ‘Linux x86 64-bit’ FORMAT ‘E:\test1\%U’;

RMAN> CONVERT DATABASE ON TARGET PLATFORM CONVERT SCRIPT ‘cnvt.sql’ TRANSPORT SCRIPT ‘crdb.sql’ NEW DATABASE ‘nora10g’ FORMAT ‘E:\test1\%U’;

Follow the steps from 1.5 to copy the files and execute the scripts on the target database.

1. Transportable Tablespace from Backup
   

   Transportable tablespace from backup feature prepares a tablespace set for transportation without placing the tablespaces in read only mode. The feature connects to the source database as a target database and uses a backup of the source database to starts a temporary auxiliary instance. After that performs restore and recovery of the auxiliary instance database up to the point in time specified using backups of the source database. Once the auxiliary instance is restored and recovered up to the point in time specified the tablespace set metadata is exported and the datafile files and dump files are placed in the specified location. The AUXILIARY DESTINATION parameter determines where the temp auxiliary instance datafiles are created. The dump file determines the name of the export dump to be used for the import of the tablespace set on the destination database. Export log parameter specifies the log for the progress of the export. Import script parameter generates the script to be used for the import on the destination database. The tablespace destination determines where the datafiles and the dump file comprising the transportable tablespace set are placed. In order to transport a tableaspace(s) while database is open in a read write mode connect with rman and issue the TRANSPORT TABLESPACE command as follows.
   

   rman target =/
   

   RUN {
   

   TRANSPORT TABLESPACE ‘TBSBF’
   

   AUXILIARY DESTINATION ‘e:\test’
   

   DUMP FILE ‘tbs.dmp’
   

   EXPORT LOG ‘tbs.log’
   

   IMPORT SCRIPT ‘imptbsalert.sql’
   

   TABLESPACE DESTINATION ‘e:\test1’
   

   UNTIL TIME “to_date(‘February 12 2011,
   

   02:15:00′,’Month dd yyyy, HH24:MI:SS’)”;
   

   }
   

   The output is shown in Annex B. Generated dump file and the tablespace datafiles are placed in the location e:\test1 specified by tablespace destination. Content of the import script is in Annex B. Content of the export log is in Annex B. After completion of the export copy the datafiles and dump file to the destination database. If the endian format is different perform the conversion as in case of regular tablespace transportation. The conversion can take place either on source or target database. Finally import the tablespace metadata.
   

   Steps to perform transportable tablespace from backup are
   

• From a database running in read write mode without placing the tablespaces in read only mode and valid backups execute the transport tablespace command.
  

• Copy the dump file and the tablespace datafiles to the target platform.
  

• Perform conversion either on the target or source database as in the case of a regular transportable tablespace.
  

• Plug the tablespace into the destination database using either impdp or the script specified.
  

1. Tablespace Point in Time Recovery (TSPITR)
   

   TSPITR feature allows recovering a tablespace to a point in time while the database is available up and running . The following block needs to be executed.
   

   run {
   

   sql ‘alter system archive log current’;
   

   recover tablespace ‘TSPITR’ until time “to_date(‘2011-02-12 22:55:00’, ‘YYYY-MM-DD HH24:MI:SS’)” auxiliary destination = ‘e:\oracle11gr2\auxdir’;
   

   backup tablespace ‘TSPITR’;
   

   sql ‘alter tablespace “TSPITR” online’;
   

   }
   

   Auxiliary destination specify the location where the auxiliary instance datafiles will be created. Rman during the TSPITR creates an auxiliary instance by restoring and recovering from backup the auxiliary database to the point in time specified. After that the tablespace is plugged into the target database from the auxiliary database. The auxiliary instance is automatically destroyed after successful TSPITR.
   

   After successful TSPITR the tablespace must be backed up!
   

   Annex C contains the output of the run block.
   

Summary

We looked at how to transport a database across platforms having the same byte ordering. If a tablespace cannot be placed in read only mode due to availability requirements the transportable tablespace from backup feature comes handy. We outlined the steps to transport a tablespace from backup. TSPITR was covered as well.

Annex A.1

RMAN> CONVERT DATABASE TRANSPORT SCRIPT ‘crdb1.sql’NEW DATABASE ‘nora10g’ TO PLATFORM ‘Linux x86 64-bit’ FORMAT ‘E:\test1\%U’;

Starting convert at 19-JAN-11

using channel ORA_DISK_1

using channel ORA_DISK_2

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.EM_TTS_DIR_OBJECT found in the database

Directory SYS.LOG_FILE_DIR found in the database

Directory SYS.DATA_FILE_DIR found in the database

Directory SYS.MEDIA_DIR found in the database

Directory SYS.SUBDIR found in the database

Directory SYS.XMLDIR found in the database

Directory SYS.ORACLECLRDIR found in the database

Directory SYS.WORK_DIR found in the database

Directory SYS.ADMIN_DIR found in the database

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

Directory SYS.DATA_PUMP_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

User SYSTEM with SYSDBA privilege found in password file

channel ORA_DISK_2: starting datafile conversion

input datafile fno=00003 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.D

BF

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00001 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.D

BF

converted datafile=E:\TEST\DATA_D-ORA10G_I-4030987615_TS-SYSTEM_FNO-1_0VM2H2DC

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00005 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\EXAMPLE01.

DBF

converted datafile=E:\TEST\DATA_D-ORA10G_I-4030987615_TS-SYSAUX_FNO-3_0UM2H2DC

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:25

channel ORA_DISK_2: starting datafile conversion

input datafile fno=00002 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.

DBF

converted datafile=E:\TEST\DATA_D-ORA10G_I-4030987615_TS-EXAMPLE_FNO-5_10M2H2G1

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00004 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DB

F

converted datafile=E:\TEST\DATA_D-ORA10G_I-4030987615_TS-UNDOTBS1_FNO-2_11M2H2G1

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:26

channel ORA_DISK_2: starting datafile conversion

input datafile fno=00006 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\TBSALERT.D

BF

converted datafile=E:\TEST\DATA_D-ORA10G_I-4030987615_TS-TBSALERT_FNO-6_13M2H2GR

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:03

converted datafile=E:\TEST\DATA_D-ORA10G_I-4030987615_TS-USERS_FNO-4_12M2H2GQ

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:05

Run SQL script E:\TEST\CRDB.SQL on the target platform to create database

Edit init.ora file E:\TEST\INIT_00M2H2DC_1_0.ORA. This PFILE will be used to cre

ate the database on the target platform

To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target plat

form

To change the internal database identifier, use DBNEWID Utility

Finished backup at 19-JAN-11

RMAN>

— CRDB1.SQL

—

— The following commands will create a new control file and use it

— to open the database.

— Data used by Recovery Manager will be lost.

— The contents of online logs will be lost and all backups will

— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL

— statement will place the database in the appropriate

— protection mode:

— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE=’E:\TEST1\INIT_00M4EOQP_1_0.ORA’

CREATE CONTROLFILE REUSE SET DATABASE “NORA10G” RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 SIZE 50M,

GROUP 2 SIZE 50M,

GROUP 3 SIZE 50M

DATAFILE

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-SYSTEM_FNO-1_2DM4EOQP’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-UNDOTBS1_FNO-2_2FM4EOTU’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-SYSAUX_FNO-3_2CM4EOQP’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-USERS_FNO-4_2GM4EOUN’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-EXAMPLE_FNO-5_2EM4EOT5’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-TBSALERT_FNO-6_2HM4EOV7’

CHARACTER SET WE8MSWIN1252

;

— Block change tracking was enabled, so re-enable it now.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\ORA10G\CHANGETRACKING\O1_MF_6M6ZBMQ7_.CHG’ REUSE;

— Set Database Guard and/or Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

— Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.

— Online tempfiles have complete space information.

— Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE

SIZE 588251136 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

— End of tempfile additions.

—

set echo off

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt * Your database has been created successfully!

prompt * There are many things to think about for the new database. Here

prompt * is a checklist to help you stay on track:

prompt * 1. You may want to redefine the location of the directory objects.

prompt * 2. You may want to change the internal database identifier (DBID)

prompt * or the global database name for this database. Use the

prompt * NEWDBID Utility (nid).

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE

STARTUP UPGRADE PFILE=’E:\TEST1\INIT_00M4EOQP_1_0.ORA’

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE=’E:\TEST1\INIT_00M4EOQP_1_0.ORA’

— The following step will recompile all PL/SQL modules.

— It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

# E:\TEST1\INIT_00M4EOQP_1_0.ORA

# Please change the values of the following parameters:

control_files = “E:\TEST1\CF_D-NORA10G_ID-4030987615_00M4EOQP”

db_create_file_dest = “E:\TEST1\ORA10G”

db_recovery_file_dest = “E:\TEST1\flash_recovery_area”

db_recovery_file_dest_size= 10737418240

service_names = “NORA10G”

service_names = “NORA10Gtihomir”

audit_file_dest = “E:\TEST1\ADUMP”

background_dump_dest = “E:\TEST1\BDUMP”

user_dump_dest = “E:\TEST1\UDUMP”

core_dump_dest = “E:\TEST1\CDUMP”

db_name = “NORA10G”

# Please review the values of the following parameters:

__shared_pool_size = 234881024

__large_pool_size = 4194304

__java_pool_size = 8388608

__streams_pool_size = 0

__db_cache_size = 687865856

remote_login_passwordfile= “EXCLUSIVE”

db_domain = “”

dispatchers = “(PROTOCOL=TCP) (SERVICE=ora10gXDB)”

_awr_flush_threshold_metrics= TRUE

# The values of the following parameters are from source database:

processes = 150

shared_pool_size = 0

large_pool_size = 0

java_pool_size = 0

streams_pool_size = 0

resource_manager_plan = “”

sga_target = 943718400

db_block_size = 8192

db_cache_size = 4194304

compatible = “10.2.0.3.0”

log_archive_format = “ARC%S_%R.%T”

db_file_multiblock_read_count= 16

fast_start_mttr_target = 260

log_checkpoints_to_alert = TRUE

db_flashback_retention_target= 5760

undo_management = “AUTO”

undo_tablespace = “UNDOTBS1”

undo_retention = 7200

smtp_out_server = “localhost:25”

job_queue_processes = 10

open_cursors = 300

pga_aggregate_target = 838860800

statistics_level = “ALL”

aq_tm_processes = 2

Annex A.2

RMAN> CONVERT DATABASE ON TARGET PLATFORM CONVERT SCRIPT ‘cnvt.sql’ TRANSPORT SCRIPT ‘crdb.sql’ NEW DATABASE ‘nora10g’ FORMAT ‘E:\test1\%U’;

Starting convert at 19-JAN-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=153 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=147 devtype=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.EM_TTS_DIR_OBJECT found in the database

Directory SYS.LOG_FILE_DIR found in the database

Directory SYS.DATA_FILE_DIR found in the database

Directory SYS.MEDIA_DIR found in the database

Directory SYS.SUBDIR found in the database

Directory SYS.XMLDIR found in the database

Directory SYS.ORACLECLRDIR found in the database

Directory SYS.WORK_DIR found in the database

Directory SYS.ADMIN_DIR found in the database

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

Directory SYS.DATA_PUMP_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

User SYSTEM with SYSDBA privilege found in password file

channel ORA_DISK_2: starting to check datafiles

input datafile fno=00003 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.D

BF

channel ORA_DISK_2: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_2: starting to check datafiles

input datafile fno=00001 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.D

BF

channel ORA_DISK_2: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_2: starting to check datafiles

input datafile fno=00005 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\EXAMPLE01.

DBF

channel ORA_DISK_2: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_2: starting to check datafiles

input datafile fno=00002 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.

DBF

channel ORA_DISK_2: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_2: starting to check datafiles

input datafile fno=00004 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DB

F

channel ORA_DISK_2: datafile checking complete, elapsed time: 00:00:00

channel ORA_DISK_2: starting to check datafiles

input datafile fno=00006 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\TBSALERT.D

BF

channel ORA_DISK_2: datafile checking complete, elapsed time: 00:00:00

Run SQL script E:\TEST1\CRDB.SQL on the target platform to create database

Edit init.ora file E:\TEST1\INIT_00M2H3GE_1_0.ORA. This PFILE will be used to cr

eate the database on the target platform

Run RMAN script E:\TEST1\CNVT.SQL on target platform to convert datafiles

To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target plat

form

To change the internal database identifier, use DBNEWID Utility

Finished backup at 19-JAN-11

RMAN>

— SRDB.SQL

—

— The following commands will create a new control file and use it

— to open the database.

— Data used by Recovery Manager will be lost.

— The contents of online logs will be lost and all backups will

— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL

— statement will place the database in the appropriate

— protection mode:

— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE=’E:\TEST1\INIT_00M4EP7B_1_0.ORA’

CREATE CONTROLFILE REUSE SET DATABASE “NORA10G” RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 SIZE 50M,

GROUP 2 SIZE 50M,

GROUP 3 SIZE 50M

DATAFILE

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-SYSTEM_FNO-1_2JM4EP7B’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-UNDOTBS1_FNO-2_2LM4EP7C’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-SYSAUX_FNO-3_2IM4EP7B’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-USERS_FNO-4_2MM4EP7C’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-EXAMPLE_FNO-5_2KM4EP7B’,

‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-TBSALERT_FNO-6_2NM4EP7C’

CHARACTER SET WE8MSWIN1252

;

— Block change tracking was enabled, so re-enable it now.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\ORA10G\CHANGETRACKING\O1_MF_6M6ZBMQ7_.CHG’ REUSE;

— Set Database Guard and/or Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

— Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.

— Online tempfiles have complete space information.

— Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE

SIZE 588251136 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

— End of tempfile additions.

—

set echo off

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt * Your database has been created successfully!

prompt * There are many things to think about for the new database. Here

prompt * is a checklist to help you stay on track:

prompt * 1. You may want to redefine the location of the directory objects.

prompt * 2. You may want to change the internal database identifier (DBID)

prompt * or the global database name for this database. Use the

prompt * NEWDBID Utility (nid).

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE

STARTUP UPGRADE PFILE=’E:\TEST1\INIT_00M4EP7B_1_0.ORA’

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE=’E:\TEST1\INIT_00M4EP7B_1_0.ORA’

— The following step will recompile all PL/SQL modules.

— It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

CNVT.SQL

RUN {

CONVERT DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.DBF’

FROM PLATFORM ‘Microsoft Windows x86 64-bit’

FORMAT ‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-SYSAUX_FNO-3_2IM4EP7B’;

CONVERT DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF’

FROM PLATFORM ‘Microsoft Windows x86 64-bit’

FORMAT ‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-SYSTEM_FNO-1_2JM4EP7B’;

CONVERT DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\EXAMPLE01.DBF’

FROM PLATFORM ‘Microsoft Windows x86 64-bit’

FORMAT ‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-EXAMPLE_FNO-5_2KM4EP7B’;

CONVERT DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.DBF’

FROM PLATFORM ‘Microsoft Windows x86 64-bit’

FORMAT ‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-UNDOTBS1_FNO-2_2LM4EP7C’;

CONVERT DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DBF’

FROM PLATFORM ‘Microsoft Windows x86 64-bit’

FORMAT ‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-USERS_FNO-4_2MM4EP7C’;

CONVERT DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\TBSALERT.DBF’

FROM PLATFORM ‘Microsoft Windows x86 64-bit’

FORMAT ‘E:\TEST1\DATA_D-ORA10G_I-4030987615_TS-TBSALERT_FNO-6_2NM4EP7C’;

}

INIT_00M4EP7B_1_0.ORA

# INIT_00M4EP7B_1_0.ORA

# Please change the values of the following parameters:

control_files = “E:\TEST1\CF_D-NORA10G_ID-4030987615_00M4EP7B”

db_create_file_dest = “E:\TEST1\ORA10G”

db_recovery_file_dest = “E:\TEST1\flash_recovery_area”

db_recovery_file_dest_size= 10737418240

service_names = “NORA10G”

service_names = “NORA10Gtihomir”

audit_file_dest = “E:\TEST1\ADUMP”

background_dump_dest = “E:\TEST1\BDUMP”

user_dump_dest = “E:\TEST1\UDUMP”

core_dump_dest = “E:\TEST1\CDUMP”

db_name = “NORA10G”

# Please review the values of the following parameters:

__shared_pool_size = 234881024

__large_pool_size = 4194304

__java_pool_size = 8388608

__streams_pool_size = 0

__db_cache_size = 687865856

remote_login_passwordfile= “EXCLUSIVE”

db_domain = “”

dispatchers = “(PROTOCOL=TCP) (SERVICE=ora10gXDB)”

_awr_flush_threshold_metrics= TRUE

# The values of the following parameters are from source database:

processes = 150

shared_pool_size = 0

large_pool_size = 0

java_pool_size = 0

streams_pool_size = 0

resource_manager_plan = “”

sga_target = 943718400

db_block_size = 8192

db_cache_size = 4194304

compatible = “10.2.0.3.0”

log_archive_format = “ARC%S_%R.%T”

db_file_multiblock_read_count= 16

fast_start_mttr_target = 260

log_checkpoints_to_alert = TRUE

db_flashback_retention_target= 5760

undo_management = “AUTO”

undo_tablespace = “UNDOTBS1”

undo_retention = 7200

smtp_out_server = “localhost:25”

job_queue_processes = 10

open_cursors = 300

pga_aggregate_target = 838860800

statistics_level = “ALL”

aq_tm_processes = 2

Annex B

RUN {

TRANSPORT TABLESPACE ‘TBSALERT’

AUXILIARY DESTINATION ‘e:\test’

DUMP FILE ‘tbsjfv.dmp’

EXPORT LOG ‘tbsjfv.log’

IMPORT SCRIPT ‘imptbsalert.sql’

TABLESPACE DESTINATION ‘e:\test1’

UNTIL TIME “to_date(‘February 12 2011,

02:15:00′,’Month dd yyyy, HH24:MI:SS’)”;

}

Recovery Manager: Release 11.2.0.2.0 – Production on Sat Feb 12 04:11:19 2011

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

connected to target database: ORCL (DBID=1270198833)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>

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 UNDOTBS1

Creating automatic instance, with SID=’artg’

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=artg_tspitr_ORCL

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=e:\test

log_archive_dest_1=’location=e:\test’

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2252568 bytes

Variable Size 100663528 bytes

Database Buffers 184549376 bytes

Redo Buffers 4812800 bytes

Automatic instance created

Removing automatic instance

shutting down automatic instance

Oracle instance shut down

Automatic instance removed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of transport tablespace command at 02/12/2011 04:12:26

RMAN-20202: Tablespace not found in the recovery catalog

RMAN-06019: could not translate tablespace name “TBSBF”

RMAN>

Starting backup at 12-FEB-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=193 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF

input datafile file number=00002 name=E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF

input datafile file number=00005 name=E:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF

input datafile file number=00003 name=E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF

input datafile file number=00006 name=E:\APP\ADMINISTRATOR\ORADATA\ORCL\ORCL\DATAFILE\O1_MF_TBSBF_6OCV1278_.DBF

input datafile file number=00004 name=E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 12-FEB-11

channel ORA_DISK_1: finished piece 1 at 12-FEB-11

piece handle=E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T041610_6OCVCCM2_.BKP tag=TAG20110212T041610 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 12-FEB-11

channel ORA_DISK_1: finished piece 1 at 12-FEB-11

piece handle=E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\BACKUPSET\2011_02_12\O1_MF_NCSNF_TAG20110212T041610_6OCVHXSQ_.BKP tag=TAG20110212T041610 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 12-FEB-11

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>

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 UNDOTBS1

Creating automatic instance, with SID=’ylFg’

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=ylFg_tspitr_ORCL

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=e:\test

log_archive_dest_1=’location=e:\test’

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2252568 bytes

Variable Size 100663528 bytes

Database Buffers 184549376 bytes

Redo Buffers 4812800 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 time “to_date(‘February 12 2011,04:19:00′,’Month dd yyyy, HH24:MI:SS’)”;

# 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 12-FEB-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=42 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 E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\BACKUPSET\2011_02_12\O1_MF_NCSNF_TAG20110212T041610_6OCVHXSQ_.BKP

channel ORA_AUX_DISK_1: piece handle=E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\BACKUPSET\2011_02_12\O1_MF_NCSNF_TAG20110212T041610_6OCVHXSQ_.BKP tag=TAG20110212T041610

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=E:\TEST\ORCL\CONTROLFILE\O1_MF_6OCVMQY4_.CTL

Finished restore at 12-FEB-11

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 time “to_date(‘February 12 2011,04:19:00′,’Month dd yyyy, HH24:MI:SS’)”;

# 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 6 to

“e:\test1\O1_MF_TBSBF_%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, 6;

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 E:\TEST\ORCL\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

Starting restore at 12-FEB-11

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 E:\TEST\ORCL\DATAFILE\O1_MF_SYSTEM_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00003 to E:\TEST\ORCL\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00002 to E:\TEST\ORCL\DATAFILE\O1_MF_SYSAUX_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00006 to e:\test1\O1_MF_TBSBF_%U_.DBF

channel ORA_AUX_DISK_1: reading from backup piece E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T041610_6OCVCCM2_.BKP

channel ORA_AUX_DISK_1: piece handle=E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T041610_6OCVCCM2_.BKP tag=TAG20110212T041610

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:26

Finished restore at 12-FEB-11

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=742882934 file name=E:\TEST\ORCL\DATAFILE\O1_MF_SYSTEM_6OCVN15V_.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=742882934 file name=E:\TEST\ORCL\DATAFILE\O1_MF_UNDOTBS1_6OCVN19G_.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=742882934 file name=E:\TEST\ORCL\DATAFILE\O1_MF_SYSAUX_6OCVN15Z_.DBF

datafile 6 switched to datafile copy

input datafile copy RECID=9 STAMP=742882935 file name=E:\TEST1\O1_MF_TBSBF_6OCVN19M_.DBF

contents of Memory Script:

{

# set requested point in time

set until time “to_date(‘February 12 2011,04:19:00′,’Month dd yyyy, HH24:MI:SS’)”;

# 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 6 online”;

# recover and open resetlogs

recover clone database tablespace “TBSBF”, “SYSTEM”, “UNDOTBS1”, “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 6 online

Starting recover at 12-FEB-11

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 27 is already on disk as file E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_02_12\O1_MF_1_27_6OCVMYBS_.ARC

archived log file name=E:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_02_12\O1_MF_1_27_6OCVMYBS_.ARC thread=1 sequence=27

media recovery complete, elapsed time: 00:00:02

Finished recover at 12-FEB-11

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone ‘alter tablespace “TBSBF” read only’;

# create directory for datapump export

sql clone “create or replace directory STREAMS_DIROBJ_DPDIR as ”

e:\test1””;

}

executing Memory Script

sql statement: alter tablespace “TBSBF” read only

sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ”e:\test1”

Performing export of metadata…

EXPDP> Starting “SYS”.”TSPITR_EXP_ylFg”:

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table “SYS”.”TSPITR_EXP_ylFg” successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_ylFg is:

EXPDP> E:\TEST1\TBSJFV.DMP

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace TBSBF:

EXPDP> E:\TEST1\O1_MF_TBSBF_6OCVN19M_.DBF

EXPDP> Job “SYS”.”TSPITR_EXP_ylFg” successfully completed at 04:24:29

Export completed

/*

The following command may be used to import the tablespaces.

Substitute values for <logon> and <directory>.

impdp <logon> directory=<directory> dumpfile= ‘tbsjfv.dmp’ transport_datafiles= E:\TEST1\O1_MF_TBSBF_6OCVN19M_.DBF

*/

————————————————————–

— Start of sample PL/SQL script for importing the tablespaces

————————————————————–

— creating directory objects

CREATE DIRECTORY STREAMS$DIROBJ$1 AS ‘E:\TEST1\’;

CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS ‘e:\test1’;

/* PL/SQL Script to import the exported tablespaces */

DECLARE

— the datafiles

tbs_files dbms_streams_tablespace_adm.file_set;

cvt_files dbms_streams_tablespace_adm.file_set;

— the dumpfile to import

dump_file dbms_streams_tablespace_adm.file;

dp_job_name VARCHAR2(30) := NULL;

— names of tablespaces that were imported

ts_names dbms_streams_tablespace_adm.tablespace_set;

BEGIN

— dump file name and location

dump_file.file_name := ‘tbsjfv.dmp’;

dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;

— forming list of datafiles for import

tbs_files( 1).file_name := ‘O1_MF_TBSBF_6OCVN19M_.DBF’;

tbs_files( 1).directory_object := ‘STREAMS$DIROBJ$1’;

— import tablespaces

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);

— output names of imported tablespaces

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;

/

— dropping directory objects

DROP DIRECTORY STREAMS$DIROBJ$1;

DROP DIRECTORY STREAMS$DIROBJ$DPDIR;

————————————————————–

— End of sample PL/SQL script

————————————————————–

Removing automatic instance

shutting down automatic instance

database closed

database dismounted

Oracle instance shut down

Automatic instance removed

auxiliary instance file E:\TEST\ORCL\DATAFILE\O1_MF_TEMP_6OCVQ61O_.TMP deleted

auxiliary instance file E:\TEST\ORCL\ONLINELOG\O1_MF_3_6OCVQ0VM_.LOG deleted

auxiliary instance file E:\TEST\ORCL\ONLINELOG\O1_MF_2_6OCVPYRZ_.LOG deleted

auxiliary instance file E:\TEST\ORCL\ONLINELOG\O1_MF_1_6OCVPWX9_.LOG deleted

auxiliary instance file E:\TEST\ORCL\DATAFILE\O1_MF_SYSAUX_6OCVN15Z_.DBF deleted

auxiliary instance file E:\TEST\ORCL\DATAFILE\O1_MF_UNDOTBS1_6OCVN19G_.DBF deleted

auxiliary instance file E:\TEST\ORCL\DATAFILE\O1_MF_SYSTEM_6OCVN15V_.DBF deleted

auxiliary instance file E:\TEST\ORCL\CONTROLFILE\O1_MF_6OCVMQY4_.CTL deleted

Export log file

Starting “SYS”.”TSPITR_EXP_ylFg”:

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table “SYS”.”TSPITR_EXP_ylFg” successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.TSPITR_EXP_ylFg is:

E:\TEST1\TBS.DMP

******************************************************************************

Datafiles required for transportable tablespace TBSBF:

E:\TEST1\O1_MF_TBSBF_6OCVN19M_.DBF

Job “SYS”.”TSPITR_EXP_ylFg” successfully completed at 04:24:29

Import script imptbsalert.sql

/*

The following command may be used to import the tablespaces.

Substitute values for <logon> and <directory>.

impdp <logon> directory=<directory> dumpfile= ‘tbs.dmp’ transport_datafiles= E:\TEST1\O1_MF_TBSBF_6OCVN19M_.DBF

*/

————————————————————–

— Start of sample PL/SQL script for importing the tablespaces

————————————————————–

— creating directory objects

CREATE DIRECTORY STREAMS$DIROBJ$1 AS ‘E:\TEST1\’;

CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS ‘e:\test1’;

/* PL/SQL Script to import the exported tablespaces */

DECLARE

— the datafiles

tbs_files dbms_streams_tablespace_adm.file_set;

cvt_files dbms_streams_tablespace_adm.file_set;

— the dumpfile to import

dump_file dbms_streams_tablespace_adm.file;

dp_job_name VARCHAR2(30) := NULL;

— names of tablespaces that were imported

ts_names dbms_streams_tablespace_adm.tablespace_set;

BEGIN

— dump file name and location

dump_file.file_name := ‘tbs.dmp’;

dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;

— forming list of datafiles for import

tbs_files( 1).file_name := ‘O1_MF_TBSBF_6OCVN19M_.DBF’;

tbs_files( 1).directory_object := ‘STREAMS$DIROBJ$1’;

— import tablespaces

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);

— output names of imported tablespaces

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;

/

— dropping directory objects

DROP DIRECTORY STREAMS$DIROBJ$1;

DROP DIRECTORY STREAMS$DIROBJ$DPDIR;

————————————————————–

— End of sample PL/SQL script

————————————————————–

Annex C.

run {

sql ‘alter system archive log current’;

recover tablespace ‘TSPITR’ until time “to_date(‘2011-02-12 22:55:00’, ‘YYYY-MM-DD HH24:MI:SS’)” auxiliary destination = ‘e:\oracle11gr2\auxdir’;

backup tablespace ‘TSPITR’;

sql ‘alter tablespace “TSPITR” online’;

}

RMAN> 2> 3> 4> 5> 6>

using target database control file instead of recovery catalog

sql statement: alter system archive log current

Starting recover at 12-FEB-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=226 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=227 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=7 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=221 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=37 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/12/2011 22:55:39

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

RMAN> 2> 3> 4> 5> 6>

sql statement: alter system archive log current

Starting recover at 12-FEB-11

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

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 UNDOTBS1

Creating automatic instance, with SID=’iDhn’

initialization parameters used for automatic instance:

db_name=ORCL11GR

db_unique_name=iDhn_tspitr_ORCL11GR

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=e:\oracle11gr2\auxdir

log_archive_dest_1=’location=e:\oracle11gr2\auxdir’

#No auxiliary parameter file used

starting up automatic instance ORCL11GR

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2175128 bytes

Variable Size 100667240 bytes

Database Buffers 184549376 bytes

Redo Buffers 4886528 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 time “to_date(‘2011-02-12 22:55:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;

# 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’;

# avoid unnecessary autobackups for structural changes during TSPITR

sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;

}

executing Memory Script

executing command: SET until clause

Starting restore at 12-FEB-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=42 device type=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: SID=55 device type=DISK

allocated channel: ORA_AUX_DISK_3

channel ORA_AUX_DISK_3: SID=68 device type=DISK

allocated channel: ORA_AUX_DISK_4

channel ORA_AUX_DISK_4: SID=81 device type=DISK

allocated channel: ORA_AUX_DISK_5

channel ORA_AUX_DISK_5: SID=95 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 E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\AUTOBACKUP\2011_02_12\O1_MF_S_742949419_6OFWNDQF_.BKP

channel ORA_AUX_DISK_1: piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\AUTOBACKUP\2011_02_12\O1_MF_S_742949419_6OFWNDQF_.BKP tag=TAG20110212T225019

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=E:\ORACLE11GR2\AUXDIR\ORCL11GR2\CONTROLFILE\O1_MF_6OFX44GY_.CTL

Finished restore at 12-FEB-11

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until time “to_date(‘2011-02-12 22:55:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;

plsql <<<– tspitr_2

declare

sqlstatement varchar2(512);

offline_not_needed exception;

pragma exception_init(offline_not_needed, -01539);

begin

sqlstatement := ‘alter tablespace ‘|| ‘”TSPITR”‘ ||’ offline immediate’;

krmicd.writeMsg(6162, sqlstatement);

krmicd.execSql(sqlstatement);

exception

when offline_not_needed then

null;

end; >>>;

# 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 6 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 7 to

“E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\TSPITR.DBF”;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 6, 2, 7;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

sql statement: alter tablespace “TSPITR” offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

Starting restore at 12-FEB-11

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

using channel ORA_AUX_DISK_3

using channel ORA_AUX_DISK_4

using channel ORA_AUX_DISK_5

channel ORA_AUX_DISK_1: restoring datafile 00002

input datafile copy RECID=627 STAMP=742874515 file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\DATAFILE\O1_MF_SYSAUX_6GVBKN1L_.DBF

destination for restore of datafile 00002: E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSAUX_%U_.DBF

channel ORA_AUX_DISK_2: starting datafile backup set restore

channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_2: restoring datafile 00003 to E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF

channel ORA_AUX_DISK_2: restoring datafile 00007 to E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\TSPITR.DBF

channel ORA_AUX_DISK_2: reading from backup piece E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T223356_6OFVOPKQ_.BKP

channel ORA_AUX_DISK_3: starting datafile backup set restore

channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_3: restoring datafile 00006 to E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF

channel ORA_AUX_DISK_3: reading from backup piece E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T223356_6OFVOV7W_.BKP

channel ORA_AUX_DISK_4: starting datafile backup set restore

channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_4: restoring datafile 00001 to E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSTEM_%U_.DBF

channel ORA_AUX_DISK_4: reading from backup piece E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T223356_6OFVPK0W_.BKP

channel ORA_AUX_DISK_4: piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T223356_6OFVPK0W_.BKP tag=TAG20110212T223356

channel ORA_AUX_DISK_4: restored backup piece 1

channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:04:18

channel ORA_AUX_DISK_3: piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T223356_6OFVOV7W_.BKP tag=TAG20110212T223356

channel ORA_AUX_DISK_3: restored backup piece 1

channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:14:44

channel ORA_AUX_DISK_2: piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T223356_6OFVOPKQ_.BKP tag=TAG20110212T223356

channel ORA_AUX_DISK_2: restored backup piece 1

channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:14:54

channel ORA_AUX_DISK_1: copied datafile copy of datafile 00002

output file name=E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSAUX_6OFX4LDP_.DBF RECID=631 STAMP=742950859

Finished restore at 12-FEB-11

datafile 1 switched to datafile copy

input datafile copy RECID=632 STAMP=742950863 file name=E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSTEM_6OFX4R6Y_.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=633 STAMP=742950863 file name=E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_UNDOTBS1_6OFX4M1R_.DBF

datafile 6 switched to datafile copy

input datafile copy RECID=634 STAMP=742950864 file name=E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_UNDOTBS1_6OFX4OQT_.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=635 STAMP=742950864 file name=E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSAUX_6OFX4LDP_.DBF

contents of Memory Script:

{

# set requested point in time

set until time “to_date(‘2011-02-12 22:55:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;

# 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 6 online”;

sql clone “alter database datafile 2 online”;

sql clone “alter database datafile 7 online”;

# recover and open resetlogs

recover clone database tablespace “TSPITR”, “SYSTEM”, “UNDOTBS1”, “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 6 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 7 online

Starting recover at 12-FEB-11

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

using channel ORA_AUX_DISK_3

using channel ORA_AUX_DISK_4

using channel ORA_AUX_DISK_5

channel ORA_AUX_DISK_1: starting incremental datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00002: E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSAUX_6OFX4LDP_.DBF

channel ORA_AUX_DISK_1: reading from backup piece E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNND1_ORA_OEM_LEVEL_0_6OCMJPNH_.BKP

channel ORA_AUX_DISK_1: piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNND1_ORA_OEM_LEVEL_0_6OCMJPNH_.BKP tag=ORA_OEM_LEVEL_0

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45

starting media recovery

archived log for thread 1 with sequence 1069 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1069_6OCO2BOM_.ARC

archived log for thread 1 with sequence 1070 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1070_6OCPP0GK_.ARC

archived log for thread 1 with sequence 1071 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1071_6OCS2K7D_.ARC

archived log for thread 1 with sequence 1072 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1072_6OCYY5S9_.ARC

archived log for thread 1 with sequence 1073 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1073_6OD1JCCF_.ARC

archived log for thread 1 with sequence 1074 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1074_6OD1Y1Y3_.ARC

archived log for thread 1 with sequence 1075 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1075_6OD4YSLJ_.ARC

archived log for thread 1 with sequence 1076 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1076_6OF6WSLM_.ARC

archived log for thread 1 with sequence 1077 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1077_6OF7TH75_.ARC

archived log for thread 1 with sequence 1078 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1078_6OFCNHBW_.ARC

archived log for thread 1 with sequence 1079 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1079_6OFJXW2Z_.ARC

archived log for thread 1 with sequence 1080 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1080_6OFNGZCO_.ARC

archived log for thread 1 with sequence 1081 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1081_6OFNK42D_.ARC

archived log for thread 1 with sequence 1082 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1082_6OFNO9O7_.ARC

archived log for thread 1 with sequence 1083 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1083_6OFOPZON_.ARC

archived log for thread 1 with sequence 1084 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1084_6OFPPTXX_.ARC

archived log for thread 1 with sequence 1085 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1085_6OFQDWKY_.ARC

archived log for thread 1 with sequence 1086 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1086_6OFQX856_.ARC

archived log for thread 1 with sequence 1087 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1087_6OFQZP7H_.ARC

archived log for thread 1 with sequence 1088 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1088_6OFWN724_.ARC

archived log for thread 1 with sequence 1089 is already on disk as file E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1089_6OFWY9JK_.ARC

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1069_6OCO2BOM_.ARC thread=1 sequence=1069

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1070_6OCPP0GK_.ARC thread=1 sequence=1070

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1071_6OCS2K7D_.ARC thread=1 sequence=1071

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1072_6OCYY5S9_.ARC thread=1 sequence=1072

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1073_6OD1JCCF_.ARC thread=1 sequence=1073

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1074_6OD1Y1Y3_.ARC thread=1 sequence=1074

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1075_6OD4YSLJ_.ARC thread=1 sequence=1075

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1076_6OF6WSLM_.ARC thread=1 sequence=1076

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1077_6OF7TH75_.ARC thread=1 sequence=1077

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1078_6OFCNHBW_.ARC thread=1 sequence=1078

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1079_6OFJXW2Z_.ARC thread=1 sequence=1079

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1080_6OFNGZCO_.ARC thread=1 sequence=1080

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1081_6OFNK42D_.ARC thread=1 sequence=1081

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1082_6OFNO9O7_.ARC thread=1 sequence=1082

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1083_6OFOPZON_.ARC thread=1 sequence=1083

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1084_6OFPPTXX_.ARC thread=1 sequence=1084

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1085_6OFQDWKY_.ARC thread=1 sequence=1085

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1086_6OFQX856_.ARC thread=1 sequence=1086

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1087_6OFQZP7H_.ARC thread=1 sequence=1087

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1088_6OFWN724_.ARC thread=1 sequence=1088

archived log file name=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\ARCHIVELOG\2011_02_12\O1_MF_1_1089_6OFWY9JK_.ARC thread=1 sequence=1089

media recovery complete, elapsed time: 00:02:26

Finished recover at 12-FEB-11

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone ‘alter tablespace “TSPITR” read only’;

# create directory for datapump import

sql “create or replace directory TSPITR_DIROBJ_DPDIR as ”

e:\oracle11gr2\auxdir””;

# create directory for datapump export

sql clone “create or replace directory TSPITR_DIROBJ_DPDIR as ”

e:\oracle11gr2\auxdir””;

}

executing Memory Script

sql statement: alter tablespace “TSPITR” read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”e:\oracle11gr2\auxdir”

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”e:\oracle11gr2\auxdir”

Performing export of metadata…

EXPDP> Starting “SYS”.”TSPITR_EXP_iDhn”:

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table “SYS”.”TSPITR_EXP_iDhn” successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_iDhn is:

EXPDP> E:\ORACLE11GR2\AUXDIR\TSPITR_IDHN_72700.DMP

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace TSPITR:

EXPDP> E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\TSPITR.DBF

EXPDP> Job “SYS”.”TSPITR_EXP_iDhn” successfully completed at 23:20:36

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql ‘drop tablespace “TSPITR” including contents keep datafiles’;

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

sql statement: drop tablespace “TSPITR” including contents keep datafiles

Performing import of metadata…

IMPDP> Master table “SYS”.”TSPITR_IMP_iDhn” successfully loaded/unloaded

IMPDP> Starting “SYS”.”TSPITR_IMP_iDhn”:

IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

IMPDP> Job “SYS”.”TSPITR_IMP_iDhn” successfully completed at 23:21:02

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql ‘alter tablespace “TSPITR” read write’;

sql ‘alter tablespace “TSPITR” offline’;

# enable autobackups after TSPITR is finished

sql ‘begin dbms_backup_restore.AutoBackupFlag(TRUE); end;’;

}

executing Memory Script

sql statement: alter tablespace “TSPITR” read write

sql statement: alter tablespace “TSPITR” offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_TEMP_6OFY828X_.TMP deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\ONLINELOG\O1_MF_3_6OFY7WK3_.LOG deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\ONLINELOG\O1_MF_2_6OFY7TWQ_.LOG deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\ONLINELOG\O1_MF_1_6OFY7S5T_.LOG deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSAUX_6OFX4LDP_.DBF deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_UNDOTBS1_6OFX4OQT_.DBF deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_UNDOTBS1_6OFX4M1R_.DBF deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\DATAFILE\O1_MF_SYSTEM_6OFX4R6Y_.DBF deleted

auxiliary instance file E:\ORACLE11GR2\AUXDIR\ORCL11GR2\CONTROLFILE\O1_MF_6OFX44GY_.CTL deleted

Finished recover at 12-FEB-11

Starting backup at 12-FEB-11

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\TSPITR.DBF

channel ORA_DISK_1: starting piece 1 at 12-FEB-11

channel ORA_DISK_1: finished piece 1 at 12-FEB-11

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\2011_02_12\O1_MF_NNNDF_TAG20110212T232105_6OFYG1N6_.BKP tag=TAG20110212T232105 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 12-FEB-11

Starting Control File and SPFILE Autobackup at 12-FEB-11

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\AUTOBACKUP\2011_02_12\O1_MF_S_742951268_6OFYG6CM_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 12-FEB-11

sql statement: alter tablespace “TSPITR” online

February 12, 2011 - Posted by gjilevski | oracle