萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> oracle教程 >> oracle冷備份遷移數據庫到其他機器步驟

oracle冷備份遷移數據庫到其他機器步驟

  步驟很簡單:關源庫->拷貝到目標機器->啟動到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 小時

copyright © 萬盛學電腦網 all rights reserved