[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

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!!!

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

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.

Configure Enterprise Manager (Database Control) Manually

Oracle Enterprise Manager - Database Control (dbconsole) is something that usually installed and configured automatically whenever you install a new Oracle 10g database, using Database Configuration Assistant.

I was wondering, if i was to delete the service or remove Enterprise Manager - Database Control, how is it possible to bring back EM! This is what I did to figure manually install and configure the Enterprise Manager for my 10g Test Database.

First, I dropped my repository to remove the EM-Database Control and delete the EM Service.

C:\>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Nov 8, 2009 2:23:59 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: db10gee
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Nov 8, 2009 2:24:20 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at
C:\oracle\product\10.2.0\db_2\cfgtoollogs\emca\DB10GEE\emca_2009-11-08_02-23-59-PM.log.
Nov 8, 2009 2:24:24 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Nov 8, 2009 2:24:31 PM oracle.sysman.emcp.EMReposConfig stopDBMSJobs
WARNING: Error initializing SQL connection. SQL operations cannot be
performed
Nov 8, 2009 2:24:31 PM oracle.sysman.emcp.EMReposConfig invoke
WARNING: Unable to remove DBMS jobs.Nov 8, 2009 2:24:31 PM
oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Terminate batch job (Y/N)? n

C:\>emctl status agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights
reserved.
---------------------------------------------------------------
Agent is Not Running

C:\>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights
reserved.
http://zaffark:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The service name
is invalid.

More help is available by typing NET HELPMSG 2185.

C:\>NET HELPMSG 2185

The service name is invalid.

EXPLANATION

You tried to start a service that is not configured on this
system.

ACTION

Check the spelling of the service name or check the configuration
information for the service using the Services option from Server Manager.

So, the EM-Database Control has been removed and the EM Windows Service has been deleted. So, let's create the Repository and install Enterprise Manager-Database Control.

C:\>emca -repos create

STARTED EMCA at Nov 8, 2009 3:23:24 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: DB10GEE
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Nov 8, 2009 3:23:41 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at
C:\oracle\product\10.2.0\db_2\cfgtoollogs\emca\DB10GEE\emca_2009-11-08_03-23-24-PM.log.
Nov 8, 2009 3:23:42 PM oracle.sysman.emcp.EMReposConfig
createRepository
INFO: Creating the EM repository (this may take a while) ...
Nov 8, 2009 3:28:49 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 8, 2009 3:28:50 PM

C:\>emca -config dbcontrol db

STARTED EMCA at Nov 8, 2009 3:30:30 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: db10gee
Database Control is already configured for the database db10gee
You have chosen to configure Database Control for managing the database
db10gee
This will remove the existing configuration and the default settings and
perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings
Database ORACLE_HOME
................ C:\oracle\product\10.2.0\db_2
Database hostname
................ zaffark
Listener port number ................ 1521
Database SID ................ db10gee
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 8, 2009 3:31:18 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at
C:\oracle\product\10.2.0\db_2\cfgtoollogs\emca\DB10GEE\emca_2009-11-08_03-30-29-PM.log.
Nov 8, 2009 3:31:23 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Nov 8, 2009 3:31:48 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Nov 8, 2009 3:32:48 PM oracle.sysman.emcp.EMDBPostConfig
performConfiguration
INFO: Database Control started successfullyNov 8, 2009 3:32:50 PM
oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL
is http://zaffark:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 8, 2009 3:32:50 PM

C:\>

I checked my Database Control URL and the Enterprise Manager configuration was a success.

10G Flashback-related Crash Scenario

Today, I created a crash scenario by deleting all the files (including the archives and backups) in the FLASH RECOVERY AREA (C:\oracle\flash_recovery_area) in in my test 10G database. After which, I was only able to mount the database. A "startup" or an "alter database open" gave the following error: "ORA-38760: This database instance failed to turn on flashback database".

Here, I will show how I worked around the problem, to get my database up and running.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 192
Next log sequence to archive 192
Current log sequence 194

SQL> show parameter rec
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
control_file_record_keep_time integer 7
db_recovery_file_dest string C:\oracle/flash_recovery_area
db_recovery_file_dest_size big integer 4G
db_recycle_cache_size big integer 0
ldap_directory_access string NONE
recovery_parallelism integer 0
recyclebin string on
use_indirect_data_buffers boolean FALSE

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SQL> alter database flashback off;
Database altered.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Usually at this point database should start. In our case, we need to check for Guaranteed Restore Point, and if it exists then we need to drop it.

SQL> select NAME, SCN, GUARANTEE_FLASHBACK_DATABASE, DATABASE_INCARNATION# from v$restore_point;
NAME SCN GUA DATABASE_INCARNATION#
---------- ---------- --- ---------------------
A 4073278 YES 2

SQL> DROP RESTORE POINT A;
Restore point dropped.

SQL> select NAME, SCN, GUARANTEE_FLASHBACK_DATABASE, DATABASE_INCARNATION# from v$restore_point;
no rows selected

SQL> alter database open;
Database altered.

SQL> show sga
Total System Global Area 314572800 bytes
Fixed Size 1248768 bytes
Variable Size 79692288 bytes
Database Buffers 226492416 bytes
Redo Buffers 7139328 bytes

10G New Features: Data Pump Basics

Data Pump Components:

There are 3 major components of Data Pump Technology:

1. DBMS_DATAPUMP package
   In this package are the procedures used to carryout the Data Pump export and import activities.
   
2. DBMS_METADATA package
   This package is used to extract the metadata of the data dictionary objects.
   
3. expdp, impdp client utilities
   Using "expdp", you can extract data objects from the source database. And, using "impdp", you can load data objects into the target database.

Directory Objects:

The Data Pump job creates the dump files on the server. Using Directory Objects, Data Pump can write to/read from the O/S directory on the server.

You can use the DIRECTORY parameter of expdp/impdp utilities to relate to the directory.
You can also set the Environment Vairable "DATA_PUMP_DIR" and furnish on the dumpfile or logfile name.
Or, in the DUMPFILE and LOGFILE parameters, prefix the dumpfile name or the logfile name with the Directory Name followed by a ':', i.e. DIRECTORY:FILE notation.
Let's see how it works:

SQL> conn / as sysdba
Connected.

SQL> create directory dp_dir01 as 'c:\oracle\backup\dp';

Directory created.

SQL> grant read, write on directory dp_dir01 to system;

Grant succeeded.

SQL> host expdp system/db10gee full=y dumpfile=dp_dir01:exp01.dmp logfile=dp_dir01:exp01.log

;;;
Export: Release 10.2.0.1.0 - Production on Saturday, 11 April, 2009 14:46:36

Copyright (c) 2003, 2005, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** full=y dumpfile=dp_dir01:exp01.dmp logfile=dp_dir01:exp01.log
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 120.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
................
................
................
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  C:\ORACLE\BACKUP\DP\EXP01.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 14:51:10

Order of Precedence for file locations will be as follows:

1. DIRECTORY:FILE Notation
2. DIRECTORY Parameter
3. DATA_PUMP_DIR

10G New Features: Statistics Collection

I am reading through "OCP Oracle Database 10G: New Features for Administrators Exam Guide" by Sam R. Alapati. A must read for 9i DBAs looking to upgrade skills to Oracle 10G.

In 10G, there is a new background process called MMON [Manageability Monitor Process]. MMON keeps track of the High Water Mark Statistics and the Database Usage statistics, recording it in AWR [Automatic Workload Repository].

Database Usage Statistics:

SQL> desc dba_feature_usage_statistics
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DBID                                      NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(64)
VERSION                                   NOT NULL VARCHAR2(17)
DETECTED_USAGES                           NOT NULL NUMBER
TOTAL_SAMPLES                             NOT NULL NUMBER CURRENTLY_USED                                     VARCHAR2(5)
FIRST_USAGE_DATE                                   DATE
LAST_USAGE_DATE                                    DATE
AUX_COUNT                                          NUMBER
FEATURE_INFO                                       CLOB
LAST_SAMPLE_DATE                                   DATE
LAST_SAMPLE_PERIOD                                 NUMBER
SAMPLE_INTERVAL                                    NUMBER
DESCRIPTION                                        VARCHAR2(128)

HWM Statistics:

SQL>
SQL> desc dba_high_water_mark_statistics
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DBID                                      NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(64)
VERSION                                   NOT NULL VARCHAR2(17)
HIGHWATER                                          NUMBER
LAST_VALUE                                         NUMBER
DESCRIPTION                                        VARCHAR2(128)

SQL>
SQL> select name, highwater, description
2  from dba_high_water_mark_statistics
3  order by 3;

NAME                                                             HIGHWATER                                                          
---------------------------------------------------------------- ----------
DESCRIPTION
--------------------------------------------------------------------------------
CPU_COUNT                                                                 2
Maximum Number of CPUs
SESSIONS                                                                 18
Maximum Number of Concurrent Sessions seen in the database
DATAFILES                                                                12
Maximum Number of Datafiles

PART_INDEXES                                                              0
Maximum Number of Partitions belonging to an User Index
PART_TABLES                                                               6
Maximum Number of Partitions belonging to an User Table
SQL_NCHAR_COLUMNS                                                        11
Maximum Number of SQL NCHAR Columns

SERVICES                                                                  4
Maximum Number of Services
TABLESPACES                                                              13
Maximum Number of Tablespaces
QUERY_LENGTH                                                            139
Maximum Query Length

DB_SIZE                                                          1704001536
Maximum Size of the Database (Bytes)
USER_INDEXES                                                           2001
Number of User Indexes
USER_TABLES                                                             998
Number of User Tables

SEGMENT_SIZE                                                      183500800
Size of Largest Segment (Bytes)

13 rows selected.

SQL>

Pretty Cool.... Didn't have that in 9i.