Hi All,

I am the CEO here at Kudrant Academy and have over 10 years oracle DBA experience. I’m thrilled to be your guide throughout this course.

In this course, I will teach you the strategies and principles needed to stay focused and on track in achieving your goal of becoming an Oracle DBA.

These principles are at the core of any success story and will be reviewed throughout the program

It will be up to you to “take action” by completing all the course assignments.

Ready?

Let's get started!

Imgard Ekokobe
CEO/Database Engineer
Kudrant Academy

Next

Firstly, if you are a member of Kudrant Academy, please post questions about this Lab (or any other database related questions) to the  Facebook Group.

Here’s how to get the most out of the hands-on practice exercise:

Next

• Install the Oracle software.
• Create a database.
• Create DBA accounts.
•  Design and create objects
•  Use scripts to create all objects.
•  Create tablespaces on disks.
•  Create tables in the tablespaces.
•  Populate tables with test data.
• Backup the database using data pump export and RMAN.
• Create user accounts for end users.
• Collect statistics on how your instance is running
• Manually add and delete columns from tables.
• Monitor space utilization.
•  Monitor the alert log.
•  Monitor user activity.
•  Test the recovery capability.
•  Startup and shutdown the instance after changes are made.
• Set up cron jobs that can run continuously.
•  Upgrade and patch a database.

Next

Download and print out the workbook for this project.

Next

Databases are carefully planned and designed after gathering and analyzing user requirements from stakeholders.

After the requirements, have been gathered and defined, a model will be created for the database.

This model will be used to use to create the Entity Relationship Diagrams (ERD) and these ERDs will then be translated into Data Definition Languages (DDL).

The DDL script will be used to create your database tables and the DML script will be used to load some data into your tables. You can also use tools such as SQL loader, data pump import to load bulk data into the database.

After the database has been populated with data, you will perform certain tasks to ensure that the database is available to the end user and can be recoverability in event of data loss.

These are some tools to help you design, develop and administer databases.

•  An editor (notepad in your case).
•  SQL*Plus, and capabilities to print your source code and capture the output.
•  Putty
•  Winscp
•  ER Assistant tool.
•  DBCA
•  DBUA
•  NETCA
•  LSNRCTL
•  Data pump export/import
•  SQL Loader
•  AWR
•  ADDM
•  SQL Developer
•  Oracle Enterprise Manager Cloud Control 13C1

Next

Develop an ERD from the DDL of the ten tables.

Next

Create DDL.

1. Create the DDL for the ten tables.
2. Be sure the tables can be joined.

Next

Create the DDL for the tablespaces.

1. Size the tablespaces.
2. Create DDL for the tablespaces.
3. Modify the table DDL so each table is assigned to one of the tablespaces. (This step has been

completed – See DDL provided.)

Next

1. Set up DBA accounts. You will connect from your DBA accounts to create the applicationschema account.
2. Create application account.

Next

1. Execute the DDL for the tablespaces from your DBA account.
2. Connect as the APP_USER
3. Execute the DDL to create the objects.
4. Verify that all the objects were created by querying the data dictionary.

Next

1. Take a full database export of your database.
2. Set up a cron job so an export is done every night at a specific time for a week ONLY. Check to be sure that the file is actually written!

Next

1. Load 50,000 records in the zip table (use sqlload).
2. Load at least 1,000 records on the other tables.
3. You may also use the Insert into as select statement to load data into two of your tables.

Next

Gather statistics on all ten tables.

Next

1. Restructure database objects by using the ALTER TABLE command. You will create the

DDL statements to modify the tables in questions A-E in scripts and execute these scripts.

1. Modify one of your tables and add a column. Use a query and see if it successfully added

the column.

1. Modify a table by making one column longer.
2. Modify a table by making one column shorter.
3. Modify a table by changing a column from a NUMBER to a CHAR.
4. Modify a table by changing a column from a CHAR to NUMBER.
5. Restructure a table by actually creating a new object.
6. Delete a column on a table. Do not use the DROP COLUMN command.

 Create a table like the one that in which the column should be deleted. The new table

should not have the old column.

 Use a mass insert to copy the data to the new table.

 Check the data in the new table and be sure it is right.

 Add a primary key to the new table the using the ALTER TABLE command.

 Add any foreign keys.

 Drop the original table.

 Rename the new table.

Next

TEST 1:

Embed SQL in a script that can be executed a variable number of times. The embedded SQL should

do the following:

1. Select all the rows from one table.
2. Join two tables.
3. Join ten tables primary to foreign keys.
4. While the script is executing, gather information about the session, locks, SQL, CPU and

memory usage, I/O, any wait events, DB wait time, load on server.

TEST 2:

