Wednesday 22 June 2016

Rac Cache Fusion Flow


http://www.datadisk.co.uk/html_docs/rac/cache_fusion.htm

How to Delete Old Obsolete and Expired Oracle RMAN Backup.



If you are sysadmin, sometimes you might find yourself dealing with Oracle backups.
Apart from taking oracle RMAN backup, you should also understand how to Delete the old backups from the RMAN catalog and from physical filesystem.
If you don’t properly delete obsolete and expired Oracle DB backup from the catalog, it will cause some unnecessary throw error message during backup and also it will take-up space at the filesystem level.
Also, it is not recommended to directly remove the RMAN backup files from the filesystem using Linux rm command.
This tutorial explains how to identify the backups that are obsolete and expired, and how to properly delete them from RMAN.

The main parameter that decides what to delete is the retention policy. To identify your retention policy, connect using RMAN and and execute “show all” and look for the following line.

RMAN Retention Policy

In the following example, retention policy is 4 days. So, any backup that is older than 4 days is considered obsolete and old.
$ rman target /
RMAN> SHOW ALL;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
There is a big difference between Obsolte and Expired RMAN backup. We’ll explain both in this article.

I. Delete Obsolete Backup

1. What is an Obsolete Backup?

In our example above, any backup that we have in our system (both on RMAN catalog, and as physical RMAN backup files at the filesystem level) that is older than 4 days is considered obsolete. Please keep in mind that sometimes it might not be just 4 days. It depends on whether a full-backup is available within the last 4 days. If you don’t have a full backup in the last 4 days, then what RMAN considers as obsolete will be even longer than that. i.e Until the last full backup.
Obsolete backups are those that are not required to satisfy RMAN requirement of what is specified in the retention policy to recover the database from the backup.
The following three things will happen when you perform “DELETE OBSOLETE” from RMAN prompt:
  1. The physical backup files are removed from the filesystem level (or from tape backup)
  2. The backup entries are removed from the RMAN recovery catalog
  3. The entries are marked as DELETED in the Oracle control file

2. View Backups Before Delete Obsolete

In the following example, I see that there are lot of backup that I have on the system that are way older than what I need to satisfy my recovery requirement. i.e I have backups starting from 6th June until 26th Sep (several months).
RMAN> LIST BACKUP SUMMARY;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
624     B  F  A DISK        06-JUN-14       1       1       YES        TAG20140606T220138
625     B  F  A DISK        06-JUN-14       1       1       YES        TAG20140606T220138
626     B  F  A DISK        06-JUN-14       1       1       NO         TAG20140606T221610
627     B  F  A DISK        13-JUN-14       1       1       YES        TAG20140613T220147
..
..
666     B  F  A DISK        05-SEP-14       1       1       YES        TAG20140905T220151
667     B  F  A DISK        12-SEP-14       1       1       YES        TAG20140912T220151
668     B  F  A DISK        19-SEP-14       1       1       YES        TAG20140919T220152
669     B  F  A DISK        26-SEP-14       1       1       YES        TAG20140926T220206

3. Perform RMAN CrossCheck

Before we start executing the delete obsolete command, it is always recommended to do a crosscheck of the backup as shown below.
crosscheck backup command will check for the records in the RMAN repository to make sure they are accurate. If there is an record in the RMAN catalog that is not available on the physical filesystem, it will make that entry with appropriate status.
RMAN> CROSSCHECK BACKUP;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/rman-backups/dev-db/full_okpa6ke3_788_1 RECID=642 STAMP=849564099
crosschecked backup piece: found to be 'AVAILABLE'
..
..
backup piece handle=/rman-backups/dev-db/full_qvpiu46h_863_1 RECID=686 STAMP=858722513
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/rman-backups/dev-db/full_r4pjgir0_868_1 RECID=687 STAMP=859327328
Crosschecked 46 objects

4. Delete Obsolete RMAN backup

Once the crosscheck is done, it is time to delete the old obsolete backup using the DELETE OBSOLETE command as shown below.
RMAN> DELETE OBSOLETE;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 4 days
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           625    06-JUN-14
  Backup Piece       643    06-JUN-14          /rman-backups/dev-db/full_ojpa6ke3_787_1
Backup Set           624    06-JUN-14
  Backup Piece       642    06-JUN-14          /rman-backups/dev-db/full_okpa6ke3_788_1
