现场oralce9i迁移过程

Oracle9i 数据库迁移

 

背景：用户现在所有的数据文件都存在一台P570上的本地磁盘上，是文件方式存在的，由于现在P570现在新接了一台磁盘阵列DS4800做为存储，现在要把

本地磁盘的数据文件迁移到磁盘阵列上，并且以裸设备方式存在；磁盘阵列上的裸设备是veritas的卷。 

           

一：查看现有的数据库的数据文件的大小和数目，然后使用veritas命令建立与

之对于的裸设备，本例中：

原来数据文件的位置	新的数据文件位置	备注
/oracle/app/ora9i/oradata/eas/system01.dbf	/dev/vx/rdsk/oradg/system01
/oracle/app/ora9i/oradata/eas/undotbs01.dbf	/dev/vx/rdsk/oradg/undo01
/oracle/app/ora9i/oradata/eas/cwmlite01.dbf	/dev/vx/rdsk/oradg/cwmlite01
/oracle/app/ora9i/oradata/eas/drsys01.dbf	/dev/vx/rdsk/oradg/drsys01
/oracle/app/ora9i/oradata/eas/indx01.dbf	/dev/vx/rdsk/oradg/index01
/oracle/app/ora9i/oradata/eas/odm01.dbf	/dev/vx/rdsk/oradg/odm01
/oracle/app/ora9i/oradata/eas/tools01.dbf	/dev/vx/rdsk/oradg/tools01
/oracle/app/ora9i/oradata/eas/users01.dbf	/dev/vx/rdsk/oradg/user01
/oracle/app/ora9i/oradata/eas/xdb01.dbf	/dev/vx/rdsk/oradg/xdb01
/oracle/app/ora9i/oradata/eas/users02.dbf	/dev/vx/rdsk/oradg/user02

 

在建裸设备时，裸设备的大小要大于文件的大小，下面以是建设备system01的命令：

 

vxassist –g oradg make system01 2G       #在卷组上创建2G大小的裸设备system01

vxedit –g oradg set user=oracle group=dba mode=660 system01  #修改卷的属性

 

其他的设备都用同样的方式建。

 

二：用rman执行全库备份

 

$ rman target /

 

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

 

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

 

connected to target database: EAS (DBID=1507919843)

RMAN> run {

2> allocate channel d1 type disk;

3> backup full format '/oracle/rmanbk/full_%d2' database diskratio=0

4> include current controlfile;

5> sql 'alter system archive log current';

6> backup archivelog all;

7>  release channel d1;

8> }

 

Using target database controlfile instead of recovery catalog

Allocated channel: d1

Channel d1: sid=18 devtype=DISK

 

Starting backup at 19-OCT-07

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

including current controlfile in backupset

input datafile fno=00008 name=/oracle/app/ora9i/oradata/eas/users01.dbf

input datafile fno=00010 name=/oracle/app/ora9i/oradata/eas/users02.dbf

input datafile fno=00005 name=/oracle/app/ora9i/oradata/eas/indx01.dbf

input datafile fno=00001 name=/oracle/app/ora9i/oradata/eas/system01.dbf

input datafile fno=00002 name=/oracle/app/ora9i/oradata/eas/undotbs01.dbf

input datafile fno=00009 name=/oracle/app/ora9i/oradata/eas/xdb01.dbf

input datafile fno=00003 name=/oracle/app/ora9i/oradata/eas/cwmlite01.dbf

input datafile fno=00004 name=/oracle/app/ora9i/oradata/eas/drsys01.dbf

input datafile fno=00006 name=/oracle/app/ora9i/oradata/eas/odm01.dbf

input datafile fno=00007 name=/oracle/app/ora9i/oradata/eas/tools01.dbf

channel d1: starting piece 1 at 19-OCT-07

channel d1: finished piece 1 at 19-OCT-07

piece handle=/oracle/rmanbk/full_EAS1.bk comment=NONE

channel d1: backup set complete, elapsed time: 00:05:46

finished backup at 19-OCT-07

 

starting Control File Autobackup at 19-OCT-07

piece handle=/oracle/app/ora9i/product/9.2.0/dbs/c-1507919843-20071019-00

=NONE

finished Control File Autobackup at 19-OCT-07

 

sql statement: alter system archive log current

 

starting backup at 19-OCT-07

current log archived

skipping archive log file /oracle/app/ora9i/admin/eas/arch/T0001S000000012

already backed up 1 time(s)

skipping archive log file /oracle/app/ora9i/admin/eas/arch/T0001S000000012

already backed up 1 time(s)

skipping archive log file /oracle/app/ora9i/admin/eas/arch/T0001S000000012

