MSCD640 Assignment 1

Description

MSCD640 Assignment 1

Part 2: Hands-on exercise

You’ve just been through an eight week intensive training for database administration. You are now a support DBA. You’ve been assigned to handle tickets for a database environment. Handle each of the trouble tickets below. Capture all of your work as either screen shots or spooled output. You’ll need to hand in this output to the shift manager at the end of your shift that verifies you successfully handled each ticket.

———————————————————————————
Ticket #1: Production DBA has reported that the database you’re assigned to is not in archive log mode. You need to enable archive logging in your assigned database.

Verify:
SQL> archive log list;
———————————————————————————
Ticket #2: User has reported that they’re receiving an open cursors error from the application. You need to verify the database initialization setting of open_cursors and double the setting (for example, if it’s 50, change it to 100).

Verify:
SQL> show parameter open_cursors;
———————————————————————————
Ticket #3: Production DBA notices abnormal wait times related to the online redo logs. The DBA recommends that you immediately add one online redo log to your database. Make sure you size the new online redo log the same size as the current logs.

Verify:
SQL> select group#, member from v$logfile;
———————————————————————————
Ticket #4: Development DBA has filed a ticket asking for an additional tablespace. Create a tablespace, named AP_DATA size its datafile at 20M.

Verify:
SQL> select tablespace_name from dba_tablespaces;
———————————————————————————
Ticket #5: Development team has filed a ticket asking for a new user account. Create a user named AP_MGMT with the password of f00b0r and assign it the following:
• Assign it the default tablespace of AP_DATA
• Assign it the temporary tablespace of TEMP
• Grant it connect, create table, and create sequence
• Alter the user to have a quota of unlimited on the AP_DATA

Verify:
SQL> connect ap_mgmt/f00b0r
SQL> select * from user_users;
———————————————————————————
Ticket # 6: Development team is requesting that you create two tables in the AP_DATA account: EMP and DEPT. Here are the requirements.

• The DEPT table needs 2 columns: dept_id, dept_name
• The dept_id is a number.
• The dept_id is the primary key.
• The dept_name is varchar2(30)
• The dept_name needs a check constraint that limits it to the following values (‘HR’, ‘IT’, ‘SECURITY’)

• The EMP table needs 3 columns: emp_id, emp_name, dept_id
• The emp_id column is a number.
• The emp_id column is the primary key.
• The emp_name column is varchar2(30)
• The dept_id column is a number.
• The EMP(dept_id) column needs a foreign key constraint defined that references the DEPT(dept_id) parent table.

Verify:
SQL> desc emp;
SQL> desc dept;
———————————————————————————
Ticket #7: The application team needs you to seed the EMP and DEPT tables with data:

insert into dept (dept_id, dept_name) values(1, ‘HR’);
insert into dept (dept_id, dept_name) values(2, ’IT’);
insert into dept (dept_id, dept_name) values(3, ‘SECURITY’);
insert into dept (dept_id, dept_name) values(4, ‘WAREHOUSE’);

insert into emp (emp_id, emp_name, dept_id) values (50, ‘GEORGE’, 2);
insert into emp (emp_id, emp_name, dept_id) values (20, ‘JANE’, 1);
insert into emp (emp_id, emp_name, dept_id) values (30, ‘JOHN’, 3);

Verify:
SQL> select * from emp;
SQL> select * from dept;
———————————————————————————
Ticket #8: After the EMP and DEPT tables have been created and seeded (prior two steps). An end user is reporting strange locking issues with the DEPT and EMP tables. You need to run a script that validates whether or not the EMP table has an index created on the foreign key column.

———————————————————————————
Ticket #9: Development team is reporting that they now require an index be added to the EMP table on the dept_id column. Make its tablespace APP_DATA.

Verify:
SQL> select index_name, column_name from user_ind_columns;
———————————————————————————
Ticket #10: The storage manager is concerned about disk space. The manager wants a report showing how much space all of the tablespaces in your database are consuming. The manager would like to see space free and space consumed in the report.

———————————————————————————
Ticket #11: The security department is reporting a massive security breach!! They are requesting that you lock all users in your database except for SYS.

———————————————————————————
Ticket #12: The application team wants a report showing the space used for each table and index in the AP_MGMT account:

———————————————————————————
Ticket #13: The production DBAs have requested that archive logging be disabled for your database. Disable archive logging in your database:

Verify:
SQL> archive log list;
———————————————————————————
Ticket #14: With a bit of sadness, you realize your shift will end soon. Before you leave, your team leader would like you to write few sentences indicating whether or not your training prepared you adequately for the job. Do you have any recommendations for your team leader?