RMAN数据库迁移(跨平台跨操作系统位数)
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条数据都在,说明之前虽然是不完全恢复,但是由于源库不是很繁忙,且迁移之前做了日志切换以及触发检查点和归档了未归档的日志,所以,即使是后来添加上去的第二条记录在迁移之前已经写入到归档日志或者数据文件中了,而不是在联机日志中使其在迁移过程中丢失。
小结:这个案例说明,以后也可这样进行跨平台跨操作系统位数的数据库迁移。