实战:oracle 可传输的表空间:rman

实验环境：
源数据库服务器名：beijing    数据库全局名和SID：orcl

辅助数据库:suzhou 只安装数据库软件，没有新建数据库

在辅助服务器suzhou上复制一个例程名和数据库名都是bj的数据库.

Source database                                       Duplicate database

SYSTEM: windows server 2008 R2                        SYSTEM: windows server 2008 R2

IP ADDRESS:192.168.2.188                              IP ADDRESS:192.168.2.199

HOST NAME:beijing                                     HOST NAME:suzhou

ORACLE SID: orcl                                      ORACLE SID: orcl

TNSNAMES:bj                                           TNSNAMES:sz

安装盘符:F盘                  安装盘符:E盘

------------------------------------------------------
0.源数据库服务器上新建备份目录

mkdir f:\backup

mkdir f:\dump

1.源数据库和目标数据库检测

set oracle_sid=orcl

col name heading '实例名' for a10
col version heading '数据库版本' for a15
col platform_name heading '操作系统平台' for a30
col endian_format heading '字节顺序' for a15

SELECT d.NAME, i.VERSION, d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d, V$INSTANCE i
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME
and d.DB_UNIQUE_NAME = i.INSTANCE_NAME;

 

2.源数据库新建测试表空间和用户

create tablespace ocp
datafile 'F:\app\Administrator\oradata\orcl\ocp01.dbf'
size 100m
autoextend on next 10m maxsize unlimited
extent management local autoallocate
segment space management auto;

create user test identified by password
default tablespace ocp
temporary tablespace temp;

grant dba to test;

 

conn test/password@orcl

create table t1
(
sid int not null primary key,
sname varchar2(10)
);

insert into t1 values(101,'wind');
insert into t1 values(102,'snow');
insert into t1 values(103,'apple');

 

commit;

select table_name from dba_tables where tablespace_name='OCP';

 

3.源数据库检测是否自包含

conn sys/password@orcl as sysdba  --需要sys账户

exec dbms_tts.transport_set_check('OCP', TRUE , TRUE);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

 

4.新建目录

connect / as sysdba

create directory dump as 'f:\dump'; --必须创建此目录 f:\dump

drop directory dump;--删除

exit;

5.使用rman备份源数据库

rman>

run {
configure retention policy to recovery window of 14 days;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to 'F:\backup\bak_%F';
allocate channel c1 device type disk format  'F:\backup\bak_%u';
allocate channel c2 device type disk format 'F:\backup\bak_%u';
backup database skip inaccessible
plus archivelog filesperset 20
delete all input;
release channel c1;
release channel c2;
}
allocate channel for maintenance device type disk;
crosscheck backupset;
delete noprompt obsolete;

 

6.源数据库上准备传输集

rman>

transport tablespace "OCP"
tablespace destination 'f:\dump'
auxiliary destination 'f:\dump'
datapump directory "dump"
dump file "ocp.dmp"
import script "impocpscript.sql"
export log "expocplog.log";

此时需要关闭sqlplus中其它终端回话.

-------------------------------------------常见错误

错误1:

使用 SID='sqcx' 创建自动实例

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: recover 命令 (在 03/18/2010 14:18:03 上) 失败
ORA-19852: 创建辅助实例 sqcx 的服务时出错 (错误 0)
ORA-27302: 错误发生在:
ORA-27303: 附加信息: failed to start instance

解决办法:
exec sys.dbms_backup_restore.manageAuxInstance('TSPITR', 1);

 

错误2:
启动自动实例 ORCL

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: transport tablespace 命令 (在 11/11/2011 17:44:47 上) 失败
RMAN-04014: 启动失败: ORA-01261: Parameter db_create_file_dest destination strin
g cannot be translated
ORA-01263: Name given for file destination directory is invalid
OSD-04018: ??????????????????????????
O/S-Error: (OS 2) ??????????????????????

 

解决办法:如果指定了auxiliary destination该参数，那么会包含两个参数
db_create_file_dest和control_files，默认位置都和auxiliary destination这个相同.
如果该路径无效，则报错.

 

 

-------------------------

 

 

5.目标数据库

5.1 新建目录

sqlplus / as sysdba

create directory dump as 'e:\dump'  --必须创建此目录 e:\dump

exit;

5.2 从源数据库拷贝数据

xcopy \\192.168.2.188\f$\dump\*.*  e:\dump  /E

copy e:\dump\ocp01.dbf   E:\app\Administrator\oradata\orcl\ocp01.dbf 

--建议将数据文件拷贝到默认位置，否则以后数据文件将保存在e:\dump下

5.3导入到目标数据库

sqlplus / as sysdba

@e:\dump\impocpscript.sql

---------常见错误如下

修改其中路劲，否则报错

DECLARE
*
第 1 行出现错误:
ORA-06512: 在 "SYS.DBMS_STREAMS_TABLESPACE_ADM", line 1854
ORA-06512: 在 line 18