[FAQ]-使用duplicate克隆数据库

发表于 2015 年 4 月 16 日 由 Lunar

联系：QQ(5163721)

标题：[FAQ]-使用duplicate克隆数据库

作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

帮一个朋友整理的，顺便发到blog。
.
使用duplicate克隆数据库： 将宝宝数据库科隆为Lunar数据库
这部分东西不是新东西，clone在8i就是可以手工做的，在9i还是10g（忘记了），就被封装成duplicat命令了。
之前的测试参见无所不能的duplicate–克隆数据库
.
不过一般我更喜欢手工用rman做，每一步都透明，每一步都可控可回退可追查可修改。
duplicate自动化了，但是出问题后就是整体全部重来，万一有问题，比较耽误时间。
具体步骤如下：
在备库创建目录：

mkdir -p /u01/app/oracle/admin/lunar/adump
mkdir -p /u01/app/oracle/admin/lunar/bdump
mkdir -p /u01/app/oracle/admin/lunar/cdump
mkdir -p /u01/app/oracle/admin/lunar/udump
mkdir -p /u01/app/oracle/oradata/lunar
mkdir -p /u01/app/oracle/diag/rdbms/lunar/lunar
mkdir -p /u01/app/oracle/oradata/lunar

宝宝数据库的参数文件：

*.audit_file_dest='/u01/app/oracle/admin/lunar/adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/lunar/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='bb'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=121634816
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=529145600
*.undo_tablespace='UNDOTBS1'
*.job_queue_processes=10
*.db_unique_name='bb'
*.log_archive_dest_1='location=/u01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lunar'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_max_processes=5

Lunar数据库使用的参数文件：

*.audit_file_dest='/u01/app/oracle/admin/lunar/adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/lunar/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='lunar'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=121634816
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=529145600
*.undo_tablespace='UNDOTBS1'
*.job_queue_processes=10
*.db_unique_name='lunar'
*.log_archive_dest_1='location=/u01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lunar'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_max_processes=5

创建监听：

[oracle@lunar admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.66)(PORT = 1521))
    )
  )
  
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = bb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
      (SID_NAME = bb)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = lunar)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
      (SID_NAME = lunar)
    )
   )
  )

  

  
ADR_BASE_LISTENER = /u01/app/oracle

创建tnsnames.ora文件

[oracle@lunar admin]$ 

bb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.66)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bb)
      (SID_NAME = bb)
    )
  )

lunar =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.99)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lunar)
      (SID_NAME = lunar)
    )
  )

lunar =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.66)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lunar)
      (SID_NAME = lunar)
    )
  )

重启监听
测试连接：


[oracle@lunar lunardb]$ . ~/lunar.env 
[oracle@lunar lunardb]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 00:53:25 2013

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

Connected to an idle instance.

SQL> startup nomount pfile=/test/lunardb/pfilelunar.ora
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2230024 bytes
Variable Size             163580152 bytes
Database Buffers          356515840 bytes
Redo Buffers                7962624 bytes
SQL> 

[oracle@lunar ~]$ . bb.env 
[oracle@lunar ~]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 01:03:35 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect sys/oracle@bb as sysdba
Connected.
SQL> connect sys/oracle@lunar as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

创建口令文件：


[oracle@lunar admin]$ cd $ORACLE_HOME/dbs
[oracle@lunar dbs]$ ls
10o0goff_1_1  11o0gogi_1_1  hc_bb.dat  hc_lunar.dat  init.ora  lkBB  orapwbb  snapcf_bb.f  spfilebb.ora
[oracle@lunar dbs]$ cp orapwbb orapwlunar
[oracle@lunar dbs]$

开始duplicate：

[oracle@lunar lunar]$ . ~/lunar.env 
[oracle@lunar lunar]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 01:36:58 2013

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

Connected to an idle instance.

SQL> startup nomount pfile=/test/lunardb/pfilelunar.ora
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2230024 bytes
Variable Size             163580152 bytes
Database Buffers          356515840 bytes
Redo Buffers                7962624 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lunar lunar]$ rman target sys/oracle@bb auxiliary sys/oracle@lunar

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 28 01:37:25 2013

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

