Category Archives: Oracle DB administering

DBMS_REDEFINITION: Последовательность действий при переопределении таблицы

1. Проверяем, нет ли противопоказаний для переопределения таблицы BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE( uname => ‘MYUSER’, tname => ‘FILES’, options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); END; 2. Создаем промежуточную таблицу по мотивам имеющейся таблицы, которую нужно переопределить, с новыми параметрами create table MYUSER.int_files tablespace tbs_new as select * from myuser.files where rownum = 0 Индексы и всякие констрейнты создавать не обязательно. Их […]

Read more

Кодировка SqlPlus в Windows

Чтобы пользоваться SqlPlus из-под Windows: set NLS_LANG=RUSSIAN_CIS.CL8MSWIN1251 chcp 1251 sqlplus В командном окне (Far) нужно поменять шрифт на Lucida Console NLS_LANG обычно приходится ставить, чтобы в скриптах нормально обрабатывались русские символы, а chcp позволяет понимать сообщения на русском, которые Oracle начинает выдавать после установки NLS_LANG.

Read more

Oracle: обновление базы с использованием редакций

Основной смысл заключается в том, чтобы пока все пользователи работают с текущей редакцией, создать новую редакцию на основе текущей, внести в нее необходимые изменения (пользователи этого не заметят), протестировать новую редакцию с изменениями и сделать ее текущей. Чтобы пользователи начали работать с новой редакцией они должны закрыть свои старые сессии и открыть новые, т.е. для […]

Read more

Настройка репликации с Oracle на PostgreSQL

Особенности: репликация между базами данных Oracle и Не-Oracle через Heterogenous Services поддерживается только для шлюзов “Transparent Gateway”, к которым не относится Gateway for ODBC. Проблема возникает при попытке применить захваченные процессом Streams Capture данные к таблице Postgres через Gateway из-за того, что Gateway for ODBC не поддерживает двухфазный commit. Чтобы обойти проблему можно извлекать захваченные […]

Read more

Getting rid of PARTLY AVAILABLE UNDO segments

https://community.oracle.com/thread/2593502 https://dbatricksworld.com/ora-30013-undo-tablespace-undotbs1-is-currently-in-use/ There are ACTIVE and PARTIAL AVAILABLE segments in UNDO: select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′ SEGMENT_NAME TABLESPACE_NAME STATUS —————————— —————————— —————- _SYSSMU10_820739558$ UNDOTBS1 OFFLINE _SYSSMU9_2448906239$ UNDOTBS1 OFFLINE _SYSSMU8_3066916762$ UNDOTBS1 OFFLINE _SYSSMU7_892861194$ UNDOTBS1 OFFLINE _SYSSMU6_1956589931$ UNDOTBS1 OFFLINE _SYSSMU5_2919322705$ UNDOTBS1 OFFLINE _SYSSMU4_3876247569$ UNDOTBS1 OFFLINE _SYSSMU3_4245574747$ UNDOTBS1 OFFLINE for eaxmple :_SYSSMU2_6654314$ UNDOTBS1 PARTLY AVAILABLE […]

Read more

Running access advisor

