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's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AMMySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
MySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AMMySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.
What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AMInnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.
MySQL: Not a Programming Language, But...Apr 13, 2025 am 12:03 AMMySQL is not a programming language, but its query language SQL has the characteristics of a programming language: 1. SQL supports conditional judgment, loops and variable operations; 2. Through stored procedures, triggers and functions, users can perform complex logical operations in the database.
MySQL: An Introduction to the World's Most Popular DatabaseApr 12, 2025 am 12:18 AMMySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.
The Importance of MySQL: Data Storage and ManagementApr 12, 2025 am 12:18 AMMySQL is an open source relational database management system suitable for data storage, management, query and security. 1. It supports a variety of operating systems and is widely used in Web applications and other fields. 2. Through the client-server architecture and different storage engines, MySQL processes data efficiently. 3. Basic usage includes creating databases and tables, inserting, querying and updating data. 4. Advanced usage involves complex queries and stored procedures. 5. Common errors can be debugged through the EXPLAIN statement. 6. Performance optimization includes the rational use of indexes and optimized query statements.
Why Use MySQL? Benefits and AdvantagesApr 12, 2025 am 12:17 AMMySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.
Describe InnoDB locking mechanisms (shared locks, exclusive locks, intention locks, record locks, gap locks, next-key locks).Apr 12, 2025 am 12:16 AMInnoDB's lock mechanisms include shared locks, exclusive locks, intention locks, record locks, gap locks and next key locks. 1. Shared lock allows transactions to read data without preventing other transactions from reading. 2. Exclusive lock prevents other transactions from reading and modifying data. 3. Intention lock optimizes lock efficiency. 4. Record lock lock index record. 5. Gap lock locks index recording gap. 6. The next key lock is a combination of record lock and gap lock to ensure data consistency.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
WebStorm Mac version
Useful JavaScript development tools
SublimeText3 Chinese version
Chinese version, very easy to use
Dreamweaver Mac version
Visual web development tools
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
Atom editor mac version download
The most popular open source editor
