步驟很簡單:關源庫->拷貝到目標機器->啟動到mount狀態重命名->open數據庫
登錄源數據庫所在主機
建立映射目錄 192.168.1.2oradata 映射為 z: 盤
映射完畢後檢查是否允許寫入文件
需要注意關閉目標數據庫位置服務器的防火牆
搜集源庫信息
實例名、版本、塊大小、表空間、數據文件
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
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';
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
spool renfile.sql
select 'ALTER DATABASE RENAME FILE '''||member||''' to ''d:oradataorcl'|| substr(member, instr(member, '', -1) + 1)||'''; ' cmd from v$logfile
union all
select 'ALTER DATABASE RENAME FILE '''||name||''' to ''d:oradataorcl'|| substr(name, instr(name, '', -1) + 1)||'''; ' cmd from v$datafile
union all
select 'ALTER DATABASE RENAME FILE '''||name||''' to ''d:oradataorcl'|| substr(name, instr(name, '', -1) + 1)||'''; ' cmd from v$tempfile;
spool off
create pfile from spfile;
關閉源庫
host lsnrctl stop
shutdown immediate
將windows服務設置為手工啟動(關鍵!!! 防止萬一復制出現問題還可以重新復制一致的文件)
拷貝控制文件、日志文件、數據文件,
host cpfile.bat
startup
host lsnrctl start
拷貝pfile、 tnsname.ora、 口令文件、renfile.sql
在目標數據庫修改參數文件中的控制文件內容
配置啟動參數文件pfile,修改控制文件位置及路徑信息
啟動到mount狀態
sqlplus "/as sysdba"
startup mount pfile=?/database/initorcl.ora
修改文件位置
SQL> @renfile.sql
打開數據庫
create spfile from pfile;
host lsnrctl start
檢查臨時文件是否有
連接測試
參考命令:
oradim -NEW -SID test -STARTMODE manual -PFILE "D:Oracleadmintestpfileinittest.ora"
ALTER DATABASE RENAME FILE 'c:oracleproductoradataexample.dbf'
TO 'c:oracleproductoradatademos.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 DROP LOGFILE GROUP 3;
刪除成員
ALTER DATABASE DROP LOGFILE MEMBER '$HOME/ORADATA/u04/log3c.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;
250數據庫 245G 拷貝時間需要 9 小時