I want to start this New Year creating a new session on my blog called “DBA Tip of the month” where I will try to post every month an important tip to help all fellows DBAs to improve their careers.

The first topic of this session will be:
 

“Being Proactive with Backup & Recovery”

Always when I arrive on a new client I ask the DBA on charge the following questions:

• Do you have your recovery strategy documented step by step?
• Are you 100% sure that your tape backups are usable?
• Do you know exactly how long a recovery on your production environment will take if necessary?

And almost 90% of the time the answers will be:

• No!
• I not sure, but I think so!
• No idea, probably…!

You will be on chock to know how many times I’m call to support a DBA to try to recover a Database because the most current tape backup is unusable!

Backup & Recovery are a very important (crucial) part of a DBA role, as a DBA I’ll never be stressed enough to repeat over and over what in my opinion is the most important rule for a DBA:

“The most important rule with respect to data is to never put yourself into an unrecoverable situation.”

You know, because bad stuff happen….

…When you less expect, and due to this, I’ll always recommend a DBA to perform a proactive approach to his/her Database Backup and Recovery strategy.

The main idea is:

1. Randomly choose a backup tape and recovery it on a test machine (It can be a virtual one).
2. Take this opportunity to document all the recover process.
3. Review the entire process ant try to improve it!
4. Repeat this exercise every month and try to involve other DBAs in the process!

This easy process will allow you to:

1. Test your Tape backups and see if they are being backup correctly.
2. Check and improve your recovery knowledge and strategy.
3. Document all your recovery process that could be used for any other DBA in the company in case you are not available in the recovery situation.
4. Detect any error on your backup & recovery strategy.
5. Know your recovery time. Next time your manager asks you” Do you know how long a recovery will take? You will know the exact answer.
6. Have an opportunity to review your process and try to make it more efficient.

Like you can see, this is an easy proactive exercise that will allow you and your company to be prepared in case of a disaster and recovery situations occurs, and you know when this always happens….
 

Cheers,

Francisco Munoz Alvarez

DB_ULTRA_SAFE is a new parameter introduced with Oracle 11gR1, and a fantastic new GEM for High Availability, that using Data Guard to configure on both the primary and standby will trigger the most comprehensive data corruption prevention and detection (and repair on 11gR2, see **) tool in the market.

** Starting in Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

Speaking simple, what this new functionality will do is use your Standby Database as a backup to correct automatically any data corruption on your primary database and vice-versa (again on 11.2).

The DB_ULTRA_SAFE initialization parameter also controls other data protection behavior in Oracle Database, such as requiring ASM to perform sequential mirror write I/Os.

You basically need to understand that when setting DB_ULTRA_SAFE it will control the behaviour of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters for you, which mean:

When you set DB_ULTRA_SAFE to	Then the following parameters…
DATA_AND_INDEX (recommended by Oracle)	DB_BLOCK_CHECKING is set to FULL. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.
DATA_ONLY	DB_BLOCK_CHECKING is set to MEDIUM. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.

Lets’ Check  all the parameters affected by DB_ULTRA_SAFE:

• DB_BLOCK_CHECKING (Introduced with Oracle 8.1.6) prevents memory and data corruptions, but it incurs some performance overhead on every block change.
• DB_BLOCK_CHECKSUM (Introduced with Oracle 8.1.6) detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.
• DB_LOST_WRITE_PROTECT (also introduced with 11gR1) enable or disable a physical standby database to detect lost write corruptions on both the primary and physical standby database.

Important: if you explicitly set the DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters in the initialization parameter file, then the DB_ULTRA_SAFE parameter has no effect and no changes are made to the parameter values. Thus, if you specify the DB_ULTRA_SAFE parameter, do not explicitly set these underlying parameters.

To activate it, all you need to do is follow the following steps:

On the Primary Database:  

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:
2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;
3. SQL> shutdown immediate (Shutdown your Database)
4. SQL> startup (This will start your primary Database using your new parameter set in the SPFILE previously)

On the Physical Standby Database: 

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:
2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;
3. SQL> startup nomount
4. SQL> alter database mount standby database;
5. SQL> alter database recover managed standby database disconnect from session;

If you are using your Standby Database on Read Only mode you also need to run the follow commands on your Physical Standby DB: 

1. SQL> alter database recover managed standby database cancel;
2. SQL> alter database open read only;

If you decide to change later the Read Only Standby to Standby again, you just will need to run the following command: 

1. SQL> alter database recover managed standby database disconnect from session;

Hoping this information could help you in the future,

Francisco Munoz Alvarez

Example done on primary:

Example done at the Standby:

Technorati Tags: DB_ULTRA_SAFE,HIGH AVAILABILITY,BLOCK CORRUPTION,DATA CORRUPTION

I’m writing this post due that I receive this question almost once a week regarding OEM installations.
If you see this error when installing your Oracle Enterprise Manager (Grid Control 10.2.0.3) at OEL 5 or RHEL 5:

/home/oracle/OracleHomes/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

Don’t panic, all you need to do is:

1. Connect as root
2. Run : ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
3. Problem solved, for more information please refer to: http://download.oracle.com/docs/cd/B19306_01/relnotes.102/b15659/toc.htm#CJAHFHCJ

Kind Regards,

Francisco Munoz Alvarez

The EM (Enterprise Manager) Desktop Widgets are lightweight internet applications that provide persistent desktop access to key Enterprise Manager monitoring and diagnostic information.

Oracle first released the EM Widgets at August 2009, now at December 2009; Oracle has already 3 different Widgets available, they are

• Target Search Monitoring - Provides access to Enterprise Manager Targets via quick and easy target-searching capabilities. Also allows you to mark targets as “favourites” and monitor their status in real time.

         

• High-Load Databases - Provides a summary of the top 5 databases ordered by the Average Active Sessions performance or load metric. Also provides access to recent ADDM findings (license required) and drill-down to performance pages of monitored databases.

        

• Service Level & Monitoring - Provides a snapshot of the health of the services, and presents a consolidated view of the most important service data such as availability and current alerts along with the service level. (Require apply the) Enterprise Manager patch# 8869802 before running this widget. 

         

If you are a DBA and want to try them, please refer to this link to download them: http://www.oracle.com/technology/products/oem/widgets/index.html

Kind Regards,

Francisco Munoz Alvarez

SANGAM’09 at Bangalore, India is already behind and I don’t have words to express how wonderful experience it was, I just can say I’ll never forget. India is a fantastic country and a beautiful place to visit. I want to say thank you to the AIOUG directors for the invitation to speak in their first conference, it was fantastic  and I’m 100% sure that AIOUG have a great future and I’ll be more than glad to help and participate any time requested!

Regarding my ACE program presentation in the end of my “Logging or No Logging” speech, I’ll love to see more Indian nominations for ACE Awards, due to the great capacity and knowledge of all professionals in the region, please, if you know someone that fit on this category, please submit a nomination and help Oracle to detect and award the best of the best on your country. (Nominations here)

Download SANGAM’09 presentations and script here

   

Francisco Munoz Alvarez  

After months of tests, Oracle released today the newest release of 11g Database (11.2.0.1 - for Linux x86 and 64bit only), with more than 200 new features and a lot of excellent surprises.

Oracle Database 11g Release 1 (11.1 – Released at August 2007) and Release 2 (11.2 – Released today) are the versions with the bigger number of new capabilities ever build and released by Oracle, that’s way was not a complete surprise when it received the 2009 Best Database of the year award by InfoWorld magazine, and certified by Garner as the Database #1 in the world with 48.9% of the worldwide market..

Capabilities as Real Application Testing, Active Data Guard, Advance Compression, In-Memory Database Cache, ASM Cluster File System (ACFS), the Grid Infrastructure Installation option,  Desktop and Server  class options, Intelligent Data Placement, Oracle Restart,Transparent Tablespace Encryption, Automatic Compilation for Java and PL/SQLs, ADDM for RAC,RAC One Node option, SecureFiles, online upgrade, Flashback Data Archive, Advisors for Streams, Partitioning and Repair  to name some, are new functionalities that in the past where only available at DBA dreams.

If you are looking to upgrade your current DB, direct upgrades are only allowed for versions:

• 9.2.0.8 or higher

• 10.1.0.5 or higher

• 10.2.0.2 or higher

• 11.1.0.6 or higher

 Some deprecated initialization parameters are:

• remote_os_authent

• commit_write

• cursor_space_for_time

• instance_groups

• log_archive_local_first

• plsql_debug replaced by plsql_optimize_level

• plsql_v2_compatibility

• resource_manager_cpu_allocation

• standby_archive_dest

• transaction_lag attribute

• ddl_wait_for_locks

• logmnr_max_persistent_sessions

• plsql_compiler_flags

• max_enabled_roles

• background_dump_dest replace by diagnostic_dest

• user_dump_dest replaced by diagnostic_dest

Always remember that today the most IT and Business challenges/goals are related to:

• Reduce IT Costs
• Reduce Complexity
• Reduce Risk
• Increase the rate of changes
• Be allow to manage more information
• Improve the Quality of service

Clearly Oracle 11g is the best option available in the market to allow you and your business to achieve all these goals. Some of the new functionalities included with 11g are:

New data types

The new data types available in Oracle 11g are:

• Binary XML type - up to 15X faster over XML LOBs. (11gR1)
• DICOM Medical Images. (11gR1)
• 3 D Spatial Support. (11gR1)
• RFID tag data types. (11gR1)

SQL

