Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Smile Unanswered: Database Image !!!

    Hi all

    I have running Database on oracle 9i, on windows 2000
    I want to make Identical copy from this DB to use it as test envierment,

    how I can do this

    thanx

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    ok, it won't let me attach a text file so here it is, hope it helps ....


    need to get the master database into a state where it can be copied
    log onto sqlplus "sys/password as sysdba"
    shutdown immediate
    startup exclusive
    alter system checkpoint;
    alter system switch logfile;
    alter database backup controlfile to trace;
    shutdown

    copy the admin\orcl directory to your new database name (max 8 chars), eg orclcopy
    rename the init ora file and replace all referneces to the orcl directories
    create an init ora pointer file in oracle\ora9i\database to point to your new admin\orclcopy directory

    create the windows service, must be done before copying the datafiles
    oradim -new -sid ORCLCOPY -intpwd PASSWORD -startmode auto -pfile c:\oracle\or92\database\initORCLCOPY.ora
    (used oradim -delete -sid ORCLCOPY to delete it again)

    copy database files to their new directories (eg oracle\oradata\orclcopy) and delete control file(s) (.ctl) from copy

    Look at the trace file in \oracle\admin\ORCL\udump, Alter the trace file to "SET" database AND "RESETLOGS" and
    change the directory references to point to the new files. The order MUST be the same as the trace file
    but they can be on any drive

    connect to sqlplus as sysdba again

    STARTUP NOMOUNT

    CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 16
    MAXLOGHISTORY 1815
    LOGFILE
    GROUP 1 'C:\ORACLE\ORADATA\ORCLCOPY\REDO01.LOG' SIZE 1M,
    GROUP 2 'C:\ORACLE\ORADATA\ORCLCOPY\REDO02.LOG' SIZE 1M,
    GROUP 3 'C:\ORACLE\ORADATA\ORCLCOPY\REDO03.LOG' SIZE 1M
    DATAFILE
    'C:\ORACLE\ORADATA\ORCLCOPY\SYSTEM01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\RBS01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\USERS01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\TEMP01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\TOOLS01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\INDX01.DBF',
    'D:\ORACLE\ORADATA\ORCLCOPY\XYZ.DBF'
    CHARACTER SET WE8ISO8859P1
    ;

    ALTER DATABASE OPEN RESETLOGS;

    shutdown immediate

    startup

    then check the alertlog for errors!

  3. #3
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137
    hi

    are there any easy way like using EXP - IMP

    ?????





    Originally posted by robert xr4x4
    ok, it won't let me attach a text file so here it is, hope it helps ....


    need to get the master database into a state where it can be copied
    log onto sqlplus "sys/password as sysdba"
    shutdown immediate
    startup exclusive
    alter system checkpoint;
    alter system switch logfile;
    alter database backup controlfile to trace;
    shutdown

    copy the admin\orcl directory to your new database name (max 8 chars), eg orclcopy
    rename the init ora file and replace all referneces to the orcl directories
    create an init ora pointer file in oracle\ora9i\database to point to your new admin\orclcopy directory

    create the windows service, must be done before copying the datafiles
    oradim -new -sid ORCLCOPY -intpwd PASSWORD -startmode auto -pfile c:\oracle\or92\database\initORCLCOPY.ora
    (used oradim -delete -sid ORCLCOPY to delete it again)

    copy database files to their new directories (eg oracle\oradata\orclcopy) and delete control file(s) (.ctl) from copy

    Look at the trace file in \oracle\admin\ORCL\udump, Alter the trace file to "SET" database AND "RESETLOGS" and
    change the directory references to point to the new files. The order MUST be the same as the trace file
    but they can be on any drive

    connect to sqlplus as sysdba again

    STARTUP NOMOUNT

    CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 16
    MAXLOGHISTORY 1815
    LOGFILE
    GROUP 1 'C:\ORACLE\ORADATA\ORCLCOPY\REDO01.LOG' SIZE 1M,
    GROUP 2 'C:\ORACLE\ORADATA\ORCLCOPY\REDO02.LOG' SIZE 1M,
    GROUP 3 'C:\ORACLE\ORADATA\ORCLCOPY\REDO03.LOG' SIZE 1M
    DATAFILE
    'C:\ORACLE\ORADATA\ORCLCOPY\SYSTEM01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\RBS01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\USERS01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\TEMP01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\TOOLS01.DBF',
    'C:\ORACLE\ORADATA\ORCLCOPY\INDX01.DBF',
    'D:\ORACLE\ORADATA\ORCLCOPY\XYZ.DBF'
    CHARACTER SET WE8ISO8859P1
    ;

    ALTER DATABASE OPEN RESETLOGS;

    shutdown immediate

    startup

    then check the alertlog for errors!

  4. #4
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    you can do that yes! Create a new database and export the schema you want and then import it.

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    this covers import and export
    http://download-west.oracle.com/docs...a90192/toc.htm

    but basically you can use Oracles database manager to create your blank database and then
    exp user/pass@db file=myexp.dmp log=mylog.log
    where user is the schema you want to export
    then
    imp user/pass@newdb file=myexp.dmp log=imp.log full=Y

    or if you dont want to create a new database
    imp user2/pass@db file=myexp.dmp log=imp.log fromuser=user touser=user2 full=Y
    but this is going to be dependant on not having public synonyms and suchlike

Posting Permissions

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