ITPub博客

首页 > 数据库 > Oracle > 使用rman将数据库迁移到ASM实例

使用rman将数据库迁移到ASM实例

原创 Oracle 作者:yewushang 时间:2014-03-24 14:16:03 0 删除 编辑
使用rman将数据库迁移到ASM实例


一、创建ASM实例并启动ASM实例;
我们可以手动创建ASM实例,也可以使用DBCA来创建ASM实例;
[oracle@10g ~]$ export ORACLE_SID=+ASM
[oracle@10g ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 14 00:15:44 2012


Copyright (c) 1982, 2005, Oracle.  All rights reserved.




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

SQL> startup
ASM instance started


Total System Global Area   83886080 bytes
Fixed Size    1217836 bytes
Variable Size   57502420 bytes
ASM Cache   25165824 bytes
ORA-15110: no diskgroups mounted




SQL> select status from v$instance;


STATUS
------------------------------------
STARTED


Elapsed: 00:00:00.02



二、创建ASM磁盘;

Disk /dev/sdb: 7516 MB, 7516192768 bytes
255 heads, 63 sectors/track, 913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes


  Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         366     2939863+  83  Linux
/dev/sdb2             367         732     2939895   83  Linux
/dev/sdb3             733         913     1453882+  83  Linux
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk DATA /dev/sdb1
Marking disk "DATA" as an ASM disk:                        [  OK  ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk RECOVERY /dev/sdb2
Marking disk "RECOVERY" as an ASM disk:                    [  OK  ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk LOG /dev/sdb3
Marking disk "LOG" as an ASM disk:                         [  OK  ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm listdisks
DATA
LOG
RECOVERY





三、登入到ASM创建磁盘组
[oracle@10g ~]$ export ORACLE_SID=+ASM
[oracle@10g ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 14 00:15:44 2012


Copyright (c) 1982, 2005, Oracle.  All rights reserved.




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


SQL> startup
ASM instance started


Total System Global Area   83886080 bytes
Fixed Size    1272120 bytes
Variable Size   57448136 bytes
ASM Cache   25165824 bytes
ORA-15110: no diskgroups mounted (由于没有创建diskgroup,所以这里报错)


以下创建diskgroup
SQL> create diskgroup DATA external redundancy disk 'ORCL:DATA';


Diskgroup created.


SQL> create diskgroup RECOVERY external redundancy disk 'ORCL:RECOVERY';


Diskgroup created.


SQL> create diskgroup LOG external redundancy disk 'ORCL:LOG';


Diskgroup created.


SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started


Total System Global Area   83886080 bytes
Fixed Size    1272120 bytes
Variable Size   57448136 bytes
ASM Cache   25165824 bytes
ASM diskgroups mounted
SQL> 





使用v$asm_diskgroup查看当前ASM磁盘组的状态,以及类型;
SQL> col NAME for a20;
SQL> select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB from v$asm_diskgroup;


GROUP_NUMBER NAME  STATE    TYPE TOTAL_MB
------------ -------------------- --------------------------------- ------------------ ----------
  1 DATA  MOUNTED    EXTERN     2870
  2 LOG  MOUNTED    EXTERN     1419
  3 RECOVERY  MOUNTED    EXTERN     2870

SQL> col PATH for a30;
SQL> select GROUP_NUMBER,DISK_NUMBER,PATH from v$asm_disk;

GROUP_NUMBER DISK_NUMBER PATH
------------ ----------- ------------------------------
  1       0 ORCL:DATA
  2       0 ORCL:LOG
  3       0 ORCL:RECOVERY





使用两个视图联合查看;
SQL> col NAME for a20;
SQL> col PATH for a20;
SQL> col STATE for a20;
SQL> select g.group_number,d.name,d.path,g.state,g.type
 2  from v$asm_diskgroup g,v$asm_disk d
 3  where g.group_number=d.group_number;
GROUP_NUMBER NAME  PATH       STATE    TYPE
------------ -------------------- -------------------- -------------------- ------------------
  1 DATA  ORCL:DATA       MOUNTED    EXTERN
  2 LOG  ORCL:LOG       MOUNTED    EXTERN
  3 RECOVERY  ORCL:RECOVERY        MOUNTED    EXTERN




使用ASMCMD查看磁盘;
[oracle@10g ~]$ echo $ORACLE_SID
+ASM
[oracle@10g ~]$ asmcmd
ASMCMD> ls
DATA/
LOG/
RECOVERY/

四、使用RMAN将数据库迁移到ASM实例;
1)修改SPFILE参数文件,在SPFILE参数中增加几个参数以及相应的值。
添加参数的内容:
control_files='+DATA'
DB_CREATE_FILE_DEST='+DATA'
DB_RECOVERY_FILE_DEST='+RECOVERY'
DB_RECOVERY_FILE_DEST_SIZE=3g
DB_CREATE_ONLINE_LOG_DEST_1='+LOG'


实际上ASM使用的是OMF文件系统。所以必须设置这些参数。下面分别介绍这四个参数的含义;
*DB_CREATE_FILE_DEST:这个参数指定了数据文件、控制文件、重做日志文件以及临时文件的默认创建位置,如果没有设置DB_CREATE_ONLINE_LOG_DEST_n参数,会在参数
DB_CREATER_FILE_DEST指定的磁盘组中创建重做日志文件和控制文件。
*DB_RECOVERY_FILE_DEST:该参数指定了RMAN备份、闪回日志以及归档日志的存储位置,如果没有设置参数DB_CREATE_ONLINE_LOG_DEST_n,则会
在DB_RECOVERY_FILE_DEST参数指定的目录下创建一个重做日志文件。
*DB_RECOVERY_FILE_DEST_SIZE:该参数设置快闪恢复区的大小。
*DB_CREATE_ONLINE_LOG_DEST_n:该参数存储重做日志和控制文件的副本,实现多路复用。

修改ORACLE_SID登录实例名为数据库系统名(如:ORCL)
[oracle@10g ~]$ echo $ORACLE_SID
orcl

[oracle@10g ~]$ sqlplus / as sysdba 


SQL*Plus: Release 10.2.0.5.0 - Production on Tue Aug 14 23:21:50 2012


Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to an idle instance.


SQL> startup
ORACLE instance started.


Total System Global Area  285212672 bytes
Fixed Size    1273276 bytes
Variable Size  125829700 bytes
Database Buffers  155189248 bytes
Redo Buffers    2920448 bytes
Database mounted.
Database opened.

SQL> show parameter control


NAME     TYPE       VALUE
------------------------------------ --------------------------------- ------------------------------
control_file_record_keep_time     integer       7
control_files     string       /u01/app/oracle/oradata/orcl/c
      ontrol01.ctl, /u01/app/oracle/
      oradata/orcl/control02.ctl, /u
      01/app/oracle/oradata/orcl/con
      trol03.ctl
SQL> show parameter db_create_file


NAME     TYPE       VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_file_dest     string

SQL> show parameter db_recovery_file


NAME     TYPE       VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest     string       /u01/app/oracle/flash_recovery
      _area
db_recovery_file_dest_size     big integer       2G

SQL> show parameter db_create_online_log


NAME     TYPE       VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_online_log_dest_1     string
db_create_online_log_dest_2     string
db_create_online_log_dest_3     string
db_create_online_log_dest_4     string
db_create_online_log_dest_5     string






以上是我们需要修改的几个参数
使用alter system set修改参数文件;
alter system set control_files='+DATA' scope=spfile;
alter system set db_create_file_dest='+DATA' scope=spfile;
alter system set db_recovery_file_dest='+RECOVERY' scope=spfile;
alter system set db_recovery_file_dest_size=3g scope=spfile;
alter system set db_create_online_log_dest_1='+LOG' scope=spfile;


修改好,shutdown immediate数据库,下面使用rman迁移数据库文件到ASM磁盘组

2)使用rman迁移数据库文件
登入到rman,并启动到nomount状态;
[oracle@10g ~]$ rman target /


Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 14 23:46:09 2012


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


connected to target database (not started)


RMAN> startup nomount;


Oracle instance started


Total System Global Area     285212672 bytes


Fixed Size                     1273276 bytes
Variable Size                121635396 bytes
Database Buffers             159383552 bytes
Redo Buffers                   2920448 bytes


使用原有的control file 文件恢复到+DATA,并启动到mount状态
RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/control01.ctl';


Starting restore at 14-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK


channel ORA_DISK_1: copied control file copy
output filename=+DATA/orcl/controlfile/current.256.791336867
Finished restore at 14-AUG-12


RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1


使用rman备份特性,把数据库文件移动到asm磁盘组;
RMAN> backup as copy database format '+DATA';


Starting backup at 14-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output filename=+DATA/orcl/datafile/system.257.791336925 tag=TAG20120814T234844 recid=2 stamp=791336949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output filename=+DATA/orcl/datafile/undotbs1.258.791336951 tag=TAG20120814T234844 recid=3 stamp=791336972
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output filename=+DATA/orcl/datafile/sysaux.259.791336975 tag=TAG20120814T234844 recid=4 stamp=791336989
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=+DATA/orcl/datafile/example.260.791336991 tag=TAG20120814T234844 recid=5 stamp=791336995
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/orcl/users01.dbf
output filename=+DATA/orcl/datafile/users.261.791336997 tag=TAG20120814T234844 recid=6 stamp=791336998
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/orcl/controlfile/backup.262.791336999 tag=TAG20120814T234844 recid=7 stamp=791337000
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 14-AUG-12
channel ORA_DISK_1: finished piece 1 at 14-AUG-12
piece handle=+DATA/orcl/backupset/2012_08_14/nnsnf0_tag20120814t234844_0.263.791337003 tag=TAG20120814T234844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-AUG-12


RMAN> list copy;


specification does not match any archive log in the recovery catalog


List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
2       1    A 14-AUG-12       847185     14-AUG-12       +DATA/orcl/datafile/system.257.791336925
3       2    A 14-AUG-12       847185     14-AUG-12       +DATA/orcl/datafile/undotbs1.258.791336951
4       3    A 14-AUG-12       847185     14-AUG-12       +DATA/orcl/datafile/sysaux.259.791336975
6       4    A 14-AUG-12       847185     14-AUG-12       +DATA/orcl/datafile/users.261.791336997
5       5    A 14-AUG-12       847185     14-AUG-12       +DATA/orcl/datafile/example.260.791336991


List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
7       A 14-AUG-12       847185     14-AUG-12       +DATA/orcl/controlfile/backup.262.791336999


利用RMAN的SWITCH 命令修改控制文件内数据文件的指针,使其指向新位置
RMAN> switch database to copy;


datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.257.791336925"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/undotbs1.258.791336951"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/sysaux.259.791336975"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.261.791336997"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.260.791336991"


RMAN> recover database;


Starting recover at 14-AUG-12
using channel ORA_DISK_1


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


Finished recover at 14-AUG-12


RMAN> alter database open;


database opened


RMAN> 


以上数据库文件迁移完成;
现在进入到sqlplus,重建tempfile,新建logfille到ASM磁盘组

3)增加新的tempfile,logfile到磁盘组
[oracle@10g ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.5.0 - Production on Tue Aug 14 23:54:09 2012


Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.




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

由于临时文件不会被迁移,所以我们只需要删除原来的增加新的就可以。
SQL> select name from v$tempfile;


NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf


SQL> alter tablespace temp add tempfile '+DATA';


Tablespace altered.


SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';


Tablespace altered.


SQL> select name from v$tempfile;


NAME
--------------------------------------------------------------------------------
+DATA/orcl/tempfile/temp.264.791337285


增加新的ONLINE REDOLOGS 到ASM
SQL> select GROUP#, MEMBERS, STATUS from v$log;


   GROUP#    MEMBERS STATUS
---------- ---------- ------------------------------------------------
1    1 CURRENT
2    1 INACTIVE
3    1 INACTIVE


SQL> alter database add logfile '+LOG' size 50M;


Database altered.


SQL> alter database add logfile '+LOG' size 50M;


Database altered.


SQL> alter database add logfile '+LOG' size 50M;


Database altered.


SQL> select GROUP#, MEMBERS, STATUS from v$log;


   GROUP#    MEMBERS STATUS
---------- ---------- ------------------------------------------------
1    1 CURRENT
2    1 INACTIVE
3    1 INACTIVE
4    1 UNUSED
5    1 UNUSED
6    1 UNUSED


6 rows selected.


切换日志组
SQL> alter system switch logfile;


System altered.


SQL> alter system switch logfile;


System altered.


SQL> alter system switch logfile;


System altered.


SQL> select GROUP#, MEMBERS, STATUS from v$log;


   GROUP#    MEMBERS STATUS
---------- ---------- ------------------------------------------------
1    1 ACTIVE
2    1 INACTIVE
3    1 INACTIVE
4    1 ACTIVE
5    1 ACTIVE
6    1 CURRENT


6 rows selected.


checkpoint触发日志写
SQL> alter system checkpoint;


System altered.


SQL> select GROUP#, MEMBERS, STATUS from v$log;


   GROUP#    MEMBERS STATUS
---------- ---------- ------------------------------------------------
1    1 INACTIVE
2    1 INACTIVE
3    1 INACTIVE
4    1 INACTIVE
5    1 INACTIVE
6    1 CURRENT


6 rows selected.


SQL> alter database drop logfile group 1;


Database altered.


SQL> alter database drop logfile group 2;


Database altered.


SQL> alter database drop logfile group 3;


Database altered.


SQL> select GROUP#, MEMBERS, STATUS from v$log;


   GROUP#    MEMBERS STATUS
---------- ---------- ------------------------------------------------
4    1 INACTIVE
5    1 INACTIVE
6    1 CURRENT


SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
+LOG/orcl/onlinelog/group_4.256.791337483
+LOG/orcl/onlinelog/group_5.257.791337487
+LOG/orcl/onlinelog/group_6.258.791337489


SQL> select name from v$datafile
 2  union all
 3  select name from v$controlfile
 4  union all
 5  select member from v$logfile
 6  union all
 7* select name from v$tempfile


NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.257.791336925
+DATA/orcl/datafile/undotbs1.258.791336951
+DATA/orcl/datafile/sysaux.259.791336975
+DATA/orcl/datafile/users.261.791336997
+DATA/orcl/datafile/example.260.791336991
+DATA/orcl/controlfile/current.256.791336867
+LOG/orcl/onlinelog/group_4.256.791337483
+LOG/orcl/onlinelog/group_5.257.791337487
+LOG/orcl/onlinelog/group_6.258.791337489
+DATA/orcl/tempfile/temp.264.791337285


10 rows selected.


SQL> show parameter db_recover


NAME     TYPE       VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest     string       +RECOVERY
db_recovery_file_dest_size     big integer       3G

五、最后删除原来的数据库文件


[oracle@10g orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@10g orcl]$ ls
control01.ctl  control03.ctl  redo01.log  redo03.log    system01.dbf   users01.dbf
control02.ctl  example01.dbf  redo02.log  sysaux01.dbf  undotbs01.dbf


[oracle@10g orcl]$ rm -rf *
[oracle@10g orcl]$ ls
[oracle@10g orcl]$ 


ps:感谢老王!

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

请登录后发表评论 登录
全部评论

注册时间:2014-02-09

  • 博文量
    53
  • 访问量
    279603