already backed up 1 time(s)

channel d1: starting archive log backupset

channel d1: specifying archive log(s) in backup set

input archive log thread=1 sequence=127 recid=4 stamp=636410032

input archive log thread=1 sequence=128 recid=5 stamp=636410032

channel d1: starting piece 1 at 19-OCT-07

channel d1: finished piece 1 at 19-OCT-07

piece handle=/oracle/app/ora9i/product/9.2.0/dbs/08iutm5h_1_1 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:02

finished backup at 19-OCT-07

 

starting Control File Autobackup at 19-OCT-07

piece handle=/oracle/app/ora9i/product/9.2.0/dbs/c-1507919843-20071019-01

=NONE

finished Control File Autobackup at 19-OCT-07

 

released channel: d1

 

RMAN> exit

 

 

Recovery Manager complete.                    

 

三：恢复控制文件

修改init.ora文件把控制文件指向新的位置,

启动数据库到nomount状态

$ rman target /

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

 

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

 

connected to target database (not started)

 

RMAN> set DBID=1507919843;

 

executing command: SET DBID

 

RMAN> startup nomount

 

Oracle instance started

 

Total System Global Area    4380922872 bytes

 

Fixed Size                      746488 bytes

Variable Size               2231369728 bytes

Database Buffers            2147483648 bytes

Redo Buffers                   1323008 bytes

 

RMAN> run{

allocate channel d1 type disk;

restore controlfile to '/dev/vx/rdsk/oradg/control03' from '08iutm5h_1_1';

restore controlfile to '/dev/vx/rdsk/oradg/control02' from '08iutm5h_1_1';

restore controlfile to '/dev/vx/rdsk/oradg/control03' from '08iutm5h_1_1';

release channel d1;

}

 

做这一步的时候不知道那里出现了问题，导致了控制文件没有恢复成功，select * from v$controlfile;的结果是空的。那么通过手工恢复控制文件—（同时也重建了日志文件，日志文件使用裸设备需要先创建）（在恢复完数据文件后做的）：

$ sqlplus '/as sysdba'

 

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 19 21:13:30 2007

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

 

SQL> CREATE CONTROLFILE REUSE DATABASE "EAS" RESETLOGS  ARCHIVELOG

  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE

  3      MAXLOGFILES 10

  4      MAXLOGMEMBERS 3

  5      MAXDATAFILES 250

  6      MAXINSTANCES 1

  7      MAXLOGHISTORY 226

  8  LOGFILE

  9    GROUP 1 ('/dev/vx/rdsk/oradg/redo01','/dev/vx/rdsk/oradg/redo11') SIZE 100M,

 10    GROUP 2 ('/dev/vx/rdsk/oradg/redo02','/dev/vx/rdsk/oradg/redo22') SIZE 100M,

 11    GROUP 3 ('/dev/vx/rdsk/oradg/redo03','/dev/vx/rdsk/oradg/redo33') SIZE 100M

 12  -- STANDBY LOGFILE

 13  DATAFILE

 14    '/dev/vx/rdsk/oradg/system01',

 15    '/dev/vx/rdsk/oradg/undo01',

 16    '/dev/vx/rdsk/oradg/cwmlite01',

 17    '/dev/vx/rdsk/oradg/drsys01',

 18    '/dev/vx/rdsk/oradg/index01',

 19    '/dev/vx/rdsk/oradg/odm01',

 20    '/dev/vx/rdsk/oradg/tools01',

 21    '/dev/vx/rdsk/oradg/user01',

 22    '/dev/vx/rdsk/oradg/xdb01',

 23    '/dev/vx/rdsk/oradg/user02'

 24  CHARACTER SET ZHS16GBK

 25  ;

 

Control file created.

 

SQL> select * from v$controlfile;

 

STATUS

-------

NAME

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

/dev/vx/rdsk/oradg/control01

/dev/vx/rdsk/oradg/control02

/dev/vx/rdsk/oradg/control03

 

四：恢复数据文件：

