Create database using DBCA (database configuration assistant).
DBCA (database configuration assistant) is a utility provided by oracle to create and configure a database. Moreover DBCA is an oracle recommended way for creating and configuring a database on your system.
You can find this database configuration assistant DBCA utility with every regular oracle database product like standard and enterprise version.
BUT if you are using Oracle express edition then I am sorry guys you will not find this utility there as Oracle XE can only have maximum one database which comes preinstalled.
There are two ways to open Database configuration assistant DBCA.
Before that I am assuming that you have installed oracle database 10g or 11g on your system. It doesn’t matter whether you have 10g or 11g. Steps for creating database using DBCA are same for both. If you haven’t installed it yet then please watch my tutorial 1 on how to install oracle database 11g enterprise edition.
Note: Always run DBCA with Administrative privileges
1st way to launch DBCA utility
If you are using windows system then you can launch DBCA utility from start menu. Path for DBCA utility will be
Start >> Programs >> Oracle – OraDb11g_home1 >> Configuration and Migration Tools >> Database Configuration Assistant
To launch this DBCA utility go to start >> Programs >> Here you should have a Folder by the name of Oracle followed by Home name. Here is mine Oracle – OraDb11g_home1click and expand it.
Now go to Configuration and Migration Tools, and then Database Configuration Assistant. Right click and select run as administrator.
Or you can simply search for DBCA in your start menu. Then Right click and select run as administrator.
This is the first way, now let’s take a look at the second way to open DBCA utility
2nd way to launch DBCA utility
Just go to your command prompt if on windows system or Terminal if using Linux machine. And here write DBCA and Hit enter. This will open up the DBCA utility for you.
But I highly recommend you to run DBCA with Administrator privileges otherwise you may get access denied directory errors.
Now you must have a DBCA windows open on your system with a welcome screen along with lots of text describing what you can do with this utility.
There are total 11 steps which you have to go thorough to create an oracle database.
Before starting let me be clear on one thing – your DBCA window’s screen may look different or you may have some extra options than mine. That depends upon the Oracle Database Software. So just don’t worry!
This is the first step out of 11 for creating the database. Here you can see all the operations which you can perform with database configuration assistant DBCA. You can create a new database or configure the existing database, delete a database and manage template if you have any.
As we are creating a new database thus we will choose the first option here which is “Create a Database”.
Step 2: Database Template
Here in step 2 of DBCA we have to choose the template of our database which means that we have to choose what kind of database we want to create.
Here we have 3 options:
- First one is general purpose of Transaction processing. You must have come across this kind of database. These are the often used database for example Retail billing system general purpose Database and ATM Automatic teller machine. Both of which are very good examples of transaction processing database.
- The 2nd one is Custom Database. Pretty similar to the general purpose one but it’s a bit more flexible. Here you can chose individual component of the database which you want to use such as oracle extension for dot net, Oracle OLAP, text, lab Security and enterprise manager repository etc. You can even assign your desired table space to all these database components.
- The third one is Data warehouse. Data warehouse is also a type of relational database which is basically used for query and analysis rather than for transaction processing. So if you want to create a data warehouse then choose this one.
Since we are creating a general purpose database thus we will select the first option which is “General purpose or Transaction processing”.
Step 3: Database Identification
Here in this step you have to provide the global database name for your database. Global database name is the full name of your database. This helps you in uniquely identifying your database. This should be in the form of “Database_name . domain name”. This is also an oracle recommended format for global database name. But you can give whatever name you want.
For this demonstration I will write “youtube.rebellionrider.com” Here YouTube is our database name and rebellionrider.com is the domain name.
In the second field you have to specify the SID which stands for system identifier. As you can see that by default it takes database name from Global database name field which is YouTube in our case. SID also helps in uniquely identifying the instance that runs the database.
Step 4: Management Options
Here in this step you will choose whether you want to configure enterprise manager or not. Oracle enterprise manager is a web-based management tool for individual database, and central management tools for managing your entire Oracle environment.
Select Configure Enterprise Manager to use Enterprise Manager. Then, select one option:
- If the Oracle Management Agent is installed on your host computer, then you can choose central management by selecting Register with Grid Control for centralized management and then selecting the Management Service.
- To manage your database locally, select Configure Database Control for local management. You can then optionally configure e-mail notification of database alerts or a daily backup of the database.
Along with enterprise manager you can also configure your alerts. Supply your email server details and this will send you all the alerts on your emails.
Moreover you can even configure daily disk back up if you want.
But make sure that automatic maintenance tasks are enabled.
Step 5: Database Credentials
Here you have to configure the security for all these accounts. These are few administratively mandatory and by default configure accounts.
Here you have 2 options either you can configure separate passwords for each of them or you can assign same password to all of them.
Step 6: Database File Locations
Here in this step you have to specify the type of storage you would like your database to use.
In the storage type we have 2 options first is file system and the second is ASM automatic storage management. If you have configured ASM then I recommend you to choose the same otherwise go for file system.
After choosing Storage type you have to specify the storage location.
For storage location we have 3 options:
- First one is Use Database File Locations from Template. By using this option you don’t need to do anything instead DBCA will create an ORACLE HOME directory for you.
- Second one is Use Common Location for All Database Files Here you can choose your own directory. DBCA will set the chosen directory as your ORACLE HOME and then all the database files will get created at this location.
- Last and third one is Use Oracle Managed Files. This option instructs Oracle Database to directly manage operating system files. It provides you with a flexible way for multiplexing online redo log and control file. This is recommended for security purposes.
Step 7: Recovery Configuration
Must Watch Suggestion: What is archive log mode and how to enable it?
I highly recommend you to enable Archiving. And with archiving it becomes mandatory to set at least one archive log destination thus here you have to enable flashback recovery area.
Specify flash Recovery Area— You can go for this option in order to specify a backup and recovery area as well as its directory location and size. You can even use variables to identify standard locations.
The flash recovery area size should at least be twice the database size.
Enable Archiving—Select this option to enable the archiving of database online redo log files, which can be used to recover a database. Selecting this option is the same as enabling Archive Log Mode in Oracle Enterprise Manager Database Control or running the database in ARCHIVELOG mode.
Step 8: Database Content.
Here in this step tick the sample schema check box if you want to install sample schema such as HR, scott OE and many more
Step 9: Initialization Parameter
With the help of this step you can change the default initialization parameter settings that include Memory, Sizing, Character set and Connection mode.
- Memory: You can use this window to set the initialization parameters that control how the database manages its memory. You can choose among the below mentioned methods for memory management:
- Typical — Very little configuration is required in this method. It allocates memory as a percentage of total overall physical system memory. Select Typical and enter a percentage value. You can click Show Memory Distribution to see how much memory DBCA assigns to the System Global Area (SGA) and the aggregate Program Global Area (PGA).
- Custom — This method usually requires more configuration than the previously discussed Typical option, but gives you more control over how the database instance uses system memory. You can directly specify memory sizes for the SGA and aggregate PGA and their substructures, such as the shared pool and buffer cache.
- Automatic Shared Memory Management – This allows you to allocate specific amounts of memory to the SGA and aggregate PGA. With this setting, automatic shared memory management is enabled for the SGA, and memory is allocated to the individual PGAs as needed.
- Manual Shared Memory Management – It allows you to enter specific values for each SGA component and the aggregate PGA. This disables automatic shared memory management and enables you to determine how the SGA memory is distributed among the SGA memory components.
- Sizing: This tab enables you to specify the smallest block size and the maximum number of operating system user processes that can simultaneously connect to the database.
- Character Sets: You can utilize this tab to define the character sets used by your database. Character sets are the encoding schemes used to display characters on your computer screen. They determine what languages can be represented in the database.
- Connection Mode: With the help of this window you can select the database mode. You have following mode options which can run the database:
- Dedicated Server Mode— It allows a dedicated server process for each user process. You can opt for this option when the number of total clients is expected to be small, for example, 50 or fewer. You might also choose this option when database clients typically make persistent, long-running requests to the database. By default, the database is configured for dedicated server processes.
- Shared Server Mode— It allows several client connections to share a database-allocated pool of resources. You can use this mode in such configurations where the client load is expected to cause a strain on memory and other system resources. If you choose shared server mode, then you must indicate the number of server processes you want to create when a database instance is started.
Step 10: Database Storage
At this stage you are presented with a navigation tree display that contains the storage structure of your database including control files, data files, online redo log groups, and so forth. Here you have the option of making changes in case you are not satisfied with the storage structure or parameters.
Step 11: Creation Options
This is the last step where you have to tell DBC what you want to do? Either you want to create a database or save all these settings as template for future use or you want to generate the database creation script.
You can also perform these three steps altogether.
Now hit finish
This will show you your database creation summary. Press OK here
That’s it guys. Hope you found this useful. Kindly please share this article on your social media to help me reach more people. Thanks & Have a great day!