此文是在oracle10g的基础上，将数据库从文件系统迁移到ASM管理的文件系统上。

一、环境：

OS :

[oracle@yitai ~]$ lsb_release --all
LSB Version:    :core-3.0-ia32:core-3.0-noarch:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: RedHatEnterpriseAS
Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 7)
Release:        4
Codename:       NahantUpdate7

RDBMS:

SQL> SELECT * FROM V$VERSION;

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

ASM:(这里使用磁盘组DATA2)

SQL> select name, state, type from v$asm_diskgroup;

NAME                           STATE       TYPE
------------------------------ ----------- ------
DATA1                          MOUNTED     EXTERN
DATA2                          MOUNTED     EXTERN

[oracle@yitai ~]$ export ORACLE_SID=+ASM
[oracle@yitai ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal Unbal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N      N         512   4096 1048576      2038      763                0             763              0 DATA1/
MOUNTED EXTERN N      N         512   4096 1048576      2038      929                0             929              0 DATA2/

二、迁移前查看rdbms的信息

查看当前文件系统的控制文件路径

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control01.ctl
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl

查看临时文件信息

SQL>SQL> select file#, name from v$tempfile;
FILE#, NAME
-- --------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/prod/temp01.dbf

查看日志文件信息：

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ ----------------
         1 /u01/app/oracle/oradata/prod/redo01.log                  CURRENT
         2 /u01/app/oracle/oradata/prod/redo02.log                  INACTIVE
         3 /u01/app/oracle/oradata/prod/redo03.log               INACTIVE

查看数据文件位置：

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/prod//system01.dbf
/u01/app/oracle/oradata/prod/undotbs01.dbf
/u01/app/oracle/oradata/prod/sysaux01.dbf
/u01/app/oracle/oradata/prod//users01.dbf
/u01/app/oracle/oradata/prod//example01.dbf

三、利用RMAN工具迁移

export ORACLE_SID=prod

SQL > shutdown immediate

SQL > startup nomount #rdbms操作控制文件的时候，需要进入nomount状态

[oracle@yitai ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 28 08:01:45 2012

connected to target database: CUUG (DBID=1247937484)

转移控制文件

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

Starting restore at 21-AUG-12
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 21-AUG-12

查看新的控制文件

ASMCMD> cd +DATA2
ASMCMD> ls
PROD/
DB_UNKNOWN/
ASMCMD> cd prod
ASMCMD> ls
CONTROLFILE/
ASMCMD> find -t controlfile . *
+DATA2/prod/CONTROLFILE/Backup.262.792483465
+DATA2/prod/CONTROLFILE/backup.256.792483231

修改当前系统的控制文件参数相关

SQL> alter system set control_files='+DATA2/prod/CONTROLFILE/Backup.262.792483465, +DATA2/prod/CONTROLFILE/backup.256.792483231' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

RACLE instance shut down.

SQL> startup mount; #操作数据文件，临时文件，日志文件rdbms需要进入mount状态（因为前面的控制文件已经完成，所以这里进入mount状态是完全没问题的）

ORACLE instance started.

将数据库文件系统备份到ASM磁盘组

[oracle@yitai ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 28 08:08:37 2012

connected to target database: CUUG (DBID=1247937484)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA2';

将数据库文件系统切换到ASM磁盘组

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile +DATA2/prod/datafile/system.257.792483427
datafile 2 switched to datafile +DATA2/prod/datafile/undotbs1.260.792483461
datafile 3 switched to datafile +DATA2/prod/datafile/sysaux.258.792483441
datafile 4switched to datafile +DATA2/prod/datafile/users.261.792483463

datafile 5 switched to datafile
+DATA2/prod/datafile/example.259.792483457

修改临时文件

RMAN > run {

set newname for tempfile 1 to '+DATA2';

switch tempfile all;

}

或者处于mount状态，重新在+DATA2上建立一个临时文件，然后再删除掉以前文件系统的临时文件。这两种方法都可以转移tempfile.

如果是之前一致性关闭数据库（shutdown immediate），可以直接删掉文件系统上的日志文件然后在+DATA2上重建，或者是rename 日志文件到+DATA2上。这些方法都可行，不过这里这两种方法没有采用。而采用是开库以后，在+DATA2上增加了新的日志文件，在文件系统上删除掉旧的日志文件。这三种方法都可以转移日志文件。第三种方法如下：

恢复参数文件

RMAN> run {

backup as backupset spfile;

restore spfile to "+DATA2/spfileprod.ora";

}

修改initprod.ora内容如下：

spfile=+DATA2/spfileprod.ora

RMAN> ALTER DATABASE OPEN;

database opened

[oracle@yitai ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 28 08:24:59 2012

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ ----------------
         1 /u01/app/oracle/oradata/prod/redo01.log                  CURRENT
         2 /u01/app/oracle/oradata/prod/redo02.log                  INACTIVE
         3 /u01/app/oracle/oradata/prod/redo03.log               INACTIVE

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA2' TO GROUP 1;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA2' TO GROUP 2;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA2' TO GROUP 3;

SQL> alter system checkpoint; #这个是把日志的状态从active 变为inactive，active的日志是不允许删除的（不行的话就试试alter system swith logfile，切换日志，因为状态是current的日志也不允许删除的）

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ ----------------
         1 /u01/app/oracle/oradata/prod/redo01.log                  CURRENT
         2 /u01/app/oracle/oradata/prod/redo02.log                  INACTIVE
         3 /u01/app/oracle/oradata/prod/redo03.log               INACTIVE

        1 +DATA2/prodonlinelog/group_1.265.792483781                  inactive
         2 +DATA2/prod/onlinelog/group_2.266.792483795                  INACTIVE
         3 +DATA2/prod/onlinelog/group_3.267.792483801                  INACTIVE

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/prod/redo03.log';

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/prod/redo02.log';

SQL> alter system switch logfile; #切一下日志才能删除redo01.log，因为当前状态是current，正在使用

System altered.

SQL> alter system checkpoint;

SQL > /

    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ ----------------
         1 /u01/app/oracle/oradata/prod/redo01.log                  inactive         2 /u01/app/oracle/oradata/prod/redo02.log                  INACTIVE
         3 /u01/app/oracle/oradata/prod/redo03.log               INACTIVE

         1 +DATA2/prodonlinelog/group_1.265.792483781                  CURRENT
         2 +DATA2/prod/onlinelog/group_2.266.792483795                  INACTIVE
         3 +DATA2/prod/onlinelog/group_3.267.792483801                  INACTIVE

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ ----------------
         1 +DATA2/pord/onlinelog/group_1.265.792483781                  CURRENT
         2 +DATA2/prod/onlinelog/group_2.266.792483795                  INACTIVE
         3 +DATA2/prod/onlinelog/group_3.267.792483801                  INACTIVE

SQL> select file#, name from v$tempfile;

FILE# NAME
---------- ------------------------------------------------------------
1 +DATA2/prod/tempfile/temp.264.792483707

SQL> select name from v$datafile;

NAME
------------------------------------------------------------
+DATA2/prod/datafile/system.257.792483427
+DATA2/prod/datafile/undotbs1.260.792483461
+DATA2/prod/datafile/sysaux.258.792483441
+DATA2/prod/datafile/users.261.792483463
+DATA2/prod/datafile/example.259.792483457

[oracle@yitai ~]$ vi /u01/app/oracle/product/10.2.0/db_1/dbs/initprod.ora

spfile='+DATA1/prod/spfileprod.ora'

查看asm文件结构

[oracle@yitai ~]$ export ORACLE_SID=+ASM
[oracle@yitai ~]$ asmcmd
ASMCMD> cd +DATA2
ASMCMD> ls
PROD/
ASMCMD> cd prod(# asmcmd不区分大小写)
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
spfilecuug.ora

重启rdbms数据库，查看spfile位置是否修改成功

SQL> shutdown immediate

SQL > startup

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA2/prod/spfilecuug.ora

查询测试 :

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

到此全部OK了。 GOOK LUCK WITH YOU!

ITPub博客

asm 文件系统迁移

最新文章