Category Archives: Oracle DB troubleshooting

Регистрация базы в listener

Что смотреть, если база не регистрируется в listener’е? 1. определить, на каком имени должен слушать listener. По умолчанию – это имя хоста и оно должно совпадать с именем, прописанным в lsitener.ora 2. проверить, нормально ли резолвится (прописано в /etc/hosts) имя, на котором слушает listener. 3. проверить, что имя не было переопределено с помощью переменной local_listener.

Read more

Database incomplete recovery

В случае создания controlfile есть определенное неудобство: Восстанавливаемую базу нельзя временно открыть read only и продолжить восстановление, так как возникает ошибка “в controlfile отсутствует checkpoint”. То есть для того, чтобы открыть восстанавливаемую базу в read only, нужен controlfile, который хотя бы раз открывался в read-write. recover database using backup controlfile until time ‘2012-06-11:03:00:00’; alter database […]

Read more

Ошибки и trace-файлы

08.02.2012 в логе пишут, что ARC1: Archive log rejected (thread 1 sequence 270488) at host ‘rdb2’ FAL[server, ARC1]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance rdb – Archival Error. Archiver continuing см: ORA-16401 and ORA-16055 reported in primary alert.log when redolog switch is over frequently [ID 1243177.1] проблема […]

Read more

SCN и Oracle

Converting Hexadecimal Oracle SCNs to Decimal Oracle serializes transactions and manages concurrency and recovery using an ever-increasing number called a system change number, or SCN. Usually, we see these numbers expressed in decimal, in places like the v$ views and the alert log. Occasionally, however, Oracle’s code expresses SCNs in hexadecimal. Unfortunately, you can’t just […]

Read more

Getting rid of PARTLY AVAILABLE UNDO segments

https://community.oracle.com/thread/2593502 https://dbatricksworld.com/ora-30013-undo-tablespace-undotbs1-is-currently-in-use/ There are ACTIVE and PARTIAL AVAILABLE segments in UNDO: select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′ SEGMENT_NAME TABLESPACE_NAME STATUS —————————— —————————— —————- _SYSSMU10_820739558$ UNDOTBS1 OFFLINE _SYSSMU9_2448906239$ UNDOTBS1 OFFLINE _SYSSMU8_3066916762$ UNDOTBS1 OFFLINE _SYSSMU7_892861194$ UNDOTBS1 OFFLINE _SYSSMU6_1956589931$ UNDOTBS1 OFFLINE _SYSSMU5_2919322705$ UNDOTBS1 OFFLINE _SYSSMU4_3876247569$ UNDOTBS1 OFFLINE _SYSSMU3_4245574747$ UNDOTBS1 OFFLINE for eaxmple :_SYSSMU2_6654314$ UNDOTBS1 PARTLY AVAILABLE […]

Read more

ASH: queries ranged by run time

select s.SQL_TEXT, aa.cnt, aa.SESSION_SERIAL# from v$sql s, ( select * from ( select count(sample_id) cnt, ash.SESSION_SERIAL#, sql_id, SQL_CHILD_NUMBER, sql_exec_id, top_level_sql_id from v$active_session_history ash where ash.SQL_EXEC_START between &date_from and &date_to group by ash.SESSION_SERIAL#, sql_id, sql_child_number, sql_exec_id, top_level_sql_id order by 1 desc ) where rownum < 200 ) aa where s.SQL_ID = aa.SQL_ID and s.CHILD_NUMBER = aa.sql_child_number order by […]

Read more

Running access advisor

