Oracle DB administering

Compression in database

Posted on

Compression in database may be used as an Enterprise Edition option that requires a licensing or may be used “for free” if we have Enterprise Edition”. Free compression is: – basic compression on heap tables; – index compression that is usable for b-tree indexted and IOTs. Separately licensed Advanced Compression is applicable for heap tables […]

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, […]