首页 / 数据库 / MySQL / 利用RMAN在文件系统与ASM之间迁移数据库

在Oracle10g中在操作系统与ASM之间不能使用复制命令（11g有相关复制命令）比如cp操作来复制文件，为了使用ASM来管理已经存在的数据库必须将数据库迁移到ASM中。可以通过RMAN来完成这项操作。假设在文件系统和ASM磁盘组中有足够的空间来存储整个数据库可以将数据库从文件系统中直接迁移到ASM中。如果在ASM和文件系统同时没有足够的空间来存储整个数据库，可以将数据库备份到磁带，使用旧的磁盘来创建ASM磁盘组并使用磁带备份将数据库还原到ASM中。如果文件系统和ASM同时有足够的空间来存储整个数据库，可以不使用磁带过渡直接将数据库迁移到ASM中。在这个例子中目标ASM磁盘组为+DISK1。在迁移的过程中所有的闪回日志将会被丢弃，因此，数据库中的任何受保护还原点都将变为无效。在执行迁移之前需要将其删除。--------------------------------------推荐阅读 --------------------------------------RMAN 配置归档日志删除策略 http://www.linuxidc.com/Linux/2013-11/92670.htmOracle基础教程之通过RMAN复制数据库 http://www.linuxidc.com/Linux/2013-07/87072.htmRMAN备份策略制定参考内容 http://www.linuxidc.com/Linux/2013-03/81695.htmRMAN备份学习笔记 http://www.linuxidc.com/Linux/2013-03/81892.htmOracle数据库备份加密 RMAN加密 http://www.linuxidc.com/Linux/2013-03/80729.htm--------------------------------------分割线 --------------------------------------将数据库从文件系统迁移到ASM的操作步骤如下:
1.记录数据库迁移之前的数据文件，控制文件，联机重做日志文件的文件名
SQL> select name,status from v$datafile;NAME                                                                            STATUS
-------------------------------------------------------------------------------- -------
/u01/app/oracle/oradata/test/system01.dbf                                        SYSTEM
/u01/app/oracle/oradata/test/undotbs01.dbf                                      ONLINE
/u01/app/oracle/oradata/test/sysaux01.dbf                                        ONLINE
/u01/app/oracle/oradata/test/users01.dbf                                        ONLINE
/u01/app/oracle/oradata/test/example01.dbf                                      ONLINE
/u01/app/oracle/oradata/test/tspitr01.dbf                                        ONLINE
/u01/app/oracle/oradata/test/test01.dbf                                          ONLINE7 rows selectedSQL> select member,status from v$logfile;MEMBER                                                                          STATUS
-------------------------------------------------------------------------------- -------
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo01.logSQL> show parameter control_filesNAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/test/control01.ctl,
                                                /u01/app/oracle/oradata/test/control02.ctl,
                                                /u01/app/oracle/oradata/test/control03.ctl
