oracle 11g 文件系统迁移至asm,文件系统迁移至ASM-CSDN博客

操作系统

AIX5.3

主机名

AIX213

数据库版本

10.2.0

实例名

mydb

在实施迁移之前做一全库冷备！

1.创建vg

[root@aix213 /]$mkvg

-y 'migration' -s '64' '-f' hdisk3

2.建立逻辑卷

[root@aix213

/]$mklv -y 'asm1' -t 'raw'

migration 160

[root@aix213

/]$mklv -y 'asm2' -t 'raw'

migration80

更改属主和属组

[root@aix213 /dev]$chown oracle:oinstall rasm1

[root@aix213 /dev]$chown oracle:oinstall rasm2

3.创建ASM磁盘组

本人通过DBCA…

这个错误的意思是，要想使用ASM，必须配置并启动CSS，按照提示执行

[root@aix213 /]$cd

/u01/app/oracle/product/10.2.0/db_1/bin/

[root@aix213

bin]$localconfig add

/etc/oracle does not

exist. Creating it now.

Successfully

accumulated necessary OCR keys.

Creating OCR keys

for user 'root', privgrp 'system'..

Operation

successful.

Configuration for

local CSS has been initialized

Adding to inittab

Startup will be

queued to init within 30 seconds.

Checking the status

of new Oracle init process...

Expecting the CRS

daemons to be up within 600 seconds.

CSS is active on

these nodes.

aix213

CSS is active on all

nodes.

Oracle CSS service

is installed and running under init(1M)

然后创建磁盘组+DATA 和 +FRA,这里我用的是外部冗余

创建时需要修改ASM_DISKSTRING参数

SYS@ +ASM>alter system set

ASM_DISKSTRING='/dev/rasm*'; 这里是支持通配符的

如果使用命令行创建磁盘组：

export ORACLE_SID=+ASM

sqlplus / as sysdba

create diskgroup

DATA external redundancy disk '/dev/rasm1';

create diskgroup

FRA external redundancy disk '/dev/rasm2';

查看磁盘组状态

SQL> select GROUP_NUMBER,NAME,TYPE,TOTAL_MB,FREE_MB

from v$asm_diskgroup;

GROUP_NUMBER NAME TYPE TOTAL_MB

FREE_MB

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

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

DATA

EXTERN 10240 10190

2 RECOVER EXTERN 5120 4877

4.通过RMAN backup as copy 到+DATA

启动数据库到mount

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;

BACKUP AS COPY

INCREMENTAL LEVEL 0

DATABASE

FORMAT '+DATA'

TAG 'ORA_ASM_MIGRATION';

}

备份一下spfile

RMAN> BACKUP AS

BACKUPSET SPFILE;

关闭flashback

RMAN>

SQL'ALTER DATABASE FLASHBACK

OFF';

5.转储参数文件

RMAN>restore spfile to '+DATA/spfilemydb.ora';

RMAN>shutdown immediate

转储成功后,进入ORACLE_HOME/dbs目录,删除spfile文件,这里我只是mv了一下改了个名字,总之是为了让数据库启动时不再从这里读取spfile,而是读取pfile

pfile也备份一下,并在里面只留一行：

SPFILE='+DATA/spfilemydb.ora'

这样启动数据库时,会从pfile读取这条信息,从而去ASM磁盘组中找到spfile

6.启动到nomount修改闪回区路径和DB_CREATE_FILE_DEST

SQL> STARTUP FORCE

NOMOUNT;

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA' SID='mydb'; 这个参数不是必须的，是OMF

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G SID='mydb';

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA' SID='mydb';

7.转储控制文件NOMOUNT状态

RMAN>restore controlfile to '+DATA' from '/u01/app/oracle/oradata/mydb/control01.ctl';

RMAN>restore controlfile to '+FRA' from '/u01/app/oracle/oradata/mydb/control01.ctl';

转出成功后通过ASMCMD,分别进入+DATA/mydb/controlfile/ 和 +FRA/mydb/controlfile/

