Exclusive Interview with Hemant K Chitale

Hemant K Chitale has 17 years of experience as an Oracle DBA. He has been a DBA on a wide variety of Unix platforms (some of them extinct now), Linux and Windows, from V6 to 10g. His career has spanned organisations from the Financial Services Industry to Manufacturing to Consulting. He has been a guide to junior DBAs and is in the habit of writing notes and case studies on technical issues in Oracle Database Administration. Portions of his spare time are spent on Oracle forums and on testing features of the Database. Database Performance and Backup and Recovery are his areas of interest. However, he does also have other interests in Non Fiction books and Serious Movies. “To learn and share knowledge” is a constant driver..

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

• Brief information about yourself and your family

I have a wife and a 14 year old son in Secondary School.

• Your education

My formal degree is in Finance.  However, I have done a Post  Graduate Diploma in Software Technology after I began working in I.T.

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

I began with Oracle5 on DOS and Xenix.   When I joined Stock Holding Corporation of India Ltd, I began in Securities Trading Market Operations.  However, the systems we were using intrigued me.  The architecture was ahead of it’s time : Distributed Databases with Oracle5 on DOS sharing data with a “central” Oracle5 database on a Xenix server.

I moved to the I.T. department after my first year and soon started working on the Database sometime in 1991.

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

Other than Oracle, the only “Database” I had seen was dBASE-III+  !  I liked Oracle for it’s multi-user support in V5.  V6 and V7 introduced Rollback Segments, Redo Logs, Row Locking (the “Transaction Processing Option”), PLSQL and I was hooked.

Years ago, before Oracle books came out, I read Steve Adam’s postings on newsgroups.   Then, later, Cary Millsap’s book “Optimizing Oracle Performance” Jonathan Lewis’s book “The Cost Based Optimizer”.

• What would your preference of profession if not Oracle?

Teaching.  I like sharing knowledge.

• What motivates you in your job?

The opportunities to “discover” different facets of databases and application design.  Some implementations really leave you with “awe” while others are “shocking”.  It’s always something new, exciting, challenging or disappointing or frustrating.  It is much more intellectual than Finance.

• Do you give lectures on Oracle?

When I have had the occasion to.  I have conducted a 5 day DBA-I course once and a course in Advanced Replication (both in the 8i days).   Besides these, I have, on occasion, conducted 2-4 hour sessions.

• Have you authored any book in Oracle?

Unfortunately, no.  I wish to and intend to.

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

I read non-Oracle books as well !  See my recommendations page http://web.singnet.com.sg/~hkchital/Recommendations.htm

The most recent Oracle books that I have been reading have been “Refactoring SQL Applications”  (ok, not strictly Oracle-only) by Stephane Faroult and “Advanced SQL Functions in Oracle 10g” by Richard Walsh Earp and Sikha Saha Bagui.

However, I am currently reading “The Professional” by Subroto Bagchi and “Fool’s Gold” by Gillian Tett.

• What do you think on OTN forums?

A very useful platform, still underused.  There are many people I know that should be on OTN forums but, for whatever reasons that I cannot fathom, are averse.  I guess you have to have a certain attitude before you open up or spend time on forums.  This is an infinitely better way to spend your time than something silly as Facebook.

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

Very frequently.   Most often the SQL Reference (because I don’t  — and do not intend to – memorise syntax.  Syntax has to be understood rather than memorized). Also, the RMAN Reference (known as the “Backup and Recovery Reference”) and the Database Reference.

• What is the biggest mistake you have ever made during your DBA career?

There was this time when I deleted an Online Redo Log when re-organising Logs. Fortunately, it wasn’t a CURRENT file but an INACTIVE one, so there was no harm done.  I could clear that quickly enough but did also bounce the database instance “to be safe”.

• What was your greatest achivement as an Oracle DBA?

 Single-greatest ?  Actually multiple great ones.  Cross-Platform Migration from V6 to V7 in a short time-frame was one – although most DBAs would laugh at this now, back in those days it was my first Upgrade + Cross Platform + Cross Location migration.  Another one was implementing Oracle OPS in V7.   Building a Standby Database in V7 (DataGuard was a decade in the future !).  There have been a few Oracle Ebusiness Suite (10.7 and R11) upgrades that have been significant achievements.  As also storage-based D.R. implementations.

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

 Staying cool.  I can handle technology failures.  People’s unwillingness  to learn and/or share knowledge, particularly when it is needed, is something which makes me despair.  What makes me angry pushing a “never-live” project and continuing it as if it were a success.  I have seen instances of refusal to acknowledge that corrective action is required and that responsibility must be owned.

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

