Re-link fails on target "client_sharedlib" when applying PSU

Verifying the update...
Make failed to invoke "/usr/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1"....'genclntsh: genclntsh: Could not locate  /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/shrept.lst
make: *** [client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1"....'genclntsh: genclntsh: Could not locate  /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/shrept.lst
make: *** [client_sharedlib] Error 1
'

The following make actions have failed :

Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".

check your shrep.lst file in $ORACLE_HOME/network/admin

11.2.0.3 shrep.lst   file entries should be:

# function entry points for genclntsh.sh

network : snaumihi_inithostinfo

network : snaumbg_gmt

network : naedpwd_encrypt

network : naumbsb_bld_singlebyte

network : ztapis

network : nlgh

network : ztvp52

ORA-00245: control file backup failed; target is likely on a local file system

with RAC environment

error:

INF - RMAN-03009: failure of backup command on ch00 channel at 06/04/2011 13:50:04
INF - ORA-00245: control file backup failed; target is likely on a local file system


check your SNAPSHOT CONTROLFILE parameter with RMAN
replace parameter shared location  or ASM location.



[oracle@testdb01 oracle]$ rman 

RMAN> connect target /

RMAN> show SNAPSHOT CONTROLFILE NAME;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_testdb1.f'; # default

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATADG/snapcf_testdb.f';

Create AWR report by sqlplus

set heading off;
set feedback off;
set linesize 1500;

select snap_id,dbid,begin_interval_time,end_interval_time from DBA_HIST_SNAPSHOT
order by 1;

replace your snap_id and dbid wtih following sql:

spool awr.html
select output from
table(dbms_workload_repository.awr_report_html(dbid,1,,));
spool off

Copy Table Statistics (DBMS_STATS)

--before 11g

DBMS_STATS.CREATE_STAT_TABLE ('APPUSER','MYSTATS_TAB');

delete from MYSTATS_TAB;
commit;

exec dbms_stats.unlock_table_stats('APPUSER','MYPART_TAB');

--export
exec dbms_stats.export_table_stats('APPUSER','MYPART_TAB','MYPART_TAB_201303','MYSTATS_TAB',NULL,TRUE,NULL);

update APPUSER.MYSTATS_TAB set c2='MYPART_TAB_201304';
commit;

--import
exec dbms_stats.import_table_stats('APPUSER','MYPART_TAB','MYPART_TAB_201304','MYSTATS_TAB',NULL);
commit;

exec dbms_stats.lock_table_stats('APPUSER','MYPART_TAB');

--11g or newer

exec dbms_stats.unlock_table_stats('APPUSER','MYPART_TAB');

exec DBMS_STATS.COPY_TABLE_STATS ('APPUSER','MYPART_TAB','MYPART_TAB_201303','MYPART_TAB_201304');

exec dbms_stats.lock_table_stats('APPUSER','MYPART_TAB');

Change scan_listener port in 11.2

check current port

$GRID_HOME/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521


--change port

$GRID_HOME/bin/srvctl modify scan_listener -p 8003

--change remote listener parameter

alter system set remote_listener=':' scope=both;


--restart scan_listener

$GRID_HOME/bin/srvctl stop scan_listener
$GRID_HOME/bin/srvctl start scan_listener


--confirm change

$GRID_HOME/bin/srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:8003
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:8003
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:8003

ORA-12012: error on auto execute of job "EXFSYS"."RLM$EVTCLEANUP"

Catalog db has an error in alert.log like :

Errors in file /u01/diag/rdbms/rmandb/rmandb/trace/rmandb_j000_25520.trc:
ORA-12012: error on auto execute of job "EXFSYS"."RLM$EVTCLEANUP"
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "EXFSYS.DBMS_RLMGR_DR"
ORA-06508: PL/SQL: could not find program unit being called: "EXFSYS.DBMS_RLMGR_DR"
ORA-06512: at line 1

check invalid objects and compile it or run utlrp.sql

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
        0

SQL> @?/rdbms/admin/utlrp.sql

try againg and same error...
and flush shared_pool.

SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;

try again and it works.

Debuging PL/SQL Procedure

Which privileges do you need?

grant debug any procedure to appuser;
grant debug connect session to appuser;

You must compile with debug option procedure before to run.

alter procedure my_proc compile debug;


http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm#i2062318

DEBUGGING:	—
DEBUG CONNECT SESSION	Connect the current session to a debugger.
DEBUG ANY PROCEDURE	Debug all PL/SQL and Java code in any database object. Display information on all SQL statements executed by the application. Note: Granting this privilege is equivalent to granting the DEBUG object privilege on all applicable objects in the database.

Interval partition

Interval partitioning is an extension to range partitioning.
You can use interval partition to automatically add new partitions.

Sample


CREATE TABLE mypart_table
(
  TARIH         DATE                            NOT NULL,
  KOD           NUMBER(5)                       NOT NULL,
  ALAN          VARCHAR2(600 BYTE),

)
PARTITION BY RANGE (TARIH)
(  
  PARTITION mypart_table_201303 VALUES LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
  PARTITION mypart_table_201304 VALUES LESS THAN (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
  PARTITION mypart_table_201305 VALUES LESS THAN (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
  PARTITION mypart_table_201306 VALUES LESS THAN (TO_DATE('2013-07-01', 'YYYY-MM-DD'))
)
;


CREATE INDEX BANKDB.IDX_mypart_table_01 ON mypart_table
(TARIH, KOD)
LOCAL;


--add partition interval 1 day

alter table mypart_table set INTERVAL( NUMTODSINTERVAL(1,'DAY'));

You can't modify interval partitions. You can disable and enable again.

--disable interval 
ALTER TABLE mypart_table SET INTERVAL ();

When to Use Range or Interval Partitioning

http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_avail.htm#sthref493

Using JDBC with Firewalls

from Oracle® Database JDBC Developer's Guide and Reference

http://docs.oracle.com/cd/B19306_01/java.102/b14355/apxtblsh.htm#CHDBBDDA

Firewall timeout for idle-connections may sever a connection. This can cause JDBC applications to hang while waiting for a connection. You can perform one or more of the following actions to avoid connections from being severed due to firewall timeout:

• If you are using connection caching or connection pooling, then always set the inactivity timeout value on the connection cache to be shorter than the firewall idle timeout value.
• Pass oracle.net.READ_TIMEOUT as connection property to enable read timeout on socket. The timeout value is in milliseconds.
• For both JDBC OCI and JDBC Thin drivers, use net descriptor to connect to the database and specify the ENABLE=BROKEN parameter in the DESCRIPTIONclause in the connect descriptor. Also, set a lower value for tcp_keepalive_interval.
• Enable Oracle Net DCD by setting SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file on the server-side.

How to call External Procedure from Oracle Database

create a file which has name "shell.c"

content of shell.c file
------------------------------------
#include
#include
#include

void sh(char *);

void sh( char *cmd )
{
int num;

num = system(cmd);
}
------------------------------------

--create object file
[oracle@host01 lib]$  gcc -fPIC -g -c -Wall shell.c

--create shared library file
[oracle@host01 lib]$  gcc -shared -o shell.so shell.o


modify parameter in $ORACLE_HOME/hs/admin/extproc.ora file

SET EXTPROC_DLLS=/home/oracle/lib/shell.so

--create lib and procedure for test

CREATE OR REPLACE LIBRARY SHELL_LIB
 IS '/home/oracle/lib/shell.so'
/

CREATE OR REPLACE PROCEDURE "SHELL" (cmd IN char)
as external
name "sh"
library shell_lib
language C
parameters (cmd string);
/


SQL> exec shell('/bin/pwd > /home/oracle/lib/aa.txt');


output file looks like

[oracle@host01 lib]$ more aa.txt
/u01/oracle/11.2.0.3/dbs

ORA-28575: unable to open RPC connection to external procedure agent

RMAN ORA-01008: not all variables bound

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 10 10:33:11 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /


DBGSQL:     TARGET> select count(*) into :dbstate from v$parameter where lower(name) = '_dummy_instance' and upper(value) = 'TRUE'
DBGSQL:        sqlcode = 1008
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01008: not all variables bound




workaround:

SQL>alter system flush shared_pool;

Oracle database trigger order

Oracle Database fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

Execution steps of trigger.
http://docs.oracle.com/cd/B28359_01/server.111/b28318/triggers.htm#CNCPT418


 DBA_TRIGGER_ORDERING description
http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2107.htm#REFRN20581

sample:

create or replace trigger trigger_01
before insert on test
for each row
follows trigger_02
....


create or replace trigger trigger_02
before insert on test
for each row

sqlplus ORA-01031: insufficient privileges

You want to connect intance to start.(oracle 11g)

but there is an error.

like

[oracle@oratest01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 23 02:00:05 2009

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

1-Check groups in linux.(if dba group doesn't exist firstly create it)

more /etc/group
.
....

oinstall:x:500:
dba:x:501:oracle


2-Check your oracle user groups

more /etc/passwd
.
....
oracle:x:500:500::/home/oracle:/bin/bash



[root@oratest01 ~]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

RMAN-20032: checkpoint change# too low

If you use catalog for backup


RMAN> resync database;
RMAN-20032: checkpoint change# too low

If database returned from cold backup resync can't be succesful.

solution

connect target /
connect catalog user@catdb

unregister database;
register database;

ORA-22858: invalid alteration of datatype (convert varchar2 to clob)

When you convert a field from varchar2 to clob
raise an error ORA-22858: invalid alteration of datatype

SQL> create table vty_ra1 (a varchar2(10));



SQL> alter table vty_ra1 modify a clob;



ORA-22858: invalid alteration of datatype

workaround:

alter table vty_ra1 modify a long;




alter table vty_ra1 modify a clob;

RMAN-00569: ERROR MESSAGE STACK FOLLOWS

When you want to backup your db with using catalog database.

If raise an error like follow

INF - RMAN-00571: ===========================================================
INF - RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
INF - RMAN-00571: ===========================================================
INF - Recovery Manager complete.
INF - End of Recovery Manager output.
INF - End Oracle Recovery Manager.

Cause

Catalog db's tns entry must be in tnsnames.ora.

Check your $ORACLE_HOME/network/admin/tnsnames.ora file.

How to open large file more than 2GB

in C program you can add line.

#define _FILE_OFFSET_BITS 64

Uninstall ORACLE HOME manually

You can use to deinstall ORACLE_HOME from inventory with following method.(oracle database or grid home)

export ORACLE_HOME=

# detach 
$ORACLE_HOME/oui/bin/runInstaller -detachHome -silent -local ORACLE_HOME=$ORACLE_HOME

# confirm:
$ORACLE_HOME/OPatch/opatch lsinventory -all   

# remove directory
rm -rf $ORACLE_HOME

MUST_BE_SAME_TIMEZONE_FILE_VERSION

When you upgrade database to 11g r2

when running catupgrd.sql

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
  2     FROM sys.props$
  3     WHERE
  4       (
  5        ((SELECT TO_NUMBER(value$) from sys.props$
  6           WHERE name = 'DST_PRIMARY_TT_VERSION') !=
  7         (SELECT tz_version from registry$database))
  8        AND
  9        (((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
10           '9.2.') OR
11         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
12           '10.1') OR
13         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
14           '10.2') OR
15         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
16           '11.1'))
17       );
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
                 *
ERROR at line 1:
ORA-01722: invalid number


control following steps


SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14


SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        4


If version of timezone_file is different than TZ_VERSION
run following update.

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
SQL> commit;


try to run catupgrd.sql

Oracle JDBC Connection Test

Save source  file: Conn.java

import java.sql.*;
class Conn {
  public static void main (String[] args) throws Exception
  {
   Class.forName ("oracle.jdbc.OracleDriver");

   Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   try {
     Statement stmt = conn.createStatement();
     try {
       ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
       try {
         while (rset.next())
           System.out.println (rset.getString(1));   // Print col 1
       } 
       finally {
          try { rset.close(); } catch (Exception ignore) {}
       }
     } 
     finally {
       try { stmt.close(); } catch (Exception ignore) {}
     }
   } 
   finally {
     try { conn.close(); } catch (Exception ignore) {}
   }
  }
}

ps: Change host, port, SID, user and password to your value in connection url

("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");

Compile java

javac -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn.java

Run program

java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn



PS : for compile and run in windows os, change columns(:) to semicolons(;)  like
.;$ORACLE_HOME/jdbc/lib/ojdbc5.jar;$ORACLE_HOME/jlib/orai18n.jar

also change jar names in their directories the correct ones.

simple java tricks

http://www.oracle.com/technetwork/java/compile-136656.html