分布数据文件访问多个磁盘

最新推荐文章于 2024-10-21 14:49:22 发布

cloudsnowe

最新推荐文章于 2024-10-21 14:49:22 发布

阅读量1.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： oracle OCM

本文链接：https://blog.youkuaiyun.com/Cloudsnowe/article/details/49447669

oracle OCM 专栏收录该内容

6 篇文章

订阅专栏

1.多路复用online redo logfile;


SYS@PROD3 > col member for a50;
SYS@PROD3 > select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /home/oracle/app/oradata/PROD3/redo01a.log         NO
         1         ONLINE  /home/oracle/app/oradata/PROD3/redo01b.log         NO
         2         ONLINE  /home/oracle/app/oradata/PROD3/redo02a.log         NO
         2         ONLINE  /home/oracle/app/oradata/PROD3/redo02b.log         NO
         3         ONLINE  /home/oracle/app/oradata/PROD3/redo03a.log         NO
         3         ONLINE  /home/oracle/app/oradata/PROD3/redo03b.log         NO

6 rows selected.

SYS@PROD3 > alter database add logfile group 4 ('/home/oracle/app/oradata/PROD3/redo04a.log','/home/oracle/app/oradata/PROD3/redo04b.log') size 100m;

Database altered.

SYS@PROD3 > select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /home/oracle/app/oradata/PROD3/redo01a.log         NO
         1         ONLINE  /home/oracle/app/oradata/PROD3/redo01b.log         NO
         2         ONLINE  /home/oracle/app/oradata/PROD3/redo02a.log         NO
         2         ONLINE  /home/oracle/app/oradata/PROD3/redo02b.log         NO
         3         ONLINE  /home/oracle/app/oradata/PROD3/redo03a.log         NO
         3         ONLINE  /home/oracle/app/oradata/PROD3/redo03b.log         NO
         4         ONLINE  /home/oracle/app/oradata/PROD3/redo04a.log         NO
         4         ONLINE  /home/oracle/app/oradata/PROD3/redo04b.log         NO

8 rows selected.

SYS@PROD3 > alter database drop logfile group 4;

Database altered.

2.多路复用control files

--查询控制文件的路径
SYS@PROD3 > select name from v$controlfile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/home/oracle/app/oradata/PROD3/control01.ctl
/home/oracle/app/oradata/PROD3/control02.ctl
--修改control_files参数
SYS@PROD3 > alter system set control_files='/home/oracle/app/oradata/PROD3/control01.ctl','/home/oracle/app/oradata/PROD3/control02.ctl','/home/oracle/app/oradata/PROD3/control03.ctl' scope=spfile;

System altered.
--关库
SYS@PROD3 > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--拷贝到指定路径
[oracle@gridcontrol ~]$ cp /home/oracle/app/oradata/PROD3/control01.ctl /home/oracle/app/oradata/PROD3/control03.ctl
--重启实例
SYS@PROD3 > startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2229944 bytes
Variable Size             398461256 bytes
Database Buffers          113246208 bytes
Redo Buffers                7999488 bytes
Database mounted.
Database opened.

3.分布数据文件
建立表空间不同的数据文件放在不同的磁盘

SYS@PROD3 > select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/home/oracle/app/oradata/PROD3/system01.dbf                  SYSTEM
/home/oracle/app/oradata/PROD3/sysaux01.dbf                  SYSAUX
/home/oracle/app/oradata/PROD3/undotbs01.dbf                 UNDOTBS
/home/oracle/app/oradata/PROD3/users01.dbf                   USERS
--创建表空间
SYS@PROD3 > create tablespace lob_data
  2  datafile '/home/oracle/app/oradata/PROD3/lob_data01.dbf' size 20m autoextend on;

Tablespace created.
--新增数据文件，这里是在相同的磁盘
SYS@PROD3 > alter tablespace lob_data add datafile '/home/oracle/app/oradata/PROD3/lob_data02.dbf' size 20m autoextend on;

Tablespace altered.

SYS@PROD3 > select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/home/oracle/app/oradata/PROD3/system01.dbf                  SYSTEM
/home/oracle/app/oradata/PROD3/sysaux01.dbf                  SYSAUX
/home/oracle/app/oradata/PROD3/undotbs01.dbf                 UNDOTBS
/home/oracle/app/oradata/PROD3/users01.dbf                   USERS
/home/oracle/app/oradata/PROD3/lob_data01.dbf                LOB_DATA
/home/oracle/app/oradata/PROD3/lob_data02.dbf                LOB_DATA

6 rows selected.

SYS@PROD3 > drop tablespace lob_data including contents and datafiles;

Tablespace dropped.

SYS@PROD3 > select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/home/oracle/app/oradata/PROD3/system01.dbf                  SYSTEM
/home/oracle/app/oradata/PROD3/sysaux01.dbf                  SYSAUX
/home/oracle/app/oradata/PROD3/undotbs01.dbf                 UNDOTBS
/home/oracle/app/oradata/PROD3/users01.dbf                   USERS