Oracle DB administering

Example of materialized view with fast refresh

Posted on

drop materialized view log on source_table create materialized view log on source_table tablespace tbs_mnp_big with rowid (field1, field2, field3) including new values; drop materialized view materialized_view create materialized view materialized_view tablespace my_tablespace build immediate refresh fast on commit enable query rewrite as select field1, field2, count(*) cnt, FROM source_table WHERE field1 < 5000 group by […]

Oracle DB administering

Using Swingbench

Posted on

Simple steps to use Swingbench: 1. create test schema with oewizard to start oewizard from Sparc Solaris we might need a new version of Java. 64 bit Java is installed in (for example) /usr/jdk/jdk1.6.0_37/jre/bin/sparcv9/java. /usr/jdk/jdk1.6.0_37/jre/bin/sparcv9/java -XX:MaxPermSize=512m  -Xms4048m -Xmx4048m Edit oewizard and point out which java to start. Examples of test runs: ./charbench -c ../configs/soeconfig.xml -uc […]

Oracle DB administering

Using guaranteed restore point

Posted on

In order to use the guaranteed restore point feature Flash Recovery Area must be configured. To enable Flash Recovery Area we need setup the fwo parameters: DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST. DB_RECOVERY_FILE_DEST_SIZE  should be set before DB_RECOVERY_FILE_DEST. For example: SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10G / SQL> alter system set DB_RECOVERY_FILE_DEST=’/opt/oracle10/flash_recovery_area/rdb4′ / SQL> Alter database flashback on / […]

Oracle DB administering

SQL plan analysis

Posted on

Look at the execution plan for the last SQL statement in the session: SELECT * FROM table(dbms_xplan.display_cursor(null,null,’basic’)); Record and display run time statistics: SELECT /*+ gather_plan_statistics */ p.prod_name, SUM(s.quantity_sold) FROM sales s, products p WHERE s.prod_id =p.prod_id GROUP By p.prod_name ; Check if statistics for a table exists and is not stale: select column_name, num_distinct, […]

Oracle DB administering

Relocate and enlarge online redo logs

Posted on

select * from v$log select * from v$logfile ALTER DATABASE ADD LOGFILE GROUP 10 (‘/opt/oracle10/oradata/logs/redo10.log’) SIZE 1G; ALTER DATABASE ADD LOGFILE GROUP 11 (‘/opt/oracle10/oradata/logs/redo11.log’) SIZE 1G; ALTER DATABASE ADD LOGFILE GROUP 12 (‘/opt/oracle10//oradata/logs/redo12.log’) SIZE 1G; alter system switch logfile alter database drop logfile group 1 alter system flush buffer_cache

Oracle DB administering

Change statistics gathering time

Posted on

Statistics gathering and other supplemental jobs run in the maintenance schedule window. Such jobs are called autotasks. List of tasks available for scheduling: select * from dba_autotask_task sql tuning advisor    AUTO_SQL_TUNING_PROG auto space advisor    auto_space_advisor_prog auto optimizer stats collection    gather_stats_prog The list of maintenance windows: select * from dba_scheduler_windows MONDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN TUESDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN WEDNESDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN […]

Oracle DB administering

Recovering database from RMAN backup

Posted on

Полное восстановление базы из архива RMAN 1. ищем pfile, поднимаем базу в nomount 2. запускаем rman rman target / nocatalog 3. восстанавливаем controlfile из автобэкапа restore controlfile from ‘/u01/backup/rman_pooh/control_c-1989669565-20121123-00’; желательно, чтобы controlfile был восстановлен из архива, сделанного уже после бэкапа, из которого будем восстанавливаться. Иначе в controlfile будет отсутствовать информация о нужном бэкапе и придется […]

Oracle DB administering

Examples of external tables

Posted on

CREATE TABLE admin_ext_employees (employee_id NUMBER(4), first_name VARCHAR2(20), last_name VARCHAR2(25), job_id VARCHAR2(10), manager_id NUMBER(4), hire_date DATE, salary NUMBER(8,2), commission_pct NUMBER(2,2), department_id NUMBER(4), email VARCHAR2(25) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY admin_dat_dir ACCESS PARAMETERS ( records delimited by newline badfile admin_bad_dir:’empxt%a_%p.bad’ logfile admin_log_dir:’empxt%a_%p.log’ fields terminated by ‘,’ missing field values are null ( employee_id, first_name, […]

Oracle DB administering

Physical standby howto

Posted on

http://www.adp-gmbh.ch/ora/data_guard/create_physical_standby_db.html  Cоздать физический Standby Подготовить первичную базу Проверить, что база находится в ARCHIVELOG: SQL> ARCHIVE LOG LIST;SQL> ALTER DATABASE FORCE LOGGING; создать файл паролей сконфигурировать параметры для транспортировки REDO на Standby: LOG_ARCHIVE_CONFIG=DG_CONFIG=(db1,db2) LOG_ARCHIVE_DEST_1=’SERVICE=rdb2 ASYNC DB_UNIQUE_NAME=db2 DELAY=180′ LOG_ARCHIVE_DEST_STATE_1=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE сконфигурировать параметры переключения в standby: FAL_SERVER=db1 – должен быть на стороне standby и указывать на имя сервиса […]