Oracle Database Administration – Lab #2
Creating a Database
STEPS FOR CREATING A DATABASE
1. Logon to your account .
Travel up the subdirectory tree to the next higher subdirectory (for example, /a01/student) , by using the cd . . command. Check the security permission settings on your account (use the ls -al | more operating system command). The permissions need to read as follows for your account (permission level is 775):
drwxrwxr-x 3 user350 dba 4096 May 30 23:44 user350
If the permission settings are not as shown above, Use the chmod 775 user350 (substitute your username) command to set the permissions to 775. This is to allow other members of the dba group to write to your subdirectory. This is necessary because Oracle is also a system user and is a member of the dba group. Oracle must write to your subdirectory when creating a database. Do this for each of the subdirectories.
Create a directory named oradata inside your $HOME directory on each of the disk drives (/a01 through /a05). Set the permissions to 775 for each of the oradata directories.
2. Set the ORACLE_SID and LD_LIBRARY variable values .
Return to your home directory (cd $HOME). Use your assigned system account name for your ORACLE_SID, e.g. if your account is user350, your ORACLE_SID will be set to USER350. This will also be the value for the DB_NAME parameter in your init.ora file. The command to set the environment variable from the UNIX prompt is:
$ ORACLE_SID=USER350
$ export ORACLE_SID
You should have completed Lab 1 that required you to set the various environment variables such as the LD_LIBRARY in your .profile file so that the environment variables are set automatically while working with the database you will create. Ensure that you completed Lab 1 satisfactorily.
Currently our student accounts are set up to prompt you for the ORACLE_SID value when you startup the system. If you hit the ENTER key at the prompt, the ORACLE_SID will be correct. If you want to force the value of the ORACLE_SID (in case you make a typographical error when logging on), you should set the ORACLE_SID within the .profile file. You can accomplish this by adding operating system commands to the .profile file. An example command that must be placed AFTER the command that calls the oraenv script is:
ORACLE_SID=USER350
export ORACLE_SID
Now, logoff and login again. Use the operating system env command to verify the value of ORACLE_SID. Remember that if you alter your .profile file, you must exit Oracle and logon again for the changes to take effect.
3. Create the init.ora parameter file (pfile) .
In Lab 1 you copied the init.ora file to your $HOME directory. Based on this file, create a parameter file named init<username>.ora (example if your user account is user350, then name the file initUSER350.ora). This file needs to be stored to your $HOME directory.
(1 point) Edit the CONTROL_FILES parameter in your initUSER350.ora file. Naming your control files and their location through the CONTROL_FILES parameter causes these files to be created when you create the database. Initially you should set the CONTROL_FILES parameter to create two control files named USER350control01.ctl and USER 350control02.ctl (replace USER 350 with your own user account) – later you will create a third control file as part of another laboratory assignment. The system will establish an appropriate size for these files.
(1 point). Ensure that you create the directories and set directory permissions for the bdump, udump, and cdump locations for your database within your assigned disk space – do not write to the ORACLE2 database’s trace file locations.
Do NOT include the parameter for the remote_login_passwordfile=EXCLUSIVE parameter as you have not yet created an Oracle Password File for your database.
4. Create a script that will create the database including the SYSTEM, SYSAUX, TEMP, and UNDO01 tablespaces
Use the guidelines in Module-5 notes to specify the redo log files and sizes for these tablespaces.
Do not worry about creating a USERS tablespace at this time – you will create the USERS tablespace later in this lab.
Transfer the script to your $HOME directory.
The script should create the database files to the various directories provided to you on disk drives /a01 through /a05 as described above.
5. Connect to SQL*Plus and startup your database in nomount mode. Execute the script to create the database
During execution of the script, you will receive no feedback from the Oracle2 server – you’ll have to wait to see if you receive the Database Created message.
After execution check to ensure that your database files (tablespace datafiles, control files, and redo log files) were created.
Check the alert_sid.log file to ensure that no errors occurred during database creation.
6. Change the PASSWORDS for SYS and SYSTEM
· Now that your database has been created, the database should be in an "OPEN" state.
· Alter the passwords for SYS and SYSTEM if you did not set them as part of the CREATE DATABASE command.
Example: ALTER USER sys IDENTIFIED BY password;
7. Run the Scripts to create the data dictionary.
You must be connected as the user SYS to run the scripts—this will ensure that the data dictionary is owned by the user SYS. The scripts should not be run unattended.
$ sqlplus /nolog
SQL> connect sys as sysbda
SQL> Password: <press the enter key here>
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
· Verify that the objects in the data dictionary are valid. The following query returns any invalid objects.
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner,object_type,object_name;
If any objects are invalid, shutdown your Oracle Instance, then delete all of the files you created that comprise your database and try again to create the database.
8. Running the Product User Profile
Build the user profiles that Oracle will use on LINUX for your database.
The steps are as follows:
While connected to SQL*Plus, execute the host command – this will take you to the LINUX $ prompt – you will be considered to be the user Oracle during this operation.
Type the command: SYSTEM_PASS=system/password
<where password is the system password>
Type the command: export SYSTEM_PASS
Run the product_user profile command at the $ prompt: $ORACLE_HOME/bin/pupbld
You will see a message similar to the one shown here.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
Product user profile table installation finished.
9. Create the USERS Tablespace
Connect as the user SYS.
Create a USERS tablespace that is 5M in size.
10. Create a DBA user
Connect as the user SYS.
You will be the DBA for your database. This requires you to create a DBA user named userXXX where you use your account name for the account that will be the DBA for your database.
CREATE USER user350 IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE Temp
QUOTA UNLIMITED ON USERS
QUOTA UNLIMITED ON TEMP;
Create a second DBA user account that I as your instructor can use to grade your work. My DBA account should be named dbock and a password of "password".
(1 point) Examine the system table named sys.dba_roles and list all of the roles shown in the table. Grant the CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE roles listed to your DBA user account and to the dbock user account. Since the DBA will create other users, you need for the DBA accounts to have the admin option necessary to grant these roles to other users. An example grant command is:
SQL> GRANT CONNECT TO user350 WITH ADMIN OPTION;
Logoff SQLPLUS and reconnect as the user dbock. Confirm that dbock has DBA privileges by executing a DESC DBA_TABLESPACES command. If the view is described, then the user dbock works. If the view is not described, run the synonyms SQL script named catdbsyn.sql (located where the other scripts are located).
Logoff SQLPLUS and reconnect as the DBA user that you created for yourself (the equivalent to user350). Complete the remainder of this exercise as the DBA user you just created. If necessary, run the synonyms SQL script named catdbsyn.sql again, this time as the DBA user you created.
11. Examine Data Dictionary Views
Connect to SQLPLUS as the user DBA user that you created for yourself (e.g., user350) and examine the following tables/views (use the SQL SELECT statement). Your goal is to understand the type of information that is stored in the various tables of the data dictionary. Explain in your own words the apparent purpose of these tables/views. Do not provide me a simple description (through use of the DESC command) of the views/tables—I want to know what you think the use of these tables/views might be by a DBA. You might want to put your answer in the form of a table.
user_objects
user_synonyms
user_users
all_users
dba_users
dba_rollback_segs
dba_tablespaces
dba_data_files
dba_free_space.
12. Connect as the user SYS and shutdown the database
