同位元組序跨平臺資料庫遷移和升級的測試
目的：將linux的資料庫遷移到solaris上同時升級資料庫
原系統情況：
OS：RHEL4 U4
oracle:10.2.0.1
IP：172.17.61.131
目標系統情況：
OS：solaris 10
oracle:10.2.0.2
IP：172.17.61.130
linux平臺的版本是10.2.0.1,sun 平臺的oracle版本是10.2.0.2,通常高版本的資料庫不能向低版本的資料庫遷移。幾點注意事項：
1.源平臺和目的平臺需要具有相同的位元組序
2.重做日誌和控制檔案不會傳輸
3.臨時檔案不會被傳輸
4.BFILE、外部表和Directories、口令檔案不會被傳輸。
確認是否支援遷移，透過dbms_tdb.check_db進行檢查。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 1219208 bytes
Variable Size 109053304 bytes
Database Buffers 209715200 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready:=dbms_tdb.check_db('Solaris Operating System (x86)');
5 end;
6 /
PL/SQL procedure successfully completed.
檢查外部物件
SQL> declare
2 external boolean;
3 begin
4 external:=dbms_tdb.check_external;
5 end;
6 /
PL/SQL procedure successfully completed.
使用RMAN進行跨平臺檔案遷移。
[oracle@rhel131 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 6 09:32:41 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1193448104)
RMAN> convert database new database 'LISA'
2> transport script. '/u01/app/oradata/trans/transport.sql'
3> to platform. 'Solaris Operating System (x86)'
4> db_file_name_convert '/u01/app/oradata/orcl' '/u01/app/oradata/trans';
Starting convert at 06-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
External table SH.SALES_TRANSACTIONS_EXT found in the database
Directory SYS.ADMIN_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
BFILE PM.PRINT_MEDIA found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=/u01/app/oradata/orcl/system01.dbf
converted datafile=/u01/app/oradata/trans/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=/u01/app/oradata/orcl/sysaux01.dbf
converted datafile=/u01/app/oradata/trans/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/u01/app/oradata/orcl/example01.dbf
converted datafile=/u01/app/oradata/trans/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/u01/app/oradata/orcl/b4space.dbf
converted datafile=/u01/app/oradata/trans/b4space.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=/u01/app/oradata/orcl/undotbs01.dbf
converted datafile=/u01/app/oradata/trans/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/u01/app/oradata/orcl/trans.dbf
converted datafile=/u01/app/oradata/trans/trans.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/u01/app/oradata/orcl/users01.dbf
converted datafile=/u01/app/oradata/trans/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script. /u01/app/oradata/trans/transport.sql on the target platform. to create database
Edit init.ora file /u01/app/product/10201/dbs/init_00jsf26h_1_0.ora. This PFILE will be used to cre ate the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 06-OCT-08
生成的transport.sql 用於參考，所有的資料檔案轉換後放在/u01/app/oradata/trans目錄下，最後RMAN還自動生成一個引數檔案/u01/app/product/10201/dbs/init_00jsf26h_1_0.ora.
檔案複製過去後，建立相關目錄$ ls
$ cd /export/home/oracle/oradata/lisa
$ ls
b4space.dbf example01.dbf system01.dbf transport.sql users01.dbf
sysaux01.dbf trans.dbf undotbs01.dbf
$ mkdir -p /export/home/oracle/oradata/lisa/dbs/bdump
$ mkdir -p /export/home/oracle/oradata/lisa/dbs/udump
$ mkdir -p /export/home/oracle/oradata/lisa/dbs/cdump
$ mkdir -p /export/home/oracle/oradata/lisa/dbs/adump
$ mkdir -p /export/home/oracle/oradata/lisa/dbs/pfile
編輯.profile檔案
$ cat /export/home/oracle/.profile
ORACLE_BASE=/export/home/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.0
ORACLE_SID=lisa
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
PATH=$ORACLE_HOME/bin:/bin:/sbin:/usr/ccs/bin:/usr/local/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/local/lib:/usr/lib
TMPDIR=/var/tmp
export ORALCE_BASE ORACLE_HOME ORACLE_SID NLS_LANG PATH LD_LIBRARY_PATH DISPLAY TMPDIR
umask 022
修改拷備過去的引數檔案
$ cd /export/home/oracle/oradata/lisa/dbs/pfile
$ mv /export/home/oracle/initlisa.ora .
$ cat initlisa.ora
# Please change the values of the following parameters:
# control_files = "/u01/app/product/10201/dbs/cf_D-LISA_id-1193448104_00jsf26h"
db_recovery_file_dest = "/export/home/oracle/oradata/lisa/dbs/flash_recovery_area"
db_recovery_file_dest_size= 2147483648
background_dump_dest = "/export/home/oracle/oradata/lisa/dbs/bdump"
user_dump_dest = "/export/home/oracle/oradata/lisa/dbs/udump"
core_dump_dest = "/export/home/oracle/oradata/lisa/dbs/cdump"
audit_file_dest = "/export/home/oracle/oradata/lisa/dbs/adump"
db_name = "LISA"
...沒有修改的省略
註釋掉控制檔案的路徑，在建立控制檔案後再追加到此引數檔案中。
先啟到nomount狀態
$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 6 15:51:28 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='/export/home/oracle/oradata/lisa/dbs/pfile/initlisa.ora';
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 1280012 bytes
Variable Size 109053940 bytes
Database Buffers 209715200 bytes
Redo Buffers 2912256 bytes
SQL> SQL>
SQL>
下面根據transport.sql 指令碼的建議，修改建立控制檔案的指令碼：
SQL>
CREATE CONTROLFILE REUSE SET DATABASE "LISA" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/export/home/oracle/oradata/lisa/redo01.dbf' SIZE 10M,
GROUP 2 '/export/home/oracle/oradata/lisa/redo02.dbf' SIZE 10M,
GROUP 3 '/export/home/oracle/oradata/lisa/redo03.dbf' SIZE 10M
DATAFILE
'/export/home/oracle/oradata/lisa/system01.dbf',
'/export/home/oracle/oradata/lisa/undotbs01.dbf',
'/export/home/oracle/oradata/lisa/sysaux01.dbf',
'/export/home/oracle/oradata/lisa/users01.dbf',
'/export/home/oracle/oradata/lisa/example01.dbf',
'/export/home/oracle/oradata/lisa/b4space.dbf',
'/export/home/oracle/oradata/lisa/trans.dbf'
CHARACTER SET WE8ISO8859P1
;
Control file created.
將控制檔名稱等資訊追加到引數檔案中。
SQL> column ctl_files NEW_VALUE ctl_files;
SQL> select concat ('control_files=''',
2 concat(replace(value,',',''','''),'''')
3 ) ctl_files
4 from v$parameter where name='control_files';
CTL_FILES
--------------------------------------------------------------------------------
control_files='/export/home/oracle/oradata/lisa/dbs/flash_recovery_area/LISA/cont
rolfile/o1_mf_4gmk815n_.ctl'
$ echo "control_files='/export/home/oracle/oradata/lisa/dbs/flash_recovery_area/LISA/controlfile/o1_mf_4gmk815n_.ctl'" >> /export/home/oracle/oradata/lisa/dbs/pfile/initlisa.ora
至此新的引數檔案和控制檔案都已OK了，重新啟動到mount狀態。
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/export/home/oracle/oradata/lisa/dbs/pfile/initlisa.ora';
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 1280012 bytes
Variable Size 109053940 bytes
Database Buffers 209715200 bytes
Redo Buffers 2912256 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
出現錯誤，檢查alert.log發現需要以upgrade選項開啟資料庫，對資料執行跨版本遷移。
Errors in file /export/home/oracle/oradata/lisa/dbs/udump/lisa_ora_6184.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Mon Oct 6 16:17:53 2008
Error 704 happened during db open, shutting down database
關閉資料庫，以upgrade方式開啟
SQL> shutdown immediate;
SQL> startup upgrade pfile='/export/home/oracle/oradata/lisa/dbs/pfile/initlisa.ora';
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 1280012 bytes
Variable Size 109053940 bytes
Database Buffers 209715200 bytes
Redo Buffers 2912256 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/export/home/oracle/oradata/lisa/system01.dbf'
SQL> recover database;
Media recovery complete.
恢復完成後，再次重啟到upgrade模式
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/export/home/oracle/oradata/lisa/dbs/pfile/initlisa.ora';
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 1280012 bytes
Variable Size 109053940 bytes
Database Buffers 209715200 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
根據transport.sql的提示，執行@@ ?/rdbms/admin/utlirp.sql指令碼
SQL> @@ ?/rdbms/admin/utlrp.sql
...
PL/SQL procedure successfully completed.
重新啟動執行utlrp.sql指令碼
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade pfile='/export/home/oracle/oradata/lisa/dbs/pfile/initlisa.ora';
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 1280012 bytes
Variable Size 109053940 bytes
Database Buffers 209715200 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
SQL> @@ ?/rdbms/admin/utlrp.sql
...
PL/SQL procedure successfully completed.
執行完utlrp.sql指令碼後，需要再執行和資料庫升級相關的指令碼catupgrd.sql
SQL> @@ ?/rdbms/admin/catupgrd.sql
Total Upgrade Time: 00:42:03
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> shutdown immediate;
SQL> startup pfile='/export/home/oracle/oradata/lisa/dbs/pfile/initlisa.ora';
ORACLE instance started.
Total System Global Area 322961408 bytes
Fixed Size 1280012 bytes
Variable Size 109053940 bytes
Database Buffers 209715200 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
可以正常開啟資料庫了.
再次執行utlrp.sql指令碼對失效的物件進行編譯
SQL> @ ?/rdbms/admin/utlrp.sql
最後別忘了建立密碼檔案和臨時檔案
$ orapwd file=/export/home/oracle/product/10.0/dbs/orapwlisa.ora password=sys entries=5
SQL> alter tablespace temp add tempfile
2 '/export/home/oracle/oradata/lisa/temp.dbf' size 10m
autoextend on next 5m maxsize 100m;
tablespace altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-471194/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- RMAN同位元組序跨平臺跨版本遷移資料庫資料庫
- RMAN同位元組序跨平臺跨版本遷移資料庫（一)資料庫
- RMAN同位元組序跨平臺跨版本遷移資料庫（二）資料庫
- Oracle 10g同位元組序跨平臺遷移的測試Oracle 10g
- datapump跨平臺升級遷移的對比測試和優化優化
- datapump跨平臺升級遷移的對比測試和最佳化
- 同位元組序跨平臺表空間傳輸的測試
- datapump跨平臺升級遷移的總結
- 利用RMAN跨平臺遷移資料庫資料庫
- 跨平臺遷移oracle資料庫指南Oracle資料庫
- 測試環境的遷移式升級和資料整合
- 大型資料庫跨平臺遷移總結資料庫
- 資料庫中跨平臺遷移方法介紹資料庫
- 遷移式升級的測試
- 資料庫的升級及遷移資料庫
- 遷移式升級的測試（二）
- 遷移式升級的測試（三）
- 使用RMAN完成跨平臺資料遷移
- rman進行跨平臺資料遷移
- 資料庫的建立、遷移、升級和流等方面資料庫
- 利用Oracle Data Guard完成跨平臺的資料庫遷移案例Oracle資料庫
- iOS CoreData (二) 版本升級和資料庫遷移iOS資料庫
- 如何利用 RMAN 可傳輸表空間遷移資料庫到不同位元組序的平臺 (文件 ID 1983639.1)資料庫
- Grafana的版本升級和資料遷移Grafana
- zt 跨平臺 跨版本 大規模資料遷移
- 【MOS】如何利用RMAN可傳輸表空間遷移資料庫到不同位元組序的平臺(文件 ID 1983639.1)資料庫
- 動手為王 | Oracle 資料庫跨版本升級遷移實踐Oracle資料庫
- RMAN跨小版本跨平臺與位元組序傳輸表空間
- 應用RMAN Transportable Database進行資料庫跨平臺遷移Database資料庫
- 一個跨平臺資料遷移的方案優化優化
- 同/不同庫遷移資料(在同使用者及表空間)測試
- ORACLE11G從WINDOWS到LINUX跨平臺遷移並升級OracleWindowsLinux
- ORACLE 跨平臺遷移方法Oracle
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- 用傳輸表空間跨平臺遷移資料
- 使用GoldenGate進行平臺遷移和資料庫升級(9i->11g)步驟描述Go資料庫
- 跨 OS 平臺遷移 Oracle DBOracle
- 跨平臺遷移支援檢視