Issue encounter during Oracle Database 11.2.0.4 installation on Oracle Linux 7

1. Packages "elfutilslibelfdevel0.97" and "pdksh5.2.14" Are Missing
While installing the 11.2.0.4 database software on a Oracle Linux 7 the Oracle Universal Installer (OUI) reports that packages "elfultilslibelfdevel0.97" and "pdksh5.2.14" are missing.

Solution:
a. Under the /database directory, modify the file /database/stage/cvu/cv/admin/cvu_config , search for CV_ASSUME_DISTID and replace OEL4 with OEL7 as below.
CV_ASSUME_DISTID=OEL4
to:
CV_ASSUME_DISTID=OEL7

b. Reinstall the 11.2.0.4 software using /database/runInstaller.
OUI should now perform the OEL7 prerequisite checks (which are identical to the RHEL7 prerequisite checks) and no longer report that packages "elfutilslibelfdevel0.97" and "pdksh5.2.14" are missing.

2. Error in invoking target 'agent nmhs' of makefile
While installing Oracle Database 11.2.0.4 software, you may get an error during the LINK stage of installation:
Exception String: Error in invoking target 'agent nmhs' of makefile '/u01/oracle/app/product/11.2.0/db_1/sysman/lib/ins_emagent.mk'.

Solution:
Modify the file /sysman/lib/ins_emagent.mk, search for MK_EMAGENT_NMECTL and add in -lnnz11 as below. 
#===========================
#  emdctl
#===========================
$(SYSMANBIN)emdctl:
        $(MK_EMAGENT_NMECTL) -lnnz11

Save the file and click “RETRY” to resume the installation. The installation should be able to complete successfully.

TWO_TASK

TWO_TASK
This UNIX environment variable specify a connection string. Connections that do not specify a database will connect to the database specified in TWO_TASK.

Eg.

TWO_TASK=TEST
export TWO_TASK
sqlplus hr
is the same as:

sqlplus scott@TEST

Take note that this variable will even overwrite $ORACLE_SID and cause ORA-01031: insufficient privileges when you connect as sysdba.

Eg.
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

To unset,
sh or ksh:
unset TWO_TASK
csh:
unsetenv TWO_TASK

Eg.
$ unset TWO_TASK
$ sqlplus /nolog
SQL> conn / as sysdba
Connected.

ORA-00600: internal error code, arguments: [729], [], [space leak]

Refer to error captured from udump.

Unix process pid: 18832, image: oracle@xxxxxx (TNS V1-V3)

*** 2009-08-20 20:11:02.518
*** SESSION ID:(33.19527) 2009-08-20 20:11:02.516
******** ERROR: UGA memory leak detected 9536 ********
******************************************************
HEAP DUMP heap name="session heap" desc=0x1a6e908
extent sz=0x108c alt=32767 het=32767 rec=0 flg=3 opc=3
parent=1a14c28 owner=900ac708 nex=0 xsz=0x2050
EXTENT 0
Chunk 1aa2e84 sz= 4228 free " "
EXTENT 1
Chunk 1a8493c sz= 888 free " "
Chunk 1a84cb4 sz= 740 freeable "define var info"
EXTENT 2
Chunk 1ab9094 sz= 536 free " "
Chunk 1ab92ac sz= 376 freeable "define var info"
Chunk 1ab9424 sz= 184 free " "
Chunk 1ab94dc sz= 740 freeable "define var info"
Chunk 1ab97c0 sz= 536 free " "
Chunk 1ab99d8 sz= 376 freeable "define var info"
Chunk 1ab9b50 sz= 740 freeable "define var info"
.
.
.
.
.
*** 2009-08-20 20:11:02.521
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [729], [9536], [space leak], [], [], [], [], []

A space leak has been detected in the User Global Area (UGA). There is no data corruption as a result of this error. It is an internal memory housekeeping problem. The second argument,9536 is the number of bytes leaked.

The above dump show Memory leak. Oracle is trying to free memory allocated to a process. Chunk 1a84cb4,1ab92ac and the rest with freeable "define var info" show memory leaked.

This can be safely ignore for small memory leaks by adding the following event to init.ora:
event = "10262 trace name context forever, level 10240"
Then, restart your database. This event disables space leaks less than 10 kbytes.

You can see the details at Metalink Doc ID: 31056.1 ORA-600 [729] "UGA Space Leak" and Doc ID: 403584.1 Understanding and Diagnosing ORA-600 [729] Space Leak Errors