1. Update 1,000 rows and roll back the changes.
2. Insert 1,000 rows into a table and then rollback.
3. Delete 1,000 rows from a table and then rollback.
4. While the script is executing, gather information about the session, locks, SQL, CPU and

memory usage, I/O, any wait events, DB wait time, load on server.

TEST 3:

1. Select all rows from one table.
2. Join two tables.
3. Join ten tables primary to foreign keys.
4. Update 1,000 rows and rollback.
5. Insert 1,000 rows and rollback.
6. Delete 1,000 rows and rollback.
7. While the script is executing, gather information about the session, locks, SQL, CPU and memory usage, I/O, any wait events, DB wait time, load on server.

Next

Set up a script for the recovery tests:

1. Design a script that creates a small table and Insert 3 rows on the table. The table

should not have a primary key.

2. Be sure to add a drop table statement at the beginning of the script since you will be

executing this script several times.

Script name: rec-tests.sql

TEST 1:

1. Run the script rec-tests.sql to create a small test table with three rows in it.
2. Take an data pump export backup of the table.
3. Delete two rows. Now there is one row on the table.
4. Recover the data using data pump import. Did you get the 1 row back?
5. Explain what happened.

TEST 2:

1. Run the script rec-tests.sql to create a small test table with three rows in it.
2. Take an RMAN backup
3. Delete two rows. Now there is one row on the table.
4. Recover the data using the RMAN restore and recover commands.
5. Did you get the 1 row back?
6. Explain what happened.

TEST 3:

1. Run the script rec-tests.sql to create a small test table with three rows in it.
2. Take a data pump export of the table
3. Delete two rows. Now there is one row on the table.
   4. Insert a new row.
   5. Recover the data using import. Did you get 2 rows back?
   6. Explain what happened.

   TEST 4

   1. Run the script to rec-tests.sql create a small test able with three rows in it.
   2. Take an RMAN backup
   3. Delete two rows. Now there is one row on the table.
   4. Insert a new row.
   5. Recover the data using the RMAN restore and recover commands.
   6. Did you get 2 rows back?
   7. Explain what happened.

   TEST 5:

   1. Run the script rec-tests.sql to create a small test table with three rows in it.
   2. Take a data pump export of the table
   3. Drop the table.
   4. Use data pump import again. Were you able to recover the table with the correct data?
   5. Be able to explain what happened.

   TEST 6:

   1. Run the script rec-tests.sql to create a small test table with three rows in it.
   2. Take an RMAN backup.
   3. Drop the table.
   4. Use RMAN recover and restore. Were you able to recover the table with the correct

   data?

   5. Explain what happened

   TEST 7:

   Repeat the export/import and RMAN commands, but this time, create a primary key on the

   table in the script.

   TEST 8:

   Repeat the sequence of changes to the test table, but use flashback query in the recovery

   operations.

Next

1. Check status of database. script name: TeamName_db-status.sql
2. Monitor space. script name: TeamName_monitor-space.sql
3. Check statistics. script name: TeamName_db-stats.sql
4. Check for invalid objects. script name: TeamName_check-invobjects.sql

Next

1. Check status of database.
2. Monitor space.
3. Check statistics.
4. View objects in the database.
5. Use OEM to identify the top 10 intensive SQL statements in the database
6. Create a data pump job
7. Create database start/stop alerts (rules). Space monitoring
8. Create a tablespace
9. Check the status of your database
10. Clone a user account
11. Create AWR report for your database
12. Create ADDM report for your database

Next

1. Execute scripts to create end users. Team kud01 will be end users for kud02, kud02 for kud03, kud03 for kud04, kud04 for kud05 and so on. The last team will be end users for kud01. Each user should be entered as individuals, rather than as part of the team account.
2. Create synonyms for each of your ten tables so the prefix does not have to be used by your end users.
3. You will connect to the database where you are an end user and try to select some data from one of the tables. What happens? Why?

Next

While end-users are performing work on your database, run scripts that will show who is doing work and what code they are running.

Next

A list with seven disks (/u01-/u07) and what will reside on them

Next

1. Use OUI to install the Oracle software.
2. Create a database using DBCA.
3. Look at the parameter file used by the instance.

Next

1. Use DBCA to create an oracle database.
2. Look at the parameter file used by the instance.

Next

1. Check that the oracle installation was successfully completed.
2. Check that the oracle database was successfully created and you can connect to it.

Next

1. Upgrade the database dbtrain to 12c.
2. Re-register the new 12c database with the listener.
3. Check for invalid objects after the upgrade.
4. Gather statistics on the upgraded database.

Next

1. Patch the oracle 11gr2 home and database.
2. Patch the oracle 12c1 home and database.

Next

Compile all your files from task 1-21 in a word document and submit to

the class page.

 All log files and scripts used should be added as an appendix at the end of

your document.

Complete Course