• Automatic SQL tuning with self-learning capabilities. (11gR1)
• Tables can have Virtual columns (calculated from other columns). (11gR1)
• Virtual Columns Indexes on VC and Partitioning on VC. (11gR1)
• Fast “alter table … add column” with default values. (11gR1)
• Online rebuilding of indexes with no pause on DML activity. (11gR1)
• Ability to mark a table as “read only”. (11gR1)
• New PIVOT and UNPIVOT operations. (11gR1)
• Attribute Extraction of Requested Attributes Only of DICOM metadata. (11gR2)

PL/SQL and XML

• Native Compilation no longer requires a C-compiler. (11gR1)
• New “SIMPLE_INTEGER” data type - always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER. (11gR1)
• SQL and PL/SQL result caching (in SGA). (11gR1)
• Can specify trigger firing order (FOLLOWS-clause). (11gR1)
• Compound triggers - a trigger can be before, after, row and statement all in one. (11gR1)
• New CONTINUE statement - starts the next iteration of the loop. (11gR1)
• DML triggers are up to 25% faster - in particular, row level triggers doing updates against other tables. (11gR1)
• Finer grained dependency tracking. (11gR1)
• Dynamic SQL enhancements. (11gR1)
• Ability to reference sequences (no need to select seq.nextval into :var from dual). (11gR1)
• Support WITH HOLD Option for CURSOR DECLARATION in Pro*C. (11gR2)
• JDBC Support for Time Zone Patching. (11gR2)
• OCI Support for 8-Byte Integer Bind/Define. (11gR2)
• Pro*C Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH. (11gR2)
• Pro*COBOL Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH (11gR2)
• Binary XML Enhancements. (11gR2)
• Oracle XML DB Repository Performance Improvements and Guidelines. (11gR2)
• XMLIndex Enhancements. (11gR2)· XMLType Partitioning. (11gR2)
• JDBC Support for SecureFile Zero-Copy LOB I/O and LOB Prefetching. (11gR2)

ASM

• Support for rolling upgrades. (11gR1)
• Automatic bad block detection and repair. (11gR1)
• Fast mirror resync after temporary connectivity lost. (11gR1)
• ASM Cluster File System (ACFS) (11gR2) - The ASM Cluster File System (ACFS) extends Automatic Storage Management (ASM) by providing a robust, modern, general purpose file system for files beyond the Oracle database files. ACFS provides support for files such as Oracle binaries, report files, trace files, alert logs, and other application data files.
• ASM Dynamic Volume Manager (DVM) (11gR2) - is a kernel-loadable device driver that provides a standard device driver interface to clients (for example, ACFS). File systems or other processes can do I/O to this device driver as they would to any other disk device driver on the system.
• ASM FS Snapshot (11gR2) - is a point-in-time copy of a file system and can provide up to 64 snapshot images.
• Enterprise Manager Integration for ASM File Access Control. (11gR2)
• Oracle Cluster Registry (OCR) and Voting Disk on ASM. (11gR2)
• ASM Optimal Disk Placement. (11gR2)
• ASMCMD Command Extensions (11gR2) - The ASMCMD tool is extended to include management of ASM disks, disk groups, and ASM instance in addition to managing ASM files.
• Automatic Storage Management (ASM) File Access Control. (11gR2)

Partitioning

• Partition advisor - figure out what partitions to create. (11gR1)
• Automated partitioning by interval (new partitions are added automatically). (11gR1)
• Automated reference partitioning by Parent/Child reference (as partitions are created, partitions are created in tables that reference them). (11gR1)
• Partitioning by virtual columns. (11gR1)
• New composite partition types: Range/Range, List/Range, List/Hash, and List/List. (11gR1)
• Support for transportable partitions - for moving partitions between different operating systems. (11gR1)

Compression

• Support compression on INSERT, UPDATE and DELETE operations. 10g only supported compression for bulk data-loading operations. (11gR1)
• Advanced compression allows up to 4 x compression rate of structured and unstructured data improving query performance and reducing storage costs. (11gR1)
• Datapump export dump files can be compressed. (11gR1)
• New Columnar Table Compression .(11gR2)

Clustering

• Agent Development Framework - Oracle Clusterware provides an agent framework for managing all kinds of applications with Oracle Clusterware. Using the agent framework provides optimized application startup, checking, and stopping based on user-defined scripts. This feature helps save money and reduces costs efficiently enabling high availability for applications using Oracle Clusterware. (11gR2)
• Out-of-Place Oracle Clusterware Upgrade - A new version of Oracle Clusterware is now installed into a separate home from the current installation. This reduces the downtime required to upgrade a node in the cluster and facilitate the provisioning of clusters within an enterprise. (11gR2)
• Cluster Time Service - The Cluster Time Service synchronizes the system time on all nodes in the cluster. A synchronized system time across the cluster is a prerequisite to install and successfully run an Oracle cluster. (11gR2)
• Configuration Assistant Support for Removing Oracle RAC Installations. (11gR2)
• Configuration Assistants Support New Oracle RAC Features. (11gR2)
• Downgrading Database Configured With DBControl. (11gR2)
• Enhanced Cluster Verification Utility. (11gR2)
• Enterprise Manager Provisioning for Oracle Clusterware and Oracle Real Application Clusters - Enterprise Manager provisioning introduces procedures to easily scale. (11gR2)
• Enterprise Manager Support for Grid Plug and Play. (11gR2)
• Enterprise Manager Support for Oracle Restart. (11gR2)
• Enterprise Manager-Based Clusterware Resource Management. (11gR2)
• Grid Plug and Play (GPnP). (11gR2)
• Improved Clusterware Resource Modeling. (11gR2)
• Integration of Cluster Verification Utility (CVU) and Oracle Universal Installer (OUI). (11gR2)
• Java API for Oracle RAC FAN High Availability Events. (11gR2)
• Oracle Cluster Registry (OCR) Enhancements. (11gR2)
• Faster relocation of services on node failure.
• Oracle Clusterware now supports up to 5 copies of the OCR for improved availability of the cluster.
• OCR can now be stored in Automatic Storage Management (ASM).
• Oracle Restart Integration with Oracle Universal Installer. (11gR2)
• Oracle Universal Installer Support for Removing Oracle RAC Installations. (11gR2)
• OUI Support for Out-of-Place Oracle Clusterware Upgrade. (11gR2)
• Patch Application with DBControl. (11gR2)
• Policy-Based Cluster and Capacity Management. (11gR2)
• Role-Separated Management - Role-separated management for Oracle Clusterware allows certain administrative tasks to be delegated to different people, representing different roles in the company. It is based on the idea of a clusterware administrator. (11gR2)
• Server Control (SRVCTL) Enhancements. (11gR2)
• Server Control (SRVCTL) Enhancements to Support Grid Plug and Play. (11gR2)
• SRVCTL Support for Single-Instance Database in a Cluster. (11gR2)
• UCP Integration with Oracle Data Guard. (11gR2)
• UCP Integration with Oracle Real Application Clusters. (11gR2)
• Universal Connection Pool (UCP) for JDBC. (11gR2)
• Zero Downtime Patching for Oracle Clusterware and Oracle RAC. (11gR2
• Database QoS Management Support. (11gR2)
• Database Quality of Service (QoS) Management Server (11gR2)
• Enterprise Manager QoS Management Integration. (11gR2)

Performance improvements

• RAC - 70% faster (ADDM has a better global view of the RAC cluster). (11gR1)
• Streams - 30-50% faster. (11gR1)
• Optimizer stats collection – 10X faster. (11gR1)
• Query results caching - 25% faster. (11gR1)
• Table Scans 2.5x Faster using advance compression. (11gR1)
• OLAP-based Materialized Views for fast OLAP CUBE building. (11gR1)
• New In-Memory Database Cache, Data cached in application memory, fast and consistent response times and a standard SQL interface.
• Result caches - new memory areas in the SGA for storing SQL query results, PL/SQL function results and OCI call results. (11gR1)
• Invisible indexes - indexes that are ignored by the optimizer. Handy for testing without dropping. (11gR1)
• Oracle secure files – 5X faster than normal file systems. (11gR1)
• No need to have a C-compiler installed to use Native PL/SQL Compilation. (11gR1)
• Stored Outlines Migration for SQL Plan Management - Stored outlines can be migrated for future and enhanced usage with SQL Plan Management (SPM).
• Support for 4 KB Sector Disk Drives - Today, disk drives have 512 byte sectors. Disk drive manufacturers are moving to 4 KB sector drives because it allows them to offer higher capacity with lower overhead.
• Client Result Cache Using Table Annotations Support - enables applications to leverage client and server result caching through deployment time knobs as opposed to making application changes. In addition, this feature provides automatic client cache invalidation.
• Segment Creation on Demand (11gR2) - The initial segment creation for non partitioned tables and indexes can be delayed until data is first inserted into an object.
• System-Managed Indexes for List Partitioning Tables. (11gR2)
• Zero-Size Unusable Indexes and Index Partitions (11gR2) - Unusable indexes and index partitions do not consume any space in the database anymore; they become segment less.

Availability improvements

• Ability to apply many patches on-line without downtime (RAC and single instance databases). (11gR1)
• Data recovery advisor - quickly identify the root cause of failures; auto fix or present recovery options to the DBA. (11gR1)
• ASM Preferred Mirror Read/ Faster Mirror Resync. (11gR1)
• XA transactions spanning multiple servers. (11gR1)
• Improved runtime connection load balancing. (11gR1)
• Flashback Transaction/ Oracle Total Recall. (11gR1)
• Automatic Block Repair - Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination. (11gR2)
• Backup to Amazon Simple Storage Service (S3) Using OSB Cloud Computing. (11gR2)
• DUPLICATE Without Connection to Target Database. (11gR2)
• Enhanced Tablespace Point-In-Time Recovery (TSPITR). (11gR2)
• New DUPLICATE Options:
  • NO REDO (11gR2)
  • UNDO TABLESPACE <ts_name> [, <ts_name> …] (11gR2)
• New SET NEWNAME Clauses and Format Options (11gR2)
• Tablespace Checks in DUPLICATE. (11gR2)
• New Oracle Streams Features like (11gR2):
  • XStream In - Extended Streams Inbound (XStream In) provides a high performance, transaction-based interface to Oracle Streams for information exchange from non-Oracle databases or file systems.
  • XStream Out - Extended Streams Outbound (XStream Out) provides a high performance, transaction-based interface to Oracle Streams for information exchange to non-Oracle databases or file systems.

Security improvements

• Support for case sensitive and multi-byte passwords (disable by setting SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE. (11gR1)
• Transparent Data Encryption (support for tablespace level encryption). (11gR1)
• Hardware based master key protection. (11gR1)
• Encrypt backups. (11gR1)
• Kerberos authentication - strong passwords. (11gR1)
• Add Multi-factor DBA controls with Data Vault. (11gR1)
• Tablespace Master Key Re-Key (11gR2) - Oracle Advanced Security capability allows customers to change the master key used to protect the encryption keys used to encrypt Oracle tablespaces.

Manageability improvements

• New MEMORY_TARGET parameter for Automatic memory tuning. (11gR1)
• Enterprise Manager Support Workbench for ASM. (11gR2)
• New Gateway Support (11gR2)
• EMCA Supports New Oracle RAC Configuration for EM. (11gR2)
• SQL monitoring (11gR2) - Monitoring the progress of long-running SQL statements at the XPlan level.
• Automatic Patching of Time Stamp with Time Zone Data. (11gR2)
• Prevent Data Loss for Time Zone with Local Time Zone Data Type. (11gR2)
• Enterprise Manager Integration with ASM Optimal Disk Placement. (11gR2)
• Enterprise Manager Support for ASM Cluster File System (ACFS). (11gR2)

Business Intelligence and Data Warehousing

• Allow Virtual Columns in the Primary Key or Foreign Key for Reference Partitioning. (11gR2)
• Analytic Functions 2.0. (11gR2)
• EXECUTE Privilege for DIRECTORY Objects. (11gR2)
• In-Memory Parallel Query. (11gR2)
• Minimal Effort Parallel Execution - Auto Degree of Parallelism (DOP) and Queuing. (11gR2)
• Preprocessing Data for ORACLE_LOADER Access Driver in External Tables. (11gR2)
• Recursive WITH Clause. (11gR2)
• Significant Performance Improvement of On-Commit Fast Refresh. (11gR2)

Oracle Warehouse Builder

• Database 11g now Integrated ETL, Analytics and Data Mining. (11gR1)
• Advanced Find Support in Mapping Editor. (11gR2)
• Copy and Paste of Operators and Attributes in Mapping Editor. (11gR2)
• Current Configuration Dropdown Box in Design Center Toolbar. (11gR2)
• Editor Menu Experts. (11gR2)
• Enhanced Support for Flat File Imports. (11gR2)
• Expression Editing in Operator Edit Dialog. (11gR2)
• Foldering and Spotlighting. (11gR2)
• Fusion Client Platform Integration. (11gR2)
• Grouping and Spotlighting of Objects in Mapping Editor. (11gR2)
• Guided Assistance. (11gR2)
• Improved Management of Locations Registered in Multiple Control Centers. (11gR2)
• Improved User Interface for Managing Locations. (11gR2)
• Key Lookup Operator Enhancements. (11gR2)
  • More efficient use of screen real estate.
  • Support for non-equality lookups.
  • Dynamic lookups, where the lookup table may be modified during the mapping execution.
• Mapping Debugger Enhancements. (11gR2)
  • Improved support for watch points and enabling and disabling of individual break points.
  • Support for user-defined type columns.
  • Enhanced support for numerous existing operators, such as VARRAY, EXPAND, and CONSTRUCT.
  • Support for key lookup and table function operators.
  • Support for correlated joins.o Improved cleanup of debugger-specific objects.
• Metadata Import from COBOL Copybooks. (11gR2)
• OMB*Plus Activity Type. (11gR2)
• Operator References Included in Generated PL/SQL Code. (11gR2)
• Quick Mapper. (11gR2)
• Repository Browser Changes. (11gR2)
• Simplified Oracle Warehouse Builder Repository Upgrades. (11gR2)
• Subquery in Join Operator. (11gR2)
• Support for Extracting Data From Tables Containing LONG Data Type. (11gR2)
• Table Functions. (11gR2)

Data Guard improvements

• Standby databases can now simultaneously be in read and recovery mode - so use it for running reports 24×7. (11gR1)
• Online upgrades: Test on standby and “roll” to production. (11gR1)
• Snapshot standby (create test databases). (11gR1)
• Incremental Backup on Physical Readable Physical Standby. (11gR1)
• Offload: Complete database and fast incremental backups. (11gR1)
• Logical standby databases now supports XML and CLOB datatypes as well as transparent data encryption. (11gR1)
• Compressed Table Support in Logical Standby Databases and Oracle LogMiner. (11gR2)
• Configurable Real-Time Query Apply Lag Limit. (11gR2)
• Integrated Support for Application Failover in a Data Guard Configuration. (11gR2)
• Support Up to 30 Standby Databases. (11gR2)

Oracle SecureFiles

• SecureFiles (also known as “FastFiles” in the 11g beta release) provide faster access to unstructured data than normal file systems. For example, write access to SecureFiles is faster than a standard Linux file system, while read access is about the same. In addition, it provides compression, encryption and data deduplication. (11gR1)
• LZO Support for SecureFiles (11gR2) – This allows Fast decompression - LZO is about 2 times faster than zlib, and Fast compression - LZO is about 3 times faster than zlib.
• SecureFiles Archive Manager. (11gR2)

Real Application Testing

Real Application Testing or RAT will make it easier to do upgrades, hardware replacements and operating system changes. RAT consists of two components:

• Database Replay - capture production workload and replay on different (test) environment. (11gR1)
• SQL Performance Analyzer - identifies SQL execution plan changes and performance regressions. (11gR1)

Other features

• Online application upgrades and “hot” patching (Hot patching is described in educational and marketing materials, but no actual hot patches exist, to date.) (11gR1)· Online table and index redefinition. (11gR1)
• Improved data compression. (11gR1 and 11gR2)
• “duality” between SQL and XML - users can embed XML within PL/SQL and vice versa. (11gR1)
• New binary XML data type, a new XML index & better XQuery support. (11gR1)
• Automated capture of fault diagnostics for faster fault resolution. (11gR1)
• Repair advisers to guide DBAs through the fault diagnosis and resolution process. (11gR1)
• SQL Developer is installed with the database server software (all editions)
• The Windows SQL*Plus GUI is deprecated. (11gR1)
• APEX is now shipped with the DB. (11gR1)
• Improvements to Oracle Scheduler (11gR2) like:
  • E-mail Notification.o File Watcher.
  • Multiple Destination Jobs.
  • Remote Database Jobs.
• Data Pump Legacy Mode. (11gR2)
• Complete IPv6 Support for JDBC Thin Clients. (11gR2)
• Complete IPv6 Support for JVM and Java Debuggers. (11gR2)
• IPv6 Support in Oracle Database. (11gR2)
• Enhanced Spatial Support (11gR2) like:
  • 3D Visualization Framework.
  • Network Data Model Enhancements.
  • New GeoRaster JAVA API.
  • Raster Reprojections and Ground Control Point-Based (GCP) Georeferencing.
  • Routing and Geocoding Enhancements.

Desupported features

The following features are desupported in 11g:

• Oracle export utility (exp). Imp is still supported for backwards compatibility.
• iSQLPlus not shipped anymore, you will need to use the SQL Developer instead.
• Oracle Ultra Search
• Java Development Kit (JDK) 1.4, Oracle recommends to use JDK 5.0, but JDK 1.5 is fully supported
• CTXXPATH Index, Oracle recommends to use XMLIndex Instead

For more information and to download 11gR2 please refer to:

http://www.oracle.com/technology/index.html and

http://www.oracle.com/pls/db112/portal.all_books

If after all this information you still thinking about not move to 11g, please think again a do a favor to yourself and your company, try it

Kind Regards,

Francisco Munoz Alvarez

Sometimes understanding the Oracle Database Editions is a little confuse, and on my opinion know all the options available in the market is something very important and fundamental for any DBA. The main idea of this post is to help you to understand a little bit more about all available options in the Oracle market, learn some key differences and functionalities of each Edition.

Understanding the Different Editions

All the time I receive questions regarding Oracle Database Editions like:

• What’s the difference between Standard Edition and Enterprise Edition?  
• How complicate is to upgrade a Standard Edition Database to Enterprise Edition and vice versa?   
• Can you explain me the most important Options and Packages available with Oracle?  
• Is Oracle Express Edition really free? What are the limitations and what it can do?

So many people don’t understand the diversity of Editions available with Oracle Databases, most of the time everyone associate it with the Enterprise Edition only, saying “Oracle is so expensive! Let’s look to other brands due that we can’t afford it!”, That’s a huge mistake, if you want the best available in the market you need to pay for it, but you need to understand all the options available for you by Oracle Corporation before take a precipitate and wrong decision, you always can find an edition that is affordable for your budget, and I can guarantee that you will not find the same performance and functionality at any other product in the market.

This is like buy a car, you can always dream to have a top brand vehicle, but you don’t need to buy the most expensive model if you only need a compact one, it will be more affordable and you know that you can trust in the quality of the product. The same happens with Oracle Databases. Oracle have available several editions of their Database product, all are the same product (internal code) but depending of the edition it can have some limitations or additional options to improve the performance, security, availability, and management of your 11g Database.

Oracle Database 11g is available in a choice of editions tailored to meet your business, personal, or IT needs, and also offers several extra options to enhance your Database capabilities depending of your applications requirements. Oracle have basically 5 different editions available for you, all are build using the same reliable database engine architecture and are compatible with each other, making this way an upgrade to be a very easy task to the DBA and this will also help to make your database grow at the same speed of your business.
The Editions available are: Express Edition, Personal Edition, Standard One Edition, Standard Edition and Enterprise Edition, now let’s take a quick look on each one:

• Oracle Express Edition (XE): This edition is an entry-level, small-footprint database based at the Oracle 10g release 2 Enterprise code that is free to develop, deploy, and distribute; due to it, this is an excellent option for developers, students, and very small organizations trying to initiate their journey in the Oracle world. Oracle XE is very easy to download and very simple to administrate, it can be installed on any host machine size, with any number of CPU’s and memory available, but it will have several limitations like: will storage only 4GB of data, will use up to 1GB of memory, and will use only 1 CPU in the host machine. Currently this edition is not supported by My Oracle Support (old Metalink), this means no patches available and support, but you can always refer to the OTN forums to ask your peers for any kind of help relate to this edition.
• Oracle Personal edition (PE): This edition supports single-user development and deployment environments that require full compatibility with Oracle Database Standard Edition One, Oracle Database Standard Edition, and Oracle Database Enterprise Edition. Oracle PE includes all of the components, options and functionalities that are included with Enterprise Edition with the exception of the Oracle Real Application Clusters (RAC) option. The negative side of this edition is that this is only available for Windows environments (Windows 2000, Windows NT, Windows XP, Windows Vista, and Windows Server 2003/32-bit and 64-bit versions), and the Management Packs are not included.
• Oracle Standard Edition One (SEO): Delivers an unprecedented ease to use, power, price/performance for workgroups, department-level, and Web applications. This edition is highly recommended to be used from single-server environments for small business to any highly distributed branch environments. Oracle Database SEO includes all the facilities necessary to build any business-critical applications.
• Oracle Standard Edition (SE): Offers a low cost alternative for small/medium business or departmental applications that want to have the power of Oracle, this Edition has all the benefits of Oracle SEO plus support for larger machines and clustering of services with Real Application Clusters (Free in this Edition); RAC was not included in previous Standard Edition releases prior to Oracle Database version 10g.
• Oracle Enterprise Edition (EE): It’s the top of the Oracle Editions line, provides the best in performance, availability, scalability, and security required for mission-critical applications such as high-volume online transaction processing (OLTP) applications, query-intensive data warehouses, and demanding Internet applications. Oracle Database EE contains all of the components of Oracle Database, and can be further enhanced with the purchase of several available options and packs.

Now let’s take a closer look to all availabilities and restrictions of each Oracle Edition:

Limitations/ Availability	Express Edition	Personal Edition	Standard Edition One	Standard Edition	Enterprise Edition
Number of CPU/Sockets	1 CPU	No Limit	2 Sockets	4 Sockets	No Limit
RAM	1GB	OS Maximum	OS Maximum	OS Maximum	OS Maximum
Max. Number of Users	No Limit	1	No Limit	No Limit	No Limit
Databases per Host	1	No Limit	No Limit	No Limit	No Limit
Minimum User License Requirements	Not Apply	Not Apply	5 Named Users Plus	5 Named Users Plus	25 Named Users Plus per CPU
Database Size	4GB	No Limit	No Limit	No Limit	No Limit
Windows	Available	Available	Available	Available	Available
Linux	Available	No Available	Available	Available	Available
Unix	No Available	No Available	Available	Available	Available
64-Bit	No Available	Available	Available	Available	Available

Now you have a clear idea of all available Editions of Oracle in the market, in the next post I’ll go thru the most important options and packs available to enhance your database to achieve all your personal or company goals.

Cheers,

Francisco Munoz Alvarez

One of my DBA’s just ask me why he was receiving the following error each time he tried to setup the host information thru the dbconsole (OEM):

- RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)

The answer is very easy, this error occurs when you forgot to run the root.sh after you finished your installation, you can solve this problem easily running the root.sh  which is located in $ORACLE_HOME/root.sh (as root) and the  error will disappear.

Kind Regards,

Francisco Munoz Alvarez

By Francisco Munoz Alvarez

How to find Sessions Generating Lots of Redo

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 i.block_changes

3 FROM v$session s, v$sess_io i

4 WHERE s.sid = i.sid

5 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 t.used_ublk, t.used_urec

3 FROM v$session s, v$transaction t

4 WHERE s.taddr = t.addr

5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Useful Scripts

To see the redo generated since instance started:

col name format a30 heading ‘Statistic|Name’

col value heading ‘Statistic|Value’

start title80 “Redo Log Statistics”

spool rep_out\&db\red_stat

SELECT name, value

FROM v$sysstat

WHERE name like ‘%redo%’

order by name

/

spool off

pause Press enter to continue

ttitle off

The redo generated during my session since the session started:

select value redo_size

from v$mystat, v$statname

where v$mystat.STATISTIC# = v$statname.STATISTIC#

and name = ‘redo size’

/

The redo generated by current user sessions:

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Provide a current status for redo logs:

column first_change# format 999,999,999 heading Change#

column group# format 9,999 heading Grp#

column thread# format 999 heading Th#

column sequence# format 999,999 heading Seq#

column members format 999 heading Mem

column archived format a4 heading Arc?

column first_time format a25 heading First|Time

break on thread#

set pages 60 lines 132 feedback off

start title132 ‘Current Redo Log Status’

spool rep_out\&db\log_stat

select thread#, group#, sequence#,

bytes, members,archived,status,first_change#,

to_char(first_time,’dd-mon-yyyy hh24:mi’) first_time

from sys.v_$log

order by thread#, group#;

spool off

pause Press Enter to continue

set pages 22 lines 80 feedback on

clear breaks

clear columns

ttitle off

/

Provide redo log groups and log switch (archive generation) information:

set echo on

set linesize 150

set pagesize 500

column day format a16 heading ‘Dia’

column d_0 format a3 heading ‘00′

column d_1 format a3 heading ‘01′

column d_2 format a3 heading ‘02′

column d_3 format a3 heading ‘03′

column d_4 format a3 heading ‘04′

column d_5 format a3 heading ‘05′

column d_6 format a3 heading ‘06′

column d_7 format a3 heading ‘07′

column d_8 format a3 heading ‘08′

column d_9 format a3 heading ‘09′

column d_10 format a3 heading ‘10′

column d_11 format a3 heading ‘11′

column d_12 format a3 heading ‘12′

column d_13 format a3 heading ‘13′

column d_14 format a3 heading ‘14′

column d_15 format a3 heading ‘15′

column d_16 format a3 heading ‘16′

column d_17 format a3 heading ‘17′

column d_18 format a3 heading ‘18′

column d_19 format a3 heading ‘19′

column d_20 format a3 heading ‘20′

column d_21 format a3 heading ‘21′

column d_22 format a3 heading ‘22′

column d_23 format a3 heading ‘23′

column Total format 9999

column status format a8

column member format a40

column archived heading ‘Archived’ format a8

column bytes heading ‘Bytes|(MB)’ format 9999

Ttitle ‘Log Info’ skip 2

select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type

from v$log l, v$logfile f

where l.group# = f.group#

/

Ttitle off

prompt =========================================================================================================================

Ttitle ‘Log Switch on hour basis’ skip 2

select to_char(FIRST_TIME,’DY, DD-MON-YYYY’) dia,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0))) d_0,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0))) d_1,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0))) d_2,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0))) d_3,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0))) d_4,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0))) d_6,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0))) d_7,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0))) d_9,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0))) d_10,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0))) d_11,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0))) d_12,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0))) d_13,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0))) d_14,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0))) d_15,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0))) d_16,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0))) d_17,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0))) d_18,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0))) d_19,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0))) d_20,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0))) d_21,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0))) d_22,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0))) d_23,

count(trunc(FIRST_TIME)) Total

from v$log_history

group by to_char(FIRST_TIME,’DY, DD-MON-YYYY’)

order by to_date(substr(to_char(FIRST_TIME,’DY, DD-MON-YYYY’),5,15) )

/

Ttitle off

How to check for LOGGING/NOLOGGING objects in the DB:

Two example methods of querying the database for this information:

select owner , table_name, index_name
from dba_indexes
where logging=’NO’;

select tablespace_name, logging
from dba_tablespaces

/

Kind Regards,

Francisco Munoz Alvarez

Don’t miss the global launch webcast of Oracle Enterprise Manager 10g Release 5, this will be the 03/03/2009 09:00 AM (Pacific Standard Time), to assist please register here.

In the webcast you will learn about key new capabilities like:

• New Siebel 8.1.1 support, New management capabilities for Oracle Beehive and Oracle BRM
• New monitoring accelerators for Oracle Applications
• Comprehensive management for Oracle WebLogic Server and Oracle Service Bus
• More Oracle Database advisors, Real-time SQL monitoring, New security management, New cloning and masking automation and New change management automation
• New grid automation capabilities
• New virtualization management with an application perspective - manage both physical and virtual environments in a single console!

You can Download the OEM 10GR5  for Microsoft Windows 32-Bit and Linux x86  here.

Save this date on your agenda, and enjoy it