22 Mart 2014 Cumartesi

ORA-00314: log % of thread 1, expected sequence# % doesn't match


When switch to standby database

alert occur in alert logfile for standby redologs.

problem in alert.log file:

Errors in file /u01/oracle/diag/rdbms/oradb/oradb/trace/uc4prod_arc4_27349.trc:
ORA-00314: log 7 of thread 1, expected sequence# 292376 doesn't match 292348
ORA-00312: online log 7 thread 1: '/u02/oradata/oradb/std_redo01.log'


solution:

alter database clear unarchived logfile group 7;

9 Temmuz 2013 Salı

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


5 Temmuz 2013 Cuma

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';




4 Temmuz 2013 Perşembe

Linux dns TTL time control




[oracle@testdb01 ~]$   host -a testdb02


result sample of command:

;; QUESTION SECTION:
;testdb02 IN   A

;; ANSWER SECTION:
testdb02 492 IN A      192.168.3.24


492 is remaining time  of TTL.   if you try againg TTL is reduced




You can control on windows and unix with nslookup set debug on.





3 Temmuz 2013 Çarşamba

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');



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