Start advisor task: DECLARE taskname varchar2(30) := ‘SQLACCESS_20160902_1’; task_desc varchar2(256) := ‘SQL Access Advisor’; task_or_template varchar2(30) := ‘SQLACCESS_EMTASK’; task_id number; num_found number; sts_name varchar2(256) := ‘STS_20160902_1’; sts_cursor dbms_sqltune.sqlset_cursor; BEGIN — in case “invalid number parameter’ error need to set NLS parameter: EXECUTE IMMEDIATE ‘alter session set nls_numeric_characters=”.,”’; /* Create Task */ dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, taskname); /* […]

Read more

Oracle Linux Tests

https://oss.oracle.com/projects/olt/dist/documentation/OLT_TestCoverage.pdf Oracle Linux Tests are designed to verify Linux kernel functionality and stability essential for the Oracle Database. The Oracle Linux Test (OLT) kit, which is distributed as an archive file, provides an automated framework, a set of tools and tests for virtualized and non­virtualized systems. This document is for QA teams who conduct the […]

Read more

Using filesystem snapshots for database backup

http://www.oracle.com/technetwork/database/features/availability/rman-fra-snapshot-322251.html Snapshots Are NOT Backups Introduction While storage snapshots are widely used to quickly create point-in-time virtual copies of data, they are also often marketed as valid “backup solutions”. This is an incorrect and dangerous assumption because snapshots, unless copied to secondary media (e.g. another storage array or tape), do not protect against media failures. […]

Read more

Oracle RDBMS memory parameters

http://www.oracle.com/technetwork/articles/systems-hardware-architecture/using-dynamic-intimate-memory-sparc-168402.pdf http://www.dbas-oracle.com/2011/04/relation-between-memorytarget-sgatarget.html If  MEMORY_TARGET is set to a non-zero value. If  SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively. MEMORY_TARGET can take values from SGA_TARGET + PGA_AGGREGATE_TARGET to MEMORY_MAX_TARGET. If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune […]

Read more

Converting physical standby to snapshot database

http://satya-dba.blogspot.ru/2012/06/snapshot-standby-databases-oracle.html http://www.pafumi.net/Flash_Recovery_Area.html Database should be in flashback mode. To achieve this: SQL> ALTER SYSTEM SET db_recovery_file_dest_size=50G scope=both; SQL> ALTER SYSTEM SET db_recovery_file_dest=’/db/Db_1/fra’; SQL> select name,flashback_on from v$tablespace where flashback_on=’NO’; NAME                           FLA —————————— — TBS_INDEX_BIG                  NO TBS_LOG                        NO SQL> alter tablespace TBS_INDEX_BIG  flasjback ON; Tablespace altered SQL> select flashback_on from v$database; FLASHBACK_ON —————— NO SQL> alter […]

Read more

Exploring PGA usage

Sorting out how Oracle RDBMS uses physical memory. We have: Oracle RDBMS 11g, Solaris 10 SPARC, total 64 GB physical memory available. select sum(pga_alloc_mem)/1024/1024/1024 from v$process 3 GB select sum(value)/1024/1024/1024 from v$sga 35,8 GB sga_max_size = 36 GB swap -s total: 41424832k bytes allocated + 926416k reserved = 42351248k used, 1573376k available # echo ‘::memstat’ […]

Read more

How to reorginize sys.aud$

http://amoratech.wordpress.com/2011/05/12/steps-to-shrink-reorganize-sys-aud-table/ Steps to shrink (reorganize) sys.aud$ table Posted by Jay on May 12, 2011 The following are the quick steps to purge the aud$ table. Starting 10.2.0.1 The SYSTEM tablespace is created with manual segment allocation and as such it is not possible to run the SHRINK command for the objects that are located within. However, […]

Read more

Temporary tablespace usage

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ AS temp_size, a.inst_id as Instance, a.sid||’,’||a.serial# AS sid_serial, NVL(a.username, ‘(oracle)’) AS username, a.program, a.status, a.sql_id FROM   gv$session a, gv$sort_usage b, gv$parameter p WHERE  p.name  = ‘db_block_size’ AND    a.saddr = b.session_addr AND    a.inst_id=b.inst_id AND    a.inst_id=p.inst_id ORDER BY b.tablespace, b.blocks select * from v$temporary_lobs order by cache_lobs desc SELECT A.inst_id, […]

Read more

Purging old trace files

http://www.pafumi.net/11g_Trace_Dump_Files_ADRCI.html   The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data. As you know the location of the alert log files and trace files has changed on 11g: The ADR can be managed via the 11g Enterprise Manager GUI (Database Control and not Grid Control) or […]

Read more

RAC voting disk

http://oracleinaction.com/voting-disk/ 11g R2 RAC : VOTING DISK DEMYSTIFIED Voting disk in 11g In this post, I will write about voting disk – what does it contain, who updates it, how is it used, where is it stored and so on… Voting disk a key component of clusterware and its failure can lead to inoperability of […]

Read more

Starting and stopping RAC

https://balakumarnair.wordpress.com/2011/02/06/crsctl-start-crs-vs-start-cluster-2/ Starting Clusterware In 11gR2, you can start the High Availability Service Daemon (OHASD) and Clusterware stack in a single step (A) or start it in a two step process(B). (A) To start the OHASD & clusterware stack in a single step, use $ crsctl start crs Note that you can start components on the […]

Read more

Configuring client connections for RAC

The topic is described in Doc ID 453293.1 http://www.programering.com/a/MjNxgTNwATU.html tnsnames.ora record examples: Client Side Load Balancing Following is an example of TNS net service alias for client side load balancing CLIENT_LOADBALANCE= (DESCRIPTION = (LOAD_BALANCE = yes) (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip.idc.oracle.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip.idc.oracle.com)(PORT = 1521)) (CONNECT_DATA […]

Read more

Duplicating database with RMAN

One might need some preparation steps to perform before duplicate database command will complete successfully. 1. Record in tnsnames.ora for auxiliary database should contain … keyword. 2. During duplication process the auxiliary database will be remotely restarted. For remote start the database must be statically registered in the listener.ora file. Advisable is to test possibility […]

Read more

Monitoring tablespaces usage

select distinct tablespace_name key_value, free_bytes_in_ts attribute1, total_bytes_in_ts attribute2, round( free_bytes_in_ts / total_bytes_in_ts * 100) attribute3, null attribute4, null attribute5 from ( select df.tablespace_name, df.file_name, sum(NVL(fs.bytes, 0) ) free_bytes_in_file, df.bytes total_bytes_in_file, sum(sum(NVL(fs.bytes, 0) ) ) over (partition by df.tablespace_name) free_bytes_in_ts, sum(NVL(df.bytes, 0) ) over (partition by df.tablespace_name) total_bytes_in_ts–, from dba_free_space fs, dba_data_files df, dba_tablespaces ts where […]

Read more