Oracle 12Cr2 Using CloneDB to clone a database

这里将介绍如何使用Oracle 12CR2的CloneDB功能来克隆一个数据库，生产库orcl的Oracle home目录为/u01/app/oracle/product/12.2.0/db，数据库的备份文件存储在/u01/app/oracle/backup/目录中,CloneDB库clonedb的oracle home目录也为/u01/app/oracle/product/12.2.0/db

操作步骤如下:
1.对生产库执行备份,因为生产库jy启用了归档所以执行联机备份，使用backup as copy来备份生产库的数据文件，只备份数据文件。

SQL> col name for a50 SQL> select file#,rfile#,name from v$datafile;       FILE#     RFILE# NAME ---------- ---------- --------------------------------------------------          1          1 +DATA/ORCL/DATAFILE/system.280.941831569          2          2 +DATA/ORCL/DATAFILE/sysaux.281.941831647          3          3 +DATA/ORCL/DATAFILE/undotbs1.282.941831677          4          4 +DATA/ORCL/DATAFILE/users.284.941831687  SQL> select name from v$tablespace;  NAME -------------------------------------------------- SYSTEM SYSAUX UNDOTBS1 TEMP USERS    RMAN> backup as copy database format '/u01/app/oracle/backup/%U';  Starting backup at 25-APR-17 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.280.941831569 output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSTEM_FNO-1_1vs2jnif tag=TAG20170425T210621 RECID=17 STAMP=942267998 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=+DATA/ORCL/DATAFILE/sysaux.281.941831647 output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSAUX_FNO-2_20s2jnj1 tag=TAG20170425T210621 RECID=18 STAMP=942268013 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/ORCL/DATAFILE/users.284.941831687 output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-USERS_FNO-4_21s2jnjj tag=TAG20170425T210621 RECID=19 STAMP=942268023 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/ORCL/DATAFILE/undotbs1.282.941831677 output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-UNDOTBS1_FNO-3_22s2jnjs tag=TAG20170425T210621 RECID=20 STAMP=942268033 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08 Finished backup at 25-APR-17  Starting Control File and SPFILE Autobackup at 25-APR-17 piece handle=/u01/app/oracle/product/12.2.0/db/dbs/c-1469612247-20170425-03 comment=NONE Finished Control File and SPFILE Autobackup at 25-APR-17 

2.创建pfile参数文件

SQL> create pfile='/u01/app/oracle/product/12.2.0/db/dbs/initclonedb.ora' from spfile;  File created. 

3.创建克隆库clonedb
3.1 手动生成创建CloneDB库的SQL脚本
3.1.1 以sysdba或sysbackup管理权限连接到生产库

[oracle@jytest3 ~]$ export ORACLE_SID=orcl [oracle@jytest3 ~]$ sqlplus / as sysdba  SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 24 19:56:40 2017  Copyright (c) 1982, 2016, Oracle.  All rights reserved.   Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 

3.1.2 执行以下命令来对生产库的控制文件生成备份

SQL> alter database backup controlfile to trace;  Database altered.  SQL> oradebug setmypid Statement processed.  SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22801.trc 

3.1.3 将生成的跟踪文件中的startup nomount与create controlfile语句复制到一个新创建的脚本中。

[oracle@jytest3 dbs]$ vi create_clonedb.sql STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 2     MAXDATAFILES 30     MAXINSTANCES 1     MAXLOGHISTORY 292 LOGFILE   GROUP 1 '+DATA/ORCL/ONLINELOG/group_1.278.941831529'  SIZE 100M BLOCKSIZE 512,   GROUP 2 '+DATA/ORCL/ONLINELOG/group_2.279.941831545'  SIZE 100M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE   '+DATA/ORCL/DATAFILE/system.280.941831569',   '+DATA/ORCL/DATAFILE/sysaux.281.941831647',   '+DATA/ORCL/DATAFILE/undotbs1.282.941831677',   '+DATA/ORCL/DATAFILE/users.284.941831687' CHARACTER SET ZHS16GBK ; 

3.1.4 统计图脚本create_clonedb.sql，将数据库名从orcl修改为clonedb,将日志文件目录从+data/orcl/onlinelog/修改为+data/clonedb/onlinelog，将数据文件目录从+data/orcl/datafile修改为备份目录/u01/app/oracle/backup/，修改之后的脚本内容如下:

