oralce 從檔案系統遷移到ASM

yantaicuiwei發表於2012-08-24
此文是在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  
SQL> conn test/test 
Connected.
測試資料
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
 
檢視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> !
[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 ~]$  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
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
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
將資料庫檔案系統切換到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
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#;
    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/21601207/viewspace-741802/,如需轉載,請註明出處,否則將追究法律責任。

相關文章