I wouldn’t say that I am successful yet !  I am trying to do a good job but am not near achieving my potential.

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

Understanding the technology

• What’s your major weakness?

Not understanding people – what motivates one person but not another, why does a person or a group of people behave in a certain manner ?  Humans just are weird !

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

Yes.  Leading to depression.  The best person to talk to is your wife.  The next best person is someone outside the organization you work for.  However, fortunately, I have had good supervisors throughout my career, only that I have approached them too late.

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

Ah!  That would be telling. 

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

“Work-Life balance” is an  empty phrase.  What you really need to do is to be happy – whether at work or at home or on OTN forums  (or, I wouldn’t say this about myself, playing computer games !)

• Please describe your one day summary of activities?

Get up.   Read the newspaper.  Get ready for work.  Commute to work.  Do some work.  Return home.  Watch Television.  Spend time on the Internet (forums, lists, news etc).

Work should not be the most important part of the day – everything is equally important.

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

 4 to 6.  Sometimes I wake up in the night and can’t go back to sleep. 

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

 Annual holidays in India with my parents, brothers and in-laws.  An additional visit to some other neighbouring country in the region, about once in two years.

• Do you think about Oracle during vacations?

Only on rare occasions.

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

No.

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

Indian Vegeterian  (which covers a whole gamut of different foods, believe me !).  Tea.

• What foreign languages do you know?

Foreign to which country ?  Hindi, Marathi and Gujarati are Indian languages that are Foreign outside of India !

• What’s your average typing speed?

I have a certified speed of 40 wpm on manual typewriters from 1981.   Computer keyboards nowadays are different from those typewriters but I have heard people say that I am “fast on the keyboard”.

• Have you ever get involved in politics?

No.

• What are your hobbies? 

Reading.  Non-fiction – economics, history, politics, science/technology and Oracle – besides a bit of  crime / detective fiction.

• How do you spend your free time?

Television.  Crime Channels,  Knowledge Programs, News Programs and a few family programmes.

Reading. 

On Internet Forums and Email lists.

Internet News.

• What’s your biggest ambition?

I am not known to be ambitious. 

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

Read the documentation, practice Oracle, learn, keep an open mind.

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

My son will NOT be an Oracle DBA.  That is 100% certain !  He knows what the job requires.

• Do you have any followers of you?

Define “followers”.  I do not like any possible definition of the word.

• What is your vision on the future of Oracle?

Cheaper licencing for the Database products.

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

That’s not possible.

Chapter 10 – Managing the Undo Tablespace

-          Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo

-          Undo records are used to L:

• Rollback transactions when a ROLLBACK statement is issued
• Recover the databsae
• Provide read consistency

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

-          In automatic undo management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions

-          When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.

Undo Retention

-          After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

