Monday, May 7, 2012

TDPO for TSM setup on AIX

1. make sure oracle id has full access to tsm installation folder and files.
2. vi /usr/tivoli/tsm/client/ba/bin64/dsm.sys:
    SErvername your TSM server name, for example, TSM_ORA
    COMMmethod  TCPIP
    TCPPort     1500
    TcpServerAddress tsm server IP
    NODENAME  client_node registered on your TSM server(for example, client_ora) 
3. vi /usr/tivoli/tsm/client/ba/bin64/dsm.opt:
    SErvername  your TSM server name, for example, TSM_ORA 
4. su - oracle
5. ln -s /usr/lib/libobk64.a $ORACLE_HOME/lib/libobk.a
6. vi /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt:
    DSMI_ORC_CONFIG    (location of dsm.opt, for example, /usr/tivoli/tsm/client/oracle/bin64/dsm.opt)
    DSMI_DIR           (location for dsm.sys,for example, /usr/tivoli/tsm/client/oracle/bin64)
    TDPO_FS            (Space name for backup on TSM, for example, adsmorc
    TDPO_NODE      (Nodename for client, usually the servername of client)
    TDPO_OWNER   (TDPO owner, for example, oracle)
    TDPO_PSWDPATH      (tdpoconf password file location)
    DSMI_LOG          (Log file location, for example, /home/oracle)
7. tdpoconf password -tdpo_optfile=<full path of tdpo.opt file>
    It will create a password file TDPO.nodename
    You should get the password from the TSM admin
8. ln -s /usr/tivoli/tsm/client/lang/EN_US /usr/tivoli/tsm/client/oracle/bin64/EN_US
9. make sure user oracle has full access to all files under /usr/tivoli/tsm/client/oracle/bin64
10. test: $ORACLE_HOME/bin/sbttest test
    if you see:
    The sbt function pointers are loaded from libobk.a(shr.o) library.
    -- sbtinit succeeded

    Means the tdpo for oracle are configured successfully.

Wednesday, May 2, 2012

ORA-20005: object statistics are locked (stattype = ALL) after database clone

After database clone or datapump import, you may get the error: "ORA-20005: object statistics are locked (stattype = ALL)":
SQL> exec dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30);
BEGIN dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

Tuesday, May 1, 2012

Check the value of ESTIMATE_PERCENT:

The default value of estimate is AUTO_SAMPLE_SIZE.

10g

SQL> select
DBMS_STATS.GET_PARAM ('AUTOSTATS_TARGET'),
DBMS_STATS.GET_PARAM ('CASCADE'),
DBMS_STATS.GET_PARAM ('DEGREE'),
DBMS_STATS.GET_PARAM ('ESTIMATE_PERCENT'),
DBMS_STATS.GET_PARAM ('METHOD_OPT'),
DBMS_STATS.GET_PARAM ('NO_INVALIDATE'),
DBMS_STATS.GET_PARAM ('GRANULARITY')
from dual;

11g

SQL> select
DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET'),
DBMS_STATS.GET_PREFS ('CASCADE'),
DBMS_STATS.GET_PREFS ('DEGREE'),
DBMS_STATS.GET_PREFS ('ESTIMATE_PERCENT'),
DBMS_STATS.GET_PREFS ('METHOD_OPT'),
DBMS_STATS.GET_PREFS ('NO_INVALIDATE'),
DBMS_STATS.GET_PREFS ('GRANULARITY'),
DBMS_STATS.GET_PREFS ('PUBLISH'),
DBMS_STATS.GET_PREFS ('INCREMENTAL'),
DBMS_STATS.GET_PREFS ('STALE_PERCENT')
from dual;

GATHER_STATS_JOB

To check the job status in 10g:
select * from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_log where job_name = 'GATHER_STATS_JOB';

To Check the scheduler for this job:
select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';
SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS; 
If the state is not "Scheduled":
EXEC DBMS_SCHEDULER.ENABLE ('GATHER_STATS_JOB');
if the window is not enabled:
EXEC DBMS_SCHEDULER.ENABLE ('SYS.WINDOWNAME');

You can also create the job on different user and execute it:
BEGIN
dbms_scheduler.create_Job (job_name=>'Gather_Stats_Job',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN Dbms_Stats.gather_database_stats(cascade=>true); end;',
start_date => sysdate,
repeat_interval => 'freq=daily',
enabled=>true);
end;
/

BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/

To disable gather_stats_job in 10g:
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
To drop the job, login as creator and run:
EXEC DBMS_SCHEDULER.DROP_JOB (JOB_NAME=>'GATHER_STATS_JOB', force => true);

Friday, April 27, 2012

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).

Monday, April 23, 2012

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