Thursday, 8 June 2017

Multitenant : PDBs With Different Time Zones to the CDB in Oracle Database 12c

Oracle allows you to specify the database timezone using a time zone name, listed in the V$TIMEZONE_NAMES view, or using a UTC offset (+/-HH:MI). For performance reasons, Oracle recommends setting the database time zone to UTC (0:00), as no conversion of time zones will be required.

The multitenant architecture allows you to specify a different database time zone for each pluggable database, with the time zone of the container database being used as the default.

  • Container Database (CDB) Level


Setting the timezone at the container database level is the same as setting it for a non-CDB instance. The CDB setting is the default for all pluggable databases.

Check the current time zone for the container database.

CONN / AS SYSDBA

SELECT dbtimezone FROM DUAL;

DBTIME
------
+00:00

SQL>

Reset the time zone using the ALTER DATABASE command to specify the new TIME_ZONE value. The database will need to be restarted for this to take effect.

CONN / AS SYSDBA

ALTER DATABASE SET TIME_ZONE='Europe/London';

SHUTDOWN IMMEDIATE;
STARTUP;
We can see the database time zone has been changed.

CONN / AS SYSDBA

SELECT dbtimezone FROM DUAL;

DBTIMEZONE
-------------
Europe/London

SQL>

  • Pluggable Database (PDB) Level


Setting the time zone in the pluggable database allows it to override the CDB setting.

Check the current time zone for the pluggable database.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

SELECT dbtimezone FROM DUAL;

DBTIME
------
-07:00

SQL>

Reset the time zone using the ALTER DATABASE command to specify the new TIME_ZONE value. The pluggable database will need to be restarted for this to take effect.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

ALTER DATABASE SET TIME_ZONE='US/Eastern';

SHUTDOWN IMMEDIATE;
STARTUP;

We can see the pluggable database time zone is different to the container database.

CONN / AS SYSDBA

SELECT dbtimezone FROM DUAL;

DBTIMEZONE
-------------
Europe/London

SQL>


ALTER SESSION SET CONTAINER = pdb1;

SELECT dbtimezone FROM DUAL;

DBTIMEZONE
----------
US/Eastern

SQL>