[AGIS STAMATOPOULOS] Installation of Oracle 10gR2 database on RedHat Enterprise Linux/Oracle Enterprise Linux 5 (x86_64)

Installation of Oracle 10gR2 database on RedHat Enterprise Linux/Oracle Enterprise Linux 5 (x86_64)

WEDNESDAY, NOVEMBER 17, 2010 | AGIS STAMATOPOULOS


As root

1) Check Hardware Requirements

# grep MemTotal /proc/meminfo >= 1Gb
# grep SwapTotal /proc/meminfo 
Between 1024 MB and 2048 MB 1.5 times the size of RAM 
Between 2049 MB and 8192 MB Equal to the size of RAM 
More than 8192 MB 0.75 times the size of RAM 

# df -h 
400 MB of disk space in the /tmp directory 
Between 1.5 GB and 3.5 GB of disk space for the Oracle software, depending on the installation type 
1.2 GB of disk space for a preconfigured database that uses file system storage (optional) 
To determine the amount of free disk space on the system, enter the following command

2) Kernel Requirements 

# uname -a >= 2.6.18-8 

3) Disable SELinux or switch it to "Permissive" mode
#/usr/sbin/sestatus 
SELinux status: disabled
or 
#/usr/sbin/getenforce
Disabled

If you see the previous output SELinux is disabled.

If SELInux is enabled then run :

Commands, as root: 
====================== 
getenforce (returns "Enforcing") 
setenforce 0 
getenforce (returns "Permissive") 

End to restore it to enforcing 
setenforce 1 
getenforce (returns "Enforcing") 

For more info check Oracle Metalink Note: 454196.1

4) Installing Required packages

Use rpm -qa | grep package_name for the following packages

binutils-2.17.50.0.6-2 (x86_64)
compat-db-4.2.52-5.1 (x86_64)
compat-libstdc++-296-2.96-138 (i386)
compat-libstdc++-33-3.2.3-61(x86_64)
compat-libstdc++-33-3.2.3-61 (i386)
control-center-2.16.0-14 (x86_64)
gcc-4.1.1-52 (x86_64)
gcc-c++-4.1.1-52 (x86_64)
glibc-2.5-12 (x86_64)
glibc-2.5-12 (i686)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12 (i386)
glibc-headers-2.5-12 (x86_64)
ksh-20060214-1.4 (x86_64)
libaio-0.3.106-3.2 (x86_64)
libgcc-4.1.1-52 (i386)
libgcc-4.1.1-52 (x86_64)
libgnome-2.16.0-6 (x86_64)
libgnomeui-2.16.0-5 (x86_64)
libgomp-4.1.1-52 (x86_64)
libstdc++-4.1.1-52 (x86_64)
libstdc++-devel-4.1.1-52 (x86_64)
libXp-1.0.0-8 (i386)
make-3.81-1.1 (x86_64)
sysstat-7.0.0-3 (x86_64)

The version of the above packages is the lower needed. So if you are in higher you are OK

e.g
# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep binutils
binutils-2.17.50.0.6-14.el5 (x86_64)

5) Set the kernel parameters

kernel.shmall = physical RAM size / pagesize (getconf PAGESIZE) -- If the defaults are greater then leave it.
kernel.shmmax = 1/2 of physical RAM, but not greater than 4GB -- If the defaults are greater then leave it.
kernel.shmmni = 4096 
kernel.sem = 250 32000 100 128 
fs.file-max = 512 x processes (for example 65536 for 128 processes) 
net.ipv4.ip_local_port_range =9000 65500
a.) The runInstaller (OUI) checks may expect this to be the old guidance of “1024 65000”. The new guidance from Oracle development is “9000 65500”. Please allow the runInstaller (OUI) to proceed with the new guidance from Oracle development.
net.core.rmem_default = 262144 
net.core.rmem_max = 262144 
net.core.wmem_default = 262144 
net.core.wmem_max = 1048576 

# vi /etc/sysctl.conf 

Set the values and then run :

# sysctl -p 

6) Add Oracle user and groups and create the ORACLE_BASE directory for Oracle Software and database.

# groupadd oinstall 
# groupadd dba 
# useradd -g oinstall -G dba oracle 
# passwd oracle 

e.g
# mkdir -p /u01/app/oracle 
# chown -R oracle:oinstall /u01/app/oracle

7) Set the session limits for Oracle User

