Oracle database administration is an expert task, which requires in-depth knowledge and high-end technical skills. As Oracle database server keeps on evolving and requires frequent updating, the company keeps on introducing new releases. In most of the typical technical administration scenarios, there may be multiple releases of the same product existing simultaneously. The major releases till date are:
- Oracle v2
- Oracle v3
- Oracle v4
- Oracle v5
- Oracle v6
- Oracle7
- Oracle8
- Oracle8i Release #1, #2, #3
- Oracle9i Release #1, #2
- Oracle 10g Release #1, #2
- Oracle 11g Release #1, #2
- Oracle 12c Release #1 etc.
- Oracle Database 12c Release #1
Creating an Oracle database using SQL commands
Next, we will explore how to create an Oracle database manually. For this, you have to first do a ground level planning about the database size needed, number of table spaced and the redo log files needed in the database etc. While doing database planning, it is not just the current need, but you also have to keep in mind the space it may occupy in the next couple of years too.
Next plan the layout of the underlying OS files which the database may comprise. Proper file distribution is vital in terms of database performance. There are various ways to distribute the I/O on using Oracle as database. Say for example, you can situate the data files in order to reduce contention and may also place redo log files on different disks or try striping.
First select the block size of the standard database you are creating. Remember, this is getting specified at the database creation by DB_BLOCK_SIZE parameter, which cannot be changed once the database gets created. For normal databases, the block size may be about 4K to 8K as standard. The best practice is to write down the specifications before proceeding with the database creation.
Here are we are creating a sample database named “my_abc_db”
The system identifier (SID) and DB name as:
SID=myabcdb
DB_NAME=myabcdb
TABLESPACES
We plan for 5 table spaces for this sample database with 1 data file in each table space.
LOGFILES
Here we plan to create 2 different log groups for the database;
CONTROL FILE
We may have one control file at the following destination:
/u01/oracle/oradata/myabc/control.ora
PARAMETER FILE
Next we will use the normal parameter file, which can be later switched to SPFile:
/u01/oracle/dbs/initmyabcdb.ora
NOTE: the parameter file name must be in init<sid>.ora format and for;
- Unix OS it should be in the ORACLE_HOME/dbs directory
- Windows OS should be in the ORACLE_HOME/database directory.
Let’s start with creating the Oracle database.
STEP 1:
Log in to the oracle account to make directories.
STEP 2:
Copy default template (init.ora) and set the parameters to set up parameter file
Next open parameter file and set the parameters as given below:
STEP 3:
Set ORACLE_SID environment and initiate the instance.
STEP 4:
Next, run the database command. For settings like language and characterset etc., Oracle may use the default values, which you can custom specify if needed. Command for creating database is given below:
Once it is rendered properly, you will get the message as “Database created”.
Create a new 12cR1 database on Linux
Oracle 12c is the latest release in Oracle DBA, which is now used widely for its cloud scale simplicity, compatibility, scalability, and security. In this blog, we will discuss the step by step outline of creation of Oracle 12c R1 no Linux. To make things easier, here we will use Database Creation Assistant (dbca) to create CDB (container database) with a PDB (pluggable database).
What you have to ensure before trying it is;
- There is already Oracle Linux 6 installed
- Did installation of the 12cR1 rdbms binaries
- Redirect display to desktop.
Creating a Listener
To do this, first you have to log in to the virtual host. Redirect the display to desktop. Next, call “netca” (Network configuration assistant) from the command line of Linux. Once you get the adequate screen, click next and:
- Select the “Add” option and click next.
- Select the name “LISTENER” from the list and then click next.
- Select the TCP protocol as default and click next.
- Select the standard port 1521 as default and click next.
- Opt “no” for the option of configuring another listener and click next.
The netca assistant will now complete the “Listener” configuration and then will ask you to exit.
Before the release of Oracle 12c, all the Oracle databases were primarily Non-CDB, which means the databases were “Non Multi Tenant Container Database”. However, with 12c, one can create the databases optionally as Non-CDB or CDB databases.
In the forthcoming steps, Database Configuration Assistant (dbca) is used to make the CDB with a PDB (pluggable database) in it.
First login to Oracle 6 Linux, access the virtual box image and next invoke the dbca from o/s prompt. Doing this, you can see the banner first and from the screen you get:
- Select “Create Database”, click next.
- Select “Advanced Mode”, click next.
- Select “General Purpose / Transaction Processing”, click next.
Reaching to this level, you will be asked to provide the “Global Database Name” and SID Name. You also need to activate the check box of “Create as Container Database” and also check the radio button of “Create a Container Database with one or More PDB’s”. Then:
- Give a “PDB Name”, click next.
- Check the box asking to configure “Em Express”, click next.
- Input your credentials as Administrators and click next.
- Select the default “LISTENER”, click next.
You can further choose the “Use Common Location for all database files” and provide the location for the data files. Next uncheck the “Specify Fast Recovery Area” (this must be specific if you are setting up a real database). Click next to:
- Select install “Sample Schema’s”, click next.
- Select to use the “Automatic Memory Management”.
- Click “Character Sets” and select “AL32UTF8”, click next.
- Check boxes to “Create Database”, “Save as Database Template” to reuse in future, and “Generate Database Creation Scripts”, click next.
Next, click on finish for the database installation process to start running. Once the installation is completed uneventfully, then dbca prompts you to exit and you have a 12cR1 database running.
Author Bio: Sujain Thomas is an experienced Oracle DBA specialist who is an active member in the online tech discussion. She is an avid blogger who used to release a lot of technical administration ‘how to’ articles.
