Oracle 11g - Virtual Columns

In Oracle 11g has allowed database Tables to have virtual columns. These virtual columns can be more specifically called as derived column as these columns derived their data from other non-virtual columns of the same table. They are like any other table column but their data is not stored in the database and so they consume no disk space. They got their value from an expression which can include

Columns from the same Table

Some constants

Any SQL functions

A User-defined PL/SQL functions

You cannot explicitly write the data to the virtual column. Virtual columns can be used in queries, DML and DDL statement. You can index as well as collect statistics on them.

Click here to get the complete details about the 11g Virtual Columns

Oracle 11g Automatic Diagnostic Repository - Part 2

In this Part , you will see that how we can create a package which will collect all files required for a specific erros and can be send to Oracle Support. It will also explain the Health Monitor Report and its significance.

Click Here to get more details

Oracle 11g RAC Part 6 - Create Non-RAC Database instance

In this paper, I will present as how we will move Oracle Non-RAC database instance to RAC instance. So I will create non-RAC instance in RAC ASM instance which was created in last paper..

We can use the same scenario where let's suppose you have Production running in ASM instance and you are creating new RAC environment. Build the RAC environment as covered earlier. Now create the Standby non-RAC instance but use ASM storage of RAC Instance. This will save time in moving production to RAC environment.

Click here to get details about to create Non-RAC instance using RAC ASM storage

Oracle 11g Database Reply Part 3 - Replay Capture Workload

We have already covered as how we can collect the Workload and then process it to make it ready to be replayed on Test or Target Database server. This target server is the server which is either upgraded in terms of Hardware or Software and the replay will give us an idea as how the application behave on new environment

Click here to get more details as how to replay the Workload

Oracle 11g Temporary Tablespace Enhancements

Oracle 11g has added lots of new enhancements so as to monitor and Manage Temporary tablespaces and Temporary segments. These features will help database administrator to predict and controlled the growth of the TEMPORARY tablespace. One of the new enhancements allows you to specify the specific TEMP tablespace to GLOBAL TEMPORARY TABLE and reduce I/O contention among temporary
segments and other types of segments.

Click here to get the complete Details of Temporary Segment/tablespace enhancements

Oracle 11g RAC Part 5 - ASM Instance Creation

In Oracle RAC deployment, we will use Automatic Storage Management [ASM] to store Database files for all Database instances. In order to use ASM for datafile Storage, we need to create ASM instance which will make the Storage available for Database instances shared storage.

Click here to get more details about the configuration

Oracle 11g Database Replay Part 2- Process the Capture Load

In order to start the Replay of the Capture Workload, we need to process the Capture process recorded *.rec file. This should be done outside of Source database and preferably on the Target Database server.

Click here to get the Detail about processing the Captured Workload files

Oracle 11g Read Only Tables

Prior to Oracle 11g, we are allowed to make either the Database or the Tablespace as Read-only with single command. Oracle 11g allow drilling down this feature more to table level. We can now make a table Read-only with a single command. You cannot perform DML operation like Insert/Delete/Update on Read only tables. You can continue to perform DDL operation on the Table like Create Index/Partition etc including Drop Table command. You can revert back the Table to Read write mode as and when required.

Click here to get more details about the Read only tables

Oracle 11g Database Replay Part 1- Capture the Changes on Source Db Server

Oracle 11g DB Replay is very good feature that will be usefull in various scenarios. I will provide details as how you can use Oracle 11g database replay feature. This feature will help DBA’s to perform Real-world load testing similar to production environment on QA or new platform. Oracle 11g allows to capture Database Workload from production environment at the cost of some system resources and then this load can be played on any other system irrespective of operating system and architecture and will help management and technical people. This feature will not only help the companies in performing Real-world testing but also save money to buy expensive testing tools. It will also save lots of time spent in configuring third party testing tools and moreover will provide more accurate testing results

DB Replay Steps

Take a Backup of Source database.
You can use any Database backup procedure that can take the Source Database/schema backup and can be restored on Target Database.

Configure Oracle EM database console on both Source and Target Database If you want to use EM console which is recommended tool for Database replay setup.

Start the Capture process to record Database activities on Source Database

Apply load to the Database I used Visual Basic application to access the database and capture the corresponding load in .rec files created by DBReplay on the Database server.

Restore the Backup taken in First Step on Target Database.This step will help to compare the workload on Source and Target Database.

Reset the Time on Target Database close to Source Database Capture Time – Optional
This will help to avoid any divergence in test result while comparing the workload due to SQL function like SYSDATE or SYSTIMESTAMP

Copy the Captured Data as physical files on Source Server to Target Database Server These are the .rec and some .dmp physical file that stores the Captured Workload information of Source Database server.

Processed the Captured workload on Target Database

Replay the Workload on Target Database

Analyze the Report

Click Here to Get the details about the process of Capturing the Database changes on Source Database server

Oracle 11g RAC part 4 - Oracle 11g RAC software Installation

When you are done with Oracle 11g Clusterware installation, you are ready to do the Database software. Here you need to plan as how many Instance will be running in the RAC setup. It is recommended to have separate Oracle Home for each instance and this rule applies to ASM instance. So if you are planning to have single Instance using ASM storage, then we will install the software Two time on this Node. I am using single installation for demo purpose.

Click Here to get the details about the Installation

Oracle 11g Security Enhancements - Part 1

In this paper, we will discuss some of the 11g database User password,auditing, tablespace security features.

Click here to view the Part 1 of the security enhancements

Oracle 11g Automatic Diagnostic Repository - Part 1

Oracle 11g has introduced new fault diagnostic framework to detect, maintain and handle diagnostic data. Automatic Diagnostic Repository (ADR) is the core of this framework and considered as Black box for the Database error handling. It is central, file-based repository created outside of the database so that it can be accessed even when database is not available. This file-based repository can be accessed with new command line utilities ADRCI and Enterprise Manager.

In this Part 1, I will cover the detail about the ADR [ Automatic Diagnostic Repository]. It will explain as how various old Oracle Destination like Background|User|Core are ignored and replace by new framework in 11g. It will explain how Oracle collect all required data for critical errors occured in the database automatically.

Check the detail in this article

Oracle 11g RAC Part 3 - Install Oracle 11g CLusterware on Single Node

This is in continuation of Oracle 11g RAC deployment where I am working as how we can move existing NON-RAC environment to 11g RAC without affecting the existing production environment. We will install and configure Oracle 11g RAC component on single Node which is not currently in production. When we are done in configuring all RAC component on Node 1 then we will convert the existing production non-RAC database to RAC instance. When it is done, we will start adding second Node to RAC environment.

In last 2 Setup we have covered Oracle 11g Pre-Requisite setup and Configured ASMlib and RAW device used by Oracle components.

Now click here to start with Oracle 11g Clusterware installation on first Node.

Oracle 11g RAC Part 2- Configure RAW Devices and ASM lib

In order to Deploy RAC, we need two Raw device to store Voting Disk and OCR file. In addition, we need a shared device to store the Oracle Database files and we will use Oracle ASM [Automatic Storage Management] for this storage. In order to use ASM, we will configure ASMLib provided by Oracle for Linux platform. More Detail is available at
Install and configure ASMLib on Single Node
Configure and Raw Devices on Single Node

Oracle 11g RAC Part 1 - Check hardware/Network/Storage Infrastructure

This is first part of 11g RAC deployment where we will check the Hardware/Network/Stroage Infrastructure pre-requisite. I had provided all kinds of Cluvfy [Cluster Verification Utility] commands so as to verify that your environment is ready for Oracle RAC deployment. Click here to Perform Pre-Installation on Single Node

Oracle 11g RAC Deployment

In this part of the Blog, I will follow the below mentioned path to deploy 2-Node Oracle 11g RAC in Linux environment :
1. Perform Pre-Installation on Single Node
2. Install and configure ASMLib and Raw Devices on Single Node
3. Install Oracle 11g Clusterware on Single Node
4. Install Oracle 11g Software on Single Node
5. Configure Clustered Oracle ASM Instance on Single Node
6. Configure Non-RAC Instance on Single Node
7. Convert Non-RAC instance to RAC instance using the Same Clustered ASM Instance
8. Perform Pre-Installation on Second Node
9. Configure ASMLib and Raw Devices on Second Node
10. Add Oracle 11g Clusterware to Second Node
11. Add Oracle 11g Software to Second Node
12. Add ASM as well as Database instance to Second Node

Oracle 11g Invisible Indexes

Oracle 11g has introduced new Invisible Indexes. Invisible Indexes are not used by Oracle optimizer unless OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter is set to TRUE at the session or system level. The default value for this parameter is FALSE. This will help DBA as well as developer to utlize some indexes which are meant for some particular queries without worrying that the new index will affect any other query.

Check more details at
http://www.datasoftech.com/library/DSI_11g_Inv.pdf

See you again tomorrow

Oracle 11g Series

I am going to put several Oracle Features in the next 2 months which will cover most of the important Oracle 11g features.

Enjoy and Good Luck
Inderpal S Johal