oracle实验-数据库复制

数据库复制【jdhq_db ===> clone_db】
一、克隆数据库准备
    在克隆前需要先复制参数文件修改，并启动到nomount状态。在WINDOWS下面还需要创建对应的服务及配置监听文件。tnsnames.ora文件
    ①．建立新的目录结构
        E:\demo\Clone_db\Archive
        E:\demo\Clone_db\bdump
        E:\demo\Clone_db\cdump
        E:\demo\Clone_db\CtlFile
        E:\demo\Clone_db\Dbfile
        E:\demo\Clone_db\LogFile
        E:\demo\Clone_db\pfile
        E:\demo\Clone_db\udump
    ②．复制启动参数文件，并修改
        #复制参数文件：
        c:\>Copy E:\demo\JDHQ_DB\pfile\initJDHQDB.ora E:\demo\Clone_db\pfile\initClone.ora
        #修改参数文件【E:\demo\Clone_db\pfile\initClone.ora】
        instance_name=CLONEDB
        db_name=CLONE_DB
        control_files=("e:\demo\Clone_db\CtlFile\control01.ctl", "e:\demo\Clone_db\CtlFile\control02.ctl", "e:\demo\Clone_db\CtlFile\control03.ctl")
        background_dump_dest=e:\demo\Clone_DB\bdump
        core_dump_dest=e:\demo\Clone_DB\cdump
        user_dump_dest=e:\demo\Clone_DB\udump
        log_archive_dest_1 = "location=E:\demo\Clone_DB\Archive"
        #使用如下此参数文件，则不必set newname for datafile 文件号 to 'E:\demo\Clone_DB\DBFILE\文件名'的转换
        #注：如果开始时不用DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数，则需要修改set newname，
        #由于数据文件较多，如果手写set newname脚本会不切实际，可以用以下方法获取set newname
        DB_FILE_NAME_CONVERT =('E:\demo\JDHQ_DB\DBFILE' ,'E:\demo\Clone_DB\DBFILE')
        LOG_FILE_NAME_CONVERT=('E:\demo\JDHQ_DB\LogFile','E:\demo\Clone_DB\LogFile')
    ③．使用oradim创建新的instance
        c:\>oradim -new -sid CLONE -intpwd future -maxusers 5 -startmode a -pfile E:\demo\Clone_db\pfile\initClone.ora
        例程已创建。
    ④．使用orapwd创建password文件
        c:\>orapwd file=D:\oracle\ora92\database\PWDCLONE.ora password=sys entries=10
        增加启动配置文件
        D:\oracle\ora92\database\initClone.ora
        内容为：
        IFILE='E:\demo\Clone_db\pfile\initClone.ora'
    ⑤．配置好监听和tnsnames，然后使用sqlplus测试连接
        # Added to the listener.ora SID_LIST
        (SID_DESC =
           (GLOBAL_DBNAME = CLONE_DB)
           (O RACLE_HOME = d:\oracle\ora92)
           (SID_NAME = CLONE)
        )

        # Added to the tnsnames.ora
        CLONE_DB =
          (DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = future-mget)(PORT = 1521))
            )
            (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = CLONE_DB)
            )
          )
         
         #重新启动侦听
         net stop OracleOraHome92TNSListener
         net start OracleOraHome92TNSListener
    ⑥．连接测试数据库
        c:\>set oracle_sid=CLONE
        c:\>sqlplus /nolog
         SQL*Plus: Release 10.1.0.2.0 - Production on 星期日 3月 27 21:01:13 2005
        Copyright (c) 1982, 2004, Oracle.  All rights reserved.
        SQL> conn sys/future as sysdba
        SQL> STARTUP pfile=E:\demo\Clone_db\pfile\initClone.ora nomount;

二、备份目标数据库
    C:\>RMAN
    connect rcvcat rman/rman@rman_db
    connect target sys/change_on_install@jdhq_db
    run {
         allocate channel oem_backup_disk1 type disk ;
         backup tag 'dbfull' format 'D:\dbfull_%d_%s_%p.bck'
         database include current controlfile;
         backup tag 'logfull' format 'D:\logfull_%d_%s_%p.bck' archivelog all;
         release channel oem_backup_disk1;}

三、克隆数据库
    C:\>RMAN
    =========================================================================
    Recovery Manager: Release 9.2.0.1.0 - Production
   
    Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
   
    RMAN> connect target sys/change_on_install@jdhq_db
    =========================================================================
    connected to target database: JDHQ_DB (DBID=3736538461)
   
    RMAN> connect catalog rman/rman@rman_db
    =========================================================================
    connectedto recovery catalog database
   
    RMAN> connect auxiliary sys/future@clone_db
    =========================================================================
    connected to auxiliary database: CLONE_DB (not mounted)
   
    RMAN> duplicate target database to clone_db;
    =========================================================================
    Starting Duplicate Db at 31-MAY-06
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: sid=11 devtype=DISK
   
    printing stored script. Memory Script
    {
       set until scn  6907023745;
       set newname for datafile  1 to
     "E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB";
       set newname for datafile  2 to
     "E:\DEMO\CLONE_DB\DBFILE\RBSGJDHQDB.ODB";
       set newname for datafile  3 to
     "E:\DEMO\CLONE_DB\DBFILE\DATAJDHQDB.ODB";
       set newname for datafile  4 to
     "E:\DEMO\CLONE_DB\DBFILE\INDXJDHQDB.ODB";
       set newname for datafile  5 to
     "E:\DEMO\CLONE_DB\DBFILE\TOOLJDHQDB.ODB";
       restore
       check readonly
       clone database
       ;
    }
    executing script. Memory Script
   
    executing command: SET until clause
   
    executing command: SET NEWNAME
   
    executing command: SET NEWNAME
   
    executing command: SET NEWNAME
   
    executing command: SET NEWNAME
   
    executing command: SET NEWNAME
   
    Starting restore at 31-MAY-06
   
    using channel ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    restoring datafile 00001 to E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB
    restoring datafile 00002 to E:\DEMO\CLONE_DB\DBFILE\RBSGJDHQDB.ODB
    restoring datafile 00003 to E:\DEMO\CLONE_DB\DBFILE\DATAJDHQDB.ODB
    restoring datafile 00004 to E:\DEMO\CLONE_DB\DBFILE\INDXJDHQDB.ODB
    restoring datafile 00005 to E:\DEMO\CLONE_DB\DBFILE\TOOLJDHQDB.ODB
    channel ORA_AUX_DISK_1: restored backup piece 1
    piece handle=D:\DBFULL_JDHQ_DB_14_1.BCK tag=DBFULL params=NULL
    channel ORA_AUX_DISK_1: restore complete
    Finished restore at 31-MAY-06
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE "clone_db" RESETLOGS ARCHIV
    ELOG
      MAXLOGFILES      5
      MAXLOGMEMBERS      3
      MAXDATAFILES      100
      MAXINSTANCES     1
      MAXLOGHISTORY      226
     LOGFILE
      GROUP  1 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO01.LOG' ) SIZE  104857600  REUSE,
      GROUP  2 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO02.LOG' ) SIZE  104857600  REUSE,
      GROUP  3 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO03.LOG' ) SIZE  104857600  REUSE
     DATAFILE
      'E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB'
     CHARACTER SET WE8ISO8859P1printing stored script. Memory Script
    {
       switch clone datafile all;
    }
    executing script. Memory Script
   
    datafile 2 switched to datafile copy
    input datafilecopy recid=1 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\RBSG
    JDHQDB.ODB
    datafile 3 switched to datafile copy
    input datafi lecopy recid=2 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\DATA
    JDHQDB.ODB
    datafile 4 switched to datafile copy
    input datafilecopy recid=3 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\INDX
    JDHQDB.ODB
    datafile 5 switched to datafile copy
    input datafilecopy recid=4 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\TOOL
    JDHQDB.ODB
   
    printing stored script. Memory Script
    {
       set until scn  6907023745;
       recover
       clone database
        delete archivelog
       ;
    }
    executing script. Memory Script
   
    executing command: SET until clause
   
    Starting recover at 31-MAY-06
    using channelORA_AUX_DISK_1
   
    starting media recovery
   
    archive log thread 1 sequence 14 is already on disk as file E:\DEMO\JDHQ_DB\ARCH
    IVE\JDHQDBT001S00014.ARC
    archive log filename=E:\DEMO\JDHQ_DB\ARCHIVE\JDHQDBT001S00014.ARC thread=1 seque
    nce=14
    media recovery complete
    Finished recover at 31-MAY-06
   
    printing stored script. Memory Script
    {
       shutdown clone;
       startup clone nomount ;
    }
    executing script. Memory Script
   
    database dismounted
    Oracle instance shut down
        RMAN>

四、去掉init.ora的DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数重新启动数据库
    c:\>sqlplus /nolog      
    SQL> startup pfile=E:\demo\Clone_db\pfile\initClone.ora

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7551038/viewspace-614399/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7551038/viewspace-614399/