1. 虚拟机(Linux)
1.1. 环境
VMware WorkStation 6.0.3 build-80004
Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
Oracle 9.2.0.4
Hostname
ORACLE_HOME
说明
zz1
/oracle9/product/9i
原数据库
zz2
/oracle9/product/9i
新数据库
1.2. 备份恢复过程
1.2.1. 准备工作
在zz2上安装oracle,设置环境变量。因此处采用的是虚拟机,实际工作是将
zz1复制一份在VMware中打开为zz2,并删除相关数据文件。
相关文件
路径或文件
数据文件
/oracle9/oradata/ora9i
dump目录
/oracle9/admin/ora9i
初始化文件
/oracle9/product/9i/dbs/initora9i.ora
口令文件
/oracle9/product/9i/dbs/orapw
删除数据文件
[root@zz2 ~]# su - oracle9
[oracle9@zz2 ~]$ cd oradata/ora9i/
[oracle9@zz2 ora9i]$ pwd /oracle9/oradata/ora9i
[oracle9@zz2 ora9i]$ ll
total 835228
-rw-r-----
1 oracle9 oinstall
1662976 Aug 13 05:09 control01.ctl
-rw-r-----
1 oracle9 oinstall
1662976 Aug 13 05:09 control02.ctl
-rw-r-----
1 oracle9 oinstall
1662976 Aug 13 05:09 control03.ctl
-rw-r-----
1 oracle9 oinstall
26222592 Aug 13 05:09 indx01.dbf
-rw-r-----
1 oracle9 oinstall 104858112 Aug 13 03:59 redo01.log
-rw-r-----
1 oracle9 oinstall 104858112 Aug 13 05:09 redo02.log
-rw-r-----
1 oracle9 oinstall 104858112 Aug 13 03:59 redo03.log
-rw-r-----
1 oracle9 oinstall 262152192 Aug 13 05:09 system01.dbf
-rw-r-----
1 oracle9 oinstall
10493952 Aug 13 05:09 tools01.dbf
-rw-r-----
1 oracle9 oinstall 209723392 Aug 13 05:09 undotbs01.dbf
-rw-r-----
1 oracle9 oinstall
26222592 Aug 13 05:09 users01.dbf
[oracle9@zz2 ora9i]$ rm *
[oracle9@zz2 ora9i]$ ls
删除dump目录
[oracle9@zz2 ora9i]$ cd
[oracle9@zz2 ~]$ cd admin/ora9i/
[oracle9@zz2 ora9i]$ pwd /oracle9/admin/ora9i
[oracle9@zz2 ora9i]$ ls
bdump
cdump
create
pfile
udump
[oracle9@zz2 ora9i]$ rm -rf *
[oracle9@zz2 ora9i]$ ls
删除初始化文件和口令文件
[oracle9@zz2 ora9i]$ cd
[oracle9@zz2 ~]$ cd product/9i/dbs/
[oracle9@zz2 dbs]$ pwd /oracle9/product/9i/dbs
[oracle9@zz2 dbs]$ ls
initdw.ora
init.ora
initora9i.ora
lkORA9I
orapw
spfileora9i.ora
[oracle9@zz2 dbs]$ rm *
[oracle9@zz2 dbs]$ ls
完成这些工作,就得到了一个干净的oracle代码环境。
1.2.2. 创建相关目录和文件
创建相关目录
mkdir /oracle9/admin/ora9i/bdump
mkdir /oracle9/admin/ora9i/cdump
mkdir /oracle9/admin/ora9i/create
mkdir /oracle9/admin/ora9i/pfile
mkdir /oracle9/admin/ora9i/udump
mkdir /oracle9/oradata/ora9i
mkdir /oracle9/product/9i/dbs
创建口令文件
orapwd file='/oracle9/product/9i/dbs/orapw' password=oracle entries=5;
复制初始化文件
在zz1上创建pfile并复制至zz2:
[root@zz1 /]# su - oracle9
[oracle9@zz1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Aug 24 22:11:06 2008
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved. Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area
168891412 bytes
Fixed Size
451604 bytes
Variable Size
67108864 bytes
Database Buffers
100663296 bytes
Redo Buffers
667648 bytes
Database mounted.
Database opened.
SQL> show parameter pfile;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
spfile
string
?/dbs/spfile@.ora
SQL> create pfile='/tmp/zz.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle9@zz1 ~]$ cd /tmp
[oracle9@zz1 tmp]$ scp zz.ora oracle9@zz2:/tmp/
1.2.3. 修改初始化文件
在zz2上修改初始化文件(假设之后还需要将数据文件迁移至其他目录),修改控制文件的路径。
原路径
新路径
/oracle9/oradata/ora9i/control01.ctl,
/oradata/ora9i/control01.ctl
/oracle9/oradata/ora9i/control02.ctl,
/oradata/ora9i/control02.ctl,
/oracle9/oradata/ora9i/control03.ctl
/oradata/ora9i/control03.ctl
修改初始化文件
[oracle9@zz2 tmp]$vi /tmp/zz.ora
修改:
*.control_files='/oracle9/oradata/ora9i/control01.ctl','/oracle9/oradata/ora9i/control02.ctl','/oracle9/oradata/ora9i/control03.ctl'
为:
*.control_files='/oradata/ora9i/control01.ctl','/oradata/ora9i/control02.ctl','/oradata/ora9i/control03.ctl'
创建控制文件存放目录
[oracle9@zz2 ~]$ cd /oradata/
[oracle9@zz2 oradata]$ mkdir ora9i
1.2.4. 创建验证用数据
在zz1:
SQL> create user hicks indentified by hicks;
User created.
SQL> grant dba to hicks; Grant succeeded.
SQL> create table hicks.t1(id int,name varchar(20));
Table created.
SQL> insert into hicks.t1 values (1,'hicks');
1 row created.
SQL> insert into hicks.t1 values (2,'bob');
1 row created.
SQL> select * from hicks.t1;
ID
NAME
---------- --------------------
1
hicks
2
bob
SQL> commit;
1.2.5. 数据备份
备份数据库
创建/tmp/zz目录,将数据库备份至此。
[oracle9@zz1 dbs]$ cd /tmp/
[oracle9@zz1 tmp]$ mkdir zz
[oracle9@zz1 tmp]$ rman target /
RMAN> backup full database format '/tmp/zz/full_%s_%p_%t.bp';
备份控制文件
RMAN> backup current controlfile format '/tmp/zz/control_file.bak';
1.2.6. 数据恢复
启动数据库至nomount状态并创建spfile
[oracle9@zz2 dbs]$ sqlplus "/as sysdba"
SQL> startup nomount pfile='/tmp/zz.ora';
SQL> show parameter control
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time
integer
7
control_files
string
/oradata/ora9i/control01.ctl,
/oradata/ora9i/control02.ctl,
/oradata/ora9i/control03.ctl
SQL> create pfile from spfile;
File created.
传送数据文件
将备份出来的数据文件传至zz2:
[oracle9@zz1 tmp]$ cd zz
[oracle9@zz1 zz]$ scp * oracle9@zz2:/tmp/zz/
oracle9@zz2's password:
control_file.bak
100% 1648KB
1.6MB/s
00:01
full_14_1_663632578.bp
100%
203MB
4.0MB/s
00:51
还原控制文件
[oracle9@zz2 dbs]$ rman target /
RMAN> restore controlfile from '/tmp/zz/control_file.bak';
还原数据库
RMAN> alter database mount; database mounted
RMAN> restore database;
恢复数据库
[oracle9@zz2 dbs]$ rman target /
RMAN> recover database;
检查验证用数据
SQL> alter database open read only;
Database altered.
SQL> select * from hicks.t1;
ID NAME
---------- --------------------
1 hicks
2 bob
可成功查看相应数据。
1.2.7. 通过恢复归档同步数据库数据
修改原数据库验证用数据
在zz1:
SQL> insert into hicks.t1 values(3,'jim');
1 row created.
SQL> insert into hicks.t1 values (4, 'kate');
1 row created.
SQL> commit; Commit complete.
SQL> select * from hicks.t1;
ID NAME ---------- --------------------
1 hicks
2 bob
3 jim
4 kate
切换redo log产生归档
SQL> alter system switch logfile; System altered.
SQL> /
System altered.
复制归档日志
[oracle9@zz1 zz]$ cd /oracle9/arch/
[oracle9@zz1 arch]$ ls
1_10.arc
1_1.arc
1_2.arc
1_3.arc
1_4.arc
1_5.arc
1_6.arc
1_7.arc
1_8.arc
1_9.arc
[oracle9@zz1 arch]$ scp * oracle9@zz2:/oracle9/arch/
oracle9@zz2's password:
1_10.arc
100% 1024
1.0KB/s
00:00
1_1.arc
100% 2201KB
2.2MB/s
00:00
1_2.arc
100% 1024
1.0KB/s
00:00
1_3.arc
100% 1536
1.5KB/s
00:00
1_4.arc
100% 1024
1.0KB/s
00:00
1_5.arc
100% 1024
1.0KB/s
00:00
1_6.arc
100% 1024
1.0KB/s
00:00
1_7.arc
100% 1024
1.0KB/s
00:00
1_8.arc
100% 1024
1.0KB/s
00:00
1_9.arc
100%
28KB
27.5KB/s
00:00
恢复归档日志
[oracle9@zz2 arch]$ rman target/
RMAN> recover database;
RMAN> exit
检查验证用数据
SQL> alter database open read only;
Database altered.
SQL> select * from hicks.t1;
ID NAME
---------- --------------------
1 hicks
2 bob
3 jim
4 kate
可成功查看相应数据已更新。
打开数据库并重置redo log
SQL> shutdown immediate;
SQL> startup mount;
SQL>alter database open resetlogs;
检查验证用数据
SQL> alter database open read only;
Database altered.
SQL> select * from hicks.t1;
ID NAME
---------- --------------------
1 hicks
2 bob
3 jim
4 kate
重置redo log打开数据库之后,可成功查看相应数据已更新。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.3. RMAN copy迁移数据
1.3.1. 环境和目标
原数据文件
新数据文件
/oracle9/oradata/ora9i/system01.dbf
/ oradata/ora9i/system01.dbf
/oracle9/oradata/ora9i/undotbs01.dbf
/oradata/ora9i/undotbs01.dbf
/oracle9/oradata/ora9i/indx01.dbf
/oradata/ora9i/indx01.dbf
/oracle9/oradata/ora9i/tools01.dbf
/oradata/ora9i/tools01.dbf
/oracle9/oradata/ora9i/users01.dbf
/oradata/ora9i/users01.dbf
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oracle9/oradata/ora9i/system01.dbf
/oracle9/oradata/ora9i/undotbs01.dbf
/oracle9/oradata/ora9i/indx01.dbf
/oracle9/oradata/ora9i/tools01.dbf
/oracle9/oradata/ora9i/users01.dbf
1.3.2. RMAN copy复制数据文件
启动数据库至mount状态
SQL> shutdown immediate;
SQL> startup mount;
使用RMAN复制数据文件
[oracle9@zz2 arch]$ rman target /
RMAN> copy datafile '/oracle9/oradata/ora9i/indx01.dbf' to '/oradata/ora9i/indx01.dbf';
RMAN> copy datafile '/oracle9/oradata/ora9i/system01.dbf' to '/oradata/ora9i/system01.dbf';
RMAN> copy datafile '/oracle9/oradata/ora9i/undotbs01.dbf' to '/oradata/ora9i/undotbs01.dbf';
RMAN> copy datafile '/oracle9/oradata/ora9i/tools01.dbf' to '/oradata/ora9i/tools01.dbf';
RMAN> copy datafile '/oracle9/oradata/ora9i/users01.dbf' to '/oradata/ora9i/users01.dbf';
1.3.3. rename数据文件
rename数据文件
SQL> alter database rename file '/oracle9/oradata/ora9i/indx01.dbf' to '/oradata/ora9i/indx01.dbf';
SQL> alter database rename file '/oracle9/oradata/ora9i/system01.dbf' to '/oradata/ora9i/system01.dbf';
SQL> alter database rename file '/oracle9/oradata/ora9i/tools01.dbf' to '/oradata/ora9i/tools01.dbf';
SQL> alter database rename file '/oracle9/oradata/ora9i/undotbs01.dbf' to '/oradata/ora9i/undotbs01.dbf';
SQL> alter database rename file '/oracle9/oradata/ora9i/users01.dbf'
to
'/oradata/ora9i/users01.dbf';
检查结果
SQL> shutdown immediate;
SQL> startup
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oradata/ora9i/system01.dbf
/oradata/ora9i/undotbs01.dbf
/oradata/ora9i/indx01.dbf
/oradata/ora9i/tools01.dbf
/oradata/ora9i/users01.dbf
数据库已使用新的数据文件
1.3.4. 处理redo log
增加新的redo log
SQL> alter database add logfile member
'/oradata/ora9i/redo01.log' to group 1;
Database altered.
SQL> alter database add logfile member
'/oradata/ora9i/redo02.log' to group 2;
Database altered.
SQL> alter database add logfile member
'/oradata/ora9i/redo03.log' to group 3;
Database altered
切换日志并删除旧的redo log
SQL> select group#,members,status from v$log order by group#;
GROUP#
MEMBERS
STATUS
----------
----------
----------------
1
2
CURRENT
2
2
INACTIVE
3
2
INACTIVE
删除状态为INACTIVE的旧redo log(本例中是group2和group3):
SQL> alter database drop logfile member '/oracle9/oradata/ora9i/redo02.log';
Database altered.
SQL> alter database drop logfile member '/oracle9/oradata/ora9i/redo03.log';
Database altered.
切换redo log:
SQL> alter system switch logfile; System altered.
SQL> select group#,members,status from v$log order by group#;
GROUP#
MEMBERS
STATUS
----------
----------
----------------
1
2
INACTIVE
2
1
CURRENT
3
1
INACTIVE
删除MEMBERS为2,状态为INACTIVE的旧redo log(本例中为GROUP1):
SQL> alter database drop logfile member '/oracle9/oradata/ora9i/redo01.log';
Database altered
1.3.5. 检查结果
重启数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
SQL> startup
检查redo log
SQL> select * from v$logfile;
GROUP# STATUS
TYPE
MEMBER
---------- ------- ------- ------------------------------
1
ONLINE
/oradata/ora9i/redo01.log
2
ONLINE
/oradata/ora9i/redo02.log
3
ONLINE
/oradata/ora9i/redo03.log
SQL> select group#,members,status from v$log order by group#;
GROUP#
MEMBERS
STATUS
----------
----------
----------------
1
1
INACTIVE
2
1
CURRENT
3
1
INACTIVE
检查数据文件
SQL> select tablespace_name,bytes,file_name from dba_data_files order by file_name;
TABLESPACE_NAME
BYTES
FILE_NAME
------------------------------
----------
----------------------------------------
INDX
26214400
/oradata/ora9i/indx01.dbf
SYSTEM
262144000
/oradata/ora9i/system01.dbf
TOOLS
10485760
/oradata/ora9i/tools01.dbf
UNDOTBS1
209715200
/oradata/ora9i/undotbs01.dbf
USERS
26214400
/oradata/ora9i/users01.dbf