拷贝oracle表空间,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;

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 从源数据库拷贝数据

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