將資料庫遷移到ASM的實驗記錄

昨天的blog中測試了在非RAC環境下建立ASM的過程，現在要使用已經建立的ASM，將資料庫遷移到ASM上。

主要步驟：

1、設定control_files環境變數
2、遷移控制檔案（controlfile）
3、遷移資料檔案（datafile）
4、遷移臨時檔案（tempfile）
5、遷移線上日誌（redolog）

纖細操作過程如下：

[@more@]

1、設定control_files環境變數

設定這個環境變數的目的是為了下一步使用restore controlfile的時候，能直接在asm上建立需要的控制檔案。

[oracle@oracle ~]$ echo $ORACLE_SID orcl [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 18 09:12:48 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218292 bytes Variable Size 83888396 bytes Database Buffers 75497472 bytes Redo Buffers 7168000 bytes Database mounted. Database opened. SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /oracle/oradata/orcl/control01 .ctl, /oracle/oradata/orcl/con trol02.ctl, /oracle/oradata/or cl/control03.ctl SQL> alter system set control_files='+DATA/ORCL/control01.ctl','+DATA/ORCL/control02.ctl','+DATA/ORCL/control03.ctl' scope=spfile; System altered.

2、遷移控制檔案（controlfile）

這一步就是使用rman的restore controlfile命令，將本地磁碟上的控制檔案恢復到資料庫的control_files引數指定的檔案位置。

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218292 bytes Variable Size 71305484 bytes Database Buffers 88080384 bytes Redo Buffers 7168000 bytes SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@oracle ~]$ rman target /; Recovery Manager: Release 10.2.0.1.0 - Production on Fri Dec 18 09:35:25 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: orcl (not mounted) RMAN> restore controlfile from '/oracle/oradata/orcl/control01.ctl'; Starting restore at 18-DEC-09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK channel ORA_DISK_1: copied control file copy output filename=+DATA/orcl/control01.ctl output filename=+DATA/orcl/control02.ctl output filename=+DATA/orcl/control03.ctl Finished restore at 18-DEC-09

3、遷移資料檔案（datafile）

遷移資料檔案的操作主要就是使用rman的backup as copy命令，將整個資料庫備份到ASM上，然後執行switch命令，修改controlfile以使用備份的資料檔案

RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> backup as copy database format '+DATA'; Starting backup at 18-DEC-09 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf output filename=+DATA/orcl/datafile/system.259.705922671 tag=TAG20091218T093751 recid=11 stamp=705922714 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46 channel ORA_DISK_1: starting datafile copy input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf output filename=+DATA/orcl/datafile/sysaux.260.705922719 tag=TAG20091218T093751 recid=12 stamp=705922741 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26 channel ORA_DISK_1: starting datafile copy input datafile fno=00005 name=/oracle/oradata/orcl/example01.dbf output filename=+DATA/orcl/datafile/example.261.705922745 tag=TAG20091218T093751 recid=13 stamp=705922752 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf output filename=+DATA/orcl/datafile/undotbs1.262.705922759 tag=TAG20091218T093751 recid=14 stamp=705922762 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile fno=00006 name=/oracle/oradata/orcl/tbs_lmt01.dbf output filename=+DATA/orcl/datafile/tbs_lmt.263.705922767 tag=TAG20091218T093751 recid=15 stamp=705922767 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile fno=00007 name=/oracle/oradata/orcl/tbs_lmt_2_01.dbf output filename=+DATA/orcl/datafile/tbs_lmt_2.264.705922769 tag=TAG20091218T093751 recid=16 stamp=705922770 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile fno=00008 name=/oracle/oradata/orcl/tbs_lmt02.dbf output filename=+DATA/orcl/datafile/tbs_lmt_3.265.705922773 tag=TAG20091218T093751 recid=17 stamp=705922774 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf output filename=+DATA/orcl/datafile/users.266.705922777 tag=TAG20091218T093751 recid=18 stamp=705922776 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy copying current control file output filename=+DATA/orcl/controlfile/backup.267.705922777 tag=TAG20091218T093751 recid=19 stamp=705922778 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 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 18-DEC-09 channel ORA_DISK_1: finished piece 1 at 18-DEC-09 piece handle=+DATA/orcl/backupset/2009_12_18/nnsnf0_tag20091218t093751_0.268.705922783 tag=TAG20091218T093751 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 18-DEC-09 RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.259.705922671" datafile 2 switched to datafile copy "+DATA/orcl/datafile/undotbs1.262.705922759" datafile 3 switched to datafile copy "+DATA/orcl/datafile/sysaux.260.705922719" datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.266.705922777" datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.261.705922745" datafile 6 switched to datafile copy "+DATA/orcl/datafile/tbs_lmt.263.705922767" datafile 7 switched to datafile copy "+DATA/orcl/datafile/tbs_lmt_2.264.705922769" datafile 8 switched to datafile copy "+DATA/orcl/datafile/tbs_lmt_3.265.705922773" RMAN> alter database open; database opened RMAN> exit Recovery Manager complete.

4、遷移臨時檔案（tempfile）

臨時檔案不能透過rman備份恢復，只好先在asm上新建一個，然後再把原來的刪除。
這裡使用了oracle的omf特性（透過設定db_create_file_dest引數實現），建立臨時檔案的時候不用指定檔名。

[oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 18 09:40:50 2009 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 SQL> select file#,name from v$tempfile; FILE# ---------- NAME -------------------------------------------------------------------------------- 1 /oracle/oradata/orcl/temp01.dbf SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS TEMP EXAMPLE TBS_LMT TBS_LMT_2 TBS_LMT_3 9 rows selected. SQL> alter system set db_create_file_dest='+DATA' scope=both; System altered. SQL> alter tablespace temp add tempfile; Tablespace altered. SQL> alter tablespace temp drop tempfile 1; Tablespace altered.

5、遷移線上日誌（redolog）

線上日誌不能透過rman備份恢復，只好先在asm上新建幾組，然後再把原來的刪除。
這裡使用了oracle的omf特性（透過設定db_create_online_log_dest_1引數實現），建立線上日誌檔案的時候不用指定檔名。

SQL> select group#,member from v$logfile; GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 3 /oracle/oradata/orcl/redo03.log 2 /oracle/oradata/orcl/redo02.log 1 /oracle/oradata/orcl/redo01.log SQL> show parameter create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ create_bitmap_area_size integer 8388608 create_stored_outlines string db_create_file_dest string +DATA db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string SQL> alter system set db_create_online_log_dest_1='+DATA' scope=both; System altered. SQL> alter database add logfile group 4 size 100m; Database altered. SQL> c/4/5 1* alter database add logfile group 5 size 100m SQL> / Database altered. SQL> c/5/6 1* alter database add logfile group 6 size 100m SQL> / Database altered. SQL> select group#,member from v$logfile; GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 3 /oracle/oradata/orcl/redo03.log 2 /oracle/oradata/orcl/redo02.log 1 /oracle/oradata/orcl/redo01.log GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 4 +DATA/orcl/onlinelog/group_4.270.705923247 5 +DATA/orcl/onlinelog/group_5.271.705923261 6 +DATA/orcl/onlinelog/group_6.272.705923271 6 rows selected. SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 INACTIVE 3 INACTIVE 4 ACTIVE 5 CURRENT 6 UNUSED 6 rows selected. SQL> alter system switch logfile; System altered. SQL> alter database drop logfile group 1; Database altered. SQL> c/1/2 1* alter database drop logfile group 2 SQL> / Database altered. SQL> c/2/3 1* alter database drop logfile group 3 SQL> / Database altered.

-- end --