15 апр. 2018 г.8 мин. чтения

Oracle - команды, полезные запросы

Обновлено: 30 авг. 2021 г.

.bash_profile

--в домашней папке пользователя oracle # .bash_profile

# Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=<Inst name>; export ORACLE_SID #TNS_ADMIN=/u01/app/oracle/product/11.2.0/grid/network/admin; export TNS_ADMIN PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib: $ORACLE_HOME/rdbms/jlib; export CLASSPATH #LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL

if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi

AWR

sqlplus / as sysdba
  >@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Далее выполнять команды скрипта. Итоговый файл будет сформирован в домашней папке пользователя.

Archivelog

В SQLPlus: 
  shutdown immediate;
  startup mount;
  alter database archivelog;
  alter database open;

Configure server linux

uname -a
yum install oracle-validated
sysctl -w fs.suid_dumpable=1
sysctl -w fs.aio-max-nr=1048576
sysctl -w fs.file-max=6815744
sysctl -w kernel.shmall=2097152
sysctl -w kernel.shmmax=536870912
sysctl -w kernel.shmmni=4096
sysctl -w kernel.sem="250" "32000" "100" "128"
sysctl -w net.ipv4.ip_local_port_range="9000" "65500"
sysctl -w net.core.rmem_default=262144
sysctl -w net.core.rmem_max=4194304
sysctl -w net.core.wmem_default=262144
sysctl -w net.core.wmem_max=1048586
/sbin/sysctl -p
groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01
groupadd -g 54326 asmdba
groupadd -g 54327 asmoper
mkdir -p /u01/app/oraInventory
# chown -R oracle:oinstall /u01/app/oraInventory
# chmod -R 775 /u01/app/oraInventory
Grid Infrastructure Home
# mkdir -p  /u01/app/oracle/product/11.2.0/grid
# chown -R oracle:oinstall 
/u01/app/oracle/product/11.2.0/grid
# chmod -R 775 /u01/app/oracle/product/11.2.0/grid
Oracle Base Directory
# mkdir -p /u01/app/oracle
# mkdir /u01/app/oracle/cfgtoollogs
# chown -R oracle:oinstall /u01/app/oracle
# chmod -R 775 /u01/app/oracle
Oracle RDBMS Home
# mkdir -p /u01/app/oracle/product/11.2.0/db_1
# chown -R oracle:oinstall 
/u01/app/oracle/product/11.2.0/db_1
# chmod -R 775 /u01/app/oracle/product/11.2.0/db_1
chown oracle:oinstall /dev/sdb
chmod 600 /dev/sdb
chown oracle:oinstall /dev/sdc
chmod 600 /dev/sdc
rman TARGET sys/@ NOCATALOG AUXILIARY sys/@
orapwd file=$ORACLE_HOME/dbs/orapw..... password=..... entries=10
Oracle Inventory
# mkdir -p /u01/app/oraInventory
# chown -R grid:oinstall /u01/app/oraInventory
# chmod -R 775 /u01/app/oraInventory
Grid Infrastructure Home
# mkdir -p /u01/app/oracle/product/11.2.0/grid
# chown -R grid:oinstall 
/u01/app/oracle/product/11.2.0/grid
# chmod -R 775 /u01/app/oracle/product/11.2.0/grid
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I /opt/app/11.2.0/grid/perl/lib -I /u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -delete

DB Link

create database link <link name>
   connect to <user conn name> identified by <passwd>
   using <aliase dase>
--
drop database link <link name>
or
drop publik database link <link name>
--

удалить обычный линк может только его создатель, все остальные пользователи не могут удалять линки. исключением служить publik link, его удалять могут все у кого есть системная привилегия: "DROP PUBLIC DATABASE LINK" -- Какие линки активны


select p.LOCAL_TRAN_ID,
n.DATABASE,
n.DBUSER_OWNER,
n.DBID,
p.STATE,
p.FAIL_TIME 
from dba_2pc_neighbors n, dba_2pc_pending p 
where n.LOCAL_TRAN_ID = p.LOCAL_TRAN_ID

