assistant engineer

使用auxiliary

 

target DB

 

IP:172.18.2.233

 

SID:orcl

 

 

 

auxiliary DB

 

IP 172.18.2.234

 

sid:orcl

 

 

 

我這裡複製的目錄相同。如果不同的話需要在pfile裡面加入db_file_name_convert和log_file_name_convert.

 

1.在auxiliary 創建pfile檔案

 

test2-> echo initorcl.ora
initorcl.ora
test2->

 

只有一個參數 DB_NAME 即可開啟到nomount模式

 

2.創建password檔案

 

test2-> orapwd file=?/dbs/orapworcl password=oracle
test2->

 

3.要與主資料庫一樣的資料夾

 

test2-> cd oradata/
test2-> mkdir orcl
test2-> pwd
/u01/app/oracle/oradata
/u01/app/oracle/admin/orcl
test2-> mkdir adump dpdump pfile
test2-> mkdir fast_recovery_area

 

4.將auxiliary 開啟到nomount

 

test2-> sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 12 12:17:55 2016

 

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

 

Connected to an idle instance.

 

SQL> startup nomount
ORACLE instance started.

 

Total System Global Area  321548288 bytes
Fixed Size                  2252824 bytes
Variable Size             264245224 bytes
Database Buffers           50331648 bytes
Redo Buffers                4718592 bytes
SQL>

 

 

 

5.配置listener 和 tnsnames文件 (兩邊都要),需要用tnsping ping看看可以ping對方嗎?

 

test1-> vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

 

ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )
~

 

 

 

 

 

test1-> vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

 

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

 

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

 

6.開始 RMAN duplicate from active database

 

 

       如果目錄不同，在pfile裡加如下2個參數進行轉換：

 

              db_file_name_convert

 

              log_file_name_convert.

 

 

 

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
test2-> rman target sys/oracle@ORCL auxiliary sys/oracle@ORCL1

 

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 12 15:08:53 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORCL (DBID=1439438369)
connected to auxiliary database: ORCL (not mounted)

 

RMAN>  duplicate target database to orcl from active database nofilenamecheck;

 

Starting Duplicate Db at 2016/05/12 15:08:59
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=396 device type=DISK

 

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

 

sql statement: create spfile from memory

 

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

 

Oracle instance shut down

 

connected to auxiliary database (not started)
Oracle instance started

 

Total System Global Area     321548288 bytes

 

Fixed Size                     2252824 bytes
Variable Size                264245224 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4718592 bytes

 

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlorcl.dbf';
   alter clone database mount;
}
executing Memory Script

 

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

 

sql statement: alter system set  db_unique_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

 

Oracle instance shut down

 

Oracle instance started

 

Total System Global Area     321548288 bytes

 

Fixed Size                     2252824 bytes
Variable Size                264245224 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4718592 bytes

 

Starting backup at 2016/05/12 15:09:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20160512T150914 RECID=4 STAMP=911660954
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2016/05/12 15:09:15

 

database mounted

 

contents of Memory Script:
{
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/orcl/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/orcl/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/orcl/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/orcl/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/orcl/test.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/orcl/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/orcl/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/orcl/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/orcl/users01.dbf"   datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/orcl/test.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting backup at 2016/05/12 15:09:20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20160512T150920
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/orcl/system01.dbf tag=TAG20160512T150920
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20160512T150920
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/test.dbf
output file name=/u01/app/oracle/oradata/orcl/test.dbf tag=TAG20160512T150920
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/orcl/users01.dbf tag=TAG20160512T150920
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2016/05/12 15:10:25

 

sql statement: alter system archive log current

 

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_05_12/o1_mf_1_82_cm8c31yf_.arc" auxiliary format
 "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_82_911657633.dbf"   ;
   catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_82_911657633.dbf";
   switch clone datafile all;
}
executing Memory Script

 

Starting backup at 2016/05/12 15:10:25
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=82 RECID=10 STAMP=911661026
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_82_911657633.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2016/05/12 15:10:27

 

cataloged archived log
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_82_911657633.dbf RECID=10 STAMP=911661027

 

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=911661027 file name=/u01/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=911661027 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=911661027 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=911661027 file name=/u01/app/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=911661027 file name=/u01/app/oracle/oradata/orcl/test.dbf

 

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

 

executing command: SET until clause

 

Starting recover at 2016/05/12 15:10:27
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=396 device type=DISK

 

starting media recovery

 

archived log for thread 1 with sequence 82 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_82_911657633.dbf
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_82_911657633.dbf thread=1 sequence=82
media recovery complete, elapsed time: 00:00:01
Finished recover at 2016/05/12 15:10:30
Oracle instance started

 

Total System Global Area     321548288 bytes

 

Fixed Size                     2252824 bytes
Variable Size                264245224 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4718592 bytes

 

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

 

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Reset to original value by RMAN'' scope=spfile

 

sql statement: alter system reset  db_unique_name scope=spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)
Oracle instance started

 

Total System Global Area     321548288 bytes

 

Fixed Size                     2252824 bytes
Variable Size                264245224 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4718592 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/orcl/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/orcl/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/orcl/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf'
 CHARACTER SET AL32UTF8

 

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/orcl/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/orcl/sysaux01.dbf",
 "/u01/app/oracle/oradata/orcl/undotbs01.dbf",
 "/u01/app/oracle/oradata/orcl/users01.dbf",
 "/u01/app/oracle/oradata/orcl/test.dbf";
   switch clone datafile all;
}
executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file

 

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf RECID=1 STAMP=911661042
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf RECID=2 STAMP=911661042
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/users01.dbf RECID=3 STAMP=911661042
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcl/test.dbf RECID=4 STAMP=911661042

 

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=911661042 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=911661042 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=911661042 file name=/u01/app/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=911661042 file name=/u01/app/oracle/oradata/orcl/test.dbf

 

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

 

database opened
Finished Duplicate Db at 2016/05/12 15:10:48

 

 

完成了 可以去看複製的資料庫 資料是否有過去!!

 

注意事項-----------------------------------------------------------

注意：

如果target 和 Auxiliary庫的目錄結構相同，記得加上nofilenamecheck參數，不然會報如下錯誤：