Cloning a pluggable database in 12c

I have created a CDB using Oracles database configuration assistant (dbca).
While installing, I choosed to create a Pluggable database, too, called pdbvegdb01.

If you want to make a clone of a PDB, it's a relative simple process.


1. Check your current setup by connecting to the root container (CDB$ROOT), and issue the following query:

select C.PDB_ID,C.PDB_NAME,C.STATUS,C.CON_ID "container id", P.OPEN_MODE,P.restricted
from CDB_PDBS C inner join V$PDBS P
on C.CON_ID = P.CON_ID
order by c.con_id;

PDB_ID	PDB_NAME	STATUS	container id	OPEN_MODE	RESTRICTED
2	PDB$SEED	NORMAL	2	READ ONLY	NO
3	PDBVEGDB01	NORMAL	3	READ WRITE	NO

I would like to clone the PDBVEGDB01, to a new one, that I will name vpdb01.

2. Check if the root container uses OMF (Oracle Managed Files)

select name,value,DESCRIPTION 
from V$SYSTEM_PARAMETER 
WHERE NAME in( 'db_create_file_dest');

NAME	VALUE	DESCRIPTION
db_create_file_dest	/u02/oradata	default database location

It does, and that means your new PDB will inherit this setting.

In this example, I would NOT like Oracle to manage the files in my PDB.
I want to place them in a folder called /u02/oradata/VPDB01.

3. Make directory on server:

mkdir -p /u02/oradata/VPDB01

4. Check the names and paths of the files being used by the source PDB:

select FILE_NAME from CDB_DATA_FILES where CON_ID=3
union
select file_name from cdb_temp_files where con_id=3;

The result of this union is:

FILE_NAME
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_system_d4tytlns_.dbf
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_sysaux_d4tytlo2_.dbf
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_temp_d4tytlo2_.dbf
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_users_d4tyw5y9_.dbf

All the above files must be accounted for in your clone script, when deciding not to continue using OMF.

5. If not already done, switch to the root container:

alter session set container = CDB$ROOT;

6. Close the source pdb, and open it read only:

alter PLUGGABLE database pdbvegdb01 CLOSE;
alter PLUGGABLE database pdbvegdb01 OPEN READ ONLY;

7. Clone the source pdb. Notice that I have matched the source pdb's datafiles, one by one, with a file name of my choosing:

create PLUGGABLE DATABASE VPDB01
from pdbvegdb01
FILE_NAME_CONVERT=(
'/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_system_d4tytlns_.dbf',
'/u02/oradata/VPDB01/system01.dbf',
'/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_sysaux_d4tytlo2_.dbf',
'/u02/oradata/VPDB01/sysaux01.dbf',
'/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_temp_d4tytlo2_.dbf',
'/u02/oradata/VPDB01/temp01.dbf'
'/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_users_d4tyw5y9_.dbf,
'/u02/oradata/VPDB01/users01.dbf'
);

Your sqlplus session should return "pluggable DATABASE created."

8. Open the source pdb for read/write:

alter pluggable database pdbvegdb01 close;
alter pluggable database pdbvegdb01 open read write;

You now see that your freshly cloned database is in status NEW and is MOUNTED, but not open:

PDB_ID	PDB_NAME	STATUS	container id	OPEN_MODE	RESTRICTED
2	PDB$SEED	NORMAL	2	READ ONLY	NO
3	PDBVEGDB01	NORMAL	3	READ WRITE	NO
6	VPDB01	NEW	6	MOUNTED

9. Open the pdb by switching your session container to the newly cloned pdb

alter session set container=vpdb01;

Just to verify, check the open mode of the container.

select CON_ID,DBID,name,OPEN_MODE 
from v$containers order by con_id;

You should have one row worth of output, since you switched from the root container to the pdb container:

CON_ID	DBID	NAME	OPEN_MODE
6	2049231443	VPDB01	MOUNTED

10. Open the pluggable database:

