--==================================

-- 使用RMAN遷移檔案系統數據庫到ASM

--==================================

在實際的工作過程中，由於ASM磁盤管理的便利性，因此很多時候需要將檔案系統的數據庫遷移到ASM，本文演示瞭如何將檔案系統數據庫遷移到ASM實例。

有關如何創建ASM實例及ASM磁盤管理請參考

創建ASM實例及數據庫

ASM 磁盤組及磁盤的管理

使用 ASMCMD 工具管理ASM實例

一、主要步驟(假定ASM實例已創建)

1.計算目標數據庫(檔案系統)的大小

2.根據目標數據庫的大小，為新ASM數據庫(輔助數據庫)準備可用磁盤空間

3.為輔助數據庫配置初始化引數檔案，密碼檔案，創建目錄

4.備份目標數據庫

5.遷移目標數據庫到輔助數據庫

二、實施遷移

本次遷移在同一臺主機實現，因此採用不同的ORACLE_SID

環境：Oracle Linux 5.4 + Oracle 10g R2

目標數據庫：orcl

輔助數據庫：orclasm

1.計算目標數據庫(檔案系統)的大小

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string orcl

SQL> select * from v$version where rownum<2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> select sum(bytes)/1024/1024 ||'MB' from dba_segments;

SUM(BYTES)/1024/1024||'MB'

------------------------------------------

1195.5MB

2.為輔助數據庫準備可用空間，下面顯示DG1中有3016MB可用空間，可以滿足遷移的需要

ASMCMD> ls -s

Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name

512 4096 1048576 6134 6032 0 3016 0 DG1/

512 4096 1048576 2047 1997 0 1997 0 REV/

3.配置輔助數據庫

a.啟動目標數據庫，併為目標數據庫創建pfile

[oracle@oradb ~]$ echo $ORACLE_SID

orcl

[oracle@oradb ~]$ sqlplus / as sysdba

idle> startup

sys@ORCL> create pfile from spfile;

b.復制目標數據庫的pfile來生成輔助數據庫的pfile並對其進行修改

[oracle@oradb dbs]$ cd $ORACLE_HOME/dbs

[oracle@oradb dbs]$ cp initorcl.ora initorclasm.ora

修改initorclasm.ora

對檔案中所有的orcl使用替換命令替換為orclasm(使用vi工具 :%s/orcl/orclasm/g來替換)

修改控制檔案為1個(磁盤DG1使用了normal redundancy)，路徑為'+DG1/orclasm/controlfile/'--使用ASM應注意目錄結構的變化

修改db_recovery_file_dest路徑為'+REV'

修改log_archive_dest_1路徑為'LOCATION=+REV/orclasm'

修改db_create_file_dest路徑為'+DG1'

增加下列引數(恢復完畢後清除)

*.db_file_name_convert=("orcl","orclasm")

*.log_file_name_convert=("orcl","orclasm")

下面列出發生變化的幾個重要引數

*.audit_file_dest='/u01/app/oracle/admin/orclasm/adump'

*.background_dump_dest='/u01/app/oracle/admin/orclasm/bdump'

*.control_files='+DG1/orclasm/controlfile/control01.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orclasm/cdump'

*.db_name='orclasm'

*.db_recovery_file_dest='+REV/orclasm'

*.log_archive_dest_1='LOCATION=+REV/orclasm'

*.user_dump_dest='/u01/app/oracle/admin/orclasm/udump'

*.db_create_file_dest='+DG1'

*.db_file_name_convert=("orcl","orclasm")

*.log_file_name_convert=("orcl","orclasm")

c.根據剛剛修改過的引數創建目錄

[oracle@oradb ~]$ mkdir -p $ORACLE_BASE/admin/orclasm/{a,b,c,u}dump

[oracle@oradb ~]$ ls $ORACLE_BASE/admin/orclasm

adump bdump cdump udump

d.創建密碼檔案

