Default Temporary Tablespace Creation

The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement specifies that a temporary tablespace is to be created at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace. Users can be explicitly assigned a default temporary tablespace in the CREATE USER statement. But, if no temporary tablespace is specified, they default to using the SYSTEM tablespace. It is not good practice to store temporary data in the SYSTEM tablespace. To avoid this...

Read More

Creation Of Undo Tablespace By Using Automatic Undo Management

Oracle recommends that instead of using rollback segments in your database, you use an undo tablespace. This requires the use of a different set of initialization parameters, and optionally, the inclusion of the UNDO TABLESPACE clause in your CREATE DATABASE statement. You must include the following initialization parameter if you want to operate your database in automatic undo management mode: UNDO_MANAGEMENT=AUTO In this mode, rollback information, referred to as undo, is stored in an undo tablespace rather than rollback segments and is managed...

Read More

How To Protect Your database

The clauses of the CREATE DATABASE statement used for specifying the passwords for users SYS and SYSTEM are: - USER SYS IDENTIFIED BY password - USER SYSTEM IDENTIFIED BY password If not specified, these users are assigned the default passwords change_on_install and manager, respectively. A record is written to the alert file indicating that the default passwords were used. To protect your database, you should change these passwords using the ALTER USER statement after database creation...

Read More

Oracle Database Creation Manually

When the database is created manually, there is complete control over the process. Tablespaces can be added or reduced and the flow of the creation process can be managed. Here are the steps to be followed to create a database manually. Make SID:-Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to avoid confusion with other Oracle instances that you may create later and run concurrently on your system. The value of the DB_NAME initialization parameter...

Read More

DBCA's Advantage

These are a few of the advantages of using DBCA:You can use its wizards to guide you through a selection of options providing an easy means of creating and tailoring your database. It allows you to provide varying levels of detail. You can provide a minimum of input and allow Oracle to make decisions for you, eliminating the need to spend time deciding how best to set parameters or structure the database. Optionally, it allows you to be very specific about parameter settings and file allocations.  It builds efficient and effective databases...

Read More

Step To Create Oracle Database Using DBCA

If you choose to install software only and later create a database, or if you want to create additional databases using the release software that you just installed, you can do so by using the Database Configuration Assistant (DBCA). Follow the steps below to create a database: - Log onto your computer as a member of the administrative group that is authorized to install Oracle software and create and manage the database. - Launch the DBCA on a Windows operating system as follows :                          ...

Read More

How to create an Oracle Database

Creating a database includes the following operations:Creating information structures, including the data dictionary, that Oracle requires to access and use the database  Creating and initializing the control files and redo log files for the database  Creating new datafiles or erasing data that existed in previous datafiles You use the CREATE DATABASE statement to perform these operations, but other actions are necessary before you have an operational database. A few of these actions are creating users and temporary tablespaces, building...

Read More

Creation Prerequisites

To create a new database, the following prerequisites must be met:There is sufficient memory available to start the Oracle instance.  There is sufficient disk storage space for the planned database on the computer that executes Oracle.  The desired Oracle software is installed.  You have the operating system privileges associated with a fully operational database administrator. You must be specially authenticated by your operating system or through a password file, allowing you to start up and shut down an instance before the database...

Read More

Planning Database File Locations

Plan how to protect the database, including the online redo log files, control files, data files, archived redo log files, and provide a backup strategy. Control Files For the sake of safety, you should create at least two control files on two different disks. Online Redo Log Files The online redo log files of a database should consist of multiplexed groups of online redo log files. A group of log files consists of identical copies, which should be located on different disks. To distinguish between groups and their members, use a name such as log0101.log...

Read More

Creation Of Oracle Database

Database creation is a task that prepares several operating system files and is needed only once no matter how many data files the database has. This is a very important task, because you must decide on database settings.Depending on the operating system, a database may have been created automatically as part of the installation. You can use this initial database, or you can create a new one. The following topics can help prepare you for database creation:Planning Database File Locations  Creation Prerequisites How to create an Oracle Da...

Read More

Abort Shutdown Of Oracle Database

