Oracle DBA Tutorial

A Database is the structured collection of organized information that can be accessed, modified, or analyzed efficiently. If we talk about the simple software applications like Word, Excel or Spreadsheets then they could not handle voluminous data efficiently. Here, we need a secure storage system where data can be stored or retrieved quickly whenever required.

To make this approach feasible, Oracle discovered Relational Database Management system (RDBMS) to store, retrieve or modify information more efficiently and securely. The RDBMS system delivers high performance and maximized data concurrency that boosts up the overall productivity of the multi-users database environment. It can be termed as RDMS or simple Oracle Databases. Oracle DBAs (Database Administrators) use specialized software programs to store or organize information in a mashed-up way.

Key Responsibilities of Oracle DBA

The key responsibilities of an Oracle DBA include:

• Data collection
• Planning
• Data migration
• Database designing
• Installations
• Configuration
• Performance monitoring
• Security mechanisms
• Data recovery techniques

Skills Required for an Oracle DBA

Skills Required for an Oracle DBA include:

• Database Designing
• SQL Queries
• Client-Server Architecture
• Database Modeling
• RDMS Basics
• Operating Systems
• Routine Maintenance
• Database Recovery
• Data Backup

Oracle Database

• One of the essential tasks of the Oracle Database is to store data. The following section briefly describes the physical and logical storage structure of an Oracle Database.

Physical storage structures

Let us have a quick look at the physical files that are used by Oracle Database to store voluminous data.

Data Files: These are the special database files that contain data required to initiate the database engine.

Parameter Files: These are the files that reside on the machine only and Oracle database server executes on parameter files only.

Redo Log Files: These log files contain modified information or the changes we made to the databases.

Control Files: This is the metadata or storehouse for the database used by the Oracle engine.

Logical Storage Structures

Oracle Database uses a logical storage structure for fine-grained control of disk space usage. The following are logical storage structures in an Oracle Database:

• Data blocks: a data block corresponds to a number of bytes on the disk. Oracle stores data in data blocks. Data blocks are also referred to as logical blocks, Oracle blocks or pages.
• Extents: An extent is a specific number of logically contiguous data blocks used to store the particular type of information.
• Segments: a segment is a set of extents allocated for storing database objects, e.g., a table or an index.
• Tablespaces: a database is divided into logical storage units called tablespaces. A tablespace is a logical container for a segment. Each tablespace consists of at least one data file.

Basic Terminologies in Oracle Database Administration

In this section, we will discuss all important terms that are necessary to understand before you start working on the Oracle database. This section covers almost all terms and parameters that are used frequently in Oracle Databases.

Shared Memory:

This is sometimes referred to as the Random-Access Memory (RAM) that stores a handful of entries to facilitate the application access. There are two types of memory management techniques in Oracle Database Administration. These are two type of shared memory

• Manual Memory Management
• Auto Memory Management

System Support Infrastructure: 

These are the mix of foreground and background processes that facilitate application interaction with the databases. One system support process starts automatically as soon as one Oracle instance is initiated. Each process plays an important role in managing interaction between an application and the data.

Operating System Files: 

These are the suits of files that play a significant role individually as soon as the database runs.

Instance: 

This is an important part of memory structure that can be defined as the background process to retrieve data from the database.

Process: 

Process is a running instance that can be named as Job or task as well in the Oracle database administration. These are divided into two major categories i.e. Oracle Processes or User Processes.

Buffer Cache: 

This is a small storehouse that contains frequently used or customized data. This cache is popular to optimize the overall performance of memory and they are connected to the database as well for quick access.

Shared Pool: 

This pool contains data that need to be shared with the users.

Large Pool: 

As the name suggests, this is an optimal place to store the large processes with robust options like Data Recovery and backups.

Redo Log Buffer:

This is a backup storehouse that contains the modified data and helps to recover instances or processes. This is an important part of Oracle database 12C architecture that works amazingly in case system failure.

Locks: 

Locks are required to control the simultaneous

