数据库从裸设备迁移到文件系统

数据库从裸设备迁移到文件系统

 

操作系统：RHEL 2.6.9-78.ELsmp 32bit
数据库版本：10.2.0.1 32bit
文件系统：裸设备 -> 文件系统

一、查看裸设备各个数据文件所在的位置
SQL> select tablespace_name,file_name,bytes/1048576 from dba_data_files;
 
TABLESPACE_NAME      FILE_NAME            BYTES/1048576
-------------------- -------------------- -------------
USERS                /dev/raw/raw10                   5
SYSAUX               /dev/raw/raw3                  230
UNDOTBS1             /dev/raw/raw2                   25
SYSTEM               /dev/raw/raw1                  480
 
SQL> select group#,member from v$logfile;
 
    GROUP# MEMBER
---------- ------------------------------
         2 /dev/raw/raw9
         1 /dev/raw/raw8
 
SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
/dev/raw/raw6
/dev/raw/raw7
 

二、备份数据库到文件系统

RMAN> run{
2> allocate channel ch0 type disk format='/mnt1/data/%U';
3> backup database ;
4> sql 'alter system switch logfile';
5> release channel ch0;
6> }

三、修改初始化参数中控制文件的位置
*.control_files='/mnt1/data/CONTROL01.CTL','/mnt1/data/CONTROL02.CTL'

四、从备份集还原数据库到文件系统
1)启动数据库到nomunt

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area  281018368 bytes
Fixed Size                  1218968 bytes
Variable Size              88082024 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ rman
 
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jul 11 20:05:14 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
RMAN> connect target
 
connected to target database: LGX (not mounted)

2)还原控制文件
RMAN> restore controlfile from '/mnt1/data/08kjt1f2_1_1';
 
Starting restore at 11-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
 
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/mnt1/data/CONTROL01.CTL
output filename=/mnt1/data/CONTROL02.CTL
Finished restore at 11-JUL-09

RMAN> sql 'alter database mount';
 
sql statement: alter database mount
released channel: ORA_DISK_1

 

3)修改日志文件的路径
SQL>alter database rename file '/dev/raw/raw8' to '/mnt1/data/redo1_1.ora';

Database altered.

SQL>alter database rename file '/dev/raw/raw9' to '/mnt1/data/redo2_1.ora';

Database altered.

是为了在open resetlogs后日志文件也是在文件系统中。

3)还原数据库
run{
allocate channel ch0 type disk;
set newname for datafile '/dev/raw/raw10' to '/mnt1/data/users01.dbf';
set newname for datafile '/dev/raw/raw3' to '/mnt1/data/sysaux01.dbf';
set newname for datafile '/dev/raw/raw2' to '/mnt1/data/undotbs01.dbf';
set newname for datafile '/dev/raw/raw1' to '/mnt1/data/system01.dbf';
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
release channel ch0;
}

4)添加临时表空间
SQL> alter tablespace temp add tempfile '/mnt1/data/temp01.dbf' size 10M;

5)查看数据库文件位置
SQL> select tablespace_name,file_name,bytes/1048576 from dba_data_files;
 
TABLESPACE_NAME      FILE_NAME                      BYTES/1048576
-------------------- ------------------------------ -------------
USERS                /mnt1/data/users01.dbf                     5
SYSAUX               /mnt1/data/sysaux01.dbf                  230
UNDOTBS1             /mnt1/data/undotbs01.dbf                  25
SYSTEM               /mnt1/data/system01.dbf                  480

SQL> select group#,member from v$logfile;
 
    GROUP# MEMBER
---------- ------------------------------
         2 /mnt1/data/redo2_1.ora
         1 /mnt1/data/redo1_1.ora

SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
/mnt1/data/CONTROL01.CTL
/mnt1/data/CONTROL02.CTL