Start advisor task: DECLARE taskname varchar2(30) := ‘SQLACCESS_20160902_1’; task_desc varchar2(256) := ‘SQL Access Advisor’; task_or_template varchar2(30) := ‘SQLACCESS_EMTASK’; task_id number; num_found number; sts_name varchar2(256) := ‘STS_20160902_1’; sts_cursor dbms_sqltune.sqlset_cursor; BEGIN — in case “invalid number parameter’ error need to set NLS parameter: EXECUTE IMMEDIATE ‘alter session set nls_numeric_characters=”.,”’; /* Create Task */ dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, taskname); /* […]

Read more

Oracle Linux Tests

https://oss.oracle.com/projects/olt/dist/documentation/OLT_TestCoverage.pdf Oracle Linux Tests are designed to verify Linux kernel functionality and stability essential for the Oracle Database. The Oracle Linux Test (OLT) kit, which is distributed as an archive file, provides an automated framework, a set of tools and tests for virtualized and non­virtualized systems. This document is for QA teams who conduct the […]

Read more

Exploring PGA usage

Sorting out how Oracle RDBMS uses physical memory. We have: Oracle RDBMS 11g, Solaris 10 SPARC, total 64 GB physical memory available. select sum(pga_alloc_mem)/1024/1024/1024 from v$process 3 GB select sum(value)/1024/1024/1024 from v$sga 35,8 GB sga_max_size = 36 GB swap -s total: 41424832k bytes allocated + 926416k reserved = 42351248k used, 1573376k available # echo ‘::memstat’ […]

Read more

Plan management

Look for sql_id based on a fragment of sql text: select s.CHILD_NUMBER, s.PLAN_HASH_VALUE, s.IS_BIND_SENSITIVE, s.IS_BIND_AWARE, s.* from v$sql s where sql_text like ‘%O.ORDER_NO%’   Explain plan for sql_id: SELECT * FROM table ( DBMS_XPLAN.DISPLAY_CURSOR(sql_id => ‘gd92hchnmrbwc’, cursor_child_no => 0)); select * from TABLE(dbms_xplan.display_awr(‘5q5x7zcnt3c5d’)); Purge bad plan from SGA: select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID […]

Read more

Temporary tablespace usage

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ AS temp_size, a.inst_id as Instance, a.sid||’,’||a.serial# AS sid_serial, NVL(a.username, ‘(oracle)’) AS username, a.program, a.status, a.sql_id FROM   gv$session a, gv$sort_usage b, gv$parameter p WHERE  p.name  = ‘db_block_size’ AND    a.saddr = b.session_addr AND    a.inst_id=b.inst_id AND    a.inst_id=p.inst_id ORDER BY b.tablespace, b.blocks select * from v$temporary_lobs order by cache_lobs desc SELECT A.inst_id, […]

Read more

Purging old trace files

http://www.pafumi.net/11g_Trace_Dump_Files_ADRCI.html   The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data. As you know the location of the alert log files and trace files has changed on 11g: The ADR can be managed via the 11g Enterprise Manager GUI (Database Control and not Grid Control) or […]

Read more

Monitoring oracle RDBMS Standard Edition

http://www.kylehailey.com/category/oracle/wait-events-oracle/ The wait events are rolled up in to groups called wait classes. For wait class we have the following views: V$SYSTEM_WAIT_CLASS – cumulative since start up V$WAITCLASSMETRIC – last 60 seconds deltas V$WAITCLASSMETRIC_HISTORY – 60 seconds deltas for last hour select sum(NVL(h.TIME_WAITED, 0) ) time_waited, n.WAIT_CLASS from v$waitclassmetric_history h, v$system_wait_class n where h.WAIT_CLASS# = n.WAIT_CLASS# group by […]

Read more

Advisor failure troubleshooting

Sympoms: auto space advisor job fails with the error: Errors in file /opt/oracle10/diag/rdbms/rdb3/rdb/trace/rdb_j007_4209.trc:ORA-12012: error on auto execute of job”SYS”.”ORA$AT_SA_SPC_SY_12310″ORA-20000: ORU-10027: buffer overflow, limit of 20000 byt Look at the auto tasks enabled: select * from dba_autotask_client; Disable an auto task: begin dbms_auto_task_admin.DISABLE(client_name => ‘auto space advisor’, operation => NULL, window_name => NULL); end; Find which […]

Read more

SQL plan analysis

Look at the execution plan for the last SQL statement in the session: SELECT * FROM table(dbms_xplan.display_cursor(null,null,’basic’)); Record and display run time statistics: SELECT /*+ gather_plan_statistics */ p.prod_name, SUM(s.quantity_sold) FROM sales s, products p WHERE s.prod_id =p.prod_id GROUP By p.prod_name ; Check if statistics for a table exists and is not stale: select column_name, num_distinct, […]

Read more