ORA-13516: AWR Operation failed: SWRF Schema not initialized

Problem Description
While executing dbms_workload_repository package the following error message is reported.

sql> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>0); END;
ERROR at line 1:
ORA-13516: AWR Operation failed: SWRF Schema not initialized
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133
ORA-06512: at line 1

Cause of the Problem
These errors have happened because of wrong or invalid objects with respect to AWR.

Solution of the Problem
In order to resolve the issue you need to drop and recreate the AWR objects, which you can do by running CATNOAWR.SQL and CATAWR.SQL scripts.

Note that from 10.2 onwards, the script name has changed. The catalog script for AWR Tables, used to create the Workload Repository Schema is CATAWRTB.SQL .

In case of Oracle 10gR1,
To re-create the AWR objects, by running the catnoawr.sql and catawr.sql scripts located in $ORACLE_HOME/rdbms/admin.

SQL> @$ORACLE_HOME/rdbms/admin/catnoawr.sql
SQL> @$ORACLE_HOME/rdbms/admin/catawr.sql
SQL> shut immediate
SQL> startup

In case of Oracle 10gR2,

SQL> @$ORACLE_HOME/rdbms/admin/CATAWRTB.sql
SQL> shut immediate;
SQL> startup

On re-start of the database instance, the AWR tables will be populated with the required data.

Note that, the CATNOAWR.SQL scripts is no longer available in 10.2 and cannot be found
in the $ORACLE_HOME/rdbms/admin directory.

BSLN_MAINTAIN_STATS_JOB fails with ORA-12012, ORA-06502, ORA-06512

Problem Description
The BSLN_MAINTAIN_STATS_JOB doesn't run anymore.
From the trace file we see the following information.
ORA-12012: error on auto execute of job 11689
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073

Cause of the Problem
Table DBSNMP.BSLN_BASELINES contains inconsistent information. In this case, after database cloning, existing records in table "DBSNMP.BSLN_BASELINES" conflict with new baseline information inserted in the cloned database.

Solution of the Problem
The DBSNMP user needs to be dropped and re-created.

Login as sys user.

SQL> sqlplus / as sysdba

From the sqlplus execute the following:

Drop the user by executing catnsnmp.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql

Create the DBSNMP user by executing catsnmp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql

Oracle Tablespace Exercises for Newbie

One of the more confusing aspects of the Oracle database is the fact that you musts deal with both a logical and a physical side to some of the more important database objects. Both the Redo Log and Tablespace in the Oracle database have a logical side and physical side in referencing the object. The logicalreference is the notation in the data dictionary that defines the object and its storage components, and the physical side consists of the actual data file associated with the object.
A tablespace, the database object in Oracle that is responsible for the actual table objects (as well as other types of objects) and resulting data storage, is made up of both the logical and physical aspects. The logical actually defines how storage of objects in the datafile will be handled, while the physical is made up of the actual data file created in the OS. Oracle has several ways of handling these datafiles--either as dictionary managed or as Oracle managed files. For the purposes of this lab, you will be dealing with dictionary-managed files.
In this lab you will be creating a series of tablespaces and related files, and then looking at the database to see how the storage settings you defined are accommodating changes to the database.
Use the following information as a guide for creating your new tablespaces:
Tablespace   Subdirectory   Data File     (Size)    Initial/Next   PctIncrease
Name           Name
------------ ------------- -----------   ------- ------------ ----------
INDX01        DISK3            indx01.DBF   (1M)     16K             0
USERS01      DISK1            users01.DBF (2M)     16K             0
RONLY         DISK1            ronly.DBF     (1M)     16K             0
TEMP01       DISK3            temp01.DBF  (1M)     local -
Creating a SPOOL session: Be sure that you start a spool session before you start executing your SQL code when working on the lab. Your SPOOL file name should be DATABASE_lab4. If you do the lab in several sessions, be sure that you use a different file name, so not to overwrite the existing file (for example DATABASE_lab4, DATABASE_lab4a, DATABASE_lab4b etc.)

L A B   S T E P S

Step 1: Create a series of Tablespaces

