oracle-database-operator icon indicating copy to clipboard operation
oracle-database-operator copied to clipboard
verified publisher icon oracle

sidb with loadBalancer=true Unhealth and unavalable

Open rbaumgar opened this issue 1 year ago • 6 comments

When I create a sidb (enterprise, free, xe, ..) it works fine until I set loadBalancer = true. db orperator is namespace based.

No error in operator log!

siddb.yaml

apiVersion: database.oracle.com/v1alpha1
kind: SingleInstanceDatabase
metadata:
  name: siddb
spec:
  sid: ORCL1
  edition: enterprise
  createAs: primary
  primaryDatabaseRef: ""
  adminPassword:
    secretName: siddb-admin-secret
  charset: AL32UTF8
  pdbName: orclpdb1
  flashBack: false
  archiveLog: false
  forceLog: false
  tcpsCertRenewInterval: 8760h
  initParams:
    cpuCount: 0
    processes: 0
    sgaTarget: 0
    pgaAggregateTarget: 0
  image:
    pullFrom: container-registry.oracle.com/database/enterprise:21.3.0.0
    pullSecrets: ocirsecret
    prebuiltDB: true
  persistence:
    size: 10Gi
    storageClass: nfs-csi-storage
    datafilesVolumeName: ""
    scriptsVolumeName: ""
  resources:
    requests:
      cpu: 200m
      memory: 2Gi
    limits: 
      cpu: 1
      memory: 8Gi
  loadBalancer: false
  serviceAccountName: 'sidb-sa'
  replicas: 1

status:

  archiveLog: "false"
  charset: AL32UTF8
  clusterConnectString: siddb-ext.oracle:1521/ORCL1
  conditions:
  - lastTransitionTime: "2024-09-28T17:41:14Z"
    message: processing datapatch execution
    observedGeneration: 1
    reason: LastReconcileCycleBlocked
    status: "True"
    type: ReconcileBlocked
  - lastTransitionTime: "2024-09-28T17:59:53Z"
    message: no reconcile errors
    observedGeneration: 3
    reason: LastReconcileCycleQueued
    status: "True"
    type: ReconcileQueued
  - lastTransitionTime: "2024-09-28T17:59:57Z"
    message: no reconcile errors
    observedGeneration: 3
    reason: LastReconcileCycleCompleted
    status: "True"
    type: ReconcileComplete
  connectString: 192.168.50.15:32489/ORCL1
  createdAs: primary
  datafilesCreated: "true"
  datafilesPatched: "true"
  edition: Enterprise
  flashBack: "false"
  forceLog: "false"
  initParams:
    cpuCount: 4
    pgaAggregateTarget: 512
    processes: 300
    sgaTarget: 1536
  isTcpsEnabled: false
  oemExpressUrl: https://192.168.50.15:30162/em
  pdbConnectString: 192.168.50.15:32489/ORCLPDB1
  pdbName: orclpdb1
  persistence:
    accessMode: ReadWriteOnce
    setWritePermissions: true
    size: 10Gi
    storageClass: nfs-csi-storage
  prebuiltDB: true
  releaseUpdate: 21.3.0.0.0
  replicas: 1
  role: PRIMARY
  sid: ORCL1
  status: Healthy
  tcpsConnectString: Unavailable
  tcpsPdbConnectString: Unavailable
  tcpsTlsSecret: ""

status after loadBalancer=true

  archiveLog: "false"
  charset: AL32UTF8
  clusterConnectString: siddb-ext.oracle:1521/ORCL1
  conditions:
  - lastTransitionTime: "2024-09-28T17:41:14Z"
    message: processing datapatch execution
    observedGeneration: 1
    reason: LastReconcileCycleBlocked
    status: "True"
    type: ReconcileBlocked
  - lastTransitionTime: "2024-09-28T17:59:57Z"
    message: no reconcile errors
    observedGeneration: 3
    reason: LastReconcileCycleCompleted
    status: "True"
    type: ReconcileComplete
  - lastTransitionTime: "2024-09-28T18:10:07Z"
    message: no reconcile errors
    observedGeneration: 4
    reason: LastReconcileCycleQueued
    status: "True"
    type: ReconcileQueued
  connectString: Unavailable
  createdAs: primary
  datafilesCreated: "true"
  datafilesPatched: "true"
  edition: Enterprise
  flashBack: "false"
  forceLog: "false"
  initParams:
    cpuCount: 4
    pgaAggregateTarget: 512
    processes: 300
    sgaTarget: 1536
  isTcpsEnabled: false
  oemExpressUrl: Unavailable
  pdbConnectString: Unavailable
  pdbName: orclpdb1
  persistence:
    accessMode: ReadWriteOnce
    setWritePermissions: true
    size: 10Gi
    storageClass: nfs-csi-storage
  prebuiltDB: true
  releaseUpdate: 21.3.0.0.0
  replicas: 1
  role: PRIMARY
  sid: ORCL1
  status: Updating
  tcpsConnectString: Unavailable
  tcpsPdbConnectString: Unavailable
  tcpsTlsSecret: ""

