31 October 2013

Oracle Operating System Statistics


-- Operating System Statistics
1. CPU Statistics
V$OSSTAT
                - view captures machine-level information in the database, making it easier for you to determine if hardware-level resource issues exist.
V$SYSMETRIC_HISTORY
                - view shows a one-hour history of the Host CPU Utilization metric, a representation of percentage of CPU usage at each one-minute interval
V$SYS_TIME_MODEL
                - view supplies statistics on the CPU usage by the Oracle database. Using both sets of statistics enable you to determine whether the Oracle database or other system activity is the cause of the CPU problems
--
2. Memory
                This command below includes a great deal of detail, including the size of the mapped segment, how much if it is RSS (Real memory), how much of that is shared, the sizes and permissions of the segment. It is the output above that can be ‘compared’ over time to see if a given process is growing, or leaking memory.
                $ pmap -x $$
                where $$ - process ID

3. Disk I/O Statistics
                - Consumer group
                                V$IOSTAT_CONSUMER_GROUP view captures I/O statistics for all consumer groups that are part of the currently enabled resource plan
                - Database file
                                I/O statistics of database files that are or have been accessed are captured in the V$IOSTAT_FILE view
                                select * from V$IOSTAT_FILE
                - Database function
                                I/O statistics for database functions (such as the LGWR and DBWR) are captured in the V$IOSTAT_FUNCTION view.
                                select * from V$IOSTAT_FUNCTION
4. Network Statistics
                V$IOSTAT_NETWORK - network I/O statistics
                select * from V$IOSTAT_NETWORK

Operating System Data Gathering Tools
Table shows the various tools for gathering operating statistics on *NIX.
                Component        | UNIX Tool
                CPU                       | sar, vmstat, mpstat, iostat
                Memory                              | sar, vmstat
                Disk                        | sar, iostat
                Network                              |netstat

15 October 2013

Oracle streams propagation issue ORA-22636

Issue
SQL> SELECT DESTINATION_DBLINK,
  2         STATUS,
  3         ERROR_DATE,
  4         ERROR_MESSAGE
  5    FROM DBA_PROPAGATION;

DESTINATION_DBLINK
----------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------
TO_FORCL.COM
ORA-22636: Message 22636 not found;  product=RDBMS; facility=ORA

Diagnostic
Run on both sites
SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14
--
SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_11.dat              11

the versions must be the same

Workaround
Update DST file. Follow the document:
Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)

lsnodes.bin: struct size 0

Issue
cluster nodes list
when trying to get a node list with lsnodes
lsnodes.bin: struct size 0
Solution
Try
cd $ORACLE_GRID (/u01/app/11.2.0/grid/bin)
./olsnodes


29 July 2013

sqlplus: error while loading shared libraries


Issue
sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

Solution
1.
                [root@linux_pc ~]# cat /etc/selinux/config
                # This file controls the state of SELinux on the system.
                # SELINUX= can take one of these three values:
                #       enforcing - SELinux security policy is enforced.
                #       permissive - SELinux prints warnings instead of enforcing.
                #       disabled - SELinux is fully disabled.
                SELINUX=enforcing
                # SELINUXTYPE= type of policy in use. Possible values are:
                #       targeted - Only targeted network daemons are protected.
                #       strict - Full SELinux protection.
                SELINUXTYPE=targeted

                # SETLOCALDEFS= Check local definition changes
                SETLOCALDEFS=0
                [root@linux_pc ~]# cp /etc/selinux/config /etc/selinux/config.bak
                [root@linux_pc ~]# vi /etc/selinux/config
                [root@linux_pc ~]# cat /etc/selinux/config
                # This file controls the state of SELinux on the system.
                # SELINUX= can take one of these three values:
                #       enforcing - SELinux security policy is enforced.
                #       permissive - SELinux prints warnings instead of enforcing.
                #       disabled - SELinux is fully disabled.
                SELINUX=disabled
                # SELINUXTYPE= type of policy in use. Possible values are:
                #       targeted - Only targeted network daemons are protected.
                #       strict - Full SELinux protection.
                SELINUXTYPE=targeted

2.
                echo 0 >/selinux/enforce

25 July 2013

PRVF-0002 : Could not retrieve local nodename

Issue:

While Oracle 12c Installation on Linux (my case OEL6 update 4) you could get an error.

Solution 1:

1. check hostname:
cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME = test.localdomain

2. check hosts:
cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6


3. change /etc/hosts like:
127.0.0.1 test test.localdomain
::1 test test.localdomain


4. Run installer

Solution 2:

1. setup system variable like

$ ORACLE_HOSTNAME=test.localdomain
$ export ORACLE_HOSTNAME

07 June 2013

How to find which process caused cluster node reboot


Determining Which Process Caused Reboot
Log File Locations for Processes Causing Reboots.
• oclskd
/log//client/oclskd.log
• ocssd
– /var/log/messages
/log//cssd/ocssd.log
• cssdagent
/log//agent/ohasd/oracss
dagent_root
• cssdmonitor
/log//agent/ohasd/oracss
dmonitor_root
• hangcheck-timer
– /var/log/messages

Determining Which Process Caused Reboot
First, determine the time of the node reboot by using the uptime command and subtracting the
up time from the current system time. The reboot time will be used when examining log files.
When the OCSSD daemon is responsible for rebooting a node, a message similar to “Oracle
CSSD failure. Rebooting for cluster integrity” is written into the system messages log at
/var/log/messages. The cssd daemon log file that is located at
/log//cssd/ocssd.log may also contain messages similar to
"Begin Dump" or "End Dump" just before the reboot.
If hangcheck-timer is being used, it will provide message logging to the system messages log
when a node restart is initiated by the module. To verify whether this process was responsible
for the node reboot, examine the /var/log/messages file and look for an error message
similar to: "Hangcheck: hangcheck is restarting the machine."
Other useful log files include the Clusterware alert log in /log/
and the lastgasp log in /etc/oracle/lastgasp or /var/opt/oracle/lastgasp.
If no indication of which process caused the reboot can be determined from these files,
additional debugging and tracing may need to be enabled.
Note: The oclsomon and the oprocd background processes have been eliminated in Oracle
Database 11g Release 2.

Standby redologs



Quote from documentation:

The standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby database. This means that the standby redo log has more current information than the log apply mechanism because it took a "shortcut" and was written to the standby, bypassing the traditional archiving and FTP to the standby database.