oracle 可传输的表空间:rman
2012-06-12 10:08 java环境变量 阅读(240) 评论(0) 编辑 收藏 举报实验环境:
源数据库服务器名: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