Oracle Timestamp Docker – Fixing Timestamp Sync Issues in Docker for Oracle

dockeroracletimestamp

I have created an Oracle Database Docker Image with the help of this link

SYSTIMESTAMP shows UTC time and not my local machine's time.

kaushik:~ knayak$ date
Fri Jun  8 17:46:35 IST 2018

select current_timestamp,systimestamp from dual;

CURRENT_TIMESTAMP                           SYSTIMESTAMP                      
------------------------------------------- ----------------------------------
08-06-18 05:47:51.742216000 PM ASIA/KOLKATA 08-06-18 12:17:51.742211000 PM GMT

How can I change the container's time to match with my time zone?

Best Answer

The problem is not with your database, it is the problem of the docker.

Docker has notorious problems with the time synchronization between the host machine and the virtual machines ("containers"...).

The simplest solution for the problem if you start the docker container with admin privileges (--privileged flag to docker run), and then use a time synchron tool inside the container to fix its time (ntpdate -s your.country.clock).

Related Solutions

Mysql – Docker-to-docker Mysql connection

First of all are the containers visible to one another i.e. can you ping the mysql box from the webserver?

If not it is a visibility issue, try creating a docker network to put the two containers within.

First create a docker network:

docker network create lamp-network

You can see this by the following command:

docker network ls

Now start your two containers with the additional flag:

--net lamp-network

Now try pinging and seeing if you have connection, if so then MySQL should work, although depending on setup you may have to comment out the bind-address from the config.

Oracle 12c Docker Connection – How to Connect Docker to Docker Oracle 12c

Make sure DB is up and running with status healthy
```
docker container ps
```
Make sure Oracle instant client is installed on host if trying to connect from host to containers via sqlplus or any 3rd party gui tools and set up connections in tnsnames.ora on host

3̶. S̶e̶t̶ ̶u̶p̶ ̶c̶o̶n̶n̶e̶c̶t̶i̶o̶n̶s̶ ̶i̶n̶ ̶t̶n̶s̶n̶a̶m̶e̶s̶.̶o̶r̶a̶ ̶o̶n̶ ̶b̶o̶t̶h̶ ̶c̶o̶n̶t̶a̶i̶n̶e̶r̶s̶.̶ (skip this step)

Make sure Oracle listener is up and running and you can find out services names on each containers by running the command
```
lsnrctl status 
```

You need to find out mapped ports. Execute command

docker port orcl

From my container the output is:

$ docker port orcl
1521/tcp -> 0.0.0.0:32769
5500/tcp -> 0.0.0.0:32768

You can connect directly from sqldeveloper or setup tns entries in tnsnames.ora for sqlplus client.

_{Here is the screenshot connecting from sqldeveloper}

For sqlplus client on host

#tnsname entry for container 1
orclcdb =
(DESCRIPTION =
  # host ipaddress and mapped port for 1521 is 32769
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 32769))
 (CONNECT_DATA =
(SERVER = DEDICATED)
#service name from lsnrctl status command
(SERVICE_NAME = ORCLCDB.localdomain)
)
)

Verify tnsname entries with the command from host:
```
tnsping  orclcdb
```

Note

My setup was on Windows 10(host) and docker running on VM. I executed the following command to find the ipaddress of docker VM

Input:

docker-machine ls

Output:

Correction:

I didn't notice parameter -p 1521:1521 for the container start up command. That parameter exposes port 1521 for public. Just use container ipaddress(from docker-machine ls) and same port 1521 for sqlplus client connection from the host.