oradebug

In Oracle database version 9i there is no way to determine the IP address through a database view. The only way to determine this IP address is with the oradebug command (Please keep in mind oradebug is not a supported product from Oracle, so if there are problems/crashes you are on your own). The oradebug ipc command creates a trace file. The example shows the process:
 SQL> oradebug setmypid
  SQL> oradebug ipc
  SQL> oradebug tracefile_name
The last command will show the name of the created trace file. The next step is opening the trace file and looking for the string SKGXPCTX. The following is a typical example.

SKGXPCTX: 0xcd1e730 ctx
 admno 0x7a0be402 admport:
 SSKGXPT 0xcd1e884 flags SSKGXPT_READPENDING     info for network 0
         socket no 8     IP 140.87.79.67         UDP 9152
         sflags SSKGXPT_WRITESSKGXPT_UP
         info for network 1
The cluster interconnect IP address is shown in bold. In the example above
it is 140.87.79.67.

Modify the attribute AUTO_START to “always”

With Oracle 11.2 database auto start policy in the clusterware is restore, which means that clusterware will remember the last state of the database.  If the database was stopped normally then on the next restart of clusterware it won’t be started. Otherwise if the server crashes or by some reason the OS is rebooted then clusterware will start the database because last state was ONLINE (running).

hibernate.jdbc.fetch_size and hibernate.jdbc.batch_size

Two important settings for hibernate java application, affecting hibernate CRUD performance.
C = create, R = read, U = update, D = delete

Fetch Size determines when JDBC Statement read data, how many records are going to be read per fetch.
Recommend setting for Oralce JDBC application:
hibernate.jdbc.fetch_size 100
hibernate.jdbc.batch_size 25

Disable Microsoft Office Upload Center

For starters, if you don't want it, don't run Microsoft SharePoint Workspace 2010 (what used to be called Groove). Workspace also loads the Upload Center, even if you simply start it, then cancel before doing anything. It also sets some values in the Windows registry that cause Upload Center to load again every time you restart Windows.

Windows DBConsole internal stop. No OC4J admin passwd hence hard stop

 

Enterprise manager cannot be started. The status shows dbconsole is already started:

C:\>emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

https://JDEORA01:5500/em/console/aboutApplication

Oracle Enterprise Manager 11g is running.

------------------------------------------------------------------

Logs are generated in directory C:\APP\ORACLE\product\11.2.0\se1/jdeora01_e1prod/sysman/log

Install EM grid 11.1.0.1.0 on Linux

• Software

Downloads the following software:

Enterprise Linux Release 5 Update 5 for x86_64 (64 Bit) - DVD (3.3G)

Java SE Development Kit 6u18 (76M)

Oracle Database 11g Release 2 for Linux 64-bit (2G)

opatch - Latest version (26M)

Oracle WebLogic Server 11gR1 (10.3.2) - Package Installer (Generic) (1G)

(There’s a known issue with installing Enterprise Grid Control on Weblogic 10.3.3)

Enterprise Manager Grid Control (4.1G)

Manually configure the Oracle EM dbconsole

To Create EM Dbconsole:

$ emca -repos create
$ emca -config dbcontrol db

or$emca -config dbcontrol db -repos create

Toad hangs when retrieving User Script 

When using the Schema Browser, user selects select a User and the User Script tab. Toad hangs (or taking a long time) on a 10g db. When user uses 9.2.0.5 database, User script works as it should.

Does not appear to matter which user is selected . Toad does not return script and it hangs. Changing the Optimize hint does not address issue.

Also, right-clicking a user then clicking Create Script does not work no matter to File, to Screen, or to Clipboard.

Cannot connect to database from OBIEE 2.4 BI Admin Tool

If you have connection issue, modify bi_init.bat under BI install folder\oraclebi\orahome\bifoundation\server\bin, replace all ORACLE_HOME to OBIEE_HOME, and add new ORACLE_HOME for the actual oracle installation path:

@echo off
set OBIEE_HOME=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome
set ORACLE_HOME=D:\oracle\product\11.2.0.3\owb
set ORACLE_INSTANCE=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orainst
set ORACLE_BI_APPLICATION=coreapplication
rem call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd
if NOT "%1" == "" set COMPONENT_NAME=%1
set PATH=%OBIEE_HOME%\bifoundation\server\bin;%OBIEE_HOME%\bifoundation\web\bin;%OBIEE_HOME%\bin;C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\jre\bin;%ORACLE_HOME\bin%;%windir%;%windir%\system32;%PATH%
if NOT "%2" == "" %2

Oracle Workflow ORA-24247

If you see the following errors:

ORA-24247:Network access denied by access control list(ACL) ORA-06512 at......

 

ORA-29532 when starting OWB Service

Run $ORACLE_HOME/owb/rtp/sql/start_service.sql with OWBSYS user.

If you have the following error:

Not Available

Diagnostics:

service startup failure using command "$ORACLE_HOME/owb/b

in/unix/run_service.sh -manual 1 $ORACLE_HOME OWBSYS 10.8

4.80.186:1521:bidev" reason ORA-29532: Java call terminated by uncaught Java ex

ception: java.io.IOException: service early exit: code=1 : err=JVMJ9VM015W Init

ialization error for library j9gc23(2): Failed to instantiate heap; 768M reques

ted

Could not create the Java virtual machine.

: out=

please invoke [owbhome]/owb/rtp/sql/service_doctor.sql for more information

PL/SQL procedure successfully completed.

[nQSError: 46029] for OBIEE 2.3.2

