|
|
网上搜搜很多例子啊，贴两个例子：
例子一：
由于ASM磁盘组里的文件不能通过普通的操作系统命令访问，因此如果要将数据库从文件系统迁移到ASM磁盘组里，则必须通过RMAN。注意，迁移之前，如果启用了闪回数据库功能，则需要先关闭闪回数据库功能。
将数据库整体迁移到ASM的过程如下所示（本数据库启用了归档）：
<!--[if !supportLists]-->1) <!--[endif]-->修改初始化参数control_files和Flash Recovery Area：
SQL> alter database backup controlfile to '/u01/control.ctl';
SQL> alter system set control_files='+DgroupA' scope=spfile;
SQL> alter system set db_recovery_file_dest='+DgroupB';
<!--[if !supportLists]-->2) <!--[endif]-->正常关闭数据库。
SQL> shutdown immediate;
<!--[if !supportLists]-->3) <!--[endif]-->启动RMAN，并将数据库启动到nomount阶段。
[oracle@book ora10g]$ rman target /
RMAN> startup nomount;
<!--[if !supportLists]-->4) <!--[endif]-->恢复刚才备份的控制文件，由于我们设置了初始化参数control_files，将其指向了磁盘组DgroupA，因此恢复出来的控制文件会存放在DgroupA里。
RMAN> restore controlfile from '/u01/control.ctl';
Starting restore at 18-OCT-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
……
output filename=+DGROUPA/ora10g/controlfile/current.256.636273263
Finished restore at 18-OCT-07
<!--[if !supportLists]-->5) <!--[endif]-->将数据库mount起来。
RMAN> alter database mount;
<!--[if !supportLists]-->6) <!--[endif]-->通过拷贝的方式，将所有的数据文件全都迁移到ASM磁盘组（DgroupA）里。
RMAN> backup as copy database format '+DgroupA';
Starting backup at 18-OCT-07
Starting implicit crosscheck backup at 18-OCT-07
……
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/ora10g/system01.dbf
output filename=+DGROUPA/ora10g/datafile/system.257.636273329 tag=TAG20071018T063528 recid=2 stamp=636273411
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
……
Finished backup at 18-OCT-07
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
<!--[if !supportLists]-->7) <!--[endif]-->开始恢复数据库。
RMAN> recover database;
……
archi.ve log thread 1 sequence 9 is already on disk as file /u01/app/oracle/oradata/ora10g/redo02.log
……
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-OCT-07
<!--[if !supportLists]-->8) <!--[endif]-->将临时表空间所包含的临时文件迁移到磁盘组DgroupA里。
RMAN> run{
2> switch tempfile 1 to '+DgroupA';
3> }
renamed temporary file 1 to +DgroupA in control file
<!--[if !supportLists]-->9) <!--[endif]-->通过SQL*Plus连入数据库实例，将控制文件里记录的联机日志文件的路径修改为磁盘组DgroupA。
SQL> alter database rename file '/u01/app/oracle/oradata/ora10g/redo01.log' to '+DgroupA';
SQL> alter database rename file '/u01/app/oracle/oradata/ora10g/redo02.log' to '+DgroupA';
SQL> alter database rename file '/u01/app/oracle/oradata/ora10g/redo03.log' to '+DgroupA';
<!--[if !supportLists]-->10) <!--[endif]-->在RMAN中以resetlogs选项打开数据库，从而根据控制文件里记录的联机日志文件的路径（这里也就是DgroupA），自动在DgroupA里创建联机日志文件。
RMAN> alter database open resetlogs;
-----------------------
例子二：
实验环境：WinXP SP2
数据库版本：10.2.0.1
准备迁移的数据库实例名：TEST
ASM实例名：+ASM
ASM磁盘组：+TEST
创建ASM实例和磁盘组的步骤这里不再重复，请参考：http://space.itpub.net/498744/viewspace-247789
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:/WINDOWS>set ORACLE_SID=test
C:/WINDOWS>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 27 20:56:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With thePartitioning, OLAPand Data Mining options
1、修改参数文件中的control_files参数，指向ASM
TEST>show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL, F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL02.CTL, F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL03.CTL
TEST>alter system set control_files ='+test/test/control01.ctl' scope=spfile;
System altered.
修改完成后关闭数据库
TEST>shut immediate
2、使用RMAN将controlfile迁移至ASM
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:/WINDOWS>rmantarget=sys/oracle@test
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 27 20:58:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 71304332 bytes
Database Buffers 130023424 bytes
Redo Buffers 7139328 bytes
RMAN> restore controlfile from 'F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL';
Starting restore at 27-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/27/2008 21:02:46
ORA-19504: failed to create file "+TEST/test/control01.ctl"
ORA-17502: ksfdcre:3 Failed to create file +TEST/test/control01.ctl
ORA-15001: diskgroup "TEST" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-19600: input file is control file (F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL)
ORA-19601: output file is control file (+TEST/test/control01.ctl)
出现了错误：提示磁盘组TEST不存在，连入ASM实例看看为什么
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:/WINDOWS>set ORACLE_SID=+asm
C:/WINDOWS>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 27 21:04:33 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
原来是上次做试验，把磁盘组删除后没有重建-.-!
+ASM>select * from v$asm_diskgroup;
no rows selected
重新建立磁盘组
+ASM>create diskgroup test normal redundancy
2 failgroup controller1 disk 'H:/asmDISKS/_FILE_DISK1','H:/asmDISKS/_FILE_DISK2'
3 failgroup controller2 disk 'H:/asmDISKS/_FILE_DISK3','H:/asmDISKS/_FILE_DISK4';
Diskgroup created.
回到RMAN中，再次执行成功
RMAN> restore controlfile from 'F:/ORACLE/PRODUCT/ORADATA/TEST/CONTROL01.CTL';
Starting restore at 27-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+TEST/test/control01.ctl
Finished restore at 27-MAR-08
这时候control file已经迁移成功，启动至mount
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
3、备份全部数据文件到ASM
RMAN> backup as copy database format '+test';
Starting backup at 27-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=F:/ORACLE/PRODUCT/ORADATA/TEST/SYSTEM01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/27/2008 21:07:21
ORA-19504: failed to create file "+TEST"
ORA-17502: ksfdcre:4 Failed to create file +TEST
ORA-15041: diskgroup space exhausted
出现错误：提示磁盘组的空间不足，磁盘组用的是4个200m的模拟磁盘，又使用了normal redundancy冗余模式，造成了空间不足，（尝试将冗余模式改为extenal redundancy也不够），再添加4块200m的模拟磁盘
+ASM>alter diskgroup test add disk 'H:/asmDISKS/_FILE_DISK5','H:/asmDISKS/_FILE_DISK6','H:/asmDISKS/_FILE_DISK7','H:/asmDISKS/_FILE_DISK8';
Diskgroup altered.
可以看到添加磁盘后，ASM实例自动进行了rebalance
+ASM>select * from v$asm_operation;
GROUP_NUMBER OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ ---------- -------- ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL RUN 1 1 1 226 0 0
再次备份整个数据库到ASM，成功完成
RMAN> backup as copy database format '+test';
Starting backup at 27-MAR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=F:/ORACLE/PRODUCT/ORADATA/TEST/SYSTEM01.DBF
output filename=+TEST/test/datafile/system.258.650496175 tag=TAG20080327T212252 recid=3 stamp=650496208
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=F:/ORACLE/PRODUCT/ORADATA/TEST/SYSAUX01.DBF
output filename=+TEST/test/datafile/sysaux.257.650496219 tag=TAG20080327T212252 recid=4 stamp=650496238
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=F:/ORACLE/PRODUCT/ORADATA/TEST/UNDOTBS01.DBF
output filename=+TEST/test/datafile/undotbs1.259.650496245 tag=TAG20080327T212252 recid=5 stamp=650496246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=F:/ORACLE/PRODUCT/ORADATA/TEST/USERS01.DBF
output filename=+TEST/test/datafile/users.260.650496247 tag=TAG20080327T212252 recid=6 stamp=650496248
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+TEST/test/controlfile/backup.261.650496251 tag=TAG20080327T212252 recid=7 stamp=650496252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=F:/ORACLE/PRODUCT/ORADATA/TEST/TEST_BIG.DBF
output filename=+TEST/test/datafile/test_big.262.650496255 tag=TAG20080327T212252 recid=8 stamp=650496254
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
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 27-MAR-08
channel ORA_DISK_1: finished piece 1 at 27-MAR-08
piece handle=+TEST/test/backupset/2008_03_27/nnsnf0_tag20080327t212252_0.263.650496257 tag=TAG20080327T212252 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-MAR-08
4、使用10g的新特性，切换数据库到刚才备份到ASM的备份上，至此datafile和controlfile的迁移已经完成了
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+TEST/test/datafile/system.258.650496175"
datafile 2 switched to datafile copy "+TEST/test/datafile/undotbs1.259.650496245"
datafile 3 switched to datafile copy "+TEST/test/datafile/sysaux.257.650496219"
datafile 4 switched to datafile copy "+TEST/test/datafile/users.260.650496247"
datafile 5 switched to datafile copy "+TEST/test/datafile/test_big.262.650496255"
RMAN>
5、Redo log还在文件系统上，也需要进行迁移
TEST>alter database open;
Database altered.
在ASM中建立3组新的redo log
TEST>alter database add logfile group 4 '+test/redo04.log' size 10m;
Database altered.
TEST>alter database add logfile group 5 '+test/redo05.log' size 10m;
Database altered.
TEST>alter database add logfile group 6 '+test/redo06.log' size 10m;
Database altered.
查看6组redolog的状态，可以看到当前使用的是第3组
TEST>select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED
因为要删除1-3组redolog，先进行redo log的切换
TEST>alter system switch logfile;
System altered.
TEST>/
System altered.
TEST>/
System altered.
可以看到当前使用的是第6组
TEST>select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 INACTIVE
3 INACTIVE
4 ACTIVE
5 ACTIVE
6 CURRENT
但是第1组redo log的状态仍然为ACTIVE，手工执行一次checkpoing
TEST>alter system checkpoint;
1-5组redo log的状态都为INACTIVE了
TEST>select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 CURRENT
删除1-3组redo log
TEST>alter database drop logfile group 1;
Database altered.
TEST>alter database drop logfile group 2;
Database altered.
TEST>alter database drop logfile group 3;
Database altered.
6、目前临时表空间也还在文件系统上，也需要进行迁移
查看目前数据库中tempfile的位置
TEST>select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- -----------------------
F:/ORACLE/PRODUCT/ORADATA/TEST/TEMP01.DBF TEMP
在temp表空间中新添加一个在ASM中的tempfile
TEST>alter tablespace temp add tempfile '+test/temp01.dbf' size 30m;
Tablespace altered.
删除原来在文件系统中的tempfile
TEST>alter tablespace temp drop tempfile 'F:/ORACLE/PRODUCT/ORADATA/TEST/TEMP01.DBF';
Tablespace altered.
7、目前系统只有一个controlfile，为了保证系统的安全性，再添加一个controlfile
TEST>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
idle>startup mount
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 71304332 bytes
Database Buffers 130023424 bytes
Redo Buffers 7139328 bytes
Database mounted.
将controlfile备份到ASM中（和目前的controlfile是一样的，可以直接使用）
TEST>alter database backup controlfile to '+test';
Database altered.
修改参数文件中的control_files参数
TEST>show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +TEST/test/control01.ctl
TEST>alter system set control_files='+TEST/test/control01.ctl','+TEST/test/control02.ctl' scope=spfile;
System altered.
使用asmcmd给备份至ASM的controlfile起个别名，以方便使用
C:/WINDOWS>set ORACLE_HOME=F:/oracle/product/10.2.0
C:/WINDOWS>set ORACLE_SID=+asm
C:/WINDOWS>asmcmd
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
control01.ctl
ASMCMD> cd controlfile
ASMCMD> ls -l
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE MAR 27 22:00:00 Y Backup.268.650498443
CONTROLFILE UNPROT FINE MAR 27 22:00:00 Y backup.256.650495709
ASMCMD> mkalias +TEST/TEST/CONTROLFILE/Backup.268.650498443 control02.ctl
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y BACKUPSET/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
N control01.ctl => +TEST/TEST/CONTROLFILE/backup.256.650495709
N control02.ctl => +TEST/TEST/CONTROLFILE/Backup.268.650498443
ASMCMD>
8、重启数据，整个数据库已成功迁移至ASM
TEST>startup force
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 71304332 bytes
Database Buffers 130023424 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened. |
|
IP卡
狗仔卡
发表于 2008-12-27 22:14
收藏
提升卡
置顶卡
变色卡
显身卡
发表于 2008-12-28 19:53
