A-A+

数据库从非ASM到ASM的迁移

2013年01月12日 Backup&Recovery, Database 暂无评论 阅读 979 次

数据库从非ASM到ASM的迁移
参考文档
Steps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa [ID 252219.1]
How to duplicate a controlfile when ASM is involved [ID 345180.1]

查看源文件系统

SQL> select * from v$version where rownum<5;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production

SQL> conn test/test
Connected.

测试数据
SQL> create table test (id number,name varchar2(10));

Table created.

SQL> insert into test values (1,'yallonking');

1 row created.

SQL> commit;

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10gr2/users01.dbf
/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
/u01/app/oracle/oradata/ora10gr2/system01.dbf
/u01/app/oracle/oradata/ora10gr2/test01.dbf

查看ASM磁盘组
SQL> select * from v$version where rownum<5;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production

SQL> !
[oracle@rhl5 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 20480 20430 0 20430 0 DATA/

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

查看当前文件系统的控制文件路径
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10gr2/control01.ctl
/u01/app/oracle/oradata/ora10gr2/control02.ctl
/u01/app/oracle/oradata/ora10gr2/control03.ctl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 155193108 bytes
Database Buffers 448790528 bytes
Redo Buffers 2969600 bytes
SQL> !

转移控制文件
[oracle@rhl5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 00:56:47 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ora10gr2 (not mounted)

RMAN> restore controlfile to '+DATA' from '/u01/app/oracle/oradata/ora10gr2/control01.ctl';

Starting restore at 21-AUG-12
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 21-AUG-12

查看新的控制文件
ASMCMD> pwd
+data
ASMCMD> find -t controlfile . *
+data/ORA10GR2/CONTROLFILE/backup.256.791859655

修改当前系统的控制文件参数相关
SQL> alter system set control_files='+data/ORA10GR2/CONTROLFILE/backup.256.791859655' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220844 bytes
Variable Size 155193108 bytes
Database Buffers 448790528 bytes
Redo Buffers 2969600 bytes
Database mounted.
SQL> !

将数据库文件系统备份到ASM磁盘组
[oracle@rhl5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 01:19:21 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORA10GR2 (DBID=4093928674, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 21-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/ora10gr2/system01.dbf
output filename=+DATA/ora10gr2/datafile/system.257.791860783 tag=TAG20120821T011941 recid=1 stamp=791860831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
output filename=+DATA/ora10gr2/datafile/sysaux.258.791860837 tag=TAG20120821T011941 recid=2 stamp=791860867
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
output filename=+DATA/ora10gr2/datafile/undotbs1.259.791860873 tag=TAG20120821T011941 recid=3 stamp=791860876
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/ora10gr2/test01.dbf
output filename=+DATA/ora10gr2/datafile/test.260.791860881 tag=TAG20120821T011941 recid=4 stamp=791860882
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/ora10gr2/users01.dbf
output filename=+DATA/ora10gr2/datafile/users.261.791860885 tag=TAG20120821T011941 recid=5 stamp=791860885
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/ora10gr2/controlfile/backup.262.791860885 tag=TAG20120821T011941 recid=6 stamp=791860886
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 21-AUG-12
channel ORA_DISK_1: finished piece 1 at 21-AUG-12
piece handle=+DATA/ora10gr2/backupset/2012_08_21/nnsnf0_tag20120821t011941_0.263.791860889 tag=TAG20120821T011941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 21-AUG-12

将数据库文件系统切换到ASM磁盘组
RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/ora10gr2/datafile/system.257.791860783"
datafile 2 switched to datafile copy "+DATA/ora10gr2/datafile/undotbs1.259.791860873"
datafile 3 switched to datafile copy "+DATA/ora10gr2/datafile/sysaux.258.791860837"
datafile 4 switched to datafile copy "+DATA/ora10gr2/datafile/users.261.791860885"
datafile 5 switched to datafile copy "+DATA/ora10gr2/datafile/test.260.791860881"

修改临时文件
/*
run {
set newname for tempfile 1 to '+DATA';
set newname for tempfile 2 to '+DATA';
...
switch tempfile all;
}
*/

RMAN> ALTER DATABASE OPEN;

database opened

逐步替换日志文件
[oracle@rhl5 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 21 01:27:35 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
3
/u01/app/oracle/oradata/ora10gr2/redo03.log
INACTIVE

2
/u01/app/oracle/oradata/ora10gr2/redo02.log
UNUSED

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------

1
/u01/app/oracle/oradata/ora10gr2/redo01.log
CURRENT

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------
3 /u01/app/oracle/oradata/ora10gr2/redo03.log INACTIVE
2 /u01/app/oracle/oradata/ora10gr2/redo02.log CURRENT
1 /u01/app/oracle/oradata/ora10gr2/redo01.log INACTIVE
1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
9

2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 CURRENT
7

3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 INACTIVE
3

GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------

6 rows selected.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo03.log';

Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo01.log';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------
2 /u01/app/oracle/oradata/ora10gr2/redo02.log INACTIVE
1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
9

2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
7

3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo02.log';

Database altered.

验证结果
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------
1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
9

2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
7

3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/ora10gr2/datafile/users.261.791860885
+DATA/ora10gr2/datafile/sysaux.258.791860837
+DATA/ora10gr2/datafile/undotbs1.259.791860873
+DATA/ora10gr2/datafile/system.257.791860783
+DATA/ora10gr2/datafile/test.260.791860881

SQL> conn test/test
Connected.
SQL> select * from test;

ID NAME
---------- ----------
1 yallonking

标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: