backup control file to trace

Скрипт созданный в трассировочном файле используется для восстановления контрольного файла после потери всех его копий.

Если база сконфигурирована надлежащим образом (имеет несколько копий управляющего файла на разных дисках и контроллерах), то мало вероятно что придется использовать скрипт из trace-а, тем не менее советуют резервировать управляющий файл с использованием трассировочного файла после каждого изменения физической структуры базы данных (добавление табличного пространства, файлов данных, групп журнальных файлов). Администратор получит историю изменений в текстовом виде, обязательно пригодится.

Новичкам сгенерированный скрипт даст массу полезной информации, например при помощи его я узнал как зарегистрировать файл archive-лога созданного после последнего бэкапа, и таким образом восстановить базу данных на другом сервере до последнего момента включая все archive-логи на продакшене.

Трассировочные копии управляющего файла могут быть созданы с помощью Enterprice Manager, в Oracle 11g на странице Server, раздел Storage, ссылка Control Files, на закладке General, кнопка Backup To Trace.

Или вручную по команде SQL:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Местоположение создаваемого трассировочного файла, задается параметром инициализации USER_DUMP_DEST. Его имя в формате: sid_ora_pid.trc, где pid - номер серверного процесса.

Сложновато было мне его найти в куче трассировочных файлов генерируемых сервером, использовал поиск в файле по следующим словам:

CREATE CONTROLFILE

Файл избавил от ненужной информации о трассировке событий и отправил в Subversion System.

Oracle 12c

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/ctl_file.sql' NORESETLOGS;

NORESETLOGS - говорит Oracle записать один SQL оператор в трейс файл. Если не указать NORESETLOGS, то Oracle запишет 2 SQL в трейс файл: один для пересоздания контрол файла с NORESETLOGS опцией и один для пересоздания контрол файла с RESETLOGS.

How to finding Max SESSION_CACHED_CURSORS in use

Перепечатываю с блога Oracle Logbook

select max(value)
from v$sesstat natural join v$statname
where name = 'session cursor cache count';

-- How to finding maximum session_cached_cursors
select session_cached_cursors * 30+30 Cached_Cursors_Rounded,
       sessions_count
  from (
        select trunc(value/30)  SESSION_CACHED_CURSORS,
               count(*) sessions_count
          from V$sesstat natural join v$statname
         where name = 'session cursor cache count'
         group by trunc(value/30) order by 1
       );

CACHED_CURSORS_ROUNDED SESSIONS_COUNT 
---------------------- -------------- 
                    30            223     
                    60             32     
                    90             15     
                   120              6     
                   150              2     
                   180              3     
                   210              5     

7 rows selected.

В последней строке результата значение 210 это именно то значение в которое необходимо установить параметр SESSION_CACHED_CURSORS.

Необходимо время от времени мониторить состояние и корректировать параметр, но не переусердствуйте, память сервера используется и для других процессов.

Версия для Oracle 11g с использованием LISTAGG, отображает какие usernames сколько курсоров используют:

set linesize 254
col sessions format a30
col usernames format a70

select session_cached_cursors * 30+30 Cached_Cursors_Rounded,
       sessions_count, 
       sessions, 
       usernames
  from (
        select SESSION_CACHED_CURSORS,
               sum(sessions_count) sessions_count,               
               LISTAGG(sessions, ',') WITHIN GROUP (ORDER BY sessions) sessions,               
               LISTAGG(username || '(' || user_cnt || ')', ',') WITHIN GROUP (ORDER BY sessions) usernames
          from (
                select trunc(value/30)  SESSION_CACHED_CURSORS,
                       count(*) sessions_count,                        
                       username,
                       LISTAGG(sid, ',') WITHIN GROUP (ORDER BY sid) sessions,
                       count(username) user_cnt 
                       --LISTAGG(username, ',') WITHIN GROUP (ORDER BY sid) usernames 
                  from v$sesstat ss natural join v$statname sn 
                  natural join v$session s 
                 where name = 'session cursor cache count' 
                 group by trunc(value/30), username
               )
         group by SESSION_CACHED_CURSORS
       )
 order by SESSION_CACHED_CURSORS;