rbaumgar avatar Sep 28 '24 18:09 rbaumgar

Hello Robert, please do send some oraoperator pod logs along anyway, it seems as if it's waiting for something (an IP address from the loadbalancer?) and will not update the database status but also wont error out then. Can You also see a service of type LoadBalancer which maybe is PENDING , waiting for an IP address ? But perhaps its an OpenShift permission problem again, so some logs would be great...

ilfur avatar Sep 30 '24 08:09 ilfur

you are right, svc extrnal-ip is pending. But I don't want external IP...

$ oc get svc -n oracle
NAME              TYPE           CLUSTER-IP      EXTERNAL-IP   PORT(S)                         AGE
siddb             ClusterIP      172.30.30.189   <none>        1521/TCP                        40h
siddb-ext         LoadBalancer   172.30.206.29   <pending>     5500:30162/TCP,1521:32489/TCP   40h
$ oc get svc -n oracle siddb-ext -o yaml
apiVersion: v1
kind: Service
metadata:
  creationTimestamp: "2024-09-28T17:40:18Z"
  labels:
    app: siddb
  name: siddb-ext
  namespace: oracle
  ownerReferences:
  - apiVersion: database.oracle.com/v1alpha1
    blockOwnerDeletion: true
    controller: true
    kind: SingleInstanceDatabase
    name: siddb
    uid: 4dbf1a88-cd09-41b7-83e6-df2559aea9e4
  resourceVersion: "2614998387"
  uid: 20187167-43a2-4969-baf7-27d02bd0a3b8
spec:
  allocateLoadBalancerNodePorts: true
  clusterIP: 172.30.206.29
  clusterIPs:
  - 172.30.206.29
  externalTrafficPolicy: Cluster
  internalTrafficPolicy: Cluster
  ipFamilies:
  - IPv4
  ipFamilyPolicy: SingleStack
  ports:
  - name: xmldb
    nodePort: 30162
    port: 5500
    protocol: TCP
    targetPort: 5500
  - name: listener
    nodePort: 32489
    port: 1521
    protocol: TCP
    targetPort: 1521
  selector:
    app: siddb
  sessionAffinity: None
  type: LoadBalancer
status:
  loadBalancer: {}

Operator log when "loadBalancer=true"

