Oracle DB troubleshooting

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

Posted on

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

Oracle DB troubleshooting

Database incomplete recovery

Posted on

В случае создания 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 […]

Oracle DB troubleshooting

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

Posted on

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] проблема […]

Oracle DB administering

SCN и Oracle

Posted on

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 […]

Oracle DB administering

Getting rid of PARTLY AVAILABLE UNDO segments

Posted on

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 […]

Oracle DB programming

Managed logging in PL/SQL

Posted on

1. Call log function inside the procedure: procedure my_procedure is begin ….. IF $$debug_code THEN pkg_log.write_log (‘Log message’); END IF; …. end;     2. Switch logging on with alter package pkg_name compile body plsql_ccflags = ‘debug_code:true’ reuse settings;

Oracle DB troubleshooting

ASH: queries ranged by run time

Posted on

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 […]

Oracle DB administering

Running access advisor

Posted on

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); /* […]

Oracle DB administering

Oracle Linux Tests

Posted on

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 […]