⑤alter database mount
⑥restore database，注意要set newname把数据文件执行ASM里 run { set newname fro datafile 1 to '+FLASH_AREA';
set newname fro datafile 1 to '+FLASH_AREA'; set newname fro datafile 1 to '+FLASH_AREA'; set newname fro datafile 1 to '+FLASH_AREA';restore database;
switch datafile all;
recover database; } 7、recover
8、控制文件是还原回来的，所以需要resetlogs，另一种需要resetlogs是，redo丢失的情况 alter database open resetlogs;
9、新建日志组到ASM里，把文件系统上的redo删掉 select * from v$log; alter database add logfile group 4 '+FLASH_AREA' size 50m; alter database add logfile group 5 '+FLASH_AREA' size 50m; alter database add logfile group 6 '+FLASH_AREA' size 50m;
select group#,member from v$logfile; select
alter database drop logfile group 3; alter system switch logfile;
alter system checkpoint; --生产环境不建议这样做 10、创建tempfile alter tablespace temp add tempfile '+FLASH_AREA' size 100m; alter tablespace temp drop tempfile '/u01/app/oracle/oradata/temp01.dbf';
归档默认是放在闪回区里的 ------------------------------------------------------------ 最好放到ASM上：controlfile ,datafile ,redo logfile,archive logs 可以放到文件系统上：spfile,tempfile, alter system set db_recovery_file_dest='+DATA'; create spfile='+FLASH_AREA' from pfile;
1、修改参数文件，从参数文件启动到nomount状态 pfile内容：
zhlh.__db_cache_size=184549376zhlh.__java_pool_size=4194304zhlh.__large_pool_size=4194304zhlh.__shared_pool_size=75497472zhlh.__streams_pool_size=8388608*.audit_file_dest='/u01/app/oracle/admin/zhlh/adump'*.background_dump_dest='/u01/app/oracle/admin/zhlh/bdump'*.compatible='10.2.0.1.0'*.CONTROL_FILES='+DATA/zhlh/controlfiles/control01.ctl'*.core_dump_dest='/u01/app/oracle/admin/zhlh/cdump'*.db_block_size=8192*.db_domain=''*.db_file_multiblock_read_count=16*.db_name='zhlh'*.db_recovery_file_dest_size=2147483648*.db_recovery_file_dest='/u02/flashback'*.DB_UNIQUE_NAME='zhlh'*.fast_start_mttr_target=600*.job_queue_processes=10*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'*.LOG_ARCHIVE_MAX_PROCESSES=2*.open_cursors=300*.pga_aggregate_target=94371840*.processes=150*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'*.sga_target=285212672*.undo_management='AUTO'*.undo_retention=3600*.undo_tablespace='UNDOTBS1'*.user_dump_dest='/u01/app/oracle/admin/zhlh/udump'
startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initzhlh.ora' nomount 2、从备份中恢复控制文件到ASM磁盘组 restore controlfile from '/u02/flashback/ZHLH/autobackup/2013_05_03/o1_mf_s_814491485_8r7pgy5q_.bkp'; 3、把数据库拉倒mount状态，准备恢复数据文件 alter database mount; 4、在RMAN中恢复控制文件 run {set newname for datafile 1 to '+DATA';set newname for datafile 2 to '+DATA';set newname for datafile 3 to '+DATA';set newname for datafile 4 to '+DATA';set newname for datafile 5 to '+DATA';restore database;switch datafile all;recover database; } 5、使用resetlogs选项打开数据库[因为controlfile是从备份中恢复的，要注意] alter database open resetlogs; 6、扫尾工作 通过上面几步操作，controlfile，datafile都已经存在于ＡＳＭ磁盘组上了 Online Redo Log也要迁移到ASM磁盘上，archivelog日志也要放到ASM磁盘组中 flashback区放到ASM磁盘组 spfile/tempfile也可以考虑放到ASM磁盘组上 REDO LOG FILE转移到ＡＳＭ磁盘组上： 查看当前在线日志的状态，确定如何要在ASM磁盘组中创建那些日志组，要删除那些日志组 col member for a40 select GROUP#,STATUS,MEMBER from v$logfile; select GROUP#,MEMBERS,STATUS from v$log; 向ASM中添加3个日志组： alter database add logfile group 4 '+DATA' size 50m; alter database add logfile group 5 '+DATA' size 50m; alter database add logfile group 6 '+DATA' size 50m; 准备删除在文件系统上的3个日志组： alter system switch logfile; [可以多切数次，并确保要删除的那些online redo 日志组是处于inactive状态的，之后就可以执行删除操作了] alter system checkpoint; alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; 处理临时文件的问题，为临时表空间添加新的临时文件并删除旧的且不存在的临时文件： select name from v$tempfile; alter tablespace temp add tempfile '+DATA' size 100m; alter tablespace temp drop tempfile '/u02/zhlh/temp01.dbf'; 修改闪回区的路径，将闪回区放到ＡＳＭ磁盘组中： alter system set db_recovery_file_dest='+FLA_AREA'; 修改归档日志的路径，将其执行ASM磁盘组中： alter system set log_archive_dest_1='LOCATION=+FLA_AREA'; 将spfile 放到ASM磁盘组中：
迁移完成后，对数据库做个全库备份：
RMAN> show all; RMAN configuration parameters are:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;CONFIGURE BACKUP OPTIMIZATION ON;CONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_zhlh.f'; # default
RMAN> backup database plus archivelog; Starting backup at 04-MAY-13current log archivedusing channel ORA_DISK_1using channel ORA_DISK_2channel ORA_DISK_1: starting compressed archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=10 recid=15 stamp=814532585input archive log thread=1 sequence=11 recid=16 stamp=814532585input archive log thread=1 sequence=12 recid=17 stamp=814532586channel ORA_DISK_1: starting piece 1 at 04-MAY-13channel ORA_DISK_2: starting compressed archive log backupsetchannel ORA_DISK_2: specifying archive log(s) in backup setinput archive log thread=1 sequence=1 recid=18 stamp=814533147input archive log thread=1 sequence=2 recid=19 stamp=814533166input archive log thread=1 sequence=3 recid=20 stamp=814533167input archive log thread=1 sequence=4 recid=21 stamp=814533168input archive log thread=1 sequence=5 recid=22 stamp=814533618channel ORA_DISK_2: starting piece 1 at 04-MAY-13channel ORA_DISK_2: finished piece 1 at 04-MAY-13piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112224_0.260.814533747 tag=TAG20130504T112224 comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:04channel ORA_DISK_2: starting compressed archive log backupsetchannel ORA_DISK_2: specifying archive log(s) in backup setinput archive log thread=1 sequence=6 recid=23 stamp=814533652input archive log thread=1 sequence=7 recid=24 stamp=814533744channel ORA_DISK_2: starting piece 1 at 04-MAY-13channel ORA_DISK_1: finished piece 1 at 04-MAY-13piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112224_0.259.814533747 tag=TAG20130504T112224 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:06channel ORA_DISK_2: finished piece 1 at 04-MAY-13piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112224_0.261.814533751 tag=TAG20130504T112224 comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:02Finished backup at 04-MAY-13
Starting backup at 04-MAY-13using channel ORA_DISK_1using channel ORA_DISK_2channel ORA_DISK_1: starting compressed full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=+DATA/zhlh/datafile/system.257.814532051input datafile fno=00003 name=+DATA/zhlh/datafile/sysaux.259.814532053channel ORA_DISK_1: starting piece 1 at 04-MAY-13channel ORA_DISK_2: starting compressed full datafile backupsetchannel ORA_DISK_2: specifying datafile(s) in backupsetinput datafile fno=00002 name=+DATA/zhlh/datafile/undotbs1.258.814532053input datafile fno=00005 name=+DATA/zhlh/datafile/adv.260.814532055input datafile fno=00004 name=+DATA/zhlh/datafile/users.261.814532057channel ORA_DISK_2: starting piece 1 at 04-MAY-13channel ORA_DISK_1: finished piece 1 at 04-MAY-13piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/nnndf0_tag20130504t112232_0.262.814533753 tag=TAG20130504T112232 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:56channel ORA_DISK_2: finished piece 1 at 04-MAY-13piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/nnndf0_tag20130504t112232_0.263.814533753 tag=TAG20130504T112232 comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:56Finished backup at 04-MAY-13
Starting backup at 04-MAY-13current log archivedusing channel ORA_DISK_1using channel ORA_DISK_2channel ORA_DISK_1: starting compressed archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=8 recid=25 stamp=814533808channel ORA_DISK_1: starting piece 1 at 04-MAY-13channel ORA_DISK_1: finished piece 1 at 04-MAY-13piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112329_0.265.814533811 tag=TAG20130504T112329 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 04-MAY-13
Starting Control File Autobackup at 04-MAY-13piece handle=+FLA_AREA/zhlh/autobackup/2013_05_04/n_814533811.266.814533813 comment=NONEFinished Control File Autobackup at 04-MAY-13
最终结果： select name from v$datafile; select name from v$tempfile; select name from v$controlfile; select member from v$logfile; show parameter spfile
