代码改变世界

ORACLE 移动数据文件 控制文件 重做日志文件

2014-04-20 09:59  潇湘隐者  阅读(4973)  评论(0)  编辑  收藏  举报

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: 

clip_image002

查看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

clip_image004

控制文件移动

控制文件是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>

 

clip_image006

如上所示，所有的控制文件都位于/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>

clip_image008

移动重做日志文件

联机日志文件又叫重做日志文件，记录了对数据库修改的信息，包括用户对数据修改和数据库管理员对数据库结构的修改。它主要用于在发生故障的时候和数据库备份文件配合恢复数据库，一般发生故障有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>

clip_image010

   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>

clip_image012

数据文件调优

由于采用基本安装方式,像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.

参考资料：

http://blog.csdn.net/tianlesoftware/article/details/7346212