ORACLE数据库有时候需要对存储进行调整，增加分区、IO调优等等，此时需要移动数据文件、重做日志文件、控制文件等等，下文结合例子总结一下这方面的知识点。
进行数据文件、重做日志文件、控制文件的迁移前，需要总体了解一下当前Linux服务器的磁盘、分区信息，以及服务器文件使用情况，如下所示
查看Linux服务器的文件使用情况
1: [root@DB-Server ~]# df -h
2:
3: Filesystem Size Used Avail Use% Mounted on
4:
5: /dev/mapper/VolGroup00-LogVol00
6:
7: 78G 7.1G 67G 10% /
8:
9: /dev/sdb1 78G 184M 74G 1% /u02
10:
11: /dev/sdc1 78G 184M 74G 1% /u03
12:
13: /dev/sdd1 78G 9.1G 65G 13% /u04
14:
15: /dev/sda1 99M 23M 71M 25% /boot
16:
17: tmpfs 7.9G 0 7.9G 0% /dev/shm
18:
查看Linux 服务器的磁盘、分区情况。如下所示，这台服务器，4个物理硬盘，6个逻辑分区
1: [root@DB-Server ~]# fdisk -l
2:
3: Disk /dev/sda: 128.8 GB, 128849018880 bytes
4: 255 heads, 63 sectors/track, 15665 cylinders
5: Units = cylinders of 16065 * 512 = 8225280 bytes
6:
7: Device Boot Start End Blocks Id System
8: /dev/sda1 * 1 13 104391 83 Linux
9: /dev/sda2 14 15665 125724690 8e Linux LVM
10:
11: Disk /dev/sdb: 85.8 GB, 85899345920 bytes
12: 255 heads, 63 sectors/track, 10443 cylinders
13: Units = cylinders of 16065 * 512 = 8225280 bytes
14:
15: Device Boot Start End Blocks Id System
16: /dev/sdb1 * 1 10443 83883366 83 Linux
17:
18: Disk /dev/sdc: 85.8 GB, 85899345920 bytes
19: 255 heads, 63 sectors/track, 10443 cylinders
20: Units = cylinders of 16065 * 512 = 8225280 bytes
21:
22: Device Boot Start End Blocks Id System
23: /dev/sdc1 * 1 10443 83883366 83 Linux
24:
25: Disk /dev/sdd: 85.8 GB, 85899345920 bytes
26: 255 heads, 63 sectors/track, 10443 cylinders
27: Units = cylinders of 16065 * 512 = 8225280 bytes
28:
29: Device Boot Start End Blocks Id System
30: /dev/sdd1 * 1 10443 83883366 83 Linux
31:
32: Disk /dev/dm-0: 85.8 GB, 85899345920 bytes
33: 255 heads, 63 sectors/track, 10443 cylinders
34: Units = cylinders of 16065 * 512 = 8225280 bytes
35:
36: Disk /dev/dm-0 doesn't contain a valid partition table
37:
38: Disk /dev/dm-1: 18.9 GB, 18924699648 bytes
39: 255 heads, 63 sectors/track, 2300 cylinders
40: Units = cylinders of 16065 * 512 = 8225280 bytes
41:
42: Disk /dev/dm-1 doesn't contain a valid partition table
控制文件移动
控制文件是ORACLE中最重要的文件之一，控制文件一般默认是三个。
ORACLE数据库系统在需要更新控制文件的时候，就会自动同时更新多个控制文件。当其中一个控制文件出现损坏时，系统会自动启用另外的控制文件。只有当
ORACLE数据库管理员运气比较背的时候，即所有控制文件都出现损坏，此时ORACLE数据库就无法正常启动了。
关于控制文件，通常采用分散放置，多路复用的原则。 即只要将控制文件多路复用在多块硬盘上，一般来说控制文件一起损坏的几率很小。所以采用多路复用控制文件可以在很大程度上提高控制文件的安全性。最重要的是，在控制文件转换的过程之中，不会有停机现象的产生。
关于多路复用的原理其实很简单，就是在ORACLE数据库服务器上将控制文件存放在多个磁盘分区或者多块硬盘上。所以通过把控制文件存放在不同的硬盘上，ORACLE数据库就能够避免出现单点故障的风险。
另外一个提高IO性能的方法就是将控制文件放置在裸设备(Raw device)上，记得三年前，第一次在AIX上看到控制文件为/dev/raw/raw1这种方式时，当时很是迷惑，同事告知是裸设备时，才知道有这么东东。
1: [oracle@DB-Server ~]$ sqlplus / as sysdba
2:
3: SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 11 09:10:53 2014
4:
5: Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
6:
7:
8: Connected to:
9: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
10: With the Partitioning, OLAP, Data Mining and Real Application Testing options
11:
12: SQL> desc v$controlfile;
13: Name Null? Type
14: ----------------------------------------- -------- ----------------------------
15: STATUS VARCHAR2(7)
16: NAME VARCHAR2(513)
17: IS_RECOVERY_DEST_FILE VARCHAR2(3)
18: BLOCK_SIZE NUMBER
19: FILE_SIZE_BLKS NUMBER
20:
21: SQL> SET LINESIZE 1400;
22: SQL> COL STATUS FOR A7
23: SQL> COL NAME FOR A60
24: SQL> COL BLOCK_SIZE FOR 999999
25: SQL> COL FILE_SIZE_BLKS FOR 9999999
26: SQL> SELECT * FROM V$CONTROLFILE;
27:
28: STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
29: ------- ------------------------------------------------------------ --- ---------- --------------
30: /u01/app/oracle/oradata/epps/control01.ctl NO 16384 430
31: /u01/app/oracle/oradata/epps/control02.ctl NO 16384 430
32: /u01/app/oracle/oradata/epps/control03.ctl NO 16384 430
33:
34: SQL>
如上所示，所有的控制文件都位于/u01/app/oracle
/oradata/epps目录下，也就是说位于逻辑分区/dev/sda2
，磁盘/dev/sda中。这个显然是不合理的。完全违反分散放置，多路复用的原则。例如我将控制文件分散放置到硬盘sda、sdb、sdc上。可以分散
IO。提高IO性能。下面是操作步骤。
Step 1： 在/u02, /u03等目录新建文件夹/oradata/epps
1: [oracle@DB-Server u02]$ mkdir -p ./oradata/epps
2: [oracle@DB-Server u02]$ cd /u03
3: [oracle@DB-Server u03]$ mkdir -p ./oradata/epps
4:
5:
6: [oracle@DB-Server u01]$ mkdir oradata
7: [oracle@DB-Server u01]$ cd oradata/
8: [oracle@DB-Server oradata]$ ls
9: [oracle@DB-Server oradata]$ mkdir epps
Step 2: 检查是否以spfile启动
1: SQL> show parameter spfile;
2:
3: NAME TYPE VALUE
4: ------------------------------------ ----------- ------------------------------
5: spfile string /u01/app/oracle/product/10.2.0
6: /db_1/dbs/spfileepps.ora
Step 3: 修改控制文件位置
1: SQL> alter system set control_files=
2: 2 '/u01/oradata/epps/control01.ctl',
3: 3 '/u02/oradata/epps/control02.ctl',
4: 4 '/u03/oradata/epps/control03.ctl'
5: 5 scope=spfile;
6:
7: System altered.
Step 4：关闭数据库实例
1: SQL> shutdown immediate;
2: Database closed.
3: Database dismounted.
4: ORACLE instance shut down.
Step 5：移动控制文件
1: SQL> ! mv /u01/app/oracle/oradata/epps/control01.ctl /u01/oradata/epps
2:
3: SQL> ! mv /u01/app/oracle/oradata/epps/control02.ctl /u02/oradata/epps/
4:
5: SQL> ! mv /u01/app/oracle/oradata/epps/control03.ctl /u03/oradata/epps/
Step 6：启动数据库
1: SQL> startup
2: ORACLE instance started.
3:
4: Total System Global Area 5033164800 bytes
5: Fixed Size 2090848 bytes
6: Variable Size 956303520 bytes
7: Database Buffers 4060086272 bytes
8: Redo Buffers 14684160 bytes
9: Database mounted.
10: Database opened.
Step 7：验证控制文件是否移动到其它分区
1: SQL> SELECT * FROM V$CONTROLFILE;
2:
3: STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
4: ------- ------------------------------------------------------------ --- ---------- --------------
5: /u01/oradata/epps/control01.ctl NO 16384 430
6: /u02/oradata/epps/control02.ctl NO 16384 430
7: /u03/oradata/epps/control03.ctl NO 16384 430
8:
9: SQL>
移动重做日志文件
联机日志文件又叫重做日志文件，记录了对数据库修改的信息，包括用户对数
据修改和数据库管理员对数据库结构的修改。它主要用于在发生故障的时候和数据库备份文件配合恢复数据库，一般发生故障有2个情况：一个是介质损坏另外一个
是用户误操作。每个数据库至少有两个日志文件组，每组至少包含1个或者多个日志成员，这里要多个日志成员的原因是防止日志文件组内某个日志文件损坏后及时
提供备份，所以同一组的日志成员一般内容信息相同，但是存放位置不同,就就是采用多路复用。
如下所示，这里有个问题：重做日志每个组只有一个组成员
（member），没有采用多路复用,那么我们先将这三个重做日志文件移动至/u01/oradata/epps/目录下，然后增加组成员，采用多路复
用。避免redo log files
的单点故障。重做日志在ORACLE数据库中IO操作非常频繁，所以需要将同一组的成员分散至不同的磁盘。关于联机重做日志，一般是将redo log
file移动到裸设备或I/0快的磁盘中
1: SQL> col status for a8
2: SQL> select * from v$log;
3:
4: GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
5: ------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
6: 1 1 38 52428800 1 NO INACTIVE 1092664 10-APR-14
7: 2 1 39 52428800 1 NO CURRENT 1116851 10-APR-14
8: 3 1 37 52428800 1 NO INACTIVE 1069283 10-APR-14
9:
10: SQL> col member for a60
11: SQL> col is_recovery_dest_file for a3
12: SQL> select * from v$logfile;
13:
14: GROUP# STATUS TYPE MEMBER IS_
15: ------- -------- ------- ------------------------------------------------------------ ---
16: 3 STALE ONLINE /u01/app/oracle/oradata/epps/redo03.log NO
17: 2 ONLINE /u01/app/oracle/oradata/epps/redo02.log NO
18: 1 ONLINE /u01/app/oracle/oradata/epps/redo01.log NO
19:
20: SQL> shutdown immediate;
21: Database closed.
22: Database dismounted.
23: ORACLE instance shut down.
24: SQL> ! mv /u01/app/oracle/oradata/epps/redo03.log /u01/oradata/epps/
25:
26: SQL> ! mv /u01/app/oracle/oradata/epps/redo01.log /u01/oradata/epps/
27:
28: SQL> ! mv /u01/app/oracle/oradata/epps/redo02.log /u02/oradata/epps/
29:
30: SQL> ! mv /u02/oradata/epps/redo02.log /u01/oradata/epps/
31:
32: SQL> startup mount;
33: ORACLE instance started.
34:
35: Total System Global Area 5033164800 bytes
36: Fixed Size 2090848 bytes
37: Variable Size 956303520 bytes
38: Database Buffers 4060086272 bytes
39: Redo Buffers 14684160 bytes
40: Database mounted.
41: SQL> alter database rename file '/u01/app/oracle/oradata/epps/redo01.log'
42: 2 to '/u01/oradata/epps/redo01.log';
43:
44: Database altered.
45:
46:
47: SQL> alter database rename file '/u01/app/oracle/oradata/epps/redo02.log'
48: 2 to '/u01/oradata/epps/redo02.log';
49:
50: Database altered.
51:
52: SQL> alter database rename file '/u01/app/oracle/oradata/epps/redo03.log'
53: 2 to '/u01/oradata/epps/redo03.log';
54:
55: Database altered.
56:
57: SQL> alter database open;
58:
59: Database altered.
60:
61: SQL>
1: SQL> alter database add logfile member '/u03/oradata/epps/redo01_02.log' to group 1;
2:
3: Database altered.
4:
5: SQL> alter database add logfile member '/u03/oradata/epps/redo02_02.log' to group 2;
6:
7: Database altered.
8:
9: SQL> alter database add logfile member '/u03/oradata/epps/redo03_03.log' to group 3;
10:
11: Database altered.
12:
13: SQL> col member for a60
14: SQL> col is_recovery_dest_file for a3
15: SQL> select * from v$logfile;
16:
17: GROUP# STATUS TYPE MEMBER IS_
18: ---------- ------- ------- ------------------------------------------------------------ ---
19: 3 ONLINE /u01/oradata/epps/redo03.log NO
20: 2 ONLINE /u01/oradata/epps/redo02.log NO
21: 1 ONLINE /u01/oradata/epps/redo01.log NO
22: 1 INVALID ONLINE /u03/oradata/epps/redo01_02.log NO
23: 2 INVALID ONLINE /u03/oradata/epps/redo02_02.log NO
24: 3 INVALID ONLINE /u03/oradata/epps/redo03_03.log NO
25:
26: 6 rows selected.
27:
28: SQL> alter system switch logfile;
29:
30: System altered.
31:
32: SQL> alter system switch logfile;
33:
34: System altered.
35:
36: SQL> alter system switch logfile;
37:
38: System altered.
39:
40: SQL> select * from v$logfile;
41:
42: GROUP# STATUS TYPE MEMBER IS_
43: ---------- ------- ------- ------------------------------------------------------------ ---
44: 3 ONLINE /u01/oradata/epps/redo03.log NO
45: 2 ONLINE /u01/oradata/epps/redo02.log NO
46: 1 ONLINE /u01/oradata/epps/redo01.log NO
47: 1 ONLINE /u03/oradata/epps/redo01_02.log NO
48: 2 ONLINE /u03/oradata/epps/redo02_02.log NO
49: 3 ONLINE /u03/oradata/epps/redo03_03.log NO
50:
51: 6 rows selected.
52:
53: SQL>
数据文件调优
由于采用基本安装方式,像system、uno、temp等表空间的数据
文件都放置于/u01/app/oracle/oradata/epps/下，下面我将这些系统表空间的数据文件移动至/u01/oradata
/epps下，因为我们需要定期备份/u01/app这个目录，而数据文件通过RMAN备份，所以将这些系统表空间数据从/u01/app/目录下移走就
有必要.
1: [oracle@DB-Server epps]$ ls
2: example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
3: [oracle@DB-Server epps]$ pwd
4: /u01/app/oracle/oradata/epps
5: [oracle@DB-Server epps]$ pwd
6: /u01/app/oracle/oradata/epps
7: [oracle@DB-Server epps]$ exit
8: exit
9:
10: SQL> select tablespace_name from dba_tablespaces;
11:
12: TABLESPACE_NAME
13: ------------------------------
14: SYSTEM
15: UNDOTBS1
16: SYSAUX
17: TEMP
18: USERS
19: EXAMPLE
20:
21: 6 rows selected.
表空间example数据文件移动
1: SQL> alter tablespace example offline normal;
2:
3: Tablespace altered.
4:
5: SQL> ! mv /u01/app/oracle/oradata/epps/example01.dbf /u01/oradata/epps/
6:
7: SQL> alter database rename file '/u01/app/oracle/oradata/epps/example01.dbf'
8: 2 to '/u01/oradata/epps/example01.dbf';
9:
10: Database altered.
11:
12: SQL> alter tablespace example online;
13:
14: Tablespace altered.
15:
16: SQL>
表空间sysaux数据文件移动
1: SQL> alter tablespace sysaux offline normal;
2:
3: Tablespace altered.
4:
5: SQL> ! mv /u01/app/oracle/oradata/epps/sysaux01.dbf /u01/oradata/epps/
6:
7: SQL> alter database rename file '/u01/app/oracle/oradata/epps/sysaux01.dbf'
8: 2 to '/u01/oradata/epps/sysaux01.dbf';
9:
10: Database altered.
11:
12: SQL> alter tablespace sysaux online;
13:
14: Tablespace altered.
表空间users数据文件移动
1: SQL> alter tablespace users offline normal;
2:
3: Tablespace altered.
4:
5: SQL> ! mv /u01/app/oracle/oradata/epps/users01.dbf /u01/oradata/epps/
6: SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/epps/users01.dbf'
7: 2 to '/u01/oradata/epps/users01.dbf';
8:
9: Tablespace altered.
10:
11: SQL> alter tablespace users online;
12:
13: Tablespace altered.
UNDO表空间调整
1: SQL> create undo tablespace undotbs
2: 2 datafile '/u01/oradata/epps/undotbs.dbf' size 2G
3: 3 autoextend on
4: 4 next 100m
5: 5 maxsize 8G;
6:
7: Tablespace created.
8:
9: SQL> select count(1) from v$transaction;
10:
11: COUNT(1)
12: ----------
13: 0
14:
15:
16:
17: SQL> alter system set undo_tablespace=undotbs scope=both;
18:
19: System altered.
20:
21: SQL> drop tablespace undotbs1 including contents and datafiles;
22:
23: Tablespace dropped.
临时表空间调整
1: SQL> create temporary tablespace TEMP1
2: 2 tempfile '/u02/oradata/epps/temp01.dbf'
3: 3 size 2G
4: 4 autoextend on
5: 5 next 100m
6: 6 maxsize unlimited;
7:
8: Tablespace created.
9:
10: SQL> alter database default temporary tablespace temp1;
11:
12: Database altered.
13:
14:
15: SQL> drop tablespace temp including contents and datafiles;
16:
17: Tablespace dropped.
System表空间调整
1: SQL> shutdown immediate;
2: Database closed.
3: Database dismounted.
4: ORACLE instance shut down.
5: SQL> ! mv /u01/app/oracle/oradata/epps/system01.dbf /u01/oradata/epps/
6:
7:
8: SQL> startup mount;
9: ORACLE instance started.
10:
11: Total System Global Area 5033164800 bytes
12: Fixed Size 2090848 bytes
13: Variable Size 956303520 bytes
14: Database Buffers 4060086272 bytes
15: Redo Buffers 14684160 bytes
16: Database mounted.
17: SQL> alter database rename file '/u01/app/oracle/oradata/epps/system01.dbf'
18: 2 to '/u01/oradata/epps/system01.dbf';
19:
20: Database altered.
21:
22: SQL> alter database open;
23:
24: Database altered.
时间： 2024-10-27 14:50:38