First,set up the CREATE TABLESPACE commands to create the permanent DICTIONARY MANAGED tablespaces and the single TEMPORARY tablespace, based on the names and configurations shown above. Keep in mind that the TEMP01 tablespace is to be a TEMPORARY tablespace and use EXTENT MANAGEMENT LOCAL with a uniform extent size of 512K. You also want to allow the reuse of extents.
Once you have your CREATE TABLESPACE statements complete, start an SQL*Plus session and connect to your database instance as the SYS AS SYSDBA users. Execute your statements one at a time to create the tablespaces, correcting any errors as you go. Remember that once a tablespace is successfully created, you should move on to the next tablespace. Trying to recreate a single tablespace more than once will cause an error.
Once you have successfully created all 4 tablespaces, query the dynamic performance view DBA_DATA_FILES twice to verify the results of your work. Each query will provide a little different view of your work.
Your response will look similar to this:

FILE_NAME                                                                                               FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------- ---------- ------------------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF                    1 SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK3\INDX01.DBF                         3 INDX01
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF              4 USERS01
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF                 5 RONLY
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF                    2 SYSAUX
5 rows selected.


TABLESPACE_NAME                      BYTES      BLOCKS  STATUS
------------------------------ ----------- ---------- ---------
SYSTEM                                198025216        24173 AVAILABLE
INDX01                                     1048576           128 AVAILABLE
USERS01                                   2097152           256 AVAILABLE
RONLY                                      1048576           128 AVAILABLE
SYSAUX                                 167772160       20480 AVAILABLE
5 rows selected.

Step 2: Relocating a Tablespace and datafile

In this step you are going to relocate the INDX01 tablespace from DISK 3 to DISK 5. Keep in mind that when renaming your datafile, Oracle looks at the entire path for the datafile as the datafile name. In this sense, you are not actually changing the name of the files, but rather the location. To Oracle that is the same thing.

Step by Step Hints:

1. Start an SQL*Plus session and execute the ALTER TABLESPACE command to take the tablespace INDX01 OFFLINE.
2. Query the dynamic performance view V$DATAFILE to verify the result.
3. Physically copy the INDX01.DBF datafile from the DISK3 FOLDER TO THE DISK5 FOLDER within the subdirectories within you instance directories that you mapped to the network drive.
4. Back in your SQL*Plus session, execute the ALTER TABLESPACE RENAME DATAFILE command to rename the files. In this command you want to use the full path to the datafile that starts with the F:\ as you see it in step one. This process is identical to renaming a LOGFILE as shown on page 160 in your book.
5. Execute the ALTER TABLESPACE command to take the tablespace INDX01 ONLINE.
6. Verify the results using the same query as in step 2.
7. Remove the old file from the subdirectory.

Your response will look similar to this after hint #2:

NAME                                                                                                   STATUS
--------------------------------------------------------------------------- ---------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF        SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF        ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK3\INDX01.DBF            OFFLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF     ONLINE
5 rows selected.

Your response will look similar to this after hint #6:

NAME                                                                                                   STATUS
--------------------------------------------------------------------------- ---------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF        SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF        ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK5\INDX01.DBF            ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF     ONLINE
5 rows selected.

Step 3: Verify Existing Tablespaces in your instance

Query the dynamic performance view V$TABLESPACE to verify the tablespaces you currently have in your database. Notice the difference in the results from what you have been seeing.
Your response will look similar to this:

       TS#   NAME
---------- ----------
         0    SYSTEM
         1    SYSAUX
         2    INDX01
         3    USERS01
         4    RONLY
         5    TEMP01
6 rows selected.

Step 4: Working with a Read-Only Tablespace

First, create a table name T1 with one number data type column in the RONLY tablespace.
Your response will look similar to this:

Table created.

Next,using an ALTER TABLESPACE command setthe RONLY tablespace to read only.
Your response will look similar to this:

Tablespace altered.

Query the v$datafile view to verify that the tablespace has been altered.
Your response will look similar to this:

