What is DBA & Tutorial? Learning Path - Be Productive with [Oracle]

What is DBA & Tutorial? Learning Path – Be Productive with [Oracle]

Oracle DBA Tutorial

About author

Buvanesh (Azure DevOps Engineer )

Buvanesh is an Azure DevOps Engineer and he has tons of experience in the areas of BI and Data Science teams, CI/CD with Git, Jenkins/Azure DevOps, NoSQL Databases, and Data Modelling in Hive, CDH/HDP, CDH or HDP, Spark, Airflow, NiFi, Kafka, Hive, HBase or MongoDB, Neo4J, Elastic Search, Impala, Sqoop. He spends his precious time researching various technologies and startups.

Last updated on 08th Jul 2020| 1682

(5.0) | 19100 Ratings

What is Database and Why?

  • Database is a software that allows large amounts of data to be kept together in an order.
  • Database systems that establish the connection between the data related to each other and keep them in a regular manner and eliminate the security problem are called Database Management Systems. An example scenario that will reveal the importance of a database management system can be as follows.
  • For example,  Student is going to the library wants to borrow one or more books. There are 1000-10.000 different categories and mostly independent books from each other in the library .
  • If these books are not arranged according to a specific Logic, the person who is looking for books on the shelves is probably looking for the book until night.
  • This is not possible in real life and is an undesirable situation. That is why there are many ways to facilitate searching in libraries. For example: Sort books in alphabetical order. In this way, when we search for a book, we will find the book in such a short time as to be comparable to other amateur methods.
  • The main reason I tell the above scenario is the main reason for the need for Database and Database management systems. As can be seen here, the Database management system and the Database will always be an indispensable part of the increasing information technologies in the developing world.

Here are the places where real life database is used

  • Airlines: Reservations and Ticket Sales
  • Banks: Banking transactions and transactions
  • Universities: Student Tracking system and Note entries
  • Online Sales: Customer and Product information
  • Online Perakende: Sipariş Kayıtları ve sonuçları
  • Human Resources: Employee Profiles and Salaries
  • Production: Stocks and tracking of manufactured products

We say Database management systems are indispensable now let’s look at them what are the most fundamental benefits of Database management systems .Data Consistency: The data is seen the most current to users when multiple users have access.

Ex: You want to buy the ticket from the internet page of the airline, and someone else was doing the same procedure on the same system as you did 1-2 seconds before you bought the ticket. In this case, a second customer can buy that ticket if there is not a Database Management System. This will cause inconsistency of the data. Therefore, Database Management Systems guarantee data consistency in database where multiple users are connected.

  • Data Redundancy: Enables a common data repository used by many people to be managed in a unique place. Otherwise, there will be many repetitions or copies of the data, and the consistency of the data will not be ensured.
  • Data Concurrency:  Database management system provides concurrent access multiple users  to the same data. Otherwise, if the file was used instead of database, users would have access to the same data in turn.
  • Data Integrity: When a user in the system is deleted in this method, all data of related user must be deleted.
  • Data Security: Prevents data from being unintentionally destroyed and corrupted. It also provides Security by allowing users to access only the schemes or tables on which it is permitted.
  • Data Independence: The structure of the data stored in the database, regardless of the matter does not matter Database management system solves this complexity itself.

The most popular database management used in the world:

The list is as follows:

  • Oracle
  • SQL Server
  • MySQL
  • PostgreSQL
  • Sybase
  • DB2
  • FileMaker
  • Ingress
  • Informix
  • MS-Access

Oracle Grid Architecture :

  • The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.
  • For example: you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.
  • Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

Oracle Database Architecture

  • Oracle Database is an object-relational database management system developed and marketed by Oracle Corporation. Oracle Database is commonly referred to as Oracle RDBMS or simply Oracle.

