Oracle Proffisional Interviews

 

OLTP/DSS

 

he type of the workload on a database management system (DBMS) is a key consideration in tuning the system. Allocations for resources such as main memory can be very different depending on whether the workload type is Online Transaction Processing (OLTP) or Decision Support System (DSS). A DBMS also typically experiences changes in the type of workload it handles during its normal processing cycle. Database administrators must therefore recognize the significant shifts of workload type that demand reconfiguring the system in order to maintain acceptable levels of performance. We envision intelligent, autonomic DBMSs that have the capability to manage their own performance by automatically recognizing the workload type and then reconfiguring their resources accordingly. In this paper, we present an approach to automatically identifying a DBMS workload as either OLTP or DSS. Using data mining techniques, we build a classification model based on the most significant workload characteristics that differentiate OLTP from DSS and then use the model to identify any change in the workload type. We construct and compare classifiers built from two different sets of workloads, namely the TPC-C and TPC-H benchmarks and the Browsing and Ordering profiles from the TPC-W benchmark. We demonstrate the feasibility and success of these classifiers with TPC-generated workloads and with industry-supplied workloads.

 

 

Exclusive Interview with Ittichai Chammavanijakul

Posted by Kamran Agayev A. on April 14, 2010

Ittichai  Chammavanijakul has started working with Oracle database since 2000, he has been involved in about all aspects of Oracle database technologies including RAC, ASM, Data Guard and Streams. He has designed and implemented many different varieties of high available database environments using RAC on ASM and Veritas Cluster File System. He enjoys database and SQL tuning. Recently he has also been interested in web development using Oracle APEX.

In his current role as data warehouse architect with Motorola, he has architected and implemented many business-driven solutions using Oracle and other vendor products to meet critical business needs especially in warehousing area.

He is Oracle Certified Master, OCP (8i,9i,10g), CCNA, SCSA and MCP

Could u please provide answer to the following questions as follows:

• Brief information about yourself and your family
  

 I'm married to the most beautiful, charming, funny and talented woman on earth (who is also watching my screen as I'm typing this). We live in Chicago, Illinois with our daughter and a dog who thinks he's a cat. 

• Your education
  

 I did my Bachelor Degree in Electrical Engineering with the emphasis on Telecommunications. My Master Degree is also in the same field with the same emphasis. 

• Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence? 
  

I started my career as a wireless engineer, but later switched into IT as a system administrator. Then one thing led to another and I got into Oracle. I've been working with Oracle since version 8.0. 

• What was the motive behind to prefer Oracle? Who you have been influenced by?
  

Whatever I say here will bore you to tears. I was interested in Oracle because it has the largest market share in RDBMS, and I found its technology to be very challenging. So I motivated myself to learn and become better at it. I've had fun along the way.  

• What would your preference of profession if not Oracle? 
  

  In the alternate universe, I would be a professional golf player. 

• What motivates you in your job?
  

I'm a troubleshooter by nature. I like to solve problems the way I do puzzles or riddles. And in this field, problem is something you never run out of. 

• Do you give lectures on Oracle?  
  

  I do internal workshops and trainings all the time.  

• Have you authored any book in Oracle?
  

  No, but I'd love to someday. 

• Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?  
  

Yes, definitely. Currently I'm reading the "Oracle PL/SQL Programming" by Steven Feuerstein. There are many tips and tricks I've never known before.  

• What do you think on OTN forums?  
  

It is interesting place where you can interact directly with Oracle experts. I believe what makes Oracle great is its community and contributions, especially in the OTN forums, from all Oracle technologists worldwide. 

• Do you refer to the documentation? And how often does it happen?  
  

All the time. I always go to tahiti.oracle.com for Oracle documentation if I would like to research for any topics. 

• What was your greatest achievement as an Oracle DBA?  
  

Besides the OCM certification, the fact that I'm still able to balance my technical and management roles at work is my personal achievement. 

• What is you priority to manage the challenges you face?  
  

Plan ahead as much as you can, but expect the unexpected. 

• How would you describe the essence of your success? According to your definition of success, how successful have you been so far?
  

In addition to dedication and hard working, I love what I'm doing. That will drive you to accomplish anything. 

• What are your best skills which make you differ from others?
  

Seeing the big picture to connect the dots.  