NAME                                                                                                   ENABLED       STATUS
--------------------------------------------------------------------------- ------------- -------
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSTEM01.DBF        READ WRITE  SYSTEM
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\SYSAUX01.DBF        READ WRITE  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK3\INDX01.DBF            DISABLED      OFFLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\USERS01DV001.DBF  READ WRITE  ONLINE
F:\DBM\SPRINGB\DB1000\ORACLE\ADMIN\ORADATA\DISK1\RONLYDV001.DBF     READ ONLY    ONLINE
5 rows selected.

Now attempt to create an additional table named T2.
Your response will look similar to this:

CREATE TABLE t2 (t2 number) TABLESPACE ronly
*
ORA-01647: tablespace 'RONLY' is read only, cannot allocate space in it

Now drop the original table (T1) that you first crated.
Your response will look similar to this:

Table dropped.

Create a text document to be turned in with the lab files, to explain why you could not create a new table after the RONLY tablespace status was changed, yet you could drop an existing table when the tablespace was in READ ONLY status. Save your file as yourname_LAB04_step4.txt.

Step 5: Dropping a Tablespace

Create and execute the DROP TABLESPACE command to drop the RONLY tablespace. Be sure that you delete the associated data file from the directory structure after you have successfully dropped the tablespace from the DBMS.

Hints:

• Execute the DROP TABLESPACE. Since there is a table still in the tablespace, you will need to use the INCLUDING CONTENTS constraint to remove the tablespace.
• Delete the operating-system files.

Now query the dynamic performance view V$TABLESPACE to verify the result.
Your response will look similar to this:

       TS#   NAME     
---------- ----------
            0 SYSTEM   
            1 SYSAUX   
            2 INDX01   
            3 USERS01  
            5 TEMP01      
5 rows selected.

Step 6: Addressing UNDO Management

Prior to Oracle9i, only manual undo management was available through the creation and use of multiple Rollback Segments. Rollback segments are involved in every insert, update and delete transaction that happen in the database. As of Oracle9i, undo management can be handled through a special tablespace called an "undo tablespace." In this section of the lab we will add and set up another tablespace named UNDOTBS to act as our undo management tablespace.
First, use the SHOW PARAMETER UNDO command in your SQL*Plus session to find out the current condition of undo management for your instance. You should find that the instance is in MANUAL undo management.

Your response will look similar to this:
NAME                                            TYPE          VALUE
------------------------------------ ----------- -------
undo_management                           string         MANUAL
undo_retention                                integer       900
undo_tablespace                             string

The database needs to be in AUTO undo management, so you will need to do a little manipulation here.

• Shutdown you instance using SHUTDOWN IMMEDIATE.
• On your mapped network drive, go into your INITDB###.ora file and uncomment the line UNDO_MANAGEMENT=AUTO.
• Now start your instance up again using the INITDB###.ora pfile.
• Look at the parameters for UNDO again.

Your response will look similar to this:

NAME                                            TYPE         VALUE
------------------------------------ ----------- ----------
undo_management                           string         AUTO
undo_retention                                integer       900
undo_tablespace                             string

Now we can create an undo tablespace. Using a CREATE statement similar to the one you will find in the textbook, create an undo tablespace named UNDOTBS with a 2M datafile and place the datafile in the Disk4 directory of your OFA structure. Remember that you must use the complete path to the F:\ drive for the datafile, just as you did for the tablespaces you created in step 1.

• Now, once again, shutdown you instance, using SHUTDOWN IMMEDIATE.
• On your mapped network drive, go into your INITDB###.ora file and uncomment the line undo_tablespace=UNDOTBS parameter.
• Now start your instance up again, using the INITDB###.ora pfile.
• Once again, look at the parameters for UNDO. This time you should see your new tablespace listed.

Your response will look similar to this:

NAME                                            TYPE         VALUE
------------------------------------ ----------- ----------
undo_management                           string         AUTO
undo_retention                                integer       900
undo_tablespace                             string         UNDOTBS

This concludes the Lab for Week 4. Be sure to enter a SPOOL OFF command to end your spool session and save your output file.

Logical Database Architecture Exercises

