Small change, big difference – Part 2

A couple of days ago I wrote about snapshot cloning a non-CDB database in Oracle 12.1.0.2.

It is even easier with CDB/PDB. And I think this is what it is really made for. You need only one line of code to create a snapshot copy. For this example I use the same machine as I did in the previous example, that means all the database files are on an EXT4 filesystem.

SQL> show parameter clone

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
clonedb                              boolean                           TRUE

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ ONLY  NO

SQL> create pluggable database my12c from pdb12c snapshot copy;

Pluggable database created.

SQL> alter pluggable database my12c open read write;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ ONLY  NO
         4 MY12C                          READ WRITE NO

[oracle@oel6u4 CDB12C]$ du -k 1242*/datafile/*
501772  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_sysaux_bkb5v3vq_.dbf
256012  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
56      12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_temp_bkb5v3vs_.dbf
5128    12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_users_bkb5zyhh_.dbf
16      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_sysaux_bkb6r742_.dbf
140     12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
56      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_temp_bkb6r750_.dbf
16      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_users_bkb6r750_.dbf

Here we go, a new PDB which is a snapshot copy of the initial PDB. It’s as easy as that.

You can even make the original PDB read-write again and continue.

SQL> alter pluggable database pdb12c close;

Pluggable database altered.

SQL> alter pluggable database pdb12c open read write;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ WRITE NO
         4 MY12C                          READ WRITE NO

Create some stuff in PDB12c:

SQL> alter session set container=pdb12c;

Session altered.

SQL> create user marco identified by marco quota unlimited on users;

User created.

SQL> grant create table to marco;

Grant succeeded.

SQL> grant create session to marco;

Grant succeeded.

SQL> conn marco/marco@localhost/pdb12c
Connected.
SQL> create table mytab as select owner, object_name, object_type from all_objects;

Table created.

Go to MY12C check if things are there and create other stuff inside this MY12C PDB.

SQL> alter session set container=my12c;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 MY12C                          READ WRITE NO

SQL> select username from dba_users where username='MARCO';

no rows selected

SQL> create user marco2 identified by marco2 quota unlimited on users;

User created.

SQL> grant create table to marco2;

Grant succeeded.

SQL> grant create session to marco2;

Grant succeeded.

SQL> conn marco2/marco2@localhost/my12c
Connected.
SQL> create table mytab2 as select owner, object_name, object_type from all_objects;

Table created.

The two PDBs seem to go different directions which is fine. The datafiles are growing too, obvious.

[oracle@oel6u4 CDB12C]$ du -k 1242*/datafile/*
522252  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_sysaux_bkb5v3vq_.dbf
256012  12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
56      12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_temp_bkb5v3vs_.dbf
6408    12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_users_bkb5zyhh_.dbf
21132   12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_sysaux_bkb6r742_.dbf
2236    12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
56      12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_temp_bkb6r750_.dbf
3756    12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_users_bkb6r750_.dbf

But I have no idea how Oracle manages to keep track of the changes. Maybe I just don’t see it because it’s Friday… Any ideas?

Update 04-01-2015:

Jean-Christophe commented on this and I tested the other option of creating a PDB as a copy. Let’s see:

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------
[...]
/u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
[...]
/u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
[...]

12 rows selected.

SQL> !ls -l /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
-rw-r-----. 1 oracle oinstall 262152192 Apr  1 08:48 /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb                            5v3h9_.dbf

SQL> !ls -l /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
-rw-r-----. 1 oracle oinstall 262152192 Apr  1 08:49 /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb                            6r71x_.dbf

SQL> !du -k /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
256012  /u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf

SQL> !du -k /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
2580    /u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf

SQL> alter pluggable database pdb12c close;

Pluggable database altered.

SQL> alter pluggable database pdb12c open read only;

Pluggable database altered.

SQL> create pluggable database copy12c from pdb12c;

Pluggable database created.

SQL> alter pluggable database copy12c open;

Pluggable database altered.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------
[...]
/u01/app/oracle/oradata/CDB12C/12421225AA410EA8E0530100007F9A1A/datafile/o1_mf_system_bkb5v3h9_.dbf
[...]
/u01/app/oracle/oradata/CDB12C/12424A2D6A4710C5E0530100007F0ACE/datafile/o1_mf_system_bkb6r71x_.dbf
[...]
/u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf
[...]

15 rows selected.

SQL> !ls -l /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf
-rw-r-----. 1 oracle oinstall 262152192 Apr  1 09:31 /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf

SQL> !du -k /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf
256008  /u01/app/oracle/oradata/CDB12C/12A5A5AA9D190979E0530100007F2A73/datafile/o1_mf_system_bkq7hbd2_.dbf

The first SYSTEM datafile in the list is the one of my initial PDB12C pluggable database. The file size is about 250M. The second SYSTEM datafile belongs to the snapshot copy MY12C. “ls -l” shows a size of 250M but the file only allocates 2.5M as “du -k” shows. Now the third SYSTEM datafile is part of the copied COPY12C and as it is a copy it really about 250M in size.

Advertisements

Small change, big difference

Did you notice this small change in the “New Features Guide” of 12.1.0.2 database?

With the initialization parameter CLONEDB set to true, snapshot clones of a pluggable database are supported on any local, Network File Storage (NFS) or clustered file systems with Oracle Direct NFS (dNFS) enabled. The source of the clone must remain read-only while the target needs to be on a file system that supports sparseness.

Any local filesystems? Great. But why is it restricted to PDBs only? In fact it isn’t. It also works for non-CDB architecture. The only difference is, you need to do a lot more steps. Actually it is pretty much the same as Direct-NFS-cloning.

This my VM for testing and it only has a local filesystem which is ext4.

[oracle@oel6u4]$ mount
/dev/mapper/vg_oel6u4-lv_root on / type ext4 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw,rootcontext="system_u:object_r:tmpfs_t:s0")
/dev/sda1 on /boot type ext4 (rw)
/dev/mapper/vg_data-lv_data on /u01 type ext4 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

[oracle@oel6u4]$ df .
Filesystem           1K-blocks     Used Available Use% Mounted on
/dev/mapper/vg_data-lv_data
                      51606140 16905488  32079212  35% /u01

All I need to do is take an image copy of the database.

RMAN> backup as copy database;

Then create a controlfile backup as text.

SQL> alter database backup controlfile to trace as '/tmp/db12c.ctl';

Database altered.

I need to change the RESETLOGS section a bit, create new redolog groups and use the image copies as datafiles:

CREATE CONTROLFILE REUSE SET DATABASE "CLONE12C" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/CLONE12C/redo01.rdo'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/CLONE12C/redo02.rdo'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_system_bhv010hk_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_sysaux_bhv03ztl_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_undotbs1_bhv09yrm_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_example_bhtzxq18_.dbf',
  '/u01/app/oracle/fra/DB12C/datafile/o1_mf_users_bhv06zdm_.dbf'
CHARACTER SET WE8MSWIN1252
;

Last thing I need is a new init.ora for my clone database:

audit_file_dest='/u01/app/oracle/admin/clone12c/adump'
audit_trail ='db'
clonedb = TRUE
compatible ='12.1.0.2.0'
control_files = (/u01/app/oracle/oradata/CLONE12C/control.dbf)
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_name='CLONE12C'
db_recovery_file_dest_size=2G
db_recovery_file_dest='/u01/app/oracle/fra'
diagnostic_dest='/u01/app/oracle'
pga_aggregate_target=128M
processes = 150
remote_login_passwordfile='EXCLUSIVE'
sga_target=800M
undo_tablespace='UNDOTBS1'

Now that I have that, I can start the instance and create our controlfile. Don’t forget to create the directories beforehand…

SQL> startup nomount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             230689520 bytes
Database Buffers          599785472 bytes
Redo Buffers                5455872 bytes

SQL> sta /tmp/create_control.sql

Control file created.

Now it is time to rename-clone the datafiles As I said, this is done using the DBMS_DNFS functionality:

SQL> begin
  2    dbms_dnfs.clonedb_renamefile(
  3      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_system_bhv010hk_.dbf',
  4      '/u01/app/oracle/oradata/CLONE12C/system_clone.dbf');
  5    dbms_dnfs.clonedb_renamefile(
  6      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_sysaux_bhv03ztl_.dbf',
  7      '/u01/app/oracle/oradata/CLONE12C/sysaux_clone.dbf');
  8    dbms_dnfs.clonedb_renamefile(
  9      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_undotbs1_bhv09yrm_.dbf',
 10      '/u01/app/oracle/oradata/CLONE12C/undotbs1_clone.dbf');
 11    dbms_dnfs.clonedb_renamefile(
 12      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_example_bhtzxq18_.dbf',
 13      '/u01/app/oracle/oradata/CLONE12C/example_clone.dbf');
 14    dbms_dnfs.clonedb_renamefile(
 15      '/u01/app/oracle/fra/DB12C/datafile/o1_mf_users_bhv06zdm_.dbf',
 16      '/u01/app/oracle/oradata/CLONE12C/users_clone.dbf');
 17  end;
 18  /

PL/SQL procedure successfully completed.

Now, last step is to recover and open-resetlogs the clone database. I only need to specify the propper archivelogs.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2224557 generated at 03/09/2015 12:10:46 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/CLONE12C/archivelog/2015_03_09/o1_mf_1_33_%u_.arc
ORA-00280: change 2224557 for thread 1 is in sequence #33


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fra/DB12C/archivelog/2015_03_09/o1_mf_1_33_bhv29dgb_.arc
ORA-00279: change 2225737 generated at 03/09/2015 12:51:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/CLONE12C/archivelog/2015_03_09/o1_mf_1_34_%u_.arc
ORA-00280: change 2225737 for thread 1 is in sequence #34
ORA-00278: log file
'/u01/app/oracle/fra/DB12C/archivelog/2015_03_09/o1_mf_1_33_bhv29dgb_.arc' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

That’s it. This is my clone database now using sparse files.

[oracle@oel6u4]$ cd /u01/app/oracle/oradata/CLONE12C/
[oracle@oel6u4]$ ll
-rw-r-----. 1 oracle oinstall 10371072 Mar  9 12:55 control.dbf
-rw-r-----. 1 oracle oinstall 1304174592 Mar  9 12:51 example_clone.dbf
-rw-r-----. 1 oracle oinstall 52429312 Mar  9 12:55 redo01.rdo
-rw-r-----. 1 oracle oinstall 52429312 Mar  9 12:51 redo02.rdo
-rw-r-----. 1 oracle oinstall  692068352 Mar  9 12:52 sysaux_clone.dbf
-rw-r-----. 1 oracle oinstall  838868992 Mar  9 12:51 system_clone.dbf
-rw-r-----. 1 oracle oinstall   73408512 Mar  9 12:51 undotbs1_clone.dbf
-rw-r-----. 1 oracle oinstall  690757632 Mar  9 12:51 users_clone.dbf
[oracle@oel6u4]$ du -h *
9.9M    control.dbf
16K     example_clone.dbf
51M     redo01.rdo
51M     redo02.rdo
580K    sysaux_clone.dbf
380K    system_clone.dbf
596K    undotbs1_clone.dbf
16K     users_clone.dbf

Ok, a little more work than “create pluggable database … snapshot copy …” but it works. And it is on a local filesystem.

And never ever delete the image copies 😉