第一部分 .       概述

一、        實驗環境

Os：

[oracle@lzc dblzc]$ lsb_release

LSB Version:    :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

[oracle@lzc dblzc]$ lsb_release -a

LSB Version:    :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

Distributor ID: RedHatEnterpriseServer

Description:    Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Release:        5.4

Codename:       Tikanga

ASM DISKGROUP：

[oracle@lzc dbs]$ asmcmd

ASMCMD> ls

DGROUPA/

ASMCMD>

Database：

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

二、        實驗目標

將一個檔案系統資料庫testdb移植到asm例項上

三、        實驗設計

1、檢視資料庫中控制檔案，線上日誌，臨時檔名

2、修改引數檔案

3、執行rman copy

4、重新命名線上日誌

4、後期處理

第二部分 .       技術概述

一、        實驗步驟

1.       檢視資料庫中控制檔案，線上日誌，臨時檔名

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/testdb/redo03.log

/u01/app/oracle/oradata/testdb/redo02.log

/u01/app/oracle/oradata/testdb/redo01.log

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/testdb/control01.ctl

/u01/app/oracle/oradata/testdb/control02.ctl

/u01/app/oracle/oradata/testdb/control03.ctl

SQL> select name from v$tempfile;

NAME

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

/u01/app/oracle/oradata/testdb/temp01.dbf

2.       修改引數檔案

修改引數檔案的重點在修改db_create_online_log_dest_1、db_create_file_dest這兩個引數如果你開啟了閃回恢復區，最好就把db_recovery_file_dest引數也設定到ASM磁碟上。最後去掉引數檔案中的control_files這個引數。這裡由於我使用的是spfile所以我就線上修改之後再，轉儲為pfile。

a)        線上修改引數

SQL> ALTER SYSTEM SET db_create_file_dest='+DGROUPA';

System altered.

SQL> ALTER SYSTEM SET db_create_online_log_dest_1='+DGROUPA';

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='+DGROUPA';

System altered.

b)        從spfile建立pfile

SQL> CREATE PFILE='/home/oracle/exercise/pfile_testdb_asm.ora' from spfile;

File created.

c)         備份控制檔案

SQL> alter database backup controlfile to '/home/oracle/exercise/testdb_asm.ctl';

Database altered.

d)        關閉資料庫

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

e)        修改生成pfile 檔案

將control_files這個引數去掉

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

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

*.compatible='10.2.0.1.0'

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

*.db_block_size=8192

*.db_create_file_dest='+DGROUPA'

*.db_create_online_log_dest_1='+DGROUPA'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='testdb'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='+DGROUPA'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.job_queue_processes=10

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

3.       執行rman copy

Run{

STARTUP NOMOUNT PFILE='/home/oracle/exercise/pfile_testdb_asm.ora';

RESTORE CONTROLFILE FROM '/home/oracle/exercise/testdb_asm.ctl';//恢復備份的控制檔案

ALTER DATABASE MOUNT;

BACKUP AS COPY DATABASE FORMAT '+dgroupa';//這裡的備份僅限於資料檔案

RECOVER DATABASE;//只要前面使用了restore就需要recover進行同步

}

SWITCH DATABASE TO COPY;

Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     1218992 bytes

Variable Size                 92276304 bytes

Database Buffers             188743680 bytes

Redo Buffers                   2973696 bytes

Starting restore at 12-SEP-12

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=+DGROUPA/testdb/controlfile/current.265.793833703

Finished restore at 12-SEP-12

database mounted

released channel: ORA_DISK_1

Starting backup at 12-SEP-12

Starting implicit crosscheck backup at 12-SEP-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

Finished implicit crosscheck backup at 12-SEP-12

Starting implicit crosscheck copy at 12-SEP-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at 12-SEP-12

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf

output filename=+DGROUPA/testdb/datafile/system.266.793833717 tag=TAG20120912T212155 recid=1 stamp=793833808

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:38

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf

