本文主要是介绍Stripe data files across multiple physical devices and locations，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Stripe data files across multiple physical devices and locations如果在没有做条带的磁盘(即从存储到OS没有做raid)，那么就需要手工去做I/O的分布。切记，不应该将频繁使用的table和其index分开，这样会正大I/O；
针对tables、indexes、temp tablespace，首先调优SQL，其次如果真心无法再调优SQL，再想办法分离高I/O的数据文件。
考虑归档日志、redo log不要和数据文件放在同一磁盘上，避免lgwr和arcn两大进程造成I/O导致访问数据文件慢
1、条带化redo日志
查看当前redo的位置，及三组的成员
SQL> col member for a55;
SQL> set linesize 120;
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE CURRENT 100
1 /u01/app/oracle/oradata/redologfile/redo01b.log ONLINE CURRENT 100
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE INACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02b.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03b.log ONLINE INACTIVE 100
添加group 4，然后切换日志，重建1、2、3三个组
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/redologfile/redo04a.log','/u03/app/oracle/oradata/redologfile/redo04b.log') size 200m;
Database altered.
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE CURRENT 100
1 /u01/app/oracle/oradata/redologfile/redo01b.log ONLINE CURRENT 100
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE INACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02b.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03b.log ONLINE INACTIVE 100
4 /u01/app/oracle/oradata/redologfile/redo04a.log ONLINE UNUSED 200
4 /u03/app/oracle/oradata/redologfile/redo04b.log ONLINE UNUSED 200
SQL> alter system switch logfile;
System altered.
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE ACTIVE 100
1 /u01/app/oracle/oradata/redologfile/redo01b.log ONLINE ACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE INACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02b.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03b.log ONLINE INACTIVE 100
4 /u01/app/oracle/oradata/redologfile/redo04a.log ONLINE CURRENT 200
4 /u03/app/oracle/oradata/redologfile/redo04b.log ONLINE CURRENT 200
SQL> alter system checkpoint;
System altered.
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE INACTIVE 100
1 /u01/app/oracle/oradata/redologfile/redo01b.log ONLINE INACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE INACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02b.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03b.log ONLINE INACTIVE 100
4 /u01/app/oracle/oradata/redologfile/redo04a.log ONLINE CURRENT 200
4 /u03/app/oracle/oradata/redologfile/redo04b.log ONLINE CURRENT 200
SQL> alter database drop logfile group 1;
Database altered.
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE INACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02b.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03b.log ONLINE INACTIVE 100
4 /u01/app/oracle/oradata/redologfile/redo04a.log ONLINE CURRENT 200
4 /u03/app/oracle/oradata/redologfile/redo04b.log ONLINE CURRENT 200
如果不用reuse则会出现下面的错误，虽然是drop了，从试图里也查不到了，我们删除时字典里的信息，实际的文件还是存在的
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/redologfile/redo01a.log','/u03/app/oracle/oradata/redologfile/redo01b.log') size 200m;
alter database add logfile group 1 ('/u01/app/oracle/oradata/redologfile/redo01a.log','/u03/app/oracle/oradata/redologfile/redo01b.log') size 200m
*
ERROR at line 1:
ORA-00301: error in adding log file '/u01/app/oracle/oradata/redologfile/redo01a.log' - file cannot be created
ORA-27038: created file already exists
Additional information: 1
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/redologfile/redo01a.log','/u03/app/oracle/oradata/redologfile/redo01b.log') size 200m reuse;
Database altered.
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE UNUSED 200
1 /u03/app/oracle/oradata/redologfile/redo01b.log ONLINE UNUSED 200
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE INACTIVE 100
2 /u01/app/oracle/oradata/redologfile/redo02b.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE INACTIVE 100
3 /u01/app/oracle/oradata/redologfile/redo03b.log ONLINE INACTIVE 100
4 /u01/app/oracle/oradata/redologfile/redo04a.log ONLINE CURRENT 200
4 /u03/app/oracle/oradata/redologfile/redo04b.log ONLINE CURRENT 200
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/redologfile/redo02a.log','/u03/app/oracle/oradata/redologfile/redo02b.log') size 200m reuse;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/redologfile/redo03a.log','/u03/app/oracle/oradata/redologfile/redo03b.log') size 200m reuse;
Database altered.
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE UNUSED 200
1 /u03/app/oracle/oradata/redologfile/redo01b.log ONLINE UNUSED 200
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE UNUSED 200
2 /u03/app/oracle/oradata/redologfile/redo02b.log ONLINE UNUSED 200
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE UNUSED 200
3 /u03/app/oracle/oradata/redologfile/redo03b.log ONLINE UNUSED 200
4 /u01/app/oracle/oradata/redologfile/redo04a.log ONLINE CURRENT 200
4 /u03/app/oracle/oradata/redologfile/redo04b.log ONLINE CURRENT 200
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE CURRENT 200
1 /u03/app/oracle/oradata/redologfile/redo01b.log ONLINE CURRENT 200
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE UNUSED 200
2 /u03/app/oracle/oradata/redologfile/redo02b.log ONLINE UNUSED 200
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE UNUSED 200
3 /u03/app/oracle/oradata/redologfile/redo03b.log ONLINE UNUSED 200
4 /u01/app/oracle/oradata/redologfile/redo04a.log ONLINE INACTIVE 200
4 /u03/app/oracle/oradata/redologfile/redo04b.log ONLINE INACTIVE 200
SQL> alter database drop logfile group 4;
Database altered.
多切几次日志的切换
SQL> select f.group#,f.member,f.type,l.status,l.bytes/1024/1024 mb from v$log l, v$logfile f where f.group#=l.group#;
GROUP# MEMBER TYPE STATUS MB
---------- ------------------------------------------------------- ------- ---------------- ----------
1 /u01/app/oracle/oradata/redologfile/redo01a.log ONLINE INACTIVE 200
1 /u03/app/oracle/oradata/redologfile/redo01b.log ONLINE INACTIVE 200
2 /u01/app/oracle/oradata/redologfile/redo02a.log ONLINE CURRENT 200
2 /u03/app/oracle/oradata/redologfile/redo02b.log ONLINE CURRENT 200
3 /u01/app/oracle/oradata/redologfile/redo03a.log ONLINE INACTIVE 200
3 /u03/app/oracle/oradata/redologfile/redo03b.log ONLINE INACTIVE 200
[oracle@vm010148 redologfile]$ ll
总计 820032
-rw-r----- 1 oracle oinstall 209715712 09-12 09:06 redo01b.log
-rw-r----- 1 oracle oinstall 209715712 09-12 09:06 redo02b.log
-rw-r----- 1 oracle oinstall 209715712 09-12 09:06 redo03b.log
-rw-r----- 1 oracle oinstall 209715712 09-12 09:02 redo04b.log==>虽然做了删除动作，实际的物理文件还是存在的
2、条带化 数据文件的话，可以再增加数据文件，可以用DBMS_FILE_TRANSFER来做已经的数据文件位置的变动，新添加文件的位置可以用
ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/app/oracle/oradata/datafile/system.dbf' SIZE 400M;
3、条带化归档日志
You can choose to archive redo logs to a single destination or to multiple destinations. Destinations can be local—within the local file system or an Oracle Automatic Storage Management (Oracle ASM) disk group—or remote (on a standby database). When you archive to multiple destinations, a copy of each filled redo log file is written to each destination. These redundant copies help ensure that archived logs are always available in the event of a failure at one of the destinations.
上面的这句话可以看出，所谓条带化归档，只是冗余了redo file copy，压根，没有想象的那样打散日志文件放在多处
alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archivelogfile/ORCL11G/archivelog/ORCL11G/archivelog' scope=both;
alter system set log_archive_dest_2='location=/u03/app/oracle/oradata/archivelog' scope=both;
SQL> SET LINESIZE 120;
SQL>COL NAME FOR A120;
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE NAME IS NOT NULL ORDER BY COMPLETION_TIME;
NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/archivelogfile/ORCL11G/archivelog/ORCL11G/archivelog/1_240_855936488.dbf
/u03/app/oracle/oradata/archivelog/1_240_855936488.dbf
/u01/app/oracle/oradata/archivelogfile/ORCL11G/archivelog/ORCL11G/archivelog/1_241_855936488.dbf
/u03/app/oracle/oradata/archivelog/1_241_855936488.dbf
关于怎么讲归档日志同步到remote 数据库服务器，将在dg上总结说明
这篇关于Stripe data files across multiple physical devices and locations的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