Bug:
Bug:2177050: ORA-600 [729] after application of the 8.1.7.3 patchset. The resulting trace file will include a memory dump which shows unfreed memory chunks with the tags "define var info" and/or "oactoid info".

Memory Notification: Library Cache Object loaded into SGA

Often you will see below error from alert log in Oracle Database 10g.
Memory Notification: Library Cache Object loaded into SGA
Heap size 2135K exceeds notification threshold (2048K)
Details in trace file

These warning messages is indicated as a Notification in alert messages and should not cause the session to fail. They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.

This means that the process is just spending a lot of time in finding free memory extents during an allocation as the memory may be heavily fragmented. Fragmentation in memory is impossible to eliminate completely, however, continued messages of large allocations in memory indicate there are tuning opportunities on the application.

In 10.2.0.1 set the new undocumented parameter, the KGL heap size warning threshold.
Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

SQL> shutdown immediate SQL> startup

SQL> show parameter _kgl
NAME TYPE VALUE
--------------------------------- ------- ----------------
_kgl_large_heap_warning_threshold integer 8388608


This messages does not appear in version 10.2.0.2 or later, so upgrade to the latest version is also another solution to this.

For details, refer to metalink document, Doc ID: 330239.1

Cloning a New VMware Testing Environment (RHEL4)

As a DBA, I perform a lot of testing on Oracle Database related functionalities and features. I do most of my testing on a VMware virtual machine. Instead of creating a new virtual machine each time, I have created/installed a master virtual machine for each platform and each time when I require a new testing environment, I'll clone from my master machine. Before everything is working, there are some configurations that need to be set.

Before shutting down the master VM:

Enable Telnet service.
1. Login as root and install telnet-server if it's not already installed. Go to Legacy Network Server under Add or Remove Packages,click on Details and check "telnet-server".

2. Hit Update to install the necessary packages. You will need to insert the RedHat CD/DVD as per instruction.

3. Run below commands:
# chkconfig telnet on
# service xinetd reload

Enable FTP service
1. login as root and install vsftpd, "Very Secure FTP Daemon" if it's not already installed. Just check "FTP Server" under Add or Remove Packages.

2. Hit Update to install the necessary packages. You will need to insert the RedHat CD/DVD
as per instruction.

3. Start vsftpd
# /etc/init.d/vsftpd start

4. To start vsftpd automatically during reboot,
# ln -s /etc/init.d/vsftpd /etc/rc3.d/S56vsftpd
# ln -s /etc/init.d/vsftpd /etc/rc4.d/S56vsftpd
# ln -s /etc/init.d/vsftpd /etc/rc5.d/S56vsftpd

To allow root user to telnet and ftp
1. Edit the file /etc/securetty and add the following to the end of the file:
pts/0
pts/1
pts/2
pts/3
pts/4
pts/5
pts/6
pts/7
pts/8
pts/9

2. Edit the files /etc/vsftpd.ftpusers and /etc/vsftpd.user_list and comment the 'root' line from each file.

Install VMware Client tools
1. Login as root and then choose Install VMware Tools... from the VM main menu. This will auto mount a cd contains VMware tool package.

2. Open up the cd and install from the rpm package.

3. Then, run the "vmware-config-tools.pl" as root and hit enter on the resolution option.
This should complete the VMware client tools. You can now launch "vmware-toolbox" to
configure time synchronization.

Shutdown and keep the master VM copy. Whenever necessary, copy from the master vm directory and File>>Open the new copied VM from VMware Server console.

On the new added virtual machine:
Probe a new network adaptor(s) MAC address for the new virtual machine
1. Go to Applications menu, System Settings >> Network
On every network adaptor, double click to launch the settings page. Under Hardware Device,
probe a new MAC address for that adaptor.

2. Change the IP addresses accordingly

3. Activate/reactivate the adaptor(s) to take the new network settings.

Change hostname to suite your environment
eg. from master to prod

1. Modify the file /etc/sysconfig/network
NETWORKING=yes
HOSTNAME="prod.localdomain"

2. Do not forget /etc/hosts
Add or modify the entry, Eg.
192.168.0.100 prod.localdomain prod

3. Reboot the OS.

Recovering a Deleted Datafile Without a Backup

Guess what, RMAN can handle lost datafiles even if you do not have a backup.

The conditions:
1. The control file knows about the datafile, that is, the user backed up the control file after datafile creation, but the datafile itself is not backed up.

2. If the datafile record is in the control file, then RESTORE creates the datafile in the original location or in a user-specified location (for example, with SET NEWNAME). The RECOVER command can then apply the necessary logs to the datafile.

