Oracle数据库迁移

Oracle数据库迁移 数据迁移 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 鉴于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 bytesFixed Size 1218992 bytesVariable Size 92276304 bytesDatabase Buffers 188743680 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.SQL> select name from v$controlfile; NAME——————————————————————————–/oracle/oradata/orcl/c ontrol1.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.oraSQL> select name from v$datafile; NAME——————————————————————————–/oracle/oradata/orcl/s ystem01.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 bytesFixed Size 1218992 bytesVariable Size 109053520 bytesDatabase Buffers 171966464 bytesRedo Buffers 2973696 bytesDatabase mounted. RMAN> copy datafile ‘/oracle/oradata/orcl/system01.dbf’ to ‘/oracle/oradata/orcl/system01_raw’;Starting backup at 19-JUL-10using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=156 devtype=DISKchannel ORA_DISK_1: starting datafile copyinput datafile fno=00001 name=/oracle/oradata/orcl/system01.dbfoutput filename=/oracle/oradata/orcl/system01_raw tag=TAG20100719T231316 recid=24 stamp=724806895channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45Finished backup at 19-JUL-10 RMAN> copy datafile ‘/oracle/oradata/orcl/undotbs01.dbf’ to ‘/oracle/oradata/orcl/undotbs0_raw’; Starting backup at 19-JUL-10using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbfoutput filename=/oracle/oradata/orcl/undotbs0_raw tag=TAG20100719T231544 recid=25 stamp=724806996channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55Finished backup at 19-JUL-10\ RMAN> copy datafile ‘/oracle/oradata/orcl/sysaux01.dbf’ to ‘/oracle/oradata/orcl/sysaux01_raw’;Starting backup at 20-JUL-10using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbfoutput filename=/oracle/oradata/orcl/sysaux01_raw tag=TAG20100720T001927 recid=26 stamp=724810774channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07Finished backup at 20-JUL-10 RMAN> copy datafile ‘/oracle/oradata/orcl/users01.dbf’ to ‘/oracle/oradata/orcl/users01_raw’;Starting backup at 20-JUL-10using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile fno=00004 name=/oracle/oradata/orcl/users01.dbfoutput filename=/oracle/oradata/orcl/users01_raw tag=TAG20100720T002058 recid=27 stamp=724810859channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 20-JUL-10 RMAN> copy datafile ‘/oracle/oradata/orcl/logmnr.dbf’ to ‘/oracle/oradata/orcl/logmnr_raw’;Starting backup at 20-JUL-10using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile fno=00007 name=/oracle/oradata/orcl/logmnr.dbfoutput filename=/oracle/oradata/orcl/logmnr_raw tag=TAG20100720T002111 recid=28 stamp=724810898channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35Finished backup at 20-JUL-10 RMAN> copy datafile ‘/oracle/oradata/orcl/stadm.dbf’ to ‘/oracle/oradata/orcl/stadm_raw’;Starting backup at 20-JUL-10using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKchannel ORA_DISK_1: starting datafile copyinput datafile fno=00008 name=/oracle/oradata/orcl/stadm.dbfoutput filename=/oracle/oradata/orcl/stadm_raw tag=TAG20100720T002413 recid=29 stamp=724811080channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35Finished 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 bytesFixed Size 1218992 bytesVariable Size 96470608 bytesDatabase Buffers 184549376 bytesRedo Buffers 2973696 bytesDatabase mounted. SQL> select name from v$datafile; NAME——————————————————————————–/oracle/oradata/orcl/s ystem01.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 bytesFixed Size 1218992 bytesVariable Size 96470608 bytesDatabase Buffers 184549376 bytesRedo Buffers 2973696 bytesDatabase 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.oraspfile=’/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 bytesFixed Size 1218992 bytesVariable Size 96470608 bytesDatabase Buffers 184549376 bytesRedo 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 7control_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 bytesFixed Size 1218992 bytesVariable Size 96470608 bytesDatabase Buffers 184549376 bytesRedo Buffers 2973696 bytesDatabase 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 bytesFixed Size 1218992 bytesVariable Size 96470608 bytesDatabase Buffers 184549376 bytesRedo Buffers 2973696 bytesDatabase 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 1ORA-00289: suggestion :/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_1_%u_.arcORA-00280: change 2375554 for thread 1 is in sequence #1 Specify log: {=suggested | filename | AUTO | CANCEL}autoORA-00279: change 2375827 generated at 07/20/2010 00:28:40 needed for thread 1ORA-00289: suggestion :/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_2_%u_.arcORA-00280: change 2375827 for thread 1 is in sequence #2ORA-00278: log file’/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_1_648z9rmq_.arc’ no longerneeded 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 statusLinux Error: 2: No such file or directoryAdditional information: 3 SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-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 1ORA-00289: suggestion :/oracle/oradata/ORCL/archivelog/2010_07_20/o1_mf_1_2_%u_.arcORA-00280: change 2375827 for thread 1 is in sequence #2 Specify log: {=suggested | filename | AUTO | CANCEL}/oracle/oradata/orcl/redo1_rawLog 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_rawSQL> 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 bytesFixed Size 1218992 bytesVariable Size 96470608 bytesDatabase Buffers 184549376 bytesRedo Buffers 2973696 bytesDatabase mounted.SQL> select name from v$controlfile; NAME——————————————————————————–/oracle/oradata/orcl/c ontrol1_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/ora data/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_raw7 rows selected. SQL> select name from v$controlfile; NAME——————————————————————————–/oracle/oradata/orcl/c ontrol1_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 wSQL> 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 bytesFixed Size 1218992 bytesVariable Size 109053520 bytesDatabase Buffers 171966464 bytesRedo Buffers 2973696 bytesDatabase 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 1alter database add logfile member ‘/oracle/oradata/orcl/log02.LOG’ to group 2alter database add logfile member ‘/oracle/oradata/orcl/log03.LOG’ to group 3alter 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 dropORA-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> startupORACLE instance started. Total System Global Area 285212672 bytesFixed Size 1218992 bytesVariable Size 92276304 bytesDatabase Buffers 188743680 bytesRedo Buffers 2973696 bytesDatabase 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.oraspfile=’/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 bytesFixed Size 1218992 bytesVariable Size 92276304 bytesDatabase Buffers 188743680 bytesRedo Buffers 2973696 bytesDatabase 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 controlNAME TYPE VALUE———————————— ———– ——————————control_file_record_keep_time integer 7control_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 openDatabase dismounted.ORACLE instance shut down.SQL> SQL> startup mount;ORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218992 bytesVariable Size 109053520 bytesDatabase Buffers 171966464 bytesRedo Buffers 2973696 bytesDatabase mounted.SQL> show parameters control;NAME TYPE VALUE———————————— ———– ——————————control_file_record_keep_time integer 7control_files string /oracle/oradata/orcl/control1_ raw, /oracle/oradata/orcl/cont rol2_raw, /oracle/oradata/orcl /control3_rawSQL> alter database open resetlogs; Database altered. 11、 查看数据库状态SQL> select name from v$controlfile; NAME——————————————————————————–/oracle/oradata/orcl/c ontrol1.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.oraSQL> select name from v$datafile; NAME——————————————————————————–/oracle/oradata/orcl/s ystem01.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 systemfact: 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.