ITPub博客

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

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

原创 Oracle 作者:eric0435 时间:2015-04-01 23:37:01 0 删除 编辑

在Oracle10g中在操作系统与ASM之间不能使用复制命令(11g有相关复制命令)比如cp操作来复制文件,为了使用ASM来管理已经存在的数据库必须将数据库迁移到ASM中。可以通过RMAN来完成这项操作。假设在文件系统和ASM磁盘组中有足够的空间来存储整个数据库可以将数据库从文件系统中直接迁移到ASM中。如果在ASM和文件系统同时没有足够的空间来存储整个数据库,可以将数据库备份到磁带,使用旧的磁盘来创建ASM磁盘组并使用磁带备份将数据库还原到ASM中。

如果文件系统和ASM同时有足够的空间来存储整个数据库,可以不使用磁带过渡直接将数据库迁移到ASM中。在这个例子中目标ASM磁盘组为+DISK1。在迁移的过程中所有的闪回日志将会被丢弃,因此,数据库中的任何受保护还原点都将变为无效。在执行迁移之前需要将其删除。

将数据库从文件系统迁移到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                                          ONLINE

7 rows selected

SQL> 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.log

SQL> show parameter control_files

NAME                                 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@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Apr 1 16:06:19 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

RMAN> 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:30

Starting 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:39

Starting 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:42

RMAN> 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:19

Starting 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:49

Starting 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:51

Starting restore at 2015-04-01 16:24:52
using channel ORA_DISK_1

channel 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 immediate

database closed
database dismounted
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 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 bytes

RMAN> 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=DISK

channel 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_1

RMAN> 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 complete

RMAN> recover database;

Starting recover at 2015-04-01 17:12:29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished 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 NEWNAME

renamed 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;