-          When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Dataabase attempts to retain old undo information before overwriting it. Old (commiteed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.

-          Oracle database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the databsae begins to overwrite expired undo. If the undo tbalespace has no space for new transactions after all expired undo is overwritten, the databsae may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

-          To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed, the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

-          You enable retention guarantee by specifying the RETENTIN GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement.

Chapter 9 – Managing Datafiles and Tempfiles

-          Oracle database assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view. Relative file number uniquely identifieds a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number.

-          When starting an instance, the DB_FILES parameter specifies the maximum number of database files that can be opened for this database. Default value is 200

-          If several disk drives are available to store the databsae, consider placing potentially contending datafiles on separate disks. This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time

-          Datafiles should not be stored on the same disk drive that stores the databsae redo log files. if the datafiles and redo log files are stored on the same disk drive and that disk drive fails, the files cannot be used in your database recovery procedures.

-          To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column

-          To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses

-          Clauses of the ALTER TABLESPACE statement allow you to change the online or offline status of all of the datafiles or tempfiles within a tablespace.

ALTER TABLESPACE tbs_test DATAFILE [ONLINE | OFFLINE]

To rename and relocate datafiles use the following steps:

·         Take the tablespace that contains the datafiles offline:

ALTER TABLESAPCE users OFFLINE NORMAL;

·         Rename the datafiles using the operating system

·         Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database

ALTER TABLESPACE users RENAME DATAFILE ‘file1’ TO ‘file2’;

·         Backup the database

 

-          To drop the datafile, the database must be open and the file must be empty. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile. You can’t drop the first or only datafile in a tablespace, or datafiles in a read only tablespace or SYSTEM tablespace.

-          If you want to configure the database to use checksums to verify data blocks, set the initialization parameter DB_BLOCK_CHECKSUM to TRUE. This causes the DBWn process and the direct loader to calculate a checksum for each block and to store the checksum in the block header when writing the block to disk.

The checksum is verified when the block is read, but only if DB_BLOCK_CHECKSUM is TRUE and the last write of the block stored a checksum. If corruption is detected, the database returns message ORA-01578 and writes information about the corruption to the alert log

Chapter 8 – Managing Tablespaces

Chapter 8 – Managing Tablespaces

-          If the database has multiple tablespaces you can separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline. You can store different datafiles of different tablespaces on different disk drives to reduce I/O contention and backup individual tablespaces

-          Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles

-          SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces.

-          Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps. It provides enhanced performance, and user reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks

-          Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT vlause of the CREATE TABLESPACE statement. This is the default for new permanent tbalespaces, but you must specify the EXTENT MANAGEMENET LOCAL clause if you want to specify either the AUTOALLOCATE clause or the UNIFORM clause. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default) or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM)

-          AUTOALLOCATE means that the extent size are managed by Oracle. Oracle will choose the next optimal size for the extents starting with 64KB. As the segments grow and more extents are needed, oracle will start allocating larger and larger sizes ranging from 1Mb to eventually 64Mb extents.

-          UNIFORM T option tells the database to allocate and deallocate extents in the tablespace with the same unvarying size that you can specify or let extents default to 1Mb.

-          In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment spaceL AUTOMATIC and MANUAL. Manual segment space management uses linked lists called “freelists” to manage free space in the segment, while automatic segment management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

-          To create a  bigfile tablespace, run CREATE BIGFILE TABLESPACE command. Oracle automatically creates a locally managed tablespace with automatic segment space management.

-          If default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace that you are creating

-          Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown.

-          You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view

-          When you create a temporary table, its rows are stored in your default temporary tablespace

-          Use CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single tempfile tablespace

-          To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the ALTER DATABASE RENAME FILE command to update the database controlfiles

-          Using temporary tablespace group, rather than a single remporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions.

-          You create at tablespace group implicitily when you include the TABLESPACE GORUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.

ALTER TABLESPACE tmp_tbs TABLESPACE GROUP group2;

-          The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces

-          To assign tablespace group as a default temporary tablespace, use the following statement:

ALTER DATABASE db_name DEFAULT TEMPORARAY TABLESPACE group2;

-          You can create tablespaces with block sizes different from the standard database block size, which is specified by the DB_BLOCK_SIZE parameter.

-          Use the BLOCKSIZE clause of the CREATE TABLESPACE statement to create a tablespace with a block size different from the database standard block size. In order for the BLOCKSIZE clause to succeed, you must have already set the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter. Further, and the integer you specify in the BLOCKSIZE clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter.

CREATE TABLESPACE tbs DATAFILE ‘/datafile/’ SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;

-          You can’t take SYSTEM, UNDO and TEMPORARY tablespaces offline

-          When you take tablespace to OFFLINE usint IMMEDIATE clause, media recovery for the tablespace is required before the tablespace can be brought online.

-          If you must take tablespace offline, use the NORMAL clause (the default) if possible. This setting guarantees that the tablespace will not require recovery to come back online, even if after incomplete recovery you reset the redo log sequence using an ALTER DATABASE OPEN RESETLOGS statement .

-          Specify TEMPORARY only when you cannot take tablespace offline normally.  In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Specify IMMEDIATE only after trying both the normal and temporary settings

-          You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace.

