记录一次现场“数据库迁移”的笔记

王朝other·作者佚名 2008-05-19

宽屏版字体: 小|中|大|超大

刚开始出现了些问题，搞得我既郁闷又着急。都不知道我在记什么了，大家将就着看吧!

一个10G数据库的真实迁移过程(也可以说是一个数据库系统崩溃下的恢复过程)，要求停机的时间最短

前提:拥有完整的数据库备份和归档日志，迁移的主机所有的目录结构和原主机一样。

Oracle版本: 10.1.0.3.0

操作系统: linux2.6.11

备份程序:rman，没用恢复目录

把所有rman的备份(包括控制文件的自动备份)拷贝到新的服务器上，路径要和原来的一模一样。

迁移过程如下:

1、把参数文件，tns等文件拷贝到新服务器上，原数据库停机，开始把数据库迁移到新的服务器上。

$set oracle_sid=service

SQL conn / as sysdba

Connected.

SQLstartup nomount;

2、--恢复控制文件

SQL declare

devtype varchar2(256);

done boolean;

begin

devtype :=dbms_backup_restore.deviceallocate(NULL);

dbms_backup_restore.restoresetdatafile;

dbms_backup_restore.restorecontrolfileto('/opt/data/SERVICE/control01.ctl'); dbms_backup_restore.restorebackuppiece('/opt/backup/control_c-4194659530-20050805-01',DONE = done);

end;

PL/SQL procedure successfully completed.

--这个过程我到现在还解，应该是只要控制文件自动备份，通过restore controlfile from autobackup就能恢复过来，但我在现场怎么实现不了。没办法，只要用dbms_backup_restore包了。

3、--把控制文件复制成三个文件

$ cp control01.ctl control02.ctl

$ cp control01.ctl control03.ctl

SQLalter database mount --数据库启动到mount状态准备进行数据文件的恢复

4、--启动rman

Recovery Manager: Release 10.1.0.3.0 - Production

RMAN connect target /

connected to target database: SERVICE (DBID=667643333)

RMAN restore database ;

Starting restore at 05-AUG-05

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

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

restoring datafile 00001 to /opt/data/SERVICE/system01.dbf

restoring datafile 00002 to /opt/data/SERVICE/undotbs01.dbf

restoring datafile 00003 to /opt/data/SERVICE/sysaux01.dbf

restoring datafile 00004 to /opt/data/SERVICE/users01.dbf

restoring datafile 00005 to /opt/data/SERVICE/business01.dbf

restoring datafile 00006 to /opt/data/SERVICE/advertise.dbf

restoring datafile 00007 to /opt/data/SERVICE/backup.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/service_mpgr881f_1_1_565452847 tag=TAG20050804T141407

channel ORA_DISK_1: restore complete

Finished restore at 05-AUG-05

5、--查看归档日志

RMAN list backup archivelog all;

6、进行数据库的恢复

RMAN recover database;

Starting recover at 05-AUG-05

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15553

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/service_mqgr88jj_1_1_565453427 tag=TAG20050804T142347

channel ORA_DISK_1: restore complete

archive log filename=/opt/archive/SERVICE1_15553_552391870.dbf thread=1 sequence=15553

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15554

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15555

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/service_msgr88om_1_1_565453590 tag=TAG20050804T142629

channel ORA_DISK_1: restore complete

archive log filename=/opt/archive/SERVICE1_15554_552391870.dbf thread=1 sequence=15554

archive log filename=/opt/archive/SERVICE1_15555_552391870.dbf thread=1 sequence=15555

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15556

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15557

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15558

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15559

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15560

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15561

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15562

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15563

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15564

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15565

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15566

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15567

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/service_mvgraf8g_1_1_565525776 tag=TAG20050805T102935

channel ORA_DISK_1: restore complete

archive log filename=/opt/archive/SERVICE1_15556_552391870.dbf thread=1 sequence=15556

archive log filename=/opt/archive/SERVICE1_15557_552391870.dbf thread=1 sequence=15557

archive log filename=/opt/archive/SERVICE1_15558_552391870.dbf thread=1 sequence=15558

archive log filename=/opt/archive/SERVICE1_15559_552391870.dbf thread=1 sequence=15559

archive log filename=/opt/archive/SERVICE1_15560_552391870.dbf thread=1 sequence=15560

archive log filename=/opt/archive/SERVICE1_15561_552391870.dbf thread=1 sequence=15561

archive log filename=/opt/archive/SERVICE1_15562_552391870.dbf thread=1 sequence=15562

archive log filename=/opt/archive/SERVICE1_15563_552391870.dbf thread=1 sequence=15563

archive log filename=/opt/archive/SERVICE1_15564_552391870.dbf thread=1 sequence=15564

archive log filename=/opt/archive/SERVICE1_15565_552391870.dbf thread=1 sequence=15565

archive log filename=/opt/archive/SERVICE1_15566_552391870.dbf thread=1 sequence=15566

archive log filename=/opt/archive/SERVICE1_15567_552391870.dbf thread=1 sequence=15567

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15568

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15569

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=15570

channel ORA_DISK_1: restoring archive log

archive log