Transport_Tablespace-RMAN
将192.168.3.199数据库下,chenjc用户下的t1表,导入到192.168.3.198数据库下,chenjc用户下;
一 查看操作系统版本,数据库版本
192.168.3.199
[oracle@ogg1 ~]$ cat /etc/issue
Oracle Linux Server release 6.3
SQL> select * from v$version where rownum<=2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
192.168.3.198
[oracle@ogg2 orcl]$ cat /etc/issue
Oracle Linux Server release 6.3
SQL> select * from v$version where rownum<=2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
二 创建测试表空间,测试用户,测试表
192.168.3.199
SQL> create tablespace chenjc datafile '/u01/app/oracle/oradata/orcl/chenjc01.dbf' size 30m autoextend on;
Tablespace created.
SQL> create user chenjc identified by chenjc default tablespace chenjc;
User created.
SQL> grant connect,resource,dba to chenjc;
Grant succeeded.
SQL> conn chenjc/chenjc
Connected.
SQL> create table t1 as select level id,sysdate as t_date from dual connect by level<=100000;
Table created.
三 检查准备迁移的表空间是否自包含
SQL> conn /as sysdba
Connected.
SQL> execute dbms_tts.transport_set_check(ts_list=>'CHENJC',incl_constraints=>TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
/*无返回记录,说明符合传输表空间条件*/
四 创建备份文件
RMAN> backup database plus archivelog delete input;
五 通过RMAN工具导出所要传输表空间的原数据以及脚本
RMAN> transport tablespace chenjc tablespace destination '/home/oracle' auxiliary destination '/home/oracle';
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='bEyE'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=bEyE_tspitr_ORCL
compatible=11.2.0.3.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/home/oracle
log_archive_dest_1='location=/home/oracle'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 292278272 bytes
Fixed Size 2227744 bytes
Variable Size 100663776 bytes
Database Buffers 184549376 bytes
Redo Buffers 4837376 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
contents of Memory Script:
{
# set requested point in time
set until scn 327263;
# 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 03-AUG-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 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 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_ncnnf_TAG20150803T131502_bvxy776x_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_ncnnf_TAG20150803T131502_bvxy776x_.bkp tag=TAG20150803T131502
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/ORCL/controlfile/o1_mf_bvxzhyb7_.ctl
Finished restore at 03-AUG-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until scn 327263;
# 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
"/home/oracle/chenjc01.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 /home/oracle/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 03-AUG-15
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 /home/oracle/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /home/oracle/chenjc01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_nnndf_TAG20150803T131502_bvxy6ptl_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_nnndf_TAG20150803T131502_bvxy6ptl_.bkp tag=TAG20150803T131502
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 03-AUG-15
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=886772243 file name=/home/oracle/ORCL/datafile/o1_mf_system_bvxzj4hj_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=886772243 file name=/home/oracle/ORCL/datafile/o1_mf_undotbs1_bvxzj4hn_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=886772243 file name=/home/oracle/ORCL/datafile/o1_mf_sysaux_bvxzj4j3_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=886772243 file name=/home/oracle/chenjc01.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 327263;
# 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 "CHENJC", "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 03-AUG-15
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=71
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_annnn_TAG20150803T131520_bvxy78t9_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_annnn_TAG20150803T131520_bvxy78t9_.bkp tag=TAG20150803T131520
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/1_71_885838407.dbf thread=1 sequence=71
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/1_71_885838407.dbf RECID=8 STAMP=886772244
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-AUG-15
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace CHENJC read only';
# create directory for datapump export
sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''
/home/oracle''";
}
executing Memory Script
sql statement: alter tablespace CHENJC read only
sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/home/oracle''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_bEyE":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_bEyE" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_bEyE is:
EXPDP> /home/oracle/dmpfile.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace CHENJC:
EXPDP> /home/oracle/chenjc01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_bEyE" successfully completed at 13:38:11
Export completed
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /home/oracle/chenjc01.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/home/oracle/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/home/oracle';
/* 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 := 'dmpfile.dmp';
dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'chenjc01.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 /home/oracle/ORCL/datafile/o1_mf_temp_bvxzjrfs_.tmp deleted
auxiliary instance file /home/oracle/ORCL/onlinelog/o1_mf_3_bvxzjq44_.log deleted
auxiliary instance file /home/oracle/ORCL/onlinelog/o1_mf_2_bvxzjpoq_.log deleted
auxiliary instance file /home/oracle/ORCL/onlinelog/o1_mf_1_bvxzjp71_.log deleted
auxiliary instance file /home/oracle/ORCL/datafile/o1_mf_sysaux_bvxzj4j3_.dbf deleted
auxiliary instance file /home/oracle/ORCL/datafile/o1_mf_undotbs1_bvxzj4hn_.dbf deleted
auxiliary instance file /home/oracle/ORCL/datafile/o1_mf_system_bvxzj4hj_.dbf deleted
auxiliary instance file /home/oracle/ORCL/controlfile/o1_mf_bvxzhyb7_.ctl deleted
[oracle@ogg1 ~]$ ll -rth
......
drwxr-x--- 5 oracle oinstall 4.0K Aug 3 13:37 ORCL
-rw-r----- 1 oracle oinstall 31M Aug 3 13:37 chenjc01.dbf
-rw-r----- 1 oracle oinstall 88K Aug 3 13:38 dmpfile.dmp
-rw-r--r-- 1 oracle oinstall 2.0K Aug 3 13:38 impscrpt.sql
/*
模拟平台转换(同一平台传输不需要这步)
SQL> col platform_name for a35
SQL> select * from v$transportable_platform order by platform_id;
RMAN>convert tablespace "TESTSPACE" to platform 'Microsoft Windows IA (32-bit)' format 'd:\TESTSPACE01.DBF' --这个是转换的目标地址
*/
六 将数据库文件和导出的表空间原文件复制到192.168.3.198服务器
[oracle@ogg1 ~]$ scp chenjc01.dbf dmpfile.dmp impscrpt.sql 192.168.3.198:/home/oracle/
七 目标数据库创建用户,指定表空间(目标数据库不能有和将要传输表空间同名的表空间)
192.168.3.198
SQL> drop tablespace chenjc including contents and datafiles;
Tablespace dropped.
SQL> drop user chenjc cascade;
User dropped.
SQL> create user chenjc identified by chenjc default tablespace users;
User created.
SQL> grant connect,resource,dba to chenjc;
Grant succeeded.
八 通过@impscrpt.sql脚本导入表空间
SQL> @impscrpt.sql
Directory created.
Directory created.
PL/SQL procedure successfully completed.
Directory dropped.
Directory dropped.
SQL> select name from v$dbfile;
NAME
--------------------------------------------------------------------------------
。。。。。。
/home/oracle/chenjc01.dbf
6 rows selected.
九 修改用户默认表空间
SQL> alter user chenjc default tablespace chenjc;
User altered.
十 查看
SQL> conn chenjc/chenjc
SQL> select id,to_char(t_date,'yyyy-mm-dd hh24:mi:ss') from t1 where rownum<=3;
ID TO_CHAR(T_DATE,'YYY
---------- -------------------
1 2015-08-03 09:27:01
2 2015-08-03 09:27:01
3 2015-08-03 09:27:01
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-1760405/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-1760405/