[askdba] Re: Need help with Interview Questions

  • From: "Pepling, Todd C." <tpepling@xxxxxxx>
  • To: "'askdba@xxxxxxxxxxxxx'" <askdba@xxxxxxxxxxxxx>
  • Date: Fri, 5 Nov 2004 08:11:02 -0500

See inline.

> -----Original Message-----
> From: Rajesh Puneyani [mailto:rajpuneyani@xxxxxxxxx]
> Sent: Thursday, November 04, 2004 5:52 PM
> To: askdba@xxxxxxxxxxxxx
> Subject: [askdba] Need help with Interview Questions
> Hi Guys,
> Recently I appeared in an interview for a position (after a long
> time), Here are a few questions that got me confused.
> Pls. give me your feedback regarding the same.
> 1. What are various ways to create a table in one database from
> another database ?
> I would say - Create table as select (over the database link) and 
> Export/Import in Table mode.
> Any other ?

I'm guessing they were looking for "copy", as in

  COPY FROM scott/password@DB01

> 2. If a table has 30 Uniform Extents (no size specified) then under
> what circumstances it is good or bad ? Basically - What is the
> advantage of 30 extents over 1 big large extents and what are the
> minuses ?

Pros for 30 uniform extents:
  1) reduce wasted space and fragmentation at datafile/tablespace
  2) compatible with locally managed tablespaces
  3) if even multiple of OS page size, can improve IO performance

Pros for single large extent:
  1) A single large extent can reduce time to execute
     ceratin DDL operations (i.e. truncate table), but
     highly unlikely you'd notice any difference compared
     to a 30 extent segment.
  2) can reduce time needed to extend segment if initial extent
     is sufficient to hold entire segment throughout its life
     (as often is the case in "fact" or "reference" tables)
  3) can reduce chaining

> 3.  What are various scenarios which make RMAN the best backup and
> recovery tool ?

  1) large DBs (i.e. 1 terabyte) with limited backup window/time
  2) DB refreshes/cloning
  3) validating backed-up files are usable in recovery without
     actually trying a recovery
  4) RMAN is "self-managing", but I've seen it puke on recovery,
     so it needs help
  5) automatically purges old archivelogs

I will point out that if your database is made of many 1G files
instead of fewer 8G files, a recovery with RMAN can actually be
slower that a traditional recovery.  Also RMAN had enough bugs
in 8i that it sometimes would fail to recover, while other times
it would work.  It seems to work fine in 9i.

> 4.  what errors did u hit in your most recent DB Recovery procedure ?

A refresh via cloning is a recovery.  Personally I've come across
missing archivelogs, which RMAN would verify/validate during a
backup, thus elliminating the missing archivelog issue.  This ties
into your question above.

This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you have received this email in error please notify the system manager. This 
message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail.

Other related posts: