Stripe Data Files Across Multiple Devices and Physical Locations

1. Documentation in Tahiti -> Masters Book List -> Performance Tuning Guide -> 8 I / O Configuration and Design

2. This objective, though DATAFILES Apparently only be specific, we will use it for the rest of the database file. We will create a new directory, pretending it was a different disk

# Create the directory (as root) and give permissions
  mkdir -p /u02/app/oracle/oradata/OCM
  chown -R oracle: oinstall /u02/app/oracle
  chmod -R 775 /u02/app/oracle

3. We started recreating the redo files, making striping of each member of each group on different disks.

- We check the current configuration files redo
 - We will see that we have three groups, and each group have only one member
 SELECT * FROM V $ LOG;
 SELECT * FROM V $ LOGFILE;

- We will recreate all groups but with multiplexing (two copies per group) and took the opportunity to increase the size
 ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/OCM/redo401.log','/u02/app/oracle/oradata/OCM/redo402.log') SIZE 200M;
 - Delete the first group and recreated as the previous group
 - In one of the steps we will give an error (ORA-01623) that the log is in CURRENT or ACTIVE state. To leave INACTIVE:
 - # ALTER SYSTEM SWITCH LOGFILE;
 - # ALTER SYSTEM CHECKPOINT;
 ALTER DATABASE DROP LOGFILE GROUP 1;
 ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/OCM/redo101.log','/u02/app/oracle/oradata/OCM/redo102.log') SIZE 200M REUSE;
 - The same procedure Group 2
 ALTER DATABASE DROP LOGFILE GROUP 2;
 ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/OCM/redo201.log','/u02/app/oracle/oradata/OCM/redo202.log') SIZE 200M REUSE;
 - The same procedure Group 3
 ALTER DATABASE DROP LOGFILE GROUP 3;
 ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/OCM/redo301.log','/u02/app/oracle/oradata/OCM/redo302.log') SIZE 200M REUSE;
 - We erased Group 4
 ALTER DATABASE DROP LOGFILE GROUP 4;

- We review the new size (200M) and multiplexing is correct (one member on each disk)
 SELECT * FROM V $ LOG;
 SELECT * FROM V $ LOGFILE;

4. We have already seen how we can make copies of CONTROLFILE this objective. As we have two copies (three would be advisable if we have additional time to do it) and we left. The important thing is that each copy is in a different disk, to recover the database in the event of failure of one of them.

5. Regarding DATAFILES striping. Our goal is to distribute the I / O by different disks without saturating get any. Once you have an estimate of the load, we can create DATAFILES on other disks and Oracle is responsible for distributing the load between them.
– We will add a SYSTEM DATAFILE to TBS as an example, but the manual procedure is very simple
SYSTEM ALTER TABLESPACE ADD DATAFILE ‘/u02/app/oracle/oradata/OCM/system02.dbf’ SIZE 400M;

6. It is highly recommended to have the archivelogs files and other recovery-related files to another disk. As we are used RESCOVERY AREA FAST (FRA), we assume that this location belongs to another disk.

- We found the location of the FRA (contain backups, archivelogs, LOGS FLASHBACK ...)
  - Must be in a different disk to the DATAFILES to avoid losing data in case of disk failure
  show parameter db_recovery

- In our case as we configured the FRA, the parameter takes this location LOG_ARCHIVE_DEST_1 implicitly
  - We check that the value of the parameter. It will be empty, but it is assumed that contains the path of "db_recovery_file_dest"
  show parameter LOG_ARCHIVE_DEST_1

- We can check the location where you are creating archivelogs from the BD
  SELECT NAME FROM V $ archived_log ORDER BY COMPLETION_TIME;

7. This multiplexing we have done a posteriori, can be made directly when creating the database, either DBCA or manually. In subsequent years one manually install striping BD and make BD files directly.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s