Data Modeling - Logical & Physical design

We use Erwin data modeler to create data dictionary , then we create a main data Model representing the logical world , It include functional, process flow and all other info regarding the business and does satisfy the business requirement.

Then we validate the main model with stake holder and data owners, once that's done , it is time to generate the final Data Dictionary including field definitions and comments.

Next phase is to create an Oracle Schema owner to hold the Physical representation of the model.

Sql*loader, Microsoft Access, Excel could be used to test some data, from Excel to Oracle we could use some third party tools to load the tables.

The logical model could be divided into many physical models, such as representation for operational data store and final Data Warehouse System.

The continuos collaboration with front end developers and team lead is essential to project success.

Killing a session in Oracle RAC 11G

Oracle has added a very useful option in 11gR2 RAC to kill a session on instance B from instance 1. They have added instance name as a parameter in kill session syntax
See following example
sys@ARAC> select username ,status,inst_id from gv$session where username='REMEDY';
USERNAME STATUS INST_ID
------------------------------ -------- ----------
REMEDY INACTIVE 2
sys@ARAC> alter system kill session '787,1113,@2' immediate;
System altered.
sys@ARAC> select username ,status,inst_id from gv$session where username='REMEDY';
no rows selected

Speed up Data Load

To accelerate processing, for example for quick data load or improved query you might try this:

- Put your table in parallel mode.
the operation run in parallel also this suggest your Hardware has multiple cpu for example 4 CPU's.

For this sample the chosen table name is CAR:

create table car(
car_id char(4),
make varchar2(18),
year char(4),
price number)

parallel 4;


alter table car
add constraint xpkcar primary key (car_id);
this just create the primary key. in compatible sql,

In Oracle sqlplus syntax you might just write:
--
create table car(
car_id char(4) primary key,
make varchar2(18),
year char(4),
price number)

parallel 4;


Or if the table is already created just alter it.



SQL> alter table CAR parallel 4;
SQL> alter session enable parallel DML;
DML is for Data manipulation such as insert, update, delete , if you are creating lots of object substitue DML by DDL.

SQL> alter session enable parallel DDL;
You might also force it.
SQL> alter session force parallel DDL;

I read somewhere that insert with syntax of insert into table values (A,B,C,..) does not work on parallel so I will check on it and will eventually post it.

You also might use hint such as
insert /*+ parallel (CAR) */
--

--
Need Oracle consultant contact us at service@jaffrayconsulting.com

Flash recovery Area

This is from my older site oraclemiracledba , that I thought might still be useful here.

What is the Flash Recovery Area ?

The Flash Recovery Area is an area for storing certain types of files on disk. These files typically consist of flashback database logs and RMAN related files. It can be a regular file system or an ASM group. Oracle does NOT need to use a Flash Recovery Area to operate the database. It is recommended for easy of use and simplification for Backup and Recovery purposes and the associated space management. By placing files in the Flash Recovery Area, Oracle will manage the space usage, deleting files that are no longer required, when the space is required. However, if space is not required, Oracle will maintain the files on disk (as a form of cache for files backed up to tape).

Components of the Flash Recovery Area

The flash recover area can contain permanent or transient files. It can contain any or all of the following types of files:

Multiplexed copy of control file.
Multiplexed copy of online redo log.
Archived redo log files
RMAN backup sets, backup copies, etc.
Flashback logs
Of these files ONLY the flashback logs are required to be stored in the flash recovery area.



Configure Flash Recovery Area

Configuring the Flash Recovery Area is simple. Once the disk file system is mounted, or the ASM group created, all that is required is setting is to configure it by setting the two init.ora parameters related to the configuration :

· DB_RECOVERY_FILE_DEST_SIZE

· DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. This value must be set first. It is NOT the actual size available, but the space which Oracle will use to limit the space of files in the Flash Recovery Area.

DB_RECOVERY_FILE_DEST specifies the physical disk location where the Flash Recovery Area will be stored. The Flash Recovery Area can be stored on a regular file system or can be an ASM group. As the files in the FRA are members of the redundancy set ( backups, archived redo logs and flashback logs), they should be stored in a separate physical location from the database operating components (data files, online redo).

Enable the Flash Recovery Area

To enable the Flash Recovery Area, either restart the database with the related parameters set, or issue the following statements:

ALTER SYSTEM SET db_recovery_file_dest_size = ‘1G’ SCOPE=BOTH SID=’*’;

