Oracle

ORA-12899: importing a .dmp file + character set conversion

Posted on

https://www.queryxchange.com/q/7_17504/ora-12899-importing-a-dmp-file-character-set-conversion/ You can edit the dump file and change the included NLS_LENGTH_SEMANTICS from BYTE to CHAR. Near the beginning of the file you find a line ending like this : ^@02:00:^@^@^D^@BYTE^F^@UNUSED^A^@2^K^INTERPRETED^K^@DISABLE:ALL^@^@ Change BYTE to CHAR using your favorite text editor and save the file. When you will imp that new dump file, all columns will be create […]

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