Oracle Database 12c

Oracle Database 12c

select * from dual;

The notorious v$

PluralSight

• Oracle Database 12c Fundamentals

Architecture

Oracle University[1]

Oracle is called an object-relational database

• Key Terms

1. Relation = Table
2. Attribute = Column
3. Tuple = Row

• Oracle Versions

Oracle 8i (1999) (i=internet)
Oracle 10g (2003) (g=grid computing) Oracle 12c (2014) (c=cloud)

Clients are applications that connect through middle ware or directly to the oracle server.

PDB Plugable databases

Oracle 12c Interactive Quick Reference[2]

Program Global Area (PGA) Components

Automatic Memory Management. Give Oracle a memory chunk and it does the rest.

OFA Naming standard.

Oracle block size. Match that to the OS block size on disk. Linux = 512. Windows = 4096.

Logical Storage Structures

• Heat Map and Automatic Data Optimization
  • Graphically displays data access / usage patterns
  • Move and compress data through your tiered storage hierarchy based on the data "heat" index.
• Oracle data block: Should match the OS file block.

Oracle Management Tools

• SQL*Plus: CLI for the Oracle Database
• SQL Developer: Graphical utility for DBA and development work
• EM Express: Lightweight Web Interface - very weak
• EM Cloud Control: Robust, multi-platform management tool
• DBCA: Create and manage traditional and pluggable databases
• NetCA: Wizard-based net services configuration tool
• Netcfg: Java-based graphical front-end for Oracle Net Services
• OUI: Java-based software installation and maintenance tool for Oracle products.
• Must have java installed to use any sql developer tools.

Instance

• Reads primary initialization parameter file: ORACLE_HOME/dbs/spfile.ora - this can be modified while the server is running. binary file type.
• DB_NAME parameter
• Key/value pairs are case-sensitive dependent on the OS. Single quotes for strings.

Never lose your command-line skills. Sometimes that's the only way to recover a crashed server.

SQL>

shutdown and startup are the most graceful methods. Windows Service Control manager and net stop also work.

• Alert log

v$

Windows is not case-sensitive, LINUX is.

Initialization file choice is:

1. spfile - binary
2. pfile - text

show parameter - fetches all the parameters

shutdown - the graceful command

shutdown force - A hard crash, no active work is saved.

Oracle Net Services

Called Oracle Net or SQL*Net

Transparent Network Substrate (TNS)

The Oracle Net Listener

• Server-side app accepts incoming client connection requests
• LREG background process manages the listener's activity
• Service Name: Logical name for an Oracle database service
• Service ID (SID): Unique name for a specific Oracle database
• One SID can have multiple service names. Say, one shared connection and one dedicated connection.

LISTENER.ORA file.

•  %ORACLE_HOME%\Network\Admin
• Set %TNS_ADMIN% environmental variable mapped to %ORACLE_HOME%

It contains

• Listener name
• Protocol address(es) that the listener is accepting requests on
• Database services

Example

LISTENER = // default name
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orserver)(PORT=1521))
    }
  )

• Indents must be spaces
• '(' and ')' MUST line up.

listener control utility http://is.gd/gjnng5

Oracle Naming Methods

TNSNAMES.ORA, client side file. same syntax.

Easy Connect

• connection

Oracle Internet Directory (OID) - Same as Windows Active Directory

• LDAPv3 directory service

Oracle Database Client

Oracle Storage Structures

Table Space

• Segments
  • Table
  • Row piece
    • Blocks, which are allocated as 'Extent's
• Row pieces can become chained within a block or between blocks (fragmentation can happen)
• Blocks are row data, free space, and a header that contains metadata about that block

What is a table space?

• A logical storage container for segments
  • Segments are database objects like tables or indexes
  • Table space one or more datafiles that are physical db objects
  • A segment cannot contain multiple tablespaces
• SYSTEM and SYSAUX are crucial to operating the database
• table spaces map directly to physical files.

Plugable Databases (PDB)s

Oracle Enterprise Manager works through the web browser with the link https://oramt:5501/em/login?returnUrl=/em/

PDBs, Table spaces, new stuff in 12c

• In a standard database, all tablespaces belong to one database.
• In a CDB, the root container owns one tablespace set.
• A tablespace in a PDB can contain objects associate with only one PDB
• Objects in a tablespace can have the same label because the objects are partitioned.

Single-instance database

Plugable database

Creating Database Objects

• schema
• table structure
• SQL*Plus and SQL Developer

A schema is owned by a particular user and contains logical structures

• Schemas have no direct relationship to tablespaces.
• Syntax is
  • schema.object

Tables are stored in the data dictionary in UPPERCASE.

Static DD views

• USER_TABLES
• ALL_TABLES
• DBA_TABLES

User Administration Basics

Common vs Local User

Seed PDB database, template for PDB creation.

Oracle Documentation[3]

'grant create session' to new users so they can connect.

• Undo Data

ROLLBACK - undo tablespace is wonderful.

Atomic Consistent Isolated Durable (ACID) -

Oracle Cloud[4]

Internal Links

Parent Article: Main Page