AIX环境文件系统迁移到ASM存储 系统环境： 操作系统：AIX5.3-08 Oracle： Oracle 10gR2 一、构建ASM 存储环境 http://tiany.blog.51cto.com/513694/1418564 Oracle DataBase单实例使用ASM案例(4)--AIX系统Oracle 10g应用 二、迁移文件系统到ASM 1、查看ASM I
AIX环境文件系统迁移到ASM存储
系统环境：
操作系统：AIX5.3-08
Oracle： Oracle 10gR2
一、构建ASM 存储环境
http://tiany.blog.51cto.com/513694/1418564
Oracle DataBase单实例使用ASM案例(4)--AIX系统Oracle 10g应用
二、迁移文件系统到ASM
1、查看ASM Instance
[oracle@aix211 ~]$export ORACLE_SID=+ASM
[oracle@aix211 ~]$!sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 17:23:24 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 130023424 bytes
Fixed Size 2019136 bytes
Variable Size 102838464 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> select name ,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DG1 MOUNTED
RCY1 MOUNTED
SQL>
2、修改实例参数
SQL> alter system set db_create_file_dest ='+dg1' scope=spfile;
System altered.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/prod/c
ontrol01.ctl, /dsk1/oradata/pr
od/control02.ctl
SQL> alter system set control_files='+dg1' scope=spfile;
System altered.
3、转储控制文件到控制文件
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 1258291200 bytes
Fixed Size 2020552 bytes
Variable Size 318769976 bytes
Database Buffers 922746880 bytes
Redo Buffers 14753792 bytes
RMAN> restore controlfile from '/u01/app/oracle/oradata/prod/control01.ctl';
Starting restore at 29-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DG1/prod/controlfile/backup.256.848857069
Finished restore at 29-MAY-14
4、转储数据文件到ASM磁盘组
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+dg1';
Starting backup at 29-MAY-14
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/prod/system01.dbf
output filename=+DG1/prod/datafile/system.257.848857161 tag=TAG20140529T173920 recid=12 stamp=848857281
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
output filename=+DG1/prod/datafile/sysaux.258.848857287 tag=TAG20140529T173920 recid=13 stamp=848857355
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/prod/example01.dbf
output filename=+DG1/prod/datafile/example.259.848857363 tag=TAG20140529T173920 recid=14 stamp=848857404
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
output filename=+DG1/prod/datafile/undotbs1.260.848857407 tag=TAG20140529T173920 recid=15 stamp=848857434
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
output filename=+DG1/prod/datafile/users.261.848857443 tag=TAG20140529T173920 recid=16 stamp=848857457
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 29-MAY-14
Starting Control File and SPFILE Autobackup at 29-MAY-14
piece handle=/backup/c-259825570-20140529-01 comment=NONE
Finished Control File and SPFILE Autobackup at 29-MAY-14
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/prod/datafile/system.257.848857161"
datafile 2 switched to datafile copy "+DG1/prod/datafile/undotbs1.260.848857407"
datafile 3 switched to datafile copy "+DG1/prod/datafile/sysaux.258.848857287"
datafile 4 switched to datafile copy "+DG1/prod/datafile/users.261.848857443"
datafile 5 switched to datafile copy "+DG1/prod/datafile/example.259.848857363"
5、恢复数据库
RMAN> recover database;
Starting recover at 29-MAY-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-MAY-14
RMAN> alter database open;
database opened
6、迁移临时表空间
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/prod/datafile/system.257.848857161
+DG1/prod/datafile/undotbs1.260.848857407
+DG1/prod/datafile/sysaux.258.848857287
+DG1/prod/datafile/users.261.848857443
+DG1/prod/datafile/example.259.848857363
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf
SQL> alter tablespace temp add tempfile '+dg1';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/temp01.dbf
+DG1/prod/tempfile/temp.262.848858041
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/prod/temp01.dbf';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DG1/prod/tempfile/temp.262.848858041
7、迁移redo日志组
SQL> set linesize 120
SQL> select * from v$log
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 114 52428800 2 YES INACTIVE 989355 29-MAY-14
2 1 115 52428800 2 YES INACTIVE 989368 29-MAY-14
3 1 113 52428800 2 YES INACTIVE 988986 29-MAY-14
4 1 116 52428800 2 NO CURRENT 989387 29-MAY-14
SQL> col member for a50
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /dsk1/oradata/prod/redo03a.log
2 /dsk1/oradata/prod/redo02a.log
1 /dsk1/oradata/prod/redo01a.log
1 /dsk2/oradata/prod/redo01b.log
2 /dsk2/oradata/prod/redo02b.log
3 /dsk2/oradata/prod/redo03b.log
4 /dsk1/oradata/prod/redo04a.log
4 /dsk2/oradata/prod/redo04b.log
8 rows selected.
添加新的日志组到ASM diskgroup：
SQL> alter database add logfile
2 ('+dg1','+rcy1') size 10m;
SQL> alter database add logfile
2 ('+dg1','+rcy1') size 10m;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 114 52428800 2 YES INACTIVE 989355 29-MAY-14
2 1 115 52428800 2 YES INACTIVE 989368 29-MAY-14
3 1 113 52428800 2 YES INACTIVE 988986 29-MAY-14
4 1 116 52428800 2 NO CURRENT 989387 29-MAY-14
5 1 0 10485760 2 YES UNUSED 0
6 1 0 10485760 2 YES UNUSED 0
6 rows selected.
SQL> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /dsk1/oradata/prod/redo01a.log
1 /dsk2/oradata/prod/redo01b.log
2 /dsk1/oradata/prod/redo02a.log
2 /dsk2/oradata/prod/redo02b.log
3 /dsk1/oradata/prod/redo03a.log
3 /dsk2/oradata/prod/redo03b.log
4 /dsk1/oradata/prod/redo04a.log
4 /dsk2/oradata/prod/redo04b.log
5 +RCY1/prod/onlinelog/group_5.256.848858217
5 +DG1/prod/onlinelog/group_5.263.848858213
6 +RCY1/prod/onlinelog/group_6.257.848858259
GROUP# MEMBER
---------- --------------------------------------------------
6 +DG1/prod/onlinelog/group_6.264.848858253
12 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 114 52428800 2 YES INACTIVE 989355 29-MAY-14
2 1 115 52428800 2 YES INACTIVE 989368 29-MAY-14
3 1 113 52428800 2 YES INACTIVE 988986 29-MAY-14
4 1 116 52428800 2 YES ACTIVE 989387 29-MAY-14
5 1 117 10485760 2 YES ACTIVE 990481 29-MAY-14
6 1 118 10485760 2 NO CURRENT 990483 29-MAY-14
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> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
4 1 119 52428800 2 YES ACTIVE 990548 29-MAY-14
5 1 120 10485760 2 NO CURRENT 990566 29-MAY-14
6 1 118 10485760 2 YES INACTIVE 990483 29-MAY-14
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
4 1 119 52428800 2 YES INACTIVE 990548 29-MAY-14
5 1 120 10485760 2 NO ACTIVE 990566 29-MAY-14
6 1 121 10485760 2 NO CURRENT 991692 29-MAY-14
SQL> alter database drop logfile group 4;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
5 1 122 10485760 2 NO CURRENT 991711 29-MAY-14
6 1 121 10485760 2 YES INACTIVE 991692 29-MAY-14
SQL> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
5 +DG1/prod/onlinelog/group_5.263.848858213
5 +RCY1/prod/onlinelog/group_5.256.848858217
6 +DG1/prod/onlinelog/group_6.264.848858253
6 +RCY1/prod/onlinelog/group_6.257.848858259
调整recover area参数：
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest='+rcy1' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=2g scope=spfile;
System altered.
8、删除文件系统下的原数据文件
[oracle@aix211 ~]$cd /u01/app/oracle/oradata/prod/
[oracle@aix211 prod]$ls
control01.ctl example01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@aix211 prod]$rm *
[oracle@aix211 prod]$ls
[oracle@aix211 prod]$
对于spfile参数可以存储在文件系统或ASM磁盘组上：
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileprod.ora
@至此，在AIX下迁移文件系统到ASM磁盘组工作完成，通过ASM存储，数据库读写性能得到了一定的提升。
附注：
Oracle官方文档提供的redo日志组迁移脚本：
Example 8-1 Migrating the online redo logs
SET SERVEROUTPUT ON;
DECLARE
CURSOR rlc IS
SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
FROM V$LOG
UNION
SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
FROM V$STANDBY_LOG
ORDER BY 1;
stmt VARCHAR2(2048);
BEGIN
FOR rlcRec IN rlc LOOP
IF (rlcRec.srl = 'YES') THEN
stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
EXECUTE IMMEDIATE stmt;
ELSE
stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
BEGIN
stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE stmt;
END;
END IF;
END LOOP;
END;
/
MySQL索引基數如何影響查詢性能？Apr 14, 2025 am 12:18 AMMySQL索引基数对查询性能有显著影响：1.高基数索引能更有效地缩小数据范围，提高查询效率；2.低基数索引可能导致全表扫描，降低查询性能；3.在联合索引中，应将高基数列放在前面以优化查询。
MySQL：新用戶的資源和教程Apr 14, 2025 am 12:16 AMMySQL學習路徑包括基礎知識、核心概念、使用示例和優化技巧。 1)了解表、行、列、SQL查詢等基礎概念。 2)學習MySQL的定義、工作原理和優勢。 3)掌握基本CRUD操作和高級用法，如索引和存儲過程。 4)熟悉常見錯誤調試和性能優化建議，如合理使用索引和優化查詢。通過這些步驟，你將全面掌握MySQL的使用和優化。
現實世界Mysql：示例和用例Apr 14, 2025 am 12:15 AMMySQL在現實世界的應用包括基礎數據庫設計和復雜查詢優化。 1)基本用法：用於存儲和管理用戶數據，如插入、查詢、更新和刪除用戶信息。 2)高級用法：處理複雜業務邏輯，如電子商務平台的訂單和庫存管理。 3)性能優化：通過合理使用索引、分區表和查詢緩存來提升性能。
MySQL中的SQL命令：實踐示例Apr 14, 2025 am 12:09 AMMySQL中的SQL命令可以分為DDL、DML、DQL、DCL等類別，用於創建、修改、刪除數據庫和表，插入、更新、刪除數據，以及執行複雜的查詢操作。 1.基本用法包括CREATETABLE創建表、INSERTINTO插入數據和SELECT查詢數據。 2.高級用法涉及JOIN進行表聯接、子查詢和GROUPBY進行數據聚合。 3.常見錯誤如語法錯誤、數據類型不匹配和權限問題可以通過語法檢查、數據類型轉換和權限管理來調試。 4.性能優化建議包括使用索引、避免全表掃描、優化JOIN操作和使用事務來保證數據一致性
InnoDB如何處理酸合規性？Apr 14, 2025 am 12:03 AMInnoDB通過undolog實現原子性，通過鎖機制和MVCC實現一致性和隔離性，通過redolog實現持久性。 1）原子性：使用undolog記錄原始數據，確保事務可回滾。 2）一致性：通過行級鎖和MVCC確保數據一致。 3）隔離性：支持多種隔離級別，默認使用REPEATABLEREAD。 4）持久性：使用redolog記錄修改，確保數據持久保存。
MySQL的位置：數據庫和編程Apr 13, 2025 am 12:18 AMMySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen
MySQL：從小型企業到大型企業Apr 13, 2025 am 12:17 AMMySQL適合小型和大型企業。 1)小型企業可使用MySQL進行基本數據管理，如存儲客戶信息。 2)大型企業可利用MySQL處理海量數據和復雜業務邏輯，優化查詢性能和事務處理。
幻影是什麼讀取的，InnoDB如何阻止它們（下一個鍵鎖定）？Apr 13, 2025 am 12:16 AMInnoDB通過Next-KeyLocking機制有效防止幻讀。 1）Next-KeyLocking結合行鎖和間隙鎖，鎖定記錄及其間隙，防止新記錄插入。 2）在實際應用中，通過優化查詢和調整隔離級別，可以減少鎖競爭，提高並發性能。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
熱工具
SublimeText3 Linux新版
SublimeText3 Linux最新版
EditPlus 中文破解版
體積小，語法高亮，不支援程式碼提示功能
PhpStorm Mac 版本
最新（2018.2.1 ）專業的PHP整合開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
記事本++7.3.1
好用且免費的程式碼編輯器
