windows平台下Oracle10.2 RMAN数据库迁移至Linux平台下

最新推荐文章于 2023-12-12 20:29:52 发布

cuilian2013

最新推荐文章于 2023-12-12 20:29:52 发布

阅读量211

点赞数

文章标签：数据库

windows平台下Oracle10.2 rman数据库迁移至Linux平台Oracle 10.2下

1.Convert database特性介绍

1)、只能传输数据表空间的数据文件。临时表空间将不会被传输，如果临时表空间是本地管理的话，那么传输数据库完成后将会自动在目标数据库创建临时表空间。

2)、假如源主数据库使用PFILE，那么PFILE也会被传输。如果源数据库使用的是SPFILE，那么将会自动产生一个PFILE进行传输，并在目标数据库生成SPFILE。

3)、控制文件和联机日志文件将不会被传输。在目标数据库open resetlogs的过程中，将会自动创建控制文件和联机日志文件。

4)、 BFILE、外部表和directory将不会被传输。RMAN检查的时候将会把这些对象列出，用户可以手工创建这些对象。

5)、密码文件将不会被传输，用户必须在目标数据库手工创建密码文件。

传输数据库的第一步是将源数据库置于READ ONLY模式

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1073741824 bytes
Fixed Size                  1307424 bytes
Variable Size             297798880 bytes
Database Buffers          767557632 bytes
Redo Buffers                7077888 bytes
数据库装载完毕。
SQL> alter database open read only;

数据库已更改。

2.查看ORACLE 10g可以支持转换的目标数据库的平台

SQL> set linesize 300

##源数据库（windows平台）的ENDIAN

SQL> SELECT D.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM. TP, V$DATABASE D
3 WHERE TP.PLATFORM_NAME = D.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------------
Microsoft Windows IA (32-bit) Little

SQL> col PLATFORM_NAME for a40;

##支持转换的目的数据库平台（在10G中进行全库跨平台的迁移，那么只能在低位和低位或者高位和高位平台之间进行迁移，无法进行高位与低位平台的迁移）
SQL> select * from V$TRANSPORTABLE_PLATFORM. order by 3;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- --------------------
          6 AIX-Based Systems (64-bit)               Big
         18 IBM Power Based Linux                    Big
          2 Solaris[tm] OE (64-bit)                  Big
          4 HP-UX IA (64-bit)                        Big
         16 Apple Mac OS                             Big
          1 Solaris[tm] OE (32-bit)                  Big
          9 IBM zSeries Based Linux                  Big
          3 HP-UX (64-bit)                           Big
         17 Solaris Operating System (x86)           Little
         20 Solaris Operating System (x86-64)        Little
         12 Microsoft Windows x86 64-bit             Little

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- --------------------
         13 Linux x86 64-bit                         Little
          8 Microsoft Windows IA (64-bit)            Little
         19 HP IA Open VMS                           Little
         11 Linux IA (64-bit)                        Little
          5 HP Tru64 UNIX                            Little
         10 Linux IA (32-bit)                        Little
          7 Microsoft Windows IA (32-bit)            Little
         15 HP Open VMS                              Little

已选择19行。

3. convert database前检查

利用dbms_tdb包检查数据库能否被传输以及列出外部表和DIRECTORY等无法传输的对象信息(本次是Microsoft Windows IA (32-bit)平台迁移至Linux IA (32-bit)平台)

SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Linux IA (32-bit)',dbms_tdb.skip_none);
5 end;
6 /

PL/SQL 过程已成功完成。

##使用dbms_tdb.check_external检查数据库中是否存在外部表、目录和BFILEs，RMAN不能自动传输这些文件，需要手动复制和创建目录

SQL> declare
2 external boolean;
3 begin
4 external := dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.TTS_EXP, SYS.ORACLE_OCM_CONFIG_DIR2, SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_DUM
P, SYS.DIR, SYS.DATA_PUMP_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FI
LE_DIR, SYS.DATA_FILE_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL 过程已成功完成。

