使用rman將資料庫遷移到ASM例項
使用rman將資料庫遷移到ASM例項
一、建立ASM例項並啟動ASM例項；
我們可以手動建立ASM例項，也可以使用DBCA來建立ASM例項；
[oracle@10g ~]$ export ORACLE_SID=+ASM
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 14 00:15:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted
SQL> select status from v$instance;
STATUS
------------------------------------
STARTED
Elapsed: 00:00:00.02
二、建立ASM磁碟；
Disk /dev/sdb: 7516 MB, 7516192768 bytes
255 heads, 63 sectors/track, 913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 366 2939863+ 83 Linux
/dev/sdb2 367 732 2939895 83 Linux
/dev/sdb3 733 913 1453882+ 83 Linux
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk DATA /dev/sdb1
Marking disk "DATA" as an ASM disk: [ OK ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk RECOVERY /dev/sdb2
Marking disk "RECOVERY" as an ASM disk: [ OK ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk LOG /dev/sdb3
Marking disk "LOG" as an ASM disk: [ OK ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm listdisks
DATA
LOG
RECOVERY
三、登入到ASM建立磁碟組
[oracle@10g ~]$ export ORACLE_SID=+ASM
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 14 00:15:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1272120 bytes
Variable Size 57448136 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted （由於沒有建立diskgroup，所以這裡報錯）
以下建立diskgroup
SQL> create diskgroup DATA external redundancy disk 'ORCL:DATA';
Diskgroup created.
SQL> create diskgroup RECOVERY external redundancy disk 'ORCL:RECOVERY';
Diskgroup created.
SQL> create diskgroup LOG external redundancy disk 'ORCL:LOG';
Diskgroup created.
SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1272120 bytes
Variable Size 57448136 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
使用v$asm_diskgroup檢視當前ASM磁碟組的狀態，以及型別；
SQL> col NAME for a20;
SQL> select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB
------------ -------------------- --------------------------------- ------------------ ----------
1 DATA MOUNTED EXTERN 2870
2 LOG MOUNTED EXTERN 1419
3 RECOVERY MOUNTED EXTERN 2870
SQL> col PATH for a30;
SQL> select GROUP_NUMBER,DISK_NUMBER,PATH from v$asm_disk;
GROUP_NUMBER DISK_NUMBER PATH
------------ ----------- ------------------------------
1 0 ORCL:DATA
2 0 ORCL:LOG
3 0 ORCL:RECOVERY
使用兩個檢視聯合檢視；
SQL> col NAME for a20;
SQL> col PATH for a20;
SQL> col STATE for a20;
SQL> select g.group_number,d.name,d.path,g.state,g.type
2 from v$asm_diskgroup g,v$asm_disk d
3 where g.group_number=d.group_number;
GROUP_NUMBER NAME PATH STATE TYPE
------------ -------------------- -------------------- -------------------- ------------------
1 DATA ORCL:DATA MOUNTED EXTERN
2 LOG ORCL:LOG MOUNTED EXTERN
3 RECOVERY ORCL:RECOVERY MOUNTED EXTERN
使用ASMCMD檢視磁碟；
[oracle@10g ~]$ echo $ORACLE_SID
+ASM
[oracle@10g ~]$ asmcmd
ASMCMD> ls
DATA/
LOG/
RECOVERY/
四、使用RMAN將資料庫遷移到ASM例項；
1）修改SPFILE引數檔案，在SPFILE引數中增加幾個引數以及相應的值。
新增引數的內容：
control_files='+DATA'
DB_CREATE_FILE_DEST='+DATA'
DB_RECOVERY_FILE_DEST='+RECOVERY'
DB_RECOVERY_FILE_DEST_SIZE=3g
DB_CREATE_ONLINE_LOG_DEST_1='+LOG'
實際上ASM使用的是OMF檔案系統。所以必須設定這些引數。下面分別介紹這四個引數的含義；
*DB_CREATE_FILE_DEST:這個引數指定了資料檔案、控制檔案、重做日誌檔案以及臨時檔案的預設建立位置，如果沒有設定DB_CREATE_ONLINE_LOG_DEST_n引數，會在引數
DB_CREATER_FILE_DEST指定的磁碟組中建立重做日誌檔案和控制檔案。
*DB_RECOVERY_FILE_DEST:該引數指定了RMAN備份、閃回日誌以及歸檔日誌的儲存位置，如果沒有設定引數DB_CREATE_ONLINE_LOG_DEST_n，則會
在DB_RECOVERY_FILE_DEST引數指定的目錄下建立一個重做日誌檔案。
*DB_RECOVERY_FILE_DEST_SIZE:該引數設定快閃恢復區的大小。
*DB_CREATE_ONLINE_LOG_DEST_n：該引數儲存重做日誌和控制檔案的副本，實現多路複用。
修改ORACLE_SID登入例項名為資料庫系統名（如:ORCL）
[oracle@10g ~]$ echo $ORACLE_SID
orcl
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Aug 14 23:21:50 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 125829700 bytes
Database Buffers 155189248 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
SQL> show parameter db_create_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_file_dest string
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_create_online_log
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
以上是我們需要修改的幾個引數
使用alter system set修改引數檔案;
alter system set control_files='+DATA' scope=spfile;
alter system set db_create_file_dest='+DATA' scope=spfile;
alter system set db_recovery_file_dest='+RECOVERY' scope=spfile;
alter system set db_recovery_file_dest_size=3g scope=spfile;
alter system set db_create_online_log_dest_1='+LOG' scope=spfile;
修改好，shutdown immediate資料庫，下面使用rman遷移資料庫檔案到ASM磁碟組
2）使用rman遷移資料庫檔案
登入到rman，並啟動到nomount狀態;
[oracle@10g ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 14 23:46:09 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 121635396 bytes
Database Buffers 159383552 bytes
Redo Buffers 2920448 bytes
使用原有的control file 檔案恢復到+DATA，並啟動到mount狀態
RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/control01.ctl';
Starting restore at 14-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/orcl/controlfile/current.256.791336867
Finished restore at 14-AUG-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
使用rman備份特性，把資料庫檔案移動到asm磁碟組;
RMAN> backup as copy database format '+DATA';
Starting backup at 14-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output filename=+DATA/orcl/datafile/system.257.791336925 tag=TAG20120814T234844 recid=2 stamp=791336949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output filename=+DATA/orcl/datafile/undotbs1.258.791336951 tag=TAG20120814T234844 recid=3 stamp=791336972
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output filename=+DATA/orcl/datafile/sysaux.259.791336975 tag=TAG20120814T234844 recid=4 stamp=791336989
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=+DATA/orcl/datafile/example.260.791336991 tag=TAG20120814T234844 recid=5 stamp=791336995
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/orcl/users01.dbf
output filename=+DATA/orcl/datafile/users.261.791336997 tag=TAG20120814T234844 recid=6 stamp=791336998
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/orcl/controlfile/backup.262.791336999 tag=TAG20120814T234844 recid=7 stamp=791337000
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
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 14-AUG-12
channel ORA_DISK_1: finished piece 1 at 14-AUG-12
piece handle=+DATA/orcl/backupset/2012_08_14/nnsnf0_tag20120814t234844_0.263.791337003 tag=TAG20120814T234844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-AUG-12
RMAN> list copy;
specification does not match any archive log in the recovery catalog
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 1 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/system.257.791336925
3 2 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/undotbs1.258.791336951
4 3 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/sysaux.259.791336975
6 4 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/users.261.791336997
5 5 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/example.260.791336991
List of Control File Copies
Key S Completion Time Ckp SCN Ckp Time Name
------- - --------------- ---------- --------------- ----
7 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/controlfile/backup.262.791336999
利用RMAN的SWITCH 命令修改控制檔案內資料檔案的指標，使其指向新位置
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.257.791336925"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/undotbs1.258.791336951"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/sysaux.259.791336975"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.261.791336997"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.260.791336991"
RMAN> recover database;
Starting recover at 14-AUG-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-AUG-12
RMAN> alter database open;
database opened
RMAN>
以上資料庫檔案遷移完成；
現在進入到sqlplus，重建tempfile,新建logfille到ASM磁碟組
3）增加新的tempfile，logfile到磁碟組
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Aug 14 23:54:09 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
由於臨時檔案不會被遷移，所以我們只需要刪除原來的增加新的就可以。
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl/tempfile/temp.264.791337285
增加新的ONLINE REDOLOGS 到ASM
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
SQL> alter database add logfile '+LOG' size 50M;
Database altered.
SQL> alter database add logfile '+LOG' size 50M;
Database altered.
SQL> alter database add logfile '+LOG' size 50M;
Database altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
4 1 UNUSED
5 1 UNUSED
6 1 UNUSED
6 rows selected.
切換日誌組
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 ACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 1 ACTIVE
5 1 ACTIVE
6 1 CURRENT
6 rows selected.
checkpoint觸發日誌寫
SQL> alter system checkpoint;
System altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 INACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 1 INACTIVE
5 1 INACTIVE
6 1 CURRENT
6 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
4 1 INACTIVE
5 1 INACTIVE
6 1 CURRENT
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+LOG/orcl/onlinelog/group_4.256.791337483
+LOG/orcl/onlinelog/group_5.257.791337487
+LOG/orcl/onlinelog/group_6.258.791337489
SQL> select name from v$datafile
2 union all
3 select name from v$controlfile
4 union all
5 select member from v$logfile
6 union all
7* select name from v$tempfile
NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.257.791336925
+DATA/orcl/datafile/undotbs1.258.791336951
+DATA/orcl/datafile/sysaux.259.791336975
+DATA/orcl/datafile/users.261.791336997
+DATA/orcl/datafile/example.260.791336991
+DATA/orcl/controlfile/current.256.791336867
+LOG/orcl/onlinelog/group_4.256.791337483
+LOG/orcl/onlinelog/group_5.257.791337487
+LOG/orcl/onlinelog/group_6.258.791337489
+DATA/orcl/tempfile/temp.264.791337285
10 rows selected.
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +RECOVERY
db_recovery_file_dest_size big integer 3G
五、最後刪除原來的資料庫檔案
[oracle@10g orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@10g orcl]$ ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf users01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf undotbs01.dbf
[oracle@10g orcl]$ rm -rf *
[oracle@10g orcl]$ ls
[oracle@10g orcl]$
ps：感謝老王！
一、建立ASM例項並啟動ASM例項；
我們可以手動建立ASM例項，也可以使用DBCA來建立ASM例項；
[oracle@10g ~]$ export ORACLE_SID=+ASM
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 14 00:15:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted
SQL> select status from v$instance;
STATUS
------------------------------------
STARTED
Elapsed: 00:00:00.02
二、建立ASM磁碟；
Disk /dev/sdb: 7516 MB, 7516192768 bytes
255 heads, 63 sectors/track, 913 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 366 2939863+ 83 Linux
/dev/sdb2 367 732 2939895 83 Linux
/dev/sdb3 733 913 1453882+ 83 Linux
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk DATA /dev/sdb1
Marking disk "DATA" as an ASM disk: [ OK ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk RECOVERY /dev/sdb2
Marking disk "RECOVERY" as an ASM disk: [ OK ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm createdisk LOG /dev/sdb3
Marking disk "LOG" as an ASM disk: [ OK ]
[root@10g asmlib_i686]# /etc/init.d/oracleasm listdisks
DATA
LOG
RECOVERY
三、登入到ASM建立磁碟組
[oracle@10g ~]$ export ORACLE_SID=+ASM
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 14 00:15:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1272120 bytes
Variable Size 57448136 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted （由於沒有建立diskgroup，所以這裡報錯）
以下建立diskgroup
SQL> create diskgroup DATA external redundancy disk 'ORCL:DATA';
Diskgroup created.
SQL> create diskgroup RECOVERY external redundancy disk 'ORCL:RECOVERY';
Diskgroup created.
SQL> create diskgroup LOG external redundancy disk 'ORCL:LOG';
Diskgroup created.
SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1272120 bytes
Variable Size 57448136 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
使用v$asm_diskgroup檢視當前ASM磁碟組的狀態，以及型別；
SQL> col NAME for a20;
SQL> select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB
------------ -------------------- --------------------------------- ------------------ ----------
1 DATA MOUNTED EXTERN 2870
2 LOG MOUNTED EXTERN 1419
3 RECOVERY MOUNTED EXTERN 2870
SQL> col PATH for a30;
SQL> select GROUP_NUMBER,DISK_NUMBER,PATH from v$asm_disk;
GROUP_NUMBER DISK_NUMBER PATH
------------ ----------- ------------------------------
1 0 ORCL:DATA
2 0 ORCL:LOG
3 0 ORCL:RECOVERY
使用兩個檢視聯合檢視；
SQL> col NAME for a20;
SQL> col PATH for a20;
SQL> col STATE for a20;
SQL> select g.group_number,d.name,d.path,g.state,g.type
2 from v$asm_diskgroup g,v$asm_disk d
3 where g.group_number=d.group_number;
GROUP_NUMBER NAME PATH STATE TYPE
------------ -------------------- -------------------- -------------------- ------------------
1 DATA ORCL:DATA MOUNTED EXTERN
2 LOG ORCL:LOG MOUNTED EXTERN
3 RECOVERY ORCL:RECOVERY MOUNTED EXTERN
使用ASMCMD檢視磁碟；
[oracle@10g ~]$ echo $ORACLE_SID
+ASM
[oracle@10g ~]$ asmcmd
ASMCMD> ls
DATA/
LOG/
RECOVERY/
四、使用RMAN將資料庫遷移到ASM例項；
1）修改SPFILE引數檔案，在SPFILE引數中增加幾個引數以及相應的值。
新增引數的內容：
control_files='+DATA'
DB_CREATE_FILE_DEST='+DATA'
DB_RECOVERY_FILE_DEST='+RECOVERY'
DB_RECOVERY_FILE_DEST_SIZE=3g
DB_CREATE_ONLINE_LOG_DEST_1='+LOG'
實際上ASM使用的是OMF檔案系統。所以必須設定這些引數。下面分別介紹這四個引數的含義；
*DB_CREATE_FILE_DEST:這個引數指定了資料檔案、控制檔案、重做日誌檔案以及臨時檔案的預設建立位置，如果沒有設定DB_CREATE_ONLINE_LOG_DEST_n引數，會在引數
DB_CREATER_FILE_DEST指定的磁碟組中建立重做日誌檔案和控制檔案。
*DB_RECOVERY_FILE_DEST:該引數指定了RMAN備份、閃回日誌以及歸檔日誌的儲存位置，如果沒有設定引數DB_CREATE_ONLINE_LOG_DEST_n，則會
在DB_RECOVERY_FILE_DEST引數指定的目錄下建立一個重做日誌檔案。
*DB_RECOVERY_FILE_DEST_SIZE:該引數設定快閃恢復區的大小。
*DB_CREATE_ONLINE_LOG_DEST_n：該引數儲存重做日誌和控制檔案的副本，實現多路複用。
修改ORACLE_SID登入例項名為資料庫系統名（如:ORCL）
[oracle@10g ~]$ echo $ORACLE_SID
orcl
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Aug 14 23:21:50 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 125829700 bytes
Database Buffers 155189248 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
SQL> show parameter db_create_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_file_dest string
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_create_online_log
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
以上是我們需要修改的幾個引數
使用alter system set修改引數檔案;
alter system set control_files='+DATA' scope=spfile;
alter system set db_create_file_dest='+DATA' scope=spfile;
alter system set db_recovery_file_dest='+RECOVERY' scope=spfile;
alter system set db_recovery_file_dest_size=3g scope=spfile;
alter system set db_create_online_log_dest_1='+LOG' scope=spfile;
修改好，shutdown immediate資料庫，下面使用rman遷移資料庫檔案到ASM磁碟組
2）使用rman遷移資料庫檔案
登入到rman，並啟動到nomount狀態;
[oracle@10g ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 14 23:46:09 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 121635396 bytes
Database Buffers 159383552 bytes
Redo Buffers 2920448 bytes
使用原有的control file 檔案恢復到+DATA，並啟動到mount狀態
RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/control01.ctl';
Starting restore at 14-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/orcl/controlfile/current.256.791336867
Finished restore at 14-AUG-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
使用rman備份特性，把資料庫檔案移動到asm磁碟組;
RMAN> backup as copy database format '+DATA';
Starting backup at 14-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output filename=+DATA/orcl/datafile/system.257.791336925 tag=TAG20120814T234844 recid=2 stamp=791336949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output filename=+DATA/orcl/datafile/undotbs1.258.791336951 tag=TAG20120814T234844 recid=3 stamp=791336972
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output filename=+DATA/orcl/datafile/sysaux.259.791336975 tag=TAG20120814T234844 recid=4 stamp=791336989
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=+DATA/orcl/datafile/example.260.791336991 tag=TAG20120814T234844 recid=5 stamp=791336995
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/orcl/users01.dbf
output filename=+DATA/orcl/datafile/users.261.791336997 tag=TAG20120814T234844 recid=6 stamp=791336998
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/orcl/controlfile/backup.262.791336999 tag=TAG20120814T234844 recid=7 stamp=791337000
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
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 14-AUG-12
channel ORA_DISK_1: finished piece 1 at 14-AUG-12
piece handle=+DATA/orcl/backupset/2012_08_14/nnsnf0_tag20120814t234844_0.263.791337003 tag=TAG20120814T234844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-AUG-12
RMAN> list copy;
specification does not match any archive log in the recovery catalog
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 1 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/system.257.791336925
3 2 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/undotbs1.258.791336951
4 3 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/sysaux.259.791336975
6 4 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/users.261.791336997
5 5 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/datafile/example.260.791336991
List of Control File Copies
Key S Completion Time Ckp SCN Ckp Time Name
------- - --------------- ---------- --------------- ----
7 A 14-AUG-12 847185 14-AUG-12 +DATA/orcl/controlfile/backup.262.791336999
利用RMAN的SWITCH 命令修改控制檔案內資料檔案的指標，使其指向新位置
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.257.791336925"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/undotbs1.258.791336951"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/sysaux.259.791336975"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.261.791336997"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.260.791336991"
RMAN> recover database;
Starting recover at 14-AUG-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-AUG-12
RMAN> alter database open;
database opened
RMAN>
以上資料庫檔案遷移完成；
現在進入到sqlplus，重建tempfile,新建logfille到ASM磁碟組
3）增加新的tempfile，logfile到磁碟組
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Aug 14 23:54:09 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
由於臨時檔案不會被遷移，所以我們只需要刪除原來的增加新的就可以。
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl/tempfile/temp.264.791337285
增加新的ONLINE REDOLOGS 到ASM
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
SQL> alter database add logfile '+LOG' size 50M;
Database altered.
SQL> alter database add logfile '+LOG' size 50M;
Database altered.
SQL> alter database add logfile '+LOG' size 50M;
Database altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 CURRENT
2 1 INACTIVE
3 1 INACTIVE
4 1 UNUSED
5 1 UNUSED
6 1 UNUSED
6 rows selected.
切換日誌組
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 ACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 1 ACTIVE
5 1 ACTIVE
6 1 CURRENT
6 rows selected.
checkpoint觸發日誌寫
SQL> alter system checkpoint;
System altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
1 1 INACTIVE
2 1 INACTIVE
3 1 INACTIVE
4 1 INACTIVE
5 1 INACTIVE
6 1 CURRENT
6 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select GROUP#, MEMBERS, STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ------------------------------------------------
4 1 INACTIVE
5 1 INACTIVE
6 1 CURRENT
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+LOG/orcl/onlinelog/group_4.256.791337483
+LOG/orcl/onlinelog/group_5.257.791337487
+LOG/orcl/onlinelog/group_6.258.791337489
SQL> select name from v$datafile
2 union all
3 select name from v$controlfile
4 union all
5 select member from v$logfile
6 union all
7* select name from v$tempfile
NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.257.791336925
+DATA/orcl/datafile/undotbs1.258.791336951
+DATA/orcl/datafile/sysaux.259.791336975
+DATA/orcl/datafile/users.261.791336997
+DATA/orcl/datafile/example.260.791336991
+DATA/orcl/controlfile/current.256.791336867
+LOG/orcl/onlinelog/group_4.256.791337483
+LOG/orcl/onlinelog/group_5.257.791337487
+LOG/orcl/onlinelog/group_6.258.791337489
+DATA/orcl/tempfile/temp.264.791337285
10 rows selected.
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +RECOVERY
db_recovery_file_dest_size big integer 3G
五、最後刪除原來的資料庫檔案
[oracle@10g orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@10g orcl]$ ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf users01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf undotbs01.dbf
[oracle@10g orcl]$ rm -rf *
[oracle@10g orcl]$ ls
[oracle@10g orcl]$
ps：感謝老王！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29477587/viewspace-1128277/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用rman copy將資料庫遷移到ASM例項資料庫ASM
- 利用RMAN將資料庫從檔案系統遷移到ASM(單例項)資料庫ASM單例
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- 利用rman將本地資料檔案遷移到asmASM
- RAC 例項 遷移到 單例項 -- 使用RMAN Duplicate單例
- 資料庫遷移到ASM資料庫ASM
- 單例項資料庫expdp遷移到RAC庫單例資料庫
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- 將資料庫遷移到ASM的實驗記錄資料庫ASM
- rman將linux平臺資料庫遷移到window平臺資料庫Linux資料庫
- Oracle11g使用rman從單例項遷移到racOracle單例
- 將pentaho資料庫遷移到oracle資料庫資料庫Oracle
- 單例項資料遷移到RAC補充單例
- 【ASM學習】普通資料庫向ASM例項的遷移(二)ASM資料庫
- 【ASM學習】普通資料庫向ASM例項的遷移(一)ASM資料庫
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 資料庫從檔案系統遷移到ASM資料庫ASM
- 使用RMAN將RAC+ASM複製到單例項+ASM上ASM單例
- 建立ASM例項及ASM資料庫ASM資料庫
- 使用RAM將檔案系統遷移到ASMASM
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 在不同機器之間使用rman複製資料庫例項，從非asm到asm資料庫ASM
- ASM之建立ASM例項及ASM資料庫ASM資料庫
- 【資料遷移】RMAN遷移資料庫到ASM(三)遷移onlinelog等到ASM資料庫ASM
- 【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM資料庫ASM
- 使用RMAN執行oracle ASM資料遷移OracleASM
- 【遷移】使用rman遷移資料庫資料庫
- 建立ASM例項和資料庫ASM資料庫
- 遷移到ASMASM
- hp-ux利用rman將資料庫跨平臺遷移到aix平臺上UX資料庫AI
- Oracle 11g單例項ASM遷移到檔案系統Oracle單例ASM
- 三種ASM下將資料檔案由dg遷移到dg的方法ASM
- 怎麼把達夢資料庫例項從Windows遷移到Linux系統資料庫WindowsLinux
- 採用Duplicate不連線target資料庫的方式將檔案系統遷移到ASM資料庫ASM
- 將ORACLE資料庫資料檔案遷移到其他目錄(ZT)Oracle資料庫
- rman將一個庫遷移到另一個伺服器上伺服器
- 將OPEN BRAVO資料庫從oracle 遷移到DB2資料庫OracleDB2