RMAN> run {

2>  set newname for datafile '/oracle/app/ora9i/oradata/eas/system01.dbf' to '/dev/vx/rdsk/oradg/system01';

3> set newname for datafile '/oracle/app/ora9i/oradata/eas/undotbs01.dbf' to '/dev/vx/rdsk/oradg/undo01';

4> set newname for datafile '/oracle/app/ora9i/oradata/eas/cwmlite01.dbf' to '/dev/vx/rdsk/oradg/cwmlite01';

5> set newname for datafile '/oracle/app/ora9i/oradata/eas/drsys01.dbf' to '/dev/vx/rdsk/oradg/drsys01';

6>  set newname for datafile '/oracle/app/ora9i/oradata/eas/indx01.dbf' to '/dev/vx/rdsk/oradg/index01';

7>  set newname for datafile '/oracle/app/ora9i/oradata/eas/odm01.dbf' to '/dev/vx/rdsk/oradg/odm01';

8> set newname for datafile '/oracle/app/ora9i/oradata/eas/tools01.dbf' to '/dev/vx/rdsk/oradg/tools01';

9> set newname for datafile '/oracle/app/ora9i/oradata/eas/users01.dbf' to '/dev/vx/rdsk/oradg/user01';

10> set newname for datafile '/oracle/app/ora9i/oradata/eas/xdb01.dbf' to '/dev/vx/rdsk/oradg/xdb01';

11> set newname for datafile '/oracle/app/ora9i/oradata/eas/users02.dbf' to '/dev/vx/rdsk/oradg/user02';

12>  restore database;

13>  switch datafile all;

14> }

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 19-OCT-07

 

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /dev/vx/rdsk/oradg/system01

restoring datafile 00002 to /dev/vx/rdsk/oradg/undo01

restoring datafile 00003 to /dev/vx/rdsk/oradg/cwmlite01

restoring datafile 00004 to /dev/vx/rdsk/oradg/drsys01

restoring datafile 00005 to /dev/vx/rdsk/oradg/index01

restoring datafile 00006 to /dev/vx/rdsk/oradg/odm01

restoring datafile 00007 to /dev/vx/rdsk/oradg/tools01

restoring datafile 00008 to /dev/vx/rdsk/oradg/user01

restoring datafile 00009 to /dev/vx/rdsk/oradg/xdb01

restoring datafile 00010 to /dev/vx/rdsk/oradg/user02

channel ORA_DISK_1: restored backup piece 1

piece handle=/oracle/rmanbk/full_EAS1.bk tag=TAG20071019T202804 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 19-OCT-07

 

datafile 1 switched to datafile copy

input datafilecopy recid=30 stamp=636411895 filename=/dev/vx/rdsk/oradg/system01

datafile 2 switched to datafile copy

input datafilecopy recid=31 stamp=636411895 filename=/dev/vx/rdsk/oradg/undo01

datafile 3 switched to datafile copy

input datafilecopy recid=32 stamp=636411895 filename=/dev/vx/rdsk/oradg/cwmlite01

datafile 4 switched to datafile copy

input datafilecopy recid=33 stamp=636411895 filename=/dev/vx/rdsk/oradg/drsys01

datafile 5 switched to datafile copy

input datafilecopy recid=34 stamp=636411895 filename=/dev/vx/rdsk/oradg/index01

datafile 6 switched to datafile copy

input datafilecopy recid=35 stamp=636411895 filename=/dev/vx/rdsk/oradg/odm01

datafile 7 switched to datafile copy

input datafilecopy recid=36 stamp=636411895 filename=/dev/vx/rdsk/oradg/tools01

datafile 8 switched to datafile copy

input datafilecopy recid=37 stamp=636411895 filename=/dev/vx/rdsk/oradg/user01

datafile 9 switched to datafile copy

input datafilecopy recid=38 stamp=636411895 filename=/dev/vx/rdsk/oradg/xdb01

datafile 10 switched to datafile copy

input datafilecopy recid=39 stamp=636411895 filename=/dev/vx/rdsk/oradg/user02

 

RMAN> exit

 

 

Recovery Manager complete.

 

五：打开数据库，建立（更改）临时表空间

SQL> alter database open resetlogs;

Database altered.

 

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/dev/vx/rdsk/oradg/temp01' autoextend off;

 

Tablespace altered.         

 

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

 

     FILE# NAME                   BYTES/1024/1024  

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

 

      1 /dev/vx/rdsk/oradg/temp01     4999.98438

SQL>

 

SQL> select file_name from dba_data_files;

 

FILE_NAME

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

/dev/vx/rdsk/oradg/system01

/dev/vx/rdsk/oradg/undo01

/dev/vx/rdsk/oradg/cwmlite01

/dev/vx/rdsk/oradg/drsys01

/dev/vx/rdsk/oradg/index01

/dev/vx/rdsk/oradg/odm01

/dev/vx/rdsk/oradg/tools01

/dev/vx/rdsk/oradg/user01

/dev/vx/rdsk/oradg/xdb01

/dev/vx/rdsk/oradg/user02

 

10 rows selected.

六．创建spfile文件：

SQL> create spfile='/dev/vx/rdsk/oradg/spfile' from pfile;

File created.