5.使用RMAN转换数据库

D:\>rman target /

恢复管理器: Release 10.2.0.5.0 - Production on 星期三 2月 27 16:25:17 2013

连接到目标数据库: ORA10G (DBID=4098065600)

RMAN> convert database new database 'ora10g' transport script. 'd:\backup\transpo
rtdb.sql' to platform. 'Linux IA (32-bit)' db_file_name_convert 'D:\ORACLE\PRODUC
T\10.2.0\ORADATA\ORA10G' 'D:\backup';

启动 convert 于 27-2月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=152 devtype=DISK

在数据库中找到外部表 SH.SALES_TRANSACTIONS_EXT

在数据库中找到目录 SYS.TTS_EXP
在数据库中找到目录 SYS.ORACLE_OCM_CONFIG_DIR2
在数据库中找到目录 SYS.ORACLE_OCM_CONFIG_DIR
在数据库中找到目录 SYS.DATA_DUMP
在数据库中找到目录 SYS.DIR
在数据库中找到目录 SYS.DATA_PUMP_DIR
在数据库中找到目录 SYS.SUBDIR
在数据库中找到目录 SYS.XMLDIR
在数据库中找到目录 SYS.MEDIA_DIR
在数据库中找到目录 SYS.LOG_FILE_DIR
在数据库中找到目录 SYS.DATA_FILE_DIR
在数据库中找到目录 SYS.WORK_DIR
在数据库中找到目录 SYS.ADMIN_DIR

在数据库中找到 BFILE PM.PRINT_MEDIA

在口令文件中找到用户 SYS (具有 SYSDBA and SYSOPER 权限)
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.DB
F
在文件 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.DBF 的转换期间发现一些
损坏的块
已转换的数据文件 = D:\BACKUP\UNDOTBS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:55
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00008 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\TS_DB_DATA01
.DBF
已转换的数据文件 = D:\BACKUP\TS_DB_DATA01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:45
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF

已转换的数据文件 = D:\BACKUP\SYSTEM01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:15
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.DBF

已转换的数据文件 = D:\BACKUP\SYSAUX01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:15
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00006 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\WAP_USER_DAT
A_TABLESPACE01.DBF
已转换的数据文件 = D:\BACKUP\WAP_USER_DATA_TABLESPACE01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:15
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00033 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\TEST01.DBF
已转换的数据文件 = D:\BACKUP\TEST01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\EXAMPLE01.DB
F
已转换的数据文件 = D:\BACKUP\EXAMPLE01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00032 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS02.DB
F
已转换的数据文件 = D:\BACKUP\UNDOTBS02.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00034 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\TEST02.DBF
已转换的数据文件 = D:\BACKUP\TEST02.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DBF
已转换的数据文件 = D:\BACKUP\USERS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
在目标平台上运行 SQL 脚本 D:\BACKUP\TRANSPORTDB.SQL 以创建数据库
编辑 init.ora 文件 D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00O31IUF_1_0.ORA
。此 PFILE 将用于在目标平台上创建数据库据
要重新编译所有 PL/SQL 模块, 请在目标平台上运行 utlirp.sql 和 utlrp.sql
要更改内部数据库标识符, 请使用 DBNEWID 实用程序
完成 backup 于 27-2月 -13

RMAN>

注：

1)、new database表示目标数据库的名字

2)、transport script指定生成建库脚本的位置

3)、to platform表示目标数据库的平台

4)、db_file_name_convert中

指定源数据库数据文件所在的位置为：'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G'

转换后数据文件存放的目录为：''D:\backup'

6.修改生成的参数文件

通过查看RMAN转换时生成的脚本D:\backup\TRANSPORTDB.SQL，可以找到生的的初始化参数文件是：D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00O31IUF_1_0.ORA

