Oracle DBA's Blog

Automatic Diagnostic Repository.
ADR is file based repository for diagnostic data like trace file,process dump,data structure dump etc. In oracle 11g trace. alert not saved in *_DUMP_DEST directory even you set those parameters in init.ora.11g ignore *_DUMP_DEST and store data in new format.

New initialize parameter DIAGNOSTIC_DEST decide location of ADR root,

Database Capture/replay database workloads :-
Allows the total database workload to be captured, transferred to a test database created from a backup or standby database, then replayed to test the affects of an upgrade or system change. Currently, they are working to a capture performance overhead of 5%, so you could conceivably capture real production workloads.

Automatic Memory Tuning:-
Automatic PGA tuning was introduced in Oracle 9i.
Automatic SGA tuning was introduced in Oracle 10g.
In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.
We need to set parameter MEMORY_TARGET and MEMORY_MAX_TARGET where are sga_target and pga_aggregated_target should be zero

Database Health Check:-
Oracle introducee a new feature in 11g for monitoring the database health named as health monitor. With health monitor oracle automatically run a diagnostic operation to check any database corruption or error.

Data Recovery Advisor:-
Data Recovery Advisor is an Oracle Database 11g tool that automatically diagnoses data failures, determines and presents appropriate repair options, and executes repairs at the user’s request. Data Recovery Advisor can diagnose failures such as the following:
Inaccessible components like datafiles and control files.
Physical corruptions such as block checksum failures and invalid block header
Field values,Inconsistent datafiles (online and offline)
I/O failures
The advisor however doe not recover from failures on standby databases or RAC environment. This advisor can be used through RMAN or the Enterprise Manager.

Oracle Flashback-Related New Features
Oracle Database 11g introduces the following new Flashback-related features:
LogMiner Interface in Oracle Enterprise Manager
•Oracle Flashback Transaction Backout
•Oracle Flashback Data Archives

Case sensitive password :-
In 11g Passwords are case sensitive by default, new init.ora parameter sec_case_sensitive_logon is introduce in 11g . it values could be.
sec_case_sensitive_logon = (TRUE FALSE)
On system level you can switch off
alter system set sec_case_sensitive_logon = false
Find users who have case sensitive or case insensitive passwords

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS — Password file orapwd file=orclpwd password=manager ignorecase=y

SQL Performance Analyzer (SPA):-                                                                                                                                                                 It is a new feature in 11g. The SPA is used to measure the performance of SQL statement execution impact due to changes in database. If it find out the degradation in one or more sql sttmts performance then it will give recommendations on how to improve their performance.

Active Database Duplication
Now DUPLICATE command is network aware i.e.we can create a duplicate or standby database over the network without taking backup or using old backup.

RMAN UNDO bypass : Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespaces
Fast incremental backups on physical standby database in 11g we can enable block change tracking on a physical standby database (ALTER DATABASE ENABLE/ DISABLE BLOCK CHANGE TRACKING SQL statement).This new 11g feature enables faster incremental backups on a physical standby database than in previous releases. because RMAN identify the changed blocks since the last incremental backup.

Improved block media recovery performance:-
RECOVER command can recover individual data blocks.
RMAN take older, uncorrupted blocks from flashback and the RMAN can use these blocks, thereby speeding up block media recovery.

ADDM RAC Enhancements:-
In case of RAC script addmrpt.sqlrun give report for single instance, not report of all instance in RAC. But using DBMS_ADDM , we can generate report for all instance of RAC.

Oracle 10g Release 2 (10.2.0) – September 2005

• Transparent Data Encryption
• Async commits
• CONNECT ROLE can not only connect
• Passwords for DB Links are encrypted
• New asmcmd utility for managing ASM storage

Oracle 10g Release 1 (10.1.0)

•  Grid computing – an extension of the clustering feature (Real Application Clusters)
• Manageability improvements (self-tuning features)
• Performance and scalability improvements
• Automated Storage Management (ASM)
• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM)
• Flashback operations available on row, transaction, table or database level
• Ability to UNDROP a table from a recycle bin
• Ability to rename tablespaces
• Ability to transport tablespaces across machine types (E.g Windows to Unix)
• New ‘drop database’ statement
• New database scheduler – DBMS_SCHEDULER
• DBMS_FILE_TRANSFER Package
• Support for bigfile tablespaces that is up to 8 Exabytes in size
• Data Pump – faster data movement with expdp and impdp.

Oracle 9i Release 2 (9.2.0)