..
..
Backup Set           664    05-SEP-14
  Backup Piece       682    05-SEP-14          /rman-backups/dev-db/full_qjphp6ug_851_1
Backup Set           667    12-SEP-14
  Backup Piece       685    12-SEP-14          /rman-backups/dev-db/full_qqpiblig_858_1

Do you really want to delete the above objects (enter YES or NO)? YES
Once you say “YES” to the above confirmation, then it will start deleting those obsolete backups.
backup piece handle=/rman-backups/dev-db/full_ojpa6ke3_787_1 RECID=643 STAMP=849564099
deleted backup piece
backup piece handle=/rman-backups/dev-db/full_okpa6ke3_788_1 RECID=642 STAMP=849564099
deleted backup piece
..
..
backup piece handle=/rman-backups/dev-db/full_qjphp6ug_851_1 RECID=682 STAMP=857512912
deleted backup piece
backup piece handle=/rman-backups/dev-db/full_qqpiblig_858_1 RECID=685 STAMP=858117712
Deleted 40 objects
WARNING: This will also physically delete the RMAN backup files from the filesystem. So, be careful and know exactly what you are doing before you execute this command.

5. Other Delete Obsolete Options

If you are writing a shell script that will automatically do this for you on an on-going basis, you don’t want to manually say “YES” to delete obsolete command as shown above.
Instead, you can ignore the prompt and automatically delete all obsolete backups as shown below.
RMAN> DELETE NOPROMPT OBSOLETE;
Also, if you want to delete obsolete backup based on your own recovery window criteria (instead of what is configured in RMAN when you do “show all”), you can specify it as shown below. The following will delete old backups based on recovery window of 10 days.
RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 10 DAYS;

6. View Backups After Delete Obsolete

Finally, if you do the list backup summary, you’ll notice that this has only the backups that are required to satisfy the recovery criteria. All other backups are deleted.
RMAN> LIST BACKUP SUMMARY;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
661     B  F  A DISK        29-AUG-14       1       1       YES        TAG20140829T220152
663     B  F  A DISK        29-AUG-14       1       1       NO         TAG20140829T221624
665     B  F  A DISK        05-SEP-14       1       1       YES        TAG20140905T220151
666     B  F  A DISK        05-SEP-14       1       1       YES        TAG20140905T220151
668     B  F  A DISK        19-SEP-14       1       1       YES        TAG20140919T220152
669     B  F  A DISK        26-SEP-14       1       1       YES        TAG20140926T220206

II. Delete Expired Backup

1. What is an Expired Backup?

When you have an entry in the RMAN repository for a backup, but there are no corresponding physical rman backup files at the filesystem level, that is considered as expired entry.
But, you need to execute the crosscheck command, which will go through all the records in the RMAN catalog, and mark any expired records appropriately.

2. Perform RMAN CrossCheck

Sometimes when you try to execute the delete command, you might get the following “RMAN-06207” error message, when there is a mis-match of the status for the records in the RMAN repository.
RMAN-06207: WARNING: 20036 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /rman-backup/sales-db/full_52ob26cd_149666_1
RMAN-06214: Backup Piece    /rman-backup/sales-db/full_50ob26cd_149664_1
As you see from the following output, crosscheck backup finds few records that are expired, and marking them as EXPIRED status in the catalog.
RMAN> CROSSCHECK BACKUP;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=453 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=435 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/rman-backup/sales-db/full_4cpqjjca_196748_1 recid=217396 stamp=866766218
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/rman-backup/sales-db/full_4epqjjca_196750_1 recid=217397 stamp=866766218
..
..
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/rman-backup/sales-db/ctl_c-863661937-20141013-19 recid=211590 stamp=860879752
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/rman-backup/sales-db/ctl_c-863661937-20141013-1a recid=211591 stamp=860879769
Crosschecked 1253 objects

3. View Backups Before Delete Expired

As you see from the output below, there are several records in the RMAN catalog that are marked as EXPIRED.
RMAN> LIST BACKUP SUMMARY;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
728642  B  A  X DISK        29-SEP-14       1       1       YES        DBARCHLOGS_20140929
728643  B  A  X DISK        29-SEP-14       1       1       YES        DBARCHLOGS_20140929
..
..
819598  B  A  A DISK        29-DEC-14       1       1       YES        DBARCHLOGS_20141229
819607  B  F  A DISK        29-DEC-14       1       1       NO         TAG20141229T161328
Note: The 4th column which has the title of “S” is Status column. The value “X” indicates EXPIRED status. The value “A” indicates AVAILABLE status.