output filename=+DGROUPA/testdb/datafile/sysaux.267.793833815 tag=TAG20120912T212155 recid=2 stamp=793833870

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf

output filename=+DGROUPA/testdb/datafile/undotbs1.268.793833879 tag=TAG20120912T212155 recid=3 stamp=793833884

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/u01/app/oracle/oradata/testdb/users01.dbf

output filename=+DGROUPA/testdb/datafile/users.269.793833887 tag=TAG20120912T212155 recid=4 stamp=793833888

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

Finished backup at 12-SEP-12

RMAN-06497: WARNING: control file is not current, control file autobackup skipped

Starting recover at 13-SEP-12

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /u01/app/oracle/oradata/testdb/redo03.log

archive log filename=/u01/app/oracle/oradata/testdb/redo03.log thread=1 sequence=1

media recovery complete, elapsed time: 00:00:03

Finished recover at 13-SEP-12

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DGROUPA/testdb/datafile/system.266.793833717"

datafile 2 switched to datafile copy "+DGROUPA/testdb/datafile/undotbs1.268.793833879"

datafile 3 switched to datafile copy "+DGROUPA/testdb/datafile/sysaux.267.793833815"

datafile 4 switched to datafile copy "+DGROUPA/testdb/datafile/users.269.793833887"

RMAN> exit

RMAN> **end-of-file**

4.       檔案重新命名

執行下面這個sql指令碼,這裡用到的資訊都是在第一步中檢視的資訊。

ALTER DATABASE RENAME FILE  '/u01/app/oracle/oradata/testdb/redo03.log' TO '+DGOUPA';

ALTER DATABASE RENAME FILE  '/u01/app/oracle/oradata/testdb/redo02.log' TO '+DGOUPA';

ALTER DATABASE RENAME FILE  '/u01/app/oracle/oradata/testdb/redo01.log' TO '+DGOUPA';

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE;

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' DROP;

現在資料庫中只有tempfile還留在檔案系統中所以我們需要將它刪除，但是temp tablespace又不能沒有資料檔案所以我們需要先建立一個tempfile檔案，這個時候建立的tempfile會自動穿件到前面指定的db_create_file_dest='+DGROUPA'引數下面也就是會建立到asm磁碟上。這個時候再把檔案系統上的tempfile刪除。

5.       後期處理

將控制檔案的路徑新增到引數檔案中

a)        檢視當前控制檔案的位置

SQL> show parameter control

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      +DGROUPA/testdb/controlfile/cu

                                                 rrent.257.793878867

b)        修改pfile檔案

把control_files=’ +DGROUPA/testdb/controlfile/current.257.793878867’新增到引數檔案pfile_testdb_asm.ora中

c)         重啟數據庫

SQL> startup pfile=/home/oracle/exercise/pfile_testdb_asm.ora

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

d)        建立spfile

SQL> create spfile from pfile='/home/oracle/exercise/pfile_testdb_asm.ora';

File created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/10.2.0

                                                 .1/db_1/dbs/spfiletestdb.ora

至此，全部工作就完成了。

二、        實驗分析

問題一、建立diskgroup時找不到asm磁碟

SQL> startup

ASM instance started

Total System Global Area   83886080 bytes

Fixed Size                  1217836 bytes

Variable Size              57502420 bytes

ASM Cache                  25165824 bytes

ORA-15032: not all alterations performed

ORA-15063: ASM discovered an insufficient number of disks for diskgroup

"DGROUPA"

解決方案：

1、  可能是引數檔案中asm_diskstring這個引數的路徑設定有誤

2、  許可權問題，檢視/etc/udev/rules.d/60-raw.rules這個檔案

ACTION=="add", KERNEL=="raw[1-8]", WNER="oracle", GROUP="oinstall", MODE="660"

這條語句中是否給相應的使用者和使用者組賦予了許可權。

三、        實驗小結

在進行遷移前，一定要確定ASM例項在正常進行，所需要的asm磁碟組都正常掛載。