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

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:  

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

查看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数据库服务器上将控制文件存放在多个磁盘分区或者多块硬盘上。所以通过把控制文件存放在不同的硬盘上,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> 

 

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

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

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

移动重做日志文件

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

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

   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> 

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

数据文件调优

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

上一篇:【专业找水题】状压dp最水题,没有之一


下一篇:ASP FSO操作文件(复制文件、重命名文件、删除文件、替换字符