• 11g Optimizer Plan Stability

    One of the critical aspects of any database upgrade is to ensure optimizer plan stability where tried and tested execution plans are not altered in any way by the Oracle optimizer after a database upgrade has taken place. One of the 11g new features which is SQL Plan Management and…

  • 11g ASM rolling upgrade from 11.1.0.6 to 11.1.0.7

    In Oracle 11g, we can now do rolling upgrades of the ASM instanaces in a RAC cluster configuration. In this example shown below, one of the ASM instances is being upgraded to 11.1.0.7 while the other instance is still running on version 11.1.0.6. While the ASM software is being upgraded,…

  • 11.1.0.6 Clusterware upgrade to 11.1.0.7

    The following screenshots are taken from a RAC Clusterware upgrade from 11.1.0.6 to 11.1.0.7 on AIX 64 bit platform. We encountered an issue related to Bug 8925669: ROOT111.SH 0509-150 DEPENDENT MODULE LIBTTSH11.SO COULD NOT BE LOADED. I have provided the workaround we used in this case and which did work…

  • Duplicate from active database – ASM Primary to ASM Standby

    In my earlier post 11g Standby Database Creation Without Any RMAN Backups I had illustrated how to create a standby database using the 11g Active Duplication feature which enabled us to create a standby database without any backup taken of the primary database. In this case it was a ASM…

  • 11g Standby database creation without any RMAN backups

    Quick steps to set up a 11g Standby database with Active Data Guard using the Active Duplication feature available in 11g where we can create a standby database without having to take a backup on the primary database. Datafiles are copied over the network. Primary machine – OATU036 Standby machine…

  • How to manually clear EM Grid Control alerts

    We can use the procedure outlined below to clear outstanding warning and critical alerts which are displayed on the EM Grid console home page. Normally these alerts get cleared automatically when the event ot threshold which caused these events in the first place is longer in the ‘critical’ or ‘warning’…

  • 11g Release 2 RMAN Backup Compression

    Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well. The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded. To…

  • 11g RMAN Virtual Private Catalog

    In Oracle 11g, we can grant restricted access to the RMAN catalog to some users so that they can only access a limited set of databases that are registered in the RMAN catalog. This is done by creating a Virtual Private Catalog which in turn will grant a particular user…

  • RMAN Restore Validate Examples

    We can use the RMAN RESTORE VALIDATE command to check and verify the integrity of the backups which are stored on tapes or disk as well. A point to keep in mind is that the RESTORE DATABASE VALIDATE command will only check for the datafile backups and will not check…

  • 11g Flashback Data Archive – Part One

    Businesses are increasingly realizing the immense value that historical data can provide to help them understand market trends and customer behaviour as well. Further, many regulatory and compliance policies like SOX and BASEL-2 mandate retention of historical data. Until now, this unfortunately involved application rewrites,administration overheads or costly third-party software…

  • Oracle Restart – new in 11g R2

    Similar to the clusterware processes in a RAC environment, in 11g R2 even for a standalone instance, functionality is now available to automatically restart components like the database, listener, ASM diskgroup, service etc in the event of their failure. This feature is called Oracle Restart and it runs out of…

  • 11g Release 2 Grid Infrastructure Installation

    Let us have a look at the installation screenshots of 11g Release 2 on a Red Hat Linux platform. Currently the 11g R2 software has only been released for the Linux platform with release dates for other platforms like Solaris and AIX set for sometime end October to mid November….

  • 11g Release 2 ACFS (Automatic Storage Management Cluster File System)

    The following are some of the new ASM related features introduced in 11g R2: In Oracle 11g Release 2, ASM and the Oracle Clusterware are now installed in a common home called the Grid Infrastructure Home. The clusterware files like the Voting Disk and the Cluster Registry can no longer…

  • Cross Platform Transportable Tablespace using ASM with Oracle XML DB

    In one of my earlier posts How To Transport a Tablespace from 10g ASM to 11g ASM on AIX we discussed how to transport a tablespace stored on ASM storage from Windows to AIX using the DBMS_FILE_TRANSFER package. We will now look at how the same thing can be done…

  • ASMCMD examples

    asmcmd is a command line tool that we can use to manage Oracle ASM instances, ASM disk groups, files and dierctories, templates very much as in the same way we would do while working with say a UNIX file system. ASMCMD can be launched in interactive or non-interactive modes and…

  • 10g EM Grid Control Notification Rules and Corrective Action Jobs

    In 10g Enterprise Manager Grid Control, we can create corrective action or ‘fixit’ jobs which can automatically run if say a metric threshold is crossed or a particular event occurs. For example, we may have situations where we monitor the disk space used by the archive log files and as…

  • 11g RMAN Substitution Variables

    We can now use SQL*PLUS type substitution variables in RMAN like &1, &2 and so on and the same can be linked to shell scripts as shown below with the new clause USING where we are passing the Incremental Level and the backup tag to the actual RMAN command. Also…

  • 11g Release 2 Edition-based Redefinition

    11g Release 2 Edition-based redefinition enables you to upgrade or change the database component of an application while it is in use, thereby minimising or eliminating down time which was earlier required when an object like a procedure required to be modified, but we could not do it without any…

  • 11g Pending and Published Statistics

    Prior to Oracle 11g, the default behaviour was to make statistics available for the optimizer to use as soon as they were gathered. While statistics are required to enable the optimizer to generate optimal execution plans, sometimes just by gathering fresh statistics tried and trusted execution plans can abruptly change…