Often times, the job of a DBA involves making assessments and decisions about the database, using data that is accumulated through testing and monitoring the database activity. There are all sorts of things that can affect the performance of a database, and most of them are not restricted to the number of people logged on and using the database.
Obviously, the rate at which the database grows can have a direct effect on how the database reacts to use and traffic. The things that can cause the database to start using excessive amounts of storage space are not always so obvious, but many of them are logical. Oracle's dictionary managed files cause the most administrative overhead, and knowing or being aware of some of the more common things can help eliminate obvious problems more easily. The first part of this exercise addresses some of these problem areas.
The second part of the exercise deals with the storage makeup of the Oracle database. Oracle's architecture allows for storage management and configuration in multiple areas. Understanding how the different parts of the storage architecture work together and relate to each other is an integral part of understanding how to configure a database at creation. In this part of the exercise, you will need to relate your understanding of Oracle's storage structures and how they relate to and work with one another.
Now let's get started.

I N D I V I D U A L     E X E R C I S E S

HANDS-ON #1: Storage space not adequate

Your current database uses dictionary-managed tablespaces. In running various performance tuning scripts, you have discovered that one of these tablespaces seems to have run out of space long before you calculated that it would. In tracking activity in the tablespace, you discover that the process of tables being created, then dropped and re-created, with different storage settings, has been excessive. Give an explanation for why your tablespace might have a problem, and list two actions you can take to lessen the problem.
Place and save your answers in a Word document named week4_exercise.doc.

HANDS-ON #2: Data blocks, extents, segments and tablespaces

Understanding the various parts of data storage and allocation in Oracle can help identify problems more easily.

• In your own words, provide an explanation of the difference between a data block, an extent, a segment, and a tablespace.
• Give an example of how the four are related.
• What is the relationship between a segment and an object created in a tablespace?

OMF, init.ora Parameter files, Startup, Shutdown Exercises

G E N E R A L  O V E R V I E W

Scenario/Summary

Oracle provides two primary types of file management; User Managed Files (UMF) and Oracle Managed Files (OMF).  As part of this exercise, you will need to supply some information as to how you would use both of these approaches and discuss some of the advantages of each.
Along with creating a database, there are other additional files that must be created or altered.  One such file is the INIT.ORA file, or initalization file for the database.  The example code in Step 2, although not a complete init file, will need some modifications made to it.
Finally, this exercise will introduce you to the processes and steps of shutting down your database and starting it back up again using the initialization parameter file speific to your database.  You will go through this process many times during your labs, starting in Week three, so it is best to get the hang of it now, while we have some time.
For exercise part three, you only need access to your database instance.  If you have any difficulties connecting your database instance, notify your instructor as soon as possible.
Now you are ready to proceed.

I N D I V I D U A L     E X E R C I S E S

HANDS-ON #1: Working with Dictionary and Oracle Managed files

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:

1. Describe how you plan to rectify the I/O problem with the new Oracle10g database using the UMF method.
2. Describe how you would solve the same problem using the OMF method.

Place and save your answers in a Word document named week2_exercise.doc.

HANDS-ON #2: Configuring the INIT.ORA file

The lines of code below are from an existing init.ora file, and they contain several errors.  After reviewing the code do the following:

1. List the errors you find. 
2. Rewrite the code with corrections.  Format the code as you would expect to see it in the init.ora file with appropriate section titles in comment areas.  You can use the example in the iLab Manual as a guide.

###############
 Cache and I/O
###############
db block size=8192, db domain="detroit.usa"
remote login passwordfIle=EXCLUSIVE control_files=("D:\newlogs\contro199.ctl") maxinstances=2
compatible=101020
###############
 Database Name
###############
db name=prod901.detroit.usa
instance name=trialO2

Place and save your answers in the Word document named week2_exercise.doc that you have started for this week's exercises

HANDS-ON #3: Manual Shutdown and Startup of the Database

