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