To create an Oracle database using DBCA (Database Configuration Assistant), follow these steps:

You can invoke the DBCA from a command line in the Unix environment or as an application in a
Windows environment. If you are using the Windows environment, choose Start ⇒ All Programs
⇒ Oracle Home ⇒ Configuration and Migration Tools ⇒ Database Configuration Assistant.

If you are in a Unix environment or would prefer to work from the command line in Windows,
type dbca from the $ORACLE_HOME/bin location. The ORACLE_HOME variable must be set
appropriately before invoking dbca. Also, it is recommended to set the PATH variable to
include $ORACLE_HOME/bin so that all Oracle Database 12c tools can be invoked without specifying or
changing to the $ORACLE_HOME/bin directory.

Step 1: Open DBCA

 Launch DBCA on your Oracle server. You can typically find it in your Oracle installation directory.

Step 2: Select "Create a Database"

 In DBCA, select the "Create a Database" option. This initiates the database creation process.

Step 3: Choose Database Type

 DBCA will prompt you to choose the type of database you want to create, such as a General
Purpose or Custom database. Select the appropriate type for your needs.

Step 4: Specify Database Identification

 Provide a unique name for your database in the "Global Database Name" field.

 You can also set the "SID" (System Identifier), which is a unique name for the instance.

Step 5: Configure Database Options

 Customize your database by configuring various options, including:

 Character set and national character set

 Initialization parameters

 Storage options (data files, control files, and redo log files)

 Memory allocation (SGA and PGA)

 Database options (such as Oracle Text and Oracle Spatial)

 Management options (Enterprise Manager, AWR, etc.)

 Set the passwords for database administrative users, including SYS and SYSTEM.

 You may also configure other user accounts as needed.

Step 7: Specify Backup and Recovery Options

 Configure backup and recovery options, including enabling automatic backups and specifying
the location for backup files.

Step 8: Review and Confirm

 Review the configuration details for your database.

 Confirm that the settings match your requirements.

Step 9: Create the Database

 Once you are satisfied with the configuration, click the "Create" button to start the database
creation process.

Step 10: Monitor Progress

 DBCA will display the progress of database creation. You can monitor the status and wait for the
process to complete.

Step 11: Finish

 Once the database creation is successful, you will receive a confirmation message.

 Click "Finish" to exit DBCA.

You have now successfully created an Oracle database using DBCA. The database is ready for use, and
you can connect to it using appropriate client tools or applications.
Metadata Overview:

 Metadata refers to data about the database, including information about tables, columns, rows,
and security settings.

 It is essential for database administration tasks.

Types of Metadata Views:

1. Data Dictionary Views:

 Provide information about the database and its objects.

 Owned by the SYS user and stored in the SYSTEM tablespace.

 Examples of data dictionary views include DBA_TABLES, DBA_USERS, DBA_VIEWS, etc.

 Differentiated by prefixes: DBA_, ALL_, and USER_.

 DBA_ views are commonly used by DBAs.

2. Dynamic Performance Views:

 Record current database activity and status.

 Begin with X$, and their corresponding view names begin with V$.

 Over 700 dynamic performance views exist.

 Examples include V$DATABASE, V$VERSION, V$OPTION, V$SQL, etc.

 Public synonyms beginning with V$ are created for these views.

Differences Between Data Dictionary and Dynamic Performance Views:

 DBA_ views have plural names; V$ views generally have singular names.

 DBA_ views require the database to be open and running, while some V$ views are available
even when the database is not fully running.

 Data in DBA_ views is static and persists after database shutdown, whereas V$ views contain
dynamic statistical data that is cleared upon shutdown.

Viewing Metadata:

 To view metadata, use SQL queries on the relevant views.

 For example, to see information about tables: SELECT * FROM DBA_TABLES;

Scripts for Creating Metadata:

 The Oracle data dictionary and dynamic performance views are created during database
creation.

 Scripts for creating metadata are stored in the $ORACLE_HOME/rdbms/admin directory.

 The [Link] script creates base dictionary objects, and [Link] script supports PL/SQL
functionality.

Note on Modification:

 You cannot log in as SYS and modify the data dictionary views or update information directly
using SQL.

 The only SYS-owned table you can delete records from is AUD$, which is used for database audit
information.

This information should help you understand and study Oracle Database Metadata and how to work
with metadata views using SQL commands.

Managing Initialization-Parameter Files

Oracle uses initialization-parameter files to store information about initialization parameters used when
an Oracle instance starts. Oracle reads the parameter file to obtain information about how the Oracle
instance should be sized and configured upon startup.

The parameter file can be a plaintext file, commonly referred to as a pfile, or it can be a binary
parameter file, commonly referred to as an spfile. You can use either type of file to configure instance
and database options; however, there are some important differences between the two types of
configuration files, as shown in Table 9.14.