alter pluggable database vpdb01 open;

For the last step to work, you strictly don't need to switch container, it can be done while you have your session set to the root container as well.
But it demonstrates the ability for the DBA to switch containers and work isolated with them, as though they were separate databases.
Finally, let's switch back to the root container and check the status of our PDB's:

alter session set container = CDB$ROOT;

select C.PDB_ID,C.PDB_NAME,C.STATUS,C.CON_ID "container id", P.OPEN_MODE,P.restricted
from CDB_PDBS C inner join V$PDBS P
on C.CON_ID = P.CON_ID
order by c.con_id;

Result:

PDB_ID	PDB_NAME	STATUS	container id	OPEN_MODE	RESTRICTED
2	PDB$SEED	NORMAL	2	READ ONLY	NO
3	PDBVEGDB01	NORMAL	3	READ WRITE	NO
4	VPDB01	NORMAL	4	READ WRITE	NO

How to create a line-by-line comparison between the parameters used in two different databases

Here is a simple way to create a line-by-line comparison between two databases over a database link.

First make sure there is tns Connectivity between the database you execute the script in, and the remote database.

Then, create a datatabase link:

-- create db link from prod to test
create public database link testdb
connect to system
identified by ****
using 'testdb';

-- make sure that the link works before you proceed:
select host_name from v$instance@testdb;

The following script will spool a text file to your current directory, which can be investigated for differences between the two databases' initialization parameters:

set trimspool on
spool parameter_comparison.lst
set lines 200
col name format a40
col "test value" format a50
col "prod value" format a50
select prod.name,prod.value "prod value",test.value "test value"
from v$system_parameter prod full outer join v$system_parameter@testdb test
on prod.name = test.name;

exit

How to find the last password change a user made

Limit the search to those users who changed their password today:

select name,ctime "created", ptime "passw change time",ltime "last locked", lcount "# failed logins"
 from user$
 where trunc(ptime) = trunc(sysdate)
 order by 3 desc;

Example output:

NAME	created	passw change time	last locked	# failed logins
JIM	25.11.2016 02:25:38	12.12.2016 09:59:25		0
JOHN	25.11.2016 02:25:55	12.12.2016 09:53:19		0
JAMES	25.11.2016 02:25:54	12.12.2016 09:29:50		0

How to find dependencies between views and the tables they're based upon

Use the following Query to list the base tables on which the

select o.object_type,o.owner,o.object_name, CASE WHEN D.REFERENCED_NAME = O.OBJECT_NAME THEN NULL
                                            ELSE D.REFERENCED_NAME || '  --> ' || d.referenced_type 
                                            END "dependency to" 
from dba_objects o join dba_dependencies d
on o.object_name = d.name
where object_name in 
(
'MYVIEW1', 
'MYVIEW2',
) 
and object_type <> 'SYNONYM'
order by object_type desc, "dependency to";

Is it possible to temporarily change the system time on my server, without the Oracle instance complaining?

Yes, it's possible. Oracle doesn't care about the wall-clock time, and will keep the redo log stream intact, regardless of what time the hosting server is set to.

The whole procedure is quite simple:

1. shutdown the database and listener

sqlplus / as sysdba
shutdown immediate
exit
lsnrctl stop

2. as root, change the system time. In my example, I am setting it to 10 days into the future:

Thu Dec 15 12:27:00 CET 2016
  [root@myserver ~]# date
  Thu Dec 15 12:27:01 CET 2016

3. Disable NTP på serveren:

  service ntpd stop

4. Open the instance and the listener

sqlplus / as sysdba
startup
exit
lsnrctl start

5. Verify that the database has picked up the new system time

SQL> select startup_time from v$instance;

STARTUP_TIME
-------------------
15.12.2016 12:28:17

6. Have a look at the archived logs:

select name,sequence#, archived,applied, status, completion_time from v$archived_log;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION_TIME
------------------------------------------------------------------------- ---------- --------- ---------- --- -------------------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016 11:50:48
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016 11:50:59

