Oracle11g单实例ASM迁移到文件系统
1. 环境：
CentOS5.8Oracle11.2.0.1单实例名sdzy建在ASM上
ASM:/dev/sdb1->+DATA ASM 20GB
2. 相关信息：
[oracle/grid]$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE oracle
ora....ER.lsnr ora....er.type ONLINE ONLINE oracle
ora.asm ora.asm.type ONLINE ONLINE oracle
ora.cssd ora.cssd.type ONLINE ONLINE oracle
ora.diskmon ora....on.type ONLINE ONLINE oracle
ora.sdzy.db ora....se.type ONLINE ONLINE oracle
[oracle/grid]$asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 20473 17842 0 17842 0 N DATA/
SQL> select file_name,bytes/1024/1024 MB,tablespace_name from dba_data_files;
FILE_NAME MB TABLESPACE_NAME
--------------------------------------------- ---------- ------------------------------
+DATA/sdzy/datafile/system.264.851788437 700 SYSTEM
+DATA/sdzy/datafile/sysaux.265.851788459 600 SYSAUX
+DATA/sdzy/datafile/undotbs1.266.851788475 850 UNDOTBS1
+DATA/sdzy/datafile/users.268.851788491 5 USERS
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
1 ONLINE +DATA/sdzy/onlinelog/group_1.258.851788425 NO
1 ONLINE +DATA/sdzy/onlinelog/group_1.259.851788427 YES
2 ONLINE +DATA/sdzy/onlinelog/group_2.260.851788429 NO
2 ONLINE +DATA/sdzy/onlinelog/group_2.261.851788431 YES
3 ONLINE +DATA/sdzy/onlinelog/group_3.262.851788433 NO
3 ONLINE +DATA/sdzy/onlinelog/group_3.263.851788435 YES
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/sdzy/spfilesdzy.ora
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/sdzy/controlfile/current
.256.851788423, +DATA/sdzy/con
trolfile/current.257.851788425
SQL> select file#,status,bytes/1024/1024 MB,TS#,name from v$tempfile;
FILE# STATUS MB TS# NAME
---------- ------- ---------- ---------- ---------------------------------------------
1 ONLINE 68 3 +DATA/sdzy/tempfile/temp.267.851788481
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 68
Next log sequence to archive 70
Current log sequence 70
SQL> show parameter DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 4977M
connected to target database: SDZY (DBID=1456076355)
RMAN>
3. 迁移思路：
备份参数和控制文件
关闭数据库，启动到nomount状态，恢复控制文件
再使用backup as copy命令复制ASM数据文件到文件系统
4. 修改pfile控制文件参数为文件系统位置
SQL> alter system set control_files='/db/oracle/admin/sdzy/oradata/control01.ctl','/db/oracle/admin/sdzy/oradata/control02.ctl' scope=spfile;
5. 备份PFILE和控制文件
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
RMAN> backup current controlfile format '/home/oracle/controlfile.ctl';
Starting backup at 2014-07-14 23:00:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2014-07-14 23:00:29
channel ORA_DISK_1: finished piece 1 at 2014-07-14 23:00:30
piece handle=/home/oracle/controlfile.ctl tag=TAG20140714T230028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014-07-14 23:00:30
6. 关闭数据库
RMAN> shutdown immediate;
7. 利用备份修改后的pfile文件启动到nomount状态
RMAN> startup nomount pfile='/home/oracle/pfile.ora';
connected to target database (not started)
Oracle instance started
Total System Global Area 1503199232 bytes
Fixed Size 2213536 bytes
Variable Size 1006635360 bytes
Database Buffers 486539264 bytes
Redo Buffers 7811072 bytes
8. 利用备份的控制文件恢复控制文件到pfile指定文件
RMAN> restore controlfile from '/home/oracle/controlfile.ctl';
Starting restore at 2014-07-14 23:15:00
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1146 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/db/oracle/admin/sdzy/oradata/control01.ctl
output file name=/db/oracle/admin/sdzy/oradata/control02.ctl
Finished restore at 2014-07-14 23:15:02
9. 恢复数据库到mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
10. 复制ASM磁盘组数据文件到文件系统
backup as copy datafile '+DATA/sdzy/datafile/system.264.851788437' format '/db/oracle/admin/sdzy/oradata/system01.dbf';
backup as copy datafile '+DATA/sdzy/datafile/sysaux.265.851788459' format '/db/oracle/admin/sdzy/oradata/sysaux01.dbf';
backup as copy datafile '+DATA/sdzy/datafile/undotbs1.266.851788475' format '/db/oracle/admin/sdzy/oradata/undotbs1.dbf';
backup as copy datafile '+DATA/sdzy/datafile/users.268.851788491' format '/db/oracle/admin/sdzy/oradata/users01.dbf';
备注：backup as copy 命令是以副本的方式备份数据库，其作用和操作系统的COPY
命令是一样的，只是二者的执行者不同而已。这里必须同时制定ASM文件的名字和
副本的名字。而且backup as copy 命令只备份数据文件，不备份日志文件，也不备份临时文件
11. 修改控制文件内部数据文件指针
rman>switch database to copy;
备注：因为控制文件内部对数据文件的指向依然是ASM文件的目录路径。我们的目的
是让它的指向修改为文件系统的数据库文件。该命令的功能正是如此。
【注意】此处不能使用alter database rename file 命令修改控制文件内部数据文件
的指针。因为alter database rename file命令不能跨越系统进行操作。所
不能使用其将ASM系统的ASM文件路径改为文件系统的数据文件路径。
12. 修复数据库文件
rman>recover database;
备注：应用日志到恢复后的数据文件上，以实现对其的修复
13. 以RESETLOGS方式打开数据库
rman>alter database open resetlogs;
14. 修改联机日志文件组
sql>alter database add logfile group 4 '/db/oracle/admin/sdzy/oradata/redo04_01.log' size 50m;
sql>alter database add logfile group 5 '/db/oracle/admin/sdzy/oradata/redo05_01.log' size 50m;
sql>alter database add logfile group 6 '/db/oracle/admin/sdzy/oradata/redo06_01.log' size 50m;
sql>alter system switch logfile;
sql>alter system checkpoint;
sql>alter database drop logfile group 1;
sql>alter database drop logfile group 2;
sql>alter database drop logfile group 3;
【备注】因为backup as copy database 命令只复制数据文件不复制联机日志文件，所
以需要手工添加联机日志文件，删除位于ASM磁盘组的日志文件。在删除过程中，
日志文件可能处于当前使用状态或者是活动状态，故分别使用如下命令进行解决：
alter system switch logfile；（日志切换）
alter system checkpoint;（将脏数据写入数据文件，以使日志文件状态变为非活动）
15. 添加临时表空间文件
SQL> alter tablespace temp add tempfile '/db/oracle/admin/sdzy/oradata/temp01.dbf' size 100M;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '+DATA/sdzy/tempfile/temp.267.851788481';
Tablespace altered.
【备注】因为backup as copy database 命令只复制数据文件不复制临时文件，所
以需要手工添加临时文件。
16. 修改归档路径
事先创建归档目录：
/db/oracle/admin/sdzy/oradata/arch
关闭数据库
SQL> shutdown immediate;
修改备份的pfile文件：
*.db_create_file_dest='/db/oracle/admin/sdzy/oradata'
*.db_recovery_file_dest='/db/oracle/admin/sdzy/oradata/arch'
利用备份的pfile文件启动数据库：
SQL> startup pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 1503199232 bytes
Fixed Size 2213536 bytes
Variable Size 1006635360 bytes
Database Buffers 486539264 bytes
Redo Buffers 7811072 bytes
Database mounted.
Database opened.
创建SPFILE文件
SQL> create spfile from pfile='/home/oracle/pfile.ora';
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1503199232 bytes
Fixed Size 2213536 bytes
Variable Size 1006635360 bytes
Database Buffers 486539264 bytes
Redo Buffers 7811072 bytes
Database mounted.
Database opened.
17. 检查迁移后的文件路径
确认数据文件、归档、参数文件、控制文件、临时文件、日志文件等都迁移到文件系统中，且数据未丢失
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26217983/viewspace-1461698/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26217983/viewspace-1461698/
1908
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
