oracle 数据存储迁移,某单位Oracle数据库系统存储迁移方案

最新推荐文章于 2022-02-13 12:01:45 发布

不乱说话

最新推荐文章于 2022-02-13 12:01:45 发布

阅读量345

点赞数

文章标签： oracle 数据存储迁移

第一次写博客，主要是想留点东西一方面可以自己回顾

，另一方面可以分享给正在学习oracle的朋友一起共同进步。一下是我摘自自己做的一个实施方案，我直接从做出来的word文档方案中复制粘贴出来的。

数

据

库

迁

移

计

划

书

概述篇

硬件环境：SunV890,EMC存储,华为存储

软件环境：solaris10,oracle10g

IP地址：192.168.22.40

问题描述：由于资源整理，欲将SunV890使用的EMC存储迁移到华为存储上，经过一番考查发现EMC存储只创建了名为oradatavg的磁盘组用来存放数据库的文件。注：oracle数据库为非归档模式。

解决方案：使用华为存储创建磁盘组，然后将这些文件迁移到新建的存储上。但由于数据库使用的是裸设备，所以通过数据库层面进行RMAN迁移更方便可靠。

测试篇

一、查看当前数据库状态

1、

查看实例状态

SQL> select status from v$instance;

STATUS

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

OPEN

2、

查看参数文件

SQL> show parameter pfile;

NAME TYPE VALUE

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

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

spfile string /app/oracle/product/11.2.0.3/d

bhome_1/dbs/spfilemigrate.ora

3、

查看数据文件

SQL> select name from v$datafile;

NAME

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

/app/oracle/oradata/migrate/migrate/system01.dbf

/app/oracle/oradata/migrate/migrate/sysaux01.dbf

/app/oracle/oradata/migrate/migrate/undotbs01.dbf

/app/oracle/oradata/migrate/migrate/users01.dbf

4、

将数据库启动到mount状态

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 242208768

bytes

Fixed

Size 2227176 bytes

Variable

Size 184550424 bytes

Database

Buffers 50331648 bytes

Redo

Buffers 5099520 bytes

Database mounted.

二、全备数据库

RMAN> backup database include current controlfile format

'/app/full_%d_%T_%s.bak';

Starting backup at 19-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001

name=/app/oracle/oradata/migrate/migrate/system01.dbf

input datafile file number=00002

name=/app/oracle/oradata/migrate/migrate/sysaux01.dbf

input datafile file number=00003

name=/app/oracle/oradata/migrate/migrate/undotbs01.dbf

input datafile file number=00004

name=/app/oracle/oradata/migrate/migrate/users01.dbf

channel ORA_DISK_1: starting piece 1 at 19-FEB-14

channel ORA_DISK_1: finished piece 1 at 19-FEB-14

piece handle=/app/full_MIGRATE_20140219_2.bak

tag=TAG20140219T150222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time:

00:07:40

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 19-FEB-14

channel ORA_DISK_1: finished piece 1 at 19-FEB-14

piece handle=/app/full_MIGRATE_20140219_3.bak

tag=TAG20140219T150222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time:

00:00:01

Finished backup at 19-FEB-14

三、迁移spfile文件

1、从spfile创建pfile

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

2、修改pfile中控制文件的新路径

3、使用该pfile文件nomount数据库并创建新的spfile

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area 242208768

bytes

Fixed

Size 2227176 bytes

Variable

Size 184550424 bytes

Database

Buffers 50331648 bytes

Redo

Buffers 5099520 bytes

SQL> create spfile='/data/oradata/migrate/spfilemigrate.ora'

from pfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

4、在initmigrate.ora文件中指定spfile文件

vi $ORACLE_HOME/dbs/initmigrate.ora

spfile=’/data/oradata/migrate/spfilemigrate.ora'

5、nomount数据库

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 242208768

bytes

Fixed

Size 2227176 bytes

Variable

Size 184550424 bytes

Database

Buffers 50331648 bytes

Redo

Buffers 5099520 bytes

SQL> show parameter pfile;

NAME TYPE VALUE

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

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

spfile string /data/oradata/migrate/spfilemi

grate.ora

四、

迁移pwdfile文件