--Strart/Stop/Status
cd $ORACLE_HOME/bin
./emctl stop dbconsole
./emctl start dbconsole
./emctl status dbconsole
--Create/Recreate
--drop
emca -deconfig dbcontrol db -repos drop
--create
emca -config dbcontrol db -repos create

Flashback

В SQLPlus:
  SHUTDOWN IMMEDIATE;  
  STARTUP MOUNT;  
  ALTER SYSTEM SET 
DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days(1 day 1440)  
  ALTER DATABASE FLASHBACK ON;
  ALTER DATAABASE OPEN;

Online/offline датафайл

--Отключить файл
sqlplus:
  >shutdown immediate;
  >startup mount;
  >alter database datafile <number datafile> offline;
  >alter database open;
--Включить файл
sqlplus:
  >alter database datafile <number datafile> online;

Rename schema

--Не документированно!

update sys.user$ 
set name='<New name>' 
where user#=<user id>
and name='<Old name>'

Перекомпилировать объекты схемы

Reset parameter

alter system reset <parameter> scope=spfile sid='*'
или 
alter system reset <parameter> scope=spfile sid='';

SQL PLAN

Для получения плана запроса можно использовать встроенный пакет DBMS_XPLAN. При работе с ним есть два варианта: 1. Получение плана выполненного запроса select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL ID')) 2.Получение плана нового запроса - Выполить в среде EXPLAIN PLAN FOR <Текст SQL запроса> - select * from TABLE(DBMS_XPLAN.display) p.s.: в случаи если запрос был выполнен и известен его SQL ID можно использовать так же представление V$SQL_PLAN

Save DDL from dump

impdp directory=<Directory name> dumpfile=<source dump file name> sqlfile=<output file name DDL>

Standby FAILOVER

1) Включаем FlashBack на каждом сервере SQL> SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------ NO shutdown immediate; startup mount; ALTER DATABASE FLASHBACK ON; alter database open; SQL> SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------ YES // standby SQL> SELECT name, value, time_computed FROM v$dataguard_stats WHERE name like '%lag%'; // primary shutdown abort; // standby // Для остановки резервной базы данных, нужно сначала остановить применение журналов. Нужно задать запрос к представлению V$MANAGED_STANDBY. Если в нем будет перечислен сервис применения журналов, его надо уничтожить следующей командой. ALTER database recover managed standby database cancel; // Если резервная база данных была сконфигурирована с резервными журнальными файлами: ALTER database recover managed standby database FINISH; // Если резервных журнальных файлов нет, выполните: ALTER database recover managed standby database FINISH skip standb logfile; ALTER database commit to switchover to primary with session shutdown; ALTER database OPEN; // primary - поднимаем как standby STARTUP MOUNT; SQL> SELECT TO_CHAR(standby_became_primary_scn) FAILOVER_SCN from v$database; FLASHBACK DATABASE TO SCN 961994; ALTER database convert to PHYSICAL STANDBY; SHUTDOWN IMMEDIATE; STARTUP MOUNT; // Запустить процесс применения журналов ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Standby SWITCHOVER

