Databases | Data Modeling | Oracle Tutorial

1. Introduction to Oracle Databases

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data.

2. Creating an Oracle Database

Creating an Oracle database involves several steps, including initializing database parameters, creating necessary files, and setting up administrative accounts. Here is an example of how to create a new Oracle database using SQL commands:


                    CREATE DATABASE testdb

                    USER sys IDENTIFIED BY password

                    USER system IDENTIFIED BY password

                    LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M,

                    GROUP 2 ('/u01/app/oracle/oradata/testdb/redo02.log') SIZE 50M

                    DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 500M

                    SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M

                    DEFAULT TABLESPACE users

                    DATAFILE '/u01/app/oracle/oradata/testdb/users01.dbf' SIZE 50M

                    DEFAULT TEMPORARY TABLESPACE temp

                    TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 20M

                    UNDO TABLESPACE undotbs

                    DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf' SIZE 200M;

This command creates a new Oracle database named "testdb" with the necessary system files, temporary files, and undo tablespace.

3. Connecting to an Oracle Database

To connect to an Oracle database, you typically use Oracle SQL*Plus or other Oracle client tools. Here is an example of how to connect to a database using SQL*Plus:


                    sqlplus sys/password@//localhost:1521/testdb AS SYSDBA

This command connects to the "testdb" database as the SYSDBA user.

4. Managing Oracle Databases

Managing an Oracle database involves various tasks, such as starting and stopping the database, managing storage, and monitoring performance. Below are some examples of common management tasks.

4.1. Starting and Stopping the Database

To start the database, use the following command:


                    STARTUP;

To stop the database, use the following command:


                    SHUTDOWN IMMEDIATE;

4.2. Managing Storage

To add a new datafile to an existing tablespace:


                    ALTER TABLESPACE users

                    ADD DATAFILE '/u01/app/oracle/oradata/testdb/users02.dbf' SIZE 50M;

4.3. Monitoring Performance

To monitor the performance of the database, you can use Oracle's built-in views and tools. For example, to check the current sessions:


                    SELECT sid, serial#, username, status FROM v$session;

5. Backing Up and Recovering Databases

Backing up and recovering databases is a critical aspect of database management. Oracle provides RMAN (Recovery Manager) to manage backups and recovery. Here is an example of how to back up a database using RMAN:


                    RMAN TARGET /

                    RMAN> BACKUP DATABASE;

To recover a database, you can use the following RMAN commands:


                    RMAN TARGET /

                    RMAN> RESTORE DATABASE;

                    RMAN> RECOVER DATABASE;

6. Example: Working with Databases

Let's walk through an example of creating, connecting, and managing an Oracle database.

6.1. Creating a Database


                    CREATE DATABASE exampledb

                    USER sys IDENTIFIED BY password

                    USER system IDENTIFIED BY password

                    LOGFILE GROUP 1 ('/u01/app/oracle/oradata/exampledb/redo01.log') SIZE 50M,

                    GROUP 2 ('/u01/app/oracle/oradata/exampledb/redo02.log') SIZE 50M

                    DATAFILE '/u01/app/oracle/oradata/exampledb/system01.dbf' SIZE 500M

                    SYSAUX DATAFILE '/u01/app/oracle/oradata/exampledb/sysaux01.dbf' SIZE 100M

                    DEFAULT TABLESPACE users

                    DATAFILE '/u01/app/oracle/oradata/exampledb/users01.dbf' SIZE 50M

                    DEFAULT TEMPORARY TABLESPACE temp

                    TEMPFILE '/u01/app/oracle/oradata/exampledb/temp01.dbf' SIZE 20M

                    UNDO TABLESPACE undotbs

                    DATAFILE '/u01/app/oracle/oradata/exampledb/undotbs01.dbf' SIZE 200M;

6.2. Connecting to the Database


                    sqlplus sys/password@//localhost:1521/exampledb AS SYSDBA

6.3. Managing the Database

6.3.1. Starting the Database


                    STARTUP;

6.3.2. Stopping the Database


                    SHUTDOWN IMMEDIATE;

6.3.3. Adding a Datafile


                    ALTER TABLESPACE users

                    ADD DATAFILE '/u01/app/oracle/oradata/exampledb/users02.dbf' SIZE 50M;

6.3.4. Monitoring Sessions


                                SELECT sid, serial#, username, status FROM v$session;

6.4. Backing Up and Recovering the Database

6.4.1. Backing Up the Database


                                RMAN TARGET /

                                RMAN> BACKUP DATABASE;

6.4.2. Recovering the Database


                                RMAN TARGET /

                                RMAN> RESTORE DATABASE;

                                RMAN> RECOVER DATABASE;

7. Conclusion

In this tutorial, you've learned about databases in Oracle, including creating a database, connecting to it, managing it, and performing backup and recovery operations. Databases are fundamental to storing and managing data efficiently in Oracle, and mastering these basic concepts will help you become proficient in Oracle database management.

Understanding Databases in Oracle