3. The control file does not have the datafile record, that is, the user did not back up the control file after datafile creation. During recovery, the database will detect the missing datafile and report it to RMAN, which will create a new datafile and continue recovery by applying the remaining logs. If the datafile was created in a parent incarnation, it will be created during restore or recover as appropriate.

Example:
1. Make a full database backup of your ARCHIVELOG mode database.
RMAN> BACKUP DATABASE TAG="FULL";

2. You create a tablespace "TEST" containing a single datafile called /orcl/test01.dbf.

3. create a table and populate some data.

4. Archive all the active online redo logs.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

5. Delete the file /orcl/test01.dbf.

6. Take the tablespace with the missing datafile offline.
SQL> ALTER TABLESPACE TEST OFFLINE IMMEDIATE;

7. Restore the tablespace even without backup.
RMAN> RESTORE TABLESPACE "TEST";

8. Recover tablespace.
RMAN> RECOVER TABLESPACE "TEST";

9. Bring the recovered tablespace online.
SQL> ALTER TABLESPACE TEST ONLINE;

10. Verify the contents of the tablespace.

Disabling OS-Authentication

On the server where that host the database, edit the file $ORACLE_HOME/network/admin/sqlnet.ora

Set SQLNET.AUTHENTICATION_SERVICES=(NONE)

The second way will be remove oracle OS user from dba group or ORA_DBA on Windows.

Auto statistics collection in 10gR1 and 10gR2

We all know that Oracle Database 10g introduces the new automatic optimizer-statistics collection feature. Oracle automatically creates a database job, GATHER_STATS_JOB, and Oracle Scheduler automatically schedules the job to run during the maintenance window.
The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.

But do you know there is something different between Oracle Database 10gR1 and 10gR2 ?
In 10gR1, the GATHER_STATS_JOB continues until it finishes, even if it exceeds the allocated time for the maintenance window.
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10752/stats.htm#40674

In 10gR2, the GATHER_STATS_JOB stops when the maintenance window closes.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41282

Customize 10g sqlplus prompt

Edit the file $ORACLE_HOME/sqlplus/admin/glogin.sql and add in line as below.
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "

When perform sqlplus,
[localhost.localdomain]oracle:> sqlplus user1/user1@ORCL

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jan 3 15:27:23 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

user1@ORCL>

This setting can be overwritten if you have login.sql file in the directory where you run sqlplus.
Eg. On my windows client, I have D:\> login.sql and the contents of the file is as below:
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _DATE> "

When I run sqlplus from D:\ I get output below.
D:\>sqlplus user1/user1@ORCL

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 3 15:31:38 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

user1@ORCL 03-JAN-09>

Unset streams DML handler

To unset DML handler in a streams environment, simply point "user_procedure" to NULL.

Eg.

BEGIN DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => '',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => false,
user_procedure => NULL ,
apply_database_link => NULL,
apply_name => NULL);
END;
/

How to re-synchronize the streams replicated objects online

Refer to My Oracle Support(Metalink), Note:749036.1 publish on 23-NOV-2008.
In case some reader couldn't access Metalink, refer below.

Applies to: Oracle Server - Enterprise Edition - Version: 10.2 to 11.1
Information in this document applies to any platform.

Goal: In-cases we need to re-synchronize the data between the replicating sites, like for example in cases of errors like ORA-01403 / ORA-1422 reported by the apply process because data is out of sync, and resynchronizing manually is not feasible.

Here are the steps we need to do to re-synchronize the data between the source and target site in the streams environment, without the need to stop the activity on the replicated objects on the source site. .

Solution
In here we assume that we have one SOURCE and one TARGET and streams replication is configured between them. For more complicated environments, some modifications/additions may be needed on these steps.

Here is what we need to do:
1. Stop the capture and apply processes:
-- Stop the capture process:
exec dbms_capture_adm.stop_capture('');
-- Make sure its stopped:
select capture_name, status from dba_capture;
-- Stop the apply process:
exec dbms_apply_adm.stop_apply('');
-- Make sure its stopped:
select apply_name, status from dba_apply;

2. Purge the Capture and apply queue tables:
- As we will re-synch; The messages in both source queue that has not been propagated yet, and messages in the apply queue that has not been applied yet, can both be purged.
You can do this step of purging the capture and apply queue if and only if you are doing complete resync of all the streams replication objects. If you are doing synchronization for some of the objects, then do not attempt to purge any of the queues or the other objects will get out of sync

