ASM檔案系統遷移

YallonKing發表於2012-08-21

此文是在oracle10g的基礎上，將資料庫從檔案系統遷移到ASM管理的檔案系統上。

檢視原始檔系統

SQL> select * from v$version where rownum<5;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production

SQL> conn test/test
Connected.

測試資料
SQL> create table test (id number,name varchar2(10));

Table created.

SQL> insert into test values (1,'yallonking');

1 row created.

SQL> commit;

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10gr2/users01.dbf
/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
/u01/app/oracle/oradata/ora10gr2/system01.dbf
/u01/app/oracle/oradata/ora10gr2/test01.dbf

檢視ASM磁碟組
SQL> select * from v$version where rownum<5;

SQL> !
[oracle@rhl5 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 20480 20430 0 20430 0 DATA/

------------------------

檢視當前檔案系統的控制檔案路徑
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10gr2/control01.ctl
/u01/app/oracle/oradata/ora10gr2/control02.ctl
/u01/app/oracle/oradata/ora10gr2/control03.ctl

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

Total System Global Area 608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             155193108 bytes
Database Buffers          448790528 bytes
Redo Buffers                2969600 bytes
SQL> !

轉移控制檔案
[oracle@rhl5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 00:56:47 2012

connected to target database: ora10gr2 (not mounted)

RMAN> restore controlfile to '+DATA' from '/u01/app/oracle/oradata/ora10gr2/control01.ctl';

Starting restore at 21-AUG-12
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 21-AUG-12

檢視新的控制檔案
ASMCMD> pwd
+data
ASMCMD> find -t controlfile . *
+data/ORA10GR2/CONTROLFILE/backup.256.791859655

修改當前系統的控制檔案引數相關
SQL> alter system set control_files='+data/ORA10GR2/CONTROLFILE/backup.256.791859655' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             155193108 bytes
Database Buffers          448790528 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> !

將資料庫檔案系統備份到ASM磁碟組
[oracle@rhl5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 01:19:21 2012

connected to target database: ORA10GR2 (DBID=4093928674, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 21-AUG-12
using target database control file instead of recovery catalog
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/ora10gr2/system01.dbf
output filename=+DATA/ora10gr2/datafile/system.257.791860783 tag=TAG20120821T011941 recid=1 stamp=791860831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
output filename=+DATA/ora10gr2/datafile/sysaux.258.791860837 tag=TAG20120821T011941 recid=2 stamp=791860867
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
output filename=+DATA/ora10gr2/datafile/undotbs1.259.791860873 tag=TAG20120821T011941 recid=3 stamp=791860876
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/ora10gr2/test01.dbf
output filename=+DATA/ora10gr2/datafile/test.260.791860881 tag=TAG20120821T011941 recid=4 stamp=791860882
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/ora10gr2/users01.dbf
output filename=+DATA/ora10gr2/datafile/users.261.791860885 tag=TAG20120821T011941 recid=5 stamp=791860885
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/ora10gr2/controlfile/backup.262.791860885 tag=TAG20120821T011941 recid=6 stamp=791860886
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
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 21-AUG-12
channel ORA_DISK_1: finished piece 1 at 21-AUG-12
piece handle=+DATA/ora10gr2/backupset/2012_08_21/nnsnf0_tag20120821t011941_0.263.791860889 tag=TAG20120821T011941 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 21-AUG-12

將資料庫檔案系統切換到ASM磁碟組
RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/ora10gr2/datafile/system.257.791860783"
datafile 2 switched to datafile copy "+DATA/ora10gr2/datafile/undotbs1.259.791860873"
datafile 3 switched to datafile copy "+DATA/ora10gr2/datafile/sysaux.258.791860837"
datafile 4 switched to datafile copy "+DATA/ora10gr2/datafile/users.261.791860885"
datafile 5 switched to datafile copy "+DATA/ora10gr2/datafile/test.260.791860881"

修改臨時檔案
/*
run {
set newname for tempfile 1 to '+DATA';
set newname for tempfile 2 to '+DATA';
...
switch tempfile all;
}
*/

RMAN> ALTER DATABASE OPEN;

database opened

逐步替換日誌檔案
[oracle@rhl5 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 21 01:27:35 2012

SQL> conn /as sysdba
Connected.
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
3
/u01/app/oracle/oradata/ora10gr2/redo03.log
INACTIVE

2
/u01/app/oracle/oradata/ora10gr2/redo02.log
UNUSED

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------

1
/u01/app/oracle/oradata/ora10gr2/redo01.log
CURRENT

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                        STATUS
---------- --------------------------------------------- ----------------
         3 /u01/app/oracle/oradata/ora10gr2/redo03.log   INACTIVE
         2 /u01/app/oracle/oradata/ora10gr2/redo02.log   CURRENT
         1 /u01/app/oracle/oradata/ora10gr2/redo01.log   INACTIVE
         1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
           9

2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 CURRENT
7

3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 INACTIVE
3

GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------

6 rows selected.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo03.log';

Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo01.log';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                        STATUS
---------- --------------------------------------------- ----------------
         2 /u01/app/oracle/oradata/ora10gr2/redo02.log   INACTIVE
         1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
           9

2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
7

3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo02.log';

Database altered.

驗證結果
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                        STATUS
---------- --------------------------------------------- ----------------
         1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
           9

2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
7

3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/ora10gr2/datafile/users.261.791860885
+DATA/ora10gr2/datafile/sysaux.258.791860837
+DATA/ora10gr2/datafile/undotbs1.259.791860873
+DATA/ora10gr2/datafile/system.257.791860783
+DATA/ora10gr2/datafile/test.260.791860881

SQL> conn test/test
Connected.
SQL> select * from test;

ID NAME
---------- ----------
1 yallonking

至此，全部OK！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-741521/，如需轉載，請註明出處，否則將追究法律責任。

asm 檔案系統遷移
2012-08-28
ASM
如何遷移ASM資料檔案到檔案系統
2011-11-10
ASM
使用RMAN遷移檔案系統資料庫到ASM
2013-12-05
資料庫ASM
遷移檔案系統管理下的db到asm下
2009-10-11
ASM
ASM下遷移控制檔案
2013-05-16
ASM
用rman從檔案系統遷移資料庫到asm
2014-01-14
資料庫ASM
從檔案系統遷移到ASM上
2018-02-05
ASM
oralce 從檔案系統遷移到ASM
2012-08-24
ASM
用RMAN遷移檔案到ASM或從ASM遷出
2011-01-20
ASM
四、用rman從檔案系統遷移資料庫到asm
2012-06-07
資料庫ASM
使用RAM將檔案系統遷移到ASM
2014-10-17
ASM
利用RMAN在檔案系統與ASM之間遷移資料庫
2015-04-01
ASM資料庫
遷移資料庫檔案到ASM
2015-09-17
資料庫ASM
遷移和移動 UNIX 檔案系統(轉)
2007-08-10
將spfile從ASM裡遷移到檔案系統
2018-06-27
ASM
資料庫從檔案系統遷移到ASM
2017-11-29
資料庫ASM
移動資料檔案從ASM到檔案系統
2011-09-16
ASM
把檔案系統的資料檔案遷移到ASM儲存
2014-05-14
ASM
將資料庫從ASM遷移到檔案系統
2014-03-01
資料庫ASM
將資料庫從檔案系統遷移到ASM
2014-03-01
資料庫ASM
【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM
2016-11-03
資料庫ASM
ASM與檔案系統之間copy資料檔案--檔案系統到ASM
2014-03-06
ASM
移動資料檔案從檔案系統到ASM磁碟組中
2011-09-16
ASM
Oracle 11g單例項ASM遷移到檔案系統
2015-03-16
Oracle單例ASM
利用RMAN將資料庫從檔案系統遷移到ASM
2011-05-10
資料庫ASM
FastDFS檔案系統遷移和資料恢復
2021-07-07
AST資料恢復
利用rman遷移裸裝置資料檔案到檔案系統
2010-06-08
asm拷貝檔案到檔案系統
2013-12-18
ASM
實現資料庫由檔案系統遷移到 ASM 磁碟組中
2015-12-02
資料庫ASM
透過xtts遷移單例項檔案系統表空間到RAC ASM儲存表空間
2016-11-23
TTS單例ASM
通過xtts遷移單例項檔案系統表空間到RAC ASM儲存表空間
2016-04-21
TTS單例ASM
oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組
2014-05-28
OracleASM
將ASM裡面的檔案copy到檔案系統
2017-01-19
ASM
遷移ASM磁碟組
2019-11-18
ASM
ASM下遷移spfile
2018-06-27
ASM
利用RMAN將資料庫從檔案系統遷移到ASM(單例項)
2013-04-08
資料庫ASM單例
資料檔案遷移
2014-04-15
ASM叢集檔案系統ACFS（ASM Cluster File System）
2020-01-22
ASM

ASM檔案系統遷移

相關文章