• Locally Managed SYSTEM tablespaces
• Oracle Streams – new data sharing/replication feature (can potentially replace Oracle Advance Replication and Standby Databases)
• XML DB (Oracle is now a standards compliant XML database)
• Data segment compression (compress keys in tables – only when loading data)
• Cluster file system for Windows and Linux (raw devices are no longer required).
• Create logical standby databases with Data Guard
• Java JDK 1.3 used inside the database (JVM)
• Oracle Data Guard Enhancements (SQL Apply mode – logical copy of primary database, automatic failover
• Security Improvements – Default Install Accounts locked, VPD on synonyms, AES, Migrate Users to Directory

Oracle 9i Release 1 (9.0.1) – June 2001 

• Traditional rollback segments (RBS) are still available, but can be replaced with automated System Managed Undo (SMU). Using SMU, Oracle will create it’s own “Rollback Segments” and size them automatically without any DBA involvement.
• Flashback query (dbms_flashback.enable) – one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.
• Use Oracle Ultra Search for searching databases, file systems, etc. The UltraSearch crawler fetch data and hand it to Oracle Text to be indexed.
• Oracle Nameserver is still available, but deprecate in favour of LDAP Naming (using the Oracle Internet Directory Server). A nameserver proxy is provided for backwards compatibility as pre-8i client cannot resolve names from an LDAP server.
• Oracle Parallel Server’s (OPS) scalability was improved – now called Real Application Clusters (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications doesn’t need to be cluster aware anymore.
• The Oracle Standby DB feature renamed to Oracle Data Guard. New Logical Standby databases replay SQL on standby site allowing the database to be used for normal read write operations. The Data Guard Broker allows single step fail-over when disaster strikes.
• Scrolling cursor support. Oracle9i allows fetching backwards in a result set.
• Dynamic Memory Management – Buffer Pools and shared pool can be resized on-the-fly. This eliminates the need to restart the database each time parameter changes were made.
• On-line table and index reorganization.
• VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
• Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI’s, etc. XML integrated with AQ.
• Cost Based Optimizer now also consider memory and CPU, not only disk access cost as before.
• PL/SQL programs can be natively compiled to binaries.
• Deep data protection – fine grained security and auditing. Put security on DB level. SQL access do not mean unrestricted access.
• Resumable backups and statements – suspend statement instead of rolling back immediately.
• List Partitioning – partitioning on a list of values.
• ETL (eXtract, transformation, load) Operations – with external tables and pipelining.
• OLAP – Express functionality included in the DB.
• Data Mining – Oracle Darwin’s features included in the DB.

Oracle 8i (8.1.7)

 Static HTTP server included (Apache)

• JVM Accelerator to improve performance of Java code
• Java Server Pages (JSP) engine
• MemStat – A new utility for analyzing Java Memory footprints
• OIS – Oracle Integration Server introduced.
• PLSQL Gateway introduced for deploying PL/SQL based solutions on the Web
• Enterprise Manager Enhancements – including new HTML based reporting and Advanced Replication functionality included.
• New Database Character Set Migration utility included.

Oracle 8i (8.1.6) 

• PL/SQL Server Pages (PSP’s)
• DBA Studio Introduced
• Statspack
• New SQL Functions (rank, moving average)
• ALTER FREELISTS command (previously done by DROP/CREATE TABLE)
• Checksums always on for SYSTEM tablespace allowing many possible corruptions to be fixed before writing to disk
• XML Parser for Java
• New PLSQL encrypt/decrypt package introduced
• User and Schemas separated
• Numerous Performance Enhancements

Oracle 8i (8.1.5)

 Fast Start recovery – Checkpoint rate auto-adjusted to meet roll forward criteria

• Reorganize indexes/index only tables which users accessing data – Online index rebuilds
• Log Miner introduced – Allows on-line or archived redo logs to be viewed via SQL
• OPS Cache Fusion introduced avoiding disk I/O during cross-node communication
• Advanced Queueing improvements (security, performance, OO4O support
• User Security Improvements – more centralisation, single enterprise user, users/roles across multiple databases.
• Virtual private database
• JAVA stored procedures (Oracle Java VM)
• Oracle iFS
• Resource Management using priorities – resource classes
• Hash and Composite partitioned table types
• SQL*Loader direct load API
• Copy optimizer statistics across databases to ensure same access paths across different environments.
• Standby Database – Auto shipping and application of redo logs. Read Only queries on standby database allowed.
• Enterprise Manager v2 delivered
• NLS – Euro Symbol supported
• Analyze tables in parallel
• Temporary tables supported.
• Net8 support for SSL, HTTP, HOP protocols
• Transportable tablespaces between databases
• Locally managed tablespaces – automatic sizing of extents, elimination of tablespace fragmentation, tablespace information managed in tablespace (i.e moved from data dictionary) improving tablespace reliability
• Drop Column on table (Finally !!!!!)
• DBMS_DEBUG PL/SQL package, DBMS_SQL replaced by new EXECUTE IMMEDIATE statement
• Progress Monitor to track long running DML, DDL
• Functional Indexes – NLS, case insensitive, descending

Oracle 8.0 – June 1997

• Object Relational database
• Object Types (not just date, character, number as in v7
• SQL3 standard
• Call external procedures
• LOB >1 per table
• Partitioned Tables and Indexes
• export/import individual partitions
• partitions in multiple tablespaces
• Online/offline, backup/recover individual partitions
• merge/balance partitions
• Advanced Queuing for message handling
• Many performance improvements to SQL/PLSQL/OCI making more efficient use of CPU/Memory. V7 limits extended (e.g. 1000 columns/table, 4000 bytes VARCHAR2)
• Parallel DML statements
• Connection Pooling ( uses the physical connection for idle users and transparently re-establishes the connection when needed) to support more concurrent users.
• Improved “STAR” Query optimizer
• Integrated Distributed Lock Manager in Oracle PS (as opposed to Operating system DLM in v7).
• Performance improvements in OPS – global V$ views introduced across all instances, transparent failover to a new node
• Data Cartridges introduced on database (e.g. image, video, context, time, spatial)
• Backup/Recovery improvements – Tablespace point in time recovery, incremental backups, parallel backup/recovery. Recovery manager introduced
• Security Server introduced for central user administration. User password expiry, password profiles, allow custom password scheme. Privileged database links (no need for password to be stored)
• Fast Refresh for complex snapshots, parallel replication, PL/SQL replication code moved in to Oracle kernel. Replication manager introduced.
• Index Organized tables
• Deferred integrity constraint checking (deferred until end of transaction instead of end of statement).
• SQL*Net replaced by Net8
• Reverse Key indexes
• Any VIEW updateable
• New ROWID format

Oracle 7.3

• Partitioned Views
• Bitmapped Indexes
• Asynchronous read ahead for table scans
• Standby Database
• Deferred transaction recovery on instance startup
• Updatable Join Views (with restrictions)
• SQLDBA no longer shipped.
• Index rebuilds
• db_verify introduced
• Context Option
• Spatial Data Option
• Tablespaces changes – Coalesce, Temporary Permanent,
• Trigger compilation, debug
• Unlimited extents on STORAGE clause.
• Some init.ora parameters modifiable – TIMED_STATISTICS
• HASH Joins, Antijoins
• Histograms
• Dependencies
• Oracle Trace
• Advanced Replication Object Groups
• PL/SQL – UTL_FILE

Oracle 7.2

 Resizable, autoextend data files

• Shrink Rollback Segments manually
• Create table, index UNRECOVERABLE
• Subquery in FROM clause
• PL/SQL wrapper
• PL/SQL Cursor variables
• Checksums – DB_BLOCK_CHECKSUM, LOG_BLOCK_CHECKSUM
• Parallel create table
• Job Queues – DBMS_JOB
• DBMS_SPACE
• DBMS Application Info
• Sorting Improvements – SORT_DIRECT_WRITES

Oracle 7.1

 ANSI/ISO SQL92 Entry Level

• Advanced Replication – Symmetric Data replication
• Snapshot Refresh Groups
• Parallel Recovery
• Dynamic SQL – DBMS_SQL
• Parallel Query Options – query, index creation, data loading
• Server Manager introduced
• Read Only tablespaces

Oracle 7.0 – June 1992 

• Database Integrity Constraints (primary, foreign keys, check constraints, default values)
• Stored procedures and functions, procedure packages
• Database Triggers
• View compilation
• User defined SQL functions
• Role based security
• Multiple Redo members – mirrored online redo log files
• Resource Limits – Profiles
• Much enhanced Auditing
• Enhanced Distributed database functionality – INSERTS, UPDATES,DELETES, 2PC
• Incomplete database recovery (e.g SCN)
• Cost based optimiser
• TRUNCATE tables
• Datatype changes (i.e VARCHAR2 CHAR, VARCHAR)
• SQL*Net v2, MTS
• Checkpoint process
• ·         Data replication – Snapshots

Oracle 6.2 

• Oracle Parallel Server

Oracle 6 – July 1988 

• Row-level locking
• On-line database backups
• PL/SQL in the database

Oracle 5.1 

• Distributed queries

Oracle 5.0 – 1986 

• Supporting for the Client-Server model – PC’s can access the DB on remote host

Oracle 4 – 1984 

• Read consistency

Oracle 3 – 1981 

• Atomic execution of SQL statements and transactions (COMMIT and ROLLBACK of transactions)
• Nonblocking queries (no more read locks)
• Re-written in the C Programming Language

Oracle 2 – 1979

 First public release

Basic SQL functionality, queries

Here I am giving to give a quick checklist for a production dba. I have not included any script because each dba have its own scripts. Please have a look on into daily, weekly and quarterly checks .

 Daily Checks

• Check the availability of the database and instance , every 15 mts
• Check the availability of the listener, every 15 mts
• Check the sync between the primary database and standby database , every 15 mts  or based on the SLA(Service level Agreement)
• Check the space usage and make sure that all the tablespace  usage is below critical level, once in a day
• Check the space usage of the archive log file system for both primary and standby
• Verify the success of daily backups, once in a day
• Verify the success of archive log backups , based on the backup interval to the  backup media
• Check the system performance , periodic basis
• Check the database performance , periodic  basis
• Clear the tickets assigned in the ticketing mechanism
• Check for the invalid objects
• Go through the audit files for any suspicious activities
• Go through the alert logs for any critical ora  errors , once in an hour
• Verify all the monitoring agent, including OEM agent and third party monitoring agents , once in an hour
• Archive the alert logs , if required
• Clear the trace  files in the udump and bdump directory as per the policy
• Verify the status of daily scheduled jobs

Weekly Checks

• Check the database statistics collection. On some databases this needs to be done every day depending upon the requirement
• Approve or plan any scheduled changes for the week
• Check for critical and patch updates  from oracle
• Verify the cron jobs scheduled and clear the output directory if required
• Perform logical level backups of important tables
• Perform level 0 or cold backup , this can be changed as per the backup policy

Quarterly Checks

• Checks for the critical patch updates from Oracle,make sure that your systems are in compliance with CPU patches
• Verify the accuracy for the backs by creating test databases from the backup
• Verify the accuracy of the DR mechanism by peforming  a database switch over test. This can be done once in six months based on the business requirements

As an Oracle DBA, we must  understand the concepts of Oracle architecture . Here with this artical i am going to share my knowledge about it. Hope it will be useful.

There are two main thing to clearly understand first one is Instance and second is database.

1. INSTANCE:

Instance is nothing but Allocation of the memory to SGA (System Global Area) and starting the mandatory  background process.

System Global Area: SGA is the primary memory structures and it is combination of :–DB Buffer Cache, Redo Log Buffer, Large Pool, Java Pool and Shared Pool (share pool consist of Library Cache and Data Dictionary Cache).

Buffer Cache:- Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.
Shared Pool:- Shared pool is broken into two small part memories –– Library Cache and Dictionary Cache. The library cache is used to stores information about the commonly used SQL and PL/SQL statements; and is managed by a Least Recently Used (LRU) algorithm. It is also enables the sharing those statemens among users. In the other hand, dictionary cache is used to stores information about object definitions in the database, such as columns, tables, indexes, users, privileges, etc.
The shared pool size can be set via SHARED_POOL_SIZE parameter in init.ora initialization parameter file.
Redo Log Buffer:- Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the redo log files. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.
Large Pool:- Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.
Java Pool:- As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Program Global Area:-Although the result of SQL statemen parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

Software Area Code:-Software area code is a location in memory where the Oracle application software resides.

Oracle Background Processes: Oracle background processes are  behind the scene that work together with the memories and they are PMON,SMON,DBWn,LGWR,CKPT etc.

DBWn:- Database writer (DBWn) process is used to write data from buffer cache into the datafiles. Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.
LGWR:-Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redo log files.
CKPT:- Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.
SMON:- System Monitor (SMON) process is used to recover the system crach or instance failure by applying the entries in the redo log files to the datafiles.
PMON:- Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources

2. DATABASE:

Database refers to the disk resources and it is divided into two main structures (a) Logical structures and (b) Physical structures.

Logical Structures: Oracle database is divided into smaller logical units to manage, store, and retrieve data efficiently. The logical units are tablespace, segment, extent, and data block.

Tablespace: A Tablespace is a grouping logical database objects. A database must have one or more tablespaces. Ex: SYSTEM tablespace, SYSAUX Tablespace,  USER Tablespace. Tablespace is composed by one or more datafiles.
Segment:-A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are Temporary Segment and Rollback Segment.
Extent:-A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named.
Data Block:- A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes.

Physical Structures:-The physical structures are structures of an Oracle database (in this case the disk files) that are not directly manipulated by users. The physical structure consists of datafiles, redo log files, and control files.

Datafiles:-A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace, but one tablespace can has more than one datafiles.
Redo Log Files:- Redo log files are the files that store the redo entries generated by DML statements. It can be used for recovery processes.
Control Files:-Control files are used to store information about physical structure of database, such as datafiles size and location, redo log files location, etc.