- There will be no problem if you skipped that step, the apply process will still bypass all those messages, we just do that incase we got alot of messages that may take long time to be propagated and skipped by the apply process.

-- Stop the propagation:
exec dbms_propagation_adm.stop_propagation('');
select propagation_name, status from dba_propagation;

-- To purge perform the following for both the capture and apply queue tables:
DECLARE
options dbms_aqadm.aq$_purge_options_t;
BEGIN
options.delivery_mode:=DBMS_AQADM.BUFFERED; DBMS_AQADM.PURGE_QUEUE_TABLE('',NULL,options);
END;
/

-- check that the messages has been successfully pruged for capture and apply queue:
SELECT QUEUE_SCHEMA, QUEUE_NAME, (NUM_MSGS - SPILL_MSGS) MEM_MSG, SPILL_MSGS, NUM_MSGS FROM V$BUFFERED_QUEUES where queue_name='';

-- Start the propagation again:
exec dbms_propagation_adm.start_propagation(''); select propagation_name, status from dba_propagation;

3. Export the source schema:
-- For example we are replicating schema "MARS", so we should do the following:
exp system/oracle owner=mars file=mars.dump log=mars.log object_consistent=Y
*Object_consistent must be set to Y, so the imported data would be consistent with the source.

4) Delete the apply errors:
As we will re-synchronize, we can delete the errors that are in the error_queue of the apply process.

To do so, we can execute either DBMS_APPLY_ADM.DELETE_ERROR or DBMS_APPLY_ADM.DELETE_ALL_ERRORS at the apply site.

You need to make sure that you are not deleting any needed transaction, specially if you are not synchronizing all the replicated for that apply process. If you are synchronizing all the data you can execute directly DBMS_APPLY_ADM.DELETE_ALL_ERRORS.

-- Check the errors in the error queue:
select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;
-- To delete specific transaction:
exec DBMS_APPLY_ADM.DELETE_ERROR('');
-- To delete all errors:
exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS('');

If you need to check the contents of the transactions before deleting check the procedures in documentation:
> Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-04 > 22 Monitoring Streams Apply Processes
> Displaying Detailed Information About Apply Errors

5. Import the source schema to target:
imp system/oracle file=mars.dump full=y ignore=y STREAMS_INSTANTIATION=Y

When doing STREAMS_INSTANTIATION=Y and having the export done with object_consistent=Y, the instantiation SCN for the apply will be modified to the SCN at the time the export was taken, and this will insure that data at the target is consistent with data at the source.

In the example above of exp/imp we are assuming that we are replicating schema MARS, and we are trying to synchronize it, but we may also exp/imp specific tables, we do not have to synchronize the whole schema when we are doing schema level streams.

Note:
- After doing the import, we are now sure that the data is now consistent, and the apply process will only apply changes starting from the SCN after the time of the export.
- If the exp/imp will be done using datapump, then we will not need to set any parameters, as the imported objects will be automatically imported with the SCN of the source at point in time where the export was taken, as long as there is an existing apply process on the apply site.

6. Start capture and apply:
-- Start the apply process:
exec dbms_apply_adm.start_apply('');
-- Make sure apply is started
select apply_name, status from dba_apply;
-- Start the capture process:
exec dbms_capture_adm.start_capture('');
-- Make sure capture is started
select capture_name, status from dba_capture;

sqlplus give error /usr/lib/hpux64/dld.so: Unable to find library 'libclntsh.so.10.1'

When other user in HP-UX run sqlplus and system give following error:
/usr/lib/hpux64/dld.so: Unable to find library 'libclntsh.so.10.1'

Solution:chmod -R 755 $ORACLE_HOME

Oracle 11g New Feature - Checking DB alert log

Checking alert log is no longer necessary to be done from OS level.
You can query v$diag_info view for alert information.

Drop dbcontrol repository

emca -config dbcontrol db -repos drop -silent -PORT -SERVICE_NAME -SYS_PWD -SID -DBSNMP_PWD -SYSMAN_PWD -DBCONTROL_HTTP_PORT -AGENT_PORT -RMI_PORT -JMS_PORT

Eg.
emca -config dbcontrol db -repos drop -silent -PORT 1521 -SERVICE_NAME MESTEST -SYS_PWD SYS -SID MESTEST -DBSNMP_PWD dbsnmp -SYSMAN_PWD sysman -DBCONTROL_HTTP_PORT 1158 -AGENT_PORT 3938 -RMI_PORT 5521 -JMS_PORT 5541

Create new repository to enable dbcontrol in Oracle 10.2