-          Before you can make a tablespace read-only, the tablespace must be online, can’t be active undo tablespace or SYSTEM tablespace

-          When there’re transactions waiting to commit, the ALTER TABLESPACE .. READ ONLY statement does not return immediately. It waits for all transactions started before you issued the ALTER TABLESPACE statement to either commit or rollback.

-          When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage you should consider setting the READ_ONLY_OPEN_DELAYED parameter to TRUE. This speed certain operations, primary opening the databsae, by causing datafiles in readonly tablespaces to be accessed for the first time only when an attempt is made to read data stored within them

-          You can’t rename the tablespace if

• The tablespace is SYSTEM or SYSAUX tablespace
• If any datafile in the tablespacea is offline or if the tablespace is offline

-          Use the CASCADE CONSTRAINTS clause to drop all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys of tables inside the tablespace

-          Use INCLUDING CONTENTS AND DATAFILES to delete physical datafiles from OS. It an OS error prevents the deletion of a file, the DROP TABLESPACE statement still succeeds, but a message describing the error is written to the alert log

-          If SYSAUX tablesapce becomes unavailable, core database functionality will remain operational.

-          You can monitor the occupants of the SYSAUX tablespace using the V$SYSAUX_OCCUPANTS view.

-          Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, an you use an export/import utility to transfer only the metadata of the tablespace objects to the new database

-          Starting with Oracle Database 10g, you can transport tablespaces across platforms.

-          You can query the V$TRANSPORTABLE_PLATFORM view to see the platfors that are supported, and to determine each platform’s endian format(byte ordering)

-          If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format.  If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

To transport tablespace between database follow this procedure:

• Check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view
• Pick a self containted set of tablespaces
• Generate a transportable tablespace set. It consists of datafiles for the set of tablespaces being transported and an export file containing structural information (metadata) for the set of tablespaces.
• Transport the tablespace set
• Import the tablespace set

 

-          There may be logical or physical dependiencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context “self-containted” means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. To check whether the tablespace is self-containted, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. After invoking this procedure, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view.

-          To convert tablespaces from one endian format to another, login to RMAN and run the following command:

RMAN> CONVERT TABLESPACE sales_1, sales_2 TO PLATFORM ‘Microsoft Windows NT’ FORMAT ‘/tmp/%U’;

-          Then transport both the datgafiles and the export file of the tablespaces to a place that is accessible to the target database

-          Use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs as shown in the following examples

RMAN>CONVERT DATAFILE

‘/h1/finance/tbs_21.f’,

‘/h1/finance/tbs_22.f’

TO PLATFORM =”Solaris[tm] OE (32-bit)”

FROM PLATFORM=”HP Tru64 UNIX”

DB_FILE_NAME_CONVERT=’/h1/finance/work/try/”,’/h1/finance/dbs/tru’

PARALLELISM=5;

Chapter 7 – Managing archived redo logs

Chapter 7 – Managing archived redo logs

-          The LOG_ARCHIVE_MAX_PROCESSES parameter specifies the number of ARCn processes that the database initially invokes. The default is two processes. You can specify this parameter up to ten processes to be started at instance startup. It is dynamic parameter and can be changed with ALTER SYSTEM command. The database must be mounted, but not opened.

-          If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters) or to archive to a primary and secondary destinations (LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST)

-          You specify the location for LOG_ARCHIVE_DEST_n using the keywords LOCATION (for a local file system) and SERVICE (remote archival through Oracle Net service name)

LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/archive’;

LOG_ARCHIVE_DEST_2=’SERVICE=standby’;

-          The optional parameter LOG_ARCHIVE_MIN_SUCCEED_DEST=n determines the minimum number of destinations to which the database must successfully archive a redo log group before it can reuse online log files. the default value is 1. Valid values for n are 1 to 2 if you are using dupliexing, or 1 to 10 if you are multiplexing.

-          MANDATORY option specifies that the transmission of the redo data to the destination must succeed before the local online redo log file can be made available for reuse. The default is OPTIONAL.

-          REOPEN option specifies the minimum number of seconds before the archived process (ARC) or the LGWR should try again to access a previously failed destination. Default is 300 seconds.

-          VERIFY option indicates if an archived (ARC) process should scan and verify the correctness of the contents of the completed archived redo log file, either local or remote, after successfully completing the archival operation. By default, archived redo log file are not verified.

