使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

1. windows恢复至windows

通过Rman备份数据方式为全备份命令:

Rman>rman database

备份包括两份文件,其中一个是数据文件,另一个为控制文件。还需要我们从源库中复制初始化文件(复制到目标数据库相应地方),其通常在%Oracle_Home%\database目录下SpfileXXX.ora。

其中Spfile需要按照备份数据库进行修改,但Spfile为二进制文件,需要先将其转换成pfile使用txt修改,命令为:

SQl>create pfile=‘存储路径’ from spfile(spfile以复制到相应目录)

创建之后打开pfile,如下所示:

[javascript] view plain copy
  1. orcl1234.__db_cache_size=188743680  
  2. orcl1234.__java_pool_size=12582912  
  3. orcl1234.__large_pool_size=4194304  
  4. orcl1234.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment  
  5. orcl1234.__pga_aggregate_target=285212672  
  6. orcl1234.__sga_target=536870912  
  7. orcl1234.__shared_io_pool_size=0  
  8. orcl1234.__shared_pool_size=322961408  
  9. orcl1234.__streams_pool_size=0  
  10. *.audit_file_dest='D:\app\Administrator\admin\orcl1234\adump'  
  11. *.audit_trail='db'  
  12. *.compatible='11.2.0.0.0'  
  13. *.control_files='D:\app\Administrator\oradata\orcl1234\control01.ctl'  
  14. *.db_block_size=8192  
  15. *.db_domain=''  
  16. *.db_name='orcl1234'  
  17. *.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'  
  18. *.db_recovery_file_dest_size=4039114752  
  19. *.diagnostic_dest='D:\app\Administrator'  
  20. *.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1234XDB)'  
  21. *.local_listener='LISTENER_ORCL1234'  
  22. *.memory_target=822083584  
  23. *.open_cursors=300  
  24. *.processes=150  
  25. *.remote_login_passwordfile='EXCLUSIVE'  
  26. *.undo_tablespace='UNDOTBS1'  
上面文件我已经修改好(我的%Oracle_home%为D:\app\Administrator\product\11.2.0\dbhome_1)

control_files路径可以多添加几个做备份。local_listener值为下面tnsnames.ora中的监听名字。

接下来需要配置监听service name与tnsnames.ora 要不然初始化时会出现错误。

tnsnames.ora Network Configuration File里需要添加:

LISTENER_OCRL1234 =

  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )

ORCL1234 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1234)
    )
  )

配置完成后要将pfile中更改local_listener='LISTENER_OCRL1234'‘;

listener.ora需要添加红色部分:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl1234)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = orcl1234)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = Oracle8)
      (SID_NAME = ORCL)
    )
  )

注:OCRL1234是要恢复的数据库实例名或数据库名

其中listener.ora也可以通过DBCA来配置,具体方法就不讲了。

下面进入正题:

>ORADIM -NEW -SID ORCL1234

 创建一个实例,注意此处实例名称与 源数据库相同!!!

>SET ORACLE_SID=ORCL1234

将当前实例设置为orcl1234

接下来连接到空实例进行pfile配置,命令见下图:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

待初始化文件处理后启动不挂载数据库,进入RMAN,命令如下所示:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

需要先获取源数据库文件的DBID,进入RAMN后set dbid=XXX,然后恢复控制文件到初始化文件指定目录,最后将数据库设置为挂载状态,这几步没有问题如下图:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

接下来问题来了,挂载后执行RESTORE DATABASE修复数据库后,出现如下错误。是因为源数据库与我们的备份数据库数据文件路径不一致造成的,如下图:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

那当然是将数据库文件设置修改为备份数据库路径,入乡随俗吗,具体执行步骤如下图所示:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

接下来恢复数据库,问题又来了如下图:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

执行恢复命令,因为数据文件有5个,所以挨个按照下面命令执行:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

重新存储后恢复数据库就正常了:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

最后一步,打开数据库:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

又出现错误了!日志文件路径又和源数据库不同,只好再修改了:

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

好了,但是又出现别的错误了!!

使用Rman将Windows Oracle 11gR2 32位中数据库恢复至Windows Oracle 11gR2 64位 与 centos 6.5 Oracle 11gR2 32位

唉,这又是什么错误啊!!原来是从32位转到64位上会出问题,反过来也一样,看看下面是怎么处理的吧!

ORACLE 11g R2 32位备份恢复到ORACLE 11g R2 64位 问题处理

操作步骤:源数据库是64位oracle11gR2软件,通过rman完整备份数据库(包括参数文件、数据文件、控制文件、日志文件) 恢复到异机32位oracle11gR2同名数据库中。