emca -config dbcontrol db -repos create -silent -PORT -SERVICE_NAME -SYS_PWD -SID -DBSNMP_PWD -SYSMAN_PWD -DBCONTROL_HTTP_PORT -AGENT_PORT -RMI_PORT -JMS_PORT

Eg.
emca -config dbcontrol db -repos create -silent -PORT 1521 -SERVICE_NAME MESTEST -SYS_PWD SYS -SID MESTEST -DBSNMP_PWD dbsnmp -SYSMAN_PWD sysman -DBCONTROL_HTTP_PORT 1158 -AGENT_PORT 3938 -RMI_PORT 5521 -JMS_PORT 5541

Error accessing PRODUCT_USER_PROFILE

Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

Solution: Run pupbld.sql at $ORACLE_HOME/sqlplus/admin

Install Oracle 10R2 on SUN Solaris 10 X86-64

Download Oracle Database 10gR2 installer from http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201solx8664soft.html

Checking the Hardware Requirements
1. Login as root.
2. To determine whether the system architecture can run the software,
# /bin/isainfo -kvThe result return should be "64-bit amd64 kernel modules"
3. Minimal 1024MB of physical RAM
# /usr/sbin/prtconf grep Memory
4. Minimal Swap
Between 512MB 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
# /usr/sbin/swap -s
5. To add additional swap, use mkfile to create a file suitable for a local swap area. For example, to create a 600MB swap file:
# /usr/sbin/mkfile 600m /swap
where /swap is the name of the file to be used as swap space. Units for the size can be kilobytes (k), blocks (b), or megabytes (m).
Tell the system to start using the file as swap
# /usr/sbin/swap -a /swap
Use swap -l to verify that the swap file has been activated.
6. Minimal 400MB of free space in /tmp and minimal 1.7GB of disk space for Oracle binaries
# df -k

Checking the Software Requirements
1. To check your solaris version,
# uname -a
2. To determine whether the required packages are installed
# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms \ SUNWsprot SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt
3. If any of the package is not installed, install the package from Solaris CD. Eg.
# pkgadd -d /cdrom/sol_10_1008_x86/Solaris_10/Product SUNWi1cs
# pkgadd -d /cdrom/sol_10_1008_x86/Solaris_10/Product SUNWi15cs
(Pls note that pkgadd -d required a full path follow by a space and package name)
4. Create the Oracle Inventory and DBA group
# groupadd oinstall
# groupadd dba
5. Create the Oracle Software Owner user
# useradd -s /bin/ksh -d /export/home/oracle -m -g oinstall -G dba oracle
# passwd oracle
6. Set Oracle environment parameter by modify $HOME/.profile
export ORACLE_BASE=/opt/apps/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=MESTESTexport ORACLE_TERM=vt100
export TNS_ADMIN=$ORACLE_HOME/network/adminexport EDITOR=vi
export TERM=vt100
PATH=$PATH:/usr/sbin:$ORACLE_HOME/bin:$ORACLE_HOME/lib
7. Configuring Kernel ParametersIn Solaris 10, you are not required to make changes to the /etc/system file to implement the System V TPC. Solaris 10 uses the resource control facility for its implementation.
Note: Oracle Universal Installer(OUI) will give a warning on Kernel parameter because OUI is looking at /etc/system to check kernel parameter setting hence included below in /etc/system.
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=128
set semsys:seminfo_semmni=128
set semsys:seminfo_semmsl=256

8. To modify the kernel parameters,
# projadd -U oracle -K "project.max-shm-memory=(priv,4096MB,deny)" user.oracle
# projmod -s -K "project.max-shm-ids=(priv,128,deny)" user.oracle
# projmod -s -K "project.max-sem-ids=(priv,128,deny)" user.oracle
# projmod -s -K "project.max-sem-nsems=(priv,256,deny)" user.oracle

Run Oracle Universal Installer
1. Login as oracle.
2. To view the values of kernel parameter,
$ prctl -i project user.oracle
3. To unzip Oracle Software,
$ unzip 10201_database_solx86_64.zip
4. Login as oracle in a xterm,
$ export DISPLAY=:0.0
$ cd /export/home/oracle/database
$ ./runInstaller
5. At the end of installation, run the scripts prompt as root
$ /opt/apps/oracle/oraInventory/orainstRoot.sh
$ /opt/apps/oracle/product/10.2.0/db_1/root.sh

Download Oracle Opatch

To download OPatch, visit metalink and go to Patches & Updates and download patch 6880880.