data file need media recovery
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: data file need media recovery

Hybrid View

  1. #1
    Join Date
    May 2007
    Posts
    4

    data file need media recovery

    Hello All,
    I am new to this field so please be patient. I have taken the backup of all datafiles, redologs, control file, spfile for my database.
    After taking the backup i shutdown the database and change the location of all original datafiles, redolog files , control files which is asked by my team lead.
    Now when i try to bring the database up its giving me this error:

    ORA-01113: file 1 needs media recovery
    ORA-01110: datafile 1:'D:\ORACLE\ORADATA\SONA\SYSTEM01.dbf'

    Here the problem may occur because i forgot to execute command for switching log before taking backup.

    Can any help me out!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you need to provide full details of what you did, how you took your backup, what you did to get it in that state, how you moved files.

    everything

  3. #3
    Join Date
    May 2007
    Posts
    4
    I took hot backup of all datafiles and control files and redolog files i used simple OS cp command.
    Then i asked for downtime and shutdown the database.
    After that my lead asked me to change the location of original datafiles,control files and redo log files so i did that.
    But i did not swich log for taking backup of datafiles, may be thats the problem.
    now when i try to recover the database its giving me this error.

  4. #4
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by ghotu
    I took hot backup of all datafiles and control files and redolog files i used simple OS cp command..
    That is not the correct way to take a "hot backup" read the concepts and admin manualls.
    Also probably would be best if you used a tool like rman.

    Quote Originally Posted by ghotu
    Then i asked for downtime and shutdown the database.
    After that my lead asked me to change the location of original datafiles,control files and redo log files so i did that.
    How did you change the location? What commands did you use exactly?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    If you did it right you don't even need to restore your backup.
    As Dave stated, more details are needed.
    Tell us exactly how you changed the location of datafiles, etc.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and how you did this 'hot backup'

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by davey23uk
    and how you did this 'hot backup'
    man... I was too scared to ask that question, just hope he doesn't need it after all lol
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    May 2007
    Posts
    4
    I took the hot backup by traditional way means by putting tablespaces in hot backup mode by
    alter tablespace begin backup;
    then i copy all the datafiles associated with this tablespace to desired location.i repeated same procedures for all tablespaces.
    i believe this is the only way to take hot backup...
    For redologs, control files i simply took copy of these files.
    after that i shutdown the database
    then i moved the original datafiles, control files and redolog files to a location which is asked by my lead coz they wanted to change the location for all these files.
    and that is the reason i took backup of those datafiles, control files and redolog. so that if i wont be able to startup the database i can restore it in old condition.
    now after changing the location i am not able to startup the database and i want to restore it.
    when i am trying to restore database by using my backup i am getting this error..
    i hope this time i explain my problem clearly

  9. #9
    Join Date
    Jun 2006
    Posts
    259
    You should not need to go to the backup since you shutdown the DB prior to moving your datafiles and redo logs.

    You do however need to re-create your control files. And point the pfile/spfile to the new control files.

    A suggestion for next time to assist you in re-creating control files.
    "alter database backup control file to trace"

    You have 2 choices:
    1. a. copy everything to its original location. (if you have capacity)
    b. Run alter database backup controlfile to trace...
    c. Edit the .trc file created, with the location of the the new files.
    d. edit the pfile/spfile setting up the control file location.
    e. Run the create control file command.
    or
    2. a. Build a create controlfile command by hand.
    b. edit the pfile/spfile setting up the new control file locatoin
    c. run the create control file script.

    Actually a third is asking for help from you lead dba.

    ====== SAMPLE edit parameters per your system =================
    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 50
    MAXINSTANCES 2
    MAXLOGHISTORY 1168
    LOGFILE
    GROUP 1 '/dbs/hera6410/redo_1a.dbf' SIZE 32M,
    GROUP 2 '/dbs/hera6410/redo_2a.dbf' SIZE 32M,
    GROUP 3 '/dbs/hera6410/redo_3a.dbf' SIZE 32M
    -- STANDBY LOGFILE
    DATAFILE
    '/dbs/system_std_tbl_01.dbf',
    '/dbs/und_std_tbl_01.dbf',
    '/dbs/system_aux_01.dbf',
    CHARACTER SET US7ASCII;

    -- Recovery is required if any of the datafiles are restored backups,
    -- or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    -- All logs need archiving and a log switch is needed.
    ALTER SYSTEM ARCHIVE LOG ALL;

    -- Database can now be opened normally.
    ALTER DATABASE OPEN;

    -- Commands to add tempfiles to temporary tablespaces.
    -- Online tempfiles have complete space information.
    -- Other tempfiles may require adjustment.

    ALTER TABLESPACE TEMP_STD_TBL ADD TEMPFILE '/dbs/temp_std_tbl_01.dbf'
    SIZE 62914560 REUSE AUTOEXTEND OFF;

  10. #10
    Join Date
    May 2007
    Posts
    4
    my database has come up and running fine. Thank you very much for your help ixion

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width