文件系统迁移至ASM

操作系统	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' migration 80

更改属主和属组

[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

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

           1 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

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

         1 +DATA/mydb/onlinelog/group_1.265.848845393

         2 +DATA/mydb/onlinelog/group_2.266.848845417

         4 +DATA/mydb/onlinelog/group_4.264.848845365

         4 +FRA/mydb/onlinelog/group_4.257.848845373

         1 +FRA/mydb/onlinelog/group_1.258.848845403

         2 +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实例关闭

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29492784/viewspace-1208951/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29492784/viewspace-1208951/