使用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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle使用RMAN將普通資料檔案轉成ASMOracleASM
- 怎麼把達夢資料庫例項從Windows遷移到Linux系統資料庫WindowsLinux
- 【RMAN】Oracle使用rman將11.2.0.4資料庫遷移至Oracle12c命令參考Oracle資料庫
- 用rman遷移資料庫資料庫
- 將spfile從ASM裡遷移到檔案系統ASM
- MySQL資料庫遷移到PostgresMySql資料庫
- Facebook將花費幾年時間將資料庫遷移到MySQL 8.0資料庫MySql
- 【資料庫資料恢復】ASM例項不能掛載的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 【ASK_ORACLE】Relink ASM單例項資料庫詳細步驟OracleASM單例資料庫
- EAS附件表由資料庫遷移到FTP資料庫FTP
- 將maven、gradle倉庫遷移到d盤MavenGradle
- 【資料庫資料恢復】Oracle ASM例項無法掛載的資料恢復案例資料庫資料恢復OracleASM
- 達夢遷移工具之MySQL資料庫遷移到達夢MySql資料庫
- 你的資料庫真的需要遷移到雲嗎？資料庫
- 使用RMAN備份資料庫資料庫
- 使用SpringCloud將單體遷移到微服務SpringGCCloud微服務
- 如何將Azure SQL 資料庫還原到本地資料庫例項中SQL資料庫
- 3.1.1.2 使用RMAN啟動資料庫資料庫
- 將nodejs遷移到D盤NodeJS
- 如何將 CentOS遷移到 AlmaLinux？CentOSLinux
- 【RMAN】RMAN備份至ASMASM
- 將RAC備份集恢復為單例項資料庫單例資料庫
- 使用RMAN複製資料庫 active database資料庫Database
- Oracle資料庫遷移到國產資料庫核心難點解析 | 聯盟釋出Oracle資料庫
- mongodb關閉資料庫例項MongoDB資料庫
- oracle資料庫與oracle例項Oracle資料庫
- oracle資料庫建立資料庫例項-九五小龐Oracle資料庫
- 設計模式使用例項(5)——建造者模式例項之資料庫連線管理設計模式資料庫
- RAC+DG(asm單例項)ASM單例
- oracle資料庫使用rman備份指令碼Oracle資料庫指令碼
- 資料庫正規化與例項資料庫
- Python 將所有 Bug 遷移到 GitHub 中PythonGithub
- 從本地MySQL遷移到雲資料庫，為什麼是Amazon Aurora？MySql資料庫
- ajax讀取資料庫資料程式碼例項資料庫
- github倉庫遷移到gitlab以及gitlab倉庫遷移到另一個gitlab伺服器GithubGitlab伺服器
- PayPal如何將Teradata資料倉儲遷移到BigQuery實現產品分析
- 【Oracle】ASM例項安裝入門OracleASM
- Oracle使用RMAN從Windows遷移資料到LinuxOracleWindowsLinux
- 記一次資料庫遷移到rac11204資料庫連線scan找不到主機資料庫