1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Homework Help - 5 Question please

Discussion in 'Security, Backup and Recovery' started by mef, Oct 8, 2009.

  1. mef

    mef Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Question I
    Edward uses an Oracle Database to maintain the inventory of his grocery store. Once every week, he runs a batch job to insert, update and delete data in his database. He uses a Windows machine running Oracle 9i R2. Edward starts the database up in the morning at 8 A.M., and shuts it down at 5 P.M., and operates the database all day in NOARCHIVELOG mode. He takes an offline backup (cold backup) of the database once a week, or every Sunday by copying all the datafiles, log files and control files to tape.

    I.1. On a Wednesday morning, Edward realized that he had lost a datafile that contained all the user data. He tried to start up the database using the STARTUP OPEN command got the following error
    ORA-01157 :cannot identify datafile 4 – file not found
    ORA-01110: datafile 4 :’………………………/users01.dbf’
    He realized that he had accidentally deleted one of the datafiles while trying to free some space on the disk.
    a) How would he resolve this problem ?
    b) How much data would you lose ?
    c) Write down the steps you would perform to recover your database

    I.2. Same as I.1 but the data file belonged to a temporary tablespace.
    ORA-01157 :cannot identify data file 6 – file not found
    ORA-01110 :datafile 6 : ‘…………………………./temp.dbf’
    a) How would he resolve this problem ?
    b) How much data would you lose ?
    c) Write down the steps you would perform to recover your database

    Question II
    Smith is the DBA of a real-time call-tracking system. He uses Oracle 9i R2 on a Windows machine and takes an online backup of the database every night. The total database size is 50GB and the real-time call tracking system is a heavy OLTP system primarily with the maximum activity between 9 a.m and 9 p.m., everyday. At 9 p.m. a batch job runs a command procedure that puts the tablespaces in hot backup mode, takes the backup of all the datafiles to tape at the operating system level, and then issues the alter tablespace end backup command.

    II.1. One afternoon a disk crashed, losing the SYSTEM tablespace residing on the disk. As this happened at the peak processing time, Smith had to keep the down time to a minimum and open the database as soon as possible. He wanted to start the database first and then restore the datafile that was lost, so he took the SYSTEM data file offline. When he tried to open the database he got the following error:
    ORA-01147 : SYSTEM tablespace file 1 is offline
    Write down the steps you would perform to recover your database

    II.2. Let’s assume that instead of a system data file, a non-system data file is lost due to the disk crash (datafile number 4, datafile name users01.dbf)
    Write down the steps you would perform to recover your database

    Question III
    Rama is a DBA in a banking firm. She administers an Oracle 9i R2 database on a Windows Server. She stores all the user data in the USERS tablespace, index data in the INDEXES tablespace, and undo data in the UNDOTBS tablespace. In addition, she has other tablespaces to store data for various banking applications. Since the database operates 24*7, she has an automated procedure to take online backups every night. In addition, she takes an export once a month of all the important tables in the database.
    On Monday morning, due to a media failure, all the data files that belong to the tablespace UNDOTBS was lost. It was the beginning of the week and a lot of applications needed to be run against the database, so she decided to do an online recovery. Once she took the datafile offline and opened the database, she tried to select from a user table and got the following error:
    ORA-00376 : file 2 cannot be read at this time
    (File 2 happens to be one of the datafiles that belong to the tablespace UNDOTBS)
    How would you perform a recovery ? write down the required instructions

    Question IV
    Anna works in a software company as a DBA to administer a small development database on a Windows server. She created a 500MB database.
    She decided to mirror the control files but not the online redo logs, so created the database with three log groups with one member each. Her backup strategy includes taking online backups twice a week and a full database export once a week.
    A power surge caused the database to crash and also caused a media failure, losing all the online log files. All the data files and the current control files are intact.
    How would you perform a recovery ? write down the required instructions

    Question V
    In this problem scenario, our DBA has mistakenly dropped a production tablespace. Our mission is to recover this dropped tablespace to the most recent commit possible, where
     Daily backups are available.
     RMAN catalog is available.
     DBA accidentally dropped a production tablespace
    o Drop tablespace tools;
    Your mission is to recover the database to just before the DROP, using RMAN
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where are your attempts to answer these questions? We will not do your homework for you (as it appears you are asking us to do) but we will assist you by evaluating your responses.
     
  3. mef

    mef Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    ok thanks Mr.zargon

    here you are some attempts
    for Question number 1
    I.1 1- the backup have done in Sunday.
    2- the problem have done in Wednesday morning.
    3- the database operate all day in NOARCHIVELOG mode.
    a) By coping the file users01.dbf from backup to it's path in hard disk
    b) Since the database is in NOARCHIVELOG mode, we can not recover committed data since the last backup, So
    we would loss all the data witch have entered in Monday and Tuesday
    c) 1- Connect to the database as sys user
    2- shutdown the database if it is in nomount or mount
    Mode
    3-copy the all oracle files (data, redo logs and control files) to it's path in hard disk
    4- startup
    I.2 since temporary tablespace is used in sort operation and so on, so it can be re-created , rather than recovered.
    a) re-create the temporary tablespace.
    b)no data will be lost.
    c) 1- conn sys/change_on_install as sysdba;
    2-startup mount;
    3-CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE 'D:\ORACLE\ORADATA\AUTOID\TEMP1.ora' SIZE 40M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    4-ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1"
    5-Drop TableSpace Temp;


    thanks zargon for your replay and your kindy

    if there is any correction or sulostion for any question put in here please.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your steps ijn c) are correct but do not match what you stated in b). You cannot restore pieces of a cold backup, it must be restored in its entirety.

    You're doing too much work for this; the TEMP tablespace exists, so simply add the missing tempfile to it:

    alter tablespace temp add tempfile '...../temp.dbf' size .... reuse;

    There is no need to add another TEMP tablespace just to drop the old one.



    You have no responses to the rest of the questions?
     
  5. mef

    mef Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi every body
    i don't know what you mean exactly can you give me more explain

    Q2 : Answer
    II.1- we can't perform recovery to the system table space while the database is open, so we should shutdown the database, then copy the system datafile that belong to the system tablespace, after that we can open the database
    II.2- 1. Shut down the database.
    2. Restore all datafiles from the most recent backup.
    3. we already have a valid backup, so mount the database.
    4. Recover the database until last inactive log seq from
    V$log.
    5. Open the database by using the RESETLOGS option.


    thanks any way
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO


    You stated to restore the users datafile from the cold backup as the required action, but in your description of what to do, exactly, to restore the database you described a full cold backup restore. One cannot restore one datafile from a cold backup; the restore MUST be of the entire backup set.

    Not necessarily; the SYSTEM tablespace was lost due to a disk crash so it is in an inconsistent state relative to the remaining data files. The tablespace MUST be recovered before the database can be opened.

    Why shut down the database? You have an online backup, simply take the non-system tablespace offline, restore the missing/corrupted files, perform a recovery and bring the tablespace back online. A shutdown, full restore and recovery followed by an OPEN RESETLOGS is wrong and completely unnecessary.