Oracle数据库迁移实践-优快云博客

本文链接：https://blog.youkuaiyun.com/wshxgxiaoli/article/details/9627131

1. 环境：
原机： 192.168.102.156
目标机： 192.168.102.157

2. 需求：只有数据文件的备份和归档日志的备份，无控制文件备份，并将备份恢复到 /home/oracle/oradata/datafile下。

3. 参数文件准备
3.1 在原库生成PFILE文件。

SQL> create pfile='/home/oracle/initgg1.ora' from spfile;

File created.

3.2 奖参数文件移至目标库。

[oracle@GOLDENGATE1 ~]$ scp initgg1.ora oracle@192.168.102.157:/home/oracle
oracle@192.168.102.157's password: 
initgg1.ora                                                                                                100% 1051     1.0KB/s   00:00

4. 将目标库启动至NOMOUN状态。
4.1 修改参数文件：

*.control_files='/home/oracle/oradata/datafile/control01.ctl','/home/oracle/oradata/datafile/control02.ctl','/home/oracle/oradata/datafile/control03.ctl'
*.core_dump_dest='/home/oracle/admin/gg1/cdump'

4.2 创建PFILE所需要的目录。

[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/adump
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/cdump
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/bdump
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/udump
[oracle@GOLDENGATE2 ~]$ mkdir oradata/datafile

4.3 将目标库启动到NOMOUNT状态。

[oracle@GOLDENGATE2 ~]$ export ORACLE_SID=gg1
[oracle@GOLDENGATE2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 21 03:48:06 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initgg1.ora';
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

5. 备份文件准备。
5.1 备份原库：

RMAN> backup format='/home/oracle/rman/full_%d_%T_%s' database plus archivelog;


Starting backup at 21-SEP-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=1 stamp=794527523
input archive log thread=1 sequence=5 recid=2 stamp=794538401
input archive log thread=1 sequence=6 recid=3 stamp=794539337
input archive log thread=1 sequence=7 recid=4 stamp=794539385
input archive log thread=1 sequence=8 recid=5 stamp=794539992
input archive log thread=1 sequence=9 recid=6 stamp=794542152
input archive log thread=1 sequence=10 recid=7 stamp=794542215
input archive log thread=1 sequence=11 recid=8 stamp=794549746
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_21 tag=TAG20120921T041546 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 21-SEP-12

Starting backup at 21-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/home/oracle/oradata/gg1/gg01.dbf
input datafile fno=00001 name=/home/oracle/oradata/gg1/system01.dbf
input datafile fno=00003 name=/home/oracle/oradata/gg1/sysaux01.dbf
input datafile fno=00005 name=/home/oracle/oradata/gg1/example01.dbf
input datafile fno=00002 name=/home/oracle/oradata/gg1/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oradata/gg1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_22 tag=TAG20120921T041551 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_23 tag=TAG20120921T041551 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-SEP-12

Starting backup at 21-SEP-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=12 recid=9 stamp=794549809
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_24 tag=TAG20120921T041649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 21-SEP-12

从上面的内容可以看出这里的控制文件已经做了备份， ORACLE 从10G以后就自动备份控制文件和SPFILE文件。这里我们还是继续往下做。

5.2 将备份文件移至目标机：

[oracle@GOLDENGATE1 rman]$ scp * 192.168.102.157:/home/oracle/rman
oracle@192.168.102.157's password: 
full_GG1_20120921_21                                                               100%   48MB  11.9MB/s   00:04    
full_GG1_20120921_22                                                               100%  595MB  11.0MB/s   00:54    
full_GG1_20120921_23                                                               100% 6976KB   6.8MB/s   00:01    
full_GG1_20120921_24                                                               100%   12KB  12.0KB/s   00:00    
[oracle@GOLDENGATE1 rman]$

6. 定义数据文件恢复过程

SQL> declare
        devtype varchar2(256);
        done boolean;
        begin
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1');
        sys.dbms_backup_restore.restoreSetDatafile;
        sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/home/oracle/oradata/datafile/system01.dbf');
        sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/home/oracle/oradata/datafile/undotbs01.dbf');
        sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/home/oracle/oradata/datafile/sysaux01.dbf');
        sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/home/oracle/oradata/datafile/users01.dbf');
        sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/home/oracle/oradata/datafile/example01.dbf');
        sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'/home/oracle/oradata/datafile/gg01.dbf');
        sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/home/oracle/rman/full_GG1_20120921_22',params=>null);
        sys.dbms_backup_restore.deviceDeallocate;
end;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  /

PL/SQL procedure successfully completed.

查看ALERT日志：

Full restore complete of datafile 4 to datafile copy /home/oracle/oradata/datafile/users01.dbf.  Elapsed time: 0:00:01 
  checkpoint is 537114
Full restore complete of datafile 2 to datafile copy /home/oracle/oradata/datafile/undotbs01.dbf.  Elapsed time: 0:00:05 
  checkpoint is 537114
Fri Sep 21 04:41:43 2012
Full restore complete of datafile 5 to datafile copy /home/oracle/oradata/datafile/example01.dbf.  Elapsed time: 0:00:10 
  checkpoint is 537114
Full restore complete of datafile 3 to datafile copy /home/oracle/oradata/datafile/sysaux01.dbf.  Elapsed time: 0:00:18 
  checkpoint is 537114
Fri Sep 21 04:41:58 2012
Full restore complete of datafile 1 to datafile copy /home/oracle/oradata/datafile/system01.dbf.  Elapsed time: 0:00:26 
  checkpoint is 537114
Fri Sep 21 04:42:09 2012
Full restore complete of datafile 6 to datafile copy /home/oracle/oradata/datafile/gg01.dbf.  Elapsed time: 0:00:35 
  checkpoint is 537114

7.恢复归档日志：

SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'/home/oracle/archivelog');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>11);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/home/oracle/rman/full_GG1_20120921_21',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/  2    3    4    5    6    7    8    9   10   11  

PL/SQL procedure successfully completed.


SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'/home/oracle/archivelog');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>12);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/home/oracle/rman/full_GG1_20120921_24',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/  2    3    4    5    6    7    8    9   10   11  

PL/SQL procedure successfully completed.

8. 创建控制文件。

SQL>  CREATE CONTROLFILE REUSE DATABASE "GG1" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/oradata/datafile/redo01.log'  SIZE 50M,
  GROUP 2 '/home/oracle/oradata/datafile/redo02.log'  SIZE 50M,
  GROUP 3 '/home/oracle/oradata/datafile/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/oradata/datafile/system01.dbf',
  '/home/oracle/oradata/datafile/undotbs01.dbf',
  '/home/oracle/oradata/datafile/sysaux01.dbf',
  '/home/oracle/oradata/datafile/users01.dbf',
  '/home/oracle/oradata/datafile/example01.dbf',
  '/home/oracle/oradata/datafile/gg01.dbf'
CHARACTER SET AL32UTF8
;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20  

Control file created.

9. 打开数据库。

SQL> alter database open resetlogs;

Database altered.

10. 创建临时表空间。

SQL> alter tablespace temp add tempfile '/home/oracle/oradata/datafile/temp01.dbf' size 100m;

Tablespace altered.

11. 关闭数据库，创建SPFILE，开启数据库。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/home/oracle/initgg1.ora';               

File created.

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.

RMAN无控制文件恢复