Database Objects Learning

Objects in the Oracle database are used to perform a variety of functions. Here, we will discuss the common objects that can be used in our day to day environment while working with Oracle databases.

1)Tables – Organized Data Storage

Tables are the common objects that are used as an integral part of the database. Once a table is created, data should be stored by following the basic rules like data types, values etc. A table compliance of all important rules is always easy to access and modify with simple SQL commands.

2) Views – Limited access to Data

If the database administrator wants to limit the access of data then the view concept comes into the picture. Here, different views of data can be defined based on users and Company requirements.

Take an example, where you want to share only a limited amount of information with another organization then you need to mask the rest of the information with proper SQL commands.

You are free to list the important modules only that you want to make visible in front of others. The modules that are not listed will not display and it will be hidden from the users. Views were introduced to enforce a flavor of security and it is frequently used worldwide.

3) Synonyms – Referring objects from other databases

It allows users to refer to objects that are stored somewhere else in the same or other databases. These objects are handy objects that can be used with alternative names in the table and you just need to set a pointer to the real table.

This is a memory management technique where same objects can be reused and it optimizes the overall storage space for database objects. The concept has been used by the database programmers since decades to increase the throughput of the application code.

4) Stored Objects – Re-usability of Code

These objects promote the encapsulation of code that could run either implicitly or explicitly to perform some predetermined task. The best part is that stored objects can be reused again and again whenever some action is required. It ensures consistent performance and secure processing together. They can be further divided into procedures, functions and the packages to declare constants, variables and the cursors for exceptional handling.

Installing Oracle Database

• To install Oracle database on your computer, you need to download the installer from the download page of Oracle website.
• After having the installation files which are in ZIP format, you need to extract them into a specific folder on your computer.
• The following picture shows the structure of the folder of the Oracle installation files after extraction.

• Now you need to double-click the setup.exe file to start the installation process. There will be 9 steps which mostly automatically execute.

Steps

Step 1. The installer asks you to provide your email address to get the latest security issues and updates. You can ignore it by clicking the Next button

Because I didn’t provide the email address, the Oracle database installer confirms it, you just need to click the No button to continue.

Step 2. In step 2, Oracle installer asks you whether you want to create and configure a database, install database software only or just upgrade an existing database. Because you install the Oracle database for the first time, choose option 1 and click the Next button.

Step 3. The installer allows you to choose the system class. Because you install Oracle on your computer, not a server, therefore, you choose the first option: desktop class and click the Next button.

Step 4.  This step allows you to specify the Windows user account to install and configure Oracle Home for enhanced security. Choose the third option: “Use Windows Built-in Account”.

Step 5. in this step you can (1) choose the folder on which Oracle database will be installed, (2) Global database name and password, (3) pluggable database name.

Step 6. The installer performs the prerequisite check.

Step 7.  The installer shows you the summary of the information such as global settings, database information, etc. You need to review the information and click the install button if everything is fine.

Step 8. The installer starts installing the Oracle database. It will take a few minutes to complete, depending on your computer.

You will see the Database Configuration Assistant window. Click the Password management… button to enter the password for Oracle database accounts.

Enter the password for SYS and SYSTEM accounts and then click the OK button.

Step 9.Once installation completes successfully, the installer will inform you as shown in the following screenshot. Click the Close button to close the window.

Connecting to Oracle Database

• First, launch the SQL developer application provided by the Oracle Database.
• Second, right-click the connections node and choose New Connection … menu item to create a new connection.

• Third, enter the information that you provided during the installation process as shown in the following screenshot. Click the Connect button to connect to the Oracle Database.

SQL developer will display all objects as shown below.

Conclusion

• Hope you enjoyed reading the blog “Oracle DBA tutorial online for beginners”. In this blog, we discussed all important aspects and the parameters that are necessary to understand before you start working as an Oracle Database administrator.
• Once you will go through this blog carefully, this would be easy for you to decide whether the Oracle DBA profession is the right choice for your career or not.