适用范围

oracle 11g以上

方案概述

利用rman的backup as copy将数据库从一个asm磁盘组迁移到新建的asm磁盘组中，实现跨存储迁移数据库。

实施步骤

1、重新添加ocr中的数据库信息

alter system set  db_unique_name=TF_UOPPDB sid='*' scope=spfile; --修改数据库名称，此步可选
srvctl stop database -d UOPPDB 
srvctl config  database -d UOPPDB  ---记录相关信息
srvctl remove  database -d UOPPDB 

添加新的数据库信息到OCR

srvctl add  database -d TF_UOPPDB -p '+UOPPDATA/UOPPDB/spfileUOPPDB.ora' -o /u01/app/oracle/product/11.2.0/dbhome_1 
srvctl add  instance -d TF_UOPPDB -i UOPPDB1 -n UOPPDB1
srvctl add  instance -d TF_UOPPDB -i UOPPDB2 -n UOPPDB2
srvctl start database -d TF_UOPPDB

2、记录控制文件路径

show parameter control_files

15:14:53 sys@TF_UOPPDB-UOPPDB1.bocd.com.cn >show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +UOPPDATA/uoppdb/controlfile/c
                                                 urrent.256.1054842077, +UOPPFR
                                                 A/uoppdb/controlfile/current.2
                                                 56.1054842077

3、执行backup as copy 备份

vi backup_as_copy.sh

rman target / nocatalog msglog /tmp/rman_backup_as_copy.log  << eof
run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
allocate channel ch03 type disk;
backup   as copy  incremental level=0  database format '+UOPPDATA';
release channel ch00;
release channel ch01;
release channel ch02;
release channel ch03;
}
exit
eof 

nohup sh backup_as_copy.sh &

4、修改spfile里的控制文件信息

create pfile='/tmp/pfile.ora' from spfile;
shutdown abort

修改pfile.ora中的控制文件路径如下：
*.control_files='+UOPPDATA','+UOPPFRA'
创建新的spfile路径
SQL> startup nomount pfile='/tmp/pfile.ora';
SQL> create spfile='+UOPPDATA' from pfile='/tmp/pfile.ora';

File created.
SQL>shutdown abort
 在ASMCMD中查看新生成的spfile路径名

#srvctl modify database -d TF_UOPPDB -p +UOPPDATA/TF_UOPPDB/PARAMETERFILE/spfile.269.1055005129
查看是否修改成功
#srvctl config database -d TF_UOPPDB -a

修改$ORACLE_HOME/dbs/initUOPPDB1.ora文件
SPFILE='+UOPPDATA/TF_UOPPDB/PARAMETERFILE/spfile.269.1055005129'

5、恢复控制文件

恢复控制文件
sqlplus '/ as sysdba'
startup nomount;
!rman target /
restore controlfile from '+UOPPDATA/uoppdb/controlfile/current.256.1054842077';
alter database mount;

6、恢复数据库

run {
set newname for tempfile 1 to '+UOPPDATA';
switch tempfile all;
}
switch database to copy;
recover database;

srvctl stop database -d TF_UOPPDB -o abort
srvctl start database -d TF_UOPPDB 

7、生成redo日志

生成增加日志脚本
                                                                        
select 'alter database add logfile member '||chr(39)||'+UOPPDATA'||chr(39)||','||chr(39)||'+UOPPFRA'||chr(39)||' to group '|| group#||';' from v$log;   
生成节点1删除日志脚本                                                                   
select 'alter database drop logfile member '||chr(39)||member||chr(39)||';'  from v$logfile where member like '+UOPPDATA/uoppdb/%' or member like '+UOPPFRA/uoppdb/%';    
在删除的时候可以会报错，首先保障新添加的日志文件状态正常为非INVALID,两个节点需要多次切换日志文件:
select member,group#,STATUS  from v$logfile ;
alter system switch logfile;
alter system archive log current;  
alter system checkpoint;
select group#,status from v$log;
复制

8、确认迁移成功后ASM里删除旧的目录 rm -r UOPPDB/