Oracle数据库有文件系统迁移到ASM

本实验所涉及的是将数据库有文件系统迁移到ASM上，首先要确保本机已经配置好ASM。主要过程如下：

①对当前库做全备

     backup database plus archivelog;

②修改pfile，把控制文件指向ASM中

     create pfile from spfile;

     或者alter system set control_files='+FLASH_AREA' scope=spfile;

③startup nomount

   

④恢复控制文件

⑤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=184549376

zhlh.__java_pool_size=4194304

zhlh.__large_pool_size=4194304

zhlh.__shared_pool_size=75497472

zhlh.__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; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE 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-13

current log archived

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting compressed archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=10 recid=15 stamp=814532585

input archive log thread=1 sequence=11 recid=16 stamp=814532585

input archive log thread=1 sequence=12 recid=17 stamp=814532586

channel ORA_DISK_1: starting piece 1 at 04-MAY-13

channel ORA_DISK_2: starting compressed archive log backupset

channel ORA_DISK_2: specifying archive log(s) in backup set

input archive log thread=1 sequence=1 recid=18 stamp=814533147

input archive log thread=1 sequence=2 recid=19 stamp=814533166

input archive log thread=1 sequence=3 recid=20 stamp=814533167

input archive log thread=1 sequence=4 recid=21 stamp=814533168

input archive log thread=1 sequence=5 recid=22 stamp=814533618

channel ORA_DISK_2: starting piece 1 at 04-MAY-13

channel ORA_DISK_2: finished piece 1 at 04-MAY-13

piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112224_0.260.814533747 tag=TAG20130504T112224 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:04

channel ORA_DISK_2: starting compressed archive log backupset

channel ORA_DISK_2: specifying archive log(s) in backup set

input archive log thread=1 sequence=6 recid=23 stamp=814533652

input archive log thread=1 sequence=7 recid=24 stamp=814533744

channel ORA_DISK_2: starting piece 1 at 04-MAY-13

channel ORA_DISK_1: finished piece 1 at 04-MAY-13

piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112224_0.259.814533747 tag=TAG20130504T112224 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06

channel ORA_DISK_2: finished piece 1 at 04-MAY-13

piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112224_0.261.814533751 tag=TAG20130504T112224 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02

Finished backup at 04-MAY-13

Starting backup at 04-MAY-13

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=+DATA/zhlh/datafile/system.257.814532051

input datafile fno=00003 name=+DATA/zhlh/datafile/sysaux.259.814532053

channel ORA_DISK_1: starting piece 1 at 04-MAY-13

channel ORA_DISK_2: starting compressed full datafile backupset

channel ORA_DISK_2: specifying datafile(s) in backupset

input datafile fno=00002 name=+DATA/zhlh/datafile/undotbs1.258.814532053

input datafile fno=00005 name=+DATA/zhlh/datafile/adv.260.814532055

input datafile fno=00004 name=+DATA/zhlh/datafile/users.261.814532057

channel ORA_DISK_2: starting piece 1 at 04-MAY-13

channel ORA_DISK_1: finished piece 1 at 04-MAY-13

piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/nnndf0_tag20130504t112232_0.262.814533753 tag=TAG20130504T112232 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56

channel ORA_DISK_2: finished piece 1 at 04-MAY-13

piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/nnndf0_tag20130504t112232_0.263.814533753 tag=TAG20130504T112232 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:56

Finished backup at 04-MAY-13

Starting backup at 04-MAY-13

current log archived

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting compressed archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=8 recid=25 stamp=814533808

channel ORA_DISK_1: starting piece 1 at 04-MAY-13

channel ORA_DISK_1: finished piece 1 at 04-MAY-13

piece handle=+FLA_AREA/zhlh/backupset/2013_05_04/annnf0_tag20130504t112329_0.265.814533811 tag=TAG20130504T112329 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 04-MAY-13

Starting Control File Autobackup at 04-MAY-13

piece handle=+FLA_AREA/zhlh/autobackup/2013_05_04/n_814533811.266.814533813 comment=NONE

Finished 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