If the normal and immediate shutdown options do not work, you can abort the current database instance. The shutdown abort command is pretty much a guaranteed way to get your database to shutdown. It’s a “hard crash” of the database, and this can result in a longer time to start the database back up. Still, you can’t really hurt the database using the shutdown abort command, and during your DBA years you will find more than a few occasions to use the shutdown abort command. Aborting an instance proceeds with the following conditions:Current SQL...

Read More

Transactional Shutdown Of Oracle Database

A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions No new connections are allowed  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.  When all transactions have finished, a shutdown immediately occurs.  The next startup does not require an instance recovery.  The next startup of the database will not require any instance recovery procedures. Related...

Read More

Immediate Shutdown Of Oracle Database

Immediate database shutdown proceeds with the following conditions:Current SQL statements being processed by Oracle are not completed.  The Oracle server does not wait for users currently connected to the database to disconnect.  Oracle rolls back active transactions and disconnects all connected users.  Oracle closes and dismounts the database before shutting down the instance.  The next startup does not require an instance recovery.  The next startup of the database will not require any instance recovery procedures  Use...

Read More

Normal Shutdown Of Oracle Database

A normal shutdown of an Oracle database is actually rarely used. This is because the normal shutdown waits for everyone to complete their work. When a normal shutdown occurs, the database is closed in a normal manner, and all changes made in the database are flushed to the database datafiles. SQL> shutdown When you execute a shutdown, Oracle will flush all the changes in memory out to the database datafiles. This makes database startup quicker because the database is in a consistent state. Related Post:Immediate Shutdown  Transactional...

Read More

Shutting Down Oracle Database

To initiate database shutdown, use the SQL*Plus SHUTDOWN command. Control is not returned to the session that initiates a database shutdown until shutdown is complete. The shutdown command comes in many forms.  Oracle has three shutdown modes: * Normal (default) - waits for in-flight work to complete * Immediate - terminates all sessions and does a rollback on all * Abort - aborts all sessions, leaving current DML in need of rollback, de-allocates the SGA and terminates the background processes. The "normal" and "immediate" modes can take...

Read More

Startup Database At Restricted Mode

You can also start the database in restricted mode. Restricted mode will only allow users with special privileges (we will discuss user privileges in a later chapter) to access the database (typically DBA’s), even though the database is technically open. We use the startup restrict command to open the database in restricted mode as seen in this example. SQL> startup restrict You can take the database in and out of restricted mode with the alter database command as seen in this example: SQL> alter system enable restricted session; -- Take...

Read More

Startup Oracle Database at Open Stage

When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent.Oracle opens the online datafiles and online redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you  reopen the database.To open the database, you can just use the startup command SQL> startup mount If the database is mounted,...

Read More

Startup Oracle Database at Mount Stage

You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the database datafiles.In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified, the database is ready to be opened.For example, the database must be...

Read More

Startup Oracle Database at NoMount Stage

Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform data access operations. The following command starts an instance, reads the initialization parameters from the default location, and then mounts and opens the database. (You can optionally specify a PFILE clause.) STARTUP You can start an instance without mounting a database. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT clause: STARTUP...

Read More

Starting Up Oracle Database

One of the most common jobs of the database administrator is to startup or shutdown the Oracle database. To start an instance, the database must read the initialization parameters or SPFILE. When you issue the SQL*Plus STARTUP command, the database attempts to read the initialization parameters from an SPFILE in a platform-specific default location. If it finds no SPFILE, it searches for a text initialization parameter file. In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames...

Read More

Authentication Method for Database Administrators

Database Administrators can authenticate through the database data dictionary, (using an account password ) like other users. In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPER privilege:Operating system (OS) authentication A password file Strong authentication with a network-based authentication service These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. The remainder...

Read More

Protecting Your Database

The clauses of the CREATE DATABASE statement used for specifying the passwords for users SYS and SYSTEM are:·       USER SYS IDENTIFIED BY password       USER SYSTEM IDENTIFIED BY password If you omit these clauses, these users are assigned the default passwords change_on_install and manager, respectively. A record is written to the alert log indicating that the default passwords were used. To protect your database, you must change these passwords using the ALTER USER statement immediately after...

Read More

Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.The SYSDBA and SYSOPER privileges can also be thought of as types of connections that...

Read More