Linux /def/shm related for Oracle 11g Automatic Memory Management

/def/shm does not use /etc/fstab attributes after reboot

There is a bug in Red Hat Linux 6 and Oracle Enterprise Linux 6 (UEK and RHEL-kernel) and probably all other Red Hat 6 related Linux Distro’s.

When you need more memory for SGA/PGA when using MEMORY_MAX_TARGET, you need to resize /dev/shm. By default this is 50% of total memory and Oracle tells you to add the following to /etc/fstab:

shmfs /dev/shm tmpfs size=4g 0 0

Which works…But, there is another line in /etc/fstab which in my opinion should be used and should work:

#tmpfs /dev/shm tmpfs defaults 0 0

tmpfs /dev/shm tmpfs size=4g 0 0

This works after a remount: ‘mount -o remount /dev/shm’,

but it doesn’t work after a reboot…

So  I do the another step with help of Oracle forums and its may be bug of Redhat 6.2,6.1 and OEL 6 Version

https://forums.oracle.com/message/10020717

[root@bl-sms ~]#sudo cp /etc/rc.d/rc.sysinit /etc/rc.d/rc.sysinit.bck

[root@bl-sms ~]#sudo sed -i 's/-f \/dev\/shm/\/dev\/shm/g'

/etc/rc.d/rc.sysinit

 and  then reboot the system

After check the system following command

[root@bl-sms ~]df –h /dev/shm OR

[root@bl-sms ~]df -h

Happy Updating .........

Automatically start Oracle 11g on Oracle Linux 6.2 after server reboot

Environment: Oracle database 11.2.0.3.0, Oracle Linux 6.2
Oracle 11g includes 2 scripts which can be used to start or shut down Oracle databases on Linux. Both scripts are installed in $ORACLE_HOME/bin and are called dbstart and dbshut. However, these scripts are not executed automatically after you reboot your server. I will explain here how you can configure that.
First, you need to make sure that any database instances you want to autostart are set to “Y” in the /etc/oratab file. This is how mine looks like:

#   # This file is used by ORACLE utilities.  It is created by root.sh # and updated by either Database Configuration Assistant while creating # a database or ASM Configuration Assistant while creating ASM instance.   # A colon, ':', is used as the field terminator.  A new line terminates # the entry.  Lines beginning with a pound sign, '#', are comments. # # Entries are of the form: #   $ORACLE_SID:$ORACLE_HOME:: # # The first and second fields are the system identifier and home # directory of the database respectively.  The third filed indicates # to the dbstart utility that the database should , "Y", or should not, # "N", be brought up at system boot time. # # Multiple entries with the same $ORACLE_SID are not allowed. # # baldbsms:/u01/app/oracle/product/11.2.0/db_1:Y

The /etc/oratab file is normally created by running the root.sh script at the end of the installation. If you don’t have the file, you can always add it to your system by creating it manually (with user root!).
Next, we are going to create 2 scripts under /home/oracle/scripts: ora_start.sh and ora_stop.sh. These scripts will call dbstart and dbshut and will also allow us to add some more actions, for example the start of the Enterprise Manager database control or any other services you might have.
$ su – oracle
$ vi /home/oracle/scripts/ora_start.sh

#!/bin/bash   # script to start the Oracle database, listener and dbconsole   . ~/.bash_profile   # start the listener and the database $ORACLE_HOME/bin/dbstart $ORACLE_HOME   # start the Enterprise Manager db console $ORACLE_HOME/bin/emctl start dbconsole   exit 0

$ vi /home/oracle/scripts/ora_stop.sh

#!/bin/bash   # script to stop the Oracle database, listener and dbconsole   . ~/.bash_profile    # stop the Enterprise Manager db console $ORACLE_HOME/bin/emctl stop dbconsole   # stop the listener and the database $ORACLE_HOME/bin/dbshut $ORACLE_HOME   exit 0

You see that inside the scripts, we are calling the .bash_profile file of the user “oracle”. This is needed to set the ORACLE_HOME environment variable.
Next, give execute rights to the scripts:
$  chmod u+x  /home/oracle/scripts/ora_start.sh 
$  chmod u+x  /home/oracle/scripts/ora_stop.sh
You could now test these scripts to see if they correctly shut down and start up your Oracle database.
We will now create a wrapper script that can be used to schedule as a service.
With user root, create a file called “oracle” under /etc/init.d.
$ vi /etc/init.d/oracle