Pfiles vs. Spfiles

 Pfile:

 Text file that can be edited using a text editor.

 When changes are made to the pfile, the instance must be shut down and restarted
before it takes effect.

 Is called initinstance_name.ora.
  Oracle instance reads only from pfile.

 Can be created from an spfile using the create pfile from spfile command.

 Spfile:

 Binary file that cannot be edited directly.

 Parameter changes made to the database using ALTER SYSTEM are updated in the
spfile.

 Is called spfileinstance_name.ora.

 Oracle instance reads and writes to the spfile.

 Can be created from a pfile using the create spfile from pfile command.

You can specify more than 365 documented configuration parameters in the pfile or spfile. Oracle
Database 12c divides these parameters into two categories: basic and advanced. Oracle recommends
you set only the basic initialization parameters manually. Oracle also recommends you do not modify
the remaining parameters unless directed to do so by Oracle Support or to meet the specific needs of
your application.

Most parameters can be modified dynamically, meaning a database restart is not required for the new
value to take effect. But certain parameter value changes require a database restart. Table 9.15
describes the basic initialization parameters. A “Yes” in the Static column indicates that the parameter is
static and cannot be modified dynamically without a database restart.

Oracle Database 12c Basic Initialization Parameters

Parameter Name | Static | Description

 CLUSTER_DATABASE | Yes | Tells the instance whether it is part of a clustered environment.

 COMPATIBLE | Yes | Specifies the release level and feature set you want to be active in the
instance.

 CONTROL_FILES | Yes | Designates the physical location of the database control files.

 ... (more parameters listed)

On production databases, if your Oracle license is based on the number of named users, you can enforce
the license compliance by setting the LICENSE_MAX_USERS parameter.

Note on Undocumented Parameters: More than 2,900 undocumented configuration parameters are
available in Oracle Database 12c. Undocumented configuration parameters are distinguished from their
documented counterparts by the underscore that precedes their name. We do not recommend utilizing
undocumented pfile or spfile parameters because knowing the appropriate reasons to use these
parameters, and the appropriate values to set these parameters to, is almost pure speculation because
of their undocumented nature.

You may use the following query logged in as SYSDBA to view all undocumented parameters in the
database.

Modifying Initialization-Parameter Values

In some instances, you may need to change initialization parameters. Here are the options to change the
initialization-parameter value based on the type of parameter file used:

1. If PFILE is used:

 Edit the pfile using an OS editor.

 Make any appropriate changes.

2. If SPFILE is used:

 Connect to the instance.

 Use the ALTER SYSTEM SET parameter_name = value statement to make changes.

3. Use EM Database Express:

 Navigate to the Configuration menu.

 Choose Initialization Parameters.

 Use filters to find the parameter to be modified.

 You have to be logged in as SYSDBA to modify the parameters.

Using EM Database Express:

 To modify existing database parameters:

 Navigate to the Configuration menu.

 Choose Initialization Parameters.

 The SPFile tab shows the parameters as set in the spfile.

 You can modify Dynamic parameters on the Current tab, but changes are not
permanent.

 The SPFile tab allows changes to be persistent across startups and shutdowns.
  Use SQL code alter system set "<parameter>"=<value> scope=spfile sid='*' for set.

 Use SQL code alter system reset "<parameter>" scope=spfile sid='*' for reset.

Using SQL*Plus or SQL Developer:

 Alternatively, you can use SQL*Plus or SQL Developer to make changes.

 Utilize two dynamic performance views:

 V$PARAMETER: Shows current initialization parameters.

 V$SPPARAMETER: Shows parameters defined in the spfile.

V$PARAMETER Columns:

 NAME: Name of the initialization parameter.

 VALUE: Current value of the parameter.

 DISPLAY_VALUE: Current value in a user-friendly format.

 DESCRIPTION: Short description about the parameter.

 ISBASIC: TRUE if the parameter is basic.

 ISDEFAULT: FALSE if the parameter was specified in the pfile or spfile during instance startup.

 ISMODIFIED: FALSE if the parameter has not been modified since the instance started.

 ISSES_MODIFIABLE: TRUE if the parameter can be modified using ALTER SESSION.

 ISSYS_MODIFIABLE: FALSE if the parameter cannot be modified using ALTER SYSTEM.

V$SPPARAMETER:

 Shows contents of the spfile used to start the instance.

 ISSPECIFIED column indicates if the parameter was specified in the spfile.

Changing Parameter Values:

 Use ALTER SESSION to change the value for the current session.

 Use ALTER SYSTEM to change values system-wide or in the spfile.

 Use SCOPE clause (MEMORY, SPFILE, or BOTH) to define where to apply the change.

 DEFERRED or IMMEDIATE values in ISSYS_MODIFIABLE determine when changes take effect.

 ALTER SYSTEM SET parameter_name=value SCOPE=MEMORY;

 ALTER SYSTEM SET parameter_name=value SCOPE=SPFILE;

 ALTER SYSTEM SET parameter_name=value SCOPE=BOTH;