[oracle@test dbs]$ orapwd file=/data/oradata/migrate/orapwmigrate

password=oracle entries=5

五、迁移controlfile文件

RMAN> restore controlfile from

'/app/full_MIGRATE_20140219_3.bak';

六、迁移数据文件

1、mount数据库

SQL> alter database mount;

Database altered.

2、转储数据文件

run{

set newname for datafile 1 to

'/data/oradata/migrate/system01.dbf';

set newname for datafile 2 to

'/data/oradata/migrate/sysaux01.dbf';

set newname for datafile 3 to

'/data/oradata/migrate/undotbs01.dbf';

set newname for datafile 4 to

'/data/oradata/migrate/users01.dbf';

restore database;

switch datafile all;

}

3、resetlogs打开数据库

SQL> alter database open resetlogs;

Database altered.

4、查看数据文件

SQL> select name from v$datafile;

NAME

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

/data/oradata/migrate/system01.dbf

/data/oradata/migrate/sysaux01.dbf

/data/oradata/migrate/undotbs01.dbf

/data/oradata/migrate/users01.dbf

七、迁移日志文件

1、查看日志文件

SQL> select member from v$logfile;

MEMBER

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

/app/oracle/oradata/migrate/migrate/redo03.log

/app/oracle/oradata/migrate/migrate/redo02.log

/app/oracle/oradata/migrate/migrate/redo01.log

2、添加日志组成员

SQL> alter database add logfile member

'/data/oradata/migrate/redo01.log' to group 1;

Database altered.

SQL> alter database add logfile member

'/data/oradata/migrate/redo02.log' to group 2;

Database altered.

SQL> alter database add logfile member

'/data/oradata/migrate/redo03.log' to group 3;

Database altered.

3、查看日志状态

SQL> set linesize 200;

SQL> col member for a50;

SQL> select * from v$logfile;

GROUP#

STATUS TYPE MEMBER IS_

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

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

3 ONLINE /app/oracle/oradata/migrate/migrate/redo03.log NO

2 ONLINE /app/oracle/oradata/migrate/migrate/redo02.log NO

1 ONLINE /app/oracle/oradata/migrate/migrate/redo01.log NO

1 INVALID ONLINE /data/oradata/migrate/redo01.log NO

2 INVALID ONLINE /data/oradata/migrate/redo02.log NO

3 INVALID ONLINE /data/oradata/migrate/redo03.log NO

6 rows selected.

4、切换日志组同步新添加的日志组数据

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

5、验证日志状态

SQL> select * from v$logfile;

GROUP#

STATUS TYPE MEMBER IS_

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

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

3 ONLINE /app/oracle/oradata/migrate/migrate/redo03.log NO

2 ONLINE /app/oracle/oradata/migrate/migrate/redo02.log NO

1 ONLINE /app/oracle/oradata/migrate/migrate/redo01.log NO

1 ONLINE /data/oradata/migrate/redo01.log NO

2 ONLINE /data/oradata/migrate/redo02.log NO

3 ONLINE /data/oradata/migrate/redo03.log NO

rows selected.

6、查看日志组状态

SQL> select * from

v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

STATUS

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

--- --------

1 1 4 52428800 512 2 NO CURRENT

2 1 2 52428800 512 2 NO INACTIVE

1 3 52428800 512 2 NO INACTIVE

7、删除以前的redo日志

SQL> alter database drop logfile member

'/app/oracle/oradata/migrate/migrate/redo02.log';

Database altered.

SQL> alter database drop logfile member

'/app/oracle/oradata/migrate/migrate/redo03.log';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile member

'/app/oracle/oradata/migrate/migrate/redo01.log';

Database altered.

8、验证日志文件

SQL> select member from v$logfile;

MEMBER

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

/data/oradata/migrate/redo01.log

/data/oradata/migrate/redo02.log

/data/oradata/migrate/redo03.log

八、迁移temp 文件

1、查看temp文件

SQL> select name ,bytes/1024/1024 from v$tempfile;

NAME BYTES/1024/1024

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

/app/oracle/oradata/migrate/migrate/temp01.dbf 20

2、temp表空间增加temp文件

SQL> alter tablespace temp add tempfile

