Is561 Week 2 Solution

Only available on StudyMode
  • Download(s) : 184
  • Published : July 22, 2012
Open Document
Text Preview
KeyIS561 – Week 2 iLab

STEP 1: Working with Dictionary-Managed and Oracle-Managed Files (10 points)

Scenario: As the DBA for your company, you have decided to install a new version of the Oracle Enterprise database to replace the current database version being used. The old database has become a constant headache and seems to be causing an overload on the disk drive's I/O channels. Further analysis has also shown that two primary large tables are the main points of access. These tables are CUST_REC and ACCT_RECV tables. You also have a new server with three large-capacity disk drives: F, G, and H. Briefly, provide the following information. * Describe how you plan to rectify the I/O problem with the new Oracle10g database. Response

When I\O performance is degraded, the best solution is to separate the data files, log files and control files. Ideally these files will be placed on physically separate disk volumes. However, our scenario makes an explicit reference to two tables that seem to be quite important. The only way that I know to segregate these individual tables is to make use of separate tablespaces. Aside from the default tablespaces created during the database creation process, we will need to create two additional tablespaces (along with respective datafiles), one for the CUST_REC table and one for the ACCT_RECV table. Each tablespace will have one or more associated datafiles. For the sake of simplicity, I would create the same folder structure on all three drives … something to the effect of \Oracle\oradata\<database>, \Oracle\control\<database>, and \Oracle\logs\<database>. Tablespaces and Datafiles

Taking the above explanation and laying it out on our three drives, we can use the F volume for all of the necessary tablespaces required during database creation (SYSTEM, SYSAUX, USERS, TEMP, and UNDOTBS) and to run Oracle. Each of these tablespaces would have a datafile associated with them, typically using a naming convention of <Tablespace>n.dbf where n is a sequential number like 01, 02, etc. and located in the datafiles folder (F:\Oracle\oradata\<database>). To resolve the I\O performance degradation issue for the CUST_REC and ACCT_RECV tables, I would suggest creating the CUST_REC tablespace and storing the associated datafile on the G volume (G:\Oracle\oradata\<database>\cust_rec.dbf). I would also suggest creating the ACCT_RECV tablespace and storing the associated datafile on the H volume (H:\Oracle\oradata\<database>\acct_recv.dbf), essentially separating both of these two large tables from each other and from the other tables in the database. Control and Redo Log Files

Once the datafiles are set, I would multiplex the control and redo log files across at least two of the drives. Being that the F volume houses the majority of datafiles for the Oracle-recommended tablespaces, I would multiplex the control and redo log files between the G and H volumes ( Control files => F:\Oracle\control\<database>\control01.ctl, G:\Oracle\control\<database>\control02.ctl; Redo Log files => F:\Oracle\logs\<database>\redo01.log, G:\Oracle\logs\<database>\redo02.log) . Multiplexing these files will help insure that the database can be recovered should one of the volumes fail. Oracle Managed Files (OMF)

For this scenario I would NOT suggest using OMF storage for this database. As you can see from my explanation above, precise control over tablespaces and file locations is required to maximize I\O efficiency. OMF is not quite flexible enough to easily configure this solution.

STEP 2: Configuring the INIT.ORA File

Modified init.ora sample
# Cache and I/O #
db_block_size = 8192
db_domain = detroit.usa
remote_login_passwordfile = EXCLUSIVE
control_files = ("D:\newlogs\contro199.ctl",
compatible =
# Database Name #
db_name = prod901
instance_name = prod901...
tracking img