How to Create Database in Oracle in 4 Ways Step by Step?

Oracle database is a multi-model database management system created and offered by Oracle for running online transaction processing, data warehousing, and mixed database workloads. This blog will elaborate on how to create an Oracle database in 4 ways step by step. The creation methods involve command lines and Oracle tools and are applied to Oracle 12c, 19c, 11g, 10g, and higher.

Prerequisites:

The following prerequisites are required for creating a new Oracle database:

• The operating system privileges.

• Enough memory to launch an Oracle instance.

• Enough disk space on the Oracle-running PC for the database.

Method 1: Create an Oracle Database with CREATE DATABASE Statement

1.   Set a scene that includes PL/SQL package installation and create views on data dictionary tables for single-instance installations only. (A single-instance database is accessed by one Oracle instance but still runs multiple instances on a host.)

2.    Configure the Instance Identifier (SID) manually on UNIX or Linux, for Windows only if you install the database but not create one.

For Windows:

set ORACLE_SID=newdb

For Linux and UNIX:

Bourne, Bash, or Korn shell:

ORACLE_SID=newdb

export ORACLE_SID

C shell:

setenv ORACLE_SID newdb

3.  Set the required environment.

Set ORACLE_HOME on most platforms and recommend including ORACLE_HOME/bin directory in the Path variable.

4.   Create an initialization parameter file for the new database.

The initialization parameter file (either a text file/PFILE or a binary file/server parameter file) should be stored in the default location for convenience.

ORACLE_HOME/dbs may contain the sample init.ora file, which could be used to edit. 

A server parameter file is preferred for its consistency, which could be manually created from the edited text initialization file or automatically from DBCA (Database Configuration Assistant). 

The following SQL*Plus command creates a server parameter file (SPFILE) from PFILE and writes it to the default location. Offer your file path if you are not using the default location or name.

CREATE SPFILE FROM PFILE;

5.  Create an instance on Windows.

Create an instance on Windows if there isn’t one:

oradim -NEW -SID sid-parameters -STARTMODE MANUAL -PFILE text-initialization-file-full-path

6.  Connect to the Oracle instance.

Launch SQL*Plus to connect to the instance with SYSDBA administrative privilege:

Authenticate with a password file and enter the SYS password when prompted:

$ sqlplus /nolog

SQL> CONNECT SYS AS SYSDBA

Authenticate with an OS file:

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL*Plus outputs Connected to an idle instance.

7.   Run the idle instance.

This step is performed only during database creation or maintenance, which doesn’t connect to the database.

SQL> STARTUP NOMOUNT

8.   Create a database in Oracle.

CREATE DATABASE newdb

   USER SYS IDENTIFIED BY sys_password

   USER SYSTEM IDENTIFIED BY system_password

   LOGFILE GROUP 1 ('redo-log-file-path','/ redo-log-file-path ') SIZE value-M BLOCKSIZE value,

   MAXLOGFILES value-affects control file size and data files opened in the DB

   MAXLOGMEMBERS value

   MAXLOGHISTORY value

   MAXDATAFILES value

   CHARACTER SET AL32UTF8 (datastore)

   NATIONAL CHARACTER SET AL16UTF16 (datastore in columns)

   EXTENT MANAGEMENT LOCAL

   DATAFILE 'system-tablespace-system.dbf ' SIZE value-M REUSE

   SYSAUX DATAFILE ''sysaux-tablespace-sysaux.dbf' SIZE value-M REUSE

   DEFAULT TABLESPACE users

      DATAFILE 'location-users.dbf'

      SIZE value-M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE name

      TEMPFILE 'location-temp.dbf'

      SIZE value-M REUSE

   UNDO TABLESPACE name

      DATAFILE 'location-undotbs.dbf'

      SIZE value-M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Method 2: Create an Oracle Database with DBCA

1. Start Database Configuration Assistant and select Create a database on the dashboard. Click Next. 

2. On the Creation Mode page, select Advanced configuration, and click Next.

3. On the Deployment Type page, choose either General Purpose or Transaction Processing option. Click Next.

4. In the Storage Option section, choose Use template for database storage attributes and click Next.

5. Specify Fast Recovery Option configurations, and click Next.

6. Configure Network Configuration for a new listener creation. Hit Next.

7. Set up Data Vault Option optionally.

8. On the Configuration Options page Memory selection, opt for Automatic Shared Memory Management and specify its SGA and PGA sizes. Click Next.

   Specify the maximum number of OS user processes that can be simultaneously connected to the Oracle DB on Sizing tab.

   In Character sets section, choose Use Unicode (AL32UTF8) and AL16UTF16 as National character set.

   Select Dedicated server mode on Connection mode tab.

   Check the Add sample schemas to the database on the last tab, and click Next.

9. On the Management Options page, choose Configure Enterprise Manager (EM) express and enter the port. Click Next.

10. Opt for Use the same administrative password for all accounts and input user password on User Credentials. Click Next.

11. On the Creation Option page, specify the database creation. You can build a DB creation script for repeating the whole creation process, save the new DB as a template, and specify the scripts to run when the DB is formed. Click Next.

12. Click Finish on the Summary page. You will be notified once the process is over. Unlock database accounts by clicking Password Management…. Click OK.

Method 3: From an existing database

You could generate a CREATE DATABASE script from an existing Oracle database to speed everything up.

1. Launch DBCA, choose Manage Templates and click Next.

2. On the Template Management page, input the name and location, and click Next.

3. On the Create Template Option page, choose Create template from an existing database, choose the Oracle DB, enter user credentials, and click Next.

4. On the File Location Structure section, choose either to maintain the file locations or convert them to use OFA structure.

5. Review all the settings on the Summary page and hit Finish. Wait for it to generate and click Close.

Method 4: Create an Oracle Database with the Oracle SQL Developer

1. Download the Oracle SQL Developer, extract files from the downloaded zip, and run SQL Developer.

2. On the Start Page under the Connections, find the green plus sign, click the down arrow, and choose New Database Connection…

3. Configure the database on the New/Select Database Connection window. Enter your name, username, password, and more information. Click Test, and you will see Status: Success in the bottom left, after that, you could hit Connect. The new database is located in the Oracle Connections list.

Oracle Database full protection with Vinchi Backup & Recovery

Database backup and recovery are very essential for organizations with data stored in such media. Take Oracle database backup with RMAN is a common way, and you could also Restore the database from RMAN backups. However, they are very tricky for beginners and non-techies, thus the simple database backup and recovery using a third-party solution are favored.

Vinchin Backup & Recovery facilitates data backup and recovery operations for Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, MariaDB, Linux & Windows Servers, and NAS with clear and easy options to automate the process.

1.  Download the 60-day full-featured free trial.

 2.  Navigate to Database Backup> Backup> choose Oracle from the installed database agents and select the backup source.

3.  Specify target node and subordinate storage from dropdown lists for the destination.

4.  Configure backup strategies here.

Select the backup mode as Backup as Scheduled or Once-off Backup. Customize your backup options and schedules.

Enable data reduction technologies to save at least 50% of storage space and keep the data per day or restore point.

Turn on encrypted transmission to secure the path during backup.

Specify archivelog backup days for data rollback, and delete unnecessary logs for storage. You could also enable Oracle compression for further database compression and set multithread to transfer data faster.

And lastly, review all and hit Submit.

Conclusion

This post goes into detail about the 4 different ways using CREATE DATABASE Statement, DBCA, the existing database template, and Oracle SQL Developer to establish an Oracle database.

This is just the first step, and the subsequent database protection weighs heavily against further data security. Use Vinchin Backup & Recovery for overall convenient and instant recovery to protect your database completely.