Database and Instance

  • An Oracle Database consists of a database and at least one instance.
  • An instance, or database instance, is the combination of memory and processes that are a part of a running installation and a database is a set of files that store data.
  • The following picture illustrates the Oracle Database server architecture.

    Subscribe For Free Demo

    [custom_views_post_title]

    Oracle Database Architecture
    • Sometimes, a database instance is referred to as an entire running database. However, it is important to understand the distinctions between the two.
    • First, you can start a database instance without having it accessing any database files. This is how you create a database, starting an instance first and creating the database from within the instance.
    • Second, an instance can access only one database at a time. When you start an instance, the next step is to mount that instance to a database. And an instance can mount only one database at a single point in time.
    • Third, multiple database instances can access the same database. In a clustering environment, many instances on several servers can access a central database to enable high availability and scalability.
    • Finally, a database can exist without an instance. However, it would be unusable because it is just a set of files.

    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

    The physical storage structures are simply files that store data. When you execute a CREATE DATABASE statement to create a new database, Oracle creates the following files:

    • Data files: data files contain real data, e.g., sales order and customer data. The data of logical database structures such as tables and indexes are physically stored in the data files.
    • Control files: every database has a control file that contains metadata. The metadata describes the physical structure of the database including the database name and the locations of data files.
    • Online redo log files: every database has an online redo log that consists of two or more online redo log files. An online redo log is made up of redo entries that record all changes made to the data.
    • Besides these files, an Oracle database includes other important files such as parameter files, network files, backup files, and archived redo log files for backup and recovery.
    Oracle Database Architecture - database system files

    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.

    The following picture illustrates segments, extents and data blocks within a tablespace:

    blocks of tablespace

    And the next figure shows the relationship between logical and physical storage structures:

    Logical and Physical Storage

    Database Instance

    • A Database Instance is an interface between client applications (users) and the database. An Oracle instance consists of three main parts: System Global Area (SGA), Program Global Area (PGA), and background processes.
    Oracle Database Architecture - database instance
    • The SGA is a shared memory structure allocated when the instance started up and released when it is shut down. The SGA is a group of shared memory structures that contain data and control information for one database instance.
    • Different from the SGA, which is available to all processes, PGA is a private memory area allocated to each session when the session started and released when the session ends.

    Major Oracle Database’s background processes

    The following are the major background processes of an Oracle instance:

    • PMON is the process monitor that regulates all other processes. PMON cleans up abnormally connected database connections and automatically registers a database instance with the listener process. PMON is a process that must be alive in an Oracle database.
    • SMON is the system monitor process that performs system-level clean-up operation. It has two primary responsibilities including automatically instance recovery in the event of a failed instance, e.g., power failure and cleaning up of temporary files.
    • DBWn is the database writer. Oracle performs every operation in memory instead of the disk because processing in memory is faster and more efficient than on disk. The DBWn process reads data from disk and writes it back to the disk. An Oracle instance has many database writers DBW0, DBW1, DBW2, and so on.
    • CKPT is the checkpoint process. In Oracle, data that is on disk is called block and the data which in memory is called buffer. When a block is written to the buffer and changed, the buffer becomes dirty, and it needs to be written down to the disk. The CKPT process updates the control and data file headers with checkpoint information and signals writing of dirty buffers to disk. Note that Oracle 12c allows both full and incremental checkpoints.
    • LGWR is the log writer process which is the key to the recoverability architecture. Every change occurs in the database is written out to a file called redo log for recovery purposes. And these changes are written and logged by LGWR process. The LGWR process first writes the changes to memory and then disk as redo logs which then can be used for recovery.
    • ARCn is the archiver process that copies the content of redo logs to archive redo log files. The archiver process can have multiple processes such as ARC0, ARC1, and ARC3, which allow the archiver to write to various destinations such as D: drive, E drive or other storage.
    • MMON is the manageability monitor process that gathers performance metrics.
    • MMAN is the memory manager that automatically manages memory in an Oracle database.
    • LREG is the listener registration process that registers information on the database instance and dispatcher processes with the Oracle Net Listener.
    Course Curriculum

    Best Practical Oriented Oracle DBA Certification Course

    Weekday / Weekend BatchesSee Batch Details

    Responsibilities of Database Administrators

    • Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.

    A database administrator’s responsibilities can include the following tasks:

    • Installing and upgrading the Oracle Database server and application tools
    • Allocating system storage and planning future storage requirements for the database system
    • Creating primary database storage structures (tablespaces) after application developers have designed an application
    • Creating primary objects (tables, views, indexes) once application developers have designed an application
    • Modifying the database structure, as necessary, from information given by application developers
    • Enrolling users and maintaining system security
    • Ensuring compliance with Oracle license agreements
    • Controlling and monitoring user access to the database
    • Monitoring and optimizing the performance of the database
    • Planning for backup and recovery of database information
    • Maintaining archived data on tape
    • Backing up and restoring the database
    • Contacting Oracle for technical support

    Career Path in Oracle DBA

    We will cover the certification path and the hierarchy along with it.

    There is 6 section for the Oracle database career path, these are categorized as per the level of experience one has:

    • Junior Associate (OJA) – oracle certified junior associate
    • Associate (OCA) – Oracle certified associate
    • Professional (OCP) – Oracle Certified Professional
    • Master (OCM) – Oracle certified Master
    • Specialist (OCE) – Oracle certified expert
    • Expert (OCS) – Oracle certified specialist

    These certifications are spread across 9 disciplines or technology areas which further broken down into a category of the product family and product grouping. These certifications validate candidate skills and expertise. Oracle certifications are sought to be one of the most respectful in the market for a long time.

    Job Positions or Application Areas For Career In Oracle DBA

    • Application areas are nothing but where the skills of the knowledge areas can be applied. Hope you understand this statement, let’s see a practical example of it. If you are an IoT expert than the application areas would be anything where this technology already plays or can play a crucial role if applied. In the same reference, there is wide applicability for the Oracle databases. Let’s see them –
    • One can easily figure out that databases are something that stores the data (in any form). So, they are applicable to the number of things, let’s see them.
      • Online television streaming
      • Gaming industry
      • Cloud Storage
      • Finance
      • Government organizations
      • eCommerce
      • Healthcare
      • Weather
    • Basically, the thing is if you need storage than directly or indirectly you need a database system and in return a DBA to allow it proper functioning. ORACLE being the market leader in this segment. Let’s see some facts why Oracle is the market leader and why Career in Oracle DBA people are best paid in the industry.

    Salary

    • One of the best-paid industry in the IT sector or any other sector. Data is crucial and people working for it gets a benefit of high packed salary. Let’s see some stats –
    • The average salary for an Oracle DBA is around 493k/year in India.
    • Canada has different PayScale for DBA positions, the average salary is $100,000/year or $51.28 per hour. The entry level starts with $61,100 per year while the experienced folks get up to $170,000 per year.
    • Talking of US, salary for a DBA professional is at a national average of $94,536.

    Career Outlook in Oracle DBA

    • Oracle DBA is always on demand in any company. Either it is a mid-cap or any MNC (large scale), they all need DBA to operate and manage their databases. Bigger companies have separate domains or sections to handle the database and related issue. Oracle being the pioneer in the database segment and trusted among many companies. A career in Oracle DBA will get this benefit all the time as the steam has huge demand.

    Database Administrator certifications

    • Oracle: Oracle DB Certified Associate, Oracle DB Certified Professional
    • Microsoft SQL Server: MCSE (Data platform and Business Intelligence) and the older MCDBA
    • IBM: IBM Analytics Certification
    • Oracle: MySQL Database Developer, MySQL Database Administrator
    • MongoDB: Certified DBA, Certified Developer
    • Cassandra: Certified Cassandra Administrator, Certified Cassandra Architect, Certified Cassandra Developer

    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.
    Install Oracle Database
    • Now you need to double-click the setup.exe file to start the installation process. There will be 9 steps which mostly automatically execute.

    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

    Install Oracle Database - Step 1

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

    oracle support username.

    Step 2. In step 2, Oracle installer ask you to 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 at the first time, choose the option 1 and click the Next button.

    Install Oracle Database - Step 2

    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.

    Install Oracle Database - Step 3

    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”.

    Install Oracle Database - Step 4

    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.

    Install Oracle Database - Step 5

    Step 6. The installer performs the prerequisite check.

    Install Oracle Database - Step 6

    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.

    Course Curriculum

    Get On-Demand Oracle DBA Training By MNC Experts

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    Install Oracle Database - Step 7

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

    Install Oracle Database - Step 8
    Install Oracle Database - Step 8-1
    Install Oracle Database - Step 8-2

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

    database configuration assistant

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

    password management

    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.

    Install Oracle Database - Step 9

    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.
    connecting to oracle database
    • 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.
    selecting database connection

    SQL developer will display all objects as shown below.

    connections

    Congratulation! you have installed Oracle Database 12c successfully. Let’s start exploring Oracle.

    Adding an entry to the tnsnames.ora file

    The  tnsnames.ora file is typically located in the following directory:

    • C:\app\<user>\product\12.1.0\dbhome_1\network\admin\

    If you follow the above installation steps, then the file is located at the following directory:

    • C:\app\product\12.1.0\dbhome_1\network\admin\

    You first open the  tnsnames.ora file using any text editor such as Notepad or Notepad++.  Then, you need to add the following lines at the end of the file:

    Oracle Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download
    • PDBORCL =
    •   (DESCRIPTION =
    •     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    •     (CONNECT_DATA =
    •       (SERVER = DEDICATED)
    •       (SERVICE_NAME = pdborcl)
    •     )
    •   )

    After that, you can save the file and close it. It is time to download and load a sample database into the Oracle Database server.

    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 to decide for you either Oracle DBA profession is the right choice for your career or not.
    Name Date Details

    14-Oct-2024

    (Mon-Fri) Weekdays Regular

    16-Oct-2024

    (Mon-Fri) Weekdays Regular

    12-Oct-2024

    (Sat,Sun) Weekend Regular

    12-Oct-2024

    (Sat,Sun) Weekend Fasttrack