利用RMAN在檔案系統與ASM之間遷移資料庫
在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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM(單例項)資料庫ASM單例
- 【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM資料庫ASM
- 使用RMAN在ASM和檔案系統之間複製資料ASM
- 使用RMAN在ASM和檔案系統之間拷貝資料ASM
- 利用rman遷移裸裝置資料檔案到檔案系統
- ASM與檔案系統之間copy資料檔案--檔案系統到ASMASM
- 使用rman在oracle ASM磁碟組之間移動資料檔案OracleASM
- 利用rman將本地資料檔案遷移到asmASM
- 如何遷移ASM資料檔案到檔案系統ASM
- 遷移資料庫檔案到ASM資料庫ASM
- ASM檔案系統遷移ASM
- asm 檔案系統遷移ASM
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 資料庫從檔案系統遷移到ASM資料庫ASM
- 【資料遷移】RMAN遷移資料庫到ASM(三)遷移onlinelog等到ASM資料庫ASM
- 三、rman 資料庫遷移--從檔案系統到裸裝置資料庫
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- 用RMAN遷移檔案到ASM或從ASM遷出ASM
- 在ASM Diskgroup間移動資料檔案ASM
- 利用RMAN跨平臺遷移資料庫資料庫
- 達夢資料庫系統表空間資料檔案遷移過程資料庫
- 利用RMAN將非ASM檔案移動到ASM裡 - [ASM]ASM
- 一、rman 資料庫遷移--從檔案系統到檔案系統用預設的備份路徑資料庫
- 【遷移】使用rman遷移資料庫資料庫
- 在ASM磁碟組之間移動檔案ASM
- Oracle 利用RMAN 完成資料遷移Oracle
- 三、rman 資料庫遷移--從檔案系統到裸裝置 用dd複製控制檔案資料庫
- 移動資料檔案從ASM到檔案系統ASM
- 遷移資料庫到ASM資料庫ASM
- 把檔案系統的資料檔案遷移到ASM儲存ASM
- 用rman遷移資料庫資料庫
- 資料庫檔案的遷移資料庫
- 使用RMAN執行oracle ASM資料遷移OracleASM