'/data/oradata/migrate/temp01.dbf' size 30m;

Tablespace altered.

3、删除以前的temp文件

SQL>alter tablespace temp drop tempfile

'/app/oracle/oradata/migrate/migrate/temp01.dbf';

Tablespace altered.

4、验证temp文件

SQL> select name ,bytes/1024/1024 from v$tempfile;

NAME BYTES/1024/1024

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

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

/data/oradata/migrate/temp01.dbf 30

九、迁移成功后备份当前数据库包括控制文件

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 242208768

bytes

Fixed

Size 2227176 bytes

Variable

Size 184550424 bytes

Database

Buffers 50331648 bytes

Redo

Buffers 5099520 bytes

Database mounted.

SQL>

RMAN> backup database include current controlfile format

'/data/backup_db/full_%d_%T_%s.bak';

Starting backup at 19-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001

name=/data/oradata/migrate/system01.dbf

input datafile file number=00002

name=/data/oradata/migrate/sysaux01.dbf

input datafile file number=00003

name=/data/oradata/migrate/undotbs01.dbf

input datafile file number=00004

name=/data/oradata/migrate/users01.dbf

channel ORA_DISK_1: starting piece 1 at 19-FEB-14

channel ORA_DISK_1: finished piece 1 at 19-FEB-14

piece handle=/data/backup_db/full_MIGRATE_20140219_6.bak

tag=TAG20140219T190600 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time:

00:06:13

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 19-FEB-14

channel ORA_DISK_1: finished piece 1 at 19-FEB-14

piece handle=/data/backup_db/full_MIGRATE_20140219_7.bak

tag=TAG20140219T190600 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time:

00:00:01

Finished backup at 19-FEB-14

实施篇

系统层

1、创建裸设备

vxassist -g datadg make cq3 3000m

vxassist -g datadg make cq2 3000m

vxassist -g datadg make cq1 3000m

vxassist -g datadg make KQMANAGER02 1536m

vxassist -g datadg make KQMANAGER 1024m

vxassist -g datadg make KQSERVICE 8000m

vxassist -g datadg make MAPGIS 11000m

vxassist -g datadg make MAPGIS_INDX 1536m

vxassist -g datadg make MPDBMASTER 512m

vxassist -g datadg make MPDBMASTER_INDX 512m

vxassist -g datadg make oa3 21000m

vxassist -g datadg make oa7 30000m

vxassist -g datadg make oa6 30000m

vxassist -g datadg make oa5 30000m

vxassist -g datadg make oa2 21000m

vxassist -g datadg make oa4 21000m

vxassist -g datadg make oa1 21000m

vxassist -g datadg make oa1010_1 11240m

vxassist -g datadg make oa1010_2 5150m

vxassist -g datadg make KQMANAGER01 1124m

vxassist -g datadg make ONEMAP 1188m

vxassist -g datadg make ONEMAP01 1124m

vxassist -g datadg make sde 450m

vxassist -g datadg make sysaux 2000m

vxassist -g datadg make system 2000m

vxassist -g datadg make undo 10000m

vxassist -g datadg make undo1 10000m

vxassist -g datadg make users 1000m

vxassist -g datadg make temp01 5000m

vxassist -g datadg make temp02 5000m

vxassist -g datadg make redo01A 500m

vxassist -g datadg make redo01B 500m

vxassist -g datadg make redo02A 500m

vxassist -g datadg make redo02B 500m

vxassist -g datadg make redo03A 500m

vxassist -g datadg make redo03B 500m

vxassist -g datadg make redo04A 500m

vxassist -g datadg make redo04B 500m

vxassist -g datadg make redo05A 500m

vxassist -g datadg make redo05B 500m

vxassist -g datadg make redo06A 500m

vxassist -g datadg make redo06B 500m

2、修改裸设备属主和权限

vxedit –g datadg set user=oracle group=oinstall mode=755

一、

查看当前数据库状态

1、查看实例状态

SQL> select status from v$instance;

STATUS

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

OPEN

2、查看参数文件

SQL> show parameter pfile;

NAME TYPE

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

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

VALUE

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

spfile string

/dev/vx/rdsk/oradatavg/spfile

