A-A+

RMAN数据库迁移(跨平台跨操作系统位数)

2013年01月25日 Backup&Recovery 暂无评论 阅读 2,158 次

RMAN数据库迁移(跨平台跨操作系统位数)
场景说明:将oracle10gr2的数据库从win平台32位(运行中)使用rman迁移(使用rman的全库备份)到redhat linux6.0 64位的平台下。
相关博文:数据库高版本到低版本跨操作系统位数跨用户的数据导入导出
http://www.yallonking.com/database/623.html
查看源端数据库信息

SQL> select * from gv$version;

   INST_ID BANNER
---------- ----------------------------------------------------------------
         1 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
         1 PL/SQL Release 10.2.0.1.0 - Production
         1 CORE 10.2.0.1.0      Production
         1 TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
         1 NLSRTL Version 10.2.0.1.0 - Production
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

会话已更改。

SQL> select * from test;

        ID NAME                 MYTIME
---------- -------------------- -------------------
         1 yallonking           2013/01/22 22:00:35

插入测试数据

SQL> INSERT INTO TEST VALUES(2,'YALLONKING',SYSDATE);

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> select * from test;

        ID NAME                 MYTIME
---------- -------------------- -------------------
         1 yallonking           2013/01/22 22:00:35
         2 YALLONKING           2013/01/25 14:10:10

SQL> ALTER SYSTEM SWITCH LOGFILE;

系统已更改。

SQL> ALTER SYSTEM CHECKPOINT;

系统已更改。

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

系统已更改。

SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            C:\oracle\product\10.2.0\backup\
最早的联机日志序列     7
下一个存档日志序列   9
当前日志序列           9

查看各平台字节序

SQL> set pagesize 20
SQL> col platform_name for a35
SQL> select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
          1 Solaris[tm] OE (32-bit)             Big
          2 Solaris[tm] OE (64-bit)             Big
          3 HP-UX (64-bit)                      Big
          4 HP-UX IA (64-bit)                   Big
          5 HP Tru64 UNIX                       Little
          6 AIX-Based Systems (64-bit)          Big
          7 Microsoft Windows IA (32-bit)       Little
          8 Microsoft Windows IA (64-bit)       Little
          9 IBM zSeries Based Linux             Big
         10 Linux IA (32-bit)                   Little
         11 Linux IA (64-bit)                   Little
         12 Microsoft Windows 64-bit for AMD    Little
         13 Linux 64-bit for AMD                Little
         15 HP Open VMS                         Little
         16 Apple Mac OS                        Big
         17 Solaris Operating System (x86)      Little
         18 IBM Power Based Linux               Big

已选择17行。

确认源库数据库字节序
SQL> select tp.platform_id,substr(d.platform_name,1,30),endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;

PLATFORM_ID SUBSTR(D.PLATFORM_NAME,1,30)                                 ENDIAN_
FORMAT
----------- ------------------------------------------------------------ -------
-------
          7 Microsoft Windows IA (32-bit)                                Little

使用以下脚本备份源端数据库
C:\Documents and Settings\Administrator>rman target / log='C:\oracle\product\10.2.0\backup\rman_full.log'
run{
allocate channel dev1 type disk;
allocate channel dev2 type disk;
backup full database tag 'full_backup' format 'C:\oracle\product\10.2.0\backup\full_db_backup_%d_%T_%U.bak';
sql 'alter system archive log current';
backup filesperset 5 format 'C:\oracle\product\10.2.0\backup\arch_%u_%s_%p.arch' archivelog all delete input;
backup current controlfile format 'C:\oracle\product\10.2.0\backup\db_ctl_%T_%t_%s_%p.bak';
backup spfile format 'C:\oracle\product\10.2.0\backup\db_spfile_%T_%t_%s_%p.bak';
release channel dev1;
release channel dev2;
}

将备份传送到目标端,并修改权限

