1. На промышленной и резервной базах данных создаем таблицу STATTAB
$export ORACLE_SID=prod
$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 11:27:19 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
begin
DBMS_STATS.create_stat_table(ownname => 'SYSTEM',stattab => 'STATTAB',tblspace => 'SYSAUX');
end;
/
$export ORACLE_SID=rez1
$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 11:27:19 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
begin
DBMS_STATS.create_stat_table(ownname => 'SYSTEM',stattab => 'STATTAB',tblspace => 'SYSAUX');
end;
/
2. Создаем резервную копию текущей статистики промышленной базы данных
На промышленном сервере запускаем процедуру экспорта текущей статистики
в таблицу STATTAB.
$export ORACLE_SID=prod
$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 11:27:19 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
begin
DBMS_STATS.export_schema_stats(ownname => 'OWS',stattab => 'STATTAB',statid => 'exp1' ,statown => 'SYSTEM');
end;
/
Смотрим содержимое таблицы STATTAB.
select * from system.stattab
Создаем резервную копию таблицы STATTAB
$export ORACLE_SID=prod
$exp \'/ as sysdba\' file=/u01/app/oracle/backups/dmp/stats_table.dmp.`date +%Y-%m-%d-%H:%M:%S` log=/u01/app/oracle/backups/dmp/stats_table.log.`date +%Y-%m-%d-%H:%M:%S` tables=SYSTEM.STATTAB
Export: Release 10.2.0.4.0 - Production on Wed Oct 6 16:30:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in CL8ISO8859P5 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SYSTEM
. . exporting table STATTAB 95211 rows exported
Export terminated successfully without warnings.
$
3. Сбор статистики на копии промышленной базы данных (SID=rez1)
(отдельная тема)
4. Переносим статистику собранную на базе rez1 в таблицу STATTAB
$export ORACLE_SID=rez1
Проверяем что таблица существует (мы ее создали в п.1)
select * from system.stattab
Переносим в нее собранную статистику
$export ORACLE_SID=prod
$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 11:27:19 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
begin
DBMS_STATS.export_schema_stats(ownname => 'OWS',stattab => 'STATTAB',statid => 'imp1' ,statown => 'SYSTEM');
end;
/
Смотрим содержимое таблицы:
select * from system.stattab
5. Выгружаем содержимое таблицы STATTAB в файл дампа stats_table.dmp
exp \'/ as sysdba\' file=/u01/app/oracle/backups/dmp/stats_table.dmp log=/u01/app/oracle/backups/dmp/stats_table.log tables=SYSTEM.STATTAB
Export: Release 10.2.0.4.0 - Production on Wed Oct 6 16:30:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in CL8ISO8859P5 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SYSTEM
. . exporting table STATTAB 95211 rows exported
Export terminated successfully without warnings.
$
6. Производим импорт содержимого файла stats_table.dmp в таблицу STATTAB на промышленной базе данных
(текущая статистика была сохранена в п.2)
$imp \'/ as sysdba\' file=/u01/app/oracle/backups/dmp/stats_table.dmp log=/u01/app/oracle/backups/dmp/stats_import.log fromuser=system touser=system ignore=y
Import: Release 10.2.0.4.0 - Production on Wed Oct 6 16:59:59 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in CL8ISO8859P5 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. . importing table "STATTAB" 100151 rows imported
Import terminated successfully without warnings.
$
7. Применяем перенесенную статистику на промышленной базе данных
$export ORACLE_SID=prod
$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 11:27:19 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
begin
DBMS_STATS.IMPORT_schema_stats(ownname => 'OWS',stattab => 'STATTAB',statid => 'imp1',statown => 'SYSTEM');
end;
/
commit;
8. Очистка содержимого таблиц STATTAB
После успешного переноса статистики можно очистить содержимое таблиц STATTAB на промышленной базе и базе rez1
truncate table system.stattab
Проверяем
select * from system.stattab
9. Типичные ошибки
Иногда, если в копии промышленной базы были произведены изменения в структуре таблиц (например, добавлены новые столбцы) при импорте статистики процедура DBMS_STATS.IMPORT_schema_stats завершается с ошибкой:
ORA-20000: Unable to set values for column STORE_COLUMN:
does not exist or insufficient privilegesORA-06512:
at "SYS.DBMS_STATS", line 6824ORA-06512: at "SYS.DBMS_STATS", line 6850ORA-06512:
at "SYS.DBMS_STATS", line 7382ORA-06512: at line 2
Это значит что, в какой то таблице в базе rez1 появилось новое поле STORE_COLUMN, которое отсутствует в соответствующей таблице в базе prod.
Статистику по этому полю следует удалить из таблицы STATTAB на промышленной базе данных и повторно запустить процедуру импорта:
select c1 from system.stattab where c4 = 'STORE_COLUMN'
возвратит имя таблицы:
ADDR_LEVEL
select * FROM system.stattab where c1 = 'ADDR_LEVEL' and c4='STORE_COLUMN';
delete FROM system.stattab where c1 = 'ADDR_LEVEL' and c4='STORE_COLUMN';
$export ORACLE_SID=prod
$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 11:27:19 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
begin
DBMS_STATS.IMPORT_schema_stats(ownname => 'OWS',stattab => 'STATTAB',statid => 'imp1',statown => 'SYSTEM');
end;
/
commit;
среда, 6 октября 2010 г.
вторник, 7 сентября 2010 г.
RAW устройства в RHEL
Список доступных дисков:
# ls /dev/sd*
/dev/sda /dev/sda2
/dev/sda1 /dev/sda3
Здесь sda это весь диск, а sda[1-3] это 3 партиции на sda.
# cat /proc/partitions
major minor #blocks name
8 0 26214400 sda
8 1 104391 sda1
8 2 10482412 sda2
8 3 3148740 sda3
Добавим еще несколько новых дисков:
# ls /dev/sd*
/dev/sda /dev/sda2 /dev/sdc /dev/sde
/dev/sda1 /dev/sda3 /dev/sdb /dev/sdd
# cat /proc/partitions
major minor #blocks name
8 0 26214400 sda
8 1 104391 sda1
8 2 10482412 sda2
8 3 3148740 sda3
8 16 4194304 sdb
8 32 4194304 sdc
8 48 4194304 sdd
8 64 4194304 sde
На каждом из 4 новых дисков, необходимо создать хотя бы по одному
первичному разделу:
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or
OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected
by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-522, default 522):
Using default value 522
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
Аналогично создаем разделы на оставшихся дисках (sdc, sdd, sde)
В итоге получаем следующее:
# cat /proc/partitions
major minor #blocks name
8 0 26214400 sda
8 1 104391 sda1
8 2 10482412 sda2
8 3 3148740 sda3
8 16 4194304 sdb
8 17 4192933 sdb1
8 32 4194304 sdc
8 33 4192933 sdc1
8 48 4194304 sdd
8 49 4192933 sdd1
8 64 4194304 sde
8 65 4192933 sde1
В RHEL существует утилита для работы с raw-устройствами, называемая raw.
Привязка raw-устройств в ручную осуществляется так:
# /bin/raw /dev/raw/raw1 /dev/sdb1
# /bin/raw /dev/raw/raw2 /dev/sdc1
# /bin/raw /dev/raw/raw3 /dev/sdd1
# /bin/raw /dev/raw/raw4 /dev/sde1
Просмотр файлов устройств:
# ls -lR /dev/raw*
Для того чтобы автоматически привязка осуществлялась после перезагрузки системы
в RHEL необходимо выполнить некоторые настройки:
В RHEL до 5 версии, сырые устройства настраивались примерно так:
# vi /etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdc1
/dev/raw/raw3 /dev/sdd1
/dev/raw/raw4 /dev/sde1
Restart the rawdevices service:
# service rawdevices restart
И чтобы после перезагрузки все сохранилось :
# vi /etc/rc.local
chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chown oracle:oinstall /dev/raw/raw4
chmod 600 /dev/raw/raw1
chmod 600 /dev/raw/raw2
chmod 600 /dev/raw/raw3
chmod 600 /dev/raw/raw4
В RHEL 5 сопосталение устройств raw выполняется согласно правилам udev.
Для этого в файл /etc/udev/rules.d/60-raw.rules нужно добавить строку:
ACTION=="add", KERNEL=="device_name", RUN+="raw /dev/raw/rawX %N"
Здесь вместо device_name необходимо указать имя исходного блочного устройства,
например /dev/sda1, а вместо X номер (например raw1)
Если известно не имя устройства, а его мажорный (A) и минорный (B) номера, строка будет выглядеть так:
ACTION=="add", ENV{MAJOR}="A", ENV{MINOR}="B", RUN+="raw /dev/raw/rawX %M %m"
Также, для установки разрешений в конце файла добавим правило:
ACTION=="add", KERNEL=="raw*", OWNER=="root", GROUP=="dba", MODE=="0660"
Содержимое файла /etc/udev/rules.d/60-raw.rules должно выглядеть примерно так:
ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", KERNEL=="sdd1", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sde1", RUN+="/bin/raw /dev/raw/raw4 %N"
# set permissions on these disks for the Oracle Owner user.
ACTION=="add", KERNEL=="raw*", OWNER=="oracle", GROUP=="oinstall", MODE=="0660"
Если от старой системы в файле /etc/sysconfig/rawdevices (в RHEL) накопилось
большое количество raw-устройств, сконвертировать их можно с помощью скрипта:
#!/bin/sh
grep -v "^ *#" /etc/sysconfig/rawdevices | grep -v "^$" | while read dev major minor ; do
if [ -z "$minor" ]; then
echo "ACTION==\"add\", KERNEL==\"${major##/dev/}\", RUN+=\"/usr/bin/raw $dev %N\""
else
echo "ACTION==\"add\", ENV{MAJOR}==\"$major\", ENV{MINOR}==\"$minor\", RUN+=\"/usr/bin/raw $dev %M %m\""
fi
done
Проверка правил udev:
# udevtest /block/sdb/sdb1 | grep raw
main: run: '/bin/raw /dev/raw/raw1 /dev/.tmp-8-17'
# udevtest /class/raw/raw1 | grep mode
udev_node_add: creating device node '/dev/raw/raw1', major = '162',
minor = '1', mode = '0660', uid = '500', gid = '500'
Чтобы без перезагрузки применить эти правила, выполните команду:
# udevtrigger
После перезапуска udev убедиться, что устройства создались, можно следующим образом:
# raw -qa
/dev/raw/raw1: bound to major 8, minor 17
/dev/raw/raw2: bound to major 8, minor 33
/dev/raw/raw3: bound to major 8, minor 49
/dev/raw/raw4: bound to major 8, minor 65
Сервис udev должен быть запущен
# start_udev
Starting udev: [ OK ]
Проверим, можем ли мы из под oracle что-то писать на сырое устройство можно так:
$ dd if=/dev/zero of=/dev/raw/raw1 bs=1М count=100
Как альтернативный вариант, для установки разрешений можно попробовать создать отдельный файл:
# vi /etc/udev/rules.d/99-raw-perms.rules
KERNEL=="raw[1-4]", MODE="0640", GROUP="oinstall", OWNER="oracle"
Лучше использовать UUID
$ more /etc/udev/rules.d/60-raw.rules
# Enter raw device bindings here.
#
# An example would be:
# ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda, or
# ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8, minor 1.
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263e8c000014a44c9bc710", RUN+="/bin/raw /dev/raw/raw1 %N" SYMLINK+="redoa"
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263ce2000015584c9bd23f", RUN+="/bin/raw /dev/raw/raw2 %N" SYMLINK+="redob"
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263ce2000015594c9bd3cd", RUN+="/bin/raw /dev/raw/raw3 %N" SYMLINK+="data"
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263e8c000014a64c9bc942", RUN+="/bin/raw /dev/raw/raw4 %N" SYMLINK+="fra"
# set permissions on these disks for the Oracle Owner user.
ACTION=="add", KERNEL=="raw[1-4]", OWNER=="oragrid", GROUP=="asmdba", MODE=="0660"
Команда devlabel
# devlabel add -s /dev/raw/raw1 -d /dev/sdb1 --uid `id -u oracle` --gid `id -g oracle`
RAW: /dev/raw/raw1 -> /dev/sdb1
Added /dev/raw/raw1 to /etc/sysconfig/devlabel
# raw -qa
/dev/raw/raw1: bound to major 8, minor 17.
Настройка сырых устройств в RHEL 5 и RHEL 6:
# ls -la /dev/sd*
brw-rw---- 1 root disk 8, 0 Feb 26 10:01 /dev/sda
brw-rw---- 1 root disk 8, 1 Feb 26 10:01 /dev/sda1
brw-rw---- 1 root disk 8, 2 Feb 26 10:01 /dev/sda2
brw-rw---- 1 root disk 8, 16 Feb 26 12:45 /dev/sdb
brw-rw---- 1 root disk 8, 17 Feb 26 12:46 /dev/sdb1
brw-rw---- 1 root disk 8, 32 Feb 26 10:01 /dev/sdc
brw-rw---- 1 root disk 8, 48 Feb 26 12:46 /dev/sdd
brw-rw---- 1 root disk 8, 49 Feb 26 12:47 /dev/sdd1
# cat /proc/partitions
major minor #blocks name
8 16 143247360 sdb
8 17 143243541 sdb1
8 0 143247360 sda
8 1 512000 sda1
8 2 142734336 sda2
8 48 143247360 sdd
8 49 143243541 sdd1
8 32 143247360 sdc
252 0 52428800 dm-0
252 1 12369920 dm-1
11 0 1048575 sr0
252 2 77934592 dm-2
8 32 143247360 sdc
fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or
OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected
by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-522, default 522):
Using default value 522
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
# cat /proc/partitions
major minor #blocks name
8 16 143247360 sdb
8 17 143243541 sdb1
8 0 143247360 sda
8 1 512000 sda1
8 2 142734336 sda2
8 48 143247360 sdd
8 49 143243541 sdd1
8 32 143247360 sdc
8 33 143243541 sdc1
252 0 52428800 dm-0
252 1 12369920 dm-1
11 0 1048575 sr0
252 2 77934592 dm-2
Для RHEL 6:
#/sbin/scsi_id -g -u -d /dev/sdc
SSun_Data_D0A07736
Create file /etc/udev/rules.d/99-oracle-asmdevices.rules with this 2 lines
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent",
RESULT=="SSun_Data_D0A07736", NAME="asm-disk1", OWNER="oragrid", GROUP="asmadmin", MODE="0660"
#udevadm test /block/sdc/sdc1
#udevadm control --reload-rules
#start_udev
Для RHEL 5
#ls -la /dev/asm-*
brw-rw---- 1 oragrid asmadmin 8, 33 Feb 26 15:15 /dev/asm-disk1
#/sbin/scsi_id -g -u -s /block/sdc
SSun_Data_D0A07736
# vi /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent",
RESULT=="SSun_Data_D0A07736", NAME="asm-disk1", OWNER="oragrid", GROUP="asmadmin", MODE="0660"
#udevtest /block/sdc/sdc1
#udevtrigger
# start_udev
Starting udev: [ OK ]
#
# ls -l /dev/asm-*
brw-rw---- 1 oragrid asmadmin 8, 33 Mar 6 07:56 /dev/asm-disk1
#
# ls /dev/sd*
/dev/sda /dev/sda2
/dev/sda1 /dev/sda3
Здесь sda это весь диск, а sda[1-3] это 3 партиции на sda.
# cat /proc/partitions
major minor #blocks name
8 0 26214400 sda
8 1 104391 sda1
8 2 10482412 sda2
8 3 3148740 sda3
Добавим еще несколько новых дисков:
# ls /dev/sd*
/dev/sda /dev/sda2 /dev/sdc /dev/sde
/dev/sda1 /dev/sda3 /dev/sdb /dev/sdd
# cat /proc/partitions
major minor #blocks name
8 0 26214400 sda
8 1 104391 sda1
8 2 10482412 sda2
8 3 3148740 sda3
8 16 4194304 sdb
8 32 4194304 sdc
8 48 4194304 sdd
8 64 4194304 sde
На каждом из 4 новых дисков, необходимо создать хотя бы по одному
первичному разделу:
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or
OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected
by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-522, default 522):
Using default value 522
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
Аналогично создаем разделы на оставшихся дисках (sdc, sdd, sde)
В итоге получаем следующее:
# cat /proc/partitions
major minor #blocks name
8 0 26214400 sda
8 1 104391 sda1
8 2 10482412 sda2
8 3 3148740 sda3
8 16 4194304 sdb
8 17 4192933 sdb1
8 32 4194304 sdc
8 33 4192933 sdc1
8 48 4194304 sdd
8 49 4192933 sdd1
8 64 4194304 sde
8 65 4192933 sde1
В RHEL существует утилита для работы с raw-устройствами, называемая raw.
Привязка raw-устройств в ручную осуществляется так:
# /bin/raw /dev/raw/raw1 /dev/sdb1
# /bin/raw /dev/raw/raw2 /dev/sdc1
# /bin/raw /dev/raw/raw3 /dev/sdd1
# /bin/raw /dev/raw/raw4 /dev/sde1
Просмотр файлов устройств:
# ls -lR /dev/raw*
Для того чтобы автоматически привязка осуществлялась после перезагрузки системы
в RHEL необходимо выполнить некоторые настройки:
В RHEL до 5 версии, сырые устройства настраивались примерно так:
# vi /etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdc1
/dev/raw/raw3 /dev/sdd1
/dev/raw/raw4 /dev/sde1
Restart the rawdevices service:
# service rawdevices restart
И чтобы после перезагрузки все сохранилось :
# vi /etc/rc.local
chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chown oracle:oinstall /dev/raw/raw4
chmod 600 /dev/raw/raw1
chmod 600 /dev/raw/raw2
chmod 600 /dev/raw/raw3
chmod 600 /dev/raw/raw4
В RHEL 5 сопосталение устройств raw выполняется согласно правилам udev.
Для этого в файл /etc/udev/rules.d/60-raw.rules нужно добавить строку:
ACTION=="add", KERNEL=="device_name", RUN+="raw /dev/raw/rawX %N"
Здесь вместо device_name необходимо указать имя исходного блочного устройства,
например /dev/sda1, а вместо X номер (например raw1)
Если известно не имя устройства, а его мажорный (A) и минорный (B) номера, строка будет выглядеть так:
ACTION=="add", ENV{MAJOR}="A", ENV{MINOR}="B", RUN+="raw /dev/raw/rawX %M %m"
Также, для установки разрешений в конце файла добавим правило:
ACTION=="add", KERNEL=="raw*", OWNER=="root", GROUP=="dba", MODE=="0660"
Содержимое файла /etc/udev/rules.d/60-raw.rules должно выглядеть примерно так:
ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", KERNEL=="sdd1", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sde1", RUN+="/bin/raw /dev/raw/raw4 %N"
# set permissions on these disks for the Oracle Owner user.
ACTION=="add", KERNEL=="raw*", OWNER=="oracle", GROUP=="oinstall", MODE=="0660"
Если от старой системы в файле /etc/sysconfig/rawdevices (в RHEL) накопилось
большое количество raw-устройств, сконвертировать их можно с помощью скрипта:
#!/bin/sh
grep -v "^ *#" /etc/sysconfig/rawdevices | grep -v "^$" | while read dev major minor ; do
if [ -z "$minor" ]; then
echo "ACTION==\"add\", KERNEL==\"${major##/dev/}\", RUN+=\"/usr/bin/raw $dev %N\""
else
echo "ACTION==\"add\", ENV{MAJOR}==\"$major\", ENV{MINOR}==\"$minor\", RUN+=\"/usr/bin/raw $dev %M %m\""
fi
done
Проверка правил udev:
# udevtest /block/sdb/sdb1 | grep raw
main: run: '/bin/raw /dev/raw/raw1 /dev/.tmp-8-17'
# udevtest /class/raw/raw1 | grep mode
udev_node_add: creating device node '/dev/raw/raw1', major = '162',
minor = '1', mode = '0660', uid = '500', gid = '500'
Чтобы без перезагрузки применить эти правила, выполните команду:
# udevtrigger
После перезапуска udev убедиться, что устройства создались, можно следующим образом:
# raw -qa
/dev/raw/raw1: bound to major 8, minor 17
/dev/raw/raw2: bound to major 8, minor 33
/dev/raw/raw3: bound to major 8, minor 49
/dev/raw/raw4: bound to major 8, minor 65
Сервис udev должен быть запущен
# start_udev
Starting udev: [ OK ]
Проверим, можем ли мы из под oracle что-то писать на сырое устройство можно так:
$ dd if=/dev/zero of=/dev/raw/raw1 bs=1М count=100
Как альтернативный вариант, для установки разрешений можно попробовать создать отдельный файл:
# vi /etc/udev/rules.d/99-raw-perms.rules
KERNEL=="raw[1-4]", MODE="0640", GROUP="oinstall", OWNER="oracle"
Лучше использовать UUID
$ more /etc/udev/rules.d/60-raw.rules
# Enter raw device bindings here.
#
# An example would be:
# ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda, or
# ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8, minor 1.
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263e8c000014a44c9bc710", RUN+="/bin/raw /dev/raw/raw1 %N" SYMLINK+="redoa"
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263ce2000015584c9bd23f", RUN+="/bin/raw /dev/raw/raw2 %N" SYMLINK+="redob"
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263ce2000015594c9bd3cd", RUN+="/bin/raw /dev/raw/raw3 %N" SYMLINK+="data"
ACTION=="add", KERNEL=="sd*[1]", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="3600a0b8000263e8c000014a64c9bc942", RUN+="/bin/raw /dev/raw/raw4 %N" SYMLINK+="fra"
# set permissions on these disks for the Oracle Owner user.
ACTION=="add", KERNEL=="raw[1-4]", OWNER=="oragrid", GROUP=="asmdba", MODE=="0660"
Команда devlabel
# devlabel add -s /dev/raw/raw1 -d /dev/sdb1 --uid `id -u oracle` --gid `id -g oracle`
RAW: /dev/raw/raw1 -> /dev/sdb1
Added /dev/raw/raw1 to /etc/sysconfig/devlabel
# raw -qa
/dev/raw/raw1: bound to major 8, minor 17.
Настройка сырых устройств в RHEL 5 и RHEL 6:
# ls -la /dev/sd*
brw-rw---- 1 root disk 8, 0 Feb 26 10:01 /dev/sda
brw-rw---- 1 root disk 8, 1 Feb 26 10:01 /dev/sda1
brw-rw---- 1 root disk 8, 2 Feb 26 10:01 /dev/sda2
brw-rw---- 1 root disk 8, 16 Feb 26 12:45 /dev/sdb
brw-rw---- 1 root disk 8, 17 Feb 26 12:46 /dev/sdb1
brw-rw---- 1 root disk 8, 32 Feb 26 10:01 /dev/sdc
brw-rw---- 1 root disk 8, 48 Feb 26 12:46 /dev/sdd
brw-rw---- 1 root disk 8, 49 Feb 26 12:47 /dev/sdd1
# cat /proc/partitions
major minor #blocks name
8 16 143247360 sdb
8 17 143243541 sdb1
8 0 143247360 sda
8 1 512000 sda1
8 2 142734336 sda2
8 48 143247360 sdd
8 49 143243541 sdd1
8 32 143247360 sdc
252 0 52428800 dm-0
252 1 12369920 dm-1
11 0 1048575 sr0
252 2 77934592 dm-2
8 32 143247360 sdc
fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or
OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected
by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-522, default 522):
Using default value 522
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
# cat /proc/partitions
major minor #blocks name
8 16 143247360 sdb
8 17 143243541 sdb1
8 0 143247360 sda
8 1 512000 sda1
8 2 142734336 sda2
8 48 143247360 sdd
8 49 143243541 sdd1
8 32 143247360 sdc
8 33 143243541 sdc1
252 0 52428800 dm-0
252 1 12369920 dm-1
11 0 1048575 sr0
252 2 77934592 dm-2
Для RHEL 6:
#/sbin/scsi_id -g -u -d /dev/sdc
SSun_Data_D0A07736
Create file /etc/udev/rules.d/99-oracle-asmdevices.rules with this 2 lines
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent",
RESULT=="SSun_Data_D0A07736", NAME="asm-disk1", OWNER="oragrid", GROUP="asmadmin", MODE="0660"
#udevadm test /block/sdc/sdc1
#udevadm control --reload-rules
#start_udev
Для RHEL 5
#ls -la /dev/asm-*
brw-rw---- 1 oragrid asmadmin 8, 33 Feb 26 15:15 /dev/asm-disk1
#/sbin/scsi_id -g -u -s /block/sdc
SSun_Data_D0A07736
# vi /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent",
RESULT=="SSun_Data_D0A07736", NAME="asm-disk1", OWNER="oragrid", GROUP="asmadmin", MODE="0660"
#udevtest /block/sdc/sdc1
#udevtrigger
# start_udev
Starting udev: [ OK ]
#
# ls -l /dev/asm-*
brw-rw---- 1 oragrid asmadmin 8, 33 Mar 6 07:56 /dev/asm-disk1
#
понедельник, 16 августа 2010 г.
Datapump
exp.par
userid=sys/
file=/../file.dmp
log=/../file_exp.log
owner=krg
direct=y
imp.par
userid=sys/
file=/../file.dmp
log=/../file_imp.log
fromuser=krg
touser=krg_test
buffer=10485760
commit=y
nohup exp parfile=exp.par &
nohup imp parfile=imp.par &
tail -f nohup.out
select object_type, count(*) from user_objects;
DataPump
create directory dp_stat_dir as '/u09/backups/oradb/datapump/test';
Using DataPump Export/Import examples
I will present some expamples using DataPump.
1) Full export
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y
- Excluding a schema or a list of schemas
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA')"
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA','MYOTHERSCHEMA')"
expdp system include=SCHEMA:\"like 'TST%'\" directory=tmp dumpfile=tstexpuser.dmp full=y content=metadata_only
2) Exporting Schema to a dump file
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics SCHEMAS=MYSCHEMA
3) Exporting Metadata Only
- Full Database
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics content=metadata_only full=y
-- Schema's
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics content=metadata_only schemas=myschema
4) Importing and remaping schema using dump file
impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA REMAP_SCHEMA=MYSCHEMA:OTHERSCHEMA DUMPFILE=mydump.dmp
5) Importing schemas directly from an other database using dblink
a) impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA,MYSCHEMA1 NETWORK_LINK=MYDBLINK
b) impdp system DIRECTORY=MYDIR SCHEMAS=MYSCHEMA NETWORK_LINK=MYLINK REMAP_SCHEMA=MYSCHEMA:MYSCHEMA_NEW CONTENT=METADATA_ONLY remap_tablespace=MYTBS1:MYNEWTBS,MYTBS2:MYNEWTBS
6) Export Schemas and import with remaping tablespace (Some more parameters are used)
a) Take the export
expdp system directory=EXPORT_DIR dumpfile=mydump.dmp logfile=mydump.log estimate=statistics parallel=2 job_name=mydump_exp schemas=MYSCHEMA1,MYSCHEMA2
b) Import and remaping default tablespace and index tablespaces
impdp system directory=export_dir schemas=MYSCHEMA1,MYSCHEMA2 dumpfile=mydump.dmp logfile=mydump.log job_name=mydump_imp parallel=2 remap_tablespace=myschema1_data:users,myschema1_index:users,myschema2_data:users,myschema2_index:users
7) Export table from a schema and import to an other schema
expdp system directory=export_dir dumpfile=mytable.dmp logfile=mytable.log ESTIMATE=statistics tables=myschema.mytable
impdp system directory=export_dir tables=myschema.mytable remap_schema=myschema:mynewschema dumpfile=mytable.dmp
8) Import Transportable Tablespace with Remapping Schema also objects stats are excluded during the import
impdp system dumpfile=mydump.dmp DIRECTORY=my_dir remap_schema=ORIG_SCHEMA:NEW_SCHEMA EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS TRANSPORT_DATAFILES='/u02/oradata/myfile.dbf'
Goal
This document demonstrates how to load and unload certain objects with the Oracle10g Export DataPump and Import DataPump utilities. This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.
Incorrect usage of metadata filters, can result in errors such as:
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: "DEPT": invalid identifier
ORA-39001: invalid argument value
ORA-39041: Filter "INCLUDE" either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39041: Filter "EXCLUDE" either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39038: Object path "USER" is not supported for TABLE jobs.
UDE-00011: parameter include is incompatible with parameter exclude
ksh: syntax error: '(' unexpected
ORA-31655: no data or metadata objects selected for job
Fix
The examples below are based on:
- the demo schema SCOTT that is created with script: $ORACLE_HOME/rdbms/admin/scott.sql
- the directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:
-- Windows:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;
-- Unix:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;
Note that when creating an export DataPump dumpfile, you have to ensure that the filename does not already exist in that directory.
The following examples show how metadata can be filtered with the EXCLUDE and INCLUDE parameters.
1. Syntax of the INCLUDE and EXCLUDE Data Pump parameters.
With Metadata filters you can specify a set of objects to be included or excluded from an Export or Import operation, such as tables, indexes, grants, procedures.
EXCLUDE = object_type[:name_clause] [, ...]
INCLUDE = object_type[:name_clause] [, ...]
Examples:
D:\> expdp ... SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:\"IN ('EMP', 'DEPT')\"
D:\> impdp ... SCHEMAS=scott INCLUDE=FUNCTION, PACKAGE, TABLE:\"= 'EMP'\"
The name_clause (specified after the colon separator) is optional. It allows a selection of specific objects within an object type. The EXCLUDE example above will export the complete SCOTT schema, except (1) the sequences owned by SCOTT and (2) the tables EMP and DEPT (with their dependent objects).
The INCLUDE example above will only import the functions, and packages (no procedures!) that are owned by SCOTT, and will also import the table EMP (with its dependent objects).
Note that if you forget to place the escape characters on the command line, and have spaces in the EXCLUDE and/or INCLUDE parameters, that the job may fail with an error such as: ORA-39071 (Value for EXCLUDE is badly formed).
A different kind of filtering is Data filtering. Data filtering is implemented through the QUERY and SAMPLE parameters, which specify restrictions on the table rows that are to be exported. For details, see also:
Note:277010.1 "Export/Import DataPump Parameter QUERY - How to Specify a Query"
2. SQL Operator usage.
The name_clause is a SQL expression that is used as a filter on the object names of the object. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. If no name_clause is provided, all objects of the specified type are excluded/included. The name clause must be separated from the object type with a colon. Examples of operator-usage (when used in a parameter file):
EXCLUDE=SEQUENCE
or:
EXCLUDE=TABLE:"IN ('EMP', 'DEPT')"
or:
EXCLUDE=INDEX:"= 'MY_INDX'"
or:
INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"
or:
INCLUDE=TABLE:"> 'E'"
3. Double quotes and single quotes usage.
The name clause is separated from the object type with a colon. The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings. Using the INCLUDE or EXCLUDE parameter in a parameter file is the preferred method.
Parameter file: exp.par
-------------------------
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:"IN ('EMP', 'DEPT')"
> expdp system/manager parfile=exp.par
To run this job without a parameter file, you need to escape the special characters. Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.
Command line examples (for Windows: type parameters on one single line) :
Windows (place all expdp parameters on a single line:):
D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp
LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEP')\"
Unix:
% expdp system/manager DIRECTORY=my_dir \
DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"
4. Using the same filter name for an object type more than once.
If multiple filters are specified for an object type, an implicit AND operation is applied to them. That is, the objects that are exported or imported during the job have passed all of the filters applied to their object types.
Parameter file - incorrect syntax (no tables are exported;
ORA-31655: no data or metadata objects selected for job):
INCLUDE=TABLE:"='EMP'"
INCLUDE=TABLE:"='DEPT'"
Parameter file - correct syntax:
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
or (all tables that have an 'E' and a 'P' in their name):
INCLUDE=TABLE:"LIKE '%E%'"
INCLUDE=TABLE:"LIKE '%P%'"
5. The EXCLUDE and INCLUDE parameters are mutually exclusive.
It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.
Parameter file - incorrect syntax (error: UDE-00011):
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'PK_EMP'"
Parameter file - correct syntax:
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
6. Specified object types depend on the export/import Data Pump mode.
During a TABLE level export/import, certain object types that are directly related to SCHEMA or DATABASE level jobs, cannot be specified. The same applies to a SCHEMA level export/import where no DATABASE level object types can be specified.
Example (incorrect spelling of object type USERS (should be: USER);
error: ORA-39041):
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
TABLES = scott.emp
INCLUDE = USERS:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
Example (incorrect usage of object types in INCLUDE parameter for
a TABLE level export; error: ORA-39038):
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
TABLES = scott.emp
INCLUDE = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
Corrected parameters (run job in schema mode):
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
INCLUDE = TABLE:"= 'EMP'"
To determine the name of the object types can be specified with EXCLUDE and INCLUDE, you can run the following query:
SET lines 200 pages 20000
COL object_path FOR a60
COL comments FOR a110
-- for database level export/import:
SELECT named, object_path, comments
FROM database_export_objects
WHERE object_path NOT LIKE '%/%';
-- for table schema export/import:
SELECT named, object_path, comments
FROM schema_export_objects
WHERE object_path NOT LIKE '%/%';
-- for table level export/import:
SELECT named, object_path, comments
FROM table_export_objects
WHERE object_path NOT LIKE '%/%';
7. Only specific object types can be named with a Name clause.
The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT).
To determine which object types can be named, you can run the following query:
SET lines 150 pages 20000
COL object_path FOR a30
COL comments FOR a110
-- for database level export/import:
SELECT named, object_path, comments
FROM database_export_objects
WHERE named='Y';
-- for table schema export/import:
SELECT named, object_path, comments
FROM schema_export_objects
WHERE named='Y';
-- for table level export/import:
SELECT named, object_path, comments
FROM table_export_objects
WHERE named='Y';
N OBJECT_PATH COMMENTS
- ---------------------------- -----------------------------------------------
Y CONSTRAINT Constraints (including referential constraints)
Y INDEX Indexes
Y PROCDEPOBJ Instance procedural objects
Y REF_CONSTRAINT Referential constraints
Y TRIGGER Triggers on the selected tables
Note that the object type TABLE is not listed here because this is the query output of the TABLE_EXPORT_OBJECTS view: the tables are already specified with the TABLES parameter in the DataPump job.
Import DataPump example:
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
TABLES = scott.emp
EXCLUDE = TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT
8. Excluding/Including an object, will also exclude/include it's dependent objects.
Dependent objects of an identified object are processed along with the identified object. For example, if a filter specifies that an index is to be included in an operation, then statistics from that index will also be included. Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter.
To determine which objects are dependent, e.g. for a TABLE, you can run the following query (in Oracle10g Release 2 and higher):
SET lines 200 pages 20000
COL object_path FOR a60
COL comments FOR a110
-- for TABLE dependent object types (10.2.0.x only):
SELECT named, object_path, comments
FROM database_export_objects
WHERE object_path LIKE 'TABLE/%';
N OBJECT_PATH COMMENTS
- ------------------------------------------- ------------------------------------------------
TABLE/AUDIT_OBJ Object audits on the selected tables
TABLE/COMMENT Table and column comments on the selected tables
TABLE/CONSTRAINT Constraints (including referential constraints)
TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
TABLE/FGA_POLICY Fine-grained auditing policies
TABLE/GRANT Object grants on the selected tables
TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
TABLE/INDEX Indexes
TABLE/INDEX/STATISTICS Precomputed statistics
TABLE/INSTANCE_CALLOUT Instance callouts
TABLE/MATERIALIZED_VIEW_LOG Materialized view logs
TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ Instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
TABLE/POST_TABLE_ACTION Post-table actions
TABLE/PRE_TABLE_ACTION Pre-table actions
TABLE/PROCACT_INSTANCE Instance procedural actions
TABLE/RLS_CONTEXT Fine-grained access control contexts
TABLE/RLS_GROUP Fine-grained access control policy groups
TABLE/RLS_POLICY Fine-grained access control policies
TABLE/TRIGGER Triggers
9. Exporting or Importing a large number of objects.
If a large number of objects needs to be exported (or imported), it is possible that an internal buffer limit is exceeded (e.g. for the parameters INCLUDE or TABLES). If that happens it will cause the Data Pump job to abort with an error such as: ORA-06502 (PL/SQL: numeric or value error: character string buffer too small). This happened especially in Oracle10g Release 1 where the value for the internal buffer was set to 3000 bytes. With the fix for Bug 4053129 "EXPDP fails with ORA-39125 ORA-6502 on large list of table names" (not a public bug; fixed in 10.1.0.5.0 and 10.2.0.x), this value was increased to 4000 bytes.
When exporting a large number of objects, we recommend to make use of a table inside the database that contains the names of the objects.
Example:
-- create a table that contains the names of the objects:
CONNECT scott/tiger
CREATE TABLE expdp_tab (owner VARCHAR2(30),
object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_tab VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;
-- run export DataPump job:
expdp system/manager DIRECTORY=my_dir \
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_tab WHERE \
owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"
Note that this workaround is not applicable for a long list of tablespace names. In Oracle10g Release 2 an exceed of the 4000 bytes for table names or tablespace names could result in UDE-00019 (xxx parameter list is too long). This has been resolved with the fix for Bug:5714205 "EXPDP / IMPDP do not support long lists for parameters" (fixed in 10.2.0.4.0 and 11.1.0.x) where the issue with the limitation has been removed completely.
10. Other issues when excluding objects at a Data Pump job.
When specifying the EXCLUDE parameter for en Export Data Pump or Import Data Pump job, all object types for the given mode of export/import (like schema mode) will be included, except those specified in an EXCLUDE statement. If an object is excluded, all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.
10.1. Excluding Constraints.
The following constraints cannot be excluded:
- NOT NULL constraints.
- Constraints needed for the table to be created and loaded successfully (for example, primary key constraints for index-organized tables or REF SCOPE and WITH ROWID constraints for tables with REF columns).
This means:
-- Exclude all nonreferential constraints, except for
-- NOT NULL constraints and any constraints needed for
-- successful table creation and loading:
EXCLUDE=CONSTRAINT
-- Exclude referential integrity (foreign key) constraints:
EXCLUDE=REF_CONSTRAINT
10.2. Excluding Grants.
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.
10.3. Excluding Users.
Specifying EXCLUDE=USER excludes only the definitions of users, not the objects contained within users' schemas. To exclude a specific user and all objects of that user, specify a filter such as the following (where SCOTT is the schema name of the user you want to exclude):
EXCLUDE=SCHEMA:"='SCOTT'"
If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'SCOTT'", only the CREATE USER scott DDL statement will be excluded, and you may not get the results you expect.
11. Other issues when including objects at a Data Pump job.
When specifying the INCLUDE parameter for en Export Data Pump or Import Data Pump job, only object types explicitly specified in INCLUDE statements (and their dependent objects) are exported/imported. No other object types, such as the schema definition information that is normally part of a schema-mode export when you have the EXP_FULL_DATABASE role, are exported/imported.
Additional Resources
Community: Database Utilities
Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.
References
@ BUG:4053129 - EXPDP FAILS WITH ERROR ORA-39125 ORA-6502 ON LARGE LIST OF TABLE NAMES
BUG:5527129 - UDE-00019 RETURNED WHEN ATTEMPTING TO EXPORT A LONG LIST OF TABLES VIA EXPDP
BUG:5714205 - DATAPUMP IS NOT UPWARD COMPATIBLE TO EXP
NOTE:277010.1 - Export/Import DataPump Parameter QUERY - How to Specify a Query
NOTE:277905.1 - Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas
NOTE:315529.1 - DataPump Export Failed With Error ORA-6502 PL/SQL Numeric Or Value Error: Character String Buffer Too Small
userid=sys/
file=/../file.dmp
log=/../file_exp.log
owner=krg
direct=y
imp.par
userid=sys/
file=/../file.dmp
log=/../file_imp.log
fromuser=krg
touser=krg_test
buffer=10485760
commit=y
nohup exp parfile=exp.par &
nohup imp parfile=imp.par &
tail -f nohup.out
select object_type, count(*) from user_objects;
DataPump
create directory dp_stat_dir as '/u09/backups/oradb/datapump/test';
grant read, write on directory dp_stat_dir to system;
SELECT * FROM dba_directories;
export ORACLE_SID=test
expdp \'/ as sysdba\' tables=SYSTEM.STATTAB directory=dp_stat_dir dumpfile=exp_stat_file%U.dmp logfile=exp_stat_file.log
export ORACLE_SID=test
impdp \'/ as sysdba\' tables=SYSTEM.STATTAB directory=dp_stat_dir dumpfile=exp_stat_file%U.dmp logfile=imp_stat_file.log
Using DataPump Export/Import examples
I will present some expamples using DataPump.
1) Full export
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y
- Excluding a schema or a list of schemas
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA')"
expdp system directory=export_dir dumpfile=mydump.dmp logfile=mydump.log ESTIMATE=statistics full=y EXCLUDE=SCHEMA:"in('MYSCHEMA','MYOTHERSCHEMA')"
expdp system include=SCHEMA:\"like 'TST%'\" directory=tmp dumpfile=tstexpuser.dmp full=y content=metadata_only
2) Exporting Schema to a dump file
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics SCHEMAS=MYSCHEMA
3) Exporting Metadata Only
- Full Database
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics content=metadata_only full=y
-- Schema's
expdp system DIRECTORY=EXPORT_DIR DUMPFILE=mydump.dmp ESTIMATE=statistics content=metadata_only schemas=myschema
4) Importing and remaping schema using dump file
impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA REMAP_SCHEMA=MYSCHEMA:OTHERSCHEMA DUMPFILE=mydump.dmp
5) Importing schemas directly from an other database using dblink
a) impdp system DIRECTORY=export_dir SCHEMAS=MYSCHEMA,MYSCHEMA1 NETWORK_LINK=MYDBLINK
b) impdp system DIRECTORY=MYDIR SCHEMAS=MYSCHEMA NETWORK_LINK=MYLINK REMAP_SCHEMA=MYSCHEMA:MYSCHEMA_NEW CONTENT=METADATA_ONLY remap_tablespace=MYTBS1:MYNEWTBS,MYTBS2:MYNEWTBS
6) Export Schemas and import with remaping tablespace (Some more parameters are used)
a) Take the export
expdp system directory=EXPORT_DIR dumpfile=mydump.dmp logfile=mydump.log estimate=statistics parallel=2 job_name=mydump_exp schemas=MYSCHEMA1,MYSCHEMA2
b) Import and remaping default tablespace and index tablespaces
impdp system directory=export_dir schemas=MYSCHEMA1,MYSCHEMA2 dumpfile=mydump.dmp logfile=mydump.log job_name=mydump_imp parallel=2 remap_tablespace=myschema1_data:users,myschema1_index:users,myschema2_data:users,myschema2_index:users
7) Export table from a schema and import to an other schema
expdp system directory=export_dir dumpfile=mytable.dmp logfile=mytable.log ESTIMATE=statistics tables=myschema.mytable
impdp system directory=export_dir tables=myschema.mytable remap_schema=myschema:mynewschema dumpfile=mytable.dmp
8) Import Transportable Tablespace with Remapping Schema also objects stats are excluded during the import
impdp system dumpfile=mydump.dmp DIRECTORY=my_dir remap_schema=ORIG_SCHEMA:NEW_SCHEMA EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS TRANSPORT_DATAFILES='/u02/oradata/myfile.dbf'
Goal
This document demonstrates how to load and unload certain objects with the Oracle10g Export DataPump and Import DataPump utilities. This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.
Incorrect usage of metadata filters, can result in errors such as:
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: "DEPT": invalid identifier
ORA-39001: invalid argument value
ORA-39041: Filter "INCLUDE" either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39041: Filter "EXCLUDE" either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39038: Object path "USER" is not supported for TABLE jobs.
UDE-00011: parameter include is incompatible with parameter exclude
ksh: syntax error: '(' unexpected
ORA-31655: no data or metadata objects selected for job
Fix
The examples below are based on:
- the demo schema SCOTT that is created with script: $ORACLE_HOME/rdbms/admin/scott.sql
- the directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:
-- Windows:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;
-- Unix:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;
Note that when creating an export DataPump dumpfile, you have to ensure that the filename does not already exist in that directory.
The following examples show how metadata can be filtered with the EXCLUDE and INCLUDE parameters.
1. Syntax of the INCLUDE and EXCLUDE Data Pump parameters.
With Metadata filters you can specify a set of objects to be included or excluded from an Export or Import operation, such as tables, indexes, grants, procedures.
EXCLUDE = object_type[:name_clause] [, ...]
INCLUDE = object_type[:name_clause] [, ...]
Examples:
D:\> expdp ... SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:\"IN ('EMP', 'DEPT')\"
D:\> impdp ... SCHEMAS=scott INCLUDE=FUNCTION, PACKAGE, TABLE:\"= 'EMP'\"
The name_clause (specified after the colon separator) is optional. It allows a selection of specific objects within an object type. The EXCLUDE example above will export the complete SCOTT schema, except (1) the sequences owned by SCOTT and (2) the tables EMP and DEPT (with their dependent objects).
The INCLUDE example above will only import the functions, and packages (no procedures!) that are owned by SCOTT, and will also import the table EMP (with its dependent objects).
Note that if you forget to place the escape characters on the command line, and have spaces in the EXCLUDE and/or INCLUDE parameters, that the job may fail with an error such as: ORA-39071 (Value for EXCLUDE is badly formed).
A different kind of filtering is Data filtering. Data filtering is implemented through the QUERY and SAMPLE parameters, which specify restrictions on the table rows that are to be exported. For details, see also:
Note:277010.1 "Export/Import DataPump Parameter QUERY - How to Specify a Query"
2. SQL Operator usage.
The name_clause is a SQL expression that is used as a filter on the object names of the object. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. If no name_clause is provided, all objects of the specified type are excluded/included. The name clause must be separated from the object type with a colon. Examples of operator-usage (when used in a parameter file):
EXCLUDE=SEQUENCE
or:
EXCLUDE=TABLE:"IN ('EMP', 'DEPT')"
or:
EXCLUDE=INDEX:"= 'MY_INDX'"
or:
INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"
or:
INCLUDE=TABLE:"> 'E'"
3. Double quotes and single quotes usage.
The name clause is separated from the object type with a colon. The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings. Using the INCLUDE or EXCLUDE parameter in a parameter file is the preferred method.
Parameter file: exp.par
-------------------------
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:"IN ('EMP', 'DEPT')"
> expdp system/manager parfile=exp.par
To run this job without a parameter file, you need to escape the special characters. Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.
Command line examples (for Windows: type parameters on one single line) :
Windows (place all expdp parameters on a single line:):
D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp
LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEP')\"
Unix:
% expdp system/manager DIRECTORY=my_dir \
DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"
4. Using the same filter name for an object type more than once.
If multiple filters are specified for an object type, an implicit AND operation is applied to them. That is, the objects that are exported or imported during the job have passed all of the filters applied to their object types.
Parameter file - incorrect syntax (no tables are exported;
ORA-31655: no data or metadata objects selected for job):
INCLUDE=TABLE:"='EMP'"
INCLUDE=TABLE:"='DEPT'"
Parameter file - correct syntax:
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
or (all tables that have an 'E' and a 'P' in their name):
INCLUDE=TABLE:"LIKE '%E%'"
INCLUDE=TABLE:"LIKE '%P%'"
5. The EXCLUDE and INCLUDE parameters are mutually exclusive.
It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.
Parameter file - incorrect syntax (error: UDE-00011):
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'PK_EMP'"
Parameter file - correct syntax:
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
6. Specified object types depend on the export/import Data Pump mode.
During a TABLE level export/import, certain object types that are directly related to SCHEMA or DATABASE level jobs, cannot be specified. The same applies to a SCHEMA level export/import where no DATABASE level object types can be specified.
Example (incorrect spelling of object type USERS (should be: USER);
error: ORA-39041):
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
TABLES = scott.emp
INCLUDE = USERS:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
Example (incorrect usage of object types in INCLUDE parameter for
a TABLE level export; error: ORA-39038):
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
TABLES = scott.emp
INCLUDE = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
Corrected parameters (run job in schema mode):
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = USER:"= 'SCOTT'", TABLESPACE_QUOTA, SYSTEM_GRANT, ROLE_GRANT
INCLUDE = TABLE:"= 'EMP'"
To determine the name of the object types can be specified with EXCLUDE and INCLUDE, you can run the following query:
SET lines 200 pages 20000
COL object_path FOR a60
COL comments FOR a110
-- for database level export/import:
SELECT named, object_path, comments
FROM database_export_objects
WHERE object_path NOT LIKE '%/%';
-- for table schema export/import:
SELECT named, object_path, comments
FROM schema_export_objects
WHERE object_path NOT LIKE '%/%';
-- for table level export/import:
SELECT named, object_path, comments
FROM table_export_objects
WHERE object_path NOT LIKE '%/%';
7. Only specific object types can be named with a Name clause.
The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT).
To determine which object types can be named, you can run the following query:
SET lines 150 pages 20000
COL object_path FOR a30
COL comments FOR a110
-- for database level export/import:
SELECT named, object_path, comments
FROM database_export_objects
WHERE named='Y';
-- for table schema export/import:
SELECT named, object_path, comments
FROM schema_export_objects
WHERE named='Y';
-- for table level export/import:
SELECT named, object_path, comments
FROM table_export_objects
WHERE named='Y';
N OBJECT_PATH COMMENTS
- ---------------------------- -----------------------------------------------
Y CONSTRAINT Constraints (including referential constraints)
Y INDEX Indexes
Y PROCDEPOBJ Instance procedural objects
Y REF_CONSTRAINT Referential constraints
Y TRIGGER Triggers on the selected tables
Note that the object type TABLE is not listed here because this is the query output of the TABLE_EXPORT_OBJECTS view: the tables are already specified with the TABLES parameter in the DataPump job.
Import DataPump example:
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
TABLES = scott.emp
EXCLUDE = TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT
8. Excluding/Including an object, will also exclude/include it's dependent objects.
Dependent objects of an identified object are processed along with the identified object. For example, if a filter specifies that an index is to be included in an operation, then statistics from that index will also be included. Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter.
To determine which objects are dependent, e.g. for a TABLE, you can run the following query (in Oracle10g Release 2 and higher):
SET lines 200 pages 20000
COL object_path FOR a60
COL comments FOR a110
-- for TABLE dependent object types (10.2.0.x only):
SELECT named, object_path, comments
FROM database_export_objects
WHERE object_path LIKE 'TABLE/%';
N OBJECT_PATH COMMENTS
- ------------------------------------------- ------------------------------------------------
TABLE/AUDIT_OBJ Object audits on the selected tables
TABLE/COMMENT Table and column comments on the selected tables
TABLE/CONSTRAINT Constraints (including referential constraints)
TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
TABLE/FGA_POLICY Fine-grained auditing policies
TABLE/GRANT Object grants on the selected tables
TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
TABLE/INDEX Indexes
TABLE/INDEX/STATISTICS Precomputed statistics
TABLE/INSTANCE_CALLOUT Instance callouts
TABLE/MATERIALIZED_VIEW_LOG Materialized view logs
TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ Instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
TABLE/POST_TABLE_ACTION Post-table actions
TABLE/PRE_TABLE_ACTION Pre-table actions
TABLE/PROCACT_INSTANCE Instance procedural actions
TABLE/RLS_CONTEXT Fine-grained access control contexts
TABLE/RLS_GROUP Fine-grained access control policy groups
TABLE/RLS_POLICY Fine-grained access control policies
TABLE/TRIGGER Triggers
9. Exporting or Importing a large number of objects.
If a large number of objects needs to be exported (or imported), it is possible that an internal buffer limit is exceeded (e.g. for the parameters INCLUDE or TABLES). If that happens it will cause the Data Pump job to abort with an error such as: ORA-06502 (PL/SQL: numeric or value error: character string buffer too small). This happened especially in Oracle10g Release 1 where the value for the internal buffer was set to 3000 bytes. With the fix for Bug 4053129 "EXPDP fails with ORA-39125 ORA-6502 on large list of table names" (not a public bug; fixed in 10.1.0.5.0 and 10.2.0.x), this value was increased to 4000 bytes.
When exporting a large number of objects, we recommend to make use of a table inside the database that contains the names of the objects.
Example:
-- create a table that contains the names of the objects:
CONNECT scott/tiger
CREATE TABLE expdp_tab (owner VARCHAR2(30),
object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_tab VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;
-- run export DataPump job:
expdp system/manager DIRECTORY=my_dir \
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_tab WHERE \
owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"
Note that this workaround is not applicable for a long list of tablespace names. In Oracle10g Release 2 an exceed of the 4000 bytes for table names or tablespace names could result in UDE-00019 (xxx parameter list is too long). This has been resolved with the fix for Bug:5714205 "EXPDP / IMPDP do not support long lists for parameters" (fixed in 10.2.0.4.0 and 11.1.0.x) where the issue with the limitation has been removed completely.
10. Other issues when excluding objects at a Data Pump job.
When specifying the EXCLUDE parameter for en Export Data Pump or Import Data Pump job, all object types for the given mode of export/import (like schema mode) will be included, except those specified in an EXCLUDE statement. If an object is excluded, all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.
10.1. Excluding Constraints.
The following constraints cannot be excluded:
- NOT NULL constraints.
- Constraints needed for the table to be created and loaded successfully (for example, primary key constraints for index-organized tables or REF SCOPE and WITH ROWID constraints for tables with REF columns).
This means:
-- Exclude all nonreferential constraints, except for
-- NOT NULL constraints and any constraints needed for
-- successful table creation and loading:
EXCLUDE=CONSTRAINT
-- Exclude referential integrity (foreign key) constraints:
EXCLUDE=REF_CONSTRAINT
10.2. Excluding Grants.
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.
10.3. Excluding Users.
Specifying EXCLUDE=USER excludes only the definitions of users, not the objects contained within users' schemas. To exclude a specific user and all objects of that user, specify a filter such as the following (where SCOTT is the schema name of the user you want to exclude):
EXCLUDE=SCHEMA:"='SCOTT'"
If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'SCOTT'", only the CREATE USER scott DDL statement will be excluded, and you may not get the results you expect.
11. Other issues when including objects at a Data Pump job.
When specifying the INCLUDE parameter for en Export Data Pump or Import Data Pump job, only object types explicitly specified in INCLUDE statements (and their dependent objects) are exported/imported. No other object types, such as the schema definition information that is normally part of a schema-mode export when you have the EXP_FULL_DATABASE role, are exported/imported.
Additional Resources
Community: Database Utilities
Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.
References
@ BUG:4053129 - EXPDP FAILS WITH ERROR ORA-39125 ORA-6502 ON LARGE LIST OF TABLE NAMES
BUG:5527129 - UDE-00019 RETURNED WHEN ATTEMPTING TO EXPORT A LONG LIST OF TABLES VIA EXPDP
BUG:5714205 - DATAPUMP IS NOT UPWARD COMPATIBLE TO EXP
NOTE:277010.1 - Export/Import DataPump Parameter QUERY - How to Specify a Query
NOTE:277905.1 - Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas
NOTE:315529.1 - DataPump Export Failed With Error ORA-6502 PL/SQL Numeric Or Value Error: Character String Buffer Too Small
вторник, 16 февраля 2010 г.
DBConsole recreate
Устанавливаем переменные окружения: $export ORACLE_HOSTNAME=dbsrv.oracle.com $export ORACLE_HOME=/app/oracle/product/10.2.0/db_1 $export ORACLE_SID=test1 $ Ручное удаление: Останавливаем dbconsole. $ORACLE_HOME/bin/emctl stop dbconsole $ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9 $ $ ps -ef|grep emagent $ ps -ef|grep dbsnmp $ $ ps -fu oracle SQL> alter user sys identified by XXX; SQL> alter user sysman identified by XXX; SQL> alter user dbsnmp identified by XXX; $$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager dbsrv.oracle.com 1521 test1 -action drop Enter SYS user's password : Enter repository user name : sysman Enter repository user password : Getting temporary tablespace from database... Found temporary tablespace: TEMP Checking SYS Credentials ... OK. Dropping the repository.. Quiescing DB ... Done. Checking for Repos User ... Exists. Repos User exists.. Clearing EM Contexts ... OK. Dropping EM users ... Done. Dropping Repos User ... Done. Dropping Roles/Synonymns/Tablespaces ... Done. Unquiescing DB ... Done. Dropped Repository Successfully. Автоматическое создание: Останавливаем dbconsole. $ORACLE_HOME/bin/emctl stop dbconsole $ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9 $ $ ps -ef|grep emagent $ ps -ef|grep dbsnmp $ $ ps -fu oracle $emca -config dbcontrol db -repos create STARTED EMCA at Feb 10, 2010 9:53:30 AM EM Configuration Assistant, Version 10.2.0.1.0 Production Copyright (c) 2003, 2005, Oracle. All rights reserved. Enter the following information: Database SID: test1 Listener port number: 1521 Password for SYS user: Password for DBSNMP user: Password for SYSMAN user: Email address for notifications (optional): Outgoing Mail (SMTP) server for notifications (optional): ----------------------------------------------------------------- You have specified the following settings Database ORACLE_HOME ................ /app/oracle/product/10.2.0/db_1 Database hostname ................ dbsrv.oracle.com Listener port number ................ 1521 Database SID ................ test1 Email address for notifications ............... Outgoing Mail (SMTP) server for notifications ............... ----------------------------------------------------------------- Do you wish to continue? [yes(Y)/no(N)]: Y Feb 10, 2010 9:54:20 AM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-53-30-AM.log. Feb 10, 2010 9:54:22 AM oracle.sysman.emcp.EMReposConfig createRepository INFO: Creating the EM repository (this may take a while) ... Feb 10, 2010 9:57:38 AM oracle.sysman.emcp.EMReposConfig invoke INFO: Repository successfully created Feb 10, 2010 9:57:43 AM oracle.sysman.emcp.util.DBControlUtil startOMS INFO: Starting Database Control (this may take a while) ... Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: Database Control started successfully Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: >>>>>>>>>>> The Database Control URL is http://dbsrv.oracle.com.lan:1158/em <<<<<<<<<<< Enterprise Manager configuration completed successfully FINISHED EMCA at Feb 10, 2010 9:59:25 AM $ (ОПЦИОНАЛЬНО) Можно попробовать еще раз пересоздать dbconsole, но теперь уже все автоматически. Автоматическое удаление: Останавливаем dbconsole. $ORACLE_HOME/bin/emctl stop dbconsole $ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9 $ $ ps -ef|grep emagent $ ps -ef|grep dbsnmp $ $ ps -fu oracle $emca -deconfig dbcontrol db -repos drop STARTED EMCA at Feb 10, 2010 9:50:18 AM EM Configuration Assistant, Version 10.2.0.1.0 Production Copyright (c) 2003, 2005, Oracle. All rights reserved. Enter the following information: Database SID: test1 Listener port number: 1521 Password for SYS user: Password for SYSMAN user: Do you wish to continue? [yes(Y)/no(N)]: Y Feb 10, 2010 9:50:52 AM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-50-18-AM.log. Feb 10, 2010 9:50:53 AM oracle.sysman.emcp.util.DBControlUtil stopOMS INFO: Stopping Database Control (this may take a while) ... Feb 10, 2010 9:51:01 AM oracle.sysman.emcp.EMReposConfig dropRepository INFO: Dropping the EM repository (this may take a while) ... Feb 10, 2010 9:53:15 AM oracle.sysman.emcp.EMReposConfig invoke INFO: Repository successfully dropped Enterprise Manager configuration completed successfully FINISHED EMCA at Feb 10, 2010 9:53:15 AM $ Автоматическое создание: Останавливаем dbconsole. $ORACLE_HOME/bin/emctl stop dbconsole $ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9 $ $ ps -ef|grep emagent $ ps -ef|grep dbsnmp $ $ ps -fu oracle test1:dbsrvt:~$emca -config dbcontrol db -repos create STARTED EMCA at Feb 10, 2010 9:53:30 AM EM Configuration Assistant, Version 10.2.0.1.0 Production Copyright (c) 2003, 2005, Oracle. All rights reserved. Enter the following information: Database SID: test1 Listener port number: 1521 Password for SYS user: Password for DBSNMP user: Password for SYSMAN user: Email address for notifications (optional): Outgoing Mail (SMTP) server for notifications (optional): ----------------------------------------------------------------- You have specified the following settings Database ORACLE_HOME ................ /app/oracle/product/10.2.0/db_1 Database hostname ................ dbsrv.oracle.com Listener port number ................ 1521 Database SID ................ test1 Email address for notifications ............... Outgoing Mail (SMTP) server for notifications ............... ----------------------------------------------------------------- Do you wish to continue? [yes(Y)/no(N)]: Y Feb 10, 2010 9:54:20 AM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test1/emca_2010-02-10_09-53-30-AM.log. Feb 10, 2010 9:54:22 AM oracle.sysman.emcp.EMReposConfig createRepository INFO: Creating the EM repository (this may take a while) ... Feb 10, 2010 9:57:38 AM oracle.sysman.emcp.EMReposConfig invoke INFO: Repository successfully created Feb 10, 2010 9:57:43 AM oracle.sysman.emcp.util.DBControlUtil startOMS INFO: Starting Database Control (this may take a while) ... Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: Database Control started successfully Feb 10, 2010 9:59:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: >>>>>>>>>>> The Database Control URL is http://dbsrv.oracle.com.lan:1158/em <<<<<<<<<<< Enterprise Manager configuration completed successfully FINISHED EMCA at Feb 10, 2010 9:59:25 AM $
Oracle XML Database Recreate
SQL> select comp_name, status, version from DBA_REGISTRY; COMP_NAME STATUS VERSION --------------------------------------- ----------- ------------------------------ Oracle Database Catalog Views VALID 10.2.0.3.0 Oracle Database Packages and Types VALID 10.2.0.3.0 Oracle Workspace Manager VALID 10.2.0.1.0 JServer JAVA Virtual Machine VALID 10.2.0.3.0 Oracle XDK VALID 10.2.0.3.0 Oracle Database Java Packages VALID 10.2.0.3.0 Oracle Expression Filter VALID 10.2.0.3.0 Oracle Enterprise Manager VALID 10.2.0.3.0 Oracle XML Database INVALID 10.2.0.3.0 9 rows selected. SQL> vi /app/oracle/product/10.2.0/db_1/dbs/inittest1.ora java_pool_size=150M или так: ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile; star REMOVAL STEPS 1. Shutdown and restart the database 2. Connect as sysdba and run the catnoqm.sql script SQL> set echo on SQL> spool /export/home/oracle/xdb_removal.log SQL> @?/rdbms/admin/catnoqm.sql SQL> spool off; 3.minumums shared_pool_size =150 MB java_pool_size =150 MB and XDB tablespace must have 150m 5. Shutdown the database immediate, and startup the database normal change_on_install INSTALL STEPS 1. Connect as sysdba and run the catqm.sql script SQL>set echo on SQL>spool /export/home/oracle/xdb_install.log SQL>@?/rdbms/admin/catqm.sql XDB SYSAUX TEMP SQL>spool off; SQL>@?/rdbms/admin/catxdbj.sql SQL>select comp_name, status, version from DBA_REGISTRY where comp_name= 'Oracle XML Database' SQL>select count(*) from dba_objects where owner='XDB' and status='INVALID'; 6. Restart database and listener to enable Oracle XML DB protocol access dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)" @/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql;
Oracle Upgrade 10.2.0.3 -> 10.2.0.4
Pre_upgrade export ORACLE_SID=test1 export ORACLE_HOME=/app/oracle/product/10.2.0/db_1 SQL>sqlplus "/ as sysdba" SQL>shutdown immediate SQL>STARTUP UPGRADE SQL>SPOOL /export/home/oracle/upgrade_info.log SQL>@/app/oracle/product/10.2.0/db_2/rdbms/admin/utlu102i.sql SQL>SPOOL OFF SQL>@/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql SQL>shutdown immediate; SQL>spool off SQL>exit Upgrade export PATH=/bin:/usr/bin:/usr/sbin:/etc:/opt/bin:/usr/ccs/bin:/usr/ucb/bi:/usr/local/bin:/app/oracle/product/10.2.0/db_2/bin export ORACLE_HOME=/app/oracle/product/10.2.0/db_2 $env Смотрим чтобы все переменные указывали на db_2 и в путях и в oracle_home sqlplus "/ as sysdba" STARTUP UPGRADE SPOOL /export/home/oracle/upgrade_db.log @/app/oracle/product/10.2.0/db_2/rdbms/admin/catupgrd.sql shutdown immediate; startup SPOOL OFF exit EOF sqlplus "/ as sysdba" SPOOL /tmp/invalid_test1.log @/app/oracle/product/10.2.0/db_2/rdbms/admin/utlrp.sql; spool off exit EOF $export ORACLE_HOSTNAME=dbsrv.oracle.com $export ORACLE_HOME=/app/oracle/product/10.2.0/db_2 $export ORACLE_SID=test1 $ emca -upgrade db Более подробная информация: Run the Pre-Upgrade Information Tool If you are upgrading database manually, then you should analyze it by running the Pre-Upgrade Information Tool. The Pre-Upgrade Information Tool is a SQL script that ships with the new Oracle Database 10g release, and must be copied to and run from the environment of the database being upgraded. In upgrading from 9.2.0.X to 10.2.0.4, run the Pre-Upgrade-Information Tool in the 9.2.0.X environment. In upgrading a database from 10.2.0.X to 10.2.0.4, run the 10.2.0.4 version of the utlu102i.sql script in the environment of the previous 10.2.0.X release, and not in the 10.2.0.4 database. Complete the following procedure to run the Pre-Upgrade Information Tool: 1. Start the database in the UPGRADE mode: 2. SQL> STARTUP UPGRADE 3. Set the system to spool results to a log file for later analysis: 4. SQL> SPOOL upgrade_info.log 5. Run the Pre-Upgrade Information Tool: 6. SQL> @?/rdbms/admin/utlu102i.sql 7. Turn off the spooling of script results to the log file: 8. SQL> SPOOL OFF Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file. The following is an example of the output generated by the Pre-Upgrade Information Tool: Oracle Database 10.2 Upgrade Information Utility 02-04-2008 11:48:11 . ********************************************************************** Database: ********************************************************************** --> name: X102040 --> version: 10.2.0.1.0 --> compatible: 10.2.0.1 --> blocksize: 8192 . ********************************************************************** Tablespaces: [make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size: 505 MB .... AUTOEXTEND additional space required: 15 MB --> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 401 MB .... AUTOEXTEND additional space required: 376 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size: 265 MB .... AUTOEXTEND additional space required: 15 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 58 MB .... AUTOEXTEND additional space required: 38 MB --> EXAMPLE tablespace is adequate for the upgrade. .... minimum required size: 69 MB . ********************************************************************** Update Parameters: [Update Oracle Database 10.2 init.ora or spfile] ********************************************************************** WARNING: --> "shared_pool_size" needs to be increased to at least 167772160 WARNING: --> "java_pool_size" needs to be increased to at least 67108864 . ********************************************************************** Components: [The following database components will be upgraded orinstalled] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> Oracle Data Mining [upgrade] VALID --> Messaging Gateway [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> OLAP Catalog [upgrade] VALID --> Oracle OLAP API [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID --> Oracle Ultra Search [upgrade] VALID --> Oracle Label Security [upgrade] VALID --> Expression Filter [upgrade] VALID --> EM Repository [upgrade] VALID --> Rule Manager [upgrade] VALID PL/SQL procedure successfully completed. Upgrading a Release 10.2 Database After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home: Note: If you do not run the catupgrd.sql script as described in this section and you start up a database for normal operation, then ORA-01092: ORACLE instance terminated. Disconnection forced errors will occur and the error ORA-39700: database must be opened with UPGRADE option will be in the alert log. 1.Log in as the Oracle software owner user. 2.For Oracle RAC installations, start listener on each node of the cluster as follows: $ srvctl start listener -n node 3.If you are using Automatic Storage Management, start the Automatic Storage Management instance. 4.For single-instance installations, start the listener as follows: $ lsnrctl start 5.For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges: $ sqlplus /nolog SQL> CONNECT SYS AS SYSDBA Enter password:SYS_password Users of single-instance installations now proceed to step 7. 6.For Oracle RAC installations: 1.Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges: $ sqlplus /nolog SQL> CONNECT SYS AS SYSDBA Enter password: SYS_password SQL> STARTUP NOMOUNT 2.Set the CLUSTER_DATABASE initialization parameter to FALSE: SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile; 3.Shut down the database: SQL> SHUTDOWN 7.Enter the following SQL*Plus commands: SQL> STARTUP UPGRADE SQL> SPOOL patch.log SQL> @?/rdbms/admin/catupgrd.sql SQL> SPOOL OFF 8.Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database. 9.If necessary, rerun the catupgrd.sql script after correcting any problems. 10. Restart the database: SQL> SHUTDOWN IMMEDIATE SQL> STARTUP 11.Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @?/rdbms/admin/utlrp.sql Note: When the 10.2.0.4 patch set is applied to an Oracle Database 10g Standard Edition database, there may be 54 invalid objects after the utlrp.sql script runs. These objects belong to the unsupported components and do not affect the database operation. Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following: BIN$4lzljWIt9gfgMFeM2hVSoA==$0 12.Run the following command to check the status of all the components after the upgrade: SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; In the output of the preceding command, the status of all the components should be VALID for a successful upgrade. 13.If you are using the Oracle Recovery Manager catalog, enter the following command: $ rman catalog username/password@alias RMAN> UPGRADE CATALOG; 14.For Oracle RAC installations: 1.Set the CLUSTER_DATABASE initialization parameter to TRUE: SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile; 2.Restart the database: SQL> SHUTDOWN IMMEDIATE SQL> STARTUP 3.Start any database services that you want to use: $ srvctl start service -d db_name -s service_name 15.To configure and secure Enterprise Manager follow these steps: Ensure the database and Listener are operational. In the case of a single instance, execute emca -upgrade db In the case of Oracle Real Application Clusters (RAC), execute emca -upgrade db -cluster
Solaris 10 Process Cheat Sheet
svcs - report service status
svcs -shows all enabled services
svcs -a - shows all services
svcs -l cron - shows detail information about the service
svcs -xv - shows explanations for services states that are
enabled but not running or preventing other services from running
svcadm - administration program for manipulating service instances.
svcadm enable cron - starts the cron daemon
svcadm enable -r cron - starts the cron daemon and any dependencies
svcadm disable cron - stops the cron daemon
svcadm restart cron - stops and then restarts the cron daemon
svcadm refresh cron - rereads the configuration files (like kill -HUP)
svcadm clear cron - clear the maintenance stat of the cron daemon
inetadm - administration program for manipulating inetd-controlled services
inetadm - lists inetd-controlled services
inetadm -l - lists the properties of the service
inetadm -e - enables the service
inetadm -d - disables the service
psrinfo - displays information on processes
psradm - change processor operational status
psradm -f - take a process offline
psradm -n - bring a process back online
mpstat - report per process statistics
prstat - report active process statistics
prstat -a - show processes and users totals
prstat-P cpu - show process statistics of a cpu
pmap - displays address space info on a process
pargs - prints process arguments
pargs -e - prints process environment variables
ptree - prints process tree
pfiles - prints open files and ports for a process
svcs -shows all enabled services
svcs -a - shows all services
svcs -l cron - shows detail information about the service
svcs -xv - shows explanations for services states that are
enabled but not running or preventing other services from running
svcadm - administration program for manipulating service instances.
svcadm enable cron - starts the cron daemon
svcadm enable -r cron - starts the cron daemon and any dependencies
svcadm disable cron - stops the cron daemon
svcadm restart cron - stops and then restarts the cron daemon
svcadm refresh cron - rereads the configuration files (like kill -HUP)
svcadm clear cron - clear the maintenance stat of the cron daemon
inetadm - administration program for manipulating inetd-controlled services
inetadm - lists inetd-controlled services
inetadm -l - lists the properties of the service
inetadm -e - enables the service
inetadm -d - disables the service
psrinfo - displays information on processes
psradm - change processor operational status
psradm -f - take a process offline
psradm -n - bring a process back online
mpstat - report per process statistics
prstat - report active process statistics
prstat -a - show processes and users totals
prstat-P cpu - show process statistics of a cpu
pmap - displays address space info on a process
pargs - prints process arguments
pargs -e - prints process environment variables
ptree - prints process tree
pfiles - prints open files and ports for a process
Подписаться на:
Сообщения (Atom)
