DBA Hub

📋Steps in this guide1/2

Multitenant : PDBs With Different Time Zones to the CDB in Oracle Database 12c Release 1 (12.1)

The multitenant architecture allows you to specify a different database time zone for each pluggable database.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

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. Reset the time zone using the command to specify the new value. The database will need to be restarted for this to take effect. We can see the database time zone has been changed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CONN / AS SYSDBA

SELECT dbtimezone FROM DUAL;

DBTIME
------
+00:00

SQL>

CONN / AS SYSDBA

ALTER DATABASE SET TIME_ZONE='Europe/London';

SHUTDOWN IMMEDIATE;
STARTUP;

CONN / AS SYSDBA

SELECT dbtimezone FROM DUAL;

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

SQL>
2

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. Reset the time zone using the command to specify the new value. The pluggable database will need to be restarted for this to take effect. We can see the pluggable database time zone is different to the container database. For more information see: - ALTER DATABASE : set_time_zone_clause - Multitenant : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

SELECT dbtimezone FROM DUAL;

DBTIME
------
-07:00

SQL>

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

ALTER DATABASE SET TIME_ZONE='US/Eastern';

SHUTDOWN IMMEDIATE;
STARTUP;

CONN / AS SYSDBA

SELECT dbtimezone FROM DUAL;

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

SQL>


ALTER SESSION SET CONTAINER = pdb1;

SELECT dbtimezone FROM DUAL;

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

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!