3、查看数据文件

SQL> select name from v$datafile;

NAME

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

/dev/vx/rdsk/oradatavg/system

/dev/vx/rdsk/oradatavg/undo

/dev/vx/rdsk/oradatavg/sysaux

/dev/vx/rdsk/oradatavg/users

/dev/vx/rdsk/oradatavg/oa1

/dev/vx/rdsk/oradatavg/oa2

/dev/vx/rdsk/oradatavg/oa3

/dev/vx/rdsk/oradatavg/oa4

/dev/vx/rdsk/oradatavg/cq1

/dev/vx/rdsk/oradatavg/cq2

/dev/vx/rdsk/oradatavg/undo1

NAME

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

/oracle/app/10.2.0.1/dbs/D:appAdministratorproduct11.2.0dbhome_1sde.dbf

/oracle/app/10.2.0.1/dbs/KQMANAGER.ORA

/oracle/app/10.2.0.1/dbs/KQSERVICE.ORA

/oracle/app/10.2.0.1/dbs/ONEMAP.ORA

/oracle/oradata/oa5

/oracle/oradata/oa1010_1.dbf

/oracle/oradata/oa1010_2.dbf

/oracle/oradata/ONEMAP01.DBF

/oracle/oradata/KQMANAGER01.DBF

/oracle/oradata/oa6.DBF

/oracle/oradata/KQMANAGER02.DBF

NAME

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

/dev/vx/rdsk/oradatavg/oa5

/dev/vx/rdsk/oradatavg/MPDBMASTER

/dev/vx/rdsk/oradatavg/MPDBMASTER_INDX

/dev/vx/rdsk/oradatavg/MAPGIS

/dev/vx/rdsk/oradatavg/MAPGIS_INDX

/dev/vx/rdsk/oradatavg/cq3

28 rows selected.

4、将数据库启动到mount状态

SQL> shutdown immediate;

SQL> startup mount;

二、全备数据库

RMAN>backup database format

'/arch/full_%d_%T_%s.bak';

RMAN>backup current controlfile format

'/arch/control.bak';

三、迁移spfile文件

1、从spfile创建pfile

SQL> create pfile from spfile;

SQL> shutdown immediate;

2、修改pfile中控制文件的新路径

*.control_files='/dev/vx/rdsk/datadg/control01','/dev/vx/rdsk/datadg/control02'

3、使用该pfile文件nomount数据库并创建新的spfile

SQL> startup nomount ;

SQL> create spfile='/dev/vx/rdsk/datadg/spfile' from

pfile;

SQL> shutdown immediate;

4、在initoratest.ora文件中指定spfile文件

vi $ORACLE_HOME/dbs/initoratest.ora

spfile=’/dev/vx/rdsk/datadg/spfile’

5、nomount数据库

SQL> startup nomount;

四、

迁移pwdfile文件

[oracle@test dbs]$ orapwd file=/dev/vx/rdsk/datadg/pwdfile

password=gtjxxzx entries=5

五、迁移controlfile文件

RMAN> restore controlfile from '/arch/control.bak ';

六、迁移数据文件

1、mount数据库

SQL> alter database mount;

Database altered.

2、转储数据文件