Set the following to the /etc/security/limits.conf 
oracle soft nproc 2047 
oracle hard nproc 16384 
oracle soft nofile 1024 
oracle hard nofile 65536

Set the following to the /etc/pam.d/login 
session required pam_limits.so 

Add the following to /etc/profile if Oracle user will use the bash shell.

if [ $USER = "oracle" ]; then 
ulimit -u 16384 
ulimit -n 65536 
fi 


As oracle user

8) Login as Oracle and change the .bash_profile

Add the following in order to begin the installation

export ORACLE_BASE=/u01/app/oracle

export ORACLE_SID=orcl # You can not set the ORACLE_SID if you do not want write now

export DISPLAY=YOUR_XTERM_IP:0 in order to you an X session for the installation

unset ORACLE_HOME

$ . ./.bash_profile

to apply the new settings

9) Put the software for 10gR2 to a desired server directory.

10201_database_linux_x86_64.cpio.gz

10201_companion_linux_x86_64.cpio.gz

p6810189_10204_Linux-x86-64.zip

Unzip the database and run 

$ gunzip 10201_database_linux_x86_64.cpio.gz

$ cpio -idmv < 10201_database_linux_x86_64.cpio

$ cd database

$./runInstaller –ignoreSysPrereqs

Then unzip the companion and run

$ gunzip 10201_companion_linux_x86_64.cpio.gz

$ cpio -idmv < 10201_companion_linux_x86_64.cpio

$ cd companion

$./runInstaller –ignoreSysPrereqs

Finally unzip the 10.2.0.4 Patch Set and run (At the time this post was written only the 10.2.0.4 patch was available): 

$ unzip p6810189_10204_Linux-x86-64.zip

$ cd Disk1

$ ./runInstaller –ignoreSysPrereqs

10) Create the database

Add the following to the .bash_profile

export ORACLE_HOME=[YOUR_ORACLE_HOME]

export PATH=$ORACLE_HOME/bin:.:$PATH

Run 

$ . ./.bash_profile

$ dbca

Create your desired database and you finished

After that run 

$ netca 

To create a listener

ATTENTION if your host name is more than 32 characters then you are about to have the following situation:

If you have choose to install EM and during its creation you get from dbca

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

then apply the following workaround:

1) Ignore error and continue with database creation

2) After successful finish of dbca do :

Verify the case using sqlplus

SQL> select length(host_name) from v$instance where rownum=1;

LENGTH(HOST_NAME)

-----------------
36

Here the length is 36 (>32) and for 10.2 this is a bug and em repository creation fails.

So

a) Make a backup copy of the file

$ cp $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql 
$ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql_bak

b) Change the l_host_name variable in self_monitor_post_creation.sql to have 128 characters

The l_host_name variable needs to be changed twice in the script.
$ vi $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql

c) Drop and recreate the DB Control configuration files and repository

$ $ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop
$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos create

Check your em by using the URL mentioned.

REFERENCES
Metalink Note ID 421308.1, 335118.1

Source: http://agstamy.blogspot.com/2010/11/installation-of-oracle-10gr2-database.html

The Seven Deadly Habits of a DBA . . and How to Cure Them

The Seven Deadly Habits of a DBA . . and How to Cure Them
by Paul Vallée

Calling widespread bad habits in database administration "deadly" may seem extreme. However, when you consider the critical nature of most data, and just how damaging data loss or corruption can be to a corporation, "deadly" seems pretty dead-on.

Although these habits are distressingly common among DBAs, they are curable with some shrewd management intervention. What follows is a list of the seven habits we consider the deadliest, along with some ideas on how to eliminate them.

Habit #1. THE LEAP OF FAITH: "We have faith in our backup."
Blind faith can be endearing, but not when it comes backing up a database. Backups should be trusted only as far as they have been tested and verified.
Cures:
§ Have your DBAs verify that the backup is succeeding regularly, preferably using a script that notifies them if there's an issue.
§ Maintain a backup to your backup. DBAs should always use at least two backup methods. A common technique is to use those old-fashioned exports as a backup to the online backups.
§ Resource test recoveries as often as is practical. An early sign that your DBA team is either overworked or not prioritizing correctly is having a quarter go by without a test recovery. Test recoveries confirm that your backup strategy is on track, while allowing your team to practice recovery activities so they can handle them effectively when the time comes.

