联系：手机/微信(+86 17813235971) QQ(107644445)
标题：普通库迁移至ASM存储
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
--查看当前库文件位置
SQL> select name from v$datafile
2 union
3 select member from v$logfile
4 union
5 select name from v$controlfile
6 union
7 select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/control03.ctl
/u01/oradata/tos/example01.dbf
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/sysaux01.dbf
/u01/oradata/tos/system01.dbf
/u01/oradata/tos/temp01.dbf
/u01/oradata/tos/undotbs01.dbf
/u01/oradata/tos/users01.dbf
/u01/oradata/tos/xff01.dbf
/u01/oradata/tos/xff02.dbf
14 rows selected.
--备份控制文件
SQL> alter database backup controlfile to '/u01/control.ctl';
Database altered.
--修改spfile中的控制文件至asm
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
--修改db_recovery_file_dest至asm中
SQL> alter system set db_recovery_file_dest='+FLASHBACK';
System altered.
--修改归档日志至asm中
SQL> alter system set log_archive_dest_1='location=+DATA';
System altered.
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--登录rman
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 08:02:37 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
--启动数据库至nomount状态
RMAN> startup nomount;
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1260672 bytes
Variable Size 67109760 bytes
Database Buffers 92274688 bytes
Redo Buffers 7127040 bytes
--恢复控制文件
RMAN> restore controlfile from '/u01/control.ctl';
Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/tos/controlfile/current.268.754905785
Finished restore at 27-JUN-11
--打开数据库至mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
--copy数据文件至asm
RMAN> backup as copy database format '+DATA';
Starting backup at 27-JUN-11
Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11
Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-11
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/oradata/tos/system01.dbf
output filename=+DATA/tos/datafile/system.270.754905833 tag=TAG20110627T080352 recid=2 stamp=754905928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/oradata/tos/sysaux01.dbf
output filename=+DATA/tos/datafile/sysaux.271.754905929 tag=TAG20110627T080352 recid=3 stamp=754905990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/oradata/tos/example01.dbf
output filename=+DATA/tos/datafile/example.272.754905995 tag=TAG20110627T080352 recid=4 stamp=754906010
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/oradata/tos/undotbs01.dbf
output filename=+DATA/tos/datafile/undotbs1.273.754906021 tag=TAG20110627T080352 recid=5 stamp=754906025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/tos/xff01.dbf
output filename=+DATA/tos/datafile/xff.274.754906027 tag=TAG20110627T080352 recid=6 stamp=754906029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u01/oradata/tos/xff02.dbf
output filename=+DATA/tos/datafile/xff.275.754906031 tag=TAG20110627T080352 recid=7 stamp=754906032
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/oradata/tos/users01.dbf
output filename=+DATA/tos/datafile/users.276.754906035 tag=TAG20110627T080352 recid=8 stamp=754906035
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
--恢复数据库
RMAN> recover database;
Starting recover at 27-JUN-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /u01/oradata/tos/redo02.log
archive log filename=/u01/oradata/tos/redo02.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11
--修改数据库中数据文件路径
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/tos/datafile/system.270.754905833"
datafile 2 switched to datafile copy "+DATA/tos/datafile/undotbs1.273.754906021"
datafile 3 switched to datafile copy "+DATA/tos/datafile/sysaux.271.754905929"
datafile 4 switched to datafile copy "+DATA/tos/datafile/users.276.754906035"
datafile 5 switched to datafile copy "+DATA/tos/datafile/example.272.754905995"
datafile 6 switched to datafile copy "+DATA/tos/datafile/xff.274.754906027"
datafile 7 switched to datafile copy "+DATA/tos/datafile/xff.275.754906031"
--打开数据库
RMAN> alter database open resetlogs;
database opened
--添加日志文件
SQL> alter database add logfile group 4 '+DATA' size 10m;
Database altered.
SQL> alter database add logfile group 5 '+DATA' size 10m;
Database altered.
SQL> alter database add logfile group 6 '+DATA' size 10m;
Database altered.
--添加临时文件
SQL> alter tablespace temp add tempfile '+DATA' size 30m;
Tablespace altered.
--删除原临时文件
SQL> alter tablespace temp drop tempfile '/u01/oradata/tos/temp01.dbf';
Tablespace altered.
--切换日志
SQL> alter system switch logfile; --多次
System altered.
--数据文件修改写入磁盘
SQL> alter system checkpoint;
System altered.
--检查联机日志状态
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 CURRENT
6 rows selected.
--删除原联机日志
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
--查看修改后状态
SQL> select name from v$datafile
2 union
3 select member from v$logfile
4 union
5 select name from v$controlfile
6 union
7 select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/tos/controlfile/current.268.754905785
+DATA/tos/datafile/example.272.754905995
+DATA/tos/datafile/sysaux.271.754905929
+DATA/tos/datafile/system.270.754905833
+DATA/tos/datafile/undotbs1.273.754906021
+DATA/tos/datafile/users.276.754906035
+DATA/tos/datafile/xff.274.754906027
+DATA/tos/datafile/xff.275.754906031
+DATA/tos/onlinelog/group_4.277.754906309
+DATA/tos/onlinelog/group_5.278.754906319
+DATA/tos/onlinelog/group_6.279.754906321
+DATA/tos/tempfile/temp.280.754906369