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.

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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.