Oracle Database Overview
Oracle Database Overview
- Oracle Database Overview Hong Lee
- Relational Database Management System (RDBMS)
- RDBMS is based on the relational model as introduced by E. F. Codd . D ata and the relationships are stored in the form of tables. Referential integrity between tables is often enforced by the combination of a primary key and a foreign key.
- RDBMS offers strong consistency guarantees, it uses t ransactions that provide "all-or-nothing", meaning each unit of work performed in a database must either complete entirely or have no effect at all.
- RDBMS provides strong concurrency control. Its concurrency mechanisms manage and support multiple users accessing the same group of resources (such as tables, rows).
- RDBMS interface is flexible and offers data independence. Uses interface with RDBMS in SQL – a standard interactive and programming language for querying and modifying data and managing databases.
- Such RDBMS include Microsoft SQL Server, DB2, Sybase and Oracle .
- Database Market Share
- What is an Instance?
- A database instance , or an ‘instance’ is made up of the background processes/threads needed by the database software.
- These processes usually include a process monitor, session monitor, lock monitor, database writer, etc. They will vary from database vendor to database vendor.
- An instance controls 0 or more databases
- A database can have 1 or more instances
- What is a Schema?
- A SCHEMA IS NOT A DATABASE, AND A DATABASE IS NOT A SCHEMA.
- A database application schema is the set of database objects (tables, indexes, triggers, etc.) that owned by a user that apply to a specific application.
- These objects are relational in nature, and are related to each other, within a database to serve a specific functionality.
- For example payroll, purchasing, order, etc. Usually several schemas coexist in a database.
- Tables, Indexes, Constraints
- Table - a set of columns that contain data. In the old days, a table was called a file.
- Row - a set of columns from a table reflecting a record.
- Index - an object that allows for fast retrieval of table rows. Every primary key and foreign key should have an index for retrieval speed.
- Primary key (PK) - 1 or more columns in a table that makes a record unique.
- Foreign key (FK) - a common column between 2 tables that define the relationship between those 2 tables.
- The Three Normal Forms
- First Normal Form: All column values are atomic - Indivisible
- The Three Normal Forms
- Second Normal Form: All column values depends on the value of the primary key
- The Three Normal Forms
- Third Normal Form: No column value depends on the value of any other column except the primary key.
- DBA Tasks
- System DBA
- Installing and upgrading the database software and application tools
- Allocating system storage and planning future storage requirements for the database system
- Creating database and its parameters
- Creating database storage structures (tablespaces)
- Creating database objects (tables, views, indexes)
- Create users and grant database privileges
- Setup database security and auditing
- Setup database backup
- Recover database when necessary
- Setup database monitoring
- Database optimization and performance tuning
- Perform database maintenance (reorgs, collect stats)
- Design and Implement database high availability strategy
- Contact database vendor for technical support
- Ensuring compliance with the database license agreement
- DBA Tasks
- Application DBA
- Reviewing database designs and application functionalities with the business, developers, and data modelers
- Reviewing database structure changes that provided by application developers and data modelers
- Implement the database structure such as tables, indexes
- Working with application developers to tune the performance of the database
- Reviewing and tuning the SQL scripts
- Designing and implementing the database migration
- Helping load/unload data
- Database refreshing and cloning
- Data replications
- Oracle Instance and Database
- Oracle Instance
- Oracle background processes:
- SMON – System Monitoring
- PMON – Process Monitoring
- CKPT – Checkpoint process
- ARC0 – Archive log process
- LGWR – Log writer
- …
- Memory
- SGA – System Global Area
- PGA – Program(Private) Global Area
- Oracle Database
- Data files
- Control files
- Redo log files
- Archive log files
- Parameter files
- A Simple View of Oracle Instance and Database Database Files Control Files Redo Log Files Archive Log Files Oracle Instance Shared_pool Database Buffer Cache Redo Log Buffer System Global Area (SGA) Oracle Processes Oracle Database
- Database Data Cache Redo Log Buffer
- How do I connect to the Oracle database tnsnames.ora sqlnet.ora listener.ora tnsnames.ora sqlnet.ora 1 2 3 4
- What is a Tablespace?
- A tablespace is a logical storage unit in an Oracle database.
- Database Space Management
- Add a new tablespace
- Database Space Management
- Add datafiles to a tablespace
- Database Space Management
- Dynamically sizing a datafile
- Automatic Storage Management (ASM)
- ASM is a database instance that provides:
- • Load balancing in parallel across disk drives
- • Prevention of disk space fragmentation
- • Online disk space reorganization
- • Data redundancy to provide fault tolerance
- ASM instance - Instance that manages the diskgroup metadata
- Disk Groups
- Logical grouping of raw disks
- Determines file mirroring options
- ASM Disks - LUN presented to ASM
- ASM Files - Includes database files
- Volume Management vs ASM
- Direct I/O
- Asynchronous I/O
- Striping and Mirroring
- ASM Best Practices RPM
- Install Software and Create Database
- Kernel settings:
- # Shmmax set to half of the physical mem.
- kernel.shmmax = 4294967296
- kernel.shmall = 2097152
- kernel.shmmni = 4096
- fs.file-max = 327679
- kernel.sem=250 32000 100 128
- net.ipv4.ip_local_port_range = 1024 65000
- net.core.rmem_default=262144
- net.core.wmem_default=262144
- net.core.rmem_max=262144
- net.core.wmem_max=262144
- OS Patches
- Run “root.sh”
- Oracle id: (ora1adm, ora5adm, ora9adm)
- Directories:
- /u01 – Oracle software
- /u02 ~ /uxx – Oracle database
- X-window is required
- Sysadmin and DBA
- Oracle Backup and Recovery
- SMON – System Monitor
- Background process that automatic recover the database after a crash
- Export/Import
- Fast unload/load data and structure to a dump file
- Can only recover to when the export was taken
- Import will be slow for large database
- Best suited for Dev/Test, refreshing
- Data Pump
- Newer version of export and import
- Parallelism
- Can stop and restart
- RMAN (Recover Manager)
- Oracle backup and recover tool (language)
- Able to recover database to point-in-time
- Integrated with many media management tools
- Flashback recovery
- New technology that use Flashback Area and Undo space to undo the changes
- Best suited for logical data corruption (user error)
- Recovery with RMAN and tape
- Recovery with Flashback
- Database Monitoring
- What:
- Database up and down
- Database alert log error
- Database directory usage (archive log, software)
- Database backup status
- Tablespace space usage
- Database listener up and down
- How:
- K-shell scripting integrated with NSM
- Oracle Enterprise Manager (OEM)/Grid Control
- Oracle Enterprise Manager (OEM) / Grid Control Grid control console Management server (OMS) Management repository RAC Database Managed targets Agent Agent Agent Database Database Agent
- OEM/Grid Control – Server Performance
- OEM/Grid Control – Database Performance
- OEM/Grid Control – Top Activity
- OEM/Grid Control – Top SQL
- OEM/Grid Control – SQL Tuning
- OEM/Grid Control – Memory Advice
- High Availability – Standby Database
- High Availability – Standby Database Failover
- High Availability – Real Application Cluster
- High Availability – Real Application Cluster Node n Node 1
- High Availability – Real Application Cluster
- High Availability – Real Application Cluster RAC
- A Q & Q U E S T I O N S A N S W E R S
No comments:
Post a Comment