If OBIEE is unabled to be started because of Error: [nQSError: 46029] Failed to load the DLL $BIHOME/server/Bin64/libnqsdbgatewayoci10g64.so. Check if 'Oracle OCI 10G' database client is installed.

• Make sure export LD_LIBRARY_PATH=$ORACLE_HOME/lib/ and export SHLIB_PATH=$ORACLE_HOME/lib is in the profile.

• Also make changes in $BIHOME/setup/user.sh:

ORACLE_HOME=/orasw/app/oracle/product/11.2.0.3
export ORACLE_HOME
TNS_ADMIN=/orasw/app/oracle/product/11.2.0.3/network/admin
export TNS_ADMIN
PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
export PATH
LIBPATH=$ORACLE_HOME/lib:$LIBPATH:/opt/j2se/jre/lib/sparc
export LIBPATH

OBIEE Catalog Manager Cannot be launched

When getting Java error from OBIEE Catalog Manager lauching:

Modify C:\OracleBI\web\catalogmanager\catalogmanager.ini:

-Xmx1440M
Change to-Xmx512M

Purging Cache via OBIEE analytics page

1. Log into OBIEE using an Admin account (as weblogic)
2. a) Go to Administration Page –

1. Select “Issue SQL” under “Maintenance and Trouble Shooting”

gather_system_stats

SQL> exec dbms_stats.gather_system_stats (gathering_mode => 'noworkload');

PL/SQL procedure successfully completed.

SQL> select sname, pname, pval1 from sys.aux_stats$;

Resize undo table space

1. define a new temporary undo tablespace
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/ccbprod/oradata/ccbprod/UNDOTBS2.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;

Convert UNIX to DOS format using sed command

Type the following command if you are using bash shell:
$ sed 's/$'"/`echo \\\r`/" input.txt > output.txt

Convert DOS newlines (CR/LF) to Unix format using sed command
If you are using BASH shell type the following command (press Ctrl-V then Ctrl-M to get pattern or special symbol)
$ sed 's/^M$//' input.txt > output.txt

NCARGS value configuration for AIX 5.1

NCARGS value configuration (5.1.0)
In AIX 5L Version 5.1, the option has been added to allow the super user or
any user belonging to the system group to dynamically change the value of
the NCARGS parameters. In previous releases of AIX, these values were
permanently defined as 24576, which resulted in a problem similar to that
shown below when a large number of arguments are parsed to a command:
# rm FILE*
ksh: /usr/bin/rm: 0403-027 The parameter list is too long.

The value of NCARGS can be increased to overcome this problem. The value
can be tuned anywhere within the range of 24576 to 524288 in 4 KB page
size increments. To display the value, use the following command.

Typical production init file (10g)

#####################################################################
# Common parms
#####################################################################
ifile = ?/dbs/orabase-dg.ora
#####################################################################
# Structural Parms
#####################################################################
db_domain = psoug
db_block_size = 8192
db_writer_processes = 8

Thread 1 cannot allocate new log

Sometimes, you can see in your alert.log file, the following corresponding
messages:


  Thread 1 advanced to log sequence 248
    Current log# 2 seq# 248 mem# 0: /prod1/oradata/logs/redologs02.log

  Thread 1 cannot allocate new log, sequence 249
  Checkpoint not complete

Resize Redo log

If the redo logs need to be resized, the following steps can be taken. 
1.  Create new log group members with the following command:

ALTER DATABASE ADD LOGFILE
GROUP 4 (    '/ccbprod/oradata/ccbprod/REDO04A.LOG',
        '/ccbprod/oradata/ccbprod/REDO04B.LOG') SIZE 1280M reuse,
GROUP 5 (    '/ccbprod/oradata/ccbprod/REDO05A.LOG',
        '/ccbprod/oradata/ccbprod/REDO05B.LOG') SIZE 1280M reuse,
GROUP 6 (    '/ccbprod/oradata/ccbprod/REDO06A.LOG',
        '/ccbprod/oradata/ccbprod/REDO06B.LOG') SIZE 1280M reuse;

Resize Redo log for RAC

In RAC, you have seperate log groups for each instance (called threads). You can see which group belongs to which thread by querying v$log. When you add new loggroups, you specify which thread it should belong to. The syntax is

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('firstlocation','secondlocation') SIZE 1280M;

Enable/Disable Archive logs in a RAC environment

on node 1:
set oracle_sid=racdb1
-------------Noarchivelog
sqlplus / as sysdba
alter system set cluster_database=false scope=spfile sid='racdb2';



quit

Configure rsh for CentOS
1. check if rsh-server is installed:
[root@rac1 ~]# rpm -qa|grep -i ^rsh-server

2. make sure no disable = no in /etc/xinetd.d/rlogin  :

1.  [root@rac1 ~]# cat /etc/xinetd.d/rlogin  

2.  # default: on  

3.  # description: rlogind is the server for the rlogin(1) program. The server  

4.  # provides a remote login facility with authentication based on  

5.  # privileged port numbers from trusted hosts.  

6.  service login  

7.  {disable = no 

8.  socket_type = stream 

9.  wait = no 

10. user = root 

11. log_on_success += USERID  

12. log_on_failure += USERID  

13. server = /usr/sbin/in.rlogind} 

Configure for oracle Single Instance install with ASM on centos

Create VM:

Oracle Checkpoint 

A checkpoint performs the following three operations:
Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified databaseblocks back to the datafiles.

To change DBIO_EXPECTED for EM

 SELECT parameter_value,is_default FROM dba_advisor_def_parameters WHERE advisor_name = 'ADDM' AND parameter_name ='DBIO_EXPECTED';

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 20000);

select * from aux_stats$;

EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.