• What's your major weakness?
  

  I can't say no to ice cream.  

• Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?
  

Have I lost my spirit? I would say no. Occasional discouragement, perhaps. But doesn't that happen to everybody? 

• What is the next success you would like to attain and your efforts to this end?
  

I'd like to find more time to blog more consistently. Ideas abound, but time is scarce. 

• How do you balance your daily life with your career?
  

It hasn't been a challenge, really. I love my job and I enjoy goofing off with my family. I've been blessed to have a good balance between the two while making time for myself as well. 

• Please describe your one day summary of activities?
  

I work 8-10 hours a day with occasional breaks in between and lots of phone calls and (back-to-back) meetings. In the evening, I walk the dog, play with my daughter, and cook up something with my wife in the kitchen. We love to cook. 

• How many hours do you work and sleep in a day?
  

It is varied. Usually I work about 8-10 hours a day. I also sleep 8-10 hours a day.  

• Where and how do you spend your daily, weekly and annual holidays?  
  

At home, we relax in the kitchen a lot, cooking up new dishes. On weekends, my family and I like to visit a new restaurant to try their food. Each year, we make a point to travel to a new overseas destination. 

• Do you think about Oracle during vacations?
  

  Uh, heck, no.  

• Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?  
  

  I play golf. It relaxes me and helps increase my ability to focus at the same time. Golf is about figuring out creative solutions to problems. You practice a lot disciplines in this game. You compete with yourself because, unlike other sports, it has no referee or umpire. 

• What's your favorite meal and non-alcoholic drink?  
  

I love Thai street foods: Khao Man Gai, Pad Thai, etc. I'm a fan of foie gras torchon, artisan cheeses and all those things, but the truth remains — the best foods in the world are made street-side and sold on carts. 

• What foreign languages do you know? 
  

English (foreign to me as my mother tongue is Thai). I also speak some Mandarin and read Attic Greek and Classical Hebrew. 

• What's your average typing speed?  
  

 63 WPM last time I checked when playing SpongeBob SquarePants Typing game at Apple Store. 

• Have you ever get involved in politics? 
  

No. Never. 

• What are your hobbies?   
  

Golf, photography and reading.

• How do you spend your free time?
  

We're a family of foodies, so we're always going out to eat different types of food at different places. 

• What's your biggest ambition?  
  

I'd like to start a nonprofit organization that deals with education for the underprivileged. 

• What would be your advice to the beginners in Oracle?
  

Read a lot especially Oracle documentations and/or blogs. Ask questions on forums or blogs. Test everything you read or you are told. Understanding basic concepts is the key. It is tempting to jump into more advanced concepts like RAC, ASM, etc. But without a solid foundation, it may be difficult to grasp the whole. 

• Would you like your children to follow in your footsteps or take a different path in life? 
  

I just want my daughter to be a happy person. That's all I ever want for her in life. If happiness means she follows my footsteps, then so be it. 

• Do you have any followers of you?  
  

You mean stalkers?  

• What is your vision on the future of Oracle? 
  

Oracle will continue to expand its product portfolios by acquiring more companies. Oracle solutions will be more complete covering more aspects of technologies. 

• Could you please take a photo in your office near to your desktop?  
  

Unfortunately, this is against the company's policy to take picture in the office.

 

 

 

 

Oracle Flashback Data Archive (Total Recall)

Posted by Kamran Agayev A. on July 21, 2010

Starting from Oracle 11g version, it's possible to keep every transaction made to the table and keep it as long as you want. Before 11g, in order to get before image of any row, either we were getting it from archived redo log files (if they are kept) using Log Miner, or were writing a trigger to save the data in another log table. But now, using Flashback Data Archive feature, we don't need to use Log Miner or trigger to track changes made to the table. The new background process, FBDA (Flashback Data Archive) tracks all changes made to the table and stores it in a file in a compressed and partitioned format. However, you can't use this feature with clustered, temporary, nested, remote or external tables and LONG or nested columns

It tracks all transactional changes made to specific tables for the specific time interval. To use Flashback Data Archive feature the user needs to have the FLASHBACK ARCHIVE ADMINISTER system privilege. Moreover, the FLASHBACK ARCHIVE object privilege should be granted to the user to enable historical data tracking. In the following scenario we show you the configuration and usage of this feature in detailed examples