For this part of the exercise, you are going to shutdown your individual database instance and then open it up in stages, using the SQL*Plus editor.  The process you will use is outlined in the iLab Manual, under the section titled "Shutting Down and Opening the Database Instance".  You might want to reference the iLab Manual as a guide for this exercise.  The following steps will take you through this process.  Once you have successfully logged into Oracle through the editor, start a spool session to capture your results.  Refer to the iLab Manual for the correct way to set up a SPOOL session in the Citrix environment.  The spool file will be what you submit for a grade for this part of the exercise.  NOTE: This process is Oracle specific and not version specific.  Some of the examples may show a different release of Oracle than the one you are using.  This will not affect the outcome of this exercise.
Let's get started.

1. You will first need to log into the Oracle lab environment through Citrix.  If you have not done this yet, first follow the instructions in the iLab Manual for logging into Citrix.  Once you are into Citrix, select the Oracle folder to access the different tools available.
2. Select and open the Windows Explorer application from the available icons.  Once open then open up the O: drive.  Continue to navigate through the folders until you find the folder titled 'Pfile'.  Inside this folder is a file named INITOL###.ORA (where ### is your instance number).  You want to write down or copy the full path to this file starting with the O: drive letter.  Be sure you include the actual file name in your path.  You will need this in the steps to follow.  Once you hve done this you can close the Windows Explorer.
3. Now select the SQL*Plus icon in the Oracle folder and open the SQL Plus editor.  Once the application starts you should see a login box.
4. Log into your database instance using SYS as the User Name, ORACLE as the password, and DB###.WORLD AS SYSDBA in the Host String box (where ### is your instance number).
5. Once connected to Oracle, you should note down your screen.

Notice that the editor states that you are "Connected to:" with the specific information about the database.  Now at the SQL> prompt start your spool session by typing SPOOL V:\HANDS_ON2_3_LASTNAME.TXT (where LASTNAME is your last name).  Remember that the V: drive is the network equivalent of the C: drive on your local machine.
Next, you need to shut down the database.  Type in the command SHUTDOWN IMMEDIATE and then hit ENTER.  The database will go through MOUNT and NOMOUNT states as it shuts down and will end by stating "Oracle instance shut down".
Now you want to go through the process of opening the database into each of the various modes.  You will be starting up the database and shutting it down several times during this process.  NOTE: You are doing this exercise from a mapped network drive in Citrix so the path to your init.ora file will start with the O: drive at \oracle, as in O:\DB####\oracle\admin\pfile\initdb###.ora.  If in doubt, refer to the iLab Manual for additional help and examples.
The following will outline the steps to follow.
Start the database in NOMOUNT --

1. Use STARTUP NOMOUNT PFILE= and then place the complete path to your initol###.ora file at the end.  Your path should look similar to O:\oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in NOMOUNT, shut the instance back down using SHUTDOWN IMMEDIATE.  Do not be alarmed when you see what looks like an error message saying the database is not open.

ii. Start the database in MOUNT --

1. Use STARTUP MOUNT PFILE= and then place the complete path to your initol###.ora file at the end.  Your path should look similar to O:\Oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in MOUNT, shut the instance back down using SHUTDOWN IMMEDIATE.  Do not be alarmed when you see what looks like an error message saying the database is not open.

iii. Start the database in OPEN --

1. Use STARTUP PFILE= and then place the complete path to your initol###.ora file at the end.  Remember that the OPEN key word is optional.  Your path should look similar to this: O:\Oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in NOMOUNT, shut the instance back down using SHUTDOWN IMMEDIATE.  Do not be alarmed when you see what looks like an error message saying the database is not open.

iv. For this final step, you will start the database in NOMOUNT and then ALTER the database to get it to the OPEN status.

1. Use STARTUP NOMOUNT PFILE= and then place the complete path to your initol###.ora file at the end.  Your path should look similar to O:\Oracle\admin\pfile\initDB400.ora, only your instance name will be in the file name in place of DB400.
2. Once the database is in NOMOUNT, issue an ALTER DATABASE MOUNT command.  Remember to end the command with a semicolon.
3. Once in MOUNT status (you should get a ?Database altered? reply from the system) then enter the ALTER DATABASE OPEN command.
4. Verify that you database is in OPEN status by querying the STATUS column from the V$INSTANCE data dictionary view.

