在Oracle
我們看以下測試
[oracle@danaly oradata]$ sqlplus
SQL*Plus: Release
Copyright (c)
Connected to:
Oracle Database
With the Partitioning
SQL> alter database mount;
Database altered
SQL> alter database open;
Database altered
SQL> select name from v$datafile;
NAME
/opt/oracle/oradata/eygle/EYGLE/datafile/o
/opt/oracle/oradata/eygle/EYGLE/datafile/o
/opt/oracle/oradata/eygle/EYGLE/datafile/o
/opt/oracle/oradata/eygle/EYGLE/datafile/o
SQL> show parameter comp
NAME TYPE VALUE
compatible string
nls_comp string
plsql_compiler_flags string INTERPRETED
plsql_v
SQL> create tablespace trans
Tablespace created
SQL> create user trans identified by trans default tablespace trans;
User created
SQL> grant connect
Grant succeeded
SQL> connect trans/trans
create table test as select * from dict;
Connected
SQL>
Table created
SQL> select count(*) from test;
COUNT(*)
SQL> connect / as sysdba
Connected
SQL> alter tablespace trans read only;
Tablespace altered
SQL> exit
Disconnected from Oracle Database
With the Partitioning
[oracle@danaly oradata]$ exp \
Export: Release
Copyright (c)
Connected to: Oracle Database
With the Partitioning
Export done in ZHS
Note: table data (rows) will not be exported
About to export transportable tablespace metadata
For tablespace TRANS
Export terminated successfully without warnings
[oracle@danaly oradata]$ ls
[oracle@danaly oradata]$ pwd
/opt/oracle/oradata
然後我們可以直接將這兩個文件傳輸到Windows平台上
C:\oracle\oradata\EYGLE\DATAFILE>ftp
Connected to
User (
Password:
ftp> bin
ftp> mget oradata/eygle/EYGLE/datafile/trans
mget oradata/eygle/EYGLE/datafile/trans
ftp: 收到
ftp> mget oradata/exp_trans
mget oradata/exp_trans
ftp: 收到
ftp> quit
SQL> create user trans identified by trans;
用戶已創建
SQL> grant connect
授權成功
接下來執行導入
C:\oracle\oradata\EYGLE\DATAFILE>imp
Import: Release
Copyright (c)
連接到: Oracle Database
With the Partitioning
經由常規路徑由 EXPORT:V
即將導入可傳輸的表空間元數據
已經完成 ZHS
成功終止導入
我們看到這個文件可以直接被Windows上的系統識別
SQL> select count(*) from trans
COUNT(*)
SQL> select tablespace_name
TABLESPACE_NAME STATUS
SYSTEM ONLINE
UNDOTBS
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TRANS READ ONLY
已選擇
上一節介紹的方法不免過於復雜
也許是這一遷移的技術優先級較低
這一技術實現有以下幾點注意事項
我們通過Linux平台到Windows平台的遷移來看一下這一技術的實現
首先要確定源平台和目標平台具有相同的字節序
SQL> select PLATFORM_NAME
PLATFORM_NAME ENDIAN_FORMAT
Microsoft Windows IA (
Linux IA (
然後需要確定源平台及目標平台的數據庫版本
跨平台遷移需要數據庫處於READ ONLY模式打開
SQL> set serveroutput on
SQL> declare
PL/SQL procedure successfully completed
如果以上過程成功執行
使用DBMS_TDB
SQL> set serveroutput on
SQL> declare
The following directories exist in the database:
SYS
PL/SQL procedure successfully completed
如果數據庫中存在外部表
執行跨平台遷移首先要通過RMAN對數據文件進行轉換
[oracle@danaly eygle]$ rman target /
Recovery Manager: Release
Copyright (c)
connected to target database: EYGLE (DBID=
RMAN> CONVERT DATABASE NEW DATABASE
Starting convert at
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_
channel ORA_DISK_
Directory SYS
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_
input datafile
fno=
converted datafile=/opt/oracle/oradata/transport/o
channel ORA_DISK_
channel ORA_DISK_
input datafile
fno=
converted datafile=/opt/oracle/oradata/transport/o
channel ORA_DISK_
channel ORA_DISK_
input datafile
fno=
converted datafile=/opt/oracle/oradata/transport/o
channel ORA_DISK_
channel ORA_DISK_
input datafile
fno=
converted datafile=/opt/oracle/oradata/transport/o
channel ORA_DISK_
Run SQL script /opt/oracle/oradata/transport/transport
Edit init
To recompile all PL/SQL modules
To change the internal database identifier
Finished backup at
RMAN的轉換語句中指定生成一個轉換腳本transport
最後RMAN還自動生成一個參數文件
第一部分列出需要修改的參數
# Please change the values of the following parameters:
control_files =
db_create_file_dest =
db_recovery_file_dest =
db_recovery_file_dest_size=
background_dump_dest =
user_dump_dest =
core_dump_dest =
audit_file_dest =
db_name =
第二部分列出了建議Review的參數
# Please review the values of the following parameters:
remote_login_passwordfile=
db_domain =
dispatchers =
第三部分列出了來自於源數據庫的一些特殊設置
# The values of the following parameters are from source database:
processes =
sga_target =
db_block_size =
compatible =
db_file_multiblock_read_count=
undo_management =
undo_tablespace =
job_queue_processes =
open_cursors =
pga_aggregate_target =
參數文件的內容我們可以在新的平台上重新創建
源平台的工作完成之後
C:\oracle\oradata>gzip
C:\oracle\oradata>tar
tar: blocksize =
x transport/transport
x transport/o
x transport/o
x transport/o
x transport/o
C:\oracle\oradata>mkdir
C:\oracle\oradata>mkdir JULIA\CONTROLFILE
C:\oracle\oradata>mkdir JULIA\ONLINELOG
C:\oracle\oradata>mv transport\* JULIA\DATAFILE
首先創建相關目錄
C:\oracle\oradata>mkdir C:\oracle\admin\julia\adump
C:\oracle\oradata>mkdir C:\oracle\admin\julia\bdump
C:\oracle\oradata>mkdir C:\oracle\admin\julia\cdump
C:\oracle\oradata>mkdir C:\oracle\admin\julia\dpdump
C:\oracle\oradata>mkdir C:\oracle\admin\julia\pfile
C:\oracle\oradata>mkdir C:\oracle\admin\julia\udump
創建Windows數據庫服務
C:\oracle\oradata>oradim
實例已創建
修改參數文件
db_create_file_dest =
db_recovery_file_dest =
修改後的參數文件在Windows上應該位於$ORACLE_HOME/database下
control_files =
如果我們計劃使用OMF管理
准備工作完成之後
這個腳本的第一部分給出了使用參數文件啟動實例及重新創建控制文件的語法參考
STARTUP NOMOUNT PFILE=
CREATE CONTROLFILE REUSE SET DATABASE
MAXLOGFILES
MAXLOGMEMBERS
MAXDATAFILES
MAXINSTANCES
MAXLOGHISTORY
LOGFILE
GROUP
GROUP
GROUP
DATAFILE
CHARACTER SET ZHS
;
由於我們已經編輯好了新的參數文件
C:\oracle\oradata>set ORACLE_SID=julia
C:\oracle\oradata>sqlplus
SQL*Plus: Release
Copyright (c)
已連接到空閒例程
SQL> startup nomount pfile=?\database\initjulia
ORACLE 例程已經啟動
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
接下來創建控制文件
SQL> CREATE CONTROLFILE REUSE SET DATABASE
控制文件已創建
然後將控制文件的名稱等信息追加到參數文件中
SQL> column ctl_files NEW_VALUE ctl_files;
SQL> SELECT CONCAT (
CTL_FILES
control_files=
LE\FLASH_RECOVERY_AREA\JULIA\CONTROLFILE\O
SQL> host
注意
完成了以上工作後
SQL> shutdown immediate;
ORA
已經卸載數據庫
ORACLE 例程已經關閉
SQL> startup mount;
ORACLE 例程已經啟動
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
數據庫裝載完畢
接下來再參考一下transport
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE
SIZE
現在我們需要通過RESETLOGS方式來重新生成日志文件
注意
SQL> alter database open resetlogs;
alter database open resetlogs
*
第
ORA
檢查日志我們發現以下提示
Mon Jun
Errors in file c:\oracle\admin\julia\udump\julia_ora_
ORA
ORA
Oracle要求以UPGRADE選項打開數據庫
我們繼續參考transport
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE=
@@ ?/rdbms/admin/utlirp
SHUTDOWN IMMEDIATE
STARTUP PFILE=
@@ ?/rdbms/admin/utlrp
set feedback
再次啟動數據庫到UPGRADE模式
SQL> startup upgrade;
ORACLE 例程已經啟動
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
數據庫裝載完畢
ORA
ORA
SQL> recover database;
完成介質恢復
恢復完成之後啟動數據庫到UPGRADE模式
SQL> shutdown immediate;
ORA
已經卸載數據庫
ORACLE 例程已經關閉
SQL> startup upgrade;
ORACLE 例程已經啟動
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
數據庫裝載完畢
數據庫已經打開
執行腳本?/rdbms/admin/utlirp
DOC>#######################################################################
DOC> utlirp
DOC> database have been invalidated
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp
DOC> recompile invalid objects
DOC>#######################################################################
也就是說
按照transport
SQL> @@ ?/rdbms/admin/utlrp
TIMESTAMP
COMP_TIMESTAMP UTLRP_BGN
PL/SQL 過程已成功完成
TIMESTAMP
COMP_TIMESTAMP UTLRP_END
PL/SQL 過程已成功完成
utlrp
SQL> @?/rdbms/admin/catupgrd
這個腳本調用catlog
SQL> startup
ORACLE 例程已經啟動
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
數據庫裝載完畢
數據庫已經打開
SQL> select count(*) from dba_objects where status=
COUNT(*)
已選擇
SQL> @?\rdbms\admin\utlrp
catupgrd
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE
表空間已更改
至此同字節序的跨平台遷移全部完成
通過以上測試實際上可以確認
如果進行數據庫升級
另外一種常見的遷移的方法是通過邏輯導出導入(EXP/IMP)
那麼現在
Oracle有一個小組
當然這種方法的使用要考慮的還有很多
Amadeus公司的遷移是在同類型平台不同主機之間進行的
(
(
(
(
(
(
在這個遷移過程中
使用這種方法
以上腳本已經完成了主要的工作
這裡還有兩個腳本需要執行
connect
set echo on
spool C:\oracle\admin\eygle\scripts\postScripts
@C:\oracle\
execute dbms_datapump_utl
commit;
connect
alter session set current_schema=ORDSYS;
@C:\oracle\
alter session set current_schema=SYS;
connect
connect
alter user CTXSYS account unlock identified by change_on_install;
connect
@C:\oracle\
@C:\oracle\
connect
execute dbms_swrf_internal
commit;
spool off
最後執行的腳本是postDBCreation
connect
set echo on
spool C:\oracle\admin\eygle\scripts\postDBCreation
connect
set echo on
create spfile=
FROM pfile=
shutdown immediate;
connect
startup ;
alter user SYSMAN identified by
alter user DBSNMP identified by
select
execute utl_recomp
select
host C:\oracle\
spool C:\oracle\admin\eygle\scripts\postDBCreation
exit;
看到在最後部分
此外需要注意的是以下幾句命令
select
execute utl_recomp
select
在Oracle
@/opt/oracle/product/
其實兩者是相同的
@@utlrcmp
execute utl_recomp
Rem =====================================================================
Rem Run component validation procedure
Rem =====================================================================
EXECUTE dbms_registry
Oracle在utlrp
通常我們會在Oracle的升級指導中看到這個腳本
這樣使用模板創建數據庫就完成了
From:http://tw.wingwit.com/Article/program/Oracle/201311/17385.html