Ulfat`s DataBase Blog

Every DBA at least one time faced ORA-02055 exception. It may be occur on several reasons. Here is symptom and reason. ORA-02055: distributed update operation failed; rollback required Cause: a failure during distributed update operation may not have rolled back all effects of the operation. Since some sites may be inconsistent, the transaction must roll back to a savepoint or entirely Action: rollback to a savepoint or rollback transaction and resubmit It will be helpful checking DBA_2PC_PENDING data dictionary view  select * from DBA_2PC_PENDING order by fail_time --take local_tran_id or global_tran_id example 8.44.2012311 Then enable distributed recovery and execute below statements using:        ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY      ...

Rarely every DBA uses Catalog scripts which locates under $ORACLE_HOME/rdbms/admin/ I will not some of them and when and why we use it. Please note, it depends of the Oracle version and release. We can say some of them famous, such as utlrp.sql it compiles invalid objects, it often used database upgrade, catalog.sql creates data dictionary, it used when you create database manually : create database ... etc. There are also some wrapped files (extension *.plb) Here is list (not all) of scripts: catalog.sql - create data dictionary views. cataudit.sql - data dictionary views for auditing. (catnoaud.sql will remove) catblock.sql - create views of oracle locks catclust.sql - create cluster database specific views definitions catdbsyn.sql - catalog dba synonyms (dba_synonyms.sql) catio.sql - collect i/o per table/object stats by statistical sampling catjava.sql - catalog scripts for java catjobq.sql - catalog views for the job queue catjvm.sql - create ...

Using srvctl utility on ORACLE 11g we can stop, start and check status of RAC. Stop/Start RAC instance on Oracle 11g srvctl stop instance -d dbname -i instancename srvctl stop instance -d myrac -i rac1 srvctl start instance -d dbname -i instancename srvctl start instance -d myrac -i rac2 Stop/Start RAC database srvctl stop database -d dbname -o immediate srvctl stop database -d myrac -o immediate P.S: immediate is optional and has alternative : normal, transactional, abort  srvctl start database -d dbname srvctl start database -d myrac P.S: Can be used :  nomount, mount, open Status RAC {NODE, ASM, INSTANCE, DATABASE}  srvctl status nodeapps -n nodename srvctl status nodeapps -n rac2 srvctl status asm -n nodename srvctl status asm -n rac1 srvctl status instance -d dbname -i inst_name srvctl status instance -d myrac -i rac2 srvctl status database -d racdb srvctl status database -d racdb srvctl config database -d racdb  --prov...

Today I try to open EM and monitor DB. Unfortunately it was failed and show me "Enterprise Manager is not able to connect to the database instance. The state of the components are listed below." Authentication passed but it does not work. If problem on authentication check sysman user`s account status. SQL> select account_status from dba_users where username='SYSMAN'; Also it will helpful checking emctl`s log which locates under: /u01/app/oracle/product/11.2.0/dbhome_1/localhost.localdomain_ulfetdb/sysman/log After checking logs I decided to recreate dbcontrol using below command. (Unfortunately I could not provide content of log) It will ask some parameters (some of them optional, just enter and skip) Also to be patient, it may last several minutes... [oracle@localhost ~]$ emca -config dbcontrol db -repos recreate STARTED EMCA at Dec 26, 2012 10:19:51 AM EM Configuration Assistant, Version 11.2.0.0.2 Production Copyright (c) 2003, 2005, ...

Sometimes some database object(s) stays invalid (there is a lot of reason: modification object, compiling new procedure, function, package (depending exists) etc). Easy method is compile @$ORACLE_HOME/rdbms/admin/utlrp.sql or detect invalid objects and recompile it. But it is dangerous every time to use utlrp, it is recommended to use utlrp when upgrade db version ex : 9 > 10 > 11 (also release upgrade). Because while upgrade process DB is not available to use for end user. But if invalid object count is not big and not a few you can use below single SQL and compile result of it. SQL> select 'ALTER TRIGGER '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects where status='INVALID' AND OBJECT_TYPE='TRIGGER' union select 'ALTER FUNCTION '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects where status='INVALID' AND OBJECT_TYPE='FUNCTION' union select 'ALTER PACKAGE '||OWNE...

I want to create database link on base a database. It means link from database to itself. My database name is: MYDB SQL> select name from v$database; NAME --------- MYDB SQL> --Now try to create database link via hr user. SQL> create database link mydb_mydb connect to hr identified by hr using 'mydb'; Database link created. SQL> --Succeed, now let`s check. SQL> select * from dual@mydb_mydb; select * from dual@mydb_mydb                    * ERROR at line 1: ORA-02085: database link MYDB_MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM What is that ? After researching I found : ORA-02085 : database link string connects to string Cause : A database link connected to a database with a different name. The connection is rejected . Action : create a database link with the same name as the database the database it connect...

How to find which SQL statement generates lots of redo logs. Firstly use below query to find out which sessions generate more redo logs: SELECT  s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid AND i.block_changes > 0 ORDER BY 5 DESC, 1; block_changes column provide us how much blocks have been changed the session P.S: You can omit block_changes > 0 condition if you want to see all result But, what about if you want to see amount of undo blocks and undo records accessed by the transaction? To do that use: SELECT s.sid, s.serial#, s.username, s.program, s.machine, t.used_ublk, t.used_urec FROM v$session s, v$transaction t WHERE s.taddr = t.addr ORDER BY 6, 7 desc; At the end, to get object lists which block changed historical information: SELECT dhso.object_name, object_type, SUM (db_block_changes_delta) FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_sna...

One of the best feature for Oracle 11g is setting result cache. Result cache reside in Shared Pool area in SGA. Now database can serves result for queries from the cache instead of re-execute query again and again. But when depending database object is modified cache result stays invalid.  Using result cache you can improve performance of your query. It is reduce I/O, sorts, computations. There are some restrictions for result cache which could not store data: Data dictionary objects; Temporary tables; Queries with bind variables (if different) ; Flashback query`s result; Some SQL functions : current_date, current/local_timestamp, sysdate etc; Function which has any OUT or IN OUT parameter; Function has IN parameter that are CLOB, BLOB, REF CURSOR etc; etc How to configure result cache New initialize parameters RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MODE exists on Oracle 11g. You can enable/disable result cache using  RESULT_CACHE_MAX_SIZE par...

Using EXPDP take dump from higher version and import into lower version. As you know EXPDP utility firstly was introduce in Oracle 10g. This is enhanced EXP/IMP utility (with additional features, example transport tablespace). I will use Oracle 11g R2 (11.2.0.1.0) and Oracle 10g R2 (10.2.0.4) Let`s create directory on Oracle 11g database and give read, write permission. SQL> create directory my_dir as '/u01/app/oracle/admin/ulfetdb/dpdump'; Directory created. SQL> grant read, write on directory my_dir to ulfet; Grant succeeded. SQL> Now I will connect as ulfet user and will create new table and insert into one record. SQL> conn ulfet/ulfet Connected. SQL> create table test_tbl (id number, name varchar2(10)); Table created. SQL> insert into test_tbl values(1, 'Omar'); 1 row created. SQL> commit; Commit complete. Time to take export of table using DATAPUMP. [oracle@localhost dpdump]$ expdp ulfet/ulfet@ulfetdb ...