After completing the course, participants will know how to:

• modify the relevant instance, database and session parameters
• start, stop and restart the server
• design and create a new database
• prepare parameters file
• create and manage users
• grant and revoke the roles and privileges
• manage schema objects and their parameters
• manage tablespaces
• use Flashback mechanisms to recover data
• monitor database on different levels
• configure the listener
• create a database link and use remote query

This course is for database administrators. 

The training can be useful also for developers implementing databases and database applications who – by understanding the principles of operation of the server – will be able to better use its potential.

The training is carried out in the form of theoretical part in the form of mini lectures and the practical part in the form of computer exercises.

4 days, 32 training hours

Oracle Server Architecture Overview

Databases and instances

New features in Oracle 19c instance architecture

Multitenant Architecture

Container databases

Pluggable databases

The differences in the container management in the Enterprise and Standard editions

Logical storage structure

• Tablespaces
• Blocks
• Extents
• Segments

Database users

• Database and Diagrams
• Schemes and their objects

Logical database structure

• Tables
• Limitations
• Indexes
• Views
• Profiles
• Sequences
• Synonyms

The physical data storage structure

• Data Files
• Redo log files
• Control files
• Archived log files
• Initialization parameter files
• Alerts log and trace files
• Backup files
• Oracle ManagedFiles
• Password files

Instance memory structures 

• SGA
• PGA
• Shared SQL Area
• Background Processes

Starting and Closing instance

Creating a database

Initialization parameters

• Binary and text parameters file
• Basic parameters
• Advanced Parameters

Selecting and updating data (how SQL commands are processed)

Dynamic memory management of the SGA

Ensuring consistency and concurrency transactions

Managing database structure

Tablespaces

• Types of tablespaces
• Architecture OFA (Optimal Flexible Architecture)
• SYSTEM tablespace
• SYSAUX tablespace
• TEMP tablespace
• UNDOTBS1 tablespace
• USERS tablespace
• EXAMPLE tablespace
• Managing tablespaces
• Changing the size of the tablespace
• Segments in the spaces tables

Managing the physical structure of the database

• Moving data files
• Moving redo log files
• Transferring control files
• Automatic Storage Management architecture

Storage Space management

• Free space management in tablespaces
• Providing space for temporary segments
• Undo tablespace management
• Fragmented tablespaces and segments
• Local tablespace management 
• Bigfile tablespaces
• Segment Advisor
• Undo Advisor
• Automatic Workload Repository
• The use of indices

Rollback

• Data read consistency
• Recovering

Oracle Flashback

• Flashback Query
• DBMS_FLASHBACK package
• FlashbackVersions Query
• FlashbackTransaction Query
• FlashbackTable
• Flashback Drop
• Flashback Database

National language support

Oracle Scheduler and DBMS_SCHEDULER package

Oracle Net

• Descriptors 
• Network Services
• Listener Processes
• Oracle Net Configuration Assistant
• Oracle Net Manager
• Server Listener

Distributed Databases

• Database Connectivity
• Remote selects

System resources control and management

Database users support

Security system

• Privileges
• Roles
• Database access management
• Profile Management

Monitoring

• Data monitoring location
• Instructions monitoring
• Privileges monitoring
• Schema objects monitoring
• Precision monitoring
• Monitoring dictionary views

• PL/SQL – programming language in Oracle databases
• SQL language for data analysts in an Oracle environment - advanced level
• SQL – the language of access to database Oracle
• Oracle SQL language for data analytics