Practice 4: Creating a Database
Practice 4: Creating a Database
The directories specified for the files must be placed within $HOME/DATA.
1 Create a password file using the following information:
- Password for sys:oracle
- Enable five privileged users
Ensure that Oracle can write to this file.
2 Write a script for the creation of a database with the following configuration:
– Database name and instance name DB<xx
– One control file named control01.con located in the directory DISK5
– Two redo log file groups each with one 150K member named redo0101.log
and redo0201.log located in the directory DISK6
– The maximum number of five log file groups and five log file members for
each group
– A 20M data file named system01.dbf and located in DISK4 directory
– Maximum of 30 data files that can be created for the database
– A maximum number of 100 archived redo logs for automatic media recovery
– The Character set WE8ISO8859P1
The Trace file location should be in the BDUMP and CDUMP directory.
3 Enable spooling to capture the errors and run the script.
4 (Optional) Include the command “spool <file_name>” before running the
command
5 (Optional) After creation check the database state and ensure that the database files
are created
6 Try to display the names of the database users? What happens and why?
Practice 4: Creating a Database
The directories specified for the files must be placed within $HOME/DATA.
1 Create a password file using the following information:
– Password for sys:oracle
– Enable five privileged users
Ensure that Oracle can write to this file.
Hint: Use the orapwd utility to create the password file and locate the
password file in the $ORACLE_HOME/dbs directory.
2 Write a script for the creation of a database with the following configuration:
– Database name and instance name U<xx
– One control file named control01.con located in the directory DISK5
– Two redo log file groups each with one 150K member named log1a.rdo and
log2a.rdo located in the directory DISK6
– The maximum number of five log file groups and five log file members for
each group
– A 20M data file named system01.dbf and located in DISK4 directory
– Maximum of 30 data files that can be created for the database
– A maximum number of 100 archived redo logs for automatic media recovery
– The Character set WE8ISO8859P1
The Trace file location should be in the BDUMP and CDUMP directory.
Hint
– Edit the parameter file.
– Start the instance.
– Generate the CREATE DATABASE command script.
3 Enable spooling to capture the errors and run the script (optional): Include the
command “spool <file_name>” before running the command.
4 After creation check the database state and ensure that the database files are
created (optional).
Hint: Query the dynamic performance views V$DATABASE,
V$THREAD, V$DATAFILE, V$LOGFILE, and V$CONTROLFILE to
check the database state and display the information about data files,
online redo log files, and control files.
5 Try to display the names of the database users? What happens and why? (optional)
Hint: There is no hint for this question.
Practice 4 Solutions
The directories specified for the files must be placed within $HOME/DATA.
1 Create a password file using the following information:
– Password for sys:oracle
– Enable five privileged users
Ensure that Oracle can write to this file.
Hint: Use the orapwd utility to create the password file and locate the password
file in the $ORACLE_HOME/dbs directory.
2 Write a script for the creation of a database with the following configuration:
– Database name and instance name DB<xx
– One control file named control01.con, located in the directory DISK5
– Two redo log file groups each with one 150 KB member named
redo0101.log and redo0201.log, located in the directory DISK6
– The maximum number of five log file groups and five log file members for
each group.
– A 20 MB data file named system01.dbf, located in DISK4 directory
– A maximum of 30 data files that can be created for the database
– A maximum of 100 archived redo logs for automatic media recovery
– The character set WE8ISO8859P1
The trace file location should be in the BDUMP and CDUMP directory.
Hints:
– Edit the parameter file.
– Start the instance.
– Generate the CREATE DATABASE command script
3 Enable spooling to capture the errors and run the script.
4 (Optional) Include the command “spool <file_name>” before running the
command.
5 (Optional) After creation, check the database state and ensure that the database
files are created.
Hint: Query the dynamic performance views V$DATABASE, V$THREAD,
V$DATAFILE, V$LOGFILE, and V$CONTROLFILE.
6 Try to display the names of the database users. What happens and why?