同字节序跨平台数据库迁移和升级的测试

  • jolly10

    2008-10-15 17:20:40
  • Linux操作系统
  • 原创

目的：将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.

请使用浏览器的分享功能分享到微信等