connected to target database: BB (DBID=2078073270)
connected to auxiliary database: lunar (not mounted)

RMAN> run{
2> set newname for datafile 1 to '/test/lunardb/SYSTEM01.DBF';
3> set newname for datafile 2 to '/test/lunardb/undotbs01.DBF';
4> set newname for datafile 3 to '/test/lunardb/sysaux01.DBF';
5> set newname for datafile 4 to '/test/lunardb/users01.DBF';
6> set newname for datafile 5 to '/test/lunardb/temp01.DBF';
7> duplicate target database to lunar nofilenamecheck logfile
8> '/test/lunardb/redo01.log' size 10m,
9> '/test/lunardb/redo02.log' size 10m,
10> '/test/lunardb/redo03.log' size 10m;
11> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 28-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 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     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''BB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''lunar'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes

Starting restore at 28-JAN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/11o0gogi_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/11o0gogi_1_1 tag=TAG20130128T004847
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/lunar/control01.ctl
Finished restore at 28-JAN-13

database mounted

contents of Memory Script:
{
   set until scn  330514;
   set newname for datafile  1 to 
 "/test/lunardb/SYSTEM01.DBF";
   set newname for datafile  2 to 
 "/test/lunardb/undotbs01.DBF";
   set newname for datafile  3 to 
 "/test/lunardb/sysaux01.DBF";
   set newname for datafile  4 to 
 "/test/lunardb/users01.DBF";
   restore
   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

Starting restore at 28-JAN-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /test/lunardb/SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to /test/lunardb/undotbs01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to /test/lunardb/sysaux01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to /test/lunardb/users01.DBF
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/10o0goff_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/10o0goff_1_1 tag=TAG20130128T004847
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 28-JAN-13

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

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=805858730 file name=/test/lunardb/SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=805858730 file name=/test/lunardb/undotbs01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=805858730 file name=/test/lunardb/sysaux01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=805858730 file name=/test/lunardb/users01.DBF

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

executing command: SET until clause

Starting recover at 28-JAN-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 43 is already on disk as file /u01/archive/1_43_805707062.dbf
archived log file name=/u01/archive/1_43_805707062.dbf thread=1 sequence=43
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-JAN-13
Oracle instance started

Total System Global Area     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''lunar'' 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 =  ''lunar'' 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     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "lunar" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 '/test/lunardb/redo01.log' SIZE 10 M ,
  GROUP   2 '/test/lunardb/redo02.log' SIZE 10 M ,
  GROUP   3 '/test/lunardb/redo03.log' SIZE 10 M 
 DATAFILE
  '/test/lunardb/SYSTEM01.DBF'
 CHARACTER SET AL32UTF8


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

executing command: SET NEWNAME

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

cataloged datafile copy
datafile copy file name=/test/lunardb/undotbs01.DBF RECID=1 STAMP=805858744
cataloged datafile copy
datafile copy file name=/test/lunardb/sysaux01.DBF RECID=2 STAMP=805858744
cataloged datafile copy
datafile copy file name=/test/lunardb/users01.DBF RECID=3 STAMP=805858744

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=805858744 file name=/test/lunardb/undotbs01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=805858744 file name=/test/lunardb/sysaux01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=805858744 file name=/test/lunardb/users01.DBF
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data
Executing: alter database force logging

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

database opened
Finished Duplicate Db at 28-JAN-13

RMAN>

克隆完成，创建临时文件：

[oracle@lunar lunar]$ . ~/lunar.env 
[oracle@lunar lunar]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 01:40:32 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select NAME,OPEN_MODE from v$database;

NAME               OPEN_MODE
------------------ ----------------------------------------
lunar             READ WRITE

SQL> 
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/bb/temp01.dbf' DROP  INCLUDING DATAFILES;

Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/test/lunardb/temp01.DBF' SIZE 10M;

Tablespace altered.

SQL>
此条目发表在 backup&recovery 分类目录，贴了 , 标签。将固定链接加入收藏夹。

《[FAQ]-使用duplicate克隆数据库》有 1 条评论

  1. Eric Ma 说：
    2015 年 4 月 18 日21:57

    好文，收藏了。

    回复

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注