Apply new SESSION_CACHED_CURSORS

SQL> alter system set session_cached_cursors=210 scope = spfile;
shutdown immediate;
startup;

Duplicate control file (Дублирование управляющего файла)

Oracle рекомендует использовать несколько идентичных контрольных файлов, хранящихся на разных дисках. Если управляющий файл утерян, то взяв его копию можно перезапустить экземпляр без необходимости восстановления базы данных.

Сначала изменим SPFILE:

SQL> ALTER SYSTEM SET control_files =
'$HOME/ORADATA/u01/ctrl01.ctl',
'$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;

Остановим БД в нормальном режиме:

SQL> shutdown

Затем создаем копии управляющего файла:

$ cp $HOME/ORADATA/u01/ctrl01.ctl
     $HOME/ORADATA/u02/ctrl02.ctl

И запускаем базу данных:

SQL> startup

Generation date range with specified increments

Запрос "Генерация диапазона дат с указанным приращением" мною используется когда надо сделать срезы итоговых данных с периодом в сутки или ежечасно. Другой вариант применения когда недостаточно данных в итоговых таблицах, например там хранится запись изменения состояния на определенную дату, а необходимо чтобы это состояние было на все даты до смены этого состояние на следующее значение.

Итак есть начало диапазона - date_begin, окончание диапазона - date_end (включая/не включая ее) и приращение - increase. Также был добавлен признак необходимости включения/исключения в/из диапазон(а) окончания диапазона (date_end), назовём его include_date_end.

Допустимые значения для include_date_end:

• 0 - не включать date_end в последовательность;
• 1 - включать

