28 Haziran 2013 Cuma

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.

27 Haziran 2013 Perşembe

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


24 Haziran 2013 Pazartesi

Export DDL with DBMS_METADATA



SQL>
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);

select
  dbms_metadata.get_ddl('FUNCTION','SAMPLE_FUNC','APPUSER')
from dual;


14 Haziran 2013 Cuma

ORA-01111: name for data file 350 is unknown - rename to correct file



Error in alert.log

ORA-01111: name for data file 350 is unknown - rename to correct file
ORA-01110: data file 350: '/u01/app/oracle/product/11.2.0.3/dbs/UNNAMED00350'
ORA-01157: cannot identify/lock data file 350 - see DBWR trace file
ORA-01111: name for data file 350 is unknown - rename to correct file
ORA-01110: data file 350: '/u01/app/oracle/product/11.2.0.3/dbs/UNNAMED00350'
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (ORCL)

--check broken filename on standby
SQL> select name from v$datafile where file#=350;

NAME
-------------------------------------------------------
/u01/app/oracle/product/11.2.0.3/dbs/UNNAMED00350

--modify parameter
alter system set standby_file_management=manual; 

--create datafile with correct filename

alter database create datafile '/u01/app/oracle/product/11.2.0.3/dbs/UNNAMED00350' as '/dev/ORCL/datadf130';

--modify parameter again to auto
alter system set standby_file_management=auto;

--start recovery
alter database recover managed standby database disconnect from session;

--check mrp
select process, status , sequence# from v$managed_standby;

11 Haziran 2013 Salı

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.