错误现象:ORA-06553:PLS-801 内部错误

错误原因:用64位系统上的备份片将数据库还原到32位系统中所产生,反过来也会产生此错误。

解决方案:运行脚本用32位系统重新编译一下内核参数即可

具体步骤:SQL> conn / as sysdba; 

                   SQL> shutdown immediate;
        SQL> startup upgrade;

        SQL> @?/rdbms/admin/utlirp.sql

        SQL> @?/rdbms/admin/utlrp.sql

        SQL> shutdown immediate;

        SQL> startup;

其中:

utlirp.sql的作用是把相关内容全部在32bit平台下编译一遍.

utlrp.sql的作用是编译所有失效对象.

然后再重新连接,就不会报错了。

 

SQL> conn xxx/xxx

Connected.


--End--

2. windows恢复至centos 

恢复至centos过程在restore database之前与windows相同,因此前面不说了,开始restore database。

通过执行命令:

RMAN> list backup of datafile 1;
List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.25G      DISK        00:00:56     15-APR-15      
        BP Key: 3   Status: EXPIRED  Compressed: NO  Tag: TAG20150415T101325
        Piece Name: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL1234\BACKUPSET\2015_04_15\O1_MF_NNNDF_TAG20150415T101325_BLVLB5RF_.BKP
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6379210    15-APR-15 E:\APP\ADMINISTRATOR\ORADATA\ORCL1234\SYSTEM01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    1.25G      DISK        00:01:03     15-APR-15      
        BP Key: 5   Status: EXPIRED  Compressed: NO  Tag: TAG20150415T123148
        Piece Name: E:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL1234\BACKUPSET\2015_04_15\O1_MF_NNNDF_TAG20150415T123148_BLVTFNPY_.BKP
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6385668    15-APR-15 E:\APP\ADMINISTRATOR\ORADATA\ORCL1234\SYSTEM01.DBF

发现其备份路径还是在windows下的,因此需要执行命令:

RMAN>catalog start with '/u01/app/oracle/flash_recovery_area/ORCL1234/backupset/2015_04_15' ;     //拷贝过来的备份文件路径,将备份集注册到控制文件


searching for all files that match thepattern/u01/app/oracle/flash_recovery_area/ORCL1234/backupset/2015_04_15


List of Files Unknown to the Database

=====================================

File Name:/u01/app/oracle/flash_recovery_area/ORCL1234/backupset/2015_04_15/O1_MF_NCSNF_TAG20150415T123148_BLVTHQ3F_.BKP

File Name:/u01/app/oracle/flash_recovery_area/ORCL1234/backupset/2015_04_15/O1_MF_NNNDF_TAG20150415T123148_BLVTFNPY_.BKP


Do you really want to catalog the abovefiles (enter YES or NO)? y

cataloging files...

cataloging done


List of Cataloged Files

=======================

File Name:/u01/app/oracle/flash_recovery_area/ORCL1234/backupset/2015_04_15/O1_MF_NCSNF_TAG20150415T123148_BLVTHQ3F_.BKP

File Name:/u01/app/oracle/flash_recovery_area/ORCL1234/backupset/2015_04_15/O1_MF_NNNDF_TAG20150415T123148_BLVTFNPY_.BKP

然后再执行:

RMAN> run{

2> set newname for datafile 1 to'/u01/app/oracle/oradata/orcl1234/system01.dbf';

3> set newname for datafile 2 to'/u01/app/oracle/oradata/orcl1234/sysaux01.dbf';

4> set newname for datafile 3 to'/u01/app/oracle/oradata/orcl1234/undotbs01.dbf';

5> set newname for datafile 4 to'/u01/app/oracle/oradata/orcl1234/users01.dbf';

6> set newname for datafile 5 to'/u01/app/oracle/oradata/orcl1234/example01.dbf';

7> restore database;

8> switch datafile all;     //这一句防止RMAN 06094错误

9> }


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


Starting restore at 25-APR-15

using channel ORA_DISK_1


channel ORA_DISK_1: restoring datafile00001

input datafile copy RECID=6STAMP=877965679 filename=/u01/app/oracle/oradata/orcl1234/example01.dbf

destination for restore of datafile00001: /u01/app/oracle/oradata/orcl1234/system01.dbf

channel ORA_DISK_1: copied datafilecopy of datafile 00001

output filename=/u01/app/oracle/oradata/orcl1234/system01.dbf RECID=7STAMP=877966366

channel ORA_DISK_1: restoring datafile00002

input datafile copy RECID=5STAMP=877965676 filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/E:APPADMINISTRATORORADATAORCL1234SYSAUX01.DBF