#!/bin/bash # chkconfig: 345 99 10 # description: Oracle auto start-stop script.   # Set ORA_OWNER to the user id of the owner of the # Oracle database in ORA_HOME.   ORA_OWNER=oracle RETVAL=0   case "$1" in     'start')         # Start the Oracle databases:         # The following command assumes that the oracle login         # will not prompt the user for any values         su - $ORA_OWNER -c "/home/oracle/scripts/ora_start.sh"         touch /var/lock/subsys/oracle         ;;     'stop')         # Stop the Oracle databases:         # The following command assumes that the oracle login         # will not prompt the user for any values         su - $ORA_OWNER -c "/home/oracle/scripts/ora_stop.sh"         rm -f /var/lock/subsys/oracle         ;;     *)         echo $"Usage: $0 {start|stop}"         RETVAL=1 esac exit $RETVAL

$ chmod 750 /etc/init.d/oracle

To create a service of this script, run the following command:

$ chkconfig --add oracle

Next, check the script by running “service oracle stop” or “service oracle start” from the command line:
$ service oracle stop
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
Stopping Oracle Enterprise Manager 11g Database Control …
… Stopped.
Processing Database instance “oratst”: log file /u01/app/oracle/product/11.2.0/db_1/shutdown.log
$ service oracle start
Processing Database instance “oratst”: log file /u01/app/oracle/product/11.2.0/db_1/startup.log
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
Starting Oracle Enterprise Manager 11g Database Control …… started.
After this, it’s time for the final test: reboot your server and check if your Oracle database is automatically started after the reboot.

Happy Reading ..........

Parallel transaction recovery and SMON Monitoring

Parallel transaction recovery and SMON Monitoring 

I have read  about parallel transaction monitoring and doing task as a given below:

A very nice article to read when SMON is the actual blocker on other Database Sessions.

http://www.dba-oracle.com/t_parallel_transaction_recovery_undo_cpu.htm

Now in case the transaction rollback involves index block or when you querying the V$FAST_START_SERVERS you find only one server to be working then you may force disable the parallel rollback.

Follow these steps (If in RAC you must apply to all instances) :
1) Find SMON PID
select pid, program from v$process where program like '%SMON%';
2) on SQLPLUS as sysdba disable SMON transaction rollback/recovery

oradebug setorapid
oradebug event 10513 trace name context forever, level 2

3) Kill the PQ slaves that are doing parallel transaction recovery.

select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;', 'kill -9 '||spid
from v$session s, (select * from v$process
 where pid in (select pid from v$fast_start_servers)) p
 where s.paddr=p.addr;

4)  Disable parallel rollback
alter system set fast_start_parallel_rollback=false;
5) Enable SMON to do serial recovery again

oradebug setorapid
oradebug event 10513 trace name context off
oradebug wakeup 13
6) Use the follwoing query to verify serial recovery and estimate when it will finish

select
usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;

REFERENCES :

Metalink Note Ids: 414242.1, 238507.1

EXP-00091: Exporting questionable statistics

When  I export  one of  database schema by one of Oracle client machine, I have received following warning message    “EXP-00091: Exporting questionable statistics” and  schema successfully exported. But My question is why shown the above warning message . I have searching in google  and found possible cause of error and solutions my exporting  problem.

Cause :

 => There are row errors while exporting
 =>The client character set or NCHAR character set does not match the server character set or NCHAR character set
=>A QUERY clause is specified
=>Only certain partitions or sub partitions are exported 

Solution :

When OS character set different that ORACLE character set then BEFORE export you should set NLS_LANG=.CHARACTERSET according Oracle Database Character set.
Example : Oracle 10gr2 on Linux version 4
OS NLS_LANG = US7ASCII
Oracle NLS_LANG = WE8IS0XXXXX

Just Before export set NLS_LANG like
$export NLS_LANG=.WE8ISOXXXXX
$export your_data

Example on windows

First check "NLS_CHARACTERSET" of database through below query

SQL> select VALUE
2 from nls_database_parameters
3 where PARAMETER = 'NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252


On command prompt

c:\> set NLS_LANG=.WE8MSWIN1252

ORA-00054: resource busy and acquire with NOWAIT

Resolve ORA-00054: resource busy and acquire with  NOWAIT

Overview ORA-00054:

        This is a quite normal message and is reported if a SQL statement would have blocked waiting for another user to complete some operation. The SQL command with either:

