个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2011-01-10 16:13:48
步骤很简单：关源库->拷贝到目标机器->启动到mount状态重命名->open数据库
登录源数据库所在主机
建立映射目录 映射为 z: 盘
映射完毕后检查是否允许写入文件
需要注意关闭目标数据库位置服务器的防火墙
建立映射目录 映射为 z: 盘
映射完毕后检查是否允许写入文件
需要注意关闭目标数据库位置服务器的防火墙
搜集源库信息
实例名、版本、块大小、表空间、数据文件
select * from v$instance;
select * from v$database;
select * from v$tablespace;
select * from v$datafile;
实例名、版本、块大小、表空间、数据文件
select * from v$instance;
select * from v$database;
select * from v$tablespace;
select * from v$datafile;
在源库上整理需拷贝的文件
执行文件cp.sql，其内容如下：
set linesize 130 pagesize 2000
set trimspool on
set echo off
set verify off
set timing off
set feedback off
set head off
set echo off
set trimspool on
set echo off
set verify off
set timing off
set feedback off
set head off
set echo off
spool cpfile.bat
select 'copy '||name||' z:\orcl\'|| substr(name, instr(name, '\', -1) 1) cmd from v$controlfile
union all
select 'copy '||member||' z:\orcl\'|| substr(member, instr(member, '\', -1) 1) cmd from v$logfile
union all
select 'copy '||name||' z:\orcl\'|| substr(name, instr(name, '\', -1) 1) cmd from v$datafile
union all
select 'copy '||name||' z:\orcl\'|| substr(name, instr(name, '\', -1) 1) cmd from v$tempfile
union all
select 'copy '||value||' z:\orcl\'|| substr(value, instr(value, '\', -1) 1) cmd from v$parameter where name='spfile';
union all
select 'copy '||member||' z:\orcl\'|| substr(member, instr(member, '\', -1) 1) cmd from v$logfile
union all
select 'copy '||name||' z:\orcl\'|| substr(name, instr(name, '\', -1) 1) cmd from v$datafile
union all
select 'copy '||name||' z:\orcl\'|| substr(name, instr(name, '\', -1) 1) cmd from v$tempfile
union all
select 'copy '||value||' z:\orcl\'|| substr(value, instr(value, '\', -1) 1) cmd from v$parameter where name='spfile';
spool off
生成改名脚本
执行文件ren.sql ，其内容如下：
set linesize 130 pagesize 2000
set trimspool on
set echo off
set verify off
set timing off
set feedback off
set head off
set echo off
set trimspool on
set echo off
set verify off
set timing off
set feedback off
set head off
set echo off
spool renfile.sql
select 'alter database rename file '''||member||''' to ''d:\oradata\orcl\'|| substr(member, instr(member, '\', -1) 1)||'''; ' cmd from v$logfile
union all
select 'alter database rename file '''||name||''' to ''d:\oradata\orcl\'|| substr(name, instr(name, '\', -1) 1)||'''; ' cmd from v$datafile
union all
select 'alter database rename file '''||name||''' to ''d:\oradata\orcl\'|| substr(name, instr(name, '\', -1) 1)||'''; ' cmd from v$tempfile;
union all
select 'alter database rename file '''||name||''' to ''d:\oradata\orcl\'|| substr(name, instr(name, '\', -1) 1)||'''; ' cmd from v$datafile
union all
select 'alter database rename file '''||name||''' to ''d:\oradata\orcl\'|| substr(name, instr(name, '\', -1) 1)||'''; ' cmd from v$tempfile;
spool off
create pfile from spfile;
关闭源库
host lsnrctl stop
shutdown immediate
将windows服务设置为手工启动（关键！！！ 防止万一复制出现问题还可以重新复制一致的文件）
host lsnrctl stop
shutdown immediate
将windows服务设置为手工启动（关键！！！ 防止万一复制出现问题还可以重新复制一致的文件）
拷贝控制文件、日志文件、数据文件，
host cpfile.bat
startup
host lsnrctl start
host lsnrctl start
拷贝pfile、 tnsname.ora、 口令文件、renfile.sql
在目标数据库修改参数文件中的控制文件内容
配置启动参数文件pfile,修改控制文件位置及路径信息
配置启动参数文件pfile,修改控制文件位置及路径信息
启动到mount状态
sqlplus "/as sysdba"
startup mount pfile=?/database/initorcl.ora
sqlplus "/as sysdba"
startup mount pfile=?/database/initorcl.ora
修改文件位置
sql> @renfile.sql
sql> @renfile.sql
打开数据库
create spfile from pfile;
host lsnrctl start
create spfile from pfile;
host lsnrctl start
检查临时文件是否有
连接测试
参考命令：
oradim -new -sid test -startmode manual -pfile "d:\oracle\admin\test\pfile\inittest.ora"
alter database rename file 'c:\oracle\product\oradata\example.dbf'
to 'c:\oracle\product\oradata\demos.dbf'
to 'c:\oracle\product\oradata\demos.dbf'
修改日志组位置
alter datbase [database}
rename file 'filename’[,'filename']…
to 'filename']…
创建日志组
alter database add logfile group 3
('$home/oradata/u01/log3a.rdo',
'$home/oradata/u02/log3b.rdo')
size 1m;
添加成员
alter database add logfile member
'$home/oradata/u04/log1c.rdo' to group 1,
'$home/oradata/u04/log2c.rdo' to group 2,
'$home/oradata/u04/log3c.rdo' to group 3;
alter database add logfile member
'$home/oradata/u04/log1c.rdo' to group 1,
'$home/oradata/u04/log2c.rdo' to group 2,
'$home/oradata/u04/log3c.rdo' to group 3;
删除日志组
alter database drop logfile group 3;
alter database drop logfile group 3;
删除成员
alter database drop logfile member '$home/oradata/u04/log3c.rdo';
alter database drop logfile member '$home/oradata/u04/log3c.rdo';
清除日志
alter database clear logfile '$home/oradata/u01/log2a.rdo';
alter database clear logfile '$home/oradata/u01/log2a.rdo';
----
增加临时文件
alter tablespace temp add tempfile '/oradata/temp03.dbf' size 100m;
删除临时文件：
alter database tempfile '/oradata/temp02.dbf' drop including datafiles;
alter database tempfile '/oradata/temp02.dbf' drop including datafiles;
250数据库 245g 拷贝时间需要 9 小时
给主人留下些什么吧！~~