-          Create a new user and grant him the required privileges:

view source

print?

1	SQL> create user usr identified by usr;
2

 

3	User created.
4

 

5	SQL> grant connect, resource, flashback archive administer to usr;
6

 

7	Grant succeeded.
8

 

9

SQL>

-          Create a new separate tablespace for data archive

view source

print?

1	SQL> CREATE TABLESPACE tbs_arch DATAFILE 'c:\flashback_archive.dbf' size 10m;
2

 

3	Tablespace created.
4

 

5

SQL>

-          Create flashback archive on this tablespace using CREATE FLASHBACK ARCHIVE command as follows:

view source

print?

1	SQL> create flashback archive fl_arch
2

 

3	2  tablespace tbs_arch retention 1 year;
4

 

5	Flashback archive created.
6

 

7

SQL>

With above command we've created a Flashback Archive named FL_ARCH which resides in the tablespace TBS_ARCH and holds information for 1 year. It means that we can use any Flashback Query which contains 1 year historical information regarding the table that assigned to this flashback archive

-          Now, create a table, insert one row and assign it to this flashback archive:

view source

print?

01	SQL> create table tbl_fl_archive (id number, name varchar2(20));
02

 

03	Table created.
04

 

05	SQL> insert into tbl_fl_archive values(1,'Flashback Archive');
06

 

07	1 row created.
08

 

09	SQL> commit;
10

 

11	Commit complete.
12

 

13	SQL> select * from tbl_fl_archive;
14

 

15	ID NAME
16

 

17	---------- --------------------
18

 

19	1 Flashback Archive
20

 

21	SQL> alter table tbl_fl_archive flashback archive fl_archive;
22

 

23	Table altered.
24

 

25

SQL>

The historical change on the table TBL_FL_ARCHIVE is now will be written to the flashback archive named FL_ARCHIVE.

-          To test it, delete all rows and use Flashback Query on that table. Remember, it will not look for the UNDO data, it will look to the flashback archive file for the changes

view source

print?

01	SQL> select to_char(sysdate,'ddmmyyyy hh24:mi:ss') ddate from dual;
02

 

03	DDATE
04

 

05	-----------------
06

 

07	13022010 12:46:49
08

 

09	SQL> delete from tbl_fl_archive;
10

 

11	1 row deleted.
12

 

13	SQL> commit;
14

 

15	Commit complete.
16

 

17	SQL> select * from tbl_fl_archive;
18

 

19	no rows selected
20

 

21	SQL> select * from tbl_fl_archive as of timestamp to_timestamp('13022010 12:46:49','ddmmyyyy hh24:mi:ss');
22

 

23	ID NAME
24

 

25	---------- --------------------
26

 

27	1 Flashback Archive
28

 

29

SQL>

In order to show and proof that it doesn't look to the UNDO tablespace for the historical information on the rows for the specific time, create new undo tablespace and make it default by dropping the old one. Then use Flashback Versions on that table:

view source

print?

01	SQL> conn / as sysdba
02

 

03	Connected.
04

 

05	SQL> show parameter undo_tablespace;
06

 

07	NAME                                 TYPE        VALUE
08

 

09	--------------------- -----------    ---------   ------------
10

 

11	undo_tablespace                      string      UNDOTBS1
12

 

13	SQL> select a.name from v$datafile a, v$tablespace b where a.ts#=b.ts# and b.name='UNDOTBS1';
14

 

15	NAME
16

 

17	------------------------------------------------
18

 

19	C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS01.DBF
20

 

21	SQL> create undo tablespace undotbs2 datafile 'C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS02.dbf' size 10m;
22

 

23	Tablespace created.
24

 

25	SQL> alter system set undo_tablespace='UNDOTBS2';
26

 

27	System altered.
28

 

29	SQL> startup force
30

 

31	ORACLE instance started.
32

 

33	Total System Global Area  431038464 bytes
34

 

35	Fixed Size                  1333676 bytes
36

 

37	Variable Size             251659860 bytes
38

 

39	Database Buffers          171966464 bytes
40

 

41	Redo Buffers                6078464 bytes
42

 

43	Database mounted.
44

 

45	Database opened.
46

 

47	SQL> show parameter undo_tablespace;
48

 