Проверяем: \\primary select switchover_status from v$database; Если TO_STANDBY все гуд, можно переключатся select max(sequence#) from v$log; \\standby select switchover_status from v$database; select max(sequence#) from v$log; // secondary SQL> SELECT client_process, process, sequence#, status from v$managed_standby; // primary SELECT thread#, sequence#, status from v$log; Необходимо убедиться, что redo current, и все redo журналы были применены к standby database. Необходимо, чтобы процесс MRP0 с тем же sequence был в статусе APPLYING_LOG Если Вы не видите MRP0 line, значит, применение не запущено. Если Вы видите статус WAIT_FOR_GAP, тогда, Вы не должны делать switch over, пока проблема не будет решена. Если Вы видите статус WAIT_FOR_LOG, тогда Вы не работаете в режиме применения в реальном времени, либо установлены задержки (DELAY) в применении. Если были установлены задержки в применении, необходимо остановить процесс применения и перестартовать его с использованием параметра NODELAY, иначе switchover не будет завершен. // primary SQL> alter system archive log current; // Инициирование перехода основной базы данных к роли физической резервной базы данных SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; // secondary sqlplus / as sysdba SQL> SELECT switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY // Статус должен быть TO PRIMARY. После этого можно переключиться к основной базе данных. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; SQL> ALTER DATABASE OPEN; // primary > sqlplus / as sysdba SHUTDOWN IMMEDIATE; ORA-01092: ORACLE instance terminated. Disconnection forced quit sqlplus / as sysdba STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; // secondary alter system switch logfile; select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY // primary select switchover_status from v$database; SWITCHOVER_STATUS -------------------- NOT ALLOWED select NAME,sequence#, STAMP,ARCHIVED,APPLIED,DELETED,STATUS,REGISTRAR, to_char(COMPLETION_TIME, 'dd-mon-rr hh24:mi:ss') completion_time from v$archived_log order by sequence#;

Standby create

Создание Oracle standby: Настройка primary server: sql>alter database archivelog; sql> alter database open; sql>ALTER DATABASE FORCE LOGGING; создаем standby log (на 1 больше чем redolog) ALTER DATABASE ADD STANDBY LOGFILE '<path>' SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE '<path>' SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE '<path>' SIZE 100M; ALTER DATABASE ADD STANDBY LOGFILE '<path>' SIZE 100M; SQL> ALTER SYSTEM SET log_archive_config = 'dg_config=(primary,standby)'; SQL> ALTER SYSTEM SET log_archive_dest_2 ='service=standby async valid_for=(online_logfile,primary_role) db_unique_name=standby'; На standby сервер устанавливаем ПО oracle, без создания БД. Конфигурируем листенер и tnsname: listener LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <name>.localdomain)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = standby.localdomain) (ORACLE_HOME = /u01/app/oracle/product/11.2) (SID_NAME = standby) ) )

tnsname primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <name>.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary.localdomain) ) ) standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <name>.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby.localdomain) ) )

создаем pfile и стартуем с его помощью без монтирования БД (в нем достаточно одной строчки db_name=standby) >sqlplus / as sysdba >startup nomount pfile='<path file>'; проверяем видимость баз на обоих серверах tnsping primery tnsping standby на праймари rman target sys/<pass> RMAN> connect auxiliary sys/<pass>@standby run { allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate channel prmy3 type disk; allocate channel prmy4 type disk; allocate auxiliary channel stby type disk; duplicate target database for standby from active database dorecover nofilenamecheck spfile parameter_value_convert 'primary','standby' set db_unique_name='standby' set db_file_name_convert='/primary/','/standby/' set log_file_name_convert='/primary/','/standby/' set control_files='/u01/app/oracle/oradata/standby/standby1.ctl' set log_archive_max_processes='5' set fal_client='standby' set fal_server='primary' set standby_file_management='AUTO' set log_archive_config='dg_config=(primary,standby)' set log_archive_dest_2='service=primary ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primary' ; } RMAN>quit SQL> sqlplus / as sysdba SQL> alter system switch logfile; На standby: >sqlplus / as sysdba SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; проверяем: SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; select process, status, thread#, sequence#, block#, blocks from v$managed_standby; переключаем еще раз лог на праймари и проверяем результат

Standby перевод в read only

Соединяемся со STANDBY и выдаем: SQL> alter database recover managed standby database cancel; Media recovery complete. Теперь логи все еще поступают на STANDBY, но она уже не «подкатывается». SQL> alter database open read only; Database altered. Starting Up a Physical Standby Database SQL> STARTUP MOUNT; // Перезапустить процесс применения журналов. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; To start real-time apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE; SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; # If log apply services are running, cancel them as shown in the following example: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; # Shut down the standby database. SQL> SHUTDOWN;

