数据库从非ASM到ASM的迁移
数据库从非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
