I personally really like CloneDB, a way to thin-clone an Oracle database over NFS. This can be quite interesting, and I wanted to update my blog for 12.1.0.2.3 (April PSU). Tim Hall has a good example for 11.2.0.2 and later with further references.
My setup is as follows:
- server3 (Oracle Linux 7.1) uses Oracle Restart and has database CDB1 registered. I would like to use this as the source for the clone
- The backup I will take of CDB1 resides in /u01/oraback/CDB1
- /u01/oraback is NFS-exported on server3 to server4 (Oracle Linux 7.1)
- This directory is mounted on server4 as /u01/oraback
- Oracle 12.1.0.2.3 (April 2015 PSU) is used throughout
- The Oracle accounts on server3 and server4 have been created using the preinstall RPM, and have the same user and group IDs. You will find this important, and your Oracle installation standards document should enforce common user and group IDs not only for RAC where this is mandatory
Step 1: backing up CDB1
In the first step I am taking a full cold backup of CDB1. Data files are in ASM, and there is 1 PDB defined in the CDB. Here is the script I used for the backup:
[oracle@server3 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 4 11:15:11 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=853309103, not open)
RMAN> run {
2> allocate channel c1 device type disk format '/u01/oraback/CDB1/%U';
3> allocate channel c2 device type disk format '/u01/oraback/CDB1/%U';
4> set nocfau;
5> backup as copy database;
6> }
allocated channel: c1
channel c1: SID=15 device type=DISK
allocated channel: c2
channel c2: SID=252 device type=DISK
executing command: SET NOCFAU
Starting backup at 04-AUG-15
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/CDB1/DATAFILE/system.273.879591527
...
Finished backup at 04-AUG-15
released channel: c1
released channel: c2
CloneDB requires me to provide the init.ora file as well, which I also place on the NFS mount (“create pfile=’/u01/oraback/CDB1/initCDB1.orig’ from spfile” does the trick)
That should be enough for now, I can resume work on server4.
Setup of server4
The first step is to ensure that dNFS is set up. Instead of repeating myself here I’ll simply link to a previous post where I explained how I did this for 12c. The actual line in /etc/fstab is this:
server3:/u01/oraback/ /u01/oraback nfs rw,bg,hard,nointr,tcp,vers=3,timeo=600,rsize=32768,wsize=32768 1 2
Check MOS and your NFS appliance vendor’s support site for your specific settings.
The oranfstab’s contents is shown here for reference:
[oracle@server4 lib]$ cat $ORACLE_HOME/dbs/oranfstab server: server1 local: 192.168.100.13 path: 192.168.100.12 export: /u01/oraback mount: /u01/oraback
Prepare for cloning
The clonedb script requires a few environment variables to be set, as in this example:
export MASTER_COPY_DIR=/u01/oraback/CDB1 export CLONE_FILE_CREATE_DEST=/u01/oradata/CLONEDB export CLONEDB_NAME=CLONEDB
The MASTER_COPY_DIR indicates where the backup resides, the CLONE_FILE_CREATE_DEST marks the directory where the CLONEDB data files are going to be located, and finally the CLONEDB_NAME is the database name.
With that in place, directories created and permissions set properly, it’s time to call the script, and here’s the first surprise:
[oracle@server4 ~]$ perl $ORACLE_HOME/rdbms/install/clonedb.pl
Missing braces on \o{} at /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/install/clonedb.pl line 245, near "$lne' , '$clonedbdir"
I tried with $ORACLE_HOME/perl/bin/perl and the Oracle-Linux 7.1 version of perl and both showed the same behaviour (perl provided by the RDBMS home is v5.14.1, the default is v5.16.3). I corrected the script in line 245 and was able to produce the required scripts:
[oracle@server4 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/install/clonedb.pl \
> /u01/oraback/CDB1/initCDB1.orig /tmp/script1.sql /tmp/script2.sql
[oracle@server4 ~]$ cat /tmp/script1.sql
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
STARTUP NOMOUNT PFILE=/u01/oradata/CLONEDB/initCLONEDB.ora
CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXINSTANCES 1
MAXLOGHISTORY 908
LOGFILE
GROUP 1 '/u01/oradata/CLONEDB/CLONEDB_log1.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/CLONEDB/CLONEDB_log2.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4',
'/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc'
CHARACTER SET WE8DEC;
[oracle@server4 ~]$ cat /tmp/script2.sql
declare
begin
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB0.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB1.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB2.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB3.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB4.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl' , '/u01/oradata/CLONEDB//ora_data_CLONEDB5.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk' , '/u01/oradata/CLONEDB//ora_data_CLONEDB6.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB7.dbf');
dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc' , '/u01/oradata/CLONEDB//ora_data_CLONEDB8.dbf');
end;
/
show errors;
alter database open resetlogs;
drop tablespace TEMP;
create temporary tablespace TEMP;[oracle@server4 ~]$
[oracle@server4 tmp]$ cat /u01/oradata/CLONEDB/initCLONEDB.ora
CDB1.__data_transfer_cache_size=0
CDB1.__db_cache_size=478150656
CDB1.__java_pool_size=4194304
CDB1.__large_pool_size=125829120
CDB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
CDB1.__pga_aggregate_target=268435456
CDB1.__sga_target=805306368
CDB1.__shared_io_pool_size=0
CDB1.__shared_pool_size=188743680
CDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/CDB1/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
control_files=/u01/oradata/CLONEDB/CLONEDB_ctl.dbf
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
db_name=CLONEDB
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4560m
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1XDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'
db_create_file_dest=/u01/oradata/CLONEDB/
log_archive_dest=/u01/oradata/CLONEDB/
clonedb=TRUE
The scripts in that form won’t work- bear with me a second.
Fixing the scripts
First of all the init.ora script, it needs mending. The final initCLONEDB.ora, located in $CLONE_FILE_CREATE_DEST file looks like this (mandatory directories have to be created):
*.audit_file_dest='/u01/app/oracle/admin/CLONEDB/adump' *.audit_trail='db' *.compatible='12.1.0.2.0' *.control_files=/u01/oradata/CLONEDB/CLONEDB_ctl.dbf *.db_block_size=8192 *.db_domain='' *.db_name=CLONEDB *.db_recovery_file_dest='/u01/fra' *.db_recovery_file_dest_size=4560m *.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEDBXDB)' *.enable_pluggable_database=true *.open_cursors=300 *.pga_aggregate_target=256m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=768m *.undo_tablespace='UNDOTBS1' *.db_create_file_dest=/u01/oradata/CLONEDB/ *.log_archive_dest=/u01/oradata/CLONEDB/ *.clonedb=TRUE
I took out the ASM related parameters and made sure the FRA was usable. I also tidied everything up a little bit. Take a backup of this file, it will be overwritten next time you run clonedb.pl
Next check script1.sql for problems. I initially forgot to remove the reference to the init.ora I placed in /u01/oraback/CDB1 (I thought it was a good idea. It’s not):
SQL> @/tmp/script1 SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 80 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 100 SQL> SQL> STARTUP NOMOUNT PFILE=/u01/oradata/CLONEDB/initCLONEDB.ora ORACLE instance started. Total System Global Area 805306368 bytes Fixed Size 2929552 bytes Variable Size 318770288 bytes Database Buffers 478150656 bytes Redo Buffers 5455872 bytes SQL> CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS 2 MAXLOGFILES 32 3 MAXLOGMEMBERS 2 4 MAXINSTANCES 1 5 MAXLOGHISTORY 908 6 LOGFILE 7 GROUP 1 '/u01/oradata/CLONEDB/CLONEDB_log1.log' SIZE 100M BLOCKSIZE 512, 8 GROUP 2 '/u01/oradata/CLONEDB/CLONEDB_log2.log' SIZE 100M BLOCKSIZE 512 9 DATAFILE 10 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_04qdogkp', 11 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_06qdogmu', 12 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_05qdogmq', 13 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_03qdogkp', 14 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_09qdogpr', 15 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_08qdogon', 16 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_07qdogom', 17 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0aqdogpt', 18 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0bqdogqn', 19 '/u01/oraback/CDB1/initCDB1.orig' 20 CHARACTER SET WE8DEC; CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01565: error in identifying file '/u01/oraback/CDB1/initCDB1.orig' ORA-17503: ksfdopn:7 Failed to open file /u01/oraback/CDB1/initCDB1.orig ORA-27047: unable to read the header block of file Additional information: 7 Additional information: 210592808 Additional information: 140184619486368
Took me 5 minutes to work out that there isn’t a problem with the pfile for starting the database. There is a problem with the reference to the pfile in line 19…
In a second attempt I was luckier.
[oracle@server4 CLONEDB]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 4 16:09:50 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> @/tmp/script1 SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 80 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 100 SQL> SQL> STARTUP NOMOUNT PFILE=/u01/oradata/CLONEDB/initCLONEDB.ora ORACLE instance started. Total System Global Area 805306368 bytes Fixed Size 2929552 bytes Variable Size 318770288 bytes Database Buffers 478150656 bytes Redo Buffers 5455872 bytes SQL> CREATE CONTROLFILE REUSE SET DATABASE CLONEDB RESETLOGS 2 MAXLOGFILES 32 3 MAXLOGMEMBERS 2 4 MAXINSTANCES 1 5 MAXLOGHISTORY 908 6 LOGFILE 7 GROUP 1 '/u01/oradata/CLONEDB/CLONEDB_log1.log' SIZE 100M BLOCKSIZE 512, 8 GROUP 2 '/u01/oradata/CLONEDB/CLONEDB_log2.log' SIZE 100M BLOCKSIZE 512 9 DATAFILE 10 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb', 11 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5', 12 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5', 13 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb', 14 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4', 15 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl', 16 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk', 17 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4', 18 '/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc' 19 CHARACTER SET WE8DEC; Control file created. Elapsed: 00:00:02.22
Success! Now I need to continue with script2:
SQL> @/tmp/script2
SQL> declare
2 begin
3 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-3_0mqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB0.dbf');
4 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-7_0oqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB1.dbf');
5 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSAUX_FNO-9_0nqdp1n5' , '/u01/oradata/CLONEDB//ora_data_CLONEDB2.dbf');
6 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-1_0lqdp1mb' , '/u01/oradata/CLONEDB//ora_data_CLONEDB3.dbf');
7 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-5_0rqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB4.dbf');
8 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-SYSTEM_FNO-8_0qqdp1nl' , '/u01/oradata/CLONEDB//ora_data_CLONEDB5.dbf');
9 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-UNDOTBS1_FNO-4_0pqdp1nk' , '/u01/oradata/CLONEDB//ora_data_CLONEDB6.dbf');
10 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-10_0sqdp1o4' , '/u01/oradata/CLONEDB//ora_data_CLONEDB7.dbf');
11 dbms_dnfs.clonedb_renamefile('/u01/oraback/CDB1/data_D-CDB1_I-853309103_TS-USERS_FNO-6_0tqdp1oc' , '/u01/oradata/CLONEDB//ora_data_CLONEDB8.dbf');
12 end;
13 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.05
SQL> show errors;
No errors.
SQL> alter database open resetlogs;
Database altered.
Elapsed: 00:00:22.60
SQL> drop tablespace TEMP;
drop tablespace TEMP
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
Elapsed: 00:00:00.01
SQL> create temporary tablespace TEMP;
create temporary tablespace TEMP
*
ERROR at line 1:
ORA-01543: tablespace 'TEMP' already exists
Elapsed: 00:00:00.02
Not quite success but close. I added a temp file to the TEMP tablespace and was ready. A quick test revealed that I could open the PDB, too:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
Elapsed: 00:00:01.46
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
Sparse Files
The real beauty lies in the fact that I have space efficient snapshots:
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/oradata/CLONEDB/ora_data_CLONEDB3.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB0.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB6.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB4.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB8.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB1.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB5.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB2.dbf /u01/oradata/CLONEDB/ora_data_CLONEDB7.dbf 9 rows selected. Elapsed: 00:00:00.01 SQL> !ls -lsh /u01/oradata/CLONEDB/ total 211M 0 drwxr-x---. 4 oracle asmadmin 37 Aug 4 16:14 CLONEDB 8.9M -rw-r-----. 1 oracle asmadmin 8.9M Aug 4 16:16 CLONEDB_ctl.dbf 101M -rw-r-----. 1 oracle asmadmin 101M Aug 4 16:16 CLONEDB_log1.log 101M -rw-r-----. 1 oracle asmadmin 101M Aug 4 16:10 CLONEDB_log2.log 4.0K -rw-r--r--. 1 oracle oinstall 613 Aug 4 16:09 initCLONEDB.ora 4.0K -rw-r--r--. 1 oracle oinstall 613 Aug 4 16:00 initCLONEDB.ora.orig 460K -rw-r-----. 1 oracle asmadmin 761M Aug 4 16:15 ora_data_CLONEDB0.dbf 64K -rw-r-----. 1 oracle asmadmin 491M Aug 4 16:10 ora_data_CLONEDB1.dbf 64K -rw-r-----. 1 oracle asmadmin 521M Aug 4 16:15 ora_data_CLONEDB2.dbf 420K -rw-r-----. 1 oracle asmadmin 791M Aug 4 16:15 ora_data_CLONEDB3.dbf 64K -rw-r-----. 1 oracle asmadmin 251M Aug 4 16:10 ora_data_CLONEDB4.dbf 64K -rw-r-----. 1 oracle asmadmin 261M Aug 4 16:15 ora_data_CLONEDB5.dbf 540K -rw-r-----. 1 oracle asmadmin 361M Aug 4 16:15 ora_data_CLONEDB6.dbf 64K -rw-r-----. 1 oracle asmadmin 201M Aug 4 16:15 ora_data_CLONEDB7.dbf 64K -rw-r-----. 1 oracle asmadmin 5.1M Aug 4 16:10 ora_data_CLONEDB8.dbf
The first column in the output is the actual size on disk, for example 64k for ora_data_CLONEDB4.dbf. A “regular” ls output will show the file as 251M, reflected in the above output as well. It’s the original file size.
Martins Blog 