Create Control File

when you issue the create database statement at the time of the database creation at that time control file of an oracle database is created. The name of the control file is defined by the CONTROL_FILES parameter in the pfile initialization parameter file. Example of a control file initialization parameter is as follows:

Control_files '/opt/luck/con01.ctl',

              '/opt/luck/con02.ctl'

If files names currently exist at the time of database creation, you must specify the Control Reuse clause in the CREATE DATABASE statement. The size of the control file is influenced by the following keywords in the create database or create control commands:

• MAXLOGFILES
• MAXLOGMEMBERS
• MAXLOGHISTORY
• MAXDATAFILES
• MAXINSTANCES

Read More

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 problem, and to avoid the need to assign every user a default temporary tablespace at CREATE USER time, you can use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.

If you decide later to change the default temporary tablespace, or to create an initial one after database creation, you can do so. You do this by creating a new temporary tablespace (CREATE TEMPORARY TABLESPACE), then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new temporary default tablespace.

The following statement assigns a new default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;

The new default temporary tablespace must be an existing temporary tablespace. When using a locally managed SYSTEM tablespace, the new default temporary tablespace must also be locally managed.

You cannot drop a default temporary tablespace, but you can assign a new default temporary tablespace, then drop the former one. You are not allowed to change a default temporary tablespace to a permanent tablespace, nor can you take a default temporary tablespace offline.

Users can obtain the name of the current default temporary tablespace using the DATABASE_PROPERTIES view. The PROPERTY_NAME column contains the value "DEFAULT_TEMP_TABLESPACE" and the PROPERTY_VALUE column contains the default temporary tablespace name.

Related Post:-

• Creation Oracle Database Manually
• Creation Of Undo Tablespace By Using Automatic Undo Management

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 by Oracle. If you want to create and name a specific tablespace for the undo tablespace, you can include the UNDO TABLESPACE clause at database creation time. If you omit this clause, and automatic undo management is specified, Oracle creates a default undo tablespace named SYS_UNDOTBS


Related Post:-

• Create Oracle Database Manually 
• Oracle Database Configuration Assistant 

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 should match the SID setting. 

• Preparing  the Parameter File:- Modify the initSID.ora  by editing the parameters . Store your initialization parameter file in Oracle's default location, using the default name. That way, when you start your database, it is not necessary to specify the PFILE parameter because Oracle automatically looks in the default location for the initialization parameter file. For UNIX  $ORACLE_HOME/dbs is default location of parameter file and For Window the default location of parameter file is $ORACLE_HOME\database. The following is the initialization parameter file used to create new database.

         db_name =dbname

         shared_pool=52428800

         db_block_size= 8192

         Control_files=’/opt/db01/con.ctl’

         background_dump_dest=’/opt/db01/bdump’

         user_dump_dest=’/opt/db01/udump’

         undo_management=auto

         undo_tablespace=undo01

•  Connect to the instance:- connect oracle instance as SYSDBA

         Export_SID=dbname

               Sqlplus ‘/as sysdba’ 

• Start the Instance:- Start an instance at nomount stage . Use the STARTUP command with the NOMOUNT option. Initialization parameter file is stored in the default location, you are not required to specify the PFILE clause: SQL>startup nomount 
• Creating the Database:- after connecting the intance at nomount stage u execute create database statement in SQL prompt.

1. Create database dbname
2. Datafile ‘/opt/tt/data.dbf’ size 200m
3. Sysaux datafile’/opt/tt/sysaux.dbf’ size 150m
4. Logfile group1 ‘/opt/tt/log01.log’ size 10m
5.               group2 ‘/opt/tt/log02.dbf’ size 10m
6. defaulttemporary tablespace temp tempfile’/opt/tt/temp.dbf’ size 50m
7. undotablespace undo1 datafile ‘/opt/tt/undo.dbf’ size 50m
8. Max instance            4
9. MaxDatafiles         100
10. MaxLogMembers      6
11. MaxLogHistory      200 

•  Run necessary scripts to build views, synonyms, etc.

1. Catalog.sql – create the views of data dictionary table and the dynamic performance views.
2. Catproc.sql- establishes the usage of PL/SQL functionally and many of the Pl/SQL Oraclesupplied packages. 

          Catalog statement - @?/rdbms/admin/catalog.sql

                Catproc statement- @?/rdbms/admin/catproc.sql 

• Create SPfile:- The SPFILE must initially be created from an initialization parameter file. You must have the SYSDBA or SYSOPER role to execute the CREATE SPFILE statement.                                                                       Create spfile from pfile 
• Database Backup:- You should make a full backup of the database to ensure that you have a complete set of files from which to recover if a media failure occurs.

Related Post:-

• How To Protect Your Database

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 that take advantage of Oracle's new features 
• It uses Optimal Flexible Architecture (OFA), whereby database files and administrative files, including initialization files, follow standard naming and placement practices.

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 :                           Start > Programs > Oracle - home_name > Configuration and Migration Tools >DatabaseConfigurationAssistant

- The Welcome page appears. 

- Select Create a Database on the Operations window to begin an interview that enables you to configure and create a database.

- On the Database Templates page, select the type of database template to be used in creating the database. You can click Show Details to see the configuration for each type of database. Choose the template suited to the type of workload your database will support. If you are not sure, select the default General Purpose template.

- On the Database Identification page, enter the Global Database Name and SID. 

