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)
-
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.
- Open the target database in read only mode.
- Execute against the target database dbms_tdb.check_db to verify that the database can be transported.
- 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.
- Issue the CONVERT DATABASE on the source database.
- Copy the datafile, pfile and generated scripts to the target database.
- Create password file on the target database.
- Start the target database instance using the copied pfile.
- Convert the database file if the conversion is to be done on the target. Skip this step if the conversion is already done.
- Recreate the control file and redo log files executing the transport script.
- 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.
-
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.
-
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
1 Comment »
Leave a Reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
[…] between file system and ASM. Files need to be transferred in cases of transportable tablespaces , transportable database or copying backup files to name a few. You will look at the following […]