•   Had a 'NOWAIT' clause so instead of blocking the statement returns this error.

• Was DDL and was blocked.

•  DDL or SELECT with FOR UPDATE NOWAIT can raise this error if an incompatible lock is held.

How to Check ORA-00054?

• None normally required unless this is occurring when not expected.

      

• Check v$lock for locks being held.

• For DDL repeat the command. If the DDL is issued under DBMS_SQL it is possible to cause this error if the DDL touches an object related to the current PL/SQL block by some dependency.

• For SELECT FOR UPDATE issue the same statement without the NOWAIT clause to see if the statement blocks (assuming the problem is reproducible). If it blocks check there is a blocker in v$lock to find the blocked process.

Query: 

select * from v$lock where request!=0;

select * from v$lock where type='TX' and id1='&1' and id2='&2'

                where &1 and &2 are the ID for the lock we are waiting on from above.

• V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.

In order to see locked object query,

SQL> set linesize 130

SQL> set pages 100

SQL> col username       format a20

SQL> col sess_id        format a10

SQL> col object format a25

SQL> col mode_held      format a10

SQL> select     oracle_username || ' (' || s.osuser || ')' username

,  s.sid || ',' || s.serial# sess_id

,  owner || '.' || object_name object

,  object_type

,  decode( l.block

,       0, 'Not Blocking'

,       1, 'Blocking'

,       2, 'Global') status

,  decode(v.locked_mode

,       0, 'None'

,       1, 'Null'

,       2, 'Row-S (SS)'

,       3, 'Row-X (SX)'

,       4, 'Share'

,       5, 'S/Row-X (SSX)'

,       6, 'Exclusive', TO_CHAR(lmode)) mode_held

from       v$locked_object v

,  dba_objects d

,  v$lock l

,  v$session s

where      v.object_id = d.object_id

and        v.object_id = l.id1

and        v.session_id = s.sid

order by oracle_username,  session_id

/

You can also query v$access and v$locked_object to see specific locks: 

select s.sid, s.serial#, p.spid
from
   v$session s,
   v$process p
where
   s.paddr = p.addr
and   s.sid in (select SESSION_ID from v$locked_object);

How to Fix ORA-00054?

• Re-execute the DDL after some time when the when the objects gets released from the lock.
• If the task needs to be completed immediately, kill the sessions that are preventing the exclusive lock.
• If it is bulk job which takes long time frame, get a maintenance window to prevent end user to connect to the database and then run the DDL.

ORA-07445: exception encountered: core dump [ddfnetCFull()+2040] [SIGSEGV] [Address not mapped to o bject] [0xFFFFFFFF7B4800A0] [] []

ORA-07445: exception encountered: core dump [ddfnetCFull()+2040] [SIGSEGV] [Address not mapped to o bject] [0xFFFFFFFF7B4800A0] [] []

Problem Description: 

Getting the following error while inserting the records over the dblink:

ORA-07445: exception encountered: core dump [ddfnetCFull()+2040] [SIGSEGV] [Address not mapped to o
bject] [0xFFFFFFFF7B4800A0] [] []

Symptoms:

Database Alert Files shows:


Errors in file /oradg2/orasys/admin/adp2/udump/adp_ora_6440.trc:
ORA-07445: exception encountered: core dump [ddfnetCFull()+2040] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7B1800A0] [] []


Database Trace File shows:


*** SERVICE NAME:(SYS$USERS) 2009-12-22 07:29:48.796
*** SESSION ID:(2256.34220) 2009-12-22 07:29:48.796
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0xffffffff7b4800a0, PC: [0x102450e98, ddfnetCFull()+2040]
*** 2009-12-22 07:29:48.840
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ddfnetCFull()+2040] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7B4800A0] [] []
Current SQL statement for this session:


INSERT INTO Table_Name From Table_Name@DBLINK


Explanation:

Problem caused due to in-doubt transactions exists in the database.

Solution:

purging pending transactions would rectify  ora-7445


1. Identify the id of the transaction:

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;

If there are entries returned by the above query then,

2. Try to commit/ rollback these failed transactions :

To Commit:

SQL>commit force;

To Rollback :

SQL> rollback force;

3. If still no success, then you need to purge these transactions:

SQL> alter system enable distributed recovery;

SQL> alter system set "_smu_debug_mode" = 4;
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
SQL> COMMIT;

4. Confirm that the transaction has been purged:

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;