由于WIN和LINUX的目录结构区别太大，我们需要修改这个脚本以适应LINUX平台。特别要注意大小写。修改完成之后我们把它复制到目标数据库的相关位置。

TRANSPORTDB.SQL脚本内容如下：

-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00O31IUF_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "ORA10G" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50M,
GROUP 2 SIZE 50M,
GROUP 3 SIZE 50M
DATAFILE
'D:\BACKUP\SYSTEM01.DBF',
'D:\BACKUP\UNDOTBS01.DBF',
'D:\BACKUP\SYSAUX01.DBF',
'D:\BACKUP\USERS01.DBF',
'D:\BACKUP\EXAMPLE01.DBF',
'D:\BACKUP\WAP_USER_DATA_TABLESPACE01.DBF',
'D:\BACKUP\TS_DB_DATA01.DBF',
'D:\BACKUP\UNDOTBS02.DBF',
'D:\BACKUP\TEST01.DBF',
'D:\BACKUP\TEST02.DBF'
CHARACTER SET ZHS16GBK
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE
SIZE 524288000 AUTOEXTEND OFF;
-- End of tempfile additions.
--

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00O31IUF_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00O31IUF_1_0.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

初始化参数文件INIT_00O31IUF_1_0.ORA内容如下：

# Please change the values of the following parameters:

control_files = "D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CF_D-ORA10G_ID-4098065600_00O31IUF"

db_create_file_dest = "D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ora10g"

db_recovery_file_dest = "D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\flash_recovery_area"

db_recovery_file_dest_size= 2147483648

service_names = "ORA10G"

audit_file_dest = "D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ADUMP"

background_dump_dest = "D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\BDUMP"

user_dump_dest = "D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UDUMP"

core_dump_dest = "D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CDUMP"

db_name = "ORA10G"

# Please review the values of the following parameters:

__shared_pool_size = 281018368

__large_pool_size = 4194304

__java_pool_size = 8388608

__streams_pool_size = 4194304

# db_file_name_convert = "oradata\oracle10g"

# db_file_name_convert = "oradata\ora10g"

# log_file_name_convert = "oradata\oracle10g"

# log_file_name_convert = "oradata\ora10g"

__db_cache_size = 767557632

standby_archive_dest = ""

fal_client = "oracle10g"

fal_server = "ora10g"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=ora10gXDB)"

# The values of the following parameters are from source database:

processes = 150

sga_max_size = 1073741824

nls_length_semantics = "BYTE"

resource_manager_plan = ""

sga_target = 1073741824

db_block_size = 8192

compatible = "10.2.0.1.0"

log_archive_config = "DG_CONFIG=(ora10g"

log_archive_config = "oracle10g)"

# log_archive_dest_1 = "LOCATION=D:\oracle\product\10.2.0\oradata\ora10g\ARCHIVELOG\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora10g"

log_archive_dest_2 = "SERVICE=oracle10g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle10g"

log_archive_dest_state_1 = "enable"

log_archive_dest_state_2 = "enable"

db_file_multiblock_read_count= 16

standby_file_management = "AUTO"

fast_start_mttr_target = 10

undo_management = "AUTO"

undo_tablespace = "UNDOTBS1"

undo_retention = 900

job_queue_processes = 10

open_cursors = 300

pga_aggregate_target = 524288000

aq_tm_processes = 0

修改后的初始化参数文件内容INIT.ORA如下:

# Please change the values of the following parameters:

control_files = "/home/oracle/oradata/ora10g/CONTROL.CTL"

db_create_file_dest = "/home/oracle/oradata/ora10g"

db_recovery_file_dest = "/home/oracle/flash_recovery_area"

db_recovery_file_dest_size= 2147483648

service_names = "ORA10G"

audit_file_dest = "/home/oracle/admin/ora10g/adump"

background_dump_dest = "/home/oracle/admin/ora10g/bdump"

user_dump_dest = "/home/oracle/admin/ora10g/udump"

core_dump_dest = "/home/oracle/admin/ora10g/cdump"

db_name = "ORA10G"

# Please review the values of the following parameters:

__shared_pool_size = 281018368

__large_pool_size = 4194304

__java_pool_size = 8388608

__streams_pool_size = 4194304

# db_file_name_convert = "oradata\oracle10g"

# db_file_name_convert = "oradata\ora10g"

# log_file_name_convert = "oradata\oracle10g"

# log_file_name_convert = "oradata\ora10g"

__db_cache_size = 767557632

standby_archive_dest = ""

fal_client = "oracle10g"

fal_server = "ora10g"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=ora10gXDB)"

# The values of the following parameters are from source database:

processes = 150

sga_max_size = 1073741824

nls_length_semantics = "BYTE"

resource_manager_plan = ""

sga_target = 1073741824

db_block_size = 8192

compatible = "10.2.0.1.0"

log_archive_config = "DG_CONFIG=(ora10g"

log_archive_config = "oracle10g)"

# log_archive_dest_1 = "LOCATION=D:\oracle\product\10.2.0\oradata\ora10g\ARCHIVELOG\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora10g"

log_archive_dest_2 = "SERVICE=oracle10g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle10g"

log_archive_dest_state_1 = "enable"

log_archive_dest_state_2 = "enable"

db_file_multiblock_read_count= 16

standby_file_management = "AUTO"

fast_start_mttr_target = 10

undo_management = "AUTO"

undo_tablespace = "UNDOTBS1"

undo_retention = 900

job_queue_processes = 10

open_cursors = 300

pga_aggregate_target = 524288000

aq_tm_processes = 0

修改后的转换RMAN转换脚本TRANSPORTDB.SQL内容如下：

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='/rong/backup/INIT.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "ORA10G" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/oracle/oradata/ora10g/REDO01.LOG' SIZE 50M,
GROUP 2 '/home/oracle/oradata/ora10g/REDO02.LOG' SIZE 50M,
GROUP 3 '/home/oracle/oradata/ora10g/REDO03.LOG' SIZE 50M
DATAFILE
'/home/oracle/oradata/ora10g/SYSTEM01.DBF',
'/home/oracle/oradata/ora10g/UNDOTBS01.DBF',
'/home/oracle/oradata/ora10g/SYSAUX01.DBF',
'/home/oracle/oradata/ora10g/USERS01.DBF',
'/home/oracle/oradata/ora10g/EXAMPLE01.DBF',
'/home/oracle/oradata/ora10g/WAP_USER_DATA_TABLESPACE01.DBF',
'/home/oracle/oradata/ora10g/TS_DB_DATA01.DBF',
'/home/oracle/oradata/ora10g/UNDOTBS02.DBF',
'/home/oracle/oradata/ora10g/TEST01.DBF',
'/home/oracle/oradata/ora10g/TEST02.DBF'
CHARACTER SET ZHS16GBK
;

-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='/rong/backup/INIT.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/rong/backup/INIT.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

7.将修改后的参数文件INIT.ORA和RMAN转换脚本TRANSPORTDB.SQL上次至目标数据库服务器相关位置上

8.在目标数据库上还原数据库

确保在源数据库上（windows平台）RMAN转换生成的数据文件已上传至目标数据库对应位置上，并确保上述参数文件INIT.ORA中提到的目录已建立（如*dump目录）

a.在目标数据库上仅安装数据库软件

b.在目标数据库上创建如下目录

/home/oracle/oradata/ora10g

/home/oracle/flash_recovery_area

/home/oracle/admin/ora10g/adump

/home/oracle/admin/ora10g/bdump

/home/oracle/admin/ora10g/udump

/home/oracle/admin/ora10g/cdump

[oracle@localhost ~]$ export ORACLE_SID=ora10g
[oracle@localhost ~]$ sqlplus /nolog