[oracle@rhl6 ora10gr2]$ ls -l backup/
total 548452
-rw-r--r--. 1 oracle oinstall     17408 Jan 25 21:22 ARCH_0FO0AB8A_15_1.ARCH
-rw-r--r--. 1 oracle oinstall      2560 Jan 25 21:22 ARCH_0GO0AB8A_16_1.ARCH
-rw-r--r--. 1 oracle oinstall   7110656 Jan 25 21:22 DB_CTL_20130125_805645580_17_1.BAK
-rw-r--r--. 1 oracle oinstall     98304 Jan 25 21:22 DB_SPFILE_20130125_805645585_18_1.BAK
-rw-r--r--. 1 oracle oinstall 367763456 Jan 25 21:25 FULL_DB_BACKUP_ORA10GR2_20130125_0BO0AB5M_1_1.BAK
-rw-r--r--. 1 oracle oinstall 179404800 Jan 25 21:24 FULL_DB_BACKUP_ORA10GR2_20130125_0CO0AB5M_1_1.BAK
-rw-r--r--. 1 oracle oinstall   7110656 Jan 25 21:24 FULL_DB_BACKUP_ORA10GR2_20130125_0DO0AB7Q_1_1.BAK
-rw-r--r--. 1 oracle oinstall     98304 Jan 25 21:24 FULL_DB_BACKUP_ORA10GR2_20130125_0EO0AB86_1_1.BAK
-rw-r--r--. 1 oracle oinstall      3951 Jan 25 21:24 rman_full.log

目标端信息如下:

[oracle@rhl6 ~]$ file $ORACLE_HOME/bin/oracle
/opt/ora10g/product/database/bin/oracle: setuid setgid ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

RMAN开始恢复
参数文件的恢复可以从备份恢复spfile,也可从源库创建。此处选择后者
至于前者,见本人另一篇博文,当中有提到:
数据库高版本到低版本跨操作系统位数跨用户的数据导入导出
http://www.yallonking.com/database/623.html
此处拿到源库的pfile并修改里边的dump相关目录,控制文件目录等,以及在新地址创建相关目录。

[oracle@rhl6 ora10gr2]$ pwd
/opt/ora10g/product/ora10gr2
[oracle@rhl6 ora10gr2]$ mkdir adump
[oracle@rhl6 ora10gr2]$ mkdir bdump
[oracle@rhl6 ora10gr2]$ mkdir cdump
[oracle@rhl6 ora10gr2]$ mkdir archivelog
[oracle@rhl6 ora10gr2]$ mkdir udump
[oracle@rhl6 ora10gr2]$ cat pfile.txt
ora10gr2.__db_cache_size=432013312
ora10gr2.__java_pool_size=4194304
ora10gr2.__large_pool_size=4194304
ora10gr2.__shared_pool_size=167772160
ora10gr2.__streams_pool_size=0
*.audit_file_dest='/opt/ora10g/product/ora10gr2/adump'
*.background_dump_dest='/opt/ora10g/product/ora10gr2/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/ora10g/product/ora10gr2/control01.ctl','/opt/ora10g/product/ora10gr2/control02.ctl','/opt/ora10g/product/ora10gr2/control03.ctl'
*.core_dump_dest='/opt/ora10g/product/ora10gr2/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ora10gr2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10gr2XDB)'
*.job_queue_processes=10
*.log_archive_dest='/opt/ora10g/product/ora10gr2/archivelog'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/ora10g/product/ora10gr2/udump'

使用新构造的pfile将库nomount

[oracle@rhl6 ora10gr2]$ export ORACLE_SID=ora10gr2
[oracle@rhl6 ora10gr2]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 25 21:28:04 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/opt/ora10g/product/ora10gr2/pfile.txt';
ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 1: Operation not permitted
SQL> exit
[root@rhl6 ~]# echo 501 > /proc/sys/vm/hugetlb_shm_group
[oracle@rhl6 ora10gr2]$ export ORACLE_SID=ora10gr2
[oracle@rhl6 ora10gr2]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 25 21:30:31 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/opt/ora10g/product/ora10gr2/pfile.txt';
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2022728 bytes
Variable Size             176161464 bytes
Database Buffers          432013312 bytes
Redo Buffers                2170880 bytes
SQL> 

从备份恢复控制文件
[oracle@rhl6 ora10g]$ export ORACLE_SID=ora10gr2

[oracle@rhl6 ora10g]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 25 21:31:58 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ora10gr2 (not mounted)

RMAN> restore controlfile to '/opt/ora10g/product/ora10gr2/control01.ctl' from '/opt/ora10g/product/ora10gr2/backup/DB_CTL_20130125_805645580_17_1.BAK';