13.对于主库,要迁移联机重做日志文件是通过增加新的重做日志文件并存储在ASM中并删除旧的重做日志文件。对于备库,就是删除旧的备重做日志并增加新的备重做日志并存储在ASM中

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS            FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ----------------  ------------- ------------
         1          1         97   52428800          1 NO  CURRENT            1288360      01-APR-15
         2          1         95   52428800          1 YES INACTIVE           1226685      30-MAR-15
         3          1         96   52428800          1 YES INACTIVE           1268619      31-MAR-15

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '+DISK1/redo02.log' size 50M;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '+DISK1/redo03.log' size 50M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance test (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test/redo01.log'


SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '+DISK1/redo01.log' size 50M;

Database altered.

将数据库从ASM中迁移到文件系统
1.记录数据库迁移之前的数据文件,控制文件,联机重做日志文件的文件名

SQL> select member,status from v$logfile;

MEMBER                                                                           STATUS
-------------------------------------------------------------------------------- -------
+DISK1/redo03.log
+DISK1/redo02.log
+DISK1/redo01.log

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DISK1/control01.ctl

SQL> select name,status from v$datafile;

NAME                                                                             STATUS
-------------------------------------------------------------------------------- -------
+DISK1/test/datafile/system.266.875894803                                        SYSTEM
+DISK1/test/datafile/undotbs1.263.875894899                                      ONLINE
+DISK1/test/datafile/sysaux.267.875894845                                        ONLINE
+DISK1/test/datafile/users.262.875894907                                         ONLINE
+DISK1/test/datafile/example.265.875894871                                       ONLINE
+DISK1/test/datafile/tspitr.264.875894885                                        ONLINE
+DISK1/test/datafile/test.261.875894909                                          ONLINE

7 rows selected

2.将数据库文件作为副本备份到ASM磁盘组

RMAN> backup as copy incremental level 0 database format '/u02/asm_backup/%U' tag 'NO_ASM_MIGRATION';

Starting backup at 2015-04-01 20:47:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DISK1/test/datafile/system.266.875894803
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5 tag=NO_ASM_MIGRATION recid=46 stamp=875911696
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DISK1/test/datafile/sysaux.267.875894845
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj tag=NO_ASM_MIGRATION recid=47 stamp=875911724
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DISK1/test/datafile/example.265.875894871
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm tag=NO_ASM_MIGRATION recid=48 stamp=875911742
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DISK1/test/datafile/tspitr.264.875894885
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5 tag=NO_ASM_MIGRATION recid=49 stamp=875911756
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DISK1/test/datafile/undotbs1.263.875894899
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik tag=NO_ASM_MIGRATION recid=50 stamp=875911766
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DISK1/test/datafile/users.262.875894907
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin tag=NO_ASM_MIGRATION recid=51 stamp=875911768
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DISK1/test/datafile/test.261.875894909
output filename=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio tag=NO_ASM_MIGRATION recid=52 stamp=875911769
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-04-01 20:49:29

Starting Control File and SPFILE Autobackup at 2015-04-01 20:49:30
piece handle=/u02/c-2168949517-20150401-16 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:49:34


[oracle@oracle11g asm_backup]$ ls -lrt
total 1014840
-rw-r----- 1 oracle oinstall 482353152 Apr  1 20:48 data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5
-rw-r----- 1 oracle oinstall 293609472 Apr  1 20:48 data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:49 data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:49 data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5
-rw-r----- 1 oracle oinstall  31465472 Apr  1 20:49 data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:49 data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:49 data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio

为了确保数据库能执行一致性恢复,归档当前联机重做日

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 'NO_ASM_MIGRATION' database;

Starting backup at 2015-04-01 20:51:43
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=+DISK1/test/datafile/system.266.875894803
input datafile fno=00003 name=+DISK1/test/datafile/sysaux.267.875894845
input datafile fno=00005 name=+DISK1/test/datafile/example.265.875894871
input datafile fno=00006 name=+DISK1/test/datafile/tspitr.264.875894885
input datafile fno=00002 name=+DISK1/test/datafile/undotbs1.263.875894899
input datafile fno=00004 name=+DISK1/test/datafile/users.262.875894907
input datafile fno=00007 name=+DISK1/test/datafile/test.261.875894909
channel ORA_DISK_1: starting piece 1 at 2015-04-01 20:51:44
channel ORA_DISK_1: finished piece 1 at 2015-04-01 20:52:19
piece handle=/u02/ora_test875911904_1631 tag=NO_ASM_MIGRATION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 2015-04-01 20:52:19

Starting Control File and SPFILE Autobackup at 2015-04-01 20:52:19
piece handle=/u02/c-2168949517-20150401-17 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:52:23


RMAN> recover copy of database with tag 'NO_ASM_MIGRATION';

Starting recover at 2015-04-01 20:52:57
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=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5
recovering datafile copy fno=00002 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik
recovering datafile copy fno=00003 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj
recovering datafile copy fno=00004 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin
recovering datafile copy fno=00005 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm
recovering datafile copy fno=00006 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5
recovering datafile copy fno=00007 name=/u02/asm_backup/data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio
channel ORA_DISK_1: reading from backup piece /u02/ora_test875911904_1631
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875911904_1631 tag=NO_ASM_MIGRATION
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2015-04-01 20:53:00

Starting Control File and SPFILE Autobackup at 2015-04-01 20:53:00
piece handle=/u02/c-2168949517-20150401-18 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:53:04

3.在/u02/asm_backup目录中创建一个SPFILE文件副本。将要迁移的数据库SPFILE文件存储为
/u02/asm_backup/spfiletest.ora如果数据库是使用的SPFILE启动,就执行以下命令:

RMAN> run
2> {
3> backup as backupset spfile;
4> restore spfile to '/u02/asm_backup/spfiletest.ora';
5> }

Starting backup at 2015-04-01 20:55:31
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 20:55:32
channel ORA_DISK_1: finished piece 1 at 2015-04-01 20:55:33
piece handle=/u02/ora_test875912132_1661 tag=TAG20150401T205531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-04-01 20:55:33

Starting Control File and SPFILE Autobackup at 2015-04-01 20:55:33
piece handle=/u02/c-2168949517-20150401-19 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-04-01 20:55:36

Starting restore at 2015-04-01 20:55:38
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=/u02/asm_backup/spfiletest.ora
channel ORA_DISK_1: reading from backup piece /u02/c-2168949517-20150401-19
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/c-2168949517-20150401-19 tag=TAG20150401T205533
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2015-04-01 20:55:39

[oracle@oracle11g asm_backup]$ ls -lrt
total 1014844
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:52 data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin
-rw-r----- 1 oracle oinstall  31465472 Apr  1 20:52 data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:52 data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5
-rw-r----- 1 oracle oinstall  10493952 Apr  1 20:52 data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio
-rw-r----- 1 oracle oinstall 482353152 Apr  1 20:52 data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5
-rw-r----- 1 oracle oinstall 293609472 Apr  1 20:52 data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj
-rw-r----- 1 oracle oinstall 104865792 Apr  1 20:52 data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm
-rw-r----- 1 oracle oinstall      2560 Apr  1 20:55 spfiletest.ora

如果数据库是使用PFILE启动,可以在SQL*Plus中执行create spfile命令来创建SPFILE

SQL>create spfile='/u02/asm_backup/spfiletest.ora' from pfile='+DISK1/inittest.ora'

File created.

4.如果被迁移的数据库是备库,需要停止管理恢复模式

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

执行一致性关闭

RMAN> SHUTDOWN IMMEDIATE;

5.在目录/u02/asm_backup中创建一个 pfile参数文件来引用新的SPFILE文件,并重新启动实例到nomount状态。

[oracle@oracle11g asm_backup]$ vi inittest_temp.ora
spfile='/u02/asm_backup/spfiletest.ora'


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.



SQL> startup nomount pfile='/u02/asm_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.为了下一步将控制文件迁移到文件系统中,修改control_files参数:

SQL> alter system set control_files='/u02/asm_backup/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='/u01/app/oracle/oradata/flash_recovery_area' scope=spfile;

System altered.

8.将实例重新启动到nomount状态,使用RMAN在文件系统中创建新的控制文件(使用原来控制文件来创建),切换数据文件,并执执恢复

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SSQL> startup nomount pfile='/u02/asm_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

RMAN> restore controlfile from '+DISK1/control01.ctl';

Starting restore at 2015-04-01 21:17:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u02/asm_backup/control01.ctl
Finished restore at 2015-04-01 21:18:04




RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSTEM_FNO-1_4rq3amf5"
datafile 2 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-UNDOTBS1_FNO-2_4vq3amik"
datafile 3 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-SYSAUX_FNO-3_4sq3amgj"
datafile 4 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-USERS_FNO-4_50q3amin"
datafile 5 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-EXAMPLE_FNO-5_4tq3amhm"
datafile 6 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_4uq3ami5"
datafile 7 switched to datafile copy "/u02/asm_backup/data_D-TEST_I-2168949517_TS-TEST_FNO-7_51q3amio"
starting full resync of recovery catalog
full resync complete

RMAN> recover database;

Starting recover at 2015-04-01 21:18:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 2015-04-01 21:18:41

8.将临时文件迁移到ASM,必须为每个临时文件执行set newname命令,然后执行switch命令

RMAN> run
2> {
3> set newname for tempfile '+DISK1/TEST/TEMPFILE/TEMP.256.875908789' to '/u02/asm_backup/temp01.dbf';
4> switch tempfile all;
5> }

executing command: SET NEWNAME

renamed temporary file 1 to /u02/asm_backup/temp01.dbf in control file
starting full resync of recovery catalog
full resync complete

9.禁用闪回数据库并重新启动闪回数据库让闪回日志创建在ASM中

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.


10.如果迁移的是主库,打开数据库

SQL> alter database open;

Database altered.

如果是备库,恢复管理恢复模式

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

11.对于主库,要迁移联机重做日志文件是通过增加新的重做日志文件并存储在文件系统中并删除旧的重做日志文件。对于备库,就是删除旧的备重做日志并增加新的备重做日志并存储在文件系统中。
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1         99   52428800          1 NO       CURRENT                1292113 2015/4/1 20
         2          1         98   52428800          1 YES      INACTIVE               1290567 2015/4/1 20
         3          1          0   52428800          1 YES      UNUSED                       0


SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '/u02/asm_backup/redo02.log' size 50M;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '/u02/asm_backup/redo03.log' size 50M;

Database altered.

SQL> alter system switch logfile;

System altered.


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '+DISK1/redo01.log'

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '/u02/asm_backup/redo01.log' size 50M;

Database altered.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-1482364/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
系统架构师,ACOUG MEMBER,Oracle ACE,Mail:yongjing.star@gmail.com Mobile:13875984558 QQ:409898894 Blog:www.jydba.net

注册时间:2011-10-12

  • 博文量
    527
  • 访问量
    6148649