How to restore the control files
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to restore the control files

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Posts
    141

    How to restore the control files

    Hi!
    I am practicing some recovery scenarios in oracle 9i on WIN 2000

    I am restoring the datafiles from tape drive backup to different disk,and renaming the datafile

    >alter datatbase rename file d:\a to e:\a

    and to change the datafiles location the control file

    >alter database backup controlfile to trace;

    using the latest trace file from the user_dump destination ...i am not able to recreate the control files

    The content of the control file:
    --------------------------------

    STARTUP NOMOUNT;
    CREATE CONTROLFILE REUSE DATABASE "oracle_db" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 113
    LOGFILE
    GROUP 1 'C:\ORACLE\ORADATA\oracle_db\REDO01.LOG' SIZE 100M,
    GROUP 2 'C:\ORACLE\ORADATA\oracle_db\REDO02.LOG' SIZE 100M,
    GROUP 3 'C:\ORACLE\ORADATA\oracle_db\REDO03.LOG' SIZE 100M
    # STANDBY LOGFILE
    DATAFILE
    'C:\ORACLE\ORADATA\oracle_db\SYSTEM01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\UNDOTBS01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\CWMLITE01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\DRSYS01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\EXAMPLE01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\INDX01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\TOOLS01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\USERS01.DBF'
    CHARACTER SET WE8MSWIN1252
    ;
    RECOVER DATABASE
    ALTER DATABASE OPEN;
    ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\oracle_db\TEMP01.DBF' REUSE;
    When i try to run this i am getting error!

    > @C:\oracle\admin\oracle_db\udump\ORA01420.TRC

    ERROR:

    SQL> @C:\oracle\admin\oracle_db\udump\ORA01420.TRC
    ORA-01078: failure in processing system parameters
    LRM-00111: no closing quote for value 'c:'
    '
    SP2-0734: unknown command beginning "STANDBY LO..." - rest of line ignored.
    CREATE CONTROLFILE REUSE DATABASE "ORACLE_DB" NORESETLOGS NOARCHIVELOG
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel


    SP2-0734: unknown command beginning "Recovery i..." - rest of line ignored.
    SP2-0734: unknown command beginning "or if the ..." - rest of line ignored.
    ORA-03114: not connected to ORACLE


    SP2-0734: unknown command beginning "Database c..." - rest of line ignored.
    ALTER DATABASE OPEN
    *
    ERROR at line 1:
    ORA-03114: not connected to ORACLE


    SP2-0734: unknown command beginning "Files in n..." - rest of line ignored.
    SP2-0734: unknown command beginning "TO 'C:\ORA..." - rest of line ignored.
    SP2-0734: unknown command beginning "Commands t..." - rest of line ignored.
    SP2-0734: unknown command beginning "Online tem..." - rest of line ignored.
    SP2-0734: unknown command beginning "Other temp..." - rest of line ignored.
    SP2-0044: For a list of known commands enter HELP
    and to leave enter EXIT.
    ALTER DATABASE RENAME FILE 'MISSING00009'
    *
    ERROR at line 1:
    ORA-03114: not connected to ORACLE


    Can anyone help me ?


    Many Thanks,
    Last edited by castlerock; 11-11-2003 at 12:53 AM.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\oracle_db\TEMP01.DBF' REUSE;
    When i try to run this i am getting error!

    > @C:\oracle\admin\oracle_db\udump\ORA01420.TRC

    ERROR:

    SQL> @C:\oracle\admin\oracle_db\udump\ORA01420.TRC
    ORA-01078: failure in processing system parameters
    LRM-00111: no closing quote for value 'c:'


    Doesnt't that pretty much tell you what the problem is?

  3. #3
    Join Date
    Jan 2001
    Posts
    191
    It is unclear what the sequence in your step.

    First, after you restored database from tape to disk, database is down at that time, how can you rename datafile using alter datatbase rename file d:\a to e:\a and alter database backup controlfile to trace;

    # is not a common in sqlplus. # is a common in server manager which is desupported in oracle 9i. You have to delete the line start with # .


    If you want to recreate control file, database have to be in nomount mode. You should shutdown database cleanly ( shutdown )first and run

    STARTUP NOMOUNT;

    feedback should be:

    ORACLE instance started.


    Nake sure oracle instance is up.
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel

    your instnace may not running right.

    After database is in nomount mode, you can run:

    CREATE CONTROLFILE REUSE DATABASE "oracle_db" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 113
    LOGFILE
    GROUP 1 'C:\ORACLE\ORADATA\oracle_db\REDO01.LOG' SIZE 100M,
    GROUP 2 'C:\ORACLE\ORADATA\oracle_db\REDO02.LOG' SIZE 100M,
    GROUP 3 'C:\ORACLE\ORADATA\oracle_db\REDO03.LOG' SIZE 100M
    DATAFILE
    'C:\ORACLE\ORADATA\oracle_db\SYSTEM01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\UNDOTBS01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\CWMLITE01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\DRSYS01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\EXAMPLE01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\INDX01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\TOOLS01.DBF',
    'C:\ORACLE\ORADATA\oracle_db\USERS01.DBF'
    CHARACTER SET WE8MSWIN1252
    ;

    You should see feedback :
    Control file created.

    Then you can run
    SQL> RECOVER DATABASE

    If you only shutdown your database cleanly and run startup nomount and create controlfile script, then there is no need to recover database. you will get following error:

    ORA-00283: recovery session canceled due to errors
    ORA-00264: no recovery required

    You can ignore this and go to next step.

    ALTER DATABASE OPEN;

    You should get feedback:

    Database altered.

    In your case, you get
    ALTER DATABASE OPEN
    *
    ERROR at line 1:
    ORA-03114: not connected to ORACLE

    This means your oracle instance is not up at all.(startup nomount failed. Reason: I think you did not shutdwon your database first.)


    Then you can run

    ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\oracle_db\TEMP01.DBF' REUSE;


    ...................


    I think you should run this step by step. Do not run it in one script.

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