找到控制文件的名字,然后修改spfile

alter system set

control_files='+DATA/mydb/controlfile/backup.261.848844803','+fra/mydb/controlfile/backup.261.848849757'

scope=spfile sid='mydb';

shutdown

immediate

startup

mount

8.SWITCH DATABASE TO COPY

MOUNT状态进入RMAN

SWITCH DATABASE TO

COPY;

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;

ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;

RECOVER DATABASE;

}

成功后open数据库,检查是否迁移成功

SQL> select name

from v$datafile;

NAME

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

+DATA/mydb/datafile/system.257.848843449

+DATA/mydb/datafile/undotbs1.258.848843449

+DATA/mydb/datafile/sysaux.256.848843449

+DATA/mydb/datafile/users.259.848843449

SQL> show

parameter spfile

NAME TYPE VALUE

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

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

spfile string +DATA/spfilemydb.ora

SQL> show

parameter control

NAME TYPE VALUE

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

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

control_file_record_keep_time integer 7

control_files string +DATA/mydb/controlfile/backup.

261.848844803, +FRA/mydb/contr

olfile/backup.261.848849757

SQL> select

group#,member from v$logfile;

GROUP# MEMBER

----------

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

+DATA/mydb/onlinelog/group_1.265.848845393

+DATA/mydb/onlinelog/group_2.266.848845417

+DATA/mydb/onlinelog/group_4.264.848845365

+FRA/mydb/onlinelog/group_4.257.848845373

+FRA/mydb/onlinelog/group_1.258.848845403

+FRA/mydb/onlinelog/group_2.259.848845427

9.修改数据库的默认临时表空间

SQL>

alter database tempfile '/u01/app/oracle/oradata/mydb/temp01.dbf' drop;

SQL>

alter tablespace temp add tempfile;

SQL> select name

from v$tempfile;

NAME

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

+DATA/mydb/tempfile/temp.263.848845333

10.迁移online redo log

官方文档中提供了脚本

SET SERVEROUTPUT ON;

DECLARE

CURSOR rlc IS

SELECT GROUP# GRP, THREAD# THR, BYTES,

'NO' SRL

FROM

V$LOG

UNION

SELECT GROUP# GRP, THREAD# THR, BYTES,

'YES' SRL

FROM

V$STANDBY_LOG

ORDER BY 1;

stmt

VARCHAR2(2048);

BEGIN

FOR rlcRec IN rlc LOOP

IF (rlcRec.srl = 'YES') THEN

stmt := 'ALTER DATABASE ADD STANDBY

LOGFILE THREAD ' ||

rlcRec.thr || ' SIZE ' ||

rlcRec.bytes;

EXECUTE IMMEDIATE stmt;

stmt := 'ALTER DATABASE DROP STANDBY

LOGFILE GROUP ' || rlcRec.grp;

EXECUTE IMMEDIATE stmt;

ELSE

stmt := 'ALTER DATABASE ADD LOGFILE

THREAD ' ||

rlcRec.thr || ' SIZE ' || rlcRec.bytes;

EXECUTE IMMEDIATE stmt;

BEGIN

stmt := 'ALTER DATABASE DROP

LOGFILE GROUP ' || rlcRec.grp;

DBMS_OUTPUT.PUT_LINE(stmt);

EXECUTE IMMEDIATE stmt;

EXCEPTION

WHEN OTHERS THEN

EXECUTE IMMEDIATE 'ALTER SYSTEM

SWITCH LOGFILE';

EXECUTE IMMEDIATE 'ALTER SYSTEM

CHECKPOINT GLOBAL';

EXECUTE IMMEDIATE stmt;

END;

END IF;

END LOOP;

END;

最后关库重启，验证一下所有文件是否迁移成功，做一次全备

需要注意的一点事，ASM实例应限于RDBMS实例启动，后于RDBMS实例关闭

oracle 11g 文件系统 迁移至asm,文件系统迁移至ASM

oracle 11g 文件系统迁移至asm,文件系统迁移至ASM