49	NAME                                 TYPE        VALUE
50

 

51	---------------------------              ----------- ------------
52

 

53	undo_tablespace                      string      UNDOTBS2

As you see, we're currently using the different UNDO tablespace that hasn't any information about before images of data blocks of the TBL_FL_ARCHIVE. Now, let's use Flashback Query against to that table:

view source

print?

01	SQL> conn us1/us1
02

 

03	Connected.
04

 

05	SQL> select * from tbl_fl_archive as of timestamp to_timestamp('13022010 12:45:30','ddmmyyyy hh24:mi:ss');
06

 

07	ID NAME
08

 

09	---------- --------------------
10

 

11	1 Flashback Archive
12

 

13

SQL>

This query gets the data from Flashback Data Archive

Modify the Flashback Data Archive

 
 

Use ALTER FLASHBACK ARCHIVE command to change the retention time, purge data or add or remove the tablespace. For this you need to have FLASHBACK ARCHIVE ADMINISTER privilege granted.  

-          To change the retention time, use:

view source

print?

1	SQL> alter flashback archive fl_archive modify retention 6 month;
2

 

3	Flashback archive altered.
4

 

5

SQL>

-          To change tablespace quota of the tablespace that is used by a flashback data archive, use:

view source

print?

1	SQL> alter flashback archive fl_archive add tablespace tbs_arch quota 50m;
2

 

3	Flashback archive altered.
4

 

5

SQL>

-          To add another tablespace for flashback data archive, use:

view source

print?

1	SQL> create tablespace tbs_arch2 datafile 'c:\flashback_archive2.dbf' size 10m;
2

 

3	Tablespace created.
4

 

5	SQL> alter flashback archive fl_archive add tablespace tbs_arch2 quota 10m;
6

 

7	Flashback archive altered.
8

 

9

SQL>

-          To remove the tablespace from use by flashback data archive, use:

view source

print?

1	SQL> alter flashback archive fl_archive remove tablespace tbs_arch2;
2

 

3	Flashback archive altered.
4

 

5

SQL>

-          To purge the data that's in Flashback Data Archive, use PURGE BEFORE SCN (TIMESTAMP) or PURGE ALL as follows:

view source

print?

01	SQL> ALTER FLASHBACK ARCHIVE fl_archive PURGE ALL;
02

 

03	Flashback archive altered.
04

 

05	SQL> ALTER FLASHBACK ARCHIVE fl_archive purge before timestamp to_timestamp('13022010 12:49:30','ddmmyyyy hh24:mi:ss');
06

 

07	Flashback archive altered.
08

 

09	SQL> ALTER FLASHBACK ARCHIVE fl_archive purge before scn 988827;
10

 

11	Flashback archive altered.
12

 

13

SQL>

Dropping Flashback Data Archive

 
 

To drop flashback data archive use:

view source

print?

1	SQL> drop flashback archive fl_archive;
2

 

3	Flashback archive dropped.
4

 

5

SQL>

Using default Flashback Data Archive for the system

 
 

As default, Oracle doesn't use any flashback data archive. To set default flashback data archive, use the following command by connecting as SYS user:

view source

print?

1	SQL> conn / as sysdba
2

 

3	Connected.
4

 

5	SQL> alter flashback archive fl_arc set default;
6

 

7	Flashback archive altered.
8

 

9

SQL>

By setting default flashback data archive, you will not need to specify it manually on each table you want to keep changes

To disable flashback archive for a table, use:

 
 

view source

print?

1	SQL> alter table tbl_fl_archive no flashback archive;
2

 

3	Table altered.
4

 

5

SQL>

Query Flashback Data Archive

 
 

There're mainly three views that are used to query the information regarding Flashback Data Archive.

The first view is DBA_FLASHBACK_ARCHIVE which gives information about Flashback Data Archive files

view source

print?

01	SQL> select * from dba_flashback_archive;
02

 

03	FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME
04

 

05	LAST_PURGE_TIME
06

 

07	---------------      --------------             ------------  ----------
08

 

09	FL_ARCH                       2                 365           13-FEB-10
10

 

11	08.05.14.000000000 PM                    13-FEB-10 08.05.14.000000000 PM

The second view is DBA_FLASHBACK_ARCHIVE_TS which gives information about tablespace that contains flashback data archives:

view source

print?

1	SQL> select * from dba_flashback_archive_ts;
2

 

3	FLASHBACK_ARCHIVE_NAME       FLASHBACK_ARCHIVE#  TABLESPACE_NAME QUOTA_IN_MB
4

 

5	------------------     -----------------   -------------    ----------
6

 

7	FL_ARCH                        2                TBS_ARCH        FL_ARC

The third view is FLASHBACK_ARCHIVE_TABLES which displays the information of the tables that uses Flashback Data Archive:

view source

print?

1	SQL> select * from dba_flashback_archive_tables;
2

 

3	TABLE_NAME    OWNER_NAME    FLASHBACK_ARCHIVE_NAME   ARCHIVE_TABLE_NAME
4

 

5	----------    ----------    ----------------------   ------------------
6

 

7	TBL_FL_ARCHIVE  US1        FL_ARC               SYS_FBA_HIST_69845

As it was told above, Flashback Data Archie technology stores data in Compressed and Partitioned format. It should be checked easily. Get the name of the main table that's used to store the data and query USER_TAB_PARTITIONS view as follows:

view source

print?

01	SQL> select table_name,tablespace_name from user_tables;
02

 

03	TABLE_NAME                     TABLESPACE_NAME
04

 

05	------------------------------ ------------------------------
06

 

07	SYS_FBA_HIST_69845
08

 

09	SYS_FBA_TCRV_69845             TBS_ARCH
10

 

11	SYS_FBA_DDL_COLMAP_69845       TBS_ARCH
12

 

13	TBL_FL_ARCHIVE                 USERS
14

 

15	SQL> select table_name, partition_name, compression, compress_for from user_tab_partitions where table_name='SYS_FBA_HIST_69845';
16

 

17	TABLE_NAME                     PARTITION_NAME                 COMPRESS
18

 

19	----------------           --------------                 ---------
20

 

21	SYS_FBA_HIST_69845             HIGH_PART                      ENABLED
22

 

23

SQL>

 

 

Takeaway: If you've been tasked with developing a disaster recovery plan for your company, these 10 guidelines will show you how to do it effectively.

Hollywood has used disaster as the premise for many movie blockbusters. Countless heroes have fought raging fires, wayward meteors, and destructive man-made forces to keep the world intact. In an ironic twist, one movie studio faced its own disaster of sorts when its IT system experienced a disaster—a distribution transformer that powered its computer room from outside the building short-circuited and exploded.

The studio's processors contained applications needed to schedule broadcast times for the company's premier cable television station and to manage the production, distribution, and accounting of domestic entertainment videos, laser discs, and interactive games. Okay, so the fate of the world wasn't at stake, but in terms of the potential loss of time and money for the studio, it was a disaster of epic proportions.

Unfortunately, the studio didn't have a solid disaster recovery plan in place. But it didn't take too long for it to learn the value of one. The studio hired Rick Schiesser, the author of the download "Develop an effective disaster recovery plan," to implement a recovery plan. Download it yourself to get the benefit of his expertise.

The download begins by describing the lessons learned from the studio's IT catastrophe, including what constitutes a "disaster," the distinction between the concepts of disaster recovery and business resumption, and how recovery time should be spent. It also outlines the 10 steps involved in developing an effective disaster recovery process. These steps include:

• Acquiring executive support: This is important because all funding approval comes from senior management. By involving upper management early in the process, you secure their emotional and financial buy-in should a calamity occur.
  
• Selecting a process owner: Potential candidates include an operations supervisor, the data center manager, and even the infrastructure manager.
  
• Conducting a business impact analysis: No disaster recovery plan will be able to justify the expense of including every business process and application in a recovery, so you should inventory and prioritize critical business processes.
  
• Assessing possible business continuity strategies: This would likely include alternative remote sites within the company and geographic hot spots supplied by an outside provider.
  
• Planning and executing regularly scheduled tests of the plan: Some progressive companies test three or four times annually.
  

The author has a lot of experience in managing and consulting on IT infrastructures. In the download, he also shares some nightmarish experiences that illustrate how critical planning, preparation, and performance are to a good disaster recovery plan.

If you've been charged with creating a disaster recovery plan for your organization, you should read these tips from someone experienced with the process.