将数据库迁移到ASM的实验记录

最新推荐文章于 2024-09-25 11:06:23 发布

csdn_0706

最新推荐文章于 2024-09-25 11:06:23 发布

阅读量103

点赞数

文章标签：数据库

昨天的blog中测试了在非RAC环境下创建ASM的过程，现在要使用已经创建的ASM，将数据库迁移到ASM上。

主要步骤：

1、设置control_files环境变量
2、迁移控制文件（controlfile）
3、迁移数据文件（datafile）
4、迁移临时文件（tempfile）
5、迁移在线日志（redolog）

纤细操作过程如下：

[@more@]

1、设置control_files环境变量

设置这个环境变量的目的是为了下一步使用restore controlfile的时候，能直接在asm上创建需要的控制文件。

[oracle@oracle ~]$ echo $ORACLE_SID
orcl
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 18 09:12:48 2009

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control01
.ctl, /oracle/oradata/orcl/con
trol02.ctl, /oracle/oradata/or
cl/control03.ctl
SQL> alter system set control_files='+DATA/ORCL/control01.ctl','+DATA/ORCL/control02.ctl','+DATA/ORCL/control03.ctl' scope=spfile;

System altered.

2、迁移控制文件（controlfile）

这一步就是使用rman的restore controlfile命令，将本地磁盘上的控制文件恢复到数据库的control_files参数指定的文件位置。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 71305484 bytes
Database Buffers 88080384 bytes
Redo Buffers 7168000 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@oracle ~]$ rman target /;

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Dec 18 09:35:25 2009

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

connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/oracle/oradata/orcl/control01.ctl';

Starting restore at 18-DEC-09
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=+DATA/orcl/control01.ctl
output filename=+DATA/orcl/control02.ctl
output filename=+DATA/orcl/control03.ctl
Finished restore at 18-DEC-09

3、迁移数据文件（datafile）

迁移数据文件的操作主要就是使用rman的backup as copy命令，将整个数据库备份到ASM上，然后执行switch命令，修改controlfile以使用备份的数据文件

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA';

Starting backup at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
output filename=+DATA/orcl/datafile/system.259.705922671 tag=TAG20091218T093751 recid=11 stamp=705922714
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
output filename=+DATA/orcl/datafile/sysaux.260.705922719 tag=TAG20091218T093751 recid=12 stamp=705922741
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oracle/oradata/orcl/example01.dbf
output filename=+DATA/orcl/datafile/example.261.705922745 tag=TAG20091218T093751 recid=13 stamp=705922752
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
output filename=+DATA/orcl/datafile/undotbs1.262.705922759 tag=TAG20091218T093751 recid=14 stamp=705922762
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/oracle/oradata/orcl/tbs_lmt01.dbf
output filename=+DATA/orcl/datafile/tbs_lmt.263.705922767 tag=TAG20091218T093751 recid=15 stamp=705922767
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/oracle/oradata/orcl/tbs_lmt_2_01.dbf
output filename=+DATA/orcl/datafile/tbs_lmt_2.264.705922769 tag=TAG20091218T093751 recid=16 stamp=705922770
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/oracle/oradata/orcl/tbs_lmt02.dbf
output filename=+DATA/orcl/datafile/tbs_lmt_3.265.705922773 tag=TAG20091218T093751 recid=17 stamp=705922774
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
output filename=+DATA/orcl/datafile/users.266.705922777 tag=TAG20091218T093751 recid=18 stamp=705922776
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.267.705922777 tag=TAG20091218T093751 recid=19 stamp=705922778
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
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 18-DEC-09
channel ORA_DISK_1: finished piece 1 at 18-DEC-09
piece handle=+DATA/orcl/backupset/2009_12_18/nnsnf0_tag20091218t093751_0.268.705922783 tag=TAG20091218T093751 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-DEC-09

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.259.705922671"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/undotbs1.262.705922759"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/sysaux.260.705922719"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.266.705922777"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.261.705922745"
datafile 6 switched to datafile copy "+DATA/orcl/datafile/tbs_lmt.263.705922767"
datafile 7 switched to datafile copy "+DATA/orcl/datafile/tbs_lmt_2.264.705922769"
datafile 8 switched to datafile copy "+DATA/orcl/datafile/tbs_lmt_3.265.705922773"

RMAN> alter database open;

database opened

RMAN> exit

Recovery Manager complete.

4、迁移临时文件（tempfile）

临时文件不能通过rman备份恢复，只好先在asm上新建一个，然后再把原来的删除。
这里使用了oracle的omf特性（通过设置db_create_file_dest参数实现），创建临时文件的时候不用指定文件名。

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 18 09:40:50 2009

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> select file#,name from v$tempfile;

FILE#
----------
NAME
--------------------------------------------------------------------------------
1
/oracle/oradata/orcl/temp01.dbf

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
TBS_LMT
TBS_LMT_2
TBS_LMT_3

9 rows selected.
SQL> alter system set db_create_file_dest='+DATA' scope=both;

System altered.

SQL> alter tablespace temp add tempfile;

Tablespace altered.

SQL> alter tablespace temp drop tempfile 1;

Tablespace altered.

5、迁移在线日志（redolog）

在线日志不能通过rman备份恢复，只好先在asm上新建几组，然后再把原来的删除。
这里使用了oracle的omf特性（通过设置db_create_online_log_dest_1参数实现），创建在线日志文件的时候不用指定文件名。

SQL> select group#,member from v$logfile;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/oracle/oradata/orcl/redo03.log

2
/oracle/oradata/orcl/redo02.log

1
/oracle/oradata/orcl/redo01.log

SQL> show parameter create

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string +DATA
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
SQL> alter system set db_create_online_log_dest_1='+DATA' scope=both;

System altered.
SQL> alter database add logfile group 4 size 100m;

Database altered.

SQL> c/4/5
1* alter database add logfile group 5 size 100m
SQL> /

Database altered.

SQL> c/5/6
1* alter database add logfile group 6 size 100m
SQL> /

Database altered.

SQL> select group#,member from v$logfile;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/oracle/oradata/orcl/redo03.log

2
/oracle/oradata/orcl/redo02.log

1
/oracle/oradata/orcl/redo01.log

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
4
+DATA/orcl/onlinelog/group_4.270.705923247

5
+DATA/orcl/onlinelog/group_5.271.705923261

6
+DATA/orcl/onlinelog/group_6.272.705923271

6 rows selected.

SQL> select group#,status from v$log;

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

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> c/1/2
1* alter database drop logfile group 2
SQL> /

Database altered.

SQL> c/2/3
1* alter database drop logfile group 3
SQL> /

Database altered.

-- end --

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

转载于:http://blog.itpub.net/22049049/viewspace-1029775/