技术博客 数据库、运维与工程实践笔记

Oracle从windows迁移至linux 使用RMAN

2025-04-07Linux/AIX / Oracle / RAC / RMAN

实验环境：windows 2008 R2 & RHEL 6.5Oracle版本：11.2.0.4参考文档:

  1. 将windows数据库shutdown immediate，然后启动到mount模式下2. 生成pfile文件create pfile='d:rmanbackuppfile.ora' from spfile;3. 进行RMAN备份数据库和控制文件rman target /
  1. 将pfile.ora和RMAN备份的数据文件和控制文件传到linux上
  1. 修改传送过去的pfile.ora文件，将一系列目录改为linux上的对应目录,注意sga和PGA的修改

假如报错,根据报错排查对应错误一定需要注意,修改或者加上redo日志的位置,不然就会变成下面这样这TM就很尴尬了…怎么会犯这种低级错误呢,..

  1. SQL> set linesize 999
  2. SQL> col member format a80;
  3. SQL> select * from v$logfile;
  4. GROUP# STATUS TYPE MEMBER IS_
  5. ———- ———- ——- ——————————————————————————– —
  6. 1ONLINE /u01/app/oracle/product/11.2.0/db_1/dbs/D:ORACLEPRODUCTORADATAORCLREDO01.LOG NO
  7. 3ONLINE /u01/app/oracle/product/11.2.0/db_1/dbs/D:ORACLEPRODUCTORADATAORCLREDO03.LOG NO
  8. 2ONLINE /u01/app/oracle/product/11.2.0/db_1/dbs/D:ORACLEPRODUCTORADATAORCLREDO02.LOG NO

    9. 我这就只能通过事后新建几个redo日志组,然后将这几个"另类"删掉来解决问题了…

  1. 恢复控制文件并且mount数据库rman target /
  1. [oracle@oratest ~]$ rman target /
  2. Recovery Manager: Release 11.2.0.4.0 – Production on Wed Dec 7 14:48:47 2016
  3. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  4. connected to target database: ORCL (not mounted)
  5. RMAN> restore controlfile from '/tmp/rmanbackup/CONTROL_03RMSH55_1_1';
  6. Starting restore at 07-DEC-16
  7. using target database control file instead of recovery catalog
  8. allocated channel: ORA_DISK_1
  9. channel ORA_DISK_1: SID=20 device type=DISK
  10. channel ORA_DISK_1: restoring control file
  11. channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
  12. output file name=/oradata/CONTROL01.CTL
  13. output file name=/oradata/CONTROL02.CTL
  14. output file name=/oradata/CONTROL03.CTL
  15. Finished restore at 07-DEC-16
  16. RMAN> sql 'alter database mount';
  17. sql statement: alter database mount
  18. released channel: ORA_DISK_1
  1. 清理RMAN记录的catalog的失效记录
  1. RMAN> crosscheck backup;
  2. Starting implicit crosscheck backup at 07-DEC-16
  3. allocated channel: ORA_DISK_1
  4. channel ORA_DISK_1: SID=20 device type=DISK
  5. Crosschecked 2 objects
  6. Finished implicit crosscheck backup at 07-DEC-16
  7. Starting implicit crosscheck copy at 07-DEC-16
  8. using channel ORA_DISK_1
  9. Finished implicit crosscheck copy at 07-DEC-16
  10. searching for all files in the recovery area
  11. cataloging files…
  12. no files cataloged
  13. using channel ORA_DISK_1
  14. crosschecked backup piece: found to be 'EXPIRED'
  15. backup piece handle=D:RMANBACKUPFULL_01RMSH1N_1_1 RECID=1 STAMP=929973303
  16. crosschecked backup piece: found to be 'EXPIRED'
  17. backup piece handle=D:RMANBACKUPFULL_02RMSH3P_1_1 RECID=2 STAMP=929973370
  18. Crosschecked 2 objects
  19. RMAN> delete expired backup;
  20. using channel ORA_DISK_1
  21. List of Backup Pieces
  22. BP Key BS Key Pc# Cp# Status Device Type Piece Name
  23. ——- ——- — — ———– ———– ———-
  24. 1 1 1 1 EXPIRED DISK D:RMANBACKUPFULL_01RMSH1N_1_1
  25. 2 2 1 1 EXPIRED DISK D:RMANBACKUPFULL_02RMSH3P_1_1
  26. Do you really want to delete the above objects (enter YES or NO)? yes
  27. deleted backup piece
  28. backup piece handle=D:RMANBACKUPFULL_01RMSH1N_1_1 RECID=1 STAMP=929973303
  29. deleted backup piece
  30. backup piece handle=D:RMANBACKUPFULL_02RMSH3P_1_1 RECID=2 STAMP=929973370
  31. Deleted 2 EXPIRED objects
  1. 将RMAN备份文件加入到catalog

有几个加几个,或者假如备份文件很多,那么用下面的命令

注意假如是直接指定了一个目录,那么最后必须接/表示这是一个目录,否则会被认为是一个前缀

9.开始从catalog中恢复数据库,并且set newname来指定新的路径,switch来将新的路径写到控制文件run {set newname for database to '/oradata/orcl/datafile_%U';restore database;}

10.开启数据库,使用resetlogs upgrade参数来重建redo文件SQL> alter database open resetlogs upgrade;顺便生成一下spfileSQL> create spfile from pfile='/tmp/rmanbackup/pfile.ora';

重新编译一下无效对象(是个好习惯)@?/rdbms/admin/utlrp.sql;

NOTE:遇到了一个问题,是进行数据库迁移之后,temp数据文件因为不被RMAN所保护,需要检查tempfile:

  1. ERROR at line 7:
  2. ORA-01187: cannot read from file because it failed verification tests
  3. ORA-01110: data file 201: '/u01/app/oracle/product/11.2.0/db_1/dbs/D:ORACLEPRODUCTORADATAORCLTEMP01.DBF'

    4. 检查临时表空间的数据文件

  4. col name for a50;
  5. select file#, ts#, name, status from v$tempfile;
  6. FILE# TS# NAME STATUS
  7. ———- ———- ————————————————– ——-
  8. 1 3 D:ORACLEPRODUCTORADATAORCLTEMP01.DBF ONLINE

    9. 网上的资料都是说直接删除该数据文件,但是不行

  9. SQL> alter database tempfile 'D:ORACLEPRODUCTORADATAORCLTEMP01.DBF' drop;
  10. alter database tempfile 'D:ORACLEPRODUCTORADATAORCLTEMP01.DBF' drop
  11. *
  12. ERROR at line 1:
  13. ORA-01516: nonexistent log file, data file, or temporary file "D:ORACLEPRODUCTORADATAORCLTEMP01.DBF"

    14. 直接删除,会提示不能删除默认临时表空间:

  14. SQL> drop tablespace temp;
  15. drop tablespace temp
  16. *
  17. ERROR at line 1:
  18. ORA-12906: cannot drop default temporary tablespace

解决方案:新建一个临时表空间,将其设置为默认临时表空间,再删除原本的临时表空间(本人强迫症,所以删掉原来的temp之后,再重新建一个temp,将表空间设置回temp,看着顺眼)需要数据库open状态

新建临时表空间temp2

设置temp2为默认临时表空间

删除原来的临时表空间temp

重新建临时表空间temp

将temp设置为默认临时表空间

注意:上一步修改完之后,假如想立即删除temp2表空间,那么此操作会宕住需要shutdown immediate 退出再重新进sqlplusstartup

删除临时表空间temp2和数据文件

再次检查一遍,OK

  1. SQL> col name for a50;
  2. SQL> select file#, ts#, name, status from v$tempfile;
  3. FILE# TS# NAME STATUS
  4. ———- ———- ————————————————– ——-
  5. 1 3 /oradata/orcl/TEMP01.DBF ONLINE

运行@?/rdbms/admin/utlrp.sql;重新编译一下无效对象这次成功了OK,数据库迁移完成需要注意,假如两者版本不一致或者linux上打了新补丁,做完这一步之后照着README.html运行升级脚本,再次编译无效对象再次重申:对数据库做迁移,升级之类的操作后编译无效对象是个好习惯