To change DB time zone in PDB and CDB on Exadata.

The current time zone is -5:00. All other DBs in the environment are in GMT time zone in both container DB and pluggable DB.

Overview of the Issue:

. The current time zone is -5:00. All other databases in the environment are in GMT time zone.

Outage : Required

Steps to change the dbtimezone

1) . oraenv

instance_name1

2) sqlplus / as sysdba

3) show parameter db_name

4) CREATE RESTORE POINT BEFORE_TIMEZONE_CH GUARANTEE FLASHBACK DATABASE;

5) alter session set container=container_name;

6) select name,open_mode,inst_id,con_id from gv$pdbs order by inst_id;

7) select dbtimezone from dual;

8) ALTER PLUGGABLE DATABASE container_name SET TIME_ZONE = ‘GMT’;

9) alter pluggable database container_name close immediate instances=ALL;

10) alter pluggable database container_name open instances=all;

11) select dbtimezone from dual; — It should show new format – GMT.

12) select name,open_mode,inst_id,con_id from gv$pdbs order by inst_id;

13) Repeat the above steps from 5 to 12 for another pluggable DB in same container.

To change container dbtime zone

1) hostname: EXA_NODE1

. oraenv

TEST1

2) srvctl status database -d TEST -f -v

3) sqlplus / as sysdba

4) show parameter db_name;

5) select name,open_mode,inst_id,con_id from gv$pdbs order by inst_id;

6) CREATE RESTORE POINT BEFORE_TIMEZONE_CH GUARANTEE FLASHBACK DATABASE;

7) select dbtimezone from dual;

8) ALTER DATABASE CHSNS SET TIME_ZONE = ‘GMT’;

9) exit;

10) srvctl stop database -d TEST -o immediate — wait for this commmand to complete, do not interrupt this session.

11) srvctl start database -d TEST

12) srvctl status database -d TEST -f -v = Make sure the output is matching with previous output

13) sqlplus / as sysdba

14) select dbtimezone from dual; = It should show new format “GMT”

15) select name,open_mode,inst_id,con_id from gv$pdbs order by inst_id; – Make sure the output is matching with previous command output.

16)exit