数据迁移方案(文件系统迁移到裸设备)转自网络
时间：2010-12-22 来源：zr_baobao
http://blog.csdn.net/csucxcc/archive/2010/07/20/5749438.aspx
数据迁移方案
鉴于rman进行数据复制有自己独特的优势，(见附件 )我们准备采用rman copy的方式来对oracle数据文件进行拷贝，从源文件系统格式转为目标裸设备格式。
具体步骤如下：
1、 停止生产数据库。
2、 做数据文件的冷备份，包括数据文件，控制文件，联机日志文件和pfile。
3、 开启数据库到mount状态。做数据文件从文件系统到裸设备的复制操作。
4、 将联机日志文件做文件系统到裸设备的复制操作。
5、 修改控制文件，修改数据文件、redo日志文件的位置，改到裸设备上来。
6、 将改动后的控制文件备份到裸设备上。
7、 修改spfile，改到裸设备上来。
8、 使用新的spfile，重启数据库到mount状态，（修改dbs文件夹下init_sid.ora中spfile的位置定义）
9、 修改spfile中控制文件的位置。关闭数据库。
10、开启数据库，如果有问题则进行恢复。
11、对数据库进行备份。
详细实施步骤
1、 停止生产数据库。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2、 做数据库文件冷备份。
3、 开启数据库到mount状态。做数据文件从文件系统到裸设备的复制操作。
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
RMAN> copy datafile '/oracle/oradata/orcl/system01.dbf' to '/oracle/oradata/orcl/system01_raw';
Starting backup at 19-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
RMAN> copy datafile '/oracle/oradata/orcl/undotbs01.dbf' to '/oracle/oradata/orcl/undotbs01_raw';
Starting backup at 19-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
output filename=/oracle/oradata/orcl/undotbs01_raw tag=TAG20100719T091805 recid=5 stamp=724756701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 19-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/sysaux01.dbf' to '/oracle/oradata/orcl/sysaux01_raw';
Starting backup at 19-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
output filename=/oracle/oradata/orcl/sysaux01_raw tag=TAG20100719T092208 recid=6 stamp=724756952
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 19-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/users01.dbf' to '/oracle/oradata/orcl/users01_raw';
Starting backup at 19-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
output filename=/oracle/oradata/orcl/users01_raw tag=TAG20100719T092344 recid=7 stamp=724757025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/logmnr.dbf' to '/oracle/oradata/orcl/logmnr_raw';
Starting backup at 19-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/oracle/oradata/orcl/logmnr.dbf
output filename=/oracle/oradata/orcl/logmnr_raw tag=TAG20100719T092418 recid=8 stamp=724757077
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 19-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/stadm.dbf' to '/oracle/oradata/orcl/stadm_raw';
Starting backup at 19-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/oracle/oradata/orcl/stadm.dbf
Starting backup at 19-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/oracle/oradata/orcl/stadm.dbf
output filename=/oracle/oradata/orcl/stadm_raw tag=TAG20100719T092521 recid=9 stamp=724757140
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 19-JUL-10
4、将裸设备作为联机日志文件使用。
把裸设备加入联机日志组，并做switch logfile操作，再删除老的文件系统联机日志组。
5、修改控制文件，修改数据文件、redo日志文件的位置，改到裸设备上来。
sqlplus / as sysdba
alter database rename file '/oracle/oradata/orcl/system01.dbf' to '/oracle/oradata/orcl/system01_raw';
alter database rename file '/oracle/oradata/orcl/undotbs01.dbf' to '/oracle/oradata/orcl/undotbs01_raw';
alter database rename file '/oracle/oradata/orcl/sysaux01.dbf' to '/oracle/oradata/orcl/sysaux01_raw';
alter database rename file '/oracle/oradata/orcl/users01.dbf' to '/oracle/oradata/orcl/users01_raw';
alter database rename file '/oracle/oradata/orcl/logmnr.dbf' to '/oracle/oradata/orcl/logmnr_raw';
alter database rename file '/oracle/oradata/orcl/stadm.dbf' to '/oracle/oradata/orcl/stadm_raw';
alter database rename file '/oracle/oradata/orcl/redo01.log' to '/oracle/oradata/orcl/redo1_raw';
alter database rename file '/oracle/oradata/orcl/redo02.log' to '/oracle/oradata/orcl/redo2_raw';
alter database rename file '/oracle/oradata/orcl/redo03.log' to '/oracle/oradata/orcl/redo3_raw';
6、将改动后的控制文件备份到裸设备上。
SQL> alter database backup controlfile to '/oracle/oradata/orcl/control1_raw';
Database altered.
SQL> alter database backup controlfile to '/oracle/oradata/orcl/control2_raw';
Database altered.
SQL> alter database backup controlfile to '/oracle/oradata/orcl/control3_raw';
Database altered.
7、 修改spfile，改到裸设备上来。
SQL> create pfile='/home/oracle/nowpfile.ora' from spfile;
File created.
SQL> create spfile='/oracle/oradata/orcl/spfile_raw' from pfile='/home/oracle/nowpfile.ora';
File created.
8、 使用新的spfile，重启数据库到mount状态，（修改dbs文件夹下init_sid.ora中spfile的位置定义）
[oracle@/oracle/product/10.2.0/db_1/dbs]$more initorcl.ora
spfile='/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora'
*.commit_write='IMMEDIATE','NOWAIT'
改为
spfile='/oracle/oradata/orcl/spfile_raw'
9、 修改spfile中控制文件的位置。关闭数据库。
SQL> show parameters controlfile;
SQL> show parameters control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control1_raw
SQL> alter system set
control_files='/oracle/oradata/orcl/control1_raw','/oracle/oradata/orcl/control2_raw','/oracle/oradata/orcl/control3_raw' scope=spfile;
System altered.
10、 开启数据库，如果有问题则进行恢复。
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> show parameters control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control1_
raw, /oracle/oradata/orcl/cont
rol2_raw, /oracle/oradata/orcl
/control3_raw
SQL> alter database open;
Database altered.
SQL> show parameters control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control1_
raw, /oracle/oradata/orcl/cont
rol2_raw, /oracle/oradata/orcl
/control3_raw
11、 查看数据库状态
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control1_raw
/oracle/oradata/orcl/control2_raw
/oracle/oradata/orcl/control3_raw
SQL> show parameters spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/oradata/orcl/spfile_raw
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/system01_raw
/oracle/oradata/orcl/undotbs01_raw
/oracle/oradata/orcl/sysaux01_raw
/oracle/oradata/orcl/users01_raw
/oracle/oradata/orcl/3.dbf
/oracle/oradata/orcl/logmnr_raw
/oracle/oradata/orcl/stadm_raw
7 rows selected.
12、 对数据库进行备份。
详细脚本
||||||||||||||||||||||
文件系统到裸设备
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control1.ctl
/oracle/oradata/orcl/control2.ctl
/oracle/oradata/orcl/control3.ctl
SQL> show parameters spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/10.2.0/db_1/db
s/spfileorcl.ora
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/system01.dbf
/oracle/oradata/orcl/undotbs01.dbf
/oracle/oradata/orcl/sysaux01.dbf
/oracle/oradata/orcl/users01.dbf
/oracle/oradata/orcl/3.dbf
/oracle/oradata/orcl/logmnr.dbf
/oracle/oradata/orcl/stadm.dbf
7 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/orcl/log04.LOG
/oracle/oradata/orcl/log05.LOG
/oracle/oradata/orcl/log06.LOG
1、 停止生产数据库。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2、 做数据库文件冷备份。
3、 开启数据库到mount状态。做数据文件从文件系统到裸设备的复制操作。
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
RMAN> copy datafile '/oracle/oradata/orcl/system01.dbf' to '/oracle/oradata/orcl/system01_raw';
Starting backup at 19-JUL-10
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: starting datafile copy
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
output filename=/oracle/oradata/orcl/system01_raw tag=TAG20100719T231316 recid=24 stamp=724806895
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
Finished backup at 19-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/undotbs01.dbf' to '/oracle/oradata/orcl/undotbs0_raw';
Starting backup at 19-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
output filename=/oracle/oradata/orcl/undotbs0_raw tag=TAG20100719T231544 recid=25 stamp=724806996
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
Finished backup at 19-JUL-10\
RMAN> copy datafile '/oracle/oradata/orcl/sysaux01.dbf' to '/oracle/oradata/orcl/sysaux01_raw';
Starting backup at 20-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
output filename=/oracle/oradata/orcl/sysaux01_raw tag=TAG20100720T001927 recid=26 stamp=724810774
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 20-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/users01.dbf' to '/oracle/oradata/orcl/users01_raw';
Starting backup at 20-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
output filename=/oracle/oradata/orcl/users01_raw tag=TAG20100720T002058 recid=27 stamp=724810859
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/logmnr.dbf' to '/oracle/oradata/orcl/logmnr_raw';
Starting backup at 20-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/oracle/oradata/orcl/logmnr.dbf
output filename=/oracle/oradata/orcl/logmnr_raw tag=TAG20100720T002111 recid=28 stamp=724810898
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 20-JUL-10
RMAN> copy datafile '/oracle/oradata/orcl/stadm.dbf' to '/oracle/oradata/orcl/stadm_raw';
Starting backup at 20-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/oracle/oradata/orcl/stadm.dbf
output filename=/oracle/oradata/orcl/stadm_raw tag=TAG20100720T002413 recid=29 stamp=724811080
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 20-JUL-10
4、将联机日志文件做文件系统到裸设备的复制操作。
Alter database add logfile group 1 ('/oracle/oradata/orcl/redo1_raw')size 10m;
Alter database add logfile group 2 ('/oracle/oradata/orcl/redo2_raw')size 10m;
Alter database add logfile group 3 ('/oracle/oradata/orcl/redo3_raw')size 10m;
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> Alter database add logfile group 1 ('/oracle/oradata/orcl/redo1_raw')size 10m;
Database altered.
SQL> Alter database add logfile group 2 ('/oracle/oradata/orcl/redo2_raw')size 10m;
Database altered.
SQL> Alter database add logfile group 3 ('/oracle/oradata/orcl/redo3_raw')size 10m;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
6 UNUSED
4 UNUSED
5 CURRENT
3 UNUSED
6 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
5 ACTIVE
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
5 INACTIVE
SQL> alter database drop logfile group 5;
Database altered.
5、修改控制文件，修改数据文件、redo日志文件的位置，改到裸设备上来。
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/system01.dbf
/oracle/oradata/orcl/undotbs01.dbf
/oracle/oradata/orcl/sysaux01.dbf
/oracle/oradata/orcl/users01.dbf
/oracle/oradata/orcl/3.dbf
/oracle/oradata/orcl/logmnr.dbf
/oracle/oradata/orcl/stadm.dbf
7 rows selected.
sqlplus / as sysdba
alter database rename file '/oracle/oradata/orcl/system01.dbf' to '/oracle/oradata/orcl/system01_raw';
Database altered.
alter database rename file '/oracle/oradata/orcl/undotbs01.dbf' to '/oracle/oradata/orcl/undotbs1_raw';
Database altered.
alter database rename file '/oracle/oradata/orcl/sysaux01.dbf' to '/oracle/oradata/orcl/sysaux01_raw';
Database altered.
alter database rename file '/oracle/oradata/orcl/users01.dbf' to '/oracle/oradata/orcl/users01_raw';
Database altered.
alter database rename file '/oracle/oradata/orcl/logmnr.dbf' to '/oracle/oradata/orcl/logmnr_raw';
Database altered.
alter database rename file '/oracle/oradata/orcl/stadm.dbf' to '/oracle/oradata/orcl/stadm_raw';
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
7、 修改spfile，改到裸设备上来。
SQL> create pfile='/home/oracle/nowpfile.ora' from spfile;
File created.
SQL> create spfile='/oracle/oradata/orcl/spfile_raw' from pfile='/home/oracle/nowpfile.ora';
File created.
8、 使用新的spfile，重启数据库到mount状态，（修改dbs文件夹下init_sid.ora中spfile的位置定义）
[oracle@/oracle/product/10.2.0/db_1/dbs]$more initorcl.ora
spfile='/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora'
*.commit_write='IMMEDIATE','NOWAIT'
改为
spfile='/oracle/oradata/orcl/spfile_raw'
rm spfileorcl.ora
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
sORACLE instance shut down.
SQL>startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
6、将改动后的控制文件备份到裸设备上。
SQL> alter database backup controlfile to '/oracle/oradata/orcl/control1_raw';
Database altered.
9、 修改spfile中控制文件的位置。关闭数据库。
SQL> show parameters control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control1.
ctl, /oracle/oradata/orcl/cont
rol2.ctl, /oracle/oradata/orcl
/control3.ctl
SQL> alter system set
2 control_files='/oracle/oradata/orcl/control1_raw' scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database backup controlfile to '/oracle/oradata/orcl/control2_raw';
Database altered.
SQL> alter database backup controlfile to '/oracle/oradata/orcl/control3_raw';
Database altered.
SQL> alter system set
2 control_files='/oracle/oradata/orcl/control1_raw','/oracle/oradata/orcl/control2_raw','/oracle/oradata/orcl/control3_raw' scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>
10、 开启数据库，如果有问题则进行恢复。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> recover database using backup controlfile;
ORA-00279: change 2375554 generated at 07/19/2010 23:12:12 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_1_%u_.arc
ORA-00280: change 2375554 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2375827 generated at 07/20/2010 00:28:40 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_2_%u_.arc
ORA-00280: change 2375827 for thread 1 is in sequence #2
ORA-00278: log file
'/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_1_648z9rmq_.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_2_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/orcl/system01_raw'
SQL> recover database using backup controlfile;
ORA-00279: change 2375827 generated at 07/20/2010 00:28:40 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_2_%u_.arc
ORA-00280: change 2375827 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/oradata/orcl/redo1_raw
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
11、 查看数据库状态
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control1_raw
/oracle/oradata/orcl/control2_raw
/oracle/oradata/orcl/control3_raw
SQL> show parameters spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/10.2.0/db_1/db
s/spfileorcl.ora
SQL> create spfile='/oracle/oradata/orcl/spfile_raw' from pfile='/home/oracle/nowpfile.ora';
File created.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control1_raw
/oracle/oradata/orcl/control2_raw
/oracle/oradata/orcl/control3_raw
SQL> alter database open;
Database altered.
SQL>
最后的成果:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/system01_raw
/oracle/oradata/orcl/undotbs01_raw
/oracle/oradata/orcl/sysaux01_raw
/oracle/oradata/orcl/users01_raw
/oracle/oradata/orcl/3.dbf
/oracle/oradata/orcl/logmnr_raw
/oracle/oradata/orcl/stadm_raw
7 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control1_raw
/oracle/oradata/orcl/control2_raw
/oracle/oradata/orcl/control3_raw
SQL> show parameters spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/oradata/orcl/spfile_ra
w
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/orcl/redo1_raw
/oracle/oradata/orcl/redo2_raw
/oracle/oradata/orcl/redo3_raw
12、 对数据库进行备份。
|||||||||||||||||||||||||||||||、
裸设备转为数据文件格式：
1、 停止生产数据库。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2、 做数据库文件冷备份。
3、 开启数据库到mount状态。做数据文件从文件系统到裸设备的复制操作。
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
copy datafile '/oracle/oradata/orcl/system01_raw' to '/oracle/oradata/orcl/system01.dbf';
copy datafile '/oracle/oradata/orcl/undotbs01_raw' to '/oracle/oradata/orcl/undotbs01.dbf';
copy datafile '/oracle/oradata/orcl/sysaux01_raw' to '/oracle/oradata/orcl/sysaux01.dbf';
copy datafile '/oracle/oradata/orcl/users01_raw' to '/oracle/oradata/orcl/users01.dbf';
copy datafile '/oracle/oradata/orcl/logmnr_raw' to '/oracle/oradata/orcl/logmnr.dbf';
copy datafile '/oracle/oradata/orcl/stadm_raw' to '/oracle/oradata/orcl/stadm.dbf';
5、修改控制文件，修改数据文件、redo日志文件的位置，改到裸设备上来。
sqlplus / as sysdba
alter database rename file '/oracle/oradata/orcl/system01_raw' to '/oracle/oradata/orcl/system01.dbf';
alter database rename file '/oracle/oradata/orcl/undotbs01_raw' to '/oracle/oradata/orcl/undotbs01.dbf';
alter database rename file '/oracle/oradata/orcl/sysaux01_raw' to '/oracle/oradata/orcl/sysaux01.dbf';
alter database rename file '/oracle/oradata/orcl/users01_raw' to '/oracle/oradata/orcl/users01.dbf';
alter database rename file '/oracle/oradata/orcl/logmnr_raw' to '/oracle/oradata/orcl/logmnr.dbf';
alter database rename file '/oracle/oradata/orcl/stadm_raw' to '/oracle/oradata/orcl/stadm.dbf';
SQL> alter database open;
Database altered.
4、添加文件系统作为联机日志一部分，switch logfile之后，删除裸设备文件。
alter database add logfile member '/oracle/oradata/orcl/log01.LOG' to group 1
alter database add logfile member '/oracle/oradata/orcl/log02.LOG' to group 2
alter database add logfile member '/oracle/oradata/orcl/log03.LOG' to group 3
alter database drop logfile member '/oracle/oradata/orcl/redo1_raw';
Alter database add logfile group 4 ('/oracle/oradata/orcl/log04.LOG')size 10m;
Alter database add logfile group 5 ('/oracle/oradata/orcl/log05.LOG')size 10m;
Alter database add logfile group 6 ('/oracle/oradata/orcl/log06.LOG')size 10m;
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/oracle/oradata/orcl/redo2_raw'
ORA-00312: online log 2 thread 1: '/oracle/oradata/orcl/log02.LOG'
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 ACTIVE
4 ACTIVE
5 CURRENT
6 UNUSED
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 INACTIVE
4 INACTIVE
5 CURRENT
6 UNUSED
SQL> alter database drop logfile group 2;
Database altered.
6、将改动后的控制文件备份到裸设备上。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter database backup controlfile to '/oracle/oradata/orcl/control1.ctl';
Database altered.
cp /oracle/oradata/orcl/control1.ctl /oracle/oradata/orcl/control2.ctl;
cp /oracle/oradata/orcl/control1.ctl /oracle/oradata/orcl/control3.ctl;
7、 修改spfile，改到裸设备上来。
SQL> create pfile='/home/oracle/nowpfile.ora' from spfile;
File created.
SQL> create spfile='/oracle/oradata/orcl/spfile_raw' from pfile='/home/oracle/nowpfile.ora';
File created.
8、 使用新的spfile，重启数据库到mount状态，（修改dbs文件夹下init_sid.ora中spfile的位置定义）
[oracle@/oracle/product/10.2.0/db_1/dbs]$more initorcl.ora
spfile='/oracle/oradata/orcl/spfile_raw'
*.commit_write='IMMEDIATE','NOWAIT'
改为
spfile='/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora'
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> show parameters spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/10.2.0/db_1/db
s/spfileorcl.ora
9、 修改spfile中控制文件的位置。关闭数据库。
SQL> show parameters control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control1_raw
SQL> alter system set
control_files='/oracle/oradata/orcl/control1.ctl','/oracle/oradata/orcl/control2.ctl','/oracle/oradata/orcl/control3.ctl' scope=spfile;
System altered.
10、 开启数据库，如果有问题则进行恢复。
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> show parameters control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/orcl/control1_
raw, /oracle/oradata/orcl/cont
rol2_raw, /oracle/oradata/orcl
/control3_raw
SQL> alter database open resetlogs;
Database altered.
11、 查看数据库状态
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control1.ctl
/oracle/oradata/orcl/control2.ctl
/oracle/oradata/orcl/control3.ctl
SQL> show parameters spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/10.2.0/db_1/db
s/spfileorcl.ora
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/system01.dbf
/oracle/oradata/orcl/undotbs01.dbf
/oracle/oradata/orcl/sysaux01.dbf
/oracle/oradata/orcl/users01.dbf
/oracle/oradata/orcl/3.dbf
/oracle/oradata/orcl/logmnr.dbf
/oracle/oradata/orcl/stadm.dbf
7 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/orcl/log04.LOG
/oracle/oradata/orcl/log05.LOG
/oracle/oradata/orcl/log06.LOG
12、 对数据库进行备份。
、
||||||||||||||||||||||
How to Convert Datafile from Raw Device to File System [ID 153892.1]
--------------------------------------------------------------------------------
Modified 23-JUN-2002 Type HOWTO Status PUBLISHED
goal: How to convert datafile from raw device to file system
fact: Oracle Server - Enterprise Edition
fix:
Use RMAN to move datafiles from raw devices to file system.
1. Connect to the database:
$ sqlplus system/manager@orcl
2. Put the tablespace with the datafile, which should be converted, offline:
SQL> alter tablespace test_ts offline;
3. Start rman and connect it to the database:
$ rman nocatalog target rman/rman@orcl
4. Move the datafile to file system:
RMAN> run {
2> allocate channel c1 type disk;
3> copy datafile '/dev/raw1' to '/u01/oradata/orcl/test_ts.dbf';
4> }
5. Rename the moved datafile:
SQL> alter database rename file '/dev/raw1' to '/u01/oradata/orcl/test_ts.
dbf';
6. Put the tablespace back online:
SQL> alter tablespace test_ts online;
Notes:
======
1. If you are using RMAN as the backup tool then a backup after the performed
steps is recommended, because otherwise RMAN treats the copied file as a
backup.
2. Usually Oracle datafiles are moved from filesystem to raw devices using
the dd command. Using dd is the fastest method to accomplish it. However, it is
necessary to know how many blocks to skip in the raw device (e.g. it is
necessary to skip 64K on Tru64 Unix), so that the information necessary for
the Operating System is not overwritten. The information on how many blocks to
skip is different on the different platforms. Using RMAN there's no necessity
to know such platform specific information.
相关阅读 更多 +