4. Delete Expired RMAN Catalog Entries

The following will delete all the records that are in RMAN catalog which are marked as EXPIRED. In other words, this deletes the expired records that don’t have the corresponding physical RMAN backup file in the filesystem.
RMAN> DELETE EXPIRED BACKUP;

using channel ORA_DISK_1
using channel ORA_DISK_2
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
728642  728712  1   1   EXPIRED     DISK        /rman-backup/sales-db/full_52ob26cd_149666_1
728643  728713  1   1   EXPIRED     DISK        /rman-backup/sales-db/full_50ob26cd_149664_1
..
..
Do you really want to delete the above objects (enter YES or NO)?
As shown above, before deleting it will ask for a confirmation. Say “YES” to the above, which will delete those files as shown below.
backup piece handle=/rman-backup/sales-db/full_52ob26cd_149666_1 RECID=591 STAMP=840751343
deleted backup piece
backup piece handle=/rman-backup/sales-db/full_50ob26cd_149664_1 RECID=592 STAMP=840752380
deleted backup piece
..
Deleted 107 EXPIRED objects

5. View Backups After Delete Expired

After deleting the expired entries, view the catalog to make sure it contains only the active available RMAN backup records.
RMAN> LIST BACKUP SUMMARY;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
809652  B  F  A DISK        20-DEC-14       1       1       YES        DBFULLFILE_20141220
809653  B  F  A DISK        20-DEC-14       1       1       YES        DBFULLFILE_20141220
809654  B  F  A DISK        20-DEC-14       1       1       YES        DBFULLFILE_20141220
809655  B  F  A DISK        20-DEC-14       1       1       YES        DBFULLFILE_20141220
809656  B  F  A DISK        20-DEC-14       1       1       YES        DBFULLFILE_20141220
..
..
819598  B  A  A DISK        29-DEC-14       1       1       YES        DBARCHLOGS_20141229
819607  B  F  A DISK        29-DEC-14       1       1       NO         TAG20141229T161328
As you see from the Status column (4th column), we see only values “A”, which indicates that all the records in the RMAN catalog are in AVAILABLE status now.

Thank's  to Mr Ramesh Natarajan

http://www.thegeekstuff.com/2015/01/delete-oracle-rman-backup/?utm_source=feedburner&utm_medium=email&utm_campaign=Feed%3A+TheGeekStuff+(The+Geek+Stuff)

Sunday 19 June 2016

Oracle DBA Activity.

Daily Activity
  1. Oracle Database instance is running or not
  2. Database Listener is running or not.
  3. Check any session blocking the other session
  4. Check the alert log for an error
  5. Check is there any dbms jobs running & check the status of the same
  6. Check the Top session using more Physical I/O
  7. Check the number of log switch per hour
  8. How_much_redo_generated_per_hour.sql
  9. Run the statpack report
  10. Detect lock objects
  11. Check the SQL query consuming lot of resources.
  12. Check the usage of SGA
  13. Display database sessions using rollback segments
  14. State of all the DB Block Buffer
Weekly Activity
  1. Check the objects fragmented
  2. Check the Chaining & Migrated Rows
  3. Check the size of tables & check weather it need to partition or not
  4. Check for Block corruption
  5. Check the tables without PK
  6. Check the tables having no Indexes
  7. Check the tables having more Indexes
  8. Check the tables having FK but there is no Index
  9. Check the objects having the more extents
  10. Check the frequently pin objects & place them in separate tablespace & in cache
  11. Check the objects reload in memory many time
  12. Check the free space at O/s Level
  13. Check the CPU, Memory usage at O/s level define the threshold for the same.
  14. Check the used & free Block at object level as well as on tablespaces.
  15. Check the objects reaching to it’s Max extents
  16. Check free Space in the tablespace
  17. Check invalid objects of the database
  18. Check open cursor not reaching to the max limit
  19. Check locks not reaching to the max lock
  20. Check free quota limited available of each user
  21. Check I/O of each data file