destination for restore of datafile00002: /u01/app/oracle/oradata/orcl1234/sysaux01.dbf

channel ORA_DISK_1: copied datafilecopy of datafile 00002

output filename=/u01/app/oracle/oradata/orcl1234/sysaux01.dbf RECID=8STAMP=877966386

channel ORA_DISK_1: restoring datafile00003

input datafile copy RECID=4STAMP=877965655 filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/E:APPADMINISTRATORORADATAORCL1234UNDOTBS01.DBF

destination for restore of datafile00003: /u01/app/oracle/oradata/orcl1234/undotbs01.dbf

channel ORA_DISK_1: copied datafilecopy of datafile 00003

output filename=/u01/app/oracle/oradata/orcl1234/undotbs01.dbf RECID=9STAMP=877966424

channel ORA_DISK_1: restoring datafile00004

input datafile copy RECID=3STAMP=877965652 filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/E:APPADMINISTRATORORADATAORCL1234USERS01.DBF

destination for restore of datafile00004: /u01/app/oracle/oradata/orcl1234/users01.dbf

channel ORA_DISK_1: copied datafilecopy of datafile 00004

output filename=/u01/app/oracle/oradata/orcl1234/users01.dbf RECID=10STAMP=877966435

channel ORA_DISK_1: restoring datafile00005

input datafile copy RECID=2STAMP=877965650 filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/E:APPADMINISTRATORORADATAORCL1234EXAMPLE01.DBF

destination for restore of datafile00005: /u01/app/oracle/oradata/orcl1234/example01.dbf

channel ORA_DISK_1: copied datafilecopy of datafile 00005

output filename=/u01/app/oracle/oradata/orcl1234/example01.dbf RECID=11STAMP=877966437

Finished restore at 25-APR-15


datafile 1 switched to datafile copy

input datafile copy RECID=12STAMP=877966438 filename=/u01/app/oracle/oradata/orcl1234/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=13STAMP=877966439 filename=/u01/app/oracle/oradata/orcl1234/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=14STAMP=877966439 filename=/u01/app/oracle/oradata/orcl1234/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=15STAMP=877966439 filename=/u01/app/oracle/oradata/orcl1234/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=16STAMP=877966440 filename=/u01/app/oracle/oradata/orcl1234/example01.dbf

最后执行:


RMAN> recover database;


Starting recover at 25-APR-15

using channel ORA_DISK_1


starting media recovery


unable to find archived log

archived log thread=1 sequence=297

RMAN-00571:===========================================================

RMAN-00569: =============== ERRORMESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of recover commandat 04/25/2015 15:35:55

RMAN-06054: media recovery requestingunknown archived log for thread 1 with sequence 297 and starting SCNof 6385668

//以上错误只是由于联机重做日志问题,单纯联系恢复可以忽略

最后以open resetlogs方式打开数据库

RMAN> alter database open resetlogs;

database opened 

至此不同平台数据库恢复完成


###恢复过程中遇到问题:

Oracle 11gORA-00845: MEMORY_TARGET not supported on this system

在oracle 11g中新增的内存自动管理的参数MEMORY_TARGET,它能自动调整SGA和PGA,这个特性需要用到/dev/shm共享文件系统,而且要求/dev/shm必须大于MEMORY_TARGET,如果/dev/shm比MEMORY_TARGET小就会报错,使用命令查看/dev/shm大小:

[[email protected] ~]$ df -lh
Filesystem                      Size  Used Avail Use% Mounted on
/dev/mapper/vg_primary-lv_root   50G   18G   30G  37% /
tmpfs                           4.0G  877M  3.2G  22% /dev/shm
/dev/sda1                       485M   38M  423M   9% /boot
/dev/mapper/vg_primary-lv_home  240G  4.7G  224G   3% /home

可以使用如下方法更改:

[[email protected] ~]$ mount -o remount,size=4G /dev/shm

或者通过编辑:

[[email protected] ~]$  vi /etc/fstab

tmpfs                   /dev/shm                tmpfs   defaults,size=4G 0 0
devpts                  /dev/pts                devpts  gid=5,mode=620   0 0
sysfs                   /sys                    sysfs   defaults         0 0
proc                    /proc                   proc    defaults         0 0
永久更改,再重启数据库就会正常打开。



ORA-00322: log 3 of thread 1 is not current copy

SQLALTER DATABASE CLEAR LOGFILE 'filename';



SQL>startup

RA-03113: end-of-file on communicationchannel

Process ID: 6283

Session ID: 125 Serial number: 5 

SQL> startup mount

SQL> alter database clear unarchivedlogfile group 1;

Database altered.

SQL> alter database open ;

Database altered.