oralce 從檔案系統遷移到ASM
此文是在oracle10g的基礎上,將從到管理的檔案系統上。
檢視原始檔系統
SQL> select * from v$version where rownum<5;
BANNER
----------------------------------------------------------------
Database 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
----------------------------------------------------------------
Database 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.
Connected.
測試資料
SQL> create test (id number,name varchar2(10));
SQL> create 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
--------------------------------------------------------------------------------
/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> 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
----------------------------------------------------------------
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> !
[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/
[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;
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
--------------------------------------------------------------------------------
/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.
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> !
Fixed Size 1220844 bytes
Variable Size 155193108 bytes
Database Buffers 448790528 bytes
Redo Buffers 2969600 bytes
SQL> !
轉移控制檔案
[oracle@rhl5 ~]$ target /
[oracle@rhl5 ~]$ target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 00:56:47 2012
Copyright (c) 1982, , Oracle. All rights reserved.
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
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 21-AUG-12
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;
SQL> alter system set control_files='+data/ORA10GR2/CONTROLFILE/backup.256.791859655' scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
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> !
Fixed Size 1220844 bytes
Variable Size 155193108 bytes
Database Buffers 448790528 bytes
Redo Buffers 2969600 bytes
Database mounted.
SQL> !
將資料庫檔案系統到ASM磁碟組
[oracle@rhl5 ~]$ rman target /
[oracle@rhl5 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 01:19:21 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
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;
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"
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
[oracle@rhl5 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 21 01:27:35 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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#;
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
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
3
/u01/app/oracle/oradata/ora10gr2/redo03.log
INACTIVE
2
/u01/app/oracle/oradata/ora10gr2/redo02.log
UNUSED
/u01/app/oracle/oradata/ora10gr2/redo02.log
UNUSED
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
1
/u01/app/oracle/oradata/ora10gr2/redo01.log
CURRENT
/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
---------- --------------------------------------------- ----------------
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
7
3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 INACTIVE
3
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 /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
7
3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3
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
---------- --------------------------------------------- ----------------
1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
9
2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
7
7
3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
3
SQL> select file_name from dba_data_files;
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
--------------------------------------------------------------------------------
+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;
Connected.
SQL> select * from test;
ID NAME
---------- ----------
1 yallonking
---------- ----------
1 yallonking
至此,全部OK!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21601207/viewspace-741802/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 從檔案系統遷移到ASM上ASM
- 將spfile從ASM裡遷移到檔案系統ASM
- 資料庫從檔案系統遷移到ASM資料庫ASM
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- 使用RAM將檔案系統遷移到ASMASM
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- 把檔案系統的資料檔案遷移到ASM儲存ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM(單例項)資料庫ASM單例
- oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組OracleASM
- Oracle 11g單例項ASM遷移到檔案系統Oracle單例ASM
- oracle將控制檔案從裸裝置遷移到檔案系統Oracle
- ASM檔案系統遷移ASM
- asm 檔案系統遷移ASM
- 實現資料庫由檔案系統遷移到 ASM 磁碟組中資料庫ASM
- 遷移到ASMASM
- 利用rman將本地資料檔案遷移到asmASM
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- Azure VM從ASM遷移到ARM(二)ASM
- Azure VM從ASM遷移到ARM(一)ASM
- 用RMAN遷移檔案到ASM或從ASM遷出ASM
- 如何遷移ASM資料檔案到檔案系統ASM
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- 採用Duplicate不連線target資料庫的方式將檔案系統遷移到ASM資料庫ASM
- 移動資料檔案從ASM到檔案系統ASM
- 從ASM磁碟中複製檔案到本地檔案系統ASM
- 資料庫遷移到ASM資料庫ASM
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 遷移檔案系統管理下的db到asm下ASM
- ASM與檔案系統之間copy資料檔案--檔案系統到ASMASM
- RAC 10g下從裸裝置遷移到ASMASM
- 10G下從ASM複製檔案到檔案系統ASM
- 移動資料檔案從檔案系統到ASM磁碟組中ASM
- asm拷貝檔案到檔案系統ASM
- ASM下遷移控制檔案ASM
- 從Perforce遷移到GitGit
- 利用RMAN在檔案系統與ASM之間遷移資料庫ASM資料庫
- 將ASM裡面的檔案copy到檔案系統ASM