v. Enter SPOOL OFF to end the spool session.  DO NOT shutdown you database instance.
You can now close the SQL Plus editor and exit out of the Desktop.  Remember to Log Off the Citrix server by selecting the Log Off button.  DO NOT just close the window as this will keep your session open.  Remember that the spool file you created is on the C: drive of your computer

Oracle Database Creation Exercises

L A B   O V E R V I E W

Scenario/Summary

Although Oracle provides a very nice graphical interface tool call "Enterprise Manager" to aid the DBA in working with the database, it is not always the tool of choice.  Many DBAs find it preferable to use command-line execution of scripts and procedures to do administrative work.  One such case is in the creation of a new database within the management system.
Although creation of new database requires the editing of several support files for the database (the listener.ora and tnsnames.ora files in particular), the CREATE DATABASE script is the essential file that would need to be run to accomplish this process.  In this lab you will be responsible for writing a CREATE DATABASE script that would create a new database named LABII, based on a specific set of requirements that will be listed later.  The basic configuration for the server the database instance will use is:

1. Disk Drives: There are three disk drives you will be using – C:\ - E:\ - F:\
2. OFA directory paths:  The OFA path for each drive that you will use to place the described files in is:

 \ORACLE\ORADATA\ORACLE

VERY IMPORTANT – DO NOT ATTEMPT TO RUN YOUR SCRIPT FILE OR TEST IT!!  Doing so could cause unforeseen problems and that is not the objective of the lab.  You will be furnished with a solution later that you can compare your example against.

L A B   S T E P S

Step 1: The CREATE DATABASE SQL

You are being asked to create the CREATE DATABASE script for a new database instance.  The name of the new database should be LABII.  Create a script file using Notepad that would create this database containing:

• The following parameters need to be defined and settings assigned: 
  
  
  
  • maxlogfiles to allow up to 5 groups
  • maxlogmembers to allow up to 4
  • maxdatafiles to allow up to 100
  • maxinstances to allow only 2
  A system datafile should be based on the following guidelines:
  
  
  
  • Named SYSTEM01.DBF Located on the C: with the directory path noted above.  Have a storage clause set to AUTOEXTEND ON NEXT 10240K and should have a MAXSIZE of UNLIMITED. 
  • Have a file size of 100M.
  
  
  
  
• A system auxilary datafile should be based on the following guidelines:
  
  
  
  • Named SYSAUX01.DBF Located on the C: with the directory path noted above.  Have a storage clause set to AUTOEXTEND ON NEXT 10240K and should have a MAXSIZE of UNLIMITED. 
  • Have a file size of 100M.
  
  
  
  
• Two logfile groups should be created based on the following guidelines:
  
  
  
  • Each group should have two 4M logfiles.  Log group one will have file REDO01A.LOG located on the C: drive and REDO01B.LOG located on the E: drive.  Each drive should use the directory path noted above in the opening scenario/summary section.
  • Log group two will have file REDO02A.LOG located on the E: drive and  REDO02B.LOG located on the F: drive.  Each drive should use the directory path noted above in the opening scenario/summary section.
  
  
  
  

You can include settings for character sets if you wish, however, since this database resides in an already defined DBMS, the default character sets defined with Oracle was installed would be used.Be sure to include a semicolon at the end of the script to indicate the end of the script.
REMEMBER-–You are not required, nor should you try, to execute this script.

Related Documents

Basic Oracle Sql Exercise

Practice Exercise for Oracle PL/SQL

Oracle Procedure and Function Practice

Exercise Oracle package and package body

Database design practice for a Movie Company

Oracle Enterprise Manager Excercises

Oracle Database Installation Exercises

Oracle Database Creation Exercises

Database Administration Exercises - Week 3

Temporary Tables and Data Types Exercises

Exercises with Oracle Create Table Add Columns

Practice with User Profiles and User Roles in Oracle

Practice Oracle Joins Examples

Oracle Object type Exercises - VARRAY

Audit and Profile Management Exercises in Oracle