Monthly Activity
  1. Check the database size & compare it previous size to find the exact growth of the database
  2. Find Tablespace Status, segment management, initial & Max Extents and Extent Management
  3. Check location of data file also check auto extendable or not
  4. Check default tablespace & temporary tablespace of each user
  5. Check the Indexes which is not used yet
  6. Check the Extents of each object and compare if any object extent are overridden which is define at tablespace level
  7. Tablespace need coalescing
  8. Check the overall database statistics
  9. Trend Analysis of objects with tablespace, last analyzed, no. of Rows, Growth in days & growth in KB
Nightly Activity
  1. Analyzed the objects routinely.
  2. Check the Index need to Rebuild
  3. Check the tablespace for respective Tables & Indexes
One Time Activity
  1. Database user creation with required privileges
  2. Make the portal of Oracle Predefined error with possible solution.
  3. Check database startup time(if not 24X7)
  4. Check location of control file
  5. Check location of log file
  6. Prepare the Backup strategy and test all the recovery scenario
If we together build a comprehensive list of Oracle DBA daily/weekly/monthly routine tasks, then many Oracle new DBAs like me will benefit from it.

Shared Pool Information For Tuning

Here are some scripts related to Shared Pool Information .

Quick Check

SHARED POOL QUICK CHECK NOTES:
select 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,
       'Request Failures = '||REQUEST_FAILURES  Logic
from  v$shared_pool_reserved
where  REQUEST_FAILURES > 0
and  0 != (
 select  to_number(VALUE) 
        from  v$parameter 
        where  NAME = 'shared_pool_reserved_size')
union
select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,
       'Request Failures = '||REQUEST_FAILURES Logic
from  v$shared_pool_reserved
where  REQUEST_FAILURES < 5
and  0 != ( 
 select  to_number(VALUE) 
 from  v$parameter 
 where  NAME = 'shared_pool_reserved_size')

Memory Usage