Using RO resourc

Добавить в pfile: *.event='10298 trace name context forever, level 32' и стартовать инстанс с получившегося pfile

Анализ таблицы

analyze table <table_name> compute statistics;

Добавить/удалить REDO

Добавить группу: ALTER DATABASE ADD LOGFILE GROUP <number> ("<1 file path>", "<1 file path>",...) SIZE <size>; Удалить группу: ALTER DATABASE DROP LOGFILE GROUP <number>;

Изменение колонки

alter table <table_name> modify <column name> <column type>

Изменение таблицы

Добавить колонку: alter table <table name> add <new column name> <column type> Удалить колонку: alter table <tanle name> drop column <column name> Изменение типа колонки: Alter table <table name> modify <column name> <new type> Добавить комментарий: comment on column <column name> is <текст комментария>

Открытие БД со сбросом журналов

ALTER DATABASE OPEN RESETLOGS;

Переключение журнала базы данных Oracle

ALTER SYSTEM SWITCH LOGFILE;

Перенос (переименование) датафайла

sqlplus: >shutdown immediate; >startup mount >alter database rename file <old name> to <new name>; >alter database open;

Перенос таблицы в др. ТП/Indexs

--Table alter table <table name> nologging; alter table <table name> move tablespace <TS name>; alter table <table name> logging; --Index alter index <index name> rebuild tablespace <TS name> [online] [nologging] [parallel n]

Свободное место во FRA

select name, round(space_limit/1024/1024/1024,3) as "All", round(space_used/1024/1024/1024,3) as "Use", round((space_limit - space_used)/1024/1024/1024, 3) as "Free" from v$recovery_file_dest;

Стоп/старт инстанс

В CMD: sqlplus / as sysdba {sqlplus sys/<password>@aliase as sysdba} shutdown immediate startup или {startup mount alter database open}

Трассировка сессии

--Смотрим сид и сериал: select sid, serial# from v$session; --в SQLPLUS от SYS: begin dbms_system.set_ev(sid,serial#,10046,12,""); end; 0 - трассировка выключена. 1 - минимальный уровень. результат не отличается от установки параметра sql_trace=true 4 - в трассировочный файл добавляются значения связанных переменных. 8 - в трассировочный файл добавляются значения ожидании событий на уровне запросов. 12 - добавляются, как значения связанных переменных, так и информация об ожиданиях событий. --отключить: --в SQLPLUS от SYS: begin dbms_system.set_ev(sid,serial#,10046,0, ""); end; --Обработка полученного трейса В CMD: >cd <path trace> >tkprof <trace name> <file out name>

Управление переключением в режим serial direct path read

При использовании ASMM: 1. Задать минимальные значения буферов (параметры db_cache_size и shared_pool_size). 2. Установить адекватное значение скрытого параметра _small_table_threshold.данное значение используется оптимизатором для принятия решения о размере таблицы. если _small_table_threshold*5>колличества блоков в таблице, то режим будет переключен, следовательно дорогой план выполнения (без использования SGA)/alter system set "_small_table_threshold"=999999 scope=spfile; рестарт экземпляра.

Откат транзакций

select usn,
       state,
       undoblockstotal "Total",
       undoblocksdone "Done",
       undoblockstotal-undoblocksdone "ToDo",
       decode(cputime,0,''unknown'',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated"
  from v$fast_start_transactions

Транзакции по которым не удалось корректно провести откат

  select ktuxeusn,
       ktuxeslt,
        ktuxesqn,
       ktuxesta,
       ktuxesiz
from x$ktuxe
where ktuxesta <> 'INACTIVE' and ktuxecfl like '%EAD%' order by ktuxesiz asc

Oracle - команды, полезные запросы

Недавние посты

Comments