2024-09-30T09:59:18Z INFO bash -c echo -e "SELECT 'log_mode:' || log_mode AS log_mode ,'flashback_on:' || flashback_on AS flashback_on ,'force_logging:' || force_logging AS force_logging FROM v\$database;" | sqlplus -s / as sysdba {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "ExecCommand": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO CheckModes Output {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO
LOG_MODE FLASHBACK_ON
--------------------- -------------------------------
FORCE_LOGGING
-----------------------------------------------------
log_mode:NOARCHIVELOG flashback_on:NO
force_logging:NO
{"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO FlashBackStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:18Z INFO ArchiveLogStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:18Z INFO ForceLoggingStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:18Z INFO Executing Command : {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "ExecCommand": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO bash -c echo -e "SELECT 'log_mode:' || log_mode AS log_mode ,'flashback_on:' || flashback_on AS flashback_on ,'force_logging:' || force_logging AS force_logging FROM v\$database;" | sqlplus -s / as sysdba {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "ExecCommand": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:19Z INFO CheckModes Output {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:19Z INFO
LOG_MODE FLASHBACK_ON
--------------------- -------------------------------
FORCE_LOGGING
-----------------------------------------------------
log_mode:NOARCHIVELOG flashback_on:NO
force_logging:NO
{"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:19Z INFO FlashBackStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO ArchiveLogStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO ForceLoggingStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO controllers.database.SingleInstanceDatabase Flashback {"updateDBConfig": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO controllers.database.SingleInstanceDatabase ArchiveLog {"updateDBConfig": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO controllers.database.SingleInstanceDatabase ForceLog {"updateDBConfig": {"name":"siddb","namespace":"oracle"}, "Status :": false}

rbaumgar avatar Sep 30 '24 10:09 rbaumgar

@rbaumgar I don' think the problem is from the operator code. So if the cloud provider requires some service annotation for the load balancer then you have to give that in the singleinstancedatabase config yaml file.

Can I know which provider are you using ? And please try just to deploy a load balancer on the provider to check what annotation are required

IshaanDesai45 avatar Oct 21 '24 05:10 IshaanDesai45

@IshaanDesai45 it's on OpenShift and I don't want an external IP. I only want an IP from the pod network.

rbaumgar avatar Oct 21 '24 13:10 rbaumgar

So you would need to create a private/internal load balancer if you don't want an external IP. OpenShift might have some service annotations for creating the same like OKE have it here

IshaanDesai45 avatar Oct 24 '24 16:10 IshaanDesai45

What do you think is needed to create a service in OpenShift? No annotation is required. Here is an example

kind: Service
apiVersion: v1
metadata:
  labels:
    app: oracle-crud-jvm
  name: oracle-crud-jvm
spec:
  ports:
    - name: 8080-tcp
      protocol: TCP
      port: 8080
      targetPort: 8080
  selector:
    app: oracle-crud-jvm
    deployment: oracle-crud-jvm

$ oc get svc oracle-crud-jvm 
NAME              TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
oracle-crud-jvm   ClusterIP   172.30.52.248   <none>        8080/TCP   10s

rbaumgar avatar Oct 25 '24 13:10 rbaumgar

@rbaumgar please try to create the service and let me know if you still see the issue.

psaini79 avatar Mar 06 '25 15:03 psaini79

@psaini79 see entry from Oct 25.

rbaumgar avatar Mar 06 '25 16:03 rbaumgar

@rbaumgar thanks for the reply and sorry I didn't realize you already tried. Since you need only internal service to connect to the DB pod and you also used the selectors, did the service you create helped? Also, you want to to connect to DB on port 8080? If you need this connection for DB connection then DB port is 1521.

psaini79 avatar Mar 06 '25 17:03 psaini79

@psaini79 Yes, creating a service helped. Yes, you are right. Port 1521 has to be used. Not a perfect example.

rbaumgar avatar Mar 10 '25 07:03 rbaumgar

Thanks @rbaumgar . Closing this issue, please open new one if you have any question.

psaini79 avatar Mar 10 '25 20:03 psaini79

@psaini79, I don't know why you closed this issue. It is not solved; I only presented a workaround.

rbaumgar avatar Mar 11 '25 07:03 rbaumgar

I closed as it seems the problem from openshift side. I think your main concern that it should happen automatically from Oracle DB operator and in this case you did it manually. Let me test this scenario on openshift. I have following questions before I replicate the problem:

  • Environment details of your openshift i.e. Oracle Cloud or any other
  • openshift version

psaini79 avatar Mar 12 '25 03:03 psaini79

At the time of testing, it was on a baremetal cluster and OpenShift Local version 4.17.

rbaumgar avatar Mar 12 '25 08:03 rbaumgar

@rbaumgar can try editing the created loadbalancer service and add a static IP that you need using the loadBalancerIP attribute

See: https://github.com/kubernetes/kubernetes/pull/13005

yunus-qureshi avatar Jun 12 '25 05:06 yunus-qureshi

I don't need a specific IP and I don't want to set a specific one. Let Kubernetes do the work...

rbaumgar avatar Jun 12 '25 16:06 rbaumgar

Kubernetes can't seem to fetch a public IP since you are not on any Cloud but baremetal. So the only option is provide any private IP from your CIDR block

yunus-qureshi avatar Jun 12 '25 16:06 yunus-qureshi

I don't need a public IP, I need a cluster IP which is an overlay IP - cluster internal.

rbaumgar avatar Jun 12 '25 16:06 rbaumgar

Then loadBalancer should be false. i.e use a nodeport service.

loadBalancer: true will create a loadbalancer service which will try to get a public IP

yunus-qureshi avatar Jun 12 '25 16:06 yunus-qureshi

No, nodeport is a port on the external IP. I only want to get the DB accessible from within OpenShift.

rbaumgar avatar Jun 12 '25 16:06 rbaumgar