ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 迁移数据库从文件系统到ASM

迁移数据库从文件系统到ASM

原创 Linux操作系统 作者:xuyongw2000 时间:2012-01-09 13:33:02 0 删除 编辑
数据库版本:10.2.0.1
OS版本:Redhat 5.5
1、准备ASM磁盘
[root@test ~]# fdisk -l
Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 2100 16868218+ 83 Linux
/dev/sda2 2101 2610 4096575 82 Linux swap
Disk /dev/sdb: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
Disk /dev/sdd: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
[root@test ~]# fdisk /dev/sdb
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-522, default 522):
Using default value 522
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@test ~]# fdisk /dev/sdc
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-522, default 522):
Using default value 522
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@test ~]# fdisk /dev/sdd
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-522, default 522):
Using default value 522
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@test ~]# fdisk -l
Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 2100 16868218+ 83 Linux
/dev/sda2 2101 2610 4096575 82 Linux swap
Disk /dev/sdb: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 522 4192933+ 83 Linux
Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdc1 1 522 4192933+ 83 Linux
Disk /dev/sdd: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdd1 1 522 4192933+ 83 Linux

[root@test ~]# raw /dev/raw/raw1 /dev/sdb1
/dev/raw/raw1: bound to major 8, minor 17
[root@test ~]# raw /dev/raw/raw2 /dev/sdc1
/dev/raw/raw2: bound to major 8, minor 33
[root@test ~]# raw /dev/raw/raw3 /dev/sdd1
/dev/raw/raw3: bound to major 8, minor 49
[root@test ~]# chown oracle:dba /dev/raw
raw/ rawctl
[root@test ~]# chown oracle:dba /dev/raw/raw*
---可以把以上命令添加到/etc/rc.local里

2、安装CSS服务
[root@test ~]# cd /usr/local/oracle/10g/bin
[root@test bin]# ./localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 90 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.
test
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@test bin]#
3、准备ASM参数文件