Представим всё это в виде запроса:

     select 
            to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
            to_date('02.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
            1/24 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
            0 include_date_end -- 0 - не включать date_end; 1 - включать
       from dual 

Для генерации диапазона используем возможности запроса connect by:

select level
  from dual
  connect by
    0 + level <= 7

Преобразуем запрос под наши нужды, используя with для удобства:

 -- Generation date range with specified increments
 -- increase 1/24 - один час
 -- 0 include_date_end - не включать date_end
with 
  range_vars as ( 
    select 
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
           to_date('02.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
           1/24 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
           0 include_date_end -- 0 - не включать date_end; 1 - включать
      from dual 
  ),
  range_dates as (
    select 
           date_begin + (level * increase)  - increase date_increase 
      from range_vars 
      connect by 
        date_begin + (level * increase) <= date_end + case include_date_end 
                                                        when 1 then increase
                                                        else 0
                                                      end 
  ) 
select to_char(date_increase, 'DD.MM.YYYY HH24:MI:SS') date_increase
  from range_dates

Полученный выше запрос генерирует диапазон дат между 01 октября 2010 года и 02 октября 2010 года с приращение в один час(1/24). Как видно из результата date_end не включена в диапазон:

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
01.10.2010 01:00:00
01.10.2010 02:00:00
01.10.2010 03:00:00
01.10.2010 04:00:00
01.10.2010 05:00:00
01.10.2010 06:00:00
01.10.2010 07:00:00
01.10.2010 08:00:00
01.10.2010 09:00:00
01.10.2010 10:00:00
01.10.2010 11:00:00
01.10.2010 12:00:00
01.10.2010 13:00:00
01.10.2010 14:00:00
01.10.2010 15:00:00
01.10.2010 16:00:00
01.10.2010 17:00:00
01.10.2010 18:00:00
01.10.2010 19:00:00
01.10.2010 20:00:00

DATE_INCREASE      
-------------------
01.10.2010 21:00:00
01.10.2010 22:00:00
01.10.2010 23:00:00

24 rows selected.

Можно указывать любое приращение, вот генерация диапазона по дням (включая date_end):

-- Generation date range with specified increments
 -- increase 1 - одни сутки
 -- 1 include_date_end - включать date_end
with 
  range_vars as ( 
    select 
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_begin, 
           to_date('11.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS') date_end, 
           1 increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
           1 include_date_end -- 0 - не включать date_end; 1 - включать
      from dual 
  ),
  range_dates as (
    select 
           date_begin + (level * increase)  - increase date_increase 
      from range_vars 
      connect by 
        date_begin + (level * increase) <= date_end + case include_date_end 
                                                        when 1 then increase
                                                        else 0
                                                      end 
  ) 
select to_char(date_increase, 'DD.MM.YYYY HH24:MI:SS') date_increase
  from range_dates;

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
02.10.2010 00:00:00
03.10.2010 00:00:00
04.10.2010 00:00:00
05.10.2010 00:00:00
06.10.2010 00:00:00
07.10.2010 00:00:00
08.10.2010 00:00:00
09.10.2010 00:00:00
10.10.2010 00:00:00
11.10.2010 00:00:00

11 rows selected.

И на закуску для удобства использования создана pipelined function:

create or replace type t_date_table is table of date;
/

Type created.

create or replace function get_range_dates(
  p_date_begin date, p_date_end date,
  p_increase number, p_include_date_end number
) return t_date_table pipelined
as
begin
  for rec in (
    -- Generation date range with specified increments
    with 
      range_vars as ( 
        select 
               p_date_begin date_begin, 
               p_date_end date_end, 
               p_increase increase, -- 1 - сутки; 1/2 - полдня; 1/24 - один час
               p_include_date_end include_date_end -- 0 - не включать date_end; 1 - включать
          from dual 
      ),
      range_dates as (
        select 
               date_begin + (level * increase)  - increase date_increase 
          from range_vars 
          connect by 
            date_begin + (level * increase) <= date_end + case include_date_end 
                                                            when 1 then increase
                                                            else 0
                                                          end 
      ) 
    select date_increase
      from range_dates
  ) loop
    pipe row (rec.date_increase);
  end loop;
      
  return;
end;
/

Function created.

-- Generation date range with specified increments
-- increase 1/2 - полдня
-- 1 include_date_end - включать date_end
select to_char(column_value, 'DD.MM.YYYY HH24:MI:SS') date_increase 
  from table(
         get_range_dates(
           to_date('01.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS'), -- date_begin 
           to_date('05.10.2010 00:00:00', 'DD.MM.YYYY HH24:MI:SS'), -- date_end 
           1/2, -- increase полдня
           1 -- include_date_end
         )
       );

DATE_INCREASE      
-------------------
01.10.2010 00:00:00
01.10.2010 12:00:00
02.10.2010 00:00:00
02.10.2010 12:00:00
03.10.2010 00:00:00
03.10.2010 12:00:00
04.10.2010 00:00:00
04.10.2010 12:00:00
05.10.2010 00:00:00

9 rows selected.

Customizing Locale Data NLS_SORT=UKRUMIX

    Недавно получилась забавная ситуация: во время сортировки буква Э оказалась между К и М вместо того чтобы быть на своем месте: перед Ю и Я. Оказалось ничего удивительного, на клиентских машинах локализация Windows установлена как UKRAINIAN и при инсталляции Oracle Client для программ которые работают с базой данных Oracle устанавливается украинская локализация(в реестре прописывается ключ NLS_LANG = UKRAINIAN_UKRAINE.CL8MSWIN1251), в которой буквы Э не существует, вот она и всплывает не на своем месте. Пока что данные в базе на русском и поэтому надо будет букву Э поставить на свое место в украинской локализации.

    Разработчики разными способами выходят из этой ситуации и самый первый способ к которому они прибегают - это решить проблему при помощи SQL. Но в Oracle существует более элегантный способ - настроить локализацию на сервере баз данных при помощи утилиты : Locale Builder.

    Назовем нашу локализацию UKRUMIX - добавим в украинский букву Э, а также мне не понравилась наша буква Ґ, американцы кажется не ту букву приняли за используемую у нас в Украине.

1. Запуск утилиты Locale Builder:
   Start > Programs > Oracle-OraHome10 > Configuration and Migration Tools > Locale Builder
   или
   $oracle_home/nls/lbuilder/lbuilder.bat
   
2. Переходим на Creating a New Linguistic Sort with the Oracle Locale Builder
   Нам новой сортировки не надо, мы скопируем существующую Linguistic Sort=UKRAINIAN,
   добавим в нужную позицию необходимые нам буквы, сохраним в новый файл в директорию D:\Temp, имя файла предложит утилита сама
   
   File->Open...->By Object Name...
   
   
   
   
   Выбираем Linguistic Sort(ID): UKRAINIAN(46)
   
   
   
   
   Откроется закладка General
   
   
   
   
   Заменим Collation Name: UKRAINIAN -> UKRUMIX
   Так как мы редактируем Monolingual Linguistic Sort, то возьмем ID из диапазона 1001
   (The valid range for Collation ID (sort ID) for a user-defined sort is 1000 to 2000 for monolingual collation and 10000 to 11000 for multilingual collation.)
   
   
   
   
   Переходим на закладку Major/Minor, сортируем по полю Major Sort,
   становимся на запись с Major Sort = 147, Unicode Value = 0x0403
   
   
   
   
   
   • Нажимаем New и указываем следующий значения:
     
     • Unicode Value = 0x0490
     • Major Sort = 147
     • Minor Sort = 3
       
       
       
     
     
   • Add
   • Нажимаем New и указываем следующий значения:
     
     • Unicode Value = 0x0491
     • Major Sort = 147
     • Minor Sort = 4
       
       
       
       
       
   • Add
   • Нажимаем New и указываем следующий значения:
     
     • Unicode Value = 0x042d
     • Major Sort = 227
     • Minor Sort = 1
       
       
   • Add
   • Нажимаем New и указываем следующий значения:
     
     • Unicode Value = 0x044d
     • Major Sort = 227
     • Minor Sort = 2
       
       
   • Add
   Сохраним NLT файл в D:\temp Сохранять под именем, предложенным самой утилитой(под другим именем не даст сохранить) File -> Save As...
   
   
3. Generating and Installing NLB Files
   
   1. As the user who owns the files (typically user oracle), back up the NLS installation boot file (lx0boot.nlb) and the NLS system boot file (lx1boot.nlb) in the ORA_NLS10 directory. On a UNIX platform, enter commands similar to the following example:
      

      % setenv ORA_NLS10 $ORACLE_HOME/nls/data
      % cd $ORA_NLS10
      % cp -p lx0boot.nlb lx0boot.nlb.orig
      % cp -p lx1boot.nlb lx1boot.nlb.orig
      
      

      Note that the -p option preserves the timestamp of the original file.
      
   2. In Oracle Locale Builder, choose Tools > Generate NLB or click the Generate NLB icon in the left side bar.
      
      
   3. Click Browse to find the directory where the NLT file is located
      
      
   4. Click OK to generate the NLB files.
      
      
   5. Copy the lx1boot.nlb file into the path:
      

      copy D:\temp\lx1boot.nlb $ORACLE_HOME/nls/data
      

   6. Copy the new NLB files into the $ORACLE_HOME/nls/data directory.
      

      copy D:\temp\lx303e9.nlb $ORACLE_HOME\nls\data
      

   7. Restart the database to use the newly created locale data.
      

      cmd> lsnrctl stop
      
      cmd> sqlplus / as sysdba
      sql> shutdown immediate
      sql> startup
      sql> exit
      
      
      

4. Тестируем:
   

   select * from (
     select 'Э'/*unistr('\042d')*/ name from dual
     union all
     select 'э'/*unistr('\044d')*/ name from dual
     union all
     select 'ґ'/*unistr('\0491')*/ name from dual
     union all
     select 'Ґ'/*unistr('\0490')*/ name from dual
     union all
     select 'Ю'/*unistr('\xxxx')*/ name from dual
     union all
     select 'Я'/*unistr('\xxxx')*/ name from dual
     union all
     select 'В'/*unistr('\xxxx')*/ name from dual
     union all
     select 'Д'/*unistr('\xxxx')*/ name from dual
   )
   order by NLSSORT(name, 'NLS_SORT = UKRUMIX');
   /
   
   NA
   -- 
   В
   Ґ
   ґ
   Д
   Э
   э
   Ю
   Я
   
   8 rows selected.