- The Management Options page appears. To use Enterprise Manager, select Configure the Database with Enterprise Manager. Select Use Database Control for Database Management to manage your database locally or select Use Grid Control for Database Management as appropriate to your configuration.

- Enter passwords for the administrative users.

- Select File System, Automatic Storage Management, or Raw Devices as appropriate to your environment.

- Specify the location for the creation of the datafiles. Choose one of the following:Use Database File Locations from Template, Use Common Location for All Database Files, or Use Oracle-Managed Files.

- Select Flash Recovery Area and specify a directory location and size. Select Enable Archiving to place your database in ARCHIVELOG mode.

- Click the Sample Schemas tab. Select Sample Schemas if you want to include the Sample Schemas (EXAMPLE) tablespace in your database. Click the Custom Scripts tab.

- Specify one or more SQL scripts to be run after your database is created if required in your environment. Otherwise, accept the default No scripts to run.

- The Memory page appears. Select Typical and enter a percentage value. Click the Sizing tab. - The Sizing page appears. Specify the smallest block size and the maximum number of operating system user processes that can simultaneously connect to the database. 

- The Character Set page appears. Select the character set for your database. Click the Connection Mode tab.

- The Connection Mode page appears. Select Dedicated Server or Shared Server as appropriate for your environment.

 - The Database Storage page appears. Accept the configuration or make changes as needed.

 - Select Create Database to create your database. You can also select Save as a Database Template to save your configuration. Click Finish.

 -  At the Confirmation window, confirm the options that will be installed and click OK.

 - Your database is now being created.

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 views of the data dictionary tables, and installing Oracle built-in packages.

An Oracle database can be created using:

• Oracle Database Configuration Assistant 
• The CREATE DATABASE command

Creating a database can be done either using the Oracle DatabaseConfiguration Assistant (DBCA) or by creating a SQL script using the CREATE DATABASE command. The DBCA is a graphical user interface that interacts with the Oracle Universal Installer, or can be used stand-alone, to simplify the creation of a database. The DBCA is Java-based and can be launched from any platform with a Java engine. During the installation of the Oracle Server, DBCA is launched by the Oracle Universal Installer and can automatically create a starter database for you. You have the option of using DBCA or not, and you also have the option to create a starter database. You also have the option to launch DBCA later as a standalone application to create a database. You can also migrate or upgrade an existing database if you are using a previous version of Oracle.

Related Post:

• Planning Database File Locations 
• Creation Prerequisites
• How to protect your database

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 is created or opened.

 All of these are discussed in the Oracle installation guide specific to your operating system. Additionally, the Oracle Universal Installer will guide you through your installation and provide help in setting up environment variables, directory structure, and authorizations.

Related Post:-

• Planning Database File Locations 
• How to create an Oracle 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 or log01a.log.

Data Files

Name data files by relating to the contents as the root of the name—for example, data files such as system01.dbf, temp01.dbf, and users01.dbf on Unix. Consider the characteristics of the data to be stored before determining the structure appropriate for your database, in order to:

    Minimize fragmentation

    Minimize disk contention

    Separate objects

To minimize fragmentation of the database, you should separate database objects with different life spans, such as application data and temporary data, into different tablespaces. To ensure well-balanced I/O loads, you should separate objects with competing I/O requirements, such as tables and indexes, into different tablespaces.

Read More

What is Oracle Managed Files (OMF)

Oracle Managed Files (OMF) directly manages file creation and deletion at the operating system level. Operations are specified in terms of database objects rather than filenames. Oracle internally uses standard file system interfaces to create and delete files as needed for the database structures such as tablespaces, online redo log files, and control files.

Administrators need to specify the file system directory to be used for a particular type of file. Thereafter, Oracle manages to create unique files when needed and also deletes files that are not needed. The advantages include: 

• Automatic cleanup of the filesystem when database objects are dropped. 
• Standardized naming of database files.
• Increased portability since file specifications are not needed. 
• Simplified creation of test systems on differing operating systems. 
• No unused files wasting disk space.

 The location of database files is defined using the DB_CREATE_FILE_DEST parameter. It is default file system directory location where Oracle creates data files or temp files when no file specification is given in the object creation statement.

For online redo log files and control files the default location of the file system directory is defined by db_create_online_log_dest_n. This initialization parameter can be used multiple times where n specifies a multiplexed copy of the online redo log or control file.

Note that the file system directory specified by either of these parameters must already exist. Oracle does not create it. The directory must also have permissions to allow Oracle to create the files in it. The default location is used whenever a location is not explicitly specified for the operation of creating a file. Oracle creates the filename, and a file thus created is an Oracle-managed file. The following example sets default directory to use when creating Oracle-managed files:

DB_CREATE_FILE_DEST = '/p1/oracle/oradata/LSH'

By default, this location would be applicable for data files, temp files, Online redo log files, and control files. However, the db_create_online_log_dest_n initialization parameter can be included in the initialization parameter file to identify the default location for the database server to create online redoes log files and control files. For example:

DB_CREATE_ONLINE_LOG_DEST_1 = '/p2/oracle/redo1/lsh1'

DB_CREATE_ONLINE_LOG_DEST_2 = '/u04/oracle/redo2/lsh2'

After the initialization parameters are set, the database can be created using the CREATE DATABASE command. If the command fails, any OMF files created are removed. The internally generated file names can be seen when the user selects from DBA_DATAFILES and V$LOGFILE.

Both DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_N can be modified dynamically with the ALTER SYSTEM SET command.

Read More