用戶管理的熱備份方式復制數據庫

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