linux 下利用rman 迁移 oracle 9i数据库

此文档主要记录：
linux下利用rman 迁移 oracle 9i （9.2.0.8.0） 数据库wjlcn，是在非归档模式下进行的测试。
从suse002b（192.168.17.243）服务器上的数据库利用rman备份；
将备份的文件copy到suse002c（192.168.17.242）服务器上，利用rman的restore还原数据库。

 

#######################################
第一部分：suse002b数据库wjlcn用rman备份
#######################################

 

1.在suse002b数据库wjlcn中，创建表test321，并插入数据
suse002b:~ # ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0C:29:30:8E:43 
          inet addr:192.168.17.243  Bcast:192.168.17.255  Mask:255.255.255.0
          > hostname
suse002b
> echo $ORACLE_SID
wjlcn
> sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Mar 21 10:39:49 2011

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> create table test321 (
  2  id int);

Table created.

SQL> insert into test321 values(88);
SQL> insert into test321 select * from test321;

......

SQL> commit;

Commit complete.

SQL> select count(*) from test321;

  COUNT(*)
----------
     16384

2.在suse002b服务器上，停止数据库，并将数据库启动到mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount
ORACLE instance started.

Total System Global Area  320306616 bytes
Fixed Size                   740792 bytes
Variable Size             285212672 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
Database mounted.

3.进入rman，设置CONTROLFILE AUTOBACKUP ON
> ./rman target /

Recovery Manager: Release 9.2.0.8.0 - 64bit Production
connected to target database: WJLCN (DBID=2360256816)
RMAN> show all;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # 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 SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/9iR2/dbs/snapcf_wjlcn.f'; # default

4.全备
RMAN> backup full database format '/opt/oracle/backup/wjlcnfull_%s_%p_%t';

Starting backup at 21-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=/opt/oracle/oradata/wjlcn/data
input datafile fno=00001 name=/opt/oracle/oradata/wjlcn/system01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/wjlcn/undotbs01.dbf
input datafile fno=00007 name=/opt/oracle/oradata/wjlcn/xdb01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/wjlcn/indx01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/wjlcn/users01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/wjlcn/drsys01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/wjlcn/tools01.dbf
input datafile fno=00009 name=/opt/oracle/oradata/wjlcn/test.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-11
channel ORA_DISK_1: finished piece 1 at 21-MAR-11
piece handle=/opt/oracle/backup/wjlcnfull_4_1_746362182 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 21-MAR-11

Starting Control File and SPFILE Autobackup at 21-MAR-11
piece handle=/opt/oracle/product/9iR2/dbs/c-2360256816-20110321-00 comment=NONE
Finished Control File and SPFILE Autobackup at 21-MAR-11

 

#######################################
第二部分：suse002c数据库wjlcn用rman还原
#######################################

1.将wjlcnfull_4_1_746362182 、c-2360256816-20110321-00等文件上传到suse002c服务器上
suse002c:~ # ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0C:29:9D:D5:E3 
          inet addr:192.168.17.242  Bcast:192.168.17.255  Mask:255.255.255.0
         
:~> scp -r 192.168.17.243:/opt/oracle/backup .
wjlcnfull_4_1_746362182
> scp -r 192.168.17.243:/opt/oracle/product/9iR2/dbs .
> ls
c-2360256816-20110321-00  initdw.ora  init.ora  orapwwjlcn  snapcf_wjlcn.f  spfilewjlcn.ora

2.创建/opt/oracle/admin/wjlcn、opt/oracle/oradata/wjlcn目录

3.将wjlcn数据库启动到nomount状态
> export ORACLE_SID=wjlcn
> sqlplus "/as sysdba"
SQL> startup nomount
ORACLE instance started.

Total System Global Area  320306616 bytes
Fixed Size                   740792 bytes
Variable Size             285212672 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes

4.进入rman还原控制文件
RMAN> restore controlfile from '/opt/oracle/product/9iR2/dbs/c-2360256816-20110321-00';

Starting restore at 21-MAR-11

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/oracle/oradata/wjlcn/control01.ctl
output filename=/opt/oracle/oradata/wjlcn/control02.ctl
output filename=/opt/oracle/oradata/wjlcn/control03.ctl
Finished restore at 21-MAR-11

5.启动数据库wjlcn到mount状态
RMAN> startup mount;                                                                  

database is already started
database mounted

RMAN> list backup;

6.还原数据文件
RMAN> restore database;

Starting restore at 21-MAR-11

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 /opt/oracle/oradata/wjlcn/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/wjlcn/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/wjlcn/drsys01.dbf
restoring datafile 00004 to /opt/oracle/oradata/wjlcn/indx01.dbf
restoring datafile 00005 to /opt/oracle/oradata/wjlcn/tools01.dbf
restoring datafile 00006 to /opt/oracle/oradata/wjlcn/users01.dbf
restoring datafile 00007 to /opt/oracle/oradata/wjlcn/xdb01.dbf
restoring datafile 00008 to /opt/oracle/oradata/wjlcn/data
restoring datafile 00009 to /opt/oracle/oradata/wjlcn/test.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/wjlcnfull_4_1_746362182 tag=TAG20110321T104942 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 21-MAR-11

RMAN> recover database;

7.把数据库wjlcn 启动到open状态
RMAN> alter database open resetlogs;

database opened

8.检查数据库运行状态，并查看原来的数据是否存在
> sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Mar 21 07:35:13 2011

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> select status from v$instance;

STATUS
------------
OPEN

SQL> select count(*) from test321;

  COUNT(*)
----------
     16384

SQL> exit

9.编辑oratab
vi /etc/oratab
添加"wjlcn:/opt/oracle/product/9iR2:N"
这样dbca才能管理数据库wjlcn。

                                   author by wjlcn . 2011-3-21