STARTUP NOMOUNT pfile=/u01/app/oracle/product/12.2.0/db/dbs/initclonedb.ora  CREATE CONTROLFILE REUSE SET DATABASE clonedb RESETLOGS  ARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 2     MAXDATAFILES 30     MAXINSTANCES 1     MAXLOGHISTORY 292 LOGFILE   GROUP 1 '+data/clonedb/onlinelog/redo1.log'  SIZE 100M BLOCKSIZE 512,   GROUP 2 '+data/clonedb/onlinelog/redo2.log'  SIZE 100M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE   '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSTEM_FNO-1_1vs2jnif',   '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSAUX_FNO-2_20s2jnj1',   '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-USERS_FNO-4_21s2jnjj',   '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-UNDOTBS1_FNO-3_22s2jnjs' CHARACTER SET ZHS16GBK ; 

3.1.5 将生成的参数文件pfile复制到clonedb库所存储参数文件的位置，因为这里生产库与clonedb库的Oracle home目录相同，所以不用复制，这里需要参数文件修改为clonedb库所使用的相关参数，并且要增加CLONEDB=TRUE这个参数

[oracle@jytest3 dbs]$ vi initclonedb.ora orcl.__data_transfer_cache_size=0 orcl.__db_cache_size=427819008 orcl.__inmemory_ext_roarea=0 orcl.__inmemory_ext_rwarea=0 orcl.__java_pool_size=4194304 orcl.__large_pool_size=20971520 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=432013312 orcl.__sga_target=641728512 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=171966464 orcl.__streams_pool_size=0 *.control_files='+data/clonedb/controlfile/control01.ctl','+data/clonedb/controlfile/control02.ctl' *.db_create_file_dest='+DATA' *.db_name=clonedb *.log_archive_dest_1='location=+data/arch/clonedb/' *.memory_target=1G clonedb=true 

3.1.6 使用SQL*PLUS并以sysdba管理权限连接到clonedb库

[oracle@jytest3 ~]$ export ORACLE_SID=clonedb [oracle@jytest3 ~]$ sqlplus / as sysdba  SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 24 20:24:52 2017  Copyright (c) 1982, 2016, Oracle.  All rights reserved.  Connected to an idle instance. 

3.1.7 运行之前创建的create_clonedb.sql脚本

SQL> @/u01/app/oracle/product/12.2.0/db/dbs/create_clonedb.sql ORACLE instance started.  Total System Global Area 1073741824 bytes Fixed Size                  8628936 bytes Variable Size             629146936 bytes Database Buffers          427819008 bytes Redo Buffers                8146944 bytes  Control file created. 

3.1.8 对于在备份目录中的每个数据文件，执行dbms_dnfs.clonedb_renamefile过程,srcfile指定备份文件，destfile指定目标文件

SQL> BEGIN   2  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSTEM_FNO-1_1vs2jnif',destfile =>   '+DATA/clonedb/datafile/system.dbf');   3  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSAUX_FNO-2_20s2jnj1',destfile =>   '+DATA/clonedb/datafile/sysaux.dbf');   4  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-USERS_FNO-4_21s2jnjj',destfile => '+DATA/clonedb/datafile/users.dbf');   5  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-UNDOTBS1_FNO-3_22s2jnjs',destfile =>   '+DATA/clonedb/datafile/undotbs1.dbf');   6  END;   7  /  PL/SQL procedure successfully completed. 

4.如果使用联机备份创建clonedb库，那么需要对clonedb执行恢复操作。如果是使用的完全脱机备份或使用的是backup as copy备份，则不需要执行恢复操作，因为我这是使用的backup as copy备份。需要执行恢复，执行以下命令:

SQL> recover database using backup controlfile until cancel; ORA-00279: change 630593 generated at 04/25/2017 21:06:24 needed for thread 1 ORA-00289: suggestion : +DATA/arch/clonedb/1_22_941831511.dbf ORA-00280: change 630593 for thread 1 is in sequence #22   Specify log: {=suggested | filename | AUTO | CANCEL} 

这里需要查询生产库当前正在使用的联机重做日志文件

SQL> set long 300 SQL> set linesize 300 SQL> select * from v$log;      GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME        CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------ ----------          1          1         21  104857600        512          1 YES INACTIVE                627446 25-APR-17          630528 25-APR-17             0          2          1         22  104857600        512          1 NO  CURRENT                 630528 25-APR-17      1.8447E+19                       0  SQL> col member for a50 SQL> select * from v$logfile;      GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID ---------- ------- ------- -------------------------------------------------- --- ----------          1         ONLINE  +DATA/ORCL/ONLINELOG/group_1.278.941831529         NO           0          2         ONLINE  +DATA/ORCL/ONLINELOG/group_2.279.941831545         NO           0   +DATA/ORCL/ONLINELOG/group_2.279.941831545 Log applied. Media recovery complete. SQL>  

5.执行以下命令open数据库

SQL> alter database open resetlogs;  Database altered. 

到此克隆数据库的所要操作完成。