Force a log switch and note the time stamp of the last log:

alter system archive log current;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION_TIME
------------------------------------------------------------------------- ---------- --------- ---------- --- -------------------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016 11:50:48
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016 11:50:59
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc  12540 YES       NO         A   15.12.2016 12:33:06 

Change back again:

7. shutdown the database and the listener

sqlplus / as sysdba
shutdown immediate
exit
lsnrctl stop

8. enable ntp to have the server syncronized with the current time:

service ntpd start

9. Open the database

sqlplus / as sysdba
startup
exit
lsnrctl start

10. Check the archived logs again. Note that the log sequence doesn't care what the registered time was at the time of the log switch:

select name,sequence#, archived,applied, status, completion_time from v$archived_log;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION
------------------------------------------------------------------------- ---------- --------- ---------- --- ----------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc  12540 YES       NO         A   15.12.2016

alter system archive log current;

select name,sequence#, archived,applied, status, completion_time from v$archived_log;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION
------------------------------------------------------------------------- ---------- --------- ---------- --- ----------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc  12540 YES       NO         A   15.12.2016 
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12541__1480937880624137_.arc  12541 YES       NO         A   05.12.2016 

How to find the page size for a Linux installation

In order to set one of the mandatory kernel parameters in a Red Hat flavored Linux distribution, you need to know the page size.
To find it:

[root@myserver]$ getconf PAGESIZE
4096

How to install and use the character set scanner

Although deprecated since the release of DMU (Database Migration utility for Unicode), there are still cases where you need to run csscan.

One such incident happened to me when migrating a database used for Oracles E-Business Suite.

Some errors were found in DMU, but when clicking on the number indicating the number of rows with errors, the Cleansing Editor showed up empty. To get the rowids of the offending rows, I had to resort to the character scanner.

If not installed do as follows:

1. edit the script $ORACLE_HOME/rdbms/admin/csminst.sql so that it has a valid password, and that its object will be saved in a separate tablespace

create user csmig identified by ***** account lock
/
alter user csmig default tablespace tools quota unlimited on tools
/

2. execute the script as sysdba

sqlplus / as sysdba @csminst.sql

That's it. The csscanner tool is now installed.

A simple example of usage would be when I scanned one - 1 - table.
First, I created a parameter file

userid='sys/**** as sysdba'
table=scott.emp
fromchar=WE8ISO8859P1
tochar=al32utf8
array=4096000
process=4
feedback=1000

Execute it as follows:

csscan parfile=myscan.par

This process creates three files for you:
1. scan.out - Screen log file
2. scan.err - individual exception report. This is where you would typically find your rows that contains errors
3. scan.txt - contains the Database Scan Summary Report.

Here's how my scan.err looked like after I scanned the table mentioned above:

Database Scan Individual Exception Report


[Database Scan Parameters]

Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  proddb01
Database Version               11.2.0.4.0
Scan type                      Selective tables
Scan CHAR data?                YES
Database character set         WE8ISO8859P1
FROMCHAR                       WE8ISO8859P1
TOCHAR                         al32utf8
Scan NCHAR data?               NO
Array fetch buffer size        4096000
Number of processes            4
Capture convertible data?      NO
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]


[Application data individual exceptions]

User  : SCOTT
Table : EMP
Column: ATTRIBUTE15
Type  : VARCHAR2(150)
Number of Exceptions         : 2
Max Post Conversion Data Size: 153

ROWID              Exception Type      Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAS/WmAHwAACI5ZAAC exceed column size   153 
AAS/WmAHwAACI5ZAAD exceed column size   153 
------------------ ------------------ ----- ------------------------------

As expected, the offending rows contained Scandinavian characters, which will use 2 bytes after conversion to a Unicode database. Therefore the text will no longer fit, and actions need to be taken by the data owner before the migration.

For more information, look up the official documentation here