run{

set newname for datafile 1 to

'/dev/vx/rdsk/datadg/system';

set newname for datafile 2 to

'/dev/vx/rdsk/datadg/undo';

set newname for datafile 3 to

'/dev/vx/rdsk/datadg/sysaux';

set newname for datafile 4 to

'/dev/vx/rdsk/datadg/users';

set newname for datafile 5 to '/dev/vx/rdsk/datadg/oa1';

set newname for datafile 6 to '/dev/vx/rdsk/datadg/oa2';

set newname for datafile 7 to '/dev/vx/rdsk/datadg/oa3';

set newname for datafile 8 to '/dev/vx/rdsk/datadg/oa4';

set newname for datafile 9 to '/dev/vx/rdsk/datadg/cq1';

set newname for datafile 10 to

'/dev/vx/rdsk/datadg/cq2';

set newname for datafile 11 to

'/dev/vx/rdsk/datadg/undo1';

set newname for datafile 12 to

'/dev/vx/rdsk/datadg/sde';

set newname for datafile 13 to

'/dev/vx/rdsk/datadg/KQMANAGER';

set newname for datafile 14 to

'/dev/vx/rdsk/datadg/KQSERVICE';

set newname for datafile 15 to

'/dev/vx/rdsk/datadg/ONEMAP';

set newname for datafile 16 to

'/dev/vx/rdsk/datadg/oa7';

set newname for datafile 17 to

'/dev/vx/rdsk/datadg/oa1010_1';

set newname for datafile 18 to

'/dev/vx/rdsk/datadg/oa1010_2';

set newname for datafile 19 to

'/dev/vx/rdsk/datadg/ONEMAP01';

set newname for datafile 20 to

'/dev/vx/rdsk/datadg/KQMANAGER01';

set newname for datafile 21 to

'/dev/vx/rdsk/datadg/oa6';

set newname for datafile 22 to

'/dev/vx/rdsk/datadg/KQMANAGER02';

set newname for datafile 23 to

'/dev/vx/rdsk/datadg/oa5';

set newname for datafile 24 to

'/dev/vx/rdsk/datadg/MPDBMASTER';

set newname for datafile 25 to

'/dev/vx/rdsk/datadg/MPDBMASTER_INDX';

set newname for datafile 26 to

'/dev/vx/rdsk/datadg/MAPGIS';

set newname for datafile 27 to

'/dev/vx/rdsk/datadg/MAPGIS_INDX';

set newname for datafile 28 to

'/dev/vx/rdsk/datadg/cq3';

restore database force;

switch datafile all;

}

注：restore database

如果不加force参数，restore会hang住不动，在alert日志中会频繁报如下错误：

Reread of blocknum=1, file=/dev/vx/rdsk/datadg/oa1. found same

corrupt data

Trying mirror side oa1-01.

据oracle技术支持的解释为裸设备头部为空导致的数据文件验证失败，使用force跳过验证。

3、resetlogs打开数据库

SQL> alter database open resetlogs;

Database altered.

4、查看数据文件

SQL> select name from v$datafile;

七、迁移日志文件

1、查看日志文件

SQL> select member from v$logfile;

MEMBER

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

/dev/vx/rdsk/oradatavg/redo2A

/dev/vx/rdsk/oradatavg/redo1A

/dev/vx/rdsk/oradatavg/redo3A

/dev/vx/rdsk/oradatavg/redo3B

/dev/vx/rdsk/oradatavg/redo4A

/dev/vx/rdsk/oradatavg/redo4B

/dev/vx/rdsk/oradatavg/redo5A

/dev/vx/rdsk/oradatavg/redo5B

/dev/vx/rdsk/oradatavg/redo6A

/dev/vx/rdsk/oradatavg/redo6B

/dev/vx/rdsk/oradatavg/redo2B

MEMBER

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

/dev/vx/rdsk/oradatavg/redo1B

12 rows selected.

2、添加日志组成员

SQL>alter database add logfile member

'/dev/vx/rdsk/datadg/redo01A.log' to group 1;

SQL>alter database add logfile member

'/dev/vx/rdsk/datadg/redo02A.log ' to group 2;

SQL>alter database add logfile member

'/dev/vx/rdsk/datadg/redo03A.log ' to group 3;

SQL> alter database add logfile member

'/dev/vx/rdsk/datadg/redo04A.log ' to group 4;

SQL> alter database add logfile member

'/dev/vx/rdsk/datadg/redo05A.log ' to group 5;

SQL> alter database add logfile member

'/dev/vx/rdsk/datadg/redo06A.log ' to group 6;

3、查看日志状态

SQL> set linesize 200;

SQL> col member for a50;

SQL> select * from v$logfile;

输出结果应该是新添加的日志STATUS为INVALID，因为日志文件添加后还未使用，切换日志同步一下即可。

4、切换日志组同步新添加的日志组数据

SQL> alter system switch logfile;

SQL> /

5、验证日志状态

SQL> select * from v$logfile;

经过日志切换之后，日志文件STATUS状态为空，TYPE状态均为ONLINE。

6、查看日志组状态

SQL> select * from v$log;

7、删除以前的redo日志