SHOW PARAMETER and SHOW SPPARAMETER:

 Use SQL*Plus command SHOW PARAMETER to view current parameter values.

 Use SQL*Plus command SHOW SPPARAMETER to view parameters defined in the SPFile.

Starting up and Shutting down an Oracle Instance

As a DBA, you're responsible for the startup and shutdown of the Oracle instance. You can do this using
various interfaces and authorization levels, including SYSDBA and SYSOPER. Here's how to perform these
tasks:

Authorization Levels:

 SYSDBA: Allows you to perform any database task, including startup and shutdown.

 SYSOPER: Allows startup and shutdown but restricts other administrative tasks, such as
accessing non-administrative schema objects.

Startup Modes:

 When starting an Oracle database, it goes through three modes: NOMOUNT, MOUNT, and
OPEN.

 NOMOUNT: Initializes background processes and memory structures but doesn't connect to disk
structures; the database isn't available for use.

 MOUNT: Performs NOMOUNT actions and reads the control file, used for administrative tasks
like renaming data files.

 OPEN: The default startup mode, makes the database available for all users.

Additional Startup Options:

 FORCE: Used for difficult startups, like after abrupt shutdowns; can be issued regardless of the
current mode.

 RESTRICT: Opens the database but allows only users with RESTRICTED SESSION privilege.
Using SQL*Plus for Startup:

You can start the Oracle database using SQL*Plus as follows:

 Connect to SQL*Plus with SYSDBA or SYSOPER privileges.

 Use the STARTUP command with options like NOMOUNT, MOUNT, or OPEN to specify the
startup mode.

 Example: STARTUP NOMOUNT;

Changing Database Startup States Using SQL:

 To change the state from NOMOUNT to MOUNT: ALTER DATABASE MOUNT;

 To open a database from NOMOUNT or MOUNT: ALTER DATABASE OPEN;

 To open in read-only mode: ALTER DATABASE OPEN READ ONLY;

 To enable restricted mode: ALTER SYSTEM ENABLE RESTRICTED SESSION;

 To disable restricted mode: ALTER SYSTEM DISABLE RESTRICTED SESSION;

Shutting Down:

 Various shutdown options are available:

 NORMAL: Waits for all users to disconnect.

 TRANSACTIONAL: Allows active transactions to complete before disconnecting users.

 IMMEDIATE: Terminates uncommitted transactions and disconnects users.

 ABORT: Forces immediate shutdown, terminating all sessions.

Using SQL*Plus for Shutdown:

You can shut down the Oracle database using SQL*Plus as follows:

 Connect to SQL*Plus with SYSDBA or SYSOPER privileges.

 Use the SHUTDOWN command with options like NORMAL, TRANSACTIONAL, IMMEDIATE, or
ABORT.

 Example: SHUTDOWN IMMEDIATE;

Note: Be cautious with ABORT as it's the most aggressive shutdown and requires instance recovery on
startup.
Database Alert Log

The database alert log, also known as the alert file, is a crucial resource for monitoring and
troubleshooting your database. It provides a chronological summary of events and errors, making it
an essential tool for database administrators (DBAs). Here's what you need to know:

What's in the Alert Log:

1. Startup and Shutdown Information: The alert log records every instance of starting or shutting
down the database. This includes details of when and how these actions occurred.

2. Administrative Actions: It keeps a log of certain administrative actions, such as commands like
ALTER SYSTEM and ALTER DATABASE. These commands are essential for managing and
configuring the database.

3. Database Errors: The alert log is vigilant about capturing database errors, including internal
Oracle errors (e.g., ORA-600) and space-related errors (e.g., ORA-1542). These error messages
are invaluable for diagnosing and resolving issues.

4. Shared Server and Dispatcher Errors: It reports errors related to shared servers and
dispatchers. These are critical components in a multi-user database environment, and tracking
their issues is essential for performance optimization.

5. Materialized View Refresh Errors: If you're using materialized views, any errors that occur
during their refresh processes are logged in the alert log. This information helps you identify
and address issues with data replication.

6. Initialization Parameters: The alert log records the values of initialization parameters that
differ from their default settings at instance startup. This is essential for understanding the
configuration of your database.

Why it Matters:

 Diagnosis: The alert log is the first place a DBA should check when diagnosing database
problems. It provides a detailed history of events, making it easier to pinpoint issues.

 Troubleshooting: Whether it's a startup problem, a configuration change, or an error, the alert
log helps you troubleshoot and resolve issues efficiently.

 Performance Tuning: Monitoring shared servers, dispatchers, and initialization parameters

In summary, the database alert log is a treasure trove of information that DBAs rely on for
maintaining a healthy and well-functioning database system. It's your go-to resource for diagnosing
issues, tracking events, and ensuring the smooth operation of your database.