Oracle9i Database Fundamentals 1Z1-031: Managing Instances and Creating Databases
*Course 2: Managing Instances and Creating DatabasesTo be a successful Oracle DBA and to pass OCP DBA Fundamental I, you must understand the Oracle database architecture. An Oracle database in action consists of several elements, including memory structures, special processes that make things run faster, and recovery mechanisms that enable the DBA to restore systems after seemingly unrecoverable problems.
After installing the Oracle software, the DBA should master the management of an Oracle instance. There are several important things that should be done to manage the Oracle instance before even thinking about setting up the Oracle database. Important questions about authentication must be answered, and the parameter file must be developed and managed. This parameter file is generically referred to as initsid.ora by many DBAs.
Starting up and shutting down the instance, and opening and closing the database are key areas both before and after the database is created. Finally, the management of sessions and places to look for information about the Oracle instance in action are covered in this course.
This course presents information relevant to the following Oracle 1Z1-031: Oracle9i DBA Fundamentals I exam objectives:
Managing an Oracle Instance
- Create and manage initialization parameter files
- Configure OMF
- Start up and shut down an instance
- Monitor the use of diagnostic files
- Describe the prerequisites necessary for database creation
- Create a database using Oracle Database Configuration Assistant
- Create a database manually
- Choose database character set and national character set for a database
- Specify the language-dependent behavior using initialization parameters, environment variables, and the ALTER SESSION command
- Use the different types of National Language Support parameters
- Explain the influence on language-dependent application behavior
- Obtain information about globalization support
Lesson 2. Creating an Initialization Parameter File
The initsid.ora parameter file is the first file consulted when you open an instance of an Oracle database.After completing this lesson, you should be able to:
- State the default location and name of the initialization parameter file
- View the initialization parameters
- Set the initialization parameters
How well or poorly your database performs is determined to a great extent by how you configure your Oracle instance. You configure your instance dynamically when you start it, using a parameter file. This parameter file is commonly referred to by DBAs as the initsid.ora file. The real name of the parameter file for your database is arbitrary and completely up to you.
However, when you install Oracle and create your first database, if you use the Oracle Database Configuration Assistant, Oracle will generate a parameter file for you named after the database. Thus, if your database is named ORGDB01, your default name for the initsid.ora file will be initORGDB01.ora. Throughout the discussions in this course, this file will be referred to as the init.ora or initsid.ora file.
You can create your parameter file from scratch, but why bother when Oracle creates one for you? The parameter file Oracle creates will contain several different options for the most essential initialization parameters you need to include when creating a new database. However, Oracle has hundreds of initialization parameters, documented and undocumented.
The following page shows a sample parameter file in use on a small Oracle database running under UNIX. It was created automatically when the Oracle Database Configuration Assistant created the database and was then later modified manually. The pound sign (#) is used to denote comments in the parameter file.
*Sample Initsid.ora File ######################################################## # Copyright (c) 1991, 2001 by Oracle Corporation ######################################################## ######################################################## # Cache and I/O ######################################################## db_block_size = 8192 db_cache_size = 20971520 ######################################################## # Cursors and Library Cache ######################################################## open_cursors = 300 ######################################################## # Diagnostics and Statistics ######################################################## background_dump_dest=/u01/app/db01/product/9.0.1/admin/oracle/bdump user_dump_dest=/u01/app/oracle/product/9.0.1/admin/db01/udump core_dump_dest=/u01/app/oracle/product/9.0.1/admin/db01/core # timedstatistics = TRUE ######################################################## # Distributed, Replication, and Snapshot ######################################################## db_domain = "" remote_login_passwordfile = EXCLUSIVE ######################################################## # File Configuration ######################################################## control_files = ("/u01/oradata/db01/control01.ctl", "/u02/oradata/db01/control02.ctl", "/u03/oradata/db01/control03.ctl") ######################################################## # MTS ######################################################## dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP) (PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP) (PRE=oracle.aurora.server.SgiopServer)" ######################################################## # Miscellaneous ######################################################## compatible = 9.0.0 db_name = oracle ######################################################## # Network Registration ######################################################## instance_name = ORCL ######################################################## # Pools ######################################################## java_pool_size = 10485760 large_pool_size = 1048576 shared_pool_size = 31457280 ######################################################## # Processes and Sessions ######################################################## processes = 15 ######################################################## # Redo Log and Recovery ######################################################## fast_start_mttr_target = 300 ######################################################## # Resource Manager ######################################################## resource_manager_plan = SYSTEMPLAN ######################################################## # Sort, Hash Joins, Bitmap Indexes ######################################################## sort_area_size = 524288 ######################################################## # System Managed Undo and Rollback Segments ######################################################## undo_management = AUTO undo_tablespace=UNDOTBS ######################################################## # Other Database Parameters ######################################################## utl_file_dir = /u01/oradata/db01/misc |
In general, when working with parameter files, it is best not to start from scratch. Use the Oracle default or borrow one from another DBA. However, be sure that you alter the parameters in the file to reflect your own database, including DB_NAME, DB_BLOCK_SIZE, CONTROL_FILES, and others.
You should remember where you store your parameter files on the machine hosting the Oracle database. Do not leave multiple copies in different directories; this could lead you to make a change to one but not the other, and then start your instance with the old parameter file later, and have problems. However, it can be useful to have a few different copies of a parameter file for various administrative purposes, such as one for production environments and one for running the database in restricted mode for DBA maintenance operations.
On a UNIX machine, by default, a pointer to the init.ora file in your OFA-compliant admin/SID/pfile administrative directory (where SID is replaced with the name of your Oracle database) is located in the $ORACLE_HOME/dbs directory.
In Windows, the actual init.ora file is located in the %ORACLE_HOME%\database directory. If the init.ora is not in the default location, it has to be explicitly specified while bringing up the database. If you don't, Oracle will complain.
# On UNIX:
# $ORACLE_HOME/SID points to admin/SID/pfile/initsid.ora
# On WINDOWS:
# %ORACLE_HOME%\admin\SID\initsid.ora
*Viewing Parameter Values
Once your instance is started, several different methods exist for obtaining the values set for the instance on initialization. The first and least effective way to view parameter values in your database is to look at the initsid.ora file. This choice does not give you all parameters, and what's more, some of the parameters in your parameter file may have changed since the last time you started Oracle.
| ######################################################## # Copyright (c) 1991, 2001 by Oracle Corporation ######################################################## ######################################################## # Cache and I/O ######################################################## db_block_size = 8192 db_cache_size = 20971520 ######################################################## # Cursors and Library Cache ######################################################## open_cursors = 300 | |
A much better way to obtain parameter values is to select them from a special view in Oracle called V$PARAMETER.
V$PARAMETER
Still another effective way for obtaining parameter values in Oracle is to use SQL*Plus. The show parameter command will list all parameters for the instance. Finally, you can use Oracle Enterprise Manager to display instance parameters. Both Enterprise Manager and SQL*Plus draw their initialization parameter information from V$PARAMETER.
V$PARAMETER
You may have noticed the reference to Enterprise Manager but not Instance Manager. This is because the following tools have been rolled into the Enterprise Manager starting with Oracle9i:
- Instance Manager
- Schema Manager
- Security Manager
- Data Manager
- Software Manager
Some other important V$ ("vee dollar") views available in the Oracle9i database to be aware of include:
| V$FIXEDTABLE | V$SGA |
| V$PARAMETER | V$OPTION |
| V$PROCESS | V$SESSION |
| V$VERSION | V$INSTANCE |
| V$THREAD | V$CONTROLFILE |
| V$DATABASE | V$DATAFILE |
| V$DATAFILEHEADER | V$LOGFILE |
*Setting Parameter Values
Setting parameters is done in one of two ways. By far, the most effective way to set a database parameter is to add the name of the parameter and the value to the initsid.ora file for your instance. After that, shut down and start up your instance using the initsid.ora file. Unfortunately, in the world of multiuser database environments, DBAs do not always have the luxury of bouncing the database whenever they want. You can always try to schedule this sort of thing, or if the need is not critical, wait until the weekend.
Another method for setting parameters is by using the alter commands shown here.
This used to not be an effective method for changing parameters, because not all initialization parameters could be changed using this command, but this has changed with the introduction of server parameter files. Now you can use alter system with SPFILE mode to permanently alter parameters without bringing down the database.
alter system
alter session
alter system deferred
To determine whether a parameter can be changed using alter commands, query the V$PARAMETER or V$SYSTEMPARAMETER views to list information about the modified parameter. Pay particular attention to the Boolean values in the following columns.
| IS_MODIFIED | |
| IS_DEFAULT | |
| IS_ADJUSTED | |
| ISSYS_MODIFIABLE | |
| ISSES_MODIFIABLE |
They can be used to identify whether the default value for that parameter is modified or adjusted and whether the value is modifiable with the alter system or alter session commands, respectively.
Question 1
Question 2
Question 3
Question 4
Topic 2.1 Exercises
* Exercise 1
Try viewing parameter information for a database in Oracle9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i for these practice exercises. Find and open the init.ora file for your database. (When you install Oracle you will be given the opportunity to name and create a database. Oracle automatically installs a set of sample schemas into this database.) |
| 2 | Use the V$PARAMETER view to look at the full set of parameters for the current instance. Are there values that were not explicitly set in the init.ora file? |
| 3 | Determine which values are at their default settings. Note which values are system modifiable and which are session modifiable. |
| 3 | Use the show parameter command and compare the results. |
In this lesson, you learned about the initsid.ora file which supplies initialization parameters for your database. You learned how to use the show parameter and SELECT * FROM V$PARAMETER commands to view database parameters and their attributes. You also learned that you can modify some parameters using alter system commands.
Lesson 3. Configuring Oracle-Managed Files
Oracle-Managed Files (OMF) is a new feature introduced in Oracle9i to simplify database administration with respect to database files.After completing this lesson, you should be able to:
- State the purpose and function of Oracle-Managed Files
- Use the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_N parameters
In the past, Oracle DBAs have always faced a conundrum when working with database administrative tasks. Not only does database administration require a thorough knowledge of how Oracle works, but it also requires that DBAs have a strong understanding of how the underlying operating system works as well.
Why would this be the case? Because often DBAs have to perform administrative cleanup tasks following important database management activities. Nowhere is this more apparent than in the creation and removal of datafiles related to tablespace management.
*The Purpose of OMF
You'll learn more about creating tablespaces later, but for now understand that tablespaces are logical areas where Oracle stores the contents of database objects like tables and indexes. Underlying the logical concept of tablespaces is the physical datafile or datafiles where the bits and bytes are actually stored. As the last statement implies, a tablespace can be comprised of one or more datafiles in Oracle.
Oracle-Managed Files (OMF) is a new feature introduced in Oracle9i to simplify database administration with respect to database files. In previous versions, when you created new tablespaces using the appropriate command, Oracle expected you to specify the filesystem location and filename for all datafiles to be used in conjunction with storing information in this tablespace.
In Oracle9i with OMF enabled, you specify the default location of all datafiles by using the DB_CREATE_FILE_DEST parameter in the init.ora file (or by using an alter system command at the command line). You can then create tablespaces by specifying only the tablespace name and, if desired, the size and other attributes. Issuing create tablespace myTablespace creates a 100 MB file with AUTOEXTEND set to TRUE and no file size limit.
You can set a similar parameter for the location of redo logs and control files. Once it has created the datafiles (or redo logs or control files), OMF takes over management of the files. DBAs thus relieve themselves of the obligation to delete datafiles later when the tablespace is dropped. Instead, Oracle9i will remove them for you.
*Implementing OMF
The first step is to specify directory locations using two parameters in your init.ora file. The first is DB_CREATE_FILE_DEST, used to identify the filesystem location Oracle will use for automatically creating datafiles. This directory will be used for any command that does not specify a filesystem directory.
The directory must exist before you assign it as a value for this parameter. If the directory does not exist, Oracle will not create it for you. An example of how to set DB_CREATE_FILE_DEST is shown here:
DB_CREATE_FILE_DEST = /u01/oradata/oracle
The second parameter to set is DB_CREATE_ONLINE_LOG_DEST_N, where N is set to some numeric value. This parameter is used for setting default online redo log directory locations for all your online redo logs. For greater fault tolerance and improved performance, Oracle recommends that you set at least two locations. An example is shown here:
DB_CREATE_ONLINE_LOG_1 = /u02/oradata/oracle
DB_CREATE_ONLINE_LOG_2 = /u03/oradata/oracle
The directories specified for both the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_N parameters must be set so that the user on your host system who owns all Oracle software has read and write permissions for these directories.
Once the appropriate parameters are set, you can omit file locations in commands that would ordinarily require you to specify a filesystem location, such as create tablespace. We will see some examples of how to use OMF in action when we cover the creation and removal of tablespaces and datafiles later.
Tip: TIP: When online redo log locations are specified using the appropriate parameter, Oracle automatically handles the setup of online log multiplexing for you.
Question 5
Question 6
Question 7
In this lesson, you learned about Oracle-Managed Files (OMF). OMF uses two init.ora parameters to manage database files: DB_CREATE_FILE_DEST, which points to a default datafile location, and DB_CREATE_ONLINE_LOG_N, which points to default redo log locations. OMF also handles automatic datafile removal when tablespaces are deleted.
Lesson 4. Starting an Instance Using SQL*Plus
You must start an Oracle instance before creating a new database or opening an existing database.After completing this lesson, you should be able to:
- Start an Oracle instance
- Describe where Oracle looks for init.ora if it cannot find the file
Recall that we made an important distinction earlier between an Oracle instance and an Oracle database. To refresh your memory, the Oracle database is a set of tables, indexes, procedures, and other objects used for storing data. More precisely, an Oracle database, identified by the database name (DB_NAME), represents the physical structures and is composed of operating system files.
Although it is possible to use a database name that is different from the name of the instance, you should use the same name for ease of administration. The Oracle instance, on the other hand, is the memory structures, background processes, and disk resources, all working together to fulfill user data requests and changes.
With that distinction in mind, let's consider starting the Oracle instance. You must do this before creating a new database, or before allowing access to an existing database. To start the instance, follow these steps:
From the command line on the host machine, start SQL*Plus and log in as sysdba:
SQL*Plus: Release 9.0.1.0.1 - Production on Mon Feb 18 14:53:22 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL>
If you are prompted by the GUI upon opening SQL*Plus, enter the username SYS, but do not enter the password into the GUI. Then click OK. Oracle will prompt you for the password.
The password for user SYS when logging in as sysdba is usually set to change_on_install by default. Obviously, you'll want to change the password from its default in order to avoid security problems from knowledgeable DBAs.
From within SQL*Plus, use the following command to start the database instance:
startup startoption [dbname]
Several options exist for startoption, including nomount, mount, open, and force. You can omit the database name if it is the only one on the computer. open is the default option so often you will be able just type startup to open your database.
As previously mentioned, if you are not using OMF Oracle expects to find the parameter file in the following locations:
UNIX: $ORACLE_HOME/dbs
WINDOWS: $ORACLE_HOME\database
If the file is not located there, use the PFILE parameter to identify the exact initsid.ora file you want to use. The example below starts a database which is the only one on the computer. Since the startup option is omitted Oracle will use the default option and open the database.
startup pfile=X:\path\filename
An example of startup nomount is shown here:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 227174560 bytes
Fixed Size 42764 bytes
Variable Size 93999104 bytes
Database Buffers 81920000 bytes
Redo Buffers 51208192 bytes
SQL>
Question 8
Question 9
In this lesson, you were reminded about the difference between a database (which consists of the data and control files) and an instance (which is a specific occurence of the background processes running on a computer). You also learned how to start an Oracle instance, and where Oracle looks for the init.ora file on UNIX and on Windows.
Lesson 5. Startup Methods and Options
There are several methods of opening a database, and several options when starting an instance.After completing this lesson, you should be able to:
- List the methods of opening a database
- List the four options available for starting an instance
- Describe what it means for a database to be in a read-only state or in a quiescing state
*Other Methods of Starting an Instance
In addition to using SQL*Plus, you can also start a database instance using two other methods.
You can use Oracle Enterprise Manager (OEM). All the options discussed for SQL*Plus are available via the OEM GUI.
The startup may be handled as a service. A service in Windows is similar to a daemon in UNIX. Both of these operating system functions let Oracle start automatically when the machine boots. For more information, consult the Oracle9i Installation Guide for Windows NT, which comes with that distribution of Oracle.
Regardless of the startup method, let's look at the instance startup options, with or without opening the database. The options are nomount, mount, open, andforce.
You can specify any of these options with or without specifying an initsid.ora file for the PFILE parameter. Remember that if Oracle doesn't find an initialization file, and you don't supply an explicit value for PFILE, Oracle will not start your database.
startup nomount
The startup nomount command starts the instance without mounting the database. That means all the memory structures and background processes are in place, but no database is attached to the instance. You will use this option later for creating the Oracle database.
Starting an instance without mounting the database includes the following tasks:
- Reading the parameter file init.ora
- Allocating the SGA
- Starting the background process
- Opening the ALERT file and the trace files
Note: Note that neither control files nor the database data files are opened in this mode.
startup mount
The startup mount command starts the instance, reads the control files, and attaches the database, but it does not open it. You can't mount a database you haven't created yet. This option is useful in situations where you have to move physical database files around on the machine hosting Oracle, or when database recovery is required.
Mounting the database includes the following tasks:
- Associating a database with a previously started instance
- Locating and opening the control files specified in the parameter file
- Reading the control files to obtain the names and status of the datafiles and redo log files
Note: If the instance is already started but the database is not mounted, use alter database mount instead.
startup open
The startup open command starts your instance, attaches the database, and opens it. This is the default option for starting Oracle. It is used when you want to make your database available to users. If you omit the open keyword when issuing the startup command, startup open is assumed. Of course, you can't open a database you haven't created yet.
Opening the database includes the following tasks:
- Opening the online datafiles
- Opening the online redo log files
Tip: If the instance is started and the database is mounted, use alter database open instead.
Here is an example of a startup open command:
SQL> startup open
ORACLE instance started.
Total System Global Area 148808972 bytes
Fixed Size 70924 bytes
Variable Size 40566784 bytes
Database Buffers 108093440 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
startup force
The startup force command forces the instance to start and the database to open. It is used in situations where other startup options are met with errors from Oracle, and no shutdown options seem to work either. This is an option of last resort, and there is no reason to use it generally unless you cannot start the database with any other option.
startup recover and startup restrict
Two other cases for instance startup include startup recover for handling database recovery and startup restrict for opening the database while simultaneously preventing all users but the DBA from accessing database objects.
The Oracle Enterprise Manager Console enables the DBA to change and view the initialization parameters. They can be stored either in a local parameter file or in the Oracle Enterprise Manager repository by using stored configurations.
If using stored configurations, the DBA must be connected by the way of an Oracle Management Server (OMS) to get access to a repository. In earlier versions of Oracle Enterprise Manager (1.x), the initialization parameters were stored locally in the Windows NT registry.
*Quiescing
Oracle9i has introduced at least two other modes of database operations. The database can be brought into a quiescing state where only DBA transactions, queries, or PL/SQL statements are allowed to execute. This state is best suited for DBAs for their maintenance work. This is achieved by the following statement:
alter system quiesce restricted
*Suspending and Resuming
Oracle9i also can suspend and resume the databases. Suspending a database will halt all input and output to datafiles and control files, but all preexisting I/O operations are allowed to complete. New database accesses are placed in a queued state.
Tip: TIP: If you suspend database operation from a SQL*Plus prompt while logged in as sysdba, the only statement you can issue following alter system suspend is alter system resume. Issuing any other statement will cause your session to hang.
# To suspend the database
alter system suspend
# To resume operations
alter system resume
*Starting Automatically
Most DBAs want their instance to start automatically whenever the host machine is rebooted. In Windows environments, the database can be opened by starting the OracleServiceSID service. This service is created for the database instance SID when you install Oracle on Windows environments.
To start the instance automatically, you will have to make sure that the parameter ORASIDAUTOSTART is set to TRUE in the Windows registry. For more information, refer to the Oracle software installation guide specific for the Windows operating system.
On UNIX, automating instance startup and shutdown can be controlled by the entries in the oratab file in the /var/opt/oracle directory. For more information, refer to the Oracle software installation guide for a UNIX operating system, such as Solaris.
*Opening As Read-Only
Any database can be opened as read-only, as long as it is not already open in read/write mode. The feature is especially useful for a standby database to offload query processing from the production database. If a query needs to use a temporary tablespace — for example, to do disk sorts — the current user must have a locally managed tablespace assigned as the default temporary tablespace; otherwise, the query will fail. For the user SYS, a locally managed tablespace is required.
Question 10
Question 11
Question 12
Question 13
In this lesson, you learned that an Oracle database instance can be started several ways — using a Windows service, the UNIX oratab file, the Oracle Enterprise Manager GUI, or the SQL*Plus command line. You also learned the function of the startup command options nomount, mount, open, and force.
Lesson 6. Shutting Down an Instance
Knowing how to shut down a database instance is equally as important as knowing how to start it.After completing this lesson, you should be able to:
- Log on and shut down a database instance
- Describe the four shutdown options
Shutdown of the Oracle instance works in much the same way as starting the instance. Shutting down the instance is the same thing as shutting down the database. You must be logged onto Oracle as a user with sysdba privileges. The task can be accomplished from SQL*Plus or Oracle Enterprise Manager, or as a Windows service. The steps for shutting down an Oracle database instance from SQL*Plus are as follows.
From the command line on the host machine, start SQL*Plus, and log in as sysdba:
SQL*Plus: Release 9.0.1.0.1 - Production on Mon Feb 18 14:53:22 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL>
From within SQL*Plus, use following command to shut down the instance:
shutdown shutdownoption
Several options exist for shutdownoption, including normal, abort, immediate, and transactional. The next four subtopics will explain each of these options and give cases where their use might be appropriate.
shutdown normal
shutdown normal is the lowest-priority shutdown. When it is issued, Oracle will wait for users to log out before actually shutting down the instance and closing the database. There are three rules Oracle follows during shutdown normal:
- First, Oracle will not let new users access the database.
- Second, Oracle will not force users already logged onto the system to log off in order to complete the shutdown.
- Third, this is the most graceful shutdown of all. Therefore, when a database is shut down this way, Oracle will not need to do an instance recovery when the instance is brought up again.
SQL> shutdown normal
Database closed.
Database dismounted.
ORACLE instance shut down.
shutdown immediate
shutdown immediate is a higher-priority shutdown that the DBA can use when shutdown normal would take too long. The immediate option is the default option and shuts down a database as follows:
- No new users will be able to connect to the database once the shutdown immediate command is issued.
- Oracle will not wait for a user to log off as it does for shutdown normal; instead, it terminates user connections immediately and rolls back uncommitted transactions.
- Immediate database shutdown, although more drastic than normal shutdown, does not require any instance recovery.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
shutdown abort
shutdown abort is the highest-priority database shutdown command. In all cases where this priority is used, the database will shut down immediately.
- All users are immediately disconnected.
- No transactions are rolled back.
- Media recovery will be required when the database starts up again.
SQL> shutdown abort
ORACLE instance shut down.
shutdown transactional
A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions:
- No client can start a new transaction on this particular instance.
- A client is disconnected when the client ends the transaction that is in progress.
- An immediate shutdown occurs when all transactions have finished.
- The next startup will not require an instance recovery.
SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
Question 14
Question 15
Question 16
Question 17
In this lesson, you learned how to shut down a database and the options for database shutdown. shutdown normal is the preferred but not the default method. shutdown transactional is the next best option because it waits for users to complete transactions before disconnecting them. shutdown immediate does not wait for transactions to complete, but it does not require instance recovery. shutdown abort does require instance recovery and should only be performed in emergency situations.
Lesson 7. Using Diagnostic Files
Log files and trace files allow you to view what is going on in the database. Log files record at the transactional level, and trace files record background events and processes.After completing this lesson, you should be able to:
- Describe the purpose of trace files, how they're generated, and the parameter that specifies their default location
- Describe the purpose of log files, their recommended use by a DBA, and the parameter that specifies their location
- List the events recorded by the Alert log file
There are many situations where you as the DBA might want to find out more information about what Oracle is doing behind the scenes in order to address issues related to its operation. Two ways exist for doing so — you can either guess or use diagnostic files. For the record, Oracle recommends that you use diagnostic files. There are two types of diagnostic files in a typical Oracle configuration that Oracle will utilize while your database is up and running. These two types of files are log files and trace files. Let's look at each in more detail.
*Trace Files
Background processes generate trace files whenever something goes wrong with the background process operation. Network processes generate trace files as well whenever they malfunction. Additionally, network processes enable you to specify that you want trace files generated upon request for troubleshooting purposes.
Any trace file generated by Oracle will have the filename extension .trc. Background process trace files are stored in the directory specified by the parameter BACKGROUND_DUMP_DEST in the parameter file. Here is an example of BACKGROUND_DUMP_DEST with a directory value set:
background_dump_dest=/u01/app/oracle/
product/9.0.1/admin/db01/bdump
The name of a background process trace file typically contains the name of the background process concatenated with the value specified by INSTANCE_NAME in the init.ora file. For example the file shown here was generated by the LGWR process on the Delphi instance.
Background trace files contain detailed information about what caused the background process to malfunction. It is a good procedure to check these trace files when the database behaves or crashes abnormally.
Trace files mostly contain information that only Oracle support can decipher. Many times you will be requested by Oracle support to provide these trace files for them to pinpoint the source of the problems.
Dump file C:\oracle\admin\Delphi\bdump\delphiLGWR.TRC
Tue Feb 12 09:47:35 2002
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 1, CPU type 586
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Windows 2000 Version 5.0 Service Pack 1, CPU type 586
Instance name: delphi
Redo thread mounted by this instance: 1
Oracle process number: 4
Windows thread id: 924, image: ORACLE.EXE
*** SESSION ID:(3.1) 2002-02-12 09:47:35.000
*** 2002-02-12 09:47:35.000
Assigning activation ID 1359595603 (0x5109c853)
Other processes such as the listener process may also generate trace files from time to time. These trace files are stored in locations specified in their corresponding configuration files. For example, the listener process configuration file is listener.ora.
The detail of the trace information in these trace files can be regulated. They are extremely useful in troubleshooting Oracle networking problems. However, make sure that you turn them off when not needed as they could grow very fast and fill the disk.
Server processes managing data on behalf of Oracle users may also generate trace files if you request them to. These trace files can be found in the directory specified by USER_DUMP_DEST. Oracle typically names user session trace files (also called user process files) after the process ID number generated in the background for the server process acting on behalf of the user. You usually have to dig through user session trace files or else look at the date and time the file was created in order to figure out which is which. Where background process trace files are stored in bdump, user session trace files are usually stored in the udump directory.
*Log Files
The second type of diagnostic files that Oracle generates are the log files. The most important log file that you should be aware of and monitor often is the Alert log file. The Alert log file is created automatically by Oracle as soon as you create an instance. Like the trace files, it is also stored in the location specified by the parameter BACKGROUND_DUMP_DEST in the init.ora file. The name of this file is alertSID.log or SIDALRT.log, where SID is the name specified by the parameter INSTANCE_NAME in init.ora.
The Alert log file stores information that is extremely useful in order to know the health of the database. It records the following items:
- Starting and stopping of the databases
- Creation of new redo log files (which happens every time a log switch occurs)
- Creation of tablespaces
- Addition of new datafiles to the tablespaces
- (Mostly importantly) errors that are generated by Oracle
The Alert log is the most often requested file by Oracle support. It tends to grow very large with passage of time. Parts of the file need to removed or archived on a regular basis.
Dump file C:\oracle\admin\Delphi\bdump\delphiALRT.LOG
Tue Feb 12 09:42:14 2002
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 1, CPU type 586
Starting up ORACLE RDBMS Version: 9.0.1.1.1.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 46137344
large_pool_size = 1048576
java_pool_size = 33554432
control_files = C:\oracle\oradata\Delphi\CONTROL01.CTL, C:\oracle\oradata\Delphi\CONTROL02.CTL, C:\oracle\oradata\Delphi\CONTROL03.CTL
db_block_size = 4096
db_cache_size = 33554432
compatible = 9.0.0
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS
remote_login_passwordfile= EXCLUSIVE
db_domain = MyCompany
instance_name = Delphi
dispatchers = (PROTOCOL=TCP)(SER=MODOSE), (PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer), (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
background_dump_dest = C:\oracle\admin\Delphi\bdump
user_dump_dest = C:\oracle\admin\Delphi\udump
core_dump_dest = C:\oracle\admin\Delphi\cdump
sort_area_size = 524288
db_name = Delphi
open_cursors = 300
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Tue Feb 12 09:42:20 2002
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Feb 12 09:44:27 2002
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Feb 12 09:45:02 2002
Create controlfile reuse set database Delphi
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
Datafile
'C:\oracle\oradata\Delphi\CWMLITE01.DBF' ,
'C:\oracle\oradata\Delphi\DRSYS01.DBF' ,
'C:\oracle\oradata\Delphi\EXAMPLE01.DBF' ,
'C:\oracle\oradata\Delphi\INDX01.DBF' ,
'C:\oracle\oradata\Delphi\SYSTEM01.DBF' ,
'C:\oracle\oradata\Delphi\TOOLS01.DBF' ,
'C:\oracle\oradata\Delphi\UNDOTBS01.DBF' ,
'C:\oracle\oradata\Delphi\USERS01.DBF'
LOGFILE GROUP 1 ('C:\oracle\oradata\Delphi\REDO01.LOG') SIZE 102400K,
GROUP 2 ('C:\oracle\oradata\Delphi\REDO02.LOG') SIZE 102400K,
GROUP 3 ('C:\oracle\oradata\Delphi\REDO03.LOG') SIZE 102400K RESETLOGS
Tue Feb 12 09:45:13 2002
Successful mount of redo thread 1, with mount id 1359624159.
Tue Feb 12 09:45:14 2002
Completed: Create controlfile reuse set database Delphi
MAXIN
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water mark = 1
Tue Feb 12 09:45:27 2002
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Tue Feb 12 09:45:27 2002
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
archiving is disabled
Archive process shutdown avoided: 0 active
Tue Feb 12 09:45:37 2002
Starting ORACLE instance (normal)
Tue Feb 12 09:46:06 2002
Starting up ORACLE RDBMS Version: 9.0.1.1.1.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 46137344
large_pool_size = 1048576
java_pool_size = 33554432
control_files = C:\oracle\oradata\Delphi\CONTROL01.CTL, C:\oracle\oradata\Delphi\CONTROL02.CTL, C:\oracle\oradata\Delphi\CONTROL03.CTL
db_block_size = 4096
db_cache_size = 33554432
compatible = 9.0.0
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS
remote_login_passwordfile= EXCLUSIVE
db_domain = MyCompany
instance_name = Delphi
dispatchers = (PROTOCOL=TCP)(SER=MODOSE), (PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer), (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
background_dump_dest = C:\oracle\admin\Delphi\bdump
user_dump_dest = C:\oracle\admin\Delphi\udump
core_dump_dest = C:\oracle\admin\Delphi\cdump
sort_area_size = 524288
db_name = Delphi
open_cursors = 300
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Tue Feb 12 09:46:28 2002
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Feb 12 09:46:58 2002
Create controlfile reuse set database Delphi
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
Datafile
'C:\oracle\oradata\Delphi\CWMLITE01.DBF' ,
'C:\oracle\oradata\Delphi\DRSYS01.DBF' ,
'C:\oracle\oradata\Delphi\EXAMPLE01.DBF' ,
'C:\oracle\oradata\Delphi\INDX01.DBF' ,
'C:\oracle\oradata\Delphi\SYSTEM01.DBF' ,
'C:\oracle\oradata\Delphi\TOOLS01.DBF' ,
'C:\oracle\oradata\Delphi\UNDOTBS01.DBF' ,
'C:\oracle\oradata\Delphi\USERS01.DBF'
LOGFILE GROUP 1 ('C:\oracle\oradata\Delphi\REDO01.LOG') SIZE 102400K,
GROUP 2 ('C:\oracle\oradata\Delphi\REDO02.LOG') SIZE 102400K,
GROUP 3 ('C:\oracle\oradata\Delphi\REDO03.LOG') SIZE 102400K RESETLOGS
One of the best practices as a DBA is to scan the Alert log file periodically for any error reports (they usually start with ORA-). A much better approach is to have an automated script that scans this file at regular intervals and pages the DBA if it comes across any ORA- occurrences in the file.
Other processes, such as Oracle network processes, also generate log files. These log files give enough information to gauge the health of Oracle and its network processes. The main difference between log files and trace files is that the log files are needed all the time by DBAs, as only important information is reported in them. The trace files are needed for troubleshooting. Oracle reports much more information in trace files than via log files.
Tip: If you start getting really weird errors in your database and your ALERT log contains ORA-00600 errors, you should call Oracle Support and start looking in the USER_DUMP_DEST and BACKGROUND_DUMP_DEST directories ASAP!
Question 18
Question 19
Question 20
Question 21
In this lesson, you learned how to use Oracle diagnostic files: trace (.trc) files and log (.log) files. You learned that log files record transactional information, while trace files record more obscure background information.
You also learned that the Alert log file should be monitored for errors, and that if it contains ORA-00600 errors you should immediately look at the USER_DUMP_DEST and USER_BACKGROUND folders (usually udump and bdump respectively).
Lesson 8. Planning for Database Creation
There are several hardware and operating-system level tasks that must be performed before creating a database.After completing this lesson, you should be able to:
- List the hardware and operating system tasks that must be completed before installing Oracle and creating a database
The act of creating a database is nothing but the creation of a physical database. Before a physical database is created, the database designers design the logical database using an Entity-Relationship diagram (ERD). The ERD shows the relationship between the tables, columns they contain, the constraints, and other information.
A DBA translates this logical design into a physical design by mapping where the tables need to be created, what tablespaces they should reside in, how large they have to be, and so on.
Just as in installing the Oracle software, there are many tasks a DBA has to do before and after the creation of the database. The focus of the Oracle9i DBA Fundamentals I exam is on the tasks required to create the physical database. In this lesson, we'll cover how to perform preinstallation tasks.
*Prerequisites for Creating a Database
There are a few things you should do at the operating system level before creating your database. Because every operating system is different, you'll be introduced to the general concepts here for the purpose of preparing for the exam. If you have further questions, refer to the operating system-specific Oracle installation guide that came with your software distribution.
Some of these steps are things you should be aware of at the time you install the Oracle software on your host machine, whereas others can wait until the time you are ready to issue the create database statement.
- In general, you should make sure your machine has the capacity to handle Oracle.
- At a minimum, make sure you have at least three separately controlled disk resources.
- Configuring certain environment settings is an important task executed at this point as well.
- If you are installing Oracle on a machine that currently hosts another Oracle database, make sure you shut down and back up the other Oracle databases running on the host.
- If it's appropriate, make sure that operating system patches recommended by Oracle are installed on the machine.
- If you are working on UNIX, perform UNIX-specific tasks
- Implement the Optimal Flexible Architecture (OFA) standard on your installation.
Make sure your machine has the capacity to handle Oracle. Almost any machine made these days has the capacity to install Oracle successfully. However, not every machine has the guts to run a full-scale Oracle enterprise database application. Before creating an Oracle environment, be sure to assess whether your host machine has the CPU power, memory, and disk space it takes to run an Oracle database in a multiuser environment.
Ensure that you have at least three separately controlled disk resources. A running Oracle database has many moving parts. Often, these parts are also moving at the same time. Putting every Oracle resource on the same hard drive is a recipe for slow performance on all but the smallest single-user database setups. Oracle recommends three separately controlled disk resources. An enterprise production installation of Oracle can require 20 or more. Again, think before you create.
Configure certain environment settings. You may need to configure a few environment variables before creating your database, such as ORACLE_BASE, ORACLE_HOME, ORACLE_SID, ORANLS33, LD_LIBRARY_PATH, and others. These are items that you will set up in your machine configuration files or user configuration files. Where possible, you should try to follow the OFA. This is Oracle's recommended guideline for file-system directory paths, and following it will help Oracle Support find files for you when you call in the inevitable emergency production-support issue.
Shut down and back up other Oracle databases running on the host. Unless you like long hours spent in a computer room handling recovery, don't care about your data, or both, you should never install an Oracle database on a machine already hosting Oracle without shutting down and backing up that other database first. The reuse keyword in the create database command as well as the CONTROL_FILES parameter in your initsid.ora file make it possible for one Oracle database to overwrite the files of another database on the same machine.
Avoid problems by taking the extra time to back up your data, and put different Oracle database files in different directories.
Install Oracle-recommended operating system patches on the machine. This point is as much an Oracle software installation issue as it is a database creation issue. Because the exact operating system version and required patches vary from operating system to operating system, you should consult the Oracle installation guide that came with your software for specifics, while being mindful that operating system patches may need to be applied for Oracle to work properly.
Perform UNIX specific tasks. Specifically for UNIX, after installation edit the oratab file to include the name of the database being created, its ORACLE_HOME, and whether it has to automatically shut down in event of system start or shutdown. This task is not mandatory for the working of Oracle but essential for many of the administration tasks.
Implement the Optimal Flexible Architecture (OFA) standard.* Finally, implementing OFA may be the most important thing you have to do in order keep your administration simple and prevent accidents from happening. We have already discussed the details of OFA previously. Make sure to create the administration directory under ORACLE_BASE (you configure a directory for ORACLE_BASE such as the Oracle software owner's home directory as part of configuring environment settings) to store the database administration and diagnostic files. If the datafiles of the database being created are stored in their own directories as recommended in OFA, then make sure to create the directories. In UNIX, these datafiles will not be created unless the directories exist.
Question 22
Question 23
Topic 8.1 Exercises
* Exercise 1
Try planning your OFA setup for Oracle9i.
| Step | Action |
|---|---|
| 1 | Study the Oracle documentation on OFA, available on the Web at docs.oracle.com/database_mp.html. |
| 2 | Determine the best OFA setup for your system. This should be based on your OS environment, the number of installations (or "Oracle homes") you have, the version numbers of these installations, the number of disk resources you have, and your plans for expansion. |
| 3 | Diagram your OFA setup. |
In this lesson, you learned about the preinstallation tasks you must perform before installing Oracle and creating a new database, including making sure your system can handle Oracle, has the latest patches installed, and has the correct environment settings.
Lesson 9. Preparing the Parameter File
At least nine initialization parameters must be set in order to create a new database. However, once the database has been created, a new feature called the Server Parameter file simplifies administration of the initialization parameters.After completing this lesson, you should be able to:
- List and describe the nine parameters required in order to create a new database
- Describe the function of the new Server Parameter file feature
- Implement Server Parameter files
You've already learned about the parameter file, so now let's focus on the values that must be set in order to create a new Oracle database. As mentioned, Oracle provides a generic copy of that parameter file, initsid.ora, in the software distribution used to install Oracle server on the machine hosting Oracle.
Generally, the DBA will take this generic parameter file and alter certain parameters according to his or her needs. Several parameters must be changed as part of setting up a new database. The following subtopics identify and describe the parameters you need to change.
*DB_NAME
DB_NAME is the local name of the database on the machine hosting Oracle and one component of a database's unique name within the network. If the value for this parameter is the same as another Oracle database running on the host, permanent damage may result in the event that a database is created. Try to limit this name to approximately eight characters. Do not leave the name as DEFAULT. There is a parameter for the database name and a parameter for the instance (INSTANCE_NAME), and they should be the same. DB_NAME is required for the creation of the database, and it should be unique among all Oracle databases running in your organization.
*DB_DOMAIN
DB_DOMAIN identifies the domain location of the database name within a network. It is the second component of a database's unique name within the network. This is usually set either to WORLD or to the domain name of your organization.
*DB_BLOCK_SIZE
DB_BLOCK_SIZE is the size in bytes of data blocks within the system. This is also called standard block size in Oracle9i. Data blocks are unit components of datafiles into which Oracle places the row data from indexes and tables. This is one parameter that cannot be changed once the database is created. Oracle9i supports multiple block sizes. The standard block size is defined by the parameter DB_BLOCK_SIZE and it supports additional four nonstandard block sizes. The standard block size is used for system tablespace, whereas nonstandard block size may be specified when creating other tablespaces.
*CONTROL_FILES
CONTROL_FILES is a name or list of names for the control files of the database. The control files document the location of all disk files used by Oracle. If the name(s) specified for this parameter does not match filenames that exist currently, then Oracle will create a new control file for the database at startup only when you create a new database. Otherwise, Oracle simply tells you it won't start because it can't find the control files it needs to open your existing database. Only during the creation of a new database will Oracle overwrite the contents of a file of the same name as the control file you specified in initsid.ora with the physical layout of the database being created. Beware of this feature, as it can cause a control file on an existing database to be overwritten if you are creating a second database to run on the same machine. Use of OFA helps avoid this problem.
*DB_CACHE_SIZE
DB_BLOCK_BUFFERS continues to exist in Oracle9i for backward compatibility, however, Oracle9i introduces a new parameter, DB_CACHE_SIZE. Both parameters specify the size of the default buffer pool that can be held in the SGA. Unlike DB_BLOCK_BUFFERS, which is specified in number of standard block-sized buffers, this new parameter is defined in bytes.
*LOG_BUFFER
LOG_BUFFER specifies the size of the redo log buffer in bytes. As stated earlier, the redo log buffer stores redo log entries in memory until LGWR can write the entries to online redo logs on disk. There will be more about this elsewhere.
*UNDO_MANAGEMENT
UNDO_MANAGEMENT gets set to AUTO to indicate that Oracle9i will handle undo segment management automatically for you. An undo segment is the same thing as a rollback segment. As of Oracle9i, you no longer have to create rollback segments manually.
*UNDO_TABLESPACE
This parameter is set to the name of the tablespace you want to use to house undo segments generated and managed automatically by Oracle. Make sure you specify and use a tablespace that houses nothing other than undo segments for this purpose.
*PROCESSES
This is the number of processes that can connect to Oracle at any given time. This value includes background processes (of which there are at least five) and server processes. This value should be set high in order to avoid errors that prevent users from connecting.
*Server Parameter Files
The Server Parameter file is a new feature in Oracle9i. It enables you to relieve yourself of the burden of constantly updating your init.ora file yourself whenever you decide you need to change an initialization parameter. This is a nice touch, considering that Oracle9i also makes it possible to change most every initialization parameter you would ever care to change dynamically while the database is online and available for users.
Oracle can also provide a large degree of self-tuning due to its capability to control its initialization parameter settings dynamically as well. Additionally, the Server Parameter file feature enables Oracle9i to remember settings for initialization parameters that were changed dynamically across sessions.
*Using a Server Parameter File
Server parameter files are created from standard init.ora files. They are housed inside your Oracle database, so obviously they won't be available until the database is created. Let's look at the steps for creating a Server Parameter file.
*Step 1: Create InitSID.ora
Define the settings you want in your initial Oracle9i database configuration before the database is actually created, using the guidelines for creating an init.ora file. You can use one you already have, so long as you make the changes we've already discussed.
*Step 2: Create the Database
Create the database.
*Step 3: Create SPFILE
Once the database exists, issue the create spfile command to create your Server Parameter file using code similar to the following:
| create spfile from pfile = '/u01/app/oracle/admin/oracle/pfile/init.ora'; |
Oracle9i will create a Server Parameter file on the machine hosting the Oracle database. The file will be named internally. Every time your instance starts, Oracle9i will read the Server Parameter file in addition to your init.ora file, in order to determine the settings to use.
Tip: TIP: You can also export the contents of your Server Parameter file back to a traditional init.ora file using the create pfile from spfile command.
*Step 4: Use alter system
Use the alter system set initparm = value command to change parameter settings dynamically — Oracle will update the SPFILE.
The alter system command has a new clause called scope that allows you to specify whether you want Oracle to update the Server Parameter file with the new setting or not. The scope clause has three possible settings: spfile, memory, or both. If you define the scope of the dynamic parameter change to be spfile or memory, then Oracle9i changes the parameter setting in the Server Parameter file or existing instance memory configuration only, respectively. If you use both, then Oracle9i sets the new parameter value in both the Server Parameter file and makes the change to the current instance. The following code contains the proper syntax for all three settings:
alter system set shared_pool_size = 10485760 scope = spfile;
alter system set shared_pool_size = 10485760 scope = memory;
alter system set shared_pool_size = 10485760 scope = both;
Question 24
Question 25
Question 26
Question 27
Question 28
Question 29
Question 30
Question 31
In this lesson, you learned about the nine initialization parameters that must be set when creating a new database. You also learned about the new Server Parameter file feature, how to create a Server Parameter file, and how to make alter system changes apply to the parameter file.
Lesson 10. Using the Database Configuration Assistant
The Oracle Database Configuration Assistant (DBCA) has been greatly enhanced in Oracle9i. It introduces templates which allow you to define transaction processing databases, decision support systems, data warehouses, and databases for hybrid purposes.After completing this lesson, you should be able to:
- Describe the purpose and function of the DBCA and database templates
Oracle Database Configuration Assistant (DBCA) is one of the many GUI tools that Oracle provides in support of your Oracle database. DBCA is used to create databases, and it handles a lot of the tricky work behind the scenes so you don't have to. With DBCA, you can perform the following tasks with ease:
- You can create a database from scratch by specifying all the information needed to create one.
You can either specify the tool to create the database after you specify information or generate the scripts necessary to create the database manually or store the information in a template to be used later to create other databases. It is the last feature that makes this tool very useful to DBAs. You will learn more about it in the following sections.
- You can delete an existing database.
- You can clone an existing database with and without the data. You can reverse-engineer a template of an existing database as well.
Tip: TIP: DBCA uses OFA standards, so database files, administrative files, and parameter files follow OFA naming standards and placement practices.
Templates are new to Oracle9i. A template is a definition of a database. Oracle provides a set of predefined templates and DBCA enables you to custom create templates for your own needs. When creating a template, DBCA lets you specify everything possible from the location of control files, redo log file, database datafiles, size of your SGA, location of administrative files, parameters in init.ora file, and many other things. The template selection step from DBCA is shown here.
Oracle8i did have a version of DBCA, but the tool was limited to creating databases in a few simple ways. DBCA has been made even easier by letting you define variables commonly defined as part of configuring your Oracle operating environment. For example, if you are familiar with UNIX, these environment settings are what you would find in a file such as .profile. These variables, such as ORACLE_BASE, SID, and others can be used in the filenames and such places to replace them with information specific to the instance you are creating. The environment variable dialog box from Oracle9i DBCA is shown here.
To create a database, you may choose an existing template that Oracle provides or you may create one built to suit your needs. Once you have a database template that looks close to what you are building, you use that template, change few things, and use it to create your database. This is a time-saving tool for DBAs. A portion of the details screen for the Data Warehouse template is shown here.
DBCA can create a database from either predefined templates provided by Oracle or custom-created templates created by you, or it can just create a database without using any of the templates. The DBCA wizard goes through the same displays as the one used in template creation and gathers necessary information to create the database. At the end of the wizard, you can choose between creating a database, creating a database and saving the database creation scripts, or just saving the scripts. (Saving the scripts allows you to take a look at them before executing them manually to create the database.) One of the final steps of the DBCA wizard is shown here.
Question 32
Question 33
In this lesson, you learned about the enhancements to the Database Configuration Assistant (DBCA), including templates, which simplify the creation of all types of databases.
Lesson 11. Creating a Database Manually
An Oracle database is easy to create manually using SQL*Plus.After completing this lesson, you should be able to:
- Create a database manually
- Explain the logfile, maxlogfiles, datafile, maxdatafiles, and characterset clauses
- Describe what the SYSTEM tablespace contains after you execute the create database command
Of course, you also have the option to create a database yourself manually, using scripts. Moreover, there are some compelling reasons to do so:
- First, you retain a great deal of control over the nuances of how your database will be created.
- Using scripts for database creation also gives you a template of sorts from which you can base the creation of other databases.
- Finally, scripts may be necessary in situations where you want to create a database on a remote machine, and do not have access to the graphical desktop of that remote machine in order to run the DBCA on it.
*Preparatory Steps
You should execute the following steps before manually creating a database:
| Step | Action |
|---|---|
| 1 | Make sure that init.ora file exists and the entries in it refer to the correct database name. You can execute a global search and replace operation to identify and correct discrepancies within any text editor, but be sure you comb through each situation manually so that you don't make a mistake accidentally. Now is also the time to verify one last time that the location of control files in this file is accurate. |
| 2 | Make sure the directories specified for BACKGROUND_DUMP_DEST, USER_DUMP_DEST, CORE_DUMP_DEST, UTL_FILE_DIR, and any other administrative directories used by Oracle actually exist on your filesystem. If those directories do not exist, the creation process for your new database may fail when you try to start the instance. |
| 3 | Start SQL*Plus and connect to the database as sys as sysdba. |
| 4 | Start up the instance in nomount mode so that no existing database is mounted to the instance. |
*Sample create database Statement
Execute the create database statement to create the database by either typing it into SQL*Plus or by running a script with contents similar to the statements shown here:
CONNECT SYS AS SYSDBA
CREATE DATABASE orgdb01
LOGFILE
GROUP 1 ('/u01/oradata/oracledb/redo1a.log',
'/u02/oradata/oracledb/redo1b.log') SIZE 5M,
GROUP 2 ('/u02/oradata/oracledb/redo2a.log',
'/u01/oradata/oracledb/redo2b.log') SIZE 5M
MAXLOGFILES 40
DATAFILE '/u03/oradata/oracledb/sys01.dbf'
SIZE 50M AUTOEXTEND ON NEXT 30M MAXSIZE 150M
MAXDATAFILES 240
CHARACTERSET WE8IS08859P1;
EXIT;
If your database creation process is successful, you can then mount the database you just created using the alter database mount command, and then open it using the alter database open command. Your database now contains one tablespace, called SYSTEM, with one or more datafiles.
alter database mount
alter database open
You can then create the tablespaces for undo segments, users, temp, and other data. You can also create these tablespaces using clauses within the create database command. We'll see some more examples of create tablespace commands.
alter database mount
alter database open
In versions of Oracle prior to Oracle9i, you would then create the undo segments and bring them online. In Oracle9i, however, all you need to do is create the undo tablespace — Oracle creates its own undo segments.
UNDO TABLESPACE UNDOTBS
DATAFILE '/u05/oradata/oracledb/
undotbs01.dbf
SIZE 50M REUSE AUTOEXTEND ON NEXT
5120 MAXSIZE UNLIMITED;
Finally you run the scripts provided by Oracle to create the data dictionary (also called Catalog) and other database objects necessary for database administration.
UNDO TABLESPACE UNDOTBS
DATAFILE '/u05/oradata/oracledb/
undotbs01.dbf
SIZE 50M REUSE AUTOEXTEND ON NEXT
5120 MAXSIZE UNLIMITED;
*What Is Created?
If the database is brought up in nomount mode (before the create database command has been executed), Oracle reads the init.ora file, which it uses to figure out where to create the control files. Oracle also uses the settings for parameters in this file to specify the size of the SGA, where to place to log and trace files, and other important information. With this information, it creates the SGA in memory and the background processes.
# The locations of control files,
# trace files, the alert log, and
# the size of the SGA are determined
# by init.ora.
# The locations of tablespaces and
# redo log files are determined by
# the create database command.
When the create database command is executed, it creates the control files as specified in the init.ora file, and the redo log files and the SYSTEM tablespace datafile as specified in the command itself.
The create database command also uses several other initialization parameters set in the initsid.ora file in database creation. These include DB_BLOCK_SIZE and certain NLS environment settings.
Finally, SYS and SYSTEM accounts are created and a single undo segment is created in the SYSTEM tablespace.
# STARTUP nomount starts the SGA and
# background processes and opens
# trace and alert files.
# create database creates control files,
# redo log files, and at least the SYSTEM
# tablespace with a single undo segment.
# It also creates the SYS and SYSTEM
# accounts.
*SYSTEM Tablespace
The files created as part of the datafile clause of the create database command are SYSTEM tablespace datafiles. A tablespace is a logical collection of disk files collectively used to store data. The SYSTEM tablespace can be compared to the root directory of a machine's filesystem. The SYSTEM tablespace houses the tables comprising the basis for the Oracle data dictionary, as well as the system undo segments. The tables of the data dictionary and system undo segment will all be owned by user SYS.
CONNECT SYS AS SYSDBA
CREATE DATABASE orgdb01
LOGFILE
GROUP 1 ('/u01/oradata/oracledb/redo1a.log',
'/u02/oradata/oracledb/redo1b.log') SIZE 5M,
GROUP 2 ('/u02/oradata/oracledb/redo2a.log',
'/u01/oradata/oracledb/redo2b.log') SIZE 5M
MAXLOGFILES 40
DATAFILE '/u03/oradata/oracledb/sys01.dbf'
SIZE 50M AUTOEXTEND ON NEXT 30M MAXSIZE 150M
MAXDATAFILES 240
CHARACTERSET WE8IS08859P1;
EXIT;
Oracle creates one system undo segment in the SYSTEM tablespace at database creation for Oracle to acquire at database startup. Without this system undo segment, the database won't start. In the interest of preserving the integrity of the Oracle database, the DBA should ensure that only the data dictionary and system undo segments are placed in the SYSTEM tablespace. No data objects owned by any user other than SYS should be placed in the SYSTEM tablespace. Instead, you will create other tablespaces to store those database objects. You will learn more about tablespaces and datafiles.
logfile Clause
Redo logs are created with the logfile clause. Redo logs are entries for data changes made to the database. You must create at least two redo log groups for your new database, each with at least one member. In the database created with the statements shown here, redo log group 1 consists of two members, called redo1a.dbf and redo1b.dbf, respectively.
LOGFILE
GROUP 1 ('/u01/oradata/oracledb/redo1a.log',
'/u02/oradata/oracledb/redo1b.log') SIZE 5M,
GROUP 2 ('/u02/oradata/oracledb/redo2a.log',
'/u01/oradata/oracledb/redo2b.log') SIZE 5M
If any file specified in the create database statement currently exists on the system, and the reuse keyword is used, Oracle will overwrite the file. Be careful when reusing files to prevent accidentally overwriting the files in your existing database on the host machine. You will learn more about redo logs.
maxdatafiles, maxlogfiles, and autoextend
Other options set when the database is created include maxdatafiles and maxlogfiles. The maxdatafiles option specifies the initial sizing of the datafiles section of the control file at create database or create controlfile time. An attempt to add a file whose number is greater than maxdatafiles, but less than or equal to DB_FILES, causes the control file to expand automatically so that the datafiles section can accommodate more files. Oracle recommends setting maxdatafiles to the same value as DB_FILES.
maxlogfiles specifies the maximum number of redo log file groups that can be created.
MAXLOGFILES 40
.
.
.
MAXDATAFILES 240
You can also use the autoextend option when defining datafiles. When autoextend is used, the datafiles will automatically allocate more space when the datafile fills, up to a total size specified by the maxsize keyword. However, you'll want to take care to ensure that Oracle does not try to extend the datafile to more space than the filesystem has available. The example shown here turns AUTOEXTEND on and specifies that the tablespace grow in 30MB increments up to a maximum of 150MB.
DATAFILE '/u03/oradata/oracledb/sys01.dbf'
SIZE 50M AUTOEXTEND ON NEXT 30M MAXSIZE 150M
characterset and archivelog
The final item in the create database statement is characterset, which is used to identify the character set used in the Oracle database for information storage.
Another option you can use in create database commands is archivelog. When archivelog is used, Oracle archives the redo logs generated. The location and format of archive log files are specified in init.ora. The default is NOARCHIVELOG mode, and the mode can be switched after creating the database.
ARCHIVELOG
CONNECT SYS AS SYSDBA
CREATE DATABASE orgdb01
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('/u01/oradata/oracledb/redo1a.log',
'/u02/oradata/oracledb/redo1b.log') SIZE 5M,
GROUP 2 ('/u02/oradata/oracledb/redo2a.log',
'/u01/oradata/oracledb/redo2b.log') SIZE 5M
MAXLOGFILES 40
DATAFILE '/u03/oradata/oracledb/sys01.dbf'
SIZE 50M AUTOEXTEND ON NEXT 30M MAXSIZE 150M
MAXDATAFILES 240
CHARACTERSET WE8IS08859P1;
EXIT;
Question 34
Question 35
Question 36
Question 37
In this lesson, you learned about the create database statement and how it creates the control files as specified in the init.ora file. You also learned about clauses that are used to create the redo log files and the SYSTEM tablespace datafile. And, you learned about options you can use when manually creating your database.
Lesson 12. Globalization Support
Previously, Oracle's globalization support capabilities were referred to as National Language Support (NLS) features. In Oracle9i NLS is a subset of Globalization Support.After completing this lesson, you should be able to:
- Identify the differences between single-byte, multi-byte, 7-bit, and 8-bit character sets
- List one method of changing the database character set
Globalization support enables you to deploy Oracle in languages other than English. This topic comprises only three percent of the actual test material on the Oracle9i DBA Fundamentals I exam.
Oracle supports many different language-encoding schemes in order to produce a product that is usable worldwide. There are four different classes of character codes:
- Single-byte character sets (both 7-bit and 8-bit)
- Varying-width multibyte character sets
- Fixed-width multibyte character sets
- Unicode character sets
Actually, the combination of a character set and a particular method of encoding the character set is technically called a "language encoding scheme." But in common parlance the term "character set" is often used to refer to the entire encoding scheme. We will use the term "character set."
*Single-Byte, Multibyte, and Unicode
A single-byte character set uses a single byte (8 bits) to store a character. You may be familiar with the single-byte character set U.S. 7-bit ASCII (US7ASCII) which is used in the United States. It uses only 7 bits of the byte and represents 128 characters. (The remaining bit can be used for parity.) The characters represented in ASCII are shown here.
There are also several single-byte character sets used throughout Europe that use all 8 bits. These are known as 8-bit character sets.
ISO 8859-1, also known as ISO Latin 1 (WE8ISO8859P1) is the Western European 8-bit ISO standard character set and is widely used. Because it uses all 8 bits as data bits, it includes the standard 7-bit ASCII set (represented by numbers 0 – 127), plus the additional characters used in W. European languages (represented by numbers 128 – 255). The characters of Latin I are shown here.
As of late 2001, the Euro currency had begun to be implemented. ISO 8859-1 does not include the Euro symbol, so it's reasonable to predict that the ISO 8859-15 standard, also known as ISO Latin 9 will be increasingly used in place of 8859-1.
Multibyte character sets are used to represent languages that have too many characters to be represented by one byte.
Oracle uses multibyte character sets to support languages like Japanese, Chinese, Korean, and other languages that use complex characters to represent language. Multibyte character sets are also used for Arabic and Hebrew, which add the complexity of being read from right to left. Multibyte encoding can be either fixed-width or varying width. These are covered in another lesson.
Most character sets are limited in scope, covering only a few languages or alphabets. Unicode is a standard that has been developed for encoding all characters usable in computers, including all characters in all languages, plus specialized print media, math, and computer characters. UTF8 is an example of a Unicode character set (and does include the Euro symbol). Part of the list of symbol types from the UNICODE.ORG website is shown here. Unicode character sets are not really a separate class, since a Unicode character set can be either single-byte or multi-byte, 7-bit or 8-bit.
*When You Can Switch Languages
Your database character set is defined using the characterset clause in the create database command. For example
CHARACTERSET WE8IS08859P1;
There are a few options to change the character set after the database is created. If the new character set is a superset of the existing character set, the character set can be changed by using the command alter database character set. For example U.S. 7-bit ASCII is a subset of the majority of other character sets, and so it can be easily migrated:
alter database orgdb01 characterset utf8
Note: If the new character set is not an exact superset of the existing one, then a full export of the database is required, recreating the database with a new character set, followed by a full import of the database.
Question 38
Question 39
Question 40
In this lesson, you learned about character sets (or character encoding schemes) and how they can be 7-bit or 8-bit, single-byte or multibyte. You also learned about the Unicode standard which attempts to encompass all existing characters in all languages.
Lesson 13. Choosing a Character Set
Every Oracle DBA must know enough English to understand the SQL code.After completing this lesson, you should be able to:
- Distinguish between the database character set and the national character set, and cite the circumstances under which the character set can be changed
- Describe the difference between fixed-width and varying-width character sets
Two character sets can be defined for your database: the database character set and the national character set. Both database and national character sets are defined when you create your database.
The database character set is used for Oracle SQL and PL/SQL source-code storage. SQL and PL/SQL must be stored in a language that contains all the characters in either U.S. 7-bit ASCII or EBCDIC, whichever is supported by your host platform. So, even if you speak Korean and want to store Korean in your database, you still need to know enough English to type in the SQL and PL/SQL commands.
*Database vs. National Character Sets
The national character set is the language you use to store your data. Like the database character set, the national character set is specified at the time of database creation, but using the clause national character set. If this is not specified, then Oracle defaults to the database character set. Your database and national character sets should be closely related for best results.
NATIONAL CHARACTER SET "UTF8";
The client or user machines can have a different character set from the database character set. This is achieved by setting the environment variable. Make sure that the database character set is a superset of the client character set.
setenv NLS_LANG JAPANESE_JAPAN.JA16SJIS
setenv NLS_LANG ARABIC_UNITED_ARAB_EMIRATES.
AR8MSWIN1256
setenv NLS_LANG HEBREW_ISRAEL.IW8MSWIN1255
Some special conditions apply to national character sets and text or large object variables:
- The CLOB, CHAR, and VARCHAR2 datatypes can store database character sets only, and each has national character set equivalents, called NCLOB, NCHAR, and NVARCHAR2, respectively.
- The LONG datatype can only store character sets that are allowed to be database character sets.
*Varying-Width vs. Fixed-Width Types
As mentioned previously, multibyte character sets can be varying width or fixed width. In a varying-width character set, some characters are represented using a single byte, some using two or more bytes. The value of the most significant bit is used to indicate if a byte represents a single byte or is part of a series of bytes representing a character.
The UTF-8 encoding scheme is a varying-width scheme that represents the Unicode characters. It uses one byte to represent the basic ASCII characters, and two or more bytes to represent the additional characters.
Fixed-width character sets use a fixed number of bytes for each character. The UTF-16 encoding scheme is a fixed-width scheme that also represents the Unicode characters. It uses two bytes to represent all the characters.
In general, the trade-off between fixed-width and varying-width character sets is that fixed-width sets permit better performance in string operations, such as length( ) and substr( ), but varying-width sets are better for managing space. However, you cannot use a multibyte character set that is fixed width as the database character set — it can only be used as the national character set. (You can use a single-byte set or a varying-width set.)
Don't confuse the concepts of fixed width and varying width with the concepts of fixed length and varying length as applied to the CHAR, VARCHAR2, (or NCHAR and NVARCHAR2) datatypes.
When applied to datatypes, fixed length means that the data stored in a CHAR(3) will always be three characters long; even if you specify only one character of data, it will be padded with two extra spaces. Varying length means that VARCHAR2 columns will not be padded with extra blanks, so the same one character of data in a VARCHAR2(3) column will be only one character long.
Question 41
Question 42
Question 43
In this lesson, you learned about the distinction between the database character set and the national character set and the fact that either one can be either fixed-width or varying width. The database character set cannot be fixed-width multibyte since it must be a superset of your single-byte platform character set (either U.S. 7-bit ASCII or EBCDIC). Also, certain datatypes are limited to either the database character set or the national character set.
Lesson 14. Setting NLS Parameters
There are several different areas where language-dependent behavior can be specified in Oracle.After completing this lesson, you should be able to:
- Identify NLS parameters and their functions
- List the methods of changing NLS parameters and their order of precedence
There are several language-dependent variables that are based on the database character set and/or national character set currently defined for a server, client, or session. The following is a list of some National Language Support (NLS) parameters and their specifications:
| PARAMETER | Function |
|---|---|
| NLS_LANGUAGE | Indicates the language for error messages, the names of days and months, and the symbols for 12-hour time of day and calendar era; this parameter also defines the sort mechanism Oracle will use. Determines defaults for territory and character set. |
| NLS_DATE_LANGUAGE | Changes the language for day and month names, and other language components of date information. Default is determined by language. |
| NLS_SORT | Changes the sort mechanism Oracle uses; for example, you can override the default sort order of the national character set to use the sort order of another character set. Default is determined by language. |
| NLS_TERRITORY | Indicates the numbering for day of the week, default date format, currency symbols, and decimal symbol. Default is determined by language. |
| NLS_CURRENCY | Identifies a new currency symbol. Default determined by territory. |
| NLS_ISO_CURRENCY | Identifies a new territory whose ISO currency symbol should be used. Default determined by territory. |
| NLS_DATE_FORMAT | Identifies a new date format. Default determined by territory. |
| NLS_NUMERIC_CHARACTERS | Identifies a new decimal (0.00) and group (0,000) separator. Default determined by territory. |
| NLS_LANG | Overrides default NLS settings for the user (client), using the following format: language_territory.characterset. |
*At the Server Level
There are several ways to specify NLS parameters on the server and on the client:
1. You can specify the NLS parameter in the database initialization file on the server. These settings have no effect on the client side, but only on the server behavior.
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=AMERICA
NLS_CHARACTERSET=ZHT16BIG5
*At the Client or Session Level
2. You can specify the NLS parameter as an environment variable on the client. This defines the behavior of the client and overrides the default values set for the session in the initialization parameter file. An example of setting an NLS parameter as an environment variable in a UNIX system is shown here.
% setenv NLS_LANG
english_unitedkingdom.we8iso8859p1
3. You can change NLS parameters for a session. Altering NLS parameters within the session can be accomplished in two ways:
You can use alter session set parmname = value where parmname is the name of the NLS parameter and value is what you want to set the parameter to.
% setenv NLS_LANG
english_unitedkingdom.we8iso8859p1
Another method of altering a session is to use the setnls( ) procedure in the DBMS_SESSION built-in package, which accepts two values: parmname and value.
BEGIN
DBMS_SESSION.SET_NLS
('NLS_DATE_FORMAT','''YYYY:MM:DD''')
END;
Finally, you can change parameters within certain SQL functions. For example
TOCHAR(hiredate, 'YYYY-MM-DD','NLS_DATE_LANGUAGE =
FRENCH')
The NLS parameters defined in SQL functions have the highest priority, followed by parameters specified in the alter session command, followed by the environment variable, followed by the initialization parameter, and finally followed by the lowest priority — the database default parameters.
Question 44
Question 45
In this lesson, you learned about NLS parameters and how they can be set either on the server in the init.ora file, on the client as environment variables, at the session level, or at the SQL command level.
Lesson 15. Viewing Globalization Support Information
You can retrieve your data in various NLS formats.After completing this lesson, you should be able to:
- Recognize SQL functions that take NLS parameters
- State the function of the NLS_SORT parameter
- List the views containing information about the NLS parameters of a database
You can use NLS parameters when retrieving information from your database by using the standard SQL functions to_char( ), to_number( ), and to_date( ). These datatype conversion functions accept various NLS parameters and return information based on the NLS parameter you gave them.
TO_CHAR(thisColumn,
'DD/MON/YYYY',
'nls_date_language
= language')
In addition, several sorting functions are available that utilize the NLS_SORT parameter, as shown here
TO_CHAR(thisColumn,
'DD/MON/YYYY',
'nls_date_language
= language')
Finally, NLS parameters can be used to format date and numeric information. The following lines show output from a table with NLS parameters used to assist in providing meaningful formatting in a simple report. For this example, the NLS_NUMERIC_CHARACTERS parameter is used. Note the use of L, G, and D as markers in the formatting mask. L stands for the local currency symbol, G stands for the group (thousands) separator, and D stands for the decimal separator.
SQL> select year, tochar(gnp,'L9G999G999G999D99','NLS_NUMERIC_CHARACTERS=".,$"')
2> as GNP
3> from usgnp;
YEAR GNP
--------- ---------------------------
1997 $5,948,399,939.34
1998 $6,043,345,223.34
1999 $6,143,545,453.80
You can find information about settings for your NLS parameters in Oracle by using the V$PARAMETER view, or by looking at these specialized NLS views:
| View Name | Function |
|---|---|
| NLS_DATABASE_PARAMETERS | All NLS databasewide parameters are stored in this view. |
| NLS_INSTANCE_PARAMETERS | All NLS instancewide parameters are stored in this view. |
| NLS_SESSION_PARAMETERS | All NLS parameters for the active session are stored in this view. |
| V$NLS_PARAMETERS | This is a superset of the previous three views. |
| V$NLS_VALID_VALUES | This is a listing of all valid values for all NLS parameters. |
Question 46
Question 47
Question 48
Question 49
Question 50
Topic 15.1 Exercises
* Exercise 1
Try using the NLS_NUMERIC_CHARACTERS parameter in Oracle9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i for these practice exercises. Experiment with the order of characters specified for the previous NLS_NUMERIC_CHARACTERS parameter, and see what happens with your output. (You will have to set it at the system or session level.) |
In this lesson, you learned about SQL conversion functions that take NLS parameters and the views that support NLS parameters. You also learned about the NLSSORT function, which also takes NLS parameters. Finally, you learned about the NLS_NUMERIC_CHARACTERS parameter, which assists with formatting numbers.
Lesson 16. Course In Review
This lesson includes questions and material covering the entire course. You can use it to review and study for the exam.Topic 16.1 Review Notes
*Review Notes1. Be sure you understand the purpose of an init.ora file in Oracle, and that you know some ways for obtaining the values set for parameters on a running database.
2. Understand the use of the alter system, alter session, and alter system deferred commands in order to change values set for parameters once the instance is up and running.
3. Be sure you know where to find actual copies of init.ora files and where links to actual copies of init.ora files exist in your Oracle database.
4. Understand what is meant by Oracle-Managed Files. Your understanding should include the functionality OMF provides.
5. Be sure you know which init.ora parameters to set as part of OMF, including those for datafiles and for online redo logs.
6. Be sure you understand the difference between starting an Oracle instance and opening an Oracle database for use. Know the commands for each.
7. Understand why Oracle has so many different modes for starting an instance and opening the database, rather than just simply having one command to do the job.
8. Understand the command used for shutting down an Oracle instance and closing the database. Be sure you can name the four different priorities for database shutdown.
9. Be sure you understand the difference between trace files and log files. Know the types of files as well as the processes that might generate those files that are stored in the directory identified by the BACKGROUND_DUMP_DEST and USER_DUMP_DEST parameters.
10. Understand when background processes generate trace files. Also, know what gets stored in the Alert log file. Know that this is the first place you look if your database crashes abnormally.
11. Know that there are certain init.ora parameters that must be changed in order to create a new database, such as CONTROL_FILES, DB_NAME, and DB_BLOCK_SIZE, especially if you've copied the init.ora file from another database location. If these parameters are not set to a value appropriate for the new database and you have existing databases on the host, you could damage those existing databases.
12. Understand the new SPFILE feature available in Oracle9i for dynamic instance parameter configuration, management, and tuning.
13. Understand the basic use of the Oracle Database Configuration Assistant, and be sure you can describe the advantages of using this tool versus manually creating an Oracle database.
14. Understand the concept of a database template. Be sure you can describe the benefits offered by database templates.
15. Know that any database created by the Database Configuration Assistant will be OFA-compliant in terms of its directory and file layout.
16. Understand the process for manually creating a database using the create database command. Be sure you can identify the various clauses used as part of issuing that command.
17. Be sure you understand that any datafiles created as part of the create database command will belong to the SYSTEM tablespace. Also, know that you must create at least two online redo logs as part of this process.
18. When manually creating an Oracle database, know that your instance must be started but that you cannot have any other database already mounted to that instance.
19. Compare fixed-length and varying-length datatypes to fixed-width and varying-width multibyte character sets. What is meant by each?
20. Compare database and national character sets. What is meant and permitted by each?
21. Understand that the data types NCHAR, NVARCHAR2, and NCLOB are provided to declare columns as variants of the basic types CHAR, VARCHAR2, and CLOB and note that they are stored using the national character set and not the database character set.
22. Be able to identify two ways to change NLS parameters in your session.
23. Be able to identify the parameter that changes the format of information in the DATE datatype.
24. Identify the view that contains all NLS parameters for your instance, session, and database.
25. Identify a way you might use an NLS parameter in a SQL conversion function. In what other ways might this be a useful feature in Oracle?
Question 51
Question 52
Question 53
Question 54
Question 55
Question 56
Question 57
Question 58
Question 59
Question 60
Question 61
Question 62
Question 63
Question 64
Topic 16.2 Exam Preparation
*Exam PreparationFollowing is a bulleted list of fast facts to review — crib notes for the days leading up to the Oracle9i DBA Fundamentals I exam.
- The server parameter file feature in Oracle9i enables you to create a server parameter file that Oracle9i can dynamically modify in support of changes to the configuration of your instance.
- Server parameter files are created using the create spfile from 'filename' command.
- When you use the alter system command in Oracle9i to change the settings for instance parameters, you can specify a new clause, scope, to determine where Oracle should make the instance parameter change:
spfile - Oracle9i changes the parameter setting in the server parameter file only.
memory - Oracle9i changes the parameter setting for the current instance only.
both - Oracle9i changes the parameter setting for both server parameter file and current instance. - To start and stop a database, the DBA must connect as internal or sysdba.
- The tool used to start and stop the database in Oracle9i is SQL*Plus.
- Another tool for managing database administration activity is Oracle Enterprise Manager (OEM).
- There are several options for starting a database:
startup nomount - Starts the instance and does not mount a database.
startup mount - Starts the instance and mounts but does not open the database.
startup open - Starts the instance and mounts and opens the database.
startup restrict - Starts the instance, mounts and opens the database, but restricts access to those users with restricted session privilege granted to them.
startup recover - Starts the instance, leaves the database closed, and begins recovery for disk failure scenario.
startup force - Makes an instance start that is having problems either starting or stopping. - When a database is open, any user with a username and password and the create session privilege can log into the Oracle database.
- Closing or shutting down a database must be done by the DBA while running SQL*Plus and while the DBA is connected to the database as internal or sysdba.
- There are four options for closing a database:
shutdown normal - No new existing connections are allowed, but existing sessions may take as long as they want to wrap up.
shutdown immediate - No new connections are allowed, existing sessions are terminated, and their transactions are rolled back.
shutdown transactional - No new connections are allowed, existing sessions are allowed to complete current transaction, and then disconnected.
shutdown abort - No new connections are allowed, existing sessions are terminated, and transactions are not rolled back. - Instance recovery is required after shutdown abort is used.
- You can obtain values for initialization parameters from several sources:
V$PARAMETER dynamic performance view
show parameter command in SQL*Plus
OEM Instance Manager administrative tool - Several important run-time logging files exist on the machine hosting Oracle. Each background process, such as LGWR and DBW0, will have a trace file if some error occurs in their execution, and the instance has a special trace file called the ALERT log. Trace files are written whenever the background process has a problem executing. The ALERT log is written whenever the instance is started or stopped, whenever the database structure is altered, or whenever an error occurs in database.
- Trace files and ALERT logs are found in the directory identified by the BACKGROUND_DUMP_DEST parameter in the initsid.ora file.
- Before creating the database, assess several things on the operating system level:
Are there enough individual disk resources to run Oracle without I/O bottlenecks?
Is there enough CPU, memory, and disk space for Oracle processing?
Are disk resources for different Oracle databases on the same host in separate directories?
Are environment settings correct for the database creation? - The first step in creating a database is to back up any existing databases already on the host machine.
- The second step in creating a database is for the DBA to create a parameter file with unique values for several parameters, including the following:
DB_NAME - The local name for the database.
DB_DOMAIN - The networkwide location for the database.
DB_BLOCK_SIZE - The size of each block in the database.
DB_CACHE_SIZE - The size of DB buffer cache.
PROCESSES - The maximum number of processes available on the database.
UNDO_MANAGEMENT and UNDO_TABLESPACE - Defines how Oracle should handle configuration and management of undo segments. - After creating the parameter file, the DBA executes the create database command, which creates the datafiles for the SYSTEM tablespace, an initial undo segment, SYS and SYSTEM users, and redo log files. On conclusion of the create database statement, the database is created and open.
- The default password for SYS is change_on_install.
- The default password for SYSTEM is manager.
- The number of datafiles and redo log files created for the life of the database can be limited with the maxdatafiles and maxlogfiles options of the create database statement.
- The size of a datafile is fixed at its creation, unless the autoextend option is used.
- The size of a control file is directly related to the number of datafiles and redo logs for the database.
No comments:
Post a Comment