Create new DB from Cold bakup of Prod DB!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Create new DB from Cold bakup of Prod DB!

  1. #1
    Join Date
    Feb 2001
    Posts
    286
    Hi all!

    Query:

    If I would like to create a new DB from the cold Backup copies of
    PROD DB of the following files:

    1.CONTROL01.CTL,CONTROL02.CTL,CONTROL03.CTL.
    2.REDO01.LOG,REDO02.LOG,REDO03.LOG.
    3.USERS01.DBF,USERS02.DBF.
    4.TOOLS01.DBF.
    5.SYSTEM01.DBF.
    6.RBS01.DBF.
    7.INDX01.DBF.
    8.TEMP01.DBF.
    9.INITBIAC.ORA.
    10.PWDBIAC.ORA.

    Are the above files enough to create a new DB.

    Do I have to create a new Control Files for the new DB.

    Please let me know the steps in details to create a new DB with the
    help of the above cold Bakup files of BIAC Prod DB.

    Thanks&Regards,

    Amit.

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Were all the backups taken at the same time (in other words, are they synchronised?)
    Are you sure there were no other tablespaces created (except the ones you listed?)
    Then there need not be any problem. You have to restore all the files to the correct directories in the filesystem and open the database (considering oracle is still installed and home directory exists).

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hello

    Please visit this page on steps for cloning a database.

    http://www.geocities.com/samoracle/index.html

    regards
    Hrishy

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello Amit

    You should provide more details why would you need such a setup .Are you planning to restore the backup on the same machine or a different machine etc?

    regards
    Hrishy

  5. #5
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    If it is Cold Backup, it should be consistent...right.

    Then you can make use of the backup and create another DB.

    Keep ur feedback posted on this exercise..
    --------------------------
    The Time has come ....

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    ------------------------------------------
    You should provide more details why would you need such a setup .Are you planning to restore the backup on the same machine or a different machine etc?
    ------------------------------------------

    True, 'cause it's going to be totally a different scenario.

  7. #7
    Join Date
    Feb 2001
    Posts
    286

    Hi hrishy!

    Hi hrishy!

    I am planning to restore on a separate machine!

    But first of all I have to install Oracle server on the separate machine and then what should I do with the default datafiles (including Control Files,redo Log Files) created by installing Oracle.

    Do I simply copy and overwrite them with the cold Bakup copies of Prod DB or first delete them and then restore and start the new DB.

    Do I have to make any changes to the INIT.ORA Parameter File like changing the name of db_name,service_name,
    instance_name,etc.,

    Thanks&Regards,

    Amit.

  8. #8
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    Just install oracle server and don't create database. then those files (datafiles, cpontrlfiles) won't get created.

    While u install oracle, in the end of installation, installer will ask u weather to create database now or laer. just check later and finish the installation.

    Later proceed with what you have.

    --------------------------
    The Time has come ....

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hello Amit

    These instructions assume the following:

    - your database is started
    - you are on the Server
    - the existing database SID is ORCL and DB_NAME is ORACLE
    - the new database SID will be TEST and DB_NAME will be TEST

    For database version 7.2 use sqldba72 and oradim72
    7.3 use svrmgr23 and oradim73
    8.0 use svrmgr30 and oradim80
    8.1 use Svrmgrl and oradim

    This document uses an 8.0 database, but these instructions can easily be
    translated to the another version by changing the utility name.


    ---------------------------------------------------------------
    WARNING:
    Before proceeding, you should take a valid full backup.
    You should also read the entire bulletin before you attempt to
    use it.
    ---------------------------------------------------------------


    1. Back up the controlfile to trace:

    C:\> SET ORACLE_SID=ORCL
    C:\> svrmgr30
    SVRMGR> CONNECT INTERNAL/
    Connected.

    Now, we need to verify the user_dump_destination. This can be done
    in two ways:

    SVRMGR> SHOW PARAMETER USER_DUMP_DEST

    NAME TYPE VALUE
    ---------------- ------- ------------------------------
    user_dump_dest string D:\ORANT\rdbms80\utrace

    ** OR **

    SVRMGR> SELECT value from v$parameter where name = 'user_dump_dest';

    VALUE
    ----------------------------------
    D:\ORANT\rdbms80\utrace


    Create the dump file copy of the controlfile:

    SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    This will create a dump file, ORAxxxxx.TRC, in the user_dump_destination.


    2. Verify which files your database contains:

    SVRMGR> select name from v$datafile;

    NAME
    -----------------------------------------
    D:\DATABASES\ORCL\SYS1ORCL.ORA
    D:\DATABASES\ORCL\RBS1ORCL.ORA
    D:\DATABASES\ORCL\USR1ORCL.ORA
    D:\DATABASES\ORCL\TMP1ORCL.ORA
    D:\DATABASES\ORCL\INDX1ORCL.ORA
    D:\DATABASES\ORCL\OEM204.DBF
    D:\DATABASES\ORCL\USR2ORCL.ORA
    D:\DATABASES\ORCL\TEST.DBS
    D:\DATABASES\ORCL\WEBDB.DBF


    3. SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE the database and EXIT.


    SVRMGR> shutdown immediate


    4. Stop the services:

    Using Control Panel/Services - scroll down to OracleServiceORCL and
    click on the STOP button. You might be asked if you want to stop the
    OracleStartORCL-service too. If so, select yes. Please note that under
    Oracle 8i, only the OracleServiceORCL will exist.

    Or type at the command prompt:

    C:\>net stop OracleServiceORCL

    The following services are dependent on the OracleServiceORCL service.
    Stopping the OracleServiceORCL service will also stop these services:

    OracleStartORCL

    Do you want to continue this operation? (Y/N): y

    The OracleStartORCL service was stopped successfully.

    The OracleServiceORCL service is stopping.
    The OracleServiceORCL service was stopped successfully.
    C:\>


    5. Generally, the database files contain the SID name; if you want to change
    this, use Explorer, File Manager or DOS, and rename the files listed in
    step 2 to the appropriate filenames.

    If you want to files to be placed elsewhere, copy the files to the new
    Location.

    Under Oracle8I, the default directory structure is such that :

    D:\Oracle\oradata\ORCL - will contain control files, redo logs, datafiles

    D:\Oracle\admin\ORCL\pfile - will contain the init file.

    These files typically will not contain the name as part of the filename.


    Example: The init file for the of 'LEO' will be:

    D:\Oracle\admin\LEO\pfile\init.ora - with the directory structure
    defining the .

    Reference the Getting Started Oracle8I for Windows NT manual for more
    information
    Regarding the Oracle8I default directory structure.


    6. Create and Edit the INITTEST.ORA:

    a. Copy the %ORACLE_HOME%\database\INITORCL.ORA to
    %ORACLE_HOME%\database\INITTEST.ORA
    b. Replace all occurrences of the string ORCL with TEST and modify all
    necessary directories to the correct path.
    c. Modify DB_NAME = ORACLE to read DB_NAME = TEST
    d. Save the file.

    7. Edit the dump file created in Step 1:

    a. Change all occurrences of the string ORCL to TEST.
    b. Remove the header (delete all lines above the STARTUP NOMOUNT)
    c. Modify the Startup line to read:

    STARTUP NOMOUNT PFILE=%ORACLE_HOME%\DATABASE\INITTEST.ORA

    In our example we would use:

    STARTUP NOMOUNT PFILE=D:\ORANT\DATABASE\INITTEST.ORA

    d. Change the NAME of the database. Modify the CREATE CONTROLFILE:

    replace REUSE DATABASE "Oracle" with SET DATABASE "Test"
    replace NORESETLOGS with RESETLOGS

    e. Remove the lines

    # 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;

    NOTE: the ALTER SYSTEM ARCHIVE LOG ALL is only present when the original
    database is in ARCHIVEMODE.

    f. Save the file to CHNAME.SQL


    =========================================================================

    Example:
    ========

    STARTUP NOMOUNT PFILE=D:\TEST\DATABASE\INITTEST.ORA
    CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 254
    MAXINSTANCES 1
    MAXLOGHISTORY 226
    LOGFILE
    GROUP 1 (
    'D:\DATABASES\TEST\LOGTEST1.ORA',
    'D:\DATABASES\TEST\CTLBIS\CTL1TESTBIS.ORA'
    ) SIZE 2M,
    GROUP 2 (
    'D:\DATABASES\TEST\LOGTEST2.ORA',
    'D:\DATABASES\TEST\CTLBIS\CTL2TESTBIS.ORA'
    ) SIZE 2M
    DATAFILE
    'D:\DATABASES\TEST\SYS1TEST.ORA',
    'D:\DATABASES\TEST\RBS1TEST.ORA',
    'D:\DATABASES\TEST\USR1TEST.ORA',
    'D:\DATABASES\TEST\TMP1TEST.ORA',
    'D:\DATABASES\TEST\INDX1TEST.ORA',
    'D:\DATABASES\TEST\OEM204.DBF',
    'D:\DATABASES\TEST\USR2TEST.ORA',
    'D:\DATABASES\TEST\TEST.DBS',
    'D:\DATABASES\TEST\WEBDB.DBF'
    ;

    ===========================================================================

    8. Use ORADIM80 to create the new instance with the new SID. From a DOS
    Command Prompt type:

    ORADIM80 -new -sid TEST -intpwd password -startmode auto -pfile
    %ORACLE_HOME%\DATABASE\INITTEST.ORA


    9. Verify that the new service is started:

    a. From in Control Panel/Services, scroll down to OracleServiceTEST.
    b. If not started, click on the START button.


    10. Executing the "chname.sql" script:

    C:\> SET ORACLE_SID=TEST
    C:\> svrmgr30

    SVRMGR> CONNECT INTERNAL/internal_password
    Connected.
    SVRMGR> @\chname.sql
    #or the directory where you saved your chname-script.

    This will recreate the controlfile with the new datafiles....


    11. Open and Shutdown the database.

    SVRMGR>ALTER DATABASE OPEN RESETLOGS;
    statement processed

    Verify at this point if the database is open:

    SVRMGR>SELECT status FROM V$THREAD;

    STATUS
    ------
    OPEN

    SVRMGR>SHUTDOWN IMMEDIATE;


    12. Stop the TEST service and take a full backup

    a. From in Control Panel/Services, scroll down to OracleServiceTEST.
    b. Click on the STOP button.
    c. Take a full backup of your database.


    13. Removing the old-instance:

    See Removing an Oracle Instance and Database from
    Windows NT

    Please note:

    a. If you reused the same files or you didn't rename them (i.e. you
    skipped Step 2 partially or completely), don't remove these files
    when following Step 6 of

    b. The instance removed in is called TEST, which is
    our new instance; you must replace TEST by ORCL in
    if you want to follow the instructions in to the
    letter.

    Also, be sure to check your NET8 or SQL*Net setup for references to ORCL
    and
    TEST (i.e. TNSNAMES.ORA, LISTENER.ORA, etc.).


    ----------------------------------------------------------------------------
    Oracle Support Services


    regards
    Hrishy

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