[oracle@test dbs]$ vi init+ASM.ora
instance_type=asm
instance_name=asm
asm_diskstring='/dev/raw/raw*'
[oracle@test dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 28 18:18:37 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ASM instance started
Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 53308140 bytes
ASM Cache 25165824 bytes
SQL> desc v$asm_disk;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
DISK_NUMBER NUMBER
COMPOUND_INDEX NUMBER
INCARNATION NUMBER
MOUNT_STATUS VARCHAR2(7)
HEADER_STATUS VARCHAR2(12)
MODE_STATUS VARCHAR2(7)
STATE VARCHAR2(8)
REDUNDANCY VARCHAR2(7)
LIBRARY VARCHAR2(64)
TOTAL_MB NUMBER
FREE_MB NUMBER
NAME VARCHAR2(30)
FAILGROUP VARCHAR2(30)
LABEL VARCHAR2(31)
PATH VARCHAR2(256)
UDID VARCHAR2(64)
PRODUCT VARCHAR2(32)
CREATE_DATE DATE
MOUNT_DATE DATE
REPAIR_TIMER NUMBER
READS NUMBER
WRITES NUMBER
READ_ERRS NUMBER
WRITE_ERRS NUMBER
READ_TIME NUMBER
WRITE_TIME NUMBER
BYTES_READ NUMBER
BYTES_WRITTEN NUMBER
SQL> select disk_number,name from v$asm_disk;
DISK_NUMBER NAME
----------- ------------------------------------------------------------
0
2
1
SQL> select disk_number,path from v$asm_disk;---查看ASM是否识别到硬盘
DISK_NUMBER
-----------
PATH
--------------------------------------------------------------------------------
0
/dev/raw/raw3
2
/dev/raw/raw1
1
/dev/raw/raw2

SQL> col path format a20
SQL> set linesize 120
SQL> /
DISK_NUMBER PATH
----------- --------------------
0 /dev/raw/raw3
2 /dev/raw/raw1
1 /dev/raw/raw2
SQL>

SQL> create diskgroup dg1 external redundancy disk '/dev/raw/raw*' force;
Diskgroup created.

4、迁移控制文件到ASM
[oracle@test dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 28 18:25:13 2011
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> alter system set control_files='+dg1' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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@test dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 28 18:26:05 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 1090519040 bytes
Fixed Size 1218920 bytes
Variable Size 301991576 bytes
Database Buffers 771751936 bytes
Redo Buffers 15556608 bytes
RMAN> restore controlfile from '/usr/local/oracle/oradata/test/control01.ctl';
Starting restore at 28-DEC-11
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=+DG1/test/controlfile/backup.256.771099991
Finished restore at 28-DEC-11
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1090519040 bytes
Fixed Size 1218920 bytes
Variable Size 301991576 bytes
Database Buffers 771751936 bytes
Redo Buffers 15556608 bytes
RMAN>
5、迁移数据文件到ASM
[oracle@test dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 28 18:27:33 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=2069242183, not open)
RMAN> backup as copy database format='+dg1';
Starting backup at 28-DEC-11
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: starting datafile copy
input datafile fno=00001 name=/usr/local/oracle/oradata/test/system01.dbf
output filename=+DG1/test/datafile/system.257.771100067 tag=TAG20111228T182746 recid=2 stamp=771100078
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/usr/local/oracle/oradata/test/sysaux01.dbf
output filename=+DG1/test/datafile/sysaux.258.771100083 tag=TAG20111228T182746 recid=3 stamp=771100089
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/usr/local/oracle/oradata/test/example01.dbf
output filename=+DG1/test/datafile/example.259.771100097 tag=TAG20111228T182746 recid=4 stamp=771100102
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/usr/local/oracle/oradata/test/undotbs01.dbf
output filename=+DG1/test/datafile/undotbs1.260.771100105 tag=TAG20111228T182746 recid=5 stamp=771100106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/usr/local/oracle/oradata/test/users01.dbf
output filename=+DG1/test/datafile/users.261.771100107 tag=TAG20111228T182746 recid=6 stamp=771100109
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DG1/test/controlfile/backup.262.771100111 tag=TAG20111228T182746 recid=7 stamp=771100112
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 28-DEC-11
channel ORA_DISK_1: finished piece 1 at 28-DEC-11
piece handle=+DG1/test/backupset/2011_12_28/nnsnf0_tag20111228t182746_0.263.771100115 tag=TAG20111228T182746 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-DEC-11
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/test/datafile/system.257.771100067"
datafile 2 switched to datafile copy "+DG1/test/datafile/undotbs1.260.771100105"
datafile 3 switched to datafile copy "+DG1/test/datafile/sysaux.258.771100083"
datafile 4 switched to datafile copy "+DG1/test/datafile/users.261.771100107"
datafile 5 switched to datafile copy "+DG1/test/datafile/example.259.771100097"
RMAN> sql 'alter database open';

6.迁移redo文件到ASM
[oracle@test dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 28 18:29:01 2011
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> alter database add logfile member '+dg1' to group 1;
Database altered.
SQL> alter database add logfile member '+dg1' to group 2;
Database altered.
SQL> alter database add logfile member '+dg1' to group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/usr/local/oracle/oradata/test/redo01.log';
Database altered.
SQL> alter database drop logfile member '/usr/local/oracle/oradata/test/redo02.log';
Database altered.
SQL> alter database drop logfile member '/usr/local/oracle/oradata/test/redo03.log';
alter database drop logfile member '/usr/local/oracle/oradata/test/redo03.log'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '/usr/local/oracle/oradata/test/redo03.log'
ORA-00312: online log 3 thread 1: '+DG1/test/onlinelog/group_3.266.771100165'

SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member '/usr/local/oracle/oradata/test/redo03.log';
Database altered.
SQL>

7.迁移临时文件到ASM
SQL> create temporary tablespace temp2 tempfile '+dg1';
Tablespace created.
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> drop tablespace temp;
Tablespace dropped.
SQL>
8.迁移归档日志到ASM
SQL> alter system set log_archive_dest_1='LOCATION=+dg1';
System altered.
SQL>
9.迁移完毕
SQL> l
1* select file_name,file_id from dba_data_files
SQL> col file_name format a60
SQL> /
FILE_NAME FILE_ID
------------------------------------------------------------ ----------
+DG1/test/datafile/users.261.771100107 4
+DG1/test/datafile/sysaux.258.771100083 3
+DG1/test/datafile/undotbs1.260.771100105 2
+DG1/test/datafile/system.257.771100067 1
+DG1/test/datafile/example.259.771100097 5
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------------------------------------
1 +DG1/test/onlinelog/group_1.264.771100155
2 +DG1/test/onlinelog/group_2.265.771100161
3 +DG1/test/onlinelog/group_3.266.771100165
SQL>
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ------------------------------------------------------------
2 +DG1/test/tempfile/temp2.267.771100295

QL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_1 string LOCATION=+dg1
log_archive_dest_10 string
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DG1
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>

SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG1/test/controlfile/backup.2
56.771099991
SQL>

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-01-09

  • 博文量
    6
  • 访问量
    28770