ALTER SYSTEM SET db_recovery_file_dest=’/u06/bkup/flash_recovery_area’;

ALTER SYSTEM SET db_flashback_retention_target=1440;

Data Pump

Some day-to-day DBA tips:
DATA LOAD & IMPORT
Well I recommend starting using Data Pump and using import, exporting utilities just for backward compatibilities, or for structure usages only (create user, grants, etc..).
Now I answer another question.
Question: In the case you imported an existing schemas to a new schemas in the same database or another database instance, if these tables and indexes are created in the wrong tablespace, what to do??
Answer: For indexes it is very easy, you should not even bother modifying the “create script”, just create it where ever it is, then move them to the right location using
Alter index index_name rebuild new_tablespace_name
For tables,
If the original tablespace does not exit, the user have quota on a specific tablespace let’s call it APP_DATA, the tables will be created in user’s default tablespace APP_DATA, BUT If the original tablespace exit in the database (in this case OLD_DATA) then the tables will be imported to old_data, so in this case it will be need to modify the “creation script” and then pre-create all the tables and then move, import or load the data.

Question : Regarding the imp utilities and data pump utilities for Oracle 10g & 11g.
In some case the import is very slow and hang, this might happen if the tables are very large, I usually recommend the use of data pump rather than import, export.
Also have the adequate memory, increae the PGA if not set correctly, go to my memory setting section in this BLOG for memory tuning.
Also have the tablespace files for target in auto extend mode, locally managed.
Also use parallel exuction parameter (parfile), set status =10 (10- second ) to se the progress.
Also alter the table you are going to import to.
Alter table table_name parallel degree 4 ( 4 or any other number related to number of cpu’s).
Make sure you have granted the read and write.
SQL> grant read, write on directory export_dir to expuser, jaffray, userx, userx;
Also you must have EXP_FULL_DATABASE role.
If you get the utl.file error some thing like this:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

The export_dir has or might have issues, it is not writing to the right place,
Check it out by:
select directory_name, directory_path from dba_directories;
Drop directory export_dir;
Create directory export_dir as ‘/u01/app/oracle/export’;
Grant read, write to export_dir

To be continued……

Ethic & Business

The right Oracle project management takes the guesswork out of your system!
It is not enough to have some good Database administrators or system admin in one place; then many developers or thousand of users in another place, then some middle manager in exactly the middle, that is just called an average project with not so average problems; but very serious issues.
In my past experience for about 28 years in IT, I have worked as consultant for at least 30 large and medium size businesses all over the world, in private as well as in public sector, Very often I came to realize that all the high- end solutions, best practices, Use of right technology it might be just another sales pitch by somebody or a group of coordinated people in some managerial position or technical position which are just fighting for the job and not for overall interest of the organization.
So the dilemma now for a real professional is not only to do his job the best way possible, but also how to reconcile that with internal politics and all the non-technical constraints, all without getting in middle of the mess or becoming a target.
That’s not always happening, because people might just decide to go for the easy way out, They do not implement the best solution, every body is happy, but that is not in long term interest of any business or organization, so ethic and professionalism should be always part of every body job, we do not want to copy wall street and all it’s failure and self-indulgence, IT professional are not in this only for the money.

The expertise is seeing the big pictures, the knowing of all side of Database solutions, the correct (business process, the data model, data, OS/Hardware) in respect to cost effectiveness and implementation ease.
The most complicated and advanced technologies are not always the best solution for the customer, what good it can make if they could not maintain or use it correctly.
Taking good care of customers is simply good business model; it creates long-term fruitful relationship.

Memory Management Oracle 11G - Overview

This is a quick overview, for situation when there is no time left reading document, it just need to be fixed then reviewed later on.
Two-memory structure A+B

A include:
1-DB_CACHE_SEIZE important, sample value 1Giga
2-JAVA_POOL_SEIZE 32 M
3-LARGE_POOL_SEIZE 16M
4-SHARED_POOL_SEIZE important, 2Giga

SGA_TARGET = 1+2+3+4 (So any of these 1,2,... if increased , that amount should be added to SGA_TARGET

B include
PGA_AGGREGATE_TARGET very important, 10Giga
Total memory is called memory target

MEMORY_TARGET= A + B

Also Oracle 11g provide automatic memory management and through enterprise manager memory adviser is good tool to monitor memory.

I will append to this article, this is not complete...