通过rman实现单实例到rac环境数据库迁移

32rwe111

环境
迁移准备

• 开启数据库归档模式
  
  

检查数据库是否已经开启归档模式

SQL>select log_mode from v$database;

LOG_MODE
------------

ARCHIVELOG

（如果为非归档模式需要开启归档模式）

• 在源库中创建backup表并插入一条数据，以便确认迁移是否成功
  
  

SQL>create table backup(id number,name varchar2(100));

Tablecreated.

SQL>insert into backup values(1,'Bianca');

1row created.

SQL>commit;

Commit complete.

• 使用nocatalog模式进行完全备份
  
• u  源端创建rman备份集目录
  
  

[oracle@test ~]$ mkdir/u01/app/backup

• nocatalog模式连接数据库 (记录dbid）
  
  

[oracle@test~]$ rman target / nocatalog

RecoveryManager: Release 11.2.0.4.0 - Production on Thu Apr 2 10:14:17 2015

Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connectedto target database: YAYI (DBID=501826307)

using target databasecontrol file instead of recovery catalog

• 修改参数
  
  

RMAN> show all

CONFIGURE RETENTION POLICY TOREDUNDANCY 2;

CONFIGURE CONTROLFILEAUTOBACKUP ON;

  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FORDEVICE TYPE DISK TO

2> '/u01/app/backup/%F';

• 完全备份数据库
  
  

RMAN> backup databaseformat '/u01/app/backup/full_%d_%T_%s_P.bak';

RMAN> backup archivelogall format '/u01/app/backup/arch_%d_%T_%s_%p.bak' DELETE INPUT;   

• 备份后检查信息
  
  

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time#Pieces #Copies Compressed Tag
------- -- -- - -------------------------- ------- ------- ---------- ---
1       B F  A DISK        31-MAR-15       1      1       NO        
TAG20150331T164644
2       B F  A DISK        31-MAR-15       1      1       NO        
TAG20150331T164901
3       B F  A DISK        02-APR-15       1      1       NO        
TAG20150402T102243
4       B F  A DISK        02-APR-15       1      1       NO        
TAG20150402T102705
5       B A  A DISK        02-APR-15       1      1       NO        
TAG20150402T105917
6      B  F  ADISK        02-APR-15       1      1       NO        
TAG20150402T105927

rac环境中进行数据恢复

• 将备份集传到rac环境中
  
  

[oracle@rac1~]$ mkdir /u01/app/backup

[oracle@testapp]$ cd backup/
[oracle@test backup]$ scp *oracle@192.168.80.101:/u01/app/backup/
oracle@192.168.80.101'spassword:
arch_YAYI_20150402_6_1.bak                  100%   86MB  3.4MB/s   00:25
c-501826307-20150402-00                       100% 9600KB   9.4MB/s 00:01   

full_YAYI_20150402_4_P.bak                    100% 1038MB   2.6MB/s   06:35   

• 还原并修改初始化文件
  
  

还原spfile

[oracle@rac1~]$ rman target /

RecoveryManager: Release 11.2.0.4.0 - Production on Thu Apr 2 10:48:02 2015

Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connectedto target database (not started)

RMAN>startup nomount;

startupfailed: ORA-01078: failure in processing system parameters
LRM-00109:could not open parameter file
'/u01/app/oracle/product/11.2.0/db_1/dbs/inityayi1.ora'

startingOracle instance without parameter file for retrieval of spfile
Oracleinstance started

TotalSystem Global Area    1068937216 bytes

FixedSize                     2260088 bytes
VariableSize                281019272 bytes
DatabaseBuffers             780140544 bytes

RedoBuffers                   5517312 bytes

RMAN>restore spfile to '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileyayi1.ora'from '/u01/app/backup/c-501826307-20150402-01';

查看asm实例中相关参数

[grid@rac1~]$ export ORACLE_SID=+ASM1
[grid@rac1~]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Thu Apr 2 14:21:48 2015

Copyright(c) 1982, 2013, Oracle.  All rightsreserved.


Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Withthe Real Application Clusters and Automatic Storage Management options

SQL>select state,redundancy,total_mb,free_mb,name,failgroup from v$asm_disk;

STATE      REDUNDANCY              TOTAL_MB    FREE_MB NAME           
FAILGROUP
------------------------------- ---------- ---------- --------------- --------------------
NORMAL     UNKNOWN                    20473      20077 GRID_0000      
GRID_0000

SQL>select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBERNAME            STATE        TOTAL_MB    FREE_MB
--------------------------- ---------- ---------- ----------
           1 GRID            MOUNTED         20473      20077

使用asmcmd 命令

[grid@rac1~]$ asmcmd
ASMCMD>ls
GRID/
ASMCMD>cd GRID
ASMCMD>ls
rac-cluster/

ASMCMD>

创建pfile 修改相关参数

SQL>create pfile from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileyayi1.ora';

Filecreated.

yayi2.__db_cache_size=381681664

yayi1.__db_cache_size=398458880

yayi2.__java_pool_size=4194304

yayi1.__java_pool_size=4194304

yayi2.__large_pool_size=8388608

yayi1.__large_pool_size=8388608

yayi1.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment

yayi2.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment

yayi2.__pga_aggregate_target=209715200

yayi1.__pga_aggregate_target=209715200

yayi2.__sga_target=633339904

yayi1.__sga_target=633339904

yayi2.__shared_io_pool_size=0

yayi1.__shared_io_pool_size=0

yayi2.__shared_pool_size=226492416

yayi1.__shared_pool_size=209715200

yayi2.__streams_pool_size=0

yayi1.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/yayi/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='GRID/yayi/controlfile/current.287.875093245'

*.db_block_size=8192

*.db_create_file_dest='GRID'

*.db_domain=''

*.db_name='yayi'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=yayiXDB)'

*.fal_client='yayi'

*.fal_server='DB11G'

yayi1.instance_number=1

yayi2.instance_number=2

*.log_archive_dest_1='LOCATION=+ARCHDISKVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=yayi'

*.log_archive_dest_2='SERVICE=db11gASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=db11g'

*.log_archive_format='%t_%s_%r.dbf'

*.pga_aggregate_target=209715200

*.processes=300

*.remote_listener='scan:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=631242752

*.standby_file_management='AUTO'

yayi2.thread=2

yayi1.thread=1

yayi2.undo_tablespace='UNDOTBS2'

yayi1.undo_tablespace='UNDOTBS1'

恢复数据库

创建spfile到共享存储下

SQL> createspfile='+GRID/yayi/spfileyayi.ora' frompfile='/u01/app/oracle/product/11.2.0/db_1/dbs/inityayi1.ora';

File created.

修改pfile指向spfile

SPFILE='+GRID/yayi/spfileyayi.ora'

并创建审计文件文件夹

启动到nomount状态

startupnomount

恢复控制文件

RMAN>restore controlfile from '/u01/app/backup/c-501826307-20150402-01';

启动到mount状态

alter databasemount；

恢复数据文件

（需要先在制定目录下创建oradata目录）

RMAN>restore database;

RMAN>recoverdatabase;

以resetlogs机制打开数据库

alter databaseopen resetlogs；

• 3.  检测
  
  

查询之前插入的数据，确认迁移成功

SQL> select * from backup;

SQL> select* from backup;

        ID NAME

----------------------------------------

          1 Bianca

本文为第一次迁移时生成的文档，可能还有很多不太完善或者不太准确的地方，以后逐步完善，也请各位大神多提意见，帮助改正，谢谢