複製資料庫步驟

這個例子是在相同主機伺服器複製，在同一個ORACLE_HOME下

1.建立相關目錄，從原資料庫生成init檔案，增加最後兩個轉向引數

 

:~> cd admin
> ls
clne  itown01
> cd clne
> ls
bdump  cdump  create  pfile  udump
> cd pfile
> ls
init.bak  init.ora  init.ora.old
> cat init.ora
*.aq_tm_processes=1
*.background_dump_dest='/u01/ora9/admin/clne/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u01/ora9/oradata/clne/control01.ctl','/u01/ora9/oradata/clne/control02.ctl','/u01/ora9/oradata/clne/control03.ctl'
*.core_dump_dest='/u01/ora9/admin/clne/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='clne'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clneXDB)'
*.fast_start_mttr_target=300
*.global_names=FALSE
*.hash_join_enabled=TRUE
*.instance_name='clne'
*.java_pool_size=83886080
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/u01/ora9/oradata/clne/archive'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/ora9/admin/clne/udump'
*.db_file_name_convert='mydb01','clne'
*.log_file_name_convert='mydb01','clne'

 

 

2.增加預設引數檔案，一定先增加，否則在rman恢復後自動載入將報錯。連線兩個資料庫。注意新增對應tns

 

sql>startup nomount pfile='/u01/ora9/admin/clne/pfile/init.ora'

 

:~> rman

Recovery Manager: Release 9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> connect target sys/sys@192.168.1.1

connected to target database: MYDB01 (DBID=3061999746)

RMAN> connect auxiliary /

connected to auxiliary database: MYDB01 (DBID=3061999746)

 

3.由於幾個資料檔案是在dbs目錄下，手動改變到新的目錄，否則將提示檔案正被目標資料庫使用。

 

RMAN> run{
2> allocate auxiliary channel d1 type disk;
3> set until logseq 552 thread 1;
4> set newname for datafile 11 to '/u01/ora9/oradata/clne/aams001.dbf';
5> set newname for datafile 12 to '/u01/ora9/oradata/clne/jira001.dbf';
6> set newname for datafile 14 to '/u01/ora9/oradata/clne/icms001.dbf';
7> set newname for datafile 15 to '/u01/ora9/oradata/clne/bsa001.dbf' ;
8> set newname for datafile 16 to '/u01/ora9/oradata/clne/ses001.dbf' ;
9> duplicate target database to clne;
10> }

released channel: ORA_AUX_DISK_1
allocated channel: d1
channel d1: sid=13 devtype=DISK

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 Duplicate Db at 09-APR-12

printing stored script. Memory Script
{
   set until scn  9493172169649;
   set newname for datafile  1 to
 "/u01/ora9/oradata/clne/system01.dbf";
   set newname for datafile  2 to
 "/u01/ora9/oradata/clne/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/ora9/oradata/clne/cwmlite01.dbf";
   set newname for datafile  4 to
 "/u01/ora9/oradata/clne/drsys01.dbf";
   set newname for datafile  5 to
 "/u01/ora9/oradata/clne/example01.dbf";
   set newname for datafile  6 to
 "/u01/ora9/oradata/clne/indx01.dbf";
   set newname for datafile  7 to
 "/u01/ora9/oradata/clne/odm01.dbf";
   set newname for datafile  8 to
 "/u01/ora9/oradata/clne/tools01.dbf";
   set newname for datafile  9 to
 "/u01/ora9/oradata/clne/users01.dbf";
   set newname for datafile  10 to
 "/u01/ora9/oradata/clne/xdb01.dbf";
   set newname for datafile  11 to
 "/u01/ora9/oradata/clne/aams001.dbf";
   set newname for datafile  12 to
 "/u01/ora9/oradata/clne/jira001.dbf";
   set newname for datafile  14 to
 "/u01/ora9/oradata/clne/icms001.dbf";
   set newname for datafile  15 to
 "/u01/ora9/oradata/clne/bsa001.dbf";
   set newname for datafile  16 to
 "/u01/ora9/oradata/clne/ses001.dbf";
   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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-APR-12

channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00016 to /u01/ora9/oradata/clne/ses001.dbf
channel d1: restored backup piece 1
piece handle=/u01/ora9/backup/db_MYDB01_17_1_779903891.dbf tag=WHOLE_INCO params=NULL
channel d1: restore complete
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/ora9/oradata/clne/undotbs01.dbf
restoring datafile 00015 to /u01/ora9/oradata/clne/bsa001.dbf
channel d1: restored backup piece 1
piece handle=/u01/ora9/backup/db_MYDB01_18_1_779904286.dbf tag=WHOLE_INCO params=NULL
channel d1: restore complete
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/ora9/oradata/clne/system01.dbf
restoring datafile 00007 to /u01/ora9/oradata/clne/odm01.dbf
restoring datafile 00011 to /u01/ora9/oradata/clne/aams001.dbf
channel d1: restored backup piece 1
piece handle=/u01/ora9/backup/db_MYDB01_19_1_779904502.dbf tag=WHOLE_INCO params=NULL
channel d1: restore complete
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/ora9/oradata/clne/cwmlite01.dbf
restoring datafile 00004 to /u01/ora9/oradata/clne/drsys01.dbf
restoring datafile 00005 to /u01/ora9/oradata/clne/example01.dbf
restoring datafile 00006 to /u01/ora9/oradata/clne/indx01.dbf
restoring datafile 00008 to /u01/ora9/oradata/clne/tools01.dbf
restoring datafile 00009 to /u01/ora9/oradata/clne/users01.dbf
restoring datafile 00010 to /u01/ora9/oradata/clne/xdb01.dbf
restoring datafile 00012 to /u01/ora9/oradata/clne/jira001.dbf
restoring datafile 00014 to /u01/ora9/oradata/clne/icms001.dbf
channel d1: restored backup piece 1
piece handle=/u01/ora9/backup/db_MYDB01_20_1_779904588.dbf tag=WHOLE_INCO params=NULL
channel d1: restore complete
Finished restore at 09-APR-12
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "clne" RESETLOGS ARCHIVELOG
  MAXLOGFILES     50
  MAXLOGMEMBERS      5
  MAXDATAFILES      100
  MAXINSTANCES     1
  MAXLOGHISTORY      226
 LOGFILE
  GROUP  1 ( '/u01/ora9/oradata/clne/redo01.log' ) SIZE  104857600  REUSE,
  GROUP  2 ( '/u01/ora9/oradata/clne/redo02.log' ) SIZE  104857600  REUSE,
  GROUP  3 ( '/u01/ora9/oradata/clne/redo03.log' ) SIZE  104857600  REUSE
 DATAFILE
  '/u01/ora9/oradata/clne/system01.dbf'
 CHARACTER SET ZHS16GBK

printing stored script. Memory Script
{
   switch clone datafile all;
}
executing script. Memory Script

datafile 11 switched to datafile copy
input datafilecopy recid=1 stamp=780149258 filename=/u01/ora9/oradata/clne/aams001.dbf
datafile 12 switched to datafile copy
input datafilecopy recid=2 stamp=780149258 filename=/u01/ora9/oradata/clne/jira001.dbf
datafile 14 switched to datafile copy
input datafilecopy recid=3 stamp=780149258 filename=/u01/ora9/oradata/clne/icms001.dbf
datafile 15 switched to datafile copy
input datafilecopy recid=4 stamp=780149258 filename=/u01/ora9/oradata/clne/bsa001.dbf
datafile 16 switched to datafile copy
input datafilecopy recid=5 stamp=780149258 filename=/u01/ora9/oradata/clne/ses001.dbf
datafile 2 switched to datafile copy
input datafilecopy recid=6 stamp=780149258 filename=/u01/ora9/oradata/clne/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=7 stamp=780149258 filename=/u01/ora9/oradata/clne/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=8 stamp=780149258 filename=/u01/ora9/oradata/clne/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=9 stamp=780149258 filename=/u01/ora9/oradata/clne/example01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=10 stamp=780149258 filename=/u01/ora9/oradata/clne/indx01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=11 stamp=780149258 filename=/u01/ora9/oradata/clne/odm01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=12 stamp=780149258 filename=/u01/ora9/oradata/clne/tools01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=13 stamp=780149258 filename=/u01/ora9/oradata/clne/users01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=14 stamp=780149258 filename=/u01/ora9/oradata/clne/xdb01.dbf

printing stored script. Memory Script
{
   set until scn  9493172169649;
   recover
   clone database
    delete archivelog
   ;
}
executing script. Memory Script

executing command: SET until clause

Starting recover at 09-APR-12

starting media recovery

archive log thread 1 sequence 550 is already on disk as file /u01/ora9/oradata/mydb01/archive/1_550.dbf
archive log thread 1 sequence 551 is already on disk as file /u01/ora9/oradata/mydb01/archive/1_551.dbf
archive log filename=/u01/ora9/oradata/mdb01/archive/1_550.dbf thread=1 sequence=550
archive log filename=/u01/ora9/oradata/mydb01/archive/1_551.dbf thread=1 sequence=551
media recovery complete
Finished recover at 09-APR-12

printing stored script. Memory Script
{
   shutdown clone;
   startup clone nomount ;
}
executing script. Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     236000476 bytes

Fixed Size                      451804 bytes
Variable Size                201326592 bytes
Database Buffers              33554432 bytes
Redo Buffers                    667648 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "clne" RESETLOGS ARCHIVELOG
  MAXLOGFILES     50
  MAXLOGMEMBERS      5
  MAXDATAFILES      100
  MAXINSTANCES     1
  MAXLOGHISTORY      226
 LOGFILE
  GROUP  1 ( '/u01/ora9/oradata/clne/redo01.log' ) SIZE  104857600  REUSE,
  GROUP  2 ( '/u01/ora9/oradata/clne/redo02.log' ) SIZE  104857600  REUSE,
  GROUP  3 ( '/u01/ora9/oradata/clne/redo03.log' ) SIZE  104857600  REUSE
 DATAFILE
  '/u01/ora9/oradata/clne/system01.dbf'
 CHARACTER SET ZHS16GBK

printing stored script. Memory Script
{
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/undotbs01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/cwmlite01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/drsys01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/example01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/indx01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/odm01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/tools01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/users01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/xdb01.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/aams001.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/jira001.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/icms001.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/bsa001.dbf";
   catalog clone datafilecopy  "/u01/ora9/oradata/clne/ses001.dbf";
   switch clone datafile all;
}
executing script. Memory Script

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/undotbs01.dbf recid=1 stamp=780149403

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/cwmlite01.dbf recid=2 stamp=780149403

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/drsys01.dbf recid=3 stamp=780149403

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/example01.dbf recid=4 stamp=780149403

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/indx01.dbf recid=5 stamp=780149403

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/odm01.dbf recid=6 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/tools01.dbf recid=7 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/users01.dbf recid=8 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/xdb01.dbf recid=9 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/aams001.dbf recid=10 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/jira001.dbf recid=11 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/icms001.dbf recid=12 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/bsa001.dbf recid=13 stamp=780149404

cataloged datafile copy
datafile copy filename=/u01/ora9/oradata/clne/ses001.dbf recid=14 stamp=780149404

datafile 11 switched to datafile copy
input datafilecopy recid=10 stamp=780149404 filename=/u01/ora9/oradata/clne/aams001.dbf
datafile 12 switched to datafile copy
input datafilecopy recid=11 stamp=780149404 filename=/u01/ora9/oradata/clne/jira001.dbf
datafile 14 switched to datafile copy
input datafilecopy recid=12 stamp=780149404 filename=/u01/ora9/oradata/clne/icms001.dbf
datafile 15 switched to datafile copy
input datafilecopy recid=13 stamp=780149404 filename=/u01/ora9/oradata/clne/bsa001.dbf
datafile 16 switched to datafile copy
input datafilecopy recid=14 stamp=780149404 filename=/u01/ora9/oradata/clne/ses001.dbf
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=780149403 filename=/u01/ora9/oradata/clne/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=780149403 filename=/u01/ora9/oradata/clne/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=780149403 filename=/u01/ora9/oradata/clne/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=780149403 filename=/u01/ora9/oradata/clne/example01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=780149403 filename=/u01/ora9/oradata/clne/indx01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=780149404 filename=/u01/ora9/oradata/clne/odm01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=780149404 filename=/u01/ora9/oradata/clne/tools01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=780149404 filename=/u01/ora9/oradata/clne/users01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=780149404 filename=/u01/ora9/oradata/clne/xdb01.dbf

printing stored script. Memory Script
{
   Alter clone database open resetlogs;
}
executing script. Memory Script

database opened
Finished Duplicate Db at 09-APR-12

RMAN>

 

4.為temp新增資料檔案

alter tablespace temp add tempfile '/u01/ora9/oradata/clne/temp001.dbf size 2000m;