Oracle数据库迁移 -

哇哈哈852

浏览: 92096 次

最近访客更多访客>>

woimovie

lxheyu

xiangkun

fanan_666

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (130)

社区版块

存档分类

2014-07 ( 54)
2014-06 ( 45)
2014-05 ( 28)
更多存档...

Oracle数据库迁移

cuug ocp dba ocm

数据库的迁移方式千变万化，各有千秋。今天为大家呈现的是第一种方法，利用RMAN来完成数据库的迁移工作。

1.打开RMAN
[oracle@ENMOEDU ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Apr 6 17:52:06 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ENMOEDU (DBID=87396644)
RMAN>

2.查看当前的配置信息并修改配置信息
RMAN> show all;

RMAN configuration parameters for database with db_unique_name ENMOEDU are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ENMOEDU.f'; # default

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

3.对数据库进行一次全备
RMAN> backup database include current controlfile plus archivelog;

Starting backup at 06-APR-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1

                            .

                            .

                            .
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-APR-14

4.查看备份情况，删除冗余备份
RMAN> list backup;
List of Backup Sets
===================

BS Key Size      Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1      70.64M    DISK        00:00:00    06-APR-14
        BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140406T181504
        Piece Name: /u01/app/oracle/fast_recovery_area/ENMOEDU/backupset/2014_04_06/o1_mf_annnn_TAG20140406T181504_9n2b987h_.bkp

.

.

.
BS Key Type LV Size      Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    9.36M      DISK        00:00:00    06-APR-14
        BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20140406T183542
        Piece Name: /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_04_06/o1_mf_s_844194942_9n2chyvw_.bkp
SPFILE Included: Modification time: 04-APR-14
SPFILE db_unique_name: ENMOEDU
Control File Included: Ckp SCN: 1302814      Ckp time: 06-APR-14

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          1      06-APR-14          /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2014_04_06/o1_mf_1_91_9n27s6o2_.arc

.

.

.

Do you really want to delete the above objects (enter YES or NO)? yes
.

.

.
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/ENMOEDU/backupset/2014_04_06/o1_mf_ncnnf_TAG20140406T183529_9n2chwnm_.bkp RECID=9 STAMP=844194940
Deleted 11 objects

5.将快速恢复区下的备份发送到要建立数据库的主机上
[oracle@ENMOEDU ~]$ cd /u01/app/oracle/fast_recovery_area/
[oracle@ENMOEDU fast_recovery_area]$ scp -r ENMOEDU/ oracle@192.168.80.11:/u01/app/oracle/fast_recovery_area/
oracle@192.168.80.11's password:
o1_mf_1_95_9n2chxov_.arc                                                              100% 3072    3.0KB/s 00:00
o1_mf_nnndf_TAG20140406T183529_9n2chkgc_.bkp                                          100% 1059MB 53.0MB/s 00:20
o1_mf_nnndf_TAG20140406T183529_9n2chrl2_.bkp                                          100% 224MB 55.9MB/s 00:04
o1_mf_annnn_TAG20140406T183541_9n2chxs6_.bkp                                          100% 4608    4.5KB/s 00:00
o1_mf_s_844194942_9n2chyvw_.bkp                                                        100% 9600KB 9.4MB/s 00:00
control02.ctl                                                                          100% 9520KB 9.3MB/s 00:00

6.在建立数据库的主机上查看传输过来的备份
[oracle@FRANK ~]$ ls /u01/app/oracle/fast_recovery_area/
ENMOEDU FRANK
此时，数据库的备份传输成功。

7.利用RMAN恢复数据库
[oracle@FRANK ENMOEDU]$ export ORACLE_SID=ENMOEDU
[oracle@FRANK ENMOEDU]$ echo $ORACLE_SID
ENMOEDU
[oracle@FRANK ENMOEDU]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Mar 1 15:10:16 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area    171581440 bytes

Fixed Size                    1343668 bytes
Variable Size                117444428 bytes
Database Buffers              50331648 bytes
Redo Buffers                  2461696 bytes
1)恢复控制文件
RMAN> restore controlfile FROM AUTOBACKUP;

Starting restore at 01-MAR-14
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ENMOEDU
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/01/2014 15:33:58
ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ENMOEDU/control01.ctl"
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Additional information: 1

根据提示我们创建文件夹：
[oracle@FRANK 2014_04_06]$ mkdir -p /u01/app/oracle/oradata/ENMOEDU

继续之前的恢复工作：
RMAN> restore controlfile FROM AUTOBACKUP;

Starting restore at 01-MAR-14
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ENMOEDU
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ENMOEDU/autobackup/2014_03_01/o1_mf_s_841048138_9k2bgbo1_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ENMOEDU/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
Finished restore at 01-MAR-14