Starting restore at 25-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 25-JAN-13

RMAN> 

在新地址复用控制文件

[oracle@rhl6 ~]$ cd /opt/ora10g/product/ora10gr2/
[oracle@rhl6 ora10gr2]$ ls -l
total 6924
drwxr-xr-x. 2 oracle oinstall    4096 Jan 25 21:32 adump
drwxr-xr-x. 2 oracle oinstall    4096 Jan 25 21:23 archivelog
drwxr-xr-x. 2 oracle oinstall    4096 Jan 25 21:24 backup
drwxr-xr-x. 2 oracle oinstall    4096 Jan 25 21:28 bdump
drwxr-xr-x. 2 oracle oinstall    4096 Jan 25 21:22 cdump
-rw-r-----. 1 oracle oinstall 7061504 Jan 25 21:33 control01.ctl
-rw-r--r--. 1 oracle oinstall    1049 Jan 25 21:26 pfile.txt
drwxr-xr-x. 2 oracle oinstall    4096 Jan 25 21:30 udump
[oracle@rhl6 ora10gr2]$ cp control01.ctl control02.ctl 
[oracle@rhl6 ora10gr2]$ cp control01.ctl control03.ctl 

使用新的pfile创建spfile将数据库启动至mount下

SQL> create spfile from pfile='/opt/ora10g/product/ora10gr2/pfile.txt';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2022728 bytes
Variable Size             176161464 bytes
Database Buffers          432013312 bytes
Redo Buffers                2170880 bytes
Database mounted.
SQL> 

在rman的run块中更新控制文件中备份所在地的信息指向新的备份所在地

[oracle@rhl6 ora10g]$ export ORACLE_SID=ora10gr2
[oracle@rhl6 ora10g]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 25 21:36:33 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORA10GR2 (DBID=4105654577, not open)

RMAN> catalog start with '/opt/ora10g/product/ora10gr2/backup/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /opt/ora10g/product/ora10gr2/backup/

List of Files Unknown to the Database
=====================================
File Name: /opt/ora10g/product/ora10gr2/backup/ARCH_0GO0AB8A_16_1.ARCH
File Name: /opt/ora10g/product/ora10gr2/backup/DB_SPFILE_20130125_805645585_18_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0DO0AB7Q_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0EO0AB86_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0CO0AB5M_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/rman_full.log
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0BO0AB5M_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/DB_CTL_20130125_805645580_17_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/ARCH_0FO0AB8A_15_1.ARCH

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/ora10g/product/ora10gr2/backup/ARCH_0GO0AB8A_16_1.ARCH
File Name: /opt/ora10g/product/ora10gr2/backup/DB_SPFILE_20130125_805645585_18_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0DO0AB7Q_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0EO0AB86_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0CO0AB5M_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0BO0AB5M_1_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/DB_CTL_20130125_805645580_17_1.BAK
File Name: /opt/ora10g/product/ora10gr2/backup/ARCH_0FO0AB8A_15_1.ARCH

List of Files Which Where Not Cataloged
=======================================
File Name: /opt/ora10g/product/ora10gr2/backup/rman_full.log
  RMAN-07517: Reason: The file header is corrupted

此处是rman备份的log日志不可用来做备份片,所以报错。

将数据库restore并做recover

RMAN> run {
2> allocate channel dev1 type disk;
3> allocate channel dev2 type disk;
4> set newname for datafile 1 to '/opt/ora10g/product/ora10gr2/SYSTEM01.DBF';
5> set newname for datafile 4 to '/opt/ora10g/product/ora10gr2/USERS01.DBF';
6> set newname for datafile 3 to '/opt/ora10g/product/ora10gr2/SYSAUX01.DBF';
7> set newname for datafile 2 to '/opt/ora10g/product/ora10gr2/UNDOTBS01.DBF';
8> restore database;
9> switch datafile all;
10> recover database;
11> release channel dev1;
12> release channel dev2;
13> }

allocated channel: dev1
channel dev1: sid=155 devtype=DISK

allocated channel: dev2
channel dev2: sid=154 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-JAN-13