-          When you specify REOPEN for an OPTIONAL destination, the database can overwrite online logs if ther is an error. If you specify REOPEN for a MANDATORY destination, the database stalls the production database when it cannot successfully archive.

-          Use LOG_ARCHIVE_TRACE parameter to specify a trace level

-          To get information about archived redo logs use the following views:

V$ARCHIVED_LOG

V$ARCHIVE_DEST

V$ARCHIVE_PROCESSES

V$BACKUP_REDOLOG

V$LOG

V$LOG_HISTORY

Chapter 5,6 – Managing Control Files and Redo Log files

Chapter 5,6 – Managing Control Files, Redo Log files

- Every oracle database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

-          The database name

-          Names and locations of associated datafiles and redo log files

-          The timestamp of the database creation

-          The current log sequence number

-          Checkpoint information

-The initial control files of an Oracle Database are created when you issue the CREATE DATABASE statement.

- The CREATE CONTROLFILE statement can potentially damage specified datafiles and redo log files. omitting a filename can cause loss of the data in that file, or loss of access to the entire database.

If a datafile exists in the data dictionary but not in the new controlfile, the database creates a placeholder entry in the controlfile under the name MISSINGnnnnn, where nnnn is the file number in decimal. MISSINGnnnn is flagged in the controlfile as being offline and requiring media recovery. If the actual datafile correspoinding to MISSINGnnnn is read-only or offline normal, then you can make the datafile accessible by renaming MISSINGnnnn to the name of the actual file. If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then you cannot use the rename operation to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the datafile

Use V$CONTROLFILE_RECORD_SECTION view to display information about controlfile record sections

 Managing the Redo Log

-          The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

-          The redo log for each database instance is also referred to as a redo thread.

-          Redo log files are filled with redo records. A redo record, also called a redo entry is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

-          Redo entreis record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks

-          Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

-          Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes

-          The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived.

-          The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files. It means that the content has not been flushed to datafiles from database buffer cache, because CKPT has not been started.  Redo log files that are no longer required for instance recovery are called inactive redo log files

-          Log switch is the point at which the database stops writing to one redo log file and begins writing to another.

-           Oracle Database assigns each redo log file a new log sequence number bevery time a log switch occurs. During crash, instance or media recovery the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files

-          Datafiles should be placed on different disks from redo log files to reduce contention in writing data blocks and redo records

-          All members of the same multiplexed redo log group must be the same size. Members of different groups can have different sizes. However, there’s no advantage in varying file size between groups.  

-          The minimum size permitted for a redo log file is 4MB

-          If messages in alert.log file indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups.

-          The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of redo log files for each database.

-          The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members for each group.

-          The ARCHIVE_LAG_TARGET parameter can be set specifically to force logs to be switched and archived. If it is set to a very low value, there can be a negative impact on performance. This can force frequent log switches.

-          To create log groups use one of the following commands:

• ALTER DATABASE ADD LOGFILE (‘/home/oracle/…./redo1c.log’,’/home/oracle2/redo2c.log’) size 10M
• ALTER DATABASE ADD LOGFILE GROUP 5  (‘file1.log’,’file2.log’) size 10M;

-          To create a new member for a redo log group use one of these commands:

• ALTER DATABASE ADD LOGFILE MEMBER ‘/file_name.log’ TO GROUP 5;
• ALTER DATABASE ADD LOGFILE MEMBER ‘/file_name.log’ TO (‘/file1.log’,’file2.log’);

-          An instance requires at least two groups of redo log files

-          If you can drop a redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur and make sure it is archived before dropping it. Check V$LOG view to see whether it has been archived or not. Drop the redo log group using ALTER DATABASE DROP LOGFILE GROUP 4; command  

-          To drop specific inactive redo log members use ALTER DATABASE DROP LOGFILE MEMBER ‘/home/….’; command

-          When redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure.

-          A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABSAE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database. ALTER DATABSAE CLEAR LOGFILE GROUP 3;

-          If the corrupted redo log file has not been archived, use the UNARCHIVED keyword in the statement

-          Use V$LOG, V$LOGFILE and V$LOG_HISTORY views for more information