用户管理的热备份方式复制数据库

1. 创建实例, 密码文件, 初始化参数文件, 以及目标库相应目录
> oradim -new -sid dup （Windows下需要）
> orapwd file=pwddup password=xxxxxx（Windows下需要）
> mkdir %ORACLE_BASE%\admin\dup
> mkdir %ORACLE_BASE%\admin\dup\adump
> mkdir %ORACLE_BASE%\admin\dup\bdump
> mkdir %ORACLE_BASE%\admin\dup\cdump
> mkdir %ORACLE_BASE%\admin\dup\udump

2. 在源库开始热备份, 并复制数据文件到目标库目录
GO> alter database begin backup;

3. 源库停止热备份
GO> alter database end backup;

4. 归档源库当前日志, 并拷贝到目标库归档目录
GO> select * from v$log;
    GROUP# SEQUENCE# ARC STATUS  
---------- --------- --- --------
         1        10 YES ACTIVE  
         2        11 NO  CURRENT 
         3         9 YES INACTIVE

GO> alter system archive log current;


5. 将目标库启动到 nomount 状态, 并创建控制文件
DUP> startup nomount

=================================
CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\DATA\ORACLE10GR2\DUP\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\DATA\ORACLE10GR2\DUP\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\DATA\ORACLE10GR2\DUP\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'D:\DATA\ORACLE10GR2\DUP\SYSTEM01.DBF',
  'D:\DATA\ORACLE10GR2\DUP\UNDOTBS01.DBF',
  'D:\DATA\ORACLE10GR2\DUP\SYSAUX01.DBF',
  'D:\DATA\ORACLE10GR2\DUP\USERS01.DBF'
CHARACTER SET AL32UTF8
;
=================================

6. 对目标库做介质恢复
DUP> recover database using backup controlfile until cancel;

ORA-00279: change 2521364 generated at 09/23/2012 16:04:19 needed for thread 1
ORA-00289: suggestion : D:\DATA\ORACLE10GR2\FLASH_RECOVERY_AREA\DUP\ARCHIVELOG\2012_09_23\O1_MF_1_10_%U_.ARC
ORA-00280: change 2521364 for thread 1 is in sequence #10

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\DATA\ORACLE10GR2\FLASH_RECOVERY_AREA\DUP\ARCHIVELOG\2012_09_23\O1_MF_1_10_85XF6VRV_.ARC
...

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\DATA\ORACLE10GR2\FLASH_RECOVERY_AREA\DUP\ARCHIVELOG\2012_09_23\O1_MF_1_11_85XF9846_.ARC
...

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

7. 以 resetlogs 方式打开目标库
DUP> alter database open resetlogs;

8. 添加临时文件到目标库
DUP> alter tablespace temp add tempfile 'D:\Data\oracle10gr2\dup\temp01.dbf' size 5m reuse;
DUP> select file#,name from v$tempfile;
     FILE# NAME
---------- ----------------------------------------
         1 D:\DATA\ORACLE10GR2\DUP\TEMP01.DBF



9. 修改目标库DBID
DUP> select dbid,name from v$database;
      DBID NAME
---------- ----------------------------------------
1002322791 DUP

DUP> shutdown immediate
DUP> startup mount

> nid target=sys/oracle

DBNEWID: Release 10.2.0.5.0 - Production on Sun Sep 23 16:32:51 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database DUP (DBID=1002322791)

Connected to server version 10.2.0

Control Files in database:
    D:\DATA\ORACLE10GR2\DUP\CONTROL01.CTL
    D:\DATA\ORACLE10GR2\DUP\CONTROL02.CTL
    D:\DATA\ORACLE10GR2\DUP\CONTROL03.CTL

Change database ID of database DUP? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1002322791 to 165619252
    Control File D:\DATA\ORACLE10GR2\DUP\CONTROL01.CTL - modified
    Control File D:\DATA\ORACLE10GR2\DUP\CONTROL02.CTL - modified
    Control File D:\DATA\ORACLE10GR2\DUP\CONTROL03.CTL - modified
    Datafile D:\DATA\ORACLE10GR2\DUP\SYSTEM01.DBF - dbid changed
    Datafile D:\DATA\ORACLE10GR2\DUP\UNDOTBS01.DBF - dbid changed
    Datafile D:\DATA\ORACLE10GR2\DUP\SYSAUX01.DBF - dbid changed
    Datafile D:\DATA\ORACLE10GR2\DUP\USERS01.DBF - dbid changed
    Datafile D:\DATA\ORACLE10GR2\DUP\TEMP01.DBF - dbid changed
    Control File D:\DATA\ORACLE10GR2\DUP\CONTROL01.CTL - dbid changed
    Control File D:\DATA\ORACLE10GR2\DUP\CONTROL02.CTL - dbid changed
    Control File D:\DATA\ORACLE10GR2\DUP\CONTROL03.CTL - dbid changed
    Instance shut down

Database ID for database DUP changed to 165619252.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.


DUP> startup mount
DUP> alter database open resetlogs;
DUP> select dbid,name from v$database;
      DBID NAME
---------- ----
 165619252 DUP