2.将数据库文件作为副本备份到ASM磁盘组
[oracle@oracle11g ~]$ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss";
[oracle@oracle11g ~]$ rman target sys/zzh_2046@test  catalog rman/rman@jyRecovery Manager: Release 10.2.0.5.0 - Production on Wed Apr 1 16:06:19 2015Copyright （c） 1982, 2007, Oracle.  All rights reserved.connected to target database: TEST （DBID=2168949517）
connected to recovery catalog databaseRMAN> backup as copy incremental level 0 database format "+DISK1" tag "DB_ASM_MIGRATION";Starting backup at 2015-04-01 16:06:27
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
output filename=+DISK1/test/datafile/system.266.875894803 tag=DB_ASM_MIGRATION recid=17 stamp=875894833
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
output filename=+DISK1/test/datafile/sysaux.267.875894845 tag=DB_ASM_MIGRATION recid=18 stamp=875894865
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
output filename=+DISK1/test/datafile/example.265.875894871 tag=DB_ASM_MIGRATION recid=19 stamp=875894883
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
output filename=+DISK1/test/datafile/tspitr.264.875894885 tag=DB_ASM_MIGRATION recid=20 stamp=875894892
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
output filename=+DISK1/test/datafile/undotbs1.263.875894899 tag=DB_ASM_MIGRATION recid=21 stamp=875894903
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
output filename=+DISK1/test/datafile/users.262.875894907 tag=DB_ASM_MIGRATION recid=22 stamp=875894907
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u01/app/oracle/oradata/test/test01.dbf
output filename=+DISK1/test/datafile/test.261.875894909 tag=DB_ASM_MIGRATION recid=23 stamp=875894910
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-04-01 16:08:30Starting Control File and SPFILE Autobackup at 2015-04-01 16:08:30
piece handle=/u02/c-2168949517-20150401-08 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:08:35
ASMCMD> ls -lrt
Type      Redund  Striped  Time            Sys  Name
DATAFILE  UNPROT  COARSE  APR 01 16:00:00  Y    EXAMPLE.265.875894871
DATAFILE  UNPROT  COARSE  APR 01 16:00:00  Y    SYSAUX.267.875894845
DATAFILE  UNPROT  COARSE  APR 01 16:00:00  Y    SYSTEM.266.875894803
DATAFILE  UNPROT  COARSE  APR 01 16:00:00  Y    TEST.261.875894909
DATAFILE  UNPROT  COARSE  APR 01 16:00:00  Y    TSPITR.264.875894885
DATAFILE  UNPROT  COARSE  APR 01 16:00:00  Y    UNDOTBS1.263.875894899
DATAFILE  UNPROT  COARSE  APR 01 16:00:00  Y    USERS.262.875894907
为了确保数据库能执行一致性恢复，归档当前联机重做日
RMAN> sql "alter system archive log current";sql statement: alter system archive log current 如果数据库很大在执行上面的备份时可能会要很长时间。如果在执行备份时有大量的数据库活动，可以对数据库生成一个增量备份在迁移之前将0级备份之后的改变刷新到0级备份所创建的副本中。
RMAN> backup incremental level 1 for recover of copy with tag "DB_ASM_MIGRATION" database;Starting backup at 2015-04-01 16:19:52
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile（s） in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/test/test01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-04-01 16:19:53
channel ORA_DISK_1: finished piece 1 at 2015-04-01 16:20:38
piece handle=/u02/ora_test875895593_1301 tag=DB_ASM_MIGRATION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 2015-04-01 16:20:39Starting Control File and SPFILE Autobackup at 2015-04-01 16:20:39
piece handle=/u02/c-2168949517-20150401-09 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:20:42RMAN> recover copy of database with tag "DB_ASM_MIGRATION";Starting recover at 2015-04-01 16:21:09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=+DISK1/test/datafile/system.266.875894803
recovering datafile copy fno=00002 name=+DISK1/test/datafile/undotbs1.263.875894899
recovering datafile copy fno=00003 name=+DISK1/test/datafile/sysaux.267.875894845
recovering datafile copy fno=00004 name=+DISK1/test/datafile/users.262.875894907
recovering datafile copy fno=00005 name=+DISK1/test/datafile/example.265.875894871
recovering datafile copy fno=00006 name=+DISK1/test/datafile/tspitr.264.875894885
recovering datafile copy fno=00007 name=+DISK1/test/datafile/test.261.875894909
channel ORA_DISK_1: reading from backup piece /u02/ora_test875895593_1301
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875895593_1301 tag=DB_ASM_MIGRATION
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished recover at 2015-04-01 16:21:19Starting Control File and SPFILE Autobackup at 2015-04-01 16:21:19
piece handle=/u02/c-2168949517-20150401-0a comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:21:23 3.在ASM磁盘组中创建一个SPFILE文件副本。将要迁移的数据库SPFILE文件存储为+DISK1/spfile如果数据库是使用的SPFILE启动，就执行以下命令:
RMAN> run
2> {
3> backup as backupset spfile;
4> restore spfile to "+DISK1/spfile";
5> }Starting backup at 2015-04-01 16:24:46
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile（s） in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2015-04-01 16:24:48
channel ORA_DISK_1: finished piece 1 at 2015-04-01 16:24:49
piece handle=/u02/ora_test875895887_1331 tag=TAG20150401T162446 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2015-04-01 16:24:49Starting Control File and SPFILE Autobackup at 2015-04-01 16:24:49
piece handle=/u02/c-2168949517-20150401-0b comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 16:24:51Starting restore at 2015-04-01 16:24:52
using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=+DISK1/spfile
channel ORA_DISK_1: reading from backup piece /u02/c-2168949517-20150401-0b
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/c-2168949517-20150401-0b tag=TAG20150401T162450
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 2015-04-01 16:24:56
ASMCMD> ls -lrt
Type          Redund  Striped  Time            Sys  Name
                                                Y    DB_UNKNOWN/
                                                Y    TEST/
                                                N    spfile => +DISK1/DB_UNKNOWN/PARAMETERFILE/SPFILE.260.875895895
如果数据库是使用PFILE启动，可以在SQL*Plus中执行create spfile命令来创建SPFILE
SQL>create spfile="+DISK1/spfile" from pfile="$ORACLE_HOME/dbs/inittest.ora"File created. 4.如果被迁移的数据库是备库，需要停止管理恢复模式
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
执行一致性关闭
RMAN> SHUTDOWN IMMEDIATE; 
5.在目录/u02/backup中创建一个 pfile参数文件来引用新的SPFILE文件，并重新启动实例到nomount状态
[oracle@oracle11g backup]$ vi inittest_temp.ora
spfile="+DISK1/spfile"RMAN> shutdown immediatedatabase closed
database dismounted
Oracle instance shut downSQL> startup nomount pfile="/u02/backup/inittest_temp.ora"
ORACLE instance started.Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size            109053160 bytes
Database Buffers          54525952 bytes
Redo Buffers                2920448 bytes
6.为了下一步将控制文件迁移到ASM中，修改control_files参数:
SQL> alter system set control_files="+DISK1/control01.ctl" scope=spfile;System altered.
7.通过设置db_recovery_file_dest和db_recovery_file_dest_size来指定闪回区，这里闪回区大小设置为2G（因为这里只是进行迁移测试实验）
SQL> alter system set db_recovery_file_dest_size=2G;System altered.SQL> alter system set db_recovery_file_dest="+DISK1";System altered.
8.将实例重新启动到nomount状态，使用RMAN在ASM中创建新的控制文件（使用原来控制文件来创建）,切换数据文件，并执执恢复
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount pfile="/u02/backup/inittest_temp.ora"
ORACLE instance started.Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size            109053160 bytes
Database Buffers          54525952 bytes
Redo Buffers                2920448 bytesRMAN> restore controlfile from "/u01/app/oracle/oradata/test/control01.ctl";Starting restore at 2015-04-01 17:11:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISKchannel ORA_DISK_1: copied control file copy
output filename=+DISK1/control01.ctl
Finished restore at 2015-04-01 17:11:28
RMAN> alter database mount;database mounted
released channel: ORA_DISK_1RMAN> switch database to copy;datafile 1 switched to datafile copy "+DISK1/test/datafile/system.266.875894803"
datafile 2 switched to datafile copy "+DISK1/test/datafile/undotbs1.263.875894899"
datafile 3 switched to datafile copy "+DISK1/test/datafile/sysaux.267.875894845"
datafile 4 switched to datafile copy "+DISK1/test/datafile/users.262.875894907"
datafile 5 switched to datafile copy "+DISK1/test/datafile/example.265.875894871"
datafile 6 switched to datafile copy "+DISK1/test/datafile/tspitr.264.875894885"
datafile 7 switched to datafile copy "+DISK1/test/datafile/test.261.875894909"
starting full resync of recovery catalog
full resync completeRMAN> recover database;Starting recover at 2015-04-01 17:12:29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISKstarting media recovery
media recovery complete, elapsed time: 00:00:03Finished recover at 2015-04-01 17:12:33
9.将临时文件迁移到ASM，必须为每个临时文件执行set newname命令，然后执行switch命令
RMAN> run
2> {
3> set newname for tempfile "/u01/app/oracle/oradata/test/temp01.dbf" to "+DISK1";
4>switch tempfile all;
5> }executing command: SET NEWNAMErenamed temporary file 1 to +DISK1 in control file
starting full resync of recovery catalog
full resync complete
10.禁用闪回数据库并重新启动闪回数据库让闪回日志创建在ASM中
SQL> alter database flashback off;Database altered.SQL> alter database flashback on;Database altered.
11.改变跟踪文件不会被迁移。只能禁用改变跟踪再重新启用改变跟踪，并将改变跟踪文件存储在ASM中
SQL> alter database disable block change tracking;Database altered.SQL> alter database enable block change tracking using file "+DISK1";Database altered.
12.如果迁移的是主库，打开数据库
SQL> alter database open;Database altered.
如果是备库，恢复管理恢复模式
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2015-04/115773p2.htm

1 2 3 下一页

EXP/IMP 导出生产库表的指定数据到测试库一例Oracle 11g没有备份文件参数文件在异机通过RMAN备份恢复找回被误删的数据相关资讯      RMAN  RMAN迁移数据库  迁移数据库 

RMAN故障一例（归档的备份，从不  （今 20:42） RMAN的FORMATA格式说明  （03月10日） Oracle 11g RMAN复制数据库的测试  （01月19日）

RMAN数据库迁移  （05月22日） 使用RMAN复制恢复开发库环境  （02月17日） Oracle 11g RMAN跨平台传输表空间  （01月19日）

本文评论 查看全部评论 （0）

表情： 姓名： 匿名字数 <div id="footbar"> 版权所有©石家庄振强科技有限公司2024 <a href="https://beian.miit.gov.cn" target="_blank">冀ICP备08103738号-5</a> <a href="/storage/sitemap.xml">网站地图</a> </div> </div> <script type="text/javascript" src="//cpro.baidustatic.com/cpro/ui/cm.js"></script> <script> var _mtj = _mtj || []; (function () { var mtj = document.createElement("script"); mtj.src = "https://node12.aizhantj.com:21233/tjjs/?k=p2tceukth5c"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(mtj, s); })(); </script> </body> </html>