SHARED POOL MEMORY USAGE NOTES:
  • Owner - Owner of the object
  • Object - Name/namespace of the object
  • Sharable Memory - Amount of sharable memory in the shared pool consumed by the object
    select  OWNER,
     NAME||' - '||TYPE object,
     SHARABLE_MEM
    from  v$db_object_cache
    where  SHARABLE_MEM > 10000 
    and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
    order  by SHARABLE_MEM desc
    
    

    Loads

    LOADS INTO SHARED POOL NOTES:
  • Owner - Owner of the object
  • Object - Name/namespace of the object
  • Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.
    select  OWNER,
     NAME||' - '||TYPE object,
     LOADS
    from  v$db_object_cache
    where  LOADS > 3 
    and  type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
    order  by LOADS desc
    
    

    Executions

    SHARED POOL EXECUTION NOTES:
  • Owner - Owner of the object
  • Object - Name/namespace of the object
  • Executions - Total number of times this object has been executed
    select  OWNER,
     NAME||' - '||TYPE object,
     EXECUTIONS
    from  v$db_object_cache
    where  EXECUTIONS > 100 
    and  type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
    order  by EXECUTIONS desc
    
    

    Details

    SHARED POOL DETAIL NOTES:
  • Owner - Owner of the object
  • Name - Name of the object
  • DB Link - Database link name, if any
  • Namespace - Namespace of the object
  • Type - Type of the object
  • Sharable Memory - Amount of sharable memory in the shared pool consumed by the object
  • Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.
  • Executions - Total number of times this object has been executed
  • Locks - Number of users currently locking this object
  • Pins - Number of users currently pinning this object
    select OWNER,
     NAME,
     DB_LINK,
     NAMESPACE,
     TYPE,
            SHARABLE_MEM,
            LOADS,
            EXECUTIONS,
            LOCKS,
            PINS
    from  v$db_object_cache
    order  by OWNER, NAME
    
    

    Library Cache Statistics

    SHARED POOL V$LIBRARYCACHE STATISTIC NOTES:
  • Namespace - Library cache namespace (SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT, PIPE)
  • Gets - Number of times the system requests handles to library objects belonging to this namespace
  • GetHits - Number of times the handles are already allocated in the cache. If the handle is not already allocated, it is a miss. The handle is then allocated and inserted into the cache.
  • GetHit Ratio - Number of GETHITS divided by GETS. Values close to 1 indicate that most of the handles the system has tried to get are cached.
  • Pins - Number of times the system issues pin requests for objects in the cache in order to access them.
  • PinHits - Number of times that objects the system is pinning and accessing are already allocated and initialized in the cache. Otherwise, it is a miss, and the system has to allocate it in the cache and initialize it with data queried from the database or generate the data.
  • PinHit Ratio - Number of PINHITS divided by number of PINS. Values close to 1 indicate that most of the objects the system has tried to pin and access have been cached.
  • Reloads - Number of times that library objects have to be reinitialized and reloaded with data because they have been aged out or invalidated.
  • Invalidations - Number of times that non-persistent library objects (like shared SQL areas) have been invalidated.
  • GetHit Ratio and PinHit Ratio should be > 70
    select  NAMESPACE,
     GETS,
     GETHITS,
     round(GETHITRATIO*100,2) gethit_ratio,
     PINS,
     PINHITS,
     round(PINHITRATIO*100,2) pinhit_ratio,
     RELOADS,
     INVALIDATIONS
    from  v$librarycache
    
    

    Reserve Pool Settings

    SHARED POOL RESERVED SIZE NOTES:
  • Parameter - Name of the parameter
  • Value - Current value for the parameter
  • shared_pool_reserved_size - Controls the amount of SHARED_POOL_SIZE reserved for large allocations. The fixed view V$SHARED_POOL_RESERVED helps you tune these parameters. Begin this tuning only after performing all other shared pool tuning on the system.
  • shared_pool_reserved_min_alloc - Controls allocation for the reserved memory. To create a reserved list, SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC. Only allocations larger than SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool's free lists. The default value of SHARED_POOL_RESERVED_MIN_ALLOC should be adequate for most systems.
    select  NAME,
     VALUE
    from  v$parameter
    where  NAME like '%reser%'
    
    

    Pinned Objects

    PINNED OBJECT NOTES:
  • Object Name - Name of the object
  • Object Type - Type of the object (INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK)
  • Kept Status - YES or NO, depending on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP
    select  NAME,
     TYPE,
     KEPT
    from  v$db_object_cache
    where  KEPT = 'YES'
    

  • Friday 29 November 2013

    RMAN Backup Completion Check

    Let’s assume, we are doing a backup of a large database. At a given time, if we need to check, how much backup is finished & how much is left, so that we can have some estimates.
    Following query can be used to check the RMAN backup status
    If multiple channels are allocated for the backup, then this query will return multiple records. Each record will be indicating the status of individual channel

    Oracle Dba Real Time Interview Question's And Answer's



    1.       Which types of backups you can take in Oracle?

    2.       A database is running in NOARCHIVELOG mode then which type of backups you can take?

    3.       Can you take partial backups if the Database is running in NOARCHIVELOG mode?

    4.       Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

    5.       How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

    6.       You cannot shutdown the database for even some minutes, then in which mode you should run
    the database?

    7.       Where should you place Archive logfiles, in the same disk where DB is or another disk?

    8.       Can you take online backup of a Control file if yes, how?

    9.       What is a Logical Backup?

    10.   Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
    11.   Why do you take tablespaces in Backup mode?

    12.   What is the advantage of RMAN utility?

    13.   How RMAN improves backup time?

    14.   Can you take Offline backups using RMAN?

    15.   How do you see information about backups in RMAN?

    16.   What is a Recovery Catalog?

    17.   Should you place Recovery Catalog in the Same DB?

    18.   Can you use RMAN without Recovery catalog?

    19.   Can you take Image Backups using RMAN?

    20.   Can you use Backupsets created by RMAN with any other utility?

    21.   Where RMAN keeps information of backups if you are using RMAN without Catalog?

    22.   You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

    23.   You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

    24.   Which is more efficient Incremental Backups using RMAN or Incremental Export?

    25.   Can you start and shutdown DB using RMAN?

    26.   How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

    27.   You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

    28.   You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

    29.   How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

    30.   You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?

    31.   You loss controlfile how do you recover from this?

    32.    The current logfile gets damaged. What you can do now?

    33.   What is a Complete Recovery?

    34.   What is Cancel Based, Time based and Change Based Recovery?

    35.   Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

    36.   Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?

    37.   A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?

    38.   How do you recover from the loss of a controlfile if you have backup of controlfile?

    39.   Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?

    40.   Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?


    41.   Have you faced any emergency situation. Tell us how you resolved it?

    42.   At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.

    43.   What is Consistent Backup?
    A Consistent backup is one in which the files being backed up contain all changes upto the same system change number (SCN)

    44.   What is fractured Block?
    Because the database continues writing to the file during an online backup, there is the possibility of backing up inconsistent data within a block. For example, assume that either RMAN or an operating system utility reads the block while database writer is in the middle of updating the block. In this case, RMAN or the copy utility could read the old data in the top half of the block and the new data in the bottom top half of the block. The block is a fractured block, meaning that the data in this block is not consistent.
    45.   What are the steps to performing complete recovery on the whole database?
    Mount the database
    Ensure that all datafiles you want to recover are online
    Restore a backup of the whole database or the files you want to recover
    Apply online or archived redo logs, or a combination of the two

    46.   What are the steps to performing complete recovery on a tablespace or datafile?
    Take the tablespace or datafile to be recovered offline if the database is open
    Restore a backup of the datafiles you want to recover
    Apply online or archived redo logs, or a combination of the two



    47.   What are the components of physical database structure of Oracle database?
    Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

    48.   What are the components of logical database structure of Oracle database?
    There are tablespaces and database's schema objects.

    49.   What is a tablespace?
    A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

    50.   What is SYSTEM tablespace and when is it created?
    Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

    51.   Explain the relationship among database, tablespace and data file.
    Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

    52.   What is schema?
    A schema is collection of database objects of a user.

    53.   What are Schema Objects?
    Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

    54.   Can objects of the same schema reside in different tablespaces?
    Yes.

    55.   Can a tablespace hold objects from different schemes?
    Yes.

    56.   What is Oracle table?
    A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

    57.   What is an Oracle view?
    A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

    58.   Do a view contain data?
    Views do not contain or store data.

    59.   Can a view based on another view?
    Yes.

    60.   What are the advantages of views?
    - Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
    - Hide data complexity.
    - Simplify commands for the user.
    - Present the data in a different perspective from that of the base table.
    - Store complex queries.

    61.   What is an Oracle sequence?
    A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

    62.   What is a synonym?
    A synonym is an alias for a table, view, sequence or program unit.

    63.   What are the types of synonyms?
    There are two types of synonyms private and public.

    64.   What is a private synonym?
    Only its owner can access a private synonym.

    65.   What is a public synonym?

    66.   Any database user can access a public synonym.


    67.   What are synonyms used for?
    - Mask the real name and owner of an object. - Provide public access to an object - Provide location transparency for tables, views or program units of a remote database. - Simplify the SQL statements for database users.

    68.   What is an Oracle index?
    An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

    69.   How are the index updates?
    Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

    70.   What are clusters?
    Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

    71.   What is cluster key?
    The related columns of the tables in a cluster are called the cluster key.

    72.   What is index cluster?
    A cluster with an index on the cluster key.

    73.   What is hash cluster?
    A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

    74.   When can hash cluster used?
    Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

    75.   What is database link?
    A database link is a named object that describes a "path" from one database to another.

    76.   What are the types of database links?
    Private database link, public database link & network database link.

    77.   What is private database link?
    Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

    78.   What is public database link?
    Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

    79.   What is network database link?
    Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

    80.   What is data block?
    Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

    81.   How to define data block size?
    A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

    82.   What is row chaining?
    In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

    83.   What is an extent?
    An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

    84.   What is a segment?
    A segment is a set of extents allocated for a certain logical structure.

    85.   What are the different types of segments?
    Data segment, index segment, rollback segment and temporary segment.

    86.   What is a data segment?
    Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

    87.   What is an index segment?
    Each index has an index segment that stores all of its data.

    88.   What is rollback segment?
    A database contains one or more rollback segments to temporarily store "undo" information.



    89.   What are the uses of rollback segment?
    To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

    90.   What is a temporary segment?
    Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

    91.   What is a datafile?
    Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tablesand indexes is physically stored in the data files allocated for a database.

    92.   What are the characteristics of data files?
    A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

    93.   What is a redo log?
    The set of redo log files for a database is collectively known as the database redo log.

    94.   What is the function of redo log?
    The primary function of the redo log is to record all changes made to data.

    95.   What is the use of redo log information?
    The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

    96.   What does a control file contains?
    - Database name - Names and locations of a database's files and redolog files. - Time stamp of database creation.

    97.   What is the use of control file?
    When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery