此文是在oracle10g的基础上,将数据库从文件系统迁移到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
----------------------------------------------------------------
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.
Connected.
测试数据
SQL> create table test (id number,name varchar2(10));
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
--------------------------------------------------------------------------------
/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;
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
----------------------------------------------------------------
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/
[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;
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
--------------------------------------------------------------------------------
/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.
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> !
Fixed Size 1220844 bytes
Variable Size 155193108 bytes
Database Buffers 448790528 bytes
Redo Buffers 2969600 bytes
SQL> !
转移控制文件
[oracle@rhl5 ~]$ rman target /
[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
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 21-AUG-12
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;
SQL> alter system set control_files='+data/ORA10GR2/CONTROLFILE/backup.256.791859655' scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
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> !
Fixed Size 1220844 bytes
Variable Size 155193108 bytes
Database Buffers 448790528 bytes
Redo Buffers 2969600 bytes
Database mounted.
SQL> !
将数据库文件系统备份到ASM磁盘组
[oracle@rhl5 ~]$ rman target /
[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
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;
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"
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
[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#;
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
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
3
/u01/app/oracle/oradata/ora10gr2/redo03.log
INACTIVE
2
/u01/app/oracle/oradata/ora10gr2/redo02.log
UNUSED
/u01/app/oracle/oradata/ora10gr2/redo02.log
UNUSED
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
1
/u01/app/oracle/oradata/ora10gr2/redo01.log
CURRENT
/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
---------- --------------------------------------------- ----------------
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
7
3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 INACTIVE
3
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 /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
7
3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3
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
---------- --------------------------------------------- ----------------
1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
9
2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
7
7
3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3
SQL> select file_name from dba_data_files;
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
--------------------------------------------------------------------------------
+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;
Connected.
SQL> select * from test;
ID NAME
---------- ----------
1 yallonking
---------- ----------
1 yallonking
至此,全部OK!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26143577/viewspace-741521/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26143577/viewspace-741521/