channel dev1: starting datafile backupset restore
channel dev1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /opt/ora10g/product/ora10gr2/UNDOTBS01.DBF
restoring datafile 00003 to /opt/ora10g/product/ora10gr2/SYSAUX01.DBF
channel dev1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\BACKUP\FULL_DB_BACKUP_ORA10GR2_20130125_0CO0AB5M_1_1.BAK
channel dev2: starting datafile backupset restore
channel dev2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/ora10g/product/ora10gr2/SYSTEM01.DBF
restoring datafile 00004 to /opt/ora10g/product/ora10gr2/USERS01.DBF
channel dev2: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\BACKUP\FULL_DB_BACKUP_ORA10GR2_20130125_0BO0AB5M_1_1.BAK
channel dev1: restored backup piece 1
failover to piece handle=/opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0CO0AB5M_1_1.BAK tag=FULL_BACKUP
channel dev1: restore complete, elapsed time: 00:01:11
channel dev2: restored backup piece 1
failover to piece handle=/opt/ora10g/product/ora10gr2/backup/FULL_DB_BACKUP_ORA10GR2_20130125_0BO0AB5M_1_1.BAK tag=FULL_BACKUP
channel dev2: restore complete, elapsed time: 00:01:28
Finished restore at 25-JAN-13

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=805671684 filename=/opt/ora10g/product/ora10gr2/SYSTEM01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=6 stamp=805671684 filename=/opt/ora10g/product/ora10gr2/USERS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=805671685 filename=/opt/ora10g/product/ora10gr2/SYSAUX01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=805671685 filename=/opt/ora10g/product/ora10gr2/UNDOTBS01.DBF

Starting recover at 25-JAN-13

starting media recovery

channel dev1: starting archive log restore to default destination
channel dev1: restoring archive log
archive log thread=1 sequence=9
channel dev1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\BACKUP\ARCH_0FO0AB8A_15_1.ARCH
channel dev2: starting archive log restore to default destination
channel dev2: restoring archive log
archive log thread=1 sequence=10
channel dev2: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\BACKUP\ARCH_0GO0AB8A_16_1.ARCH
channel dev1: restored backup piece 1
failover to piece handle=/opt/ora10g/product/ora10gr2/backup/ARCH_0FO0AB8A_15_1.ARCH tag=TAG20130125T142617
channel dev1: restore complete, elapsed time: 00:00:01
archive log filename=/opt/ora10g/product/ora10gr2/archivelog/1_9_803427765.dbf thread=1 sequence=9
channel dev2: restored backup piece 1
failover to piece handle=/opt/ora10g/product/ora10gr2/backup/ARCH_0GO0AB8A_16_1.ARCH tag=TAG20130125T142617
channel dev2: restore complete, elapsed time: 00:00:01
archive log filename=/opt/ora10g/product/ora10gr2/archivelog/1_10_803427765.dbf thread=1 sequence=10
unable to find archive log
archive log thread=1 sequence=11
released channel: dev1
released channel: dev2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/25/2013 21:41:30
RMAN-06054: media recovery requesting unknown log: thread 1 seq 11 lowscn 577972

此处发现recover的时候缺少日志,如果我们把源库的所有自从full backup一来的归档和联机日志全部拷到目标库,这样就能够和源库保持一致了,但是由于源库始终是在运行的,所有目标库肯定无法和源库保持一致,并完成完全恢复。所以,这里只能进行不完全恢复。

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 577972 generated at 01/25/2013 14:26:16 needed for thread 1
ORA-00289: suggestion :
/opt/ora10g/product/ora10gr2/archivelog/1_11_803427765.dbf
ORA-00280: change 577972 for thread 1 is in sequence #11


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/opt/ora10g/product/ora10gr2/archivelog/1_11_803427765.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> alter database open resetlogs;

Database altered.

检查测试数据

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> select * from test;

        ID NAME                 MYTIME
---------- -------------------- -------------------
         1 yallonking           2013/01/22 22:00:35
         2 YALLONKING           2013/01/25 14:10:10

注意:此处的2条数据都在,说明之前虽然是不完全恢复,但是由于源库不是很繁忙,且迁移之前做了日志切换以及触发检查点和归档了未归档的日志,所以,即使是后来添加上去的第二条记录在迁移之前已经写入到归档日志或者数据文件中了,而不是在联机日志中使其在迁移过程中丢失。

小结:这个案例说明,以后也可这样进行跨平台跨操作系统位数的数据库迁移。

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: