Poor ASM parallel read performance in Oracle - Bug 5576584

Problem Description
Oracle database file system is migrated to ASM from local file system and now it is observed that batched IO operations are getting processed as synchronized serialized IOs. The queries run slow and there are no parallel reads.

Cause of the Problem
The poor performance of ASM parallel read is caused by Oracle bug 5576584.

Solution of the Problem
This problem really indicates serious oracle ASM file system flaw. This problem exist up to Oracle database version 10.2.0.3 and fixed in Oracle database version 10.2.0.4 Server Patch Set. So there is no workaround exist. You need to install 10.2.0.4 Patch Set.

Upgrade to 11.1.0.7 from 11.1.0.6 database fails with ORA-16024

Problem Description
The database version is 11.1.0.6 and it is tried to upgrade the database to 11.1.0.7.
The 11.1.0.6 database is configured with Data Guard and it is primary database.
Now, whenever it is tried to patch the database to upgrade to 11.1.0.7 it fails with following error,
'ORA-16024 : LOG_ARCHIVE_DEST_1='LOCATION= XXXXX' CAN'T PARSED.
Then the upgrade process abort.

Cause of the Problem
The problem is happened due to Oracle bug 8225933.

Problem Investigation
In the parameter file (spfile.ora or init.ora) for an 11.1.0.6 database, the definition for one or more parameters is split over multiple lines. For example the log_archive_dest_1 and log_archive_dest_2 parameters are written on more than one line in the 11.1.0.6 database parameter file like below :

log_archive_dest_1 = "LOCATION=USE_DB_RECOVERY_FILE_DEST 
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) 
DB_UNIQUE_NAME=ORCLP"
log_archive_dest_2 = "SERVICE=standby ASYNC COMPRESSION=ENABLE
VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCLP" 

Now, while upgrading to 11.1.0.7 using DBUA to patch the 11.1.0.6 database to 11.1.0.7, these parameters are truncated to a single line as below

log_archive_dest_1       = "LOCATION=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_2       = "SERVICE=standby ASYNC COMPRESSION=ENABLE" 

Since the init parameters are truncated by dbua, this has caused problems with the rolling upgrade in the data guard environment.

Solution of the problem
To workaround the issue, update the parameter file and define the parameter on one line (rather than on multiple lines). For example:

log_archive_dest_1 = "LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLP
log_archive_dest_2 = "SERVICE=standby ASYNC COMPRESSION=ENABLE VALID_FOR=ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLP" 

Upgrade to 11.1.0.7 from 11.1.0.6 database fails with ORA-16024

Problem Description
The database version is 11.1.0.6 and it is tried to upgrade the database to 11.1.0.7.
The 11.1.0.6 database is configured with Data Guard and it is primary database.
Now, whenever it is tried to patch the database to upgrade to 11.1.0.7 it fails with following error,
'ORA-16024 : LOG_ARCHIVE_DEST_1='LOCATION= XXXXX' CAN'T PARSED.
Then the upgrade process abort.

Cause of the Problem
The problem is happened due to Oracle bug 8225933.

Problem Investigation
In the parameter file (spfile.ora or init.ora) for an 11.1.0.6 database, the definition for one or more parameters is split over multiple lines. For example the log_archive_dest_1 and log_archive_dest_2 parameters are written on more than one line in the 11.1.0.6 database parameter file like below :

log_archive_dest_1 = "LOCATION=USE_DB_RECOVERY_FILE_DEST 
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) 
DB_UNIQUE_NAME=ORCLP
log_archive_dest_2 = "SERVICE=standby ASYNC COMPRESSION=ENABLE
VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCLP" 

Now, while upgrading to 11.1.0.7 using DBUA to patch the 11.1.0.6 database to 11.1.0.7, these parameters are truncated to a single line as below

log_archive_dest_1       = "LOCATION=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_2       = "SERVICE=standby ASYNC COMPRESSION=ENABLE" 

Since the init parameters are truncated by dbua, this has caused problems with the rolling upgrade in the data guard environment.

Solution of the problem
To workaround the issue, update the parameter file and define the parameter on one line (rather than on multiple lines). For example:

log_archive_dest_1 = "LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLP
log_archive_dest_2 = "SERVICE=standby ASYNC COMPRESSION=ENABLE VALID_FOR=ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLP" 

EID Ul Adha 2010 Greetings to everyone

Alhamdulillah, after 1 year of Arabic calender again EID Ul Adha is coming to our life. This is 1431 Hizri in Islamic calender, 2010 in English Calender. EID is going to be celebrated on the 10th day of the 12th and the last Islamic month of Dhu al-Hijjah.

I hereby attaching few EID cards for everyone and wish a best life to every people in the earth.

Script to check details database growth per month

Abstract
This script will report the details of database growth per month.

Execution Environment:
Any client environment which can connect to database and execute SQL. For example, SQL*plus, iSQL*plus.

Required Privileges:
Need select privilege on sys.v_$datafile view.

Usage:
Save main script in a file named DB_GROWTH_PER_MONTH.SQL. Then,
if you are outside sqlplus then issue,
$ sqlplus username/password @DB_GROWTH_PER_MONTH.SQL
or if you are inside sqlplus then issue,
SQL> @DB_GROWTH_PER_MONTH.SQL

Instructions to use:
Copy the codes into a file named DB_GROWTH_PER_MONTH.SQL and run that script.

Description of the Script
The script will report the total size of all datafiles, grouped by the month in which they
were created. Note that, this script will report based on CREATION_TIME column of V$DATAFILE/V_$DATAFILE view which holds the timestamp of the datafile creation. So if you don't have datafile created in a month you will not get information of that month.

Sample Input:
No input is required as it will report based on datafile in the database.

Sample Output:

Month          Growth in MB
-------------- ------------
2010 November             2
2010 October            100

Main Script

select to_char(creation_time, 'YYYY Month') "Month",
    sum(bytes)/1024/1024 "Growth in MB"
    from sys.v_$datafile
    where creation_time > SYSDATE-365
    group by to_char(creation_time, 'YYYY Month'); 

Execution Result

SQL> start "E:\All Projects\Arju\Scripts\DB_GROWTH_PER_MONTH.SQL"

Month          Growth in MB
-------------- ------------
2010 November             2
2010 October            100