[oracle@oradb dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworclasm password=oracle entries=8

4.備份目標數據庫

a.目標數據庫應處於歸檔模式下

SQL> select name,log_mode from v$database;

NAME LOG_MODE

--------- ------------

ORCL ARCHIVELOG

b.連接到RMAN並進行備份，此處使用了非catalog方式

[oracle@oradb ~]$ uniread rman target sys/oracle@orcl nocatalog

RMAN> crosscheck archivelog all; --校驗歸檔日誌

RMAN> delete expired archivelog all; --刪除無效的歸檔日誌

RMAN> report obsolete;

RMAN> delete noprompt obsolete; --刪除廢棄的備份

RMAN> show channel; --查看預設的備份路徑

RMAN configuration parameters are:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/bk/orcl/data_%d_%U';

RMAN> show controlfile autobackup; --查看控制檔案的自動備份啟用情況

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> show controlfile autobackup format; --查看控制檔案的備份路徑、格式

RMAN configuration parameters are:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u01/bk/orcl/auto_ctl_%d_%F';

RMAN> run{ --對目標數據庫進行備份，此備份為0級增量包含了歸檔日誌，控制檔案spfile將自動備份

2> allocate channel ch1 device type disk;

3> backup as compressed backupset

4> incremental level 0 database format '/u01/bk/orcl/data_%d_%U'

5> plus archivelog format '/u01/bk/orcl/bk_lg_%U'

6> tag='Inc0_log';

7> release channel ch1;}

5.遷移目標數據到ASM

a.查看目標數據庫的SEQUENCE，當前為10，便於恢復時指定SEQUENCE

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 9 52428800 1 YES ACTIVE 52044530-OCT-10

2 1 10 52428800 1 NO CURRENT 52058530-OCT-10

b.查看目標數據庫的數據檔案的相關信息，後續需要為datafile指定檔名

RMAN> report schema;

Report of database schema

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 480 SYSTEM *** /u01/app/oracle/oradata/orcl/system01.dbf

2 25 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf

3 240 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf

4 5 USERS *** /u01/app/oracle/oradata/orcl/users01.dbf

5 100 EXAMPLE *** /u01/app/oracle/oradata/orcl/example01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1 20 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf

c.查看asm實例是否已正常提供服務，以及磁盤的狀態並啟動新的orclasm實例

SQL> show parameter instance_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

instance_name string +ASM

SQL> select group_number,name,state,type,total_mb,free_mb,usable_file_mb fromv$asm_diskgroup;

GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB

------------ ------------------------------ ----------- ------ ---------- ---------- --------------

1 DG1 MOUNTED NORMAL 6134 6032 3016

2 REV MOUNTED EXTERN 2047 1997 1997

d.啟動輔助數據庫到nomount狀態

[oracle@oradb ~]$ export ORACLE_SID=orclasm

[oracle@oradb ~]$ sqlplus / as sysdba

idle> startup nomount

ORACLE instance started.

e.使用RMAN連接到目標數據庫和輔助數據庫來完成遷移

[oracle@oradb dbs]$ rman auxiliary / target sys/redhat@orcl

connected to target database: ORCL (DBID=1263182651)

connected to auxiliary database: ORCLASM (not mounted)

RMAN> run {

2> allocate auxiliary channel ach1 device type disk;

3> set until sequence 10 thread 1;

4> set newname for datafile 1 to '+DG1';

5> set newname for datafile 2 to '+DG1';

6> set newname for datafile 3 to '+DG1';

7> set newname for datafile 4 to '+DG1';

8> set newname for datafile 5 to '+DG1';

9> set newname for tempfile 1 to '+DG1';

10> duplicate target database to orclasm logfile

11> group 1('+DG1') size 5m reuse,

12> group 2('+DG1') size 5m reuse;

13> }

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 30-OCT-10

6.後續處理

a.查看數據庫狀態並關閉數據庫

[oracle@oradb ~]$ export ORACLE_SID=orclasm

[oracle@oradb ~]$ sqlplus / as sysdba

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- ----------

ORCLASM READ WRITE

SQL> shutdown immediate;

b.使用vi工具編輯initorclasm.ora

清除下列引數

*.db_file_name_convert=("orcl","orclasm")

*.log_file_name_convert=("orcl","orclasm")

c.啟動數據庫並創建spfile

SQL> startup

SQL> create spfile from pfile;

SQL> startup force; --如果是生產庫建議先shutdown immediate,然後startup

7.驗證遷移結果

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string orclasm

SQL> col name format a60

SQL> select name,status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

+DG1/orclasm/datafile/system.256.752170937 SYSTEM

+DG1/orclasm/datafile/undotbs1.259.752170937 ONLINE

+DG1/orclasm/datafile/sysaux.257.752170937 ONLINE

+DG1/orclasm/datafile/users.260.752170937 ONLINE

+DG1/orclasm/datafile/example.258.752170937 ONLINE

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 5 5242880 2 YES INACTIVE 563716 27-MAY-11

2 1 6 5242880 2 NO CURRENT 583985 27-MAY-11

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ------- ------- -------------------------------------------------- ---

2 ONLINE +DG1/orclasm/onlinelog/group_2.263.752171103 NO

1 STALE ONLINE +DG1/orclasm/onlinelog/group_1.262.752171103 NO

1 STALE ONLINE +REV/orclasm/onlinelog/group_1.256.752171103 YES

2 ONLINE +REV/orclasm/onlinelog/group_2.257.752171105 YES

SQL> select file#,creation_change#, status,enabled,bytes,name from v$tempfile;

FILE# CREATION_CHANGE# STATUS ENABLED BYTES NAME

---------- ---------------- ------- ---------- ---------- --------------------------------------------------

1 464714 ONLINE READ WRITE 20971520+DG1/orclasm/tempfile/temp.264.752171113

--如果在使用duplicate時沒有生成tempfile檔案，可以使用下面的方式來手動新增tempfile檔案。

alter tablespace temp add tempfile '+DG1' size 100m autoextend off;

--可以新增歸檔目錄，並修改一下歸檔路徑

--連接到ASM實例創建歸檔目錄

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

------------------------------ ------------

+ASM STARTED

SQL> alter diskgroup REV add directory '+REV/orclasm/arch';

Diskgroup altered.

--在orclasm實例中修改歸檔路徑

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

orclasm OPEN

SQL> alter system set log_archive_dest_1='LOCATION=+REV/orclasm/arch';

System altered.

SQL> select name from v$archived_log where rownum<2 order by stamp;

NAME

--------------------------------------------------

+REV/orclasm/1_1_752171102.arc

使用RMAN遷移檔案系統資料庫到ASM

相關文章