Duplicate 複製資料庫實驗過程

Duplicate 複製資料庫

本次實驗通過duplicate命令，在本機環境中建立一個複製資料庫。目標資料庫為hongye、複製資料庫為catdb。

環境別名設定：
alias sql='rlwrap sqlplus /nolog'
alias rman='rlwrap rman'
alias dbs='cd $ORACLE_HOME/dbs'
alias rdb='cd $ORACLE_HOME/rdbms/admin'
alias udu='cd $ORACLE_BASE/admin/$ORACLE_SID/udump'
alias bdu='cd $ORACLE_BASE/admin/$ORACLE_SID/bdump'
alias rmalert='rm -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/*;rm -f $ORACLE_BASE/admin/$ORACLE_SID/udump/*'


詳細步驟如下：

1、構建catdb的初始化引數檔案，本次實驗使用的引數檔案是從hongye資料庫中建立出來的，並做了一些修改。
注意引數db_file_name_convert和log_file_name_convert引數，這兩個引數控制了資料檔案和日誌檔案的重新命名，關於資料檔案的重新命名也可以使用set newname命令，日誌檔案的重新命名可以在duplicate命令的子句中指定，但均沒有init引數中的轉換方便。
db_file_name_convert和log_file_name_convert引數都是使用串替換的方式重新命名的，通過將目標檔名的指定的字串替換成指定的字串，從而生成新的檔名
本次實驗中，hongye資料庫的資料檔案存放在路徑為：/oracle/oradata/hongye/，而catdb的資料檔案路徑為：/oracle/oradata/catdb/，所以只要將所有檔名中的hongye替換成catdb就可以了。

[oracle@ORA10G ~]$ dbs                                                                                    
[oracle@ORA10G dbs]$ echo $ORACLE_SID
hongye
[oracle@ORA10G dbs]$ ll
total 6960
-rw-rw----  1 oracle oinstall    1544 Feb 11 22:13 hc_hongye.dat
-rw-r-----  1 oracle oinstall     848 Feb 17 19:46 initcatdb.ora
-rw-r--r--  1 oracle oinstall   12920 May  3  2001 initdw.ora
-rw-r-----  1 oracle oinstall    1079 Feb 17 19:21 inithongye.ora
-rw-r-----  1 oracle oinstall    2469 Feb 11 20:59 init.ora
-rw-r-----  1 oracle oinstall      24 Feb 11 19:20 lkHONGYE
-rw-r-----  1 oracle oinstall    1536 Feb 17 19:49 orapwcatdb
-rw-r-----  1 oracle oinstall    1536 Feb 15 21:01 orapwhongye
-rw-r-----  1 oracle oinstall 7061504 Feb 17 19:20 snapcf_hongye.f
-rw-r-----  1 oracle oinstall    3584 Feb 17 19:16 spfilehongye.ora
-rw-r-----  1 oracle oinstall     694 Feb 17 19:34 sqlnet.log
[oracle@ORA10G dbs]$ vi initcatdb.ora
*.audit_file_dest='/oracle/admin/catdb/adump'
*.background_dump_dest='/oracle/admin/catdb/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oracle/oradata/catdb/control01.ctl'
*.core_dump_dest='/oracle/admin/catdb/cdump'
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_file_multiblock_read_count=16
*.db_name='catdb'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=catdbXDB)'
*.job_queue_processes=10
*.nls_date_format='yyyy-mm-dd hh24:mi:ss'
*.open_cursors=30
*.pga_aggregate_target=16777216
*.processes=50
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=70
*.sga_target=80M
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/catdb/udump'
--檔名的轉換引數：
db_file_name_convert=('hongye','catdb')
log_file_name_convert=('hongye','catdb')
~
~
~
~
~
"initcata.ora" 25L, 848C written

2、建立複製資料庫的相關目錄，包括adump、bdump、cdump、udump、oradata/catdb

[oracle@ORA10G dbs]$ cd /oracle/admin
[oracle@ORA10G dbs]$ mkdir catdb catdb/adump catdb/bdump catdb/cdump catdb/udump
[oracle@ORA10G dbs]$ ll /oracle/admin/
total 8
drwxr-xr-x  7 oracle oinstall 4096 Feb 17 19:29 catdb
drwxr-x---  8 oracle oinstall 4096 Feb 11 19:19 hongye
[oracle@ORA10G dbs]$ mkdir /oracle/oradata/catdb/
[oracle@ORA10G dbs]$ ll /oracle/oradata/catdb/
total 0

3、將ORACLE_SID設定為複製資料庫的sid，那麼就不需要額外的配置複製資料庫的網路檔案了，前提是已經配置了目標資料庫的網路配置檔案，二者必須至少要配其一。

[oracle@ORA10G dbs]$ echo $ORACLE_SID
hongye
[oracle@ORA10G dbs]$ export ORACLE_SID=catdb
[oracle@ORA10G dbs]$ echo $ORACLE_SID
catdb

4、建立複製資料庫的密碼檔案。

[oracle@ORA10G dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwcatdb

Enter password for SYS:

5、登入複製資料庫，以nomount方式開啟資料庫。

[oracle@ORA10G dbs]$ sql

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 17 19:50:57 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

IDLE > conn /as sysdba
Connected to an idle instance.
IDLE > startup nomount pfile=$ORACLE_HOME/dbs/initcatdb.ora
ORACLE instance started.

Total System Global Area   83886080 bytes
Fixed Size                  1272096 bytes
Variable Size              58722016 bytes
Database Buffers           16777216 bytes
Redo Buffers                7114752 bytes
IDLE > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

6、在RMAN中執行資料庫的複製操作。

[oracle@ORA10G dbs]$ rman

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Feb 17 19:51:50 2011

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

RMAN> connect target sys/hongye50@hongye

connected to target database: HONGYE (DBID=2424037643)

RMAN> connect auxiliary /

connected to auxiliary database: CATDB (not mounted)

RMAN> duplicate target database to catdb;

Starting Duplicate Db at 17-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=56 devtype=DISK

contents of Memory Script.:
{
   set until scn  1219581;
   set newname for datafile  1 to
"/oracle/oradata/catdb/system01.dbf";
   set newname for datafile  2 to
"/oracle/oradata/catdb/undotbs01.dbf";
   set newname for datafile  3 to
"/oracle/oradata/catdb/sysaux01.dbf";
   set newname for datafile  4 to
"/oracle/oradata/catdb/users01.dbf";
   set newname for datafile  5 to
"/oracle/oradata/catdb/example01.dbf";
   restore
   check readonly
   clone database
   ;
}
executing 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 17-FEB-11
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 /oracle/oradata/catdb/system01.dbf
restoring datafile 00002 to /oracle/oradata/catdb/undotbs01.dbf
restoring datafile 00003 to /oracle/oradata/catdb/sysaux01.dbf
restoring datafile 00004 to /oracle/oradata/catdb/users01.dbf
restoring datafile 00005 to /oracle/oradata/catdb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE/backupset/2011_02_17/o1_mf_nnndf_TAG20110217T191915_6ot11mbc_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE/backupset/2011_02_17/o1_mf_nnndf_TAG20110217T191915_6ot11mbc_.bkp tag=TAG20110217T191915
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 17-FEB-11

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CATDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP  1 ( '/oracle/oradata/catdb/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/oracle/oradata/catdb/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/oracle/oradata/catdb/redo03.log' ) SIZE 50 M  REUSE
DATAFILE
  '/oracle/oradata/catdb/system01.dbf'
CHARACTER SET WE8ISO8859P1

contents of Memory Script.:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=743370847 filename=/oracle/oradata/catdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=743370847 filename=/oracle/oradata/catdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=743370847 filename=/oracle/oradata/catdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=743370847 filename=/oracle/oradata/catdb/example01.dbf

contents of Memory Script.:
{
   set until scn  1219581;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-FEB-11
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE/backupset/2011_02_17/o1_mf_annnn_TAG20110217T192041_6ot14bjm_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE/backupset/2011_02_17/o1_mf_annnn_TAG20110217T192041_6ot14bjm_.bkp tag=TAG20110217T192041
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/oracle/flash_recovery_area/CATDB/archivelog/2011_02_17/o1_mf_1_2_6ot3332m_.arc thread=1 sequence=2
channel clone_default: deleting archive log(s)
archive log filename=/oracle/flash_recovery_area/CATDB/archivelog/2011_02_17/o1_mf_1_2_6ot3332m_.arc recid=1 stamp=743370851
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-FEB-11

contents of Memory Script.:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area      83886080 bytes

Fixed Size                     1272096 bytes
Variable Size                 58722016 bytes
Database Buffers              16777216 bytes
Redo Buffers                   7114752 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CATDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP  1 ( '/oracle/oradata/catdb/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/oracle/oradata/catdb/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/oracle/oradata/catdb/redo03.log' ) SIZE 50 M  REUSE
DATAFILE
  '/oracle/oradata/catdb/system01.dbf'
CHARACTER SET WE8ISO8859P1


contents of Memory Script.:
{
   set newname for tempfile  1 to
"/oracle/oradata/catdb/temp01.dbf";
   set newname for tempfile  2 to
"/oracle/oradata/catdb/temp02.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/oracle/oradata/catdb/undotbs01.dbf";
   catalog clone datafilecopy  "/oracle/oradata/catdb/sysaux01.dbf";
   catalog clone datafilecopy  "/oracle/oradata/catdb/users01.dbf";
   catalog clone datafilecopy  "/oracle/oradata/catdb/example01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /oracle/oradata/catdb/temp01.dbf in control file
renamed temporary file 2 to /oracle/oradata/catdb/temp02.dbf in control file

cataloged datafile copy
datafile copy filename=/oracle/oradata/catdb/undotbs01.dbf recid=1 stamp=743370868

cataloged datafile copy
datafile copy filename=/oracle/oradata/catdb/sysaux01.dbf recid=2 stamp=743370869

cataloged datafile copy
datafile copy filename=/oracle/oradata/catdb/users01.dbf recid=3 stamp=743370869

cataloged datafile copy
datafile copy filename=/oracle/oradata/catdb/example01.dbf recid=4 stamp=743370869

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=743370868 filename=/oracle/oradata/catdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=743370869 filename=/oracle/oradata/catdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=743370869 filename=/oracle/oradata/catdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=743370869 filename=/oracle/oradata/catdb/example01.dbf

contents of Memory Script.:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 17-FEB-11

RMAN> exit


Recovery Manager complete.

7、登入複製資料庫，檢視結果。

[oracle@ORA10G dbs]$ sql

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 17 19:55:07 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

IDLE > conn /as sysdba
Connected.
SYS:57@catdb > select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SYS:57@catdb > @get_allfile

TYPE                FILE_NAME                                                                 SIZE_MB
------------------- ---------------------------------------------------------------------- ----------
Datafile ........  /oracle/oradata/catdb/system01.dbf                                            600
Datafile ........  /oracle/oradata/catdb/undotbs01.dbf                                           405
Datafile ........  /oracle/oradata/catdb/sysaux01.dbf                                            300
Datafile ........  /oracle/oradata/catdb/users01.dbf                                              20
Datafile ........  /oracle/oradata/catdb/example01.dbf                                           100

TempFile ........  /oracle/oradata/catdb/temp01.dbf                                               30
TempFile ........  /oracle/oradata/catdb/temp02.dbf                                               30

Logfile .........  /oracle/oradata/catdb/redo03.log                                               50
Logfile .........  /oracle/oradata/catdb/redo02.log                                               50
Logfile .........  /oracle/oradata/catdb/redo01.log                                               50

Controlfile .....  /oracle/oradata/catdb/control01.ctl                                             7


11 rows selected.

SYS:57@catdb >

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24465008/viewspace-688037/，如需轉載，請註明出處，否則將追究法律責任。