Habit #2. GREAT EXPECTATIONS: "It will work the way we expect it to. Let's go ahead."
Although not user friendly in the traditional sense, Oracle is very power-user friendly— once you've been working with it for a while, you develop an instinct for the way things "should" work. Although that instinct is often right, one of the most dangerous habits any DBA can possess is an assumption that Oracle will "just work" the way it should.
Cures:
§ Inculcate a "practice, practice, practice" mentality throughout the organization. DBAs need to rehearse activities in the safe sandbox of a test environment that's designed to closely mimic the behaviour of the production system. The organization needs to allow the time and money for them to do so.
§ Pair inexperienced DBAs with senior ones whenever possible—or take them under your own wing. New DBAs tend to be fearless, but learning from someone else's experience can help instill some much needed paranoia.
§ Review the plans for everything. It's amazing how often DBAs say, "I've done that a hundred times, I don't need a plan." If they're heading into execution mode, they absolutely need a plan.

Habit #3. LAISSEZ-FAIRE ADMINISTRATION: "We don't need to monitor the system. The users always let us know when something's wrong."
If you depend on the users to inform the DBA team that there's a problem, it may already be too late.
Cures:
§ Install availability and performance monitoring systems so that issues are identified and resolved before they cause service-affecting failures.
§ Avoid post-release software issues by working with developers and testers to ensure that all production-ready software is stable and high-performance.

Habit #4. THE MEMORY TEST: "We'll remember how this happened, and what we did to get things going again."
It may seem impossible that a DBA team would forget a massive procedure that took them weeks to get right, and yet it happens all the time. In order to prevent recurring mistakes and take advantage of gained experience, documentation is essential.
Cures:
§ Require that your DBAs maintain a comprehensive documentation library and activity diary, including a significant level of rationale, syntax, and workflow detail.
§ Provide your team with groupware on your intranet so that these documents become searchable in an emergency.
§ Enforce the discipline of documentation and check it periodically. Ask your DBAs: When was this tablespace created, by whom, and with what SQL? What tasks were performed on a particular day? If they can't answer quickly, you'll know they've gone back to relying on memory.

Habit #5. THE BLAME GAME: "Don't look at me, it's the developer's fault that SQL is in production"
Some DBAs have a real "us versus them" mentality when it comes to developers in their organization. They see themselves not as facilitators helping the developers develop quality code from a database standpoint, but rather as guardians who prevent poor-quality code from making it into production. This might seem like semantics, but a confrontational relationship between developers and DBAs results in a lack of developer initiative and significant slowdowns in release cycles.
Cures:
§ Select DBAs who understand it's their responsibility to work as an integrated team with the developers they support.
§ Cultivate a team attitude by structuring continuous DBA involvement in every project rather than at review milestones.
§ Consider assigning an individual DBA in a developer support role. If it's clearly in the job description, there's more motivation to do it well.

Habit #6. THE SOLO ACT: "I know what I'm doing and don't need any help."
Database administration is increasingly complex and even the most senior DBAs can't possibly know every last detail. DBAs have different specialties, which need to be culled and utilized. When DBAs feel like they know, or should know, everything, they don't ask questions and miss out on valuable knowledge they could be gaining from others.
Cures:
§ Foster a teamwork culture where it's acceptable for DBAs to admit they don't know the answer and to ask for help.
§ Encourage your DBAs to seek out an outside peer group as a forum for brainstorming and testing their assumptions. No single person can match the expertise and experience of even a relatively small group.
§ Provide a safety net of tech resources such as reference materials, courses, and outside experts or consultants on call.

Habit #7. TECHNO-LUST: "Things would work so much better if only we had..."
DBAs are often on top of the latest technology, which can help them do a superlative job. But when the desire for new technology causes DBAs to recommend unnecessary hardware purchases or software add-ons, costs tend to skyrocket quickly—as do problems.
Cures:
§ Never upgrade your hardware infrastructure without first exhausting all tuning opportunities. Remember, ten years ago enormous enterprises were run on servers one-tenth the capacity—all thanks to necessity and skill.
§ Never consent to using advanced or new features until you're well aware of the ongoing maintenance commitment and resulting costs.
§ Watch out for DBA support software that presents friendly GUI interfaces for difficult tasks. This type of interface allows a beginner DBA to act as an intermediate DBA under certain circumstances, but simultaneously prevents that beginner from learning the actual skills behind the tasks. Moreover, these tools tend to hide real risks from the DBA, making potentially damaging activities as easy as point-and-click.

Whether it takes a twelve-step program or one tiny adjustment, all of these deadly DBA habits can be kicked. Of course, the first step is recognizing the problem. By starting with this list and doing a careful inventory of the successes and failures in your team's database administration, you'll be well on your way to finding a cure.

-------------------------------------------------------------------------------------------------
Paul Vallée is the Executive Chairman and Founder of Pythian, a leading global database application and infrastructure services company for Oracle, MySQL and SQL Server.

Toad World PIPELINE Newsletter (May '10 Issue) has my Second Technical Paper

The May 2010 Edition of Toad World PIPELINE Newsletter has published my second Oracle Technical Paper "Database Disaster Recovery using only RMAN Backups".

Infact, they asked me to re-write the paper using Oracle 10g or 11g so that we could relate it with the supported versions. I have chosen Oracle 10g R2 (10.2.0.4) Standard Edition on Windows O/S to demonstrate the same.

Here is the link to Toad World PIPELINE Newsletter (May '10 Issue):
http://www.quest.com/ecard/28941/default.htm

Hope it is a nice read.

Published my Second Oracle Technical Paper

Today, I have successfully published my Oracle Technical Paper titled "Database Disaster Recovery using only RMAN Backups" under My Oracle Support Community (formerly known as Oracle Customer Knowledge Exchange)
Link: https://communities.oracle.com/

The technical paper demonstrates how an Oracle Database can be recovered or reconstructed by using only the RMAN Backup files in case of a complete server crash. On this blog, you know it as "Disaster Recovery using RMAN Backups".

The access to the published document in My Oracle Support Community would require customer logon credentials. So if you are interested to know more about my technical paper, feel free to email me at zk.oracle@gmail.com for the PDF file.

Default Sort Behavorial Change for GROUP BY queries without ORDER BY clause in Oracle 10g Database

After our successful production database migration to 10gR2 (10.2.0.4), we came across a few user complaining about sorting issue in specific screens. On further investigation, we found out that some screens having fields with list of data values were not ordered, as they were in 9iR2.

Upon a little googling we found an interesting case at Jaffar's blog "Change behavior of GROUP BY clause in Oracle 10g.". It seems he had faced the same issues when he migrated from 9i to 10g, and has well documented the case with resolution in his post.

Basically, in 9i, SQL Queries having the GROUP BY clause only had a default behavior of being ordered. These SQLs in 9i appeared sorted (despite any ORDER BY clause) because of the default sorting behavior seen in the execution plan as "SORT GROUP BY".

On the other hand, 10g now has a sorting behavorial change, in which the SQL Queries having the GROUP BY clause only with no ORDER BY clause, there will be no default sorting done, which is seen in the execution plan as "HASH GROUP BY".

The 3 possible resolutions are as follows:

1. Change the affected SQL Queries to embedd ORDER BY Clause, or
2. Set "_gby_hash_aggregation_enabled" = FALSE at session level or system level, or
3. Set optimizer_features_enabled=9.2.0

In our case, as we have 3rd party ERP software, we don't have the source code of most of the affected screens. So, we could not choose the 1st option entirely (except for reports, and backend queries). Choosing the 3rd option defeats to purpose of migrating to 10g as most of the 10g optimizatin features will not be available, but this option was the last resort. We tested out the 2nd option and it worked perfectly fine.

Let me demonstrate how I implemented the 2nd option on my production 10gR2 database.

Before changing "_gby_hash_aggregation_enabled" parameter:

SQL> set autotrace on
SQL> select owner,count(1) from dba_objects where object_type='VIEW' group by owner;

OWNER COUNT(1)
------------------------------ ----------
WKSYS 79
MDSYS 59
DMSYS 1
WK_TEST 6
CTXSYS 54
SYSTEM 12
EXFSYS 56
DBSNMP 1
ORDSYS 5
SYSMAN 136
XDB 2
SYS 2885
WMSYS 112
..... (some element removed)
LBACSYS 57
25 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 651175835

------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------
0 SELECT STATEMENT 69320 1895K 195 (6) 00:00:03
1 HASH GROUP BY 69320 1895K 195 (6) 00:00:03
2 VIEW DBA_OBJECTS 69320 1895K 192 (4) 00:00:03
3 UNION-ALL
* 4 FILTER
* 5 HASH JOIN 85274 7744K 191 (4) 00:00:03
6 TABLE ACCESS FULL USER$ 86 1290 3 (0) 00:00:01
* 7 TABLE ACCESS FULL OBJ$ 85274 6495K 187 (3) 00:00:03
* 8 TABLE ACCESS BY INDEX ROWID IND$ 1 8 2 (0) 00:00:01
* 9 INDEX UNIQUE SCAN I_IND1 1 1 (0) 00:00:01
* 10 TABLE ACCESS BY INDEX ROWID SUM$ 1 26 0 (0) 00:00:01
* 11 INDEX UNIQUE SCAN I_SUM$_1 1 0 (0) 00:00:01
* 12 FILTER
13 NESTED LOOPS 1 28 1 (0) 00:00:01
14 INDEX FULL SCAN I_LINK1 1 13 0 (0) 00:00:01
15 TABLE ACCESS CLUSTER USER$ 1 15 1 (0) 00:00:01
* 16 INDEX UNIQUE SCAN I_USER# 1 0 (0) 00:00:01
------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------
4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
AND DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNON YM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
(SELECT DISTINCT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B2 AND
BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'WINDOW
GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='VIEW')
5 - access("O"."OWNER#"="U"."USER#")
7 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
9 - access("I"."OBJ#"=:B1)
10 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
11 - access("S"."OBJ#"=:B1)
12 - filter(NULL IS NOT NULL)
16 - access("L"."OWNER#"="U"."USER#")

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
31053 consistent gets
2039 physical reads
0 redo size
955 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed

After changing "_gby_hash_aggregation_enabled" parameter :

SQL> alter system set "_gby_hash_aggregation_enabled" = FALSE scope=both;
System altered.

SQL> select owner,count(1) from dba_objects where object_type='VIEW' group by owner;
OWNER COUNT(1)
------------------------------ ----------
CTXSYS 54
DBSNMP 1
DMSYS 1
EXFSYS 56
LBACSYS 57
MDSYS 59
ORDSYS 5
SYS 2885
SYSMAN 136
SYSTEM 12
WKSYS 79
WK_TEST 6
WMSYS 112
..... (some element removed)
XDB 2
25 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3063327986

------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------
0 SELECT STATEMENT 69320 1895K 195 (6) 00:00:03
1 SORT GROUP BY 69320 1895K 195 (6) 00:00:03
2 VIEW DBA_OBJECTS 69320 1895K 192 (4) 00:00:03
3 UNION-ALL
* 4 FILTER
* 5 HASH JOIN 85274 7744K 191 (4) 00:00:03
6 TABLE ACCESS FULL USER$ 86 1290 3 (0) 00:00:01
* 7 TABLE ACCESS FULL OBJ$ 85274 6495K 187 (3) 00:00:03
* 8 TABLE ACCESS BY INDEX ROWID IND$ 1 8 2 (0) 00:00:01
* 9 INDEX UNIQUE SCAN I_IND1 1 1 (0) 00:00:01
* 10 TABLE ACCESS BY INDEX ROWID SUM$ 1 26 0 (0) 00:00:01
* 11 INDEX UNIQUE SCAN I_SUM$_1 1 0 (0) 00:00:01
* 12 FILTER
13 NESTED LOOPS 1 28 1 (0) 00:00:01
14 INDEX FULL SCAN I_LINK1 1 13 0 (0) 00:00:01
15 TABLE ACCESS CLUSTER USER$ 1 15 1 (0) 00:00:01
* 16 INDEX UNIQUE SCAN I_USER# 1 0 (0) 00:00:01
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
AND DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNON
YM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA
CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
(SELECT DISTINCT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B2 AND
BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'WINDOW
GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='VIEW'
5 - access("O"."OWNER#"="U"."USER#")
7 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
9 - access("I"."OBJ#"=:B1)
10 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
11 - access("S"."OBJ#"=:B1)
12 - filter(NULL IS NOT NULL)
16 - access("L"."OWNER#"="U"."USER#")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
31051 consistent gets
0 physical reads
0 redo size
949 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
25 rows processed

We checked our affected screens in the system, and all of them are doing fine!!!!

Non-ASM to ASM Database Migration

Last week, I was searching of some ASM contents on google and came across this great ASM post on "Shafiulla Oracle DBA Blog".

Although, Conceptual Details on ASM were hardly touched, but the post was pretty straight-forward and a direct technical approach. Any DBA can use content to get their hands on 10g ASM. And, so I thought of working on the contents in my test 10g environment. And within a matter of 2 hours, I was able to migrate my Non-ASM Database to ASM Database on Windows.

Here's a brief outline on the whole content:

1. Creation of ASM Disks using "asmtool", spaced enough to have your Non-ASM Database fit in.
   asmtool -create disk01 2048
2. Creation of OCR Repository and CSS Service, using "localconfig"
   localconfig add
3. Creation of ASM Instance using "DBCA", as first step.
   Here you will have to set the following paramters like, ASM_DISKGROUP, ASM_DISKSTRING, LOCK_NAME_SPACE, etc.
4. Creation of ASM Disk Groups and Addition od ASM Disks (as Candidates), using "DBCA", assecond step.
   Ensuring _ASM_ALLOW_ONLY_RAW_DISKS is set to FALSE, to successfully add ASM Disks.
   alter system "_ASM_ALLOW_ONLY_RAW_DISKS"=false scope=spfile;
5. Ensure the Availability of the Instance:
   net start oracleasmservice+asm
6. Checking the Assignments of the ASM Disks
   show parameter asm
   At this point the ASM Instance is up with mounted ASM Disk Groups.
7. Change the following parameters (control_files, db_create_file_dest, db_create_online_log_dest_n) and startup the Non-ASM Database in NOMOUNT state.
   control_files='+DATA\control01.ctl','+DATA\control02.ctl','+DATA\control03.ctl'
8. In another session, connect as target to Non-ASM Database, using "rman"
9. Restore the control file from original location to ASM Disk, and mount the database, under "rman" session:
   restore controlfile from 'C:\oracle\asmdb\control01.ctl';
10. Take a Copy Backup of the Non-ASM Database in ASM Disk space, under "rman" session.
    backup as copy database format '+DATA';
11. Switch the Database and Tempfiles to ASM Disk having the copy backup.
    switch database to copy;
    run{set newname for tempfile 1 to '+DATA/asmdb/tempfile'; switch tempfile all;}
12. Now, open the database.
13. Add redo groups to ASM Disk, and drop the redo groups from non-ASM location
    alter database add logfile group 4 ('+data');

That's it!!! Your Non-ASM Database has just now become an ASM Database. For detailed steps on how to achieve this, please have a walkthough on the following posts. :

• Migration(Convertion) of Database from non-ASM to ASM (Part - 01)
• Migration of Database from non-ASM to ASM (Part - 02)

I am sure you will enjoy it!!!

Successful Migration on New Server

On the 20th Feb 2010, we successfully migrated our entire system onto the new server, and the migrated the 9iR2 Production Database to 10gR2 Database (10.2.0.4), and the system were made available for the end users.

Just a little bit about the server configuration:

IBM x3650 Server, Xeon Quad Core E5430 2.66 GHz, with 16 GB RAM, having around 500 GB storage after RAID configurations.

The Testing took more time than planned, but it paid off really well. We tested the entire ERP Application and Database, in terms of Data Validation and Performance. And the test results were impressive. And from 21st Feb, users were online.

In the past 15 days, we carried out the SGA optimization , encountered an Oracle Bug (will discuss its workaround in separate post), Enterprise Manager was setup, RMAN Backups and other jobs were configured (same as it was before the migration), Clone Server were setup for periodic cloning, and the DEV and TEST Environments were also refreshed.

As of now, I am enjoying the 10g Database on a powerful server. We have also recieved good feedback from a couple of users, although formal feedback has not been collected yet. With a couple of weeks of more support, I am eyeing at 11g testing and upgrade preparations...

RMAN Compressed Backups

RMAN Backups in Oracle Database 10g Standard Edition enables us to take compressed backups. "AS COMPRESSED BACKUPSET" is the addition in the default backup command:

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Well, that should be good, so I put the backup command at test for my newly migrated 10g Database. Here we go...

My Normal RMAN Backup takes an average of 30 minutes to complete, generating around 20.5 GB of Backupset for a 40GB Database.
So, I am expecting anything close to 40-50% compression (backupset size from 10-12 GB) after using the new command, and I am expecting the job to take more than 30 minutes to finish (say between 1-1:30 hrs).

And yet again, Oracle makes my day, today!!! I not only finish the job in less than 30 minutes, but guess what!, The compression is magnificent. The job finished at an average of 14 mins (superb) and the compression is almost 85-90%, i.e. backupset size was 2.5 GB (Mind Blowing). Talk about the Performance Gains and Storage Save.

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
Starting backup at
06-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
.
.
.
input datafile fno=00001 name=E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00002 name=E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=E:\ORACLE\ORADATA\ORCL\USERS01.DBF
input datafile fno=00005 name=E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 06-MAR-10
channel ORA_DISK_1: finished piece 1 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KTL7T94E_1_1_20100306
tag=TAG20100306T153854 comment=NONE
channel ORA_DISK_1: starting piece 2 at 06-MAR-10
channel ORA_DISK_1: finished piece 2 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KTL7T94E_2_1_20100306
tag=TAG20100306T153854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:14:01
Finished backup at 06-MAR-10
Starting Control File and SPFILE Autobackup at 06-MAR-10
piece handle=\\BLADE5\MIS\BACKUP\RMAN\ORCL\C-1235707396-20100306-16 comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-10

RMAN> BACKUP AS COMPRESSED BACKUPSET archivelog all;
Starting backup at 06-MAR-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1120 recid=1107 stamp=712943759
channel ORA_DISK_1: starting piece 1 at 06-MAR-10
channel ORA_DISK_1: finished piece 1 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KVL7TA4H_1_1_20100306
tag=TAG20100306T155601 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-MAR-10
Starting Control File and SPFILE Autobackup at 06-MAR-10
piece handle=\\BLADE5\MIS\BACKUP\RMAN\ORCL\C-1235707396-20100306-17 comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-10

Disaster Recovery using RMAN Backups

Every DBA has to ensure that his hours spent in planning and testing the backup-recovery strategy works, especially it should be relied upon in any unexpected scenarios occur. We DBAs are always tend to be at ease when our scheduled backup jobs are performing well and are error-free, followed by couple of recovery testings scenarios.

Nevertheless, there are always scenarios which may be left unanticipated during the B/R Testing. One such scenario was haunting me since couple of days. And it took me a while to figure it out (due to known and unknown recovery conditions).

Let's take an example. Say I have a complete RMAN Backup including archivelog backups on Disks. Let's assume that the backups are all in recoverable conditions. And then, suddenly the Production Database Server goes off. Boom!!! The only way to recover the server is to rebuild it or to have us recover the Instance from the RMAN Backups on to another Server. In this post, we will see how to recover or reconstruct a Database by only using the RMAN Backups.

2 pre-conditions to use the RMAN Backups for recovery are:

1. Any Server should be available in a state where Oracle Product can be installed.
2. Same version of Oracle Product needs to be installed with which the RMAN Backup was taken

In my case the O/S is Windows and the Oracle Database Product is 9i R2 (9.2.0.8) Standard Edition.

The following steps would outline the procedure of recovering the Database using only RMAN Backups:

1. Create the Oracle Service
2. Create the relevant folders under admin and oradata folders
3. Restore the spfile from autobackup
4. Restore the controlfile from autobackup
5. Restore the Database
6. Recover the Database
7. Open the Database resetting the logs

We create an Oracle Service using oradim without passing any parameter file

c:\>oradim -new -sid DBTEST -intpwd DBTEST

Now, we need to create the relevant folders for Oracle Database, as follows:

1. Create BDUMP, CDUMP, and UDUMP folders under 'c:\oracle\admin' folder,
2. Create DBTEST folder under 'c:\oracle\oradata' folder

We connect to RMAN and start the Recovery process. First we need to set the DBID of the Database. At times we may or maynot know the DBID of the Database. There are 2 ways to find the DBID:

1. If any RMAN Logs are maintained, the DBID can be found when the initial connection is made to the target
2. If autobackup is enabled ans has the autobackup format set, with say '%F', then the filename of the autobackup has the DBID.
   In my case the file name is 'c-1103102985-20100216-02', and the DBID is '1103102985' in the filename.

And then we need to set the autobackup location which will help us identify the location where all the RMAN files.

C:\>rman
Recovery Manager: Release 9.2.0.8.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)

RMAN> set dbid=1103102985;
executing command: SET DBID

RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\INITDBTEST.ORA'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97590688 bytes
Fixed Size 454048 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\testdb\dbtest\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Then we have to restore the SPFILE from the autobackup file. We can either give the keyword "autobackup" or directly reference the path and filename of the autobackup in the 'from' part of the command. I did the latter.

RMAN> restore spfile to 'c:\spdbtest01.ora' from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: c:\c-1103102985-20100216-02
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-FEB-10

Now, we need to shutdown the instance and startup with the restored parameter file.

RMAN> shutdown immediate
Oracle instance shut down

Remember that we have just restored the SPFILE in a non-default location and hence we would require referring a PFILE that should point to the SPFILE so as to start the instance. We need to create a text file, 'init.ora', and then mention the path and name of the restored SPFILE in it. The initDBTEST.ora would contain the following statement:

SPFILE=C:\spdbtest01.ora

And then, we need to reference the newly created PFILE to start the instance.

Alternately, you could also restore the SPFILE to the default home location, under DATABASE directory, where Oracle automatically looks for an SPFILE and thus avoid creating the PFILE to point at the SPFIL. I have chosen the former option for the demonstration.

Start the instance in NOMOUNT state using the parameter file pointing to the spfile.

RMAN> startup force pfile=’C:\initDBTEST.ora’ nomount
Oracle instance started

Total System Global Area 907482184 bytes
Fixed Size 455752 bytes
Variable Size 276824064 bytes
Database Buffers 629145600 bytes
Redo Buffers 1056768 bytes

Now, we need to restore the controlfile to bring back the database. Again here, we can either give the keyword ‘AUTOBACKUP’ or directly reference the path and filename of the autobackup in the ‘FROM’ part of the command.

RMAN> restore controlfile from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=C:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL03.CTL
Finished restore at 16-FEB-10

With the restored controlfiles, now we are in a position to restore the Database, for which we need to mount the Instance first and the fire the restore command. Once restore is complete we need to fire the recover command for applying the archivelogs.

RMAN> alter database mount;
using target database controlfile instead of recovery catalog
database mounted

RMAN> restore database;
Starting restore at 16-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\DBTEST\DRSYS01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\DBTEST\EXAMPLE01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\DBTEST\INDX01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\DBTEST\TOOLS01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\DBTEST\USERS01.DBF
.
.
.
restoring datafile 00023 to C:\ORACLE\ORADATA\DBTEST\CWMLITE01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_1_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_2_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_3_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 4
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_4_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 5
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_5_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 6
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_6_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 7
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_7_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 8
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_8_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 9
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_9_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 16-FEB-10

RMAN> recover database;
Starting recover at 17-FEB-10
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_03L65IJE_1_1_20100216 tag=FULL_160210_1230 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00002.001 thread=1 sequence=2
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_05L65Q1B_1_1_20100216 tag=FULL_160210_1430 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00003.001 thread=1 sequence=3
unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: =========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =========================================================
RMAN-03002: failure of recover command at 02/17/2010 10:25:28
RMAN-06054: media recovery requesting unknown log: thread 1 scn 34558001

Don't Panic when you see the above RMAN Error Message. You just need to open the database with resetlogs as the recovery is over.

RMAN> alter database open resetlogs;
database opened

RMAN>

Looks simple! Yet it is a critical test case, as this is the what your full RMAN backups should do, i.e. A Full Recovery.

How to Enable Auditing in 10g Database

Check the values of audit parameters of your 10g Database, as it is a default disabled feature.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

The AUDIT_TRAIL parameter can be used to enable auditing by setting one of the following values:

none or false - Auditing is disabled.
db or true - Auditing is enabled, with
all audit records stored in the database audit trial (SYS.AUD$).
db,extended
- As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml-
Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also
populated.
os- Auditing is enabled, with all audit records directed to the
operating system's audit trail.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.

The Database needs to be bounced for the change to take affect.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
System altered.

The SQL_BIND and SQL_TEXT columns are populated, when extended option in AUDIT_TRAIL is enabled.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB

The AUDIT_SYS_OPERATIONS parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB, EXTENDED

Modifications of the data in the audit trail can be audited using the following statement:

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Audit succeeded.

To enable auditing on a schema/user we can use the following syntaxes

For auditing DDL (CREATE, ALTER & DROP of objects) statements

AUDIT ALL BY ACCESS;

For auditing DML (INSERT UPDATE, DELETE, SELECT, EXECUTE) statements

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY BY ACCESS;

For auditing SYSTEM EVENTS (LOGON, LOGOFF etc.) statements

AUDIT EXECUTE PROCEDURE BY BY ACCESS;

To monitor the audit trail we can use the DBA_AUDIT_TRAIL view.