PDB delete/lifecycle

[tenstad]

When deleting a PDB Kubernetes resource like the one below, I would expect the PDB to be deleted in the database. Would say that is the expected behaviour when working with Kubernetes. It also enables cleanup of ephemeral environments by deleting a namespace, without having to manually clean up all PDBs provisioned within it.

apiVersion: database.oracle.com/v1alpha1
kind: PDB
spec:
  pdbName: pdb1
  action: Create
status:
  action: CREATE
  msg: Success
  phase: Ready

Here is the operator log from deleting pdb above:

2024-02-14T15:17:15Z INFO controllers.PDB Reconcile requested {"multitenantoperator": "oracle-database-operator-system/pdb1"}
2024-02-14T15:17:15Z INFO controllers.PDB PDB Resource Not found {"multitenantoperator": "oracle-database-operator-system/pdb1", "Name": ""}

and the state of the db after deleting the Kubernetes resource:

SELECT PDB_NAME FROM DBA_PDBS ORDER BY PDB_ID;
PDB_NAME
--------------------------------------------------------------------------------
PDB$SEED
PDB1

Current behaviour

action: Delete

Deleting a pdb by specifying action: Delete does not work, and results in the following:

status:
  action: DELETE
  msg: >-
    ORA-65179: cannot keep datafiles for a pluggable database that is not
    unplugged

    ORA-06512: at line 3
  phase: Failed

EDIT: It does work when also having dropAction: "INCLUDING".

action: Unplug

It results in Error "Required value: Please specify XML metadata filename" for field "spec.xmlFileName". Adding xmlFileName: foo.xml seems to avoid the issue, resulting in the following status:

status:
  msg: Waiting for PDB to be unplugged
  phase: Unplugging

When it succeeds unplugging, the operator also deletes the PDB resource, and the PDB is gone from the database.

[mmalvezz]

What is the sequence of event leading to the ORA-65197? can you please specify list of kubectl commands used? did you attempt to delete resource using "kubectl delete pdbs pdbs1 -n ..... " ?

[tenstad]

What is the sequence of event leading to the ORA-65197? can you please specify list of kubectl commands used? did you attempt to delete resource using "kubectl delete pdbs pdbs1 -n ..... " ?

I forgot dropAction: "INCLUDING", so modifying to action: Delete and dropAction: "INCLUDING" avoids having to unplug it.

[mmalvezz]

Issue reproduced in house working on it
`
kubectl get pdbs -n pdbnamespace
NAME CONNECT_STRING CDB NAME PDB NAME PDB STATE PDB SIZE STATUS MESSAGE
pdb1 (DESCRIPTION=(CONNECT_TIMEOUT=90)(RETRY_COUNT=30)(RETRY_DELAY=10)(TRANSPORT_CONNECT_TIMEOUT=70)(LOAD_BALLANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=scan12.testrac.com)(PORT=1521)(IP=V4_ONLY))(LOAD_BALLANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=scan34.testrac.com)(PORT=1521)(IP=V4_ONLY))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdbdev))) DB12 pdbdev READ WRITE 0.78G Ready Success

kubectl apply -f pdb_issue84.yaml
pdb.database.oracle.com/pdb1 configured

cat pdb_issue84.yaml
apiVersion: database.oracle.com/v1alpha1
kind: PDB
metadata:
name: pdb1
namespace: pdbnamespace
labels:
cdb: cdb-dev
spec:
cdbResName: "cdb-dev"
cdbNamespace: "cdbnamespace"
pdbName: "pdbdev"
action: "Delete"
pdbTlsKey:
secret:

kubectl get pdbs -n pdbnamespace
NAME CONNECT_STRING CDB NAME PDB NAME PDB STATE PDB SIZE STATUS MESSAGE
pdb1 pdbdev READ WRITE 0.79G Failed ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged
`

[tenstad]

Issue reproduced in house working on it

Sweet! Would be nice to be able to just action: Delete it, without requiring it to be correct configured for deletion.

But I also want to clarify that what I really need is the ability to delete the CDB PDB by simply deleting the resource (or the namespace), not modifying it's spec.

Let's say I spin up a review environment with a PDB and Deployment for each branch in my git repo, and use Renovate for dependency updates. Whenever a PR is merged, the branch and namespace is deleted. If the PDB inside the DB is not also deleted, a lot of them will build up. I cannot manually go into all review environments and delete the PDB before merging the PRs.

[mmalvezz]

If you forget to specify the r.Spec.DropAction when you delete database
then you get the default value which is KEEP.

// +kubebuilder:validation:Enum=INCLUDING;KEEP
DropAction string `json:"dropAction,omitempty"`

   if action == "DELETE" {
               if r.Spec.DropAction == "" {
                       r.Spec.DropAction = "KEEP"
                       pdblog.Info(" - dropAction : KEEP")
               }

As per oracle documentation keep option must be used after unpluging
database. This is the way the story goes at database level.

SQL> show pdbs

  CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       2 PDB$SEED                       READ ONLY  NO
       3 PDBDEV                         MOUNTED

drop pluggable database pdbdev keep datafiles;
*
ERROR at line 1:
ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

SQL>  alter pluggable database pdbdev unplug into '/tmp/pdbdev.xml';

Pluggable database altered.

SQL> show pdbs

  CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       2 PDB$SEED                       READ ONLY  NO
       3 PDBDEV                         MOUNTED
SQL> drop pluggable database PDBDEV keep datafiles;

Pluggable database dropped.

So we have to choices here

1. Set the default to "including" (action delete is designed to drop database regardless the
   unplug. For unplug there is a specific action)
2. Throw an error when you attempt to apply a payload without DropAction

Fix will be available in the next release

[tenstad]

Any update on if deleting the PDB resource in Kubernetes can result in the PDB being deleted in the database server? (not having to update the Kubernetes PDB resource's action field to Delete for the PDB to be deleted in the database server)

[mmalvezz]

This will not be implemented . If you need to delete a pluggable database then you need to apply a yaml file including action : "delete" + the other options associated to the delete command

[tenstad]

I'm sorry to say that doesn't work for us. It's not the common declerative way of managing infrastructure (and does not support cleaning up resouces by deleting the Kubernets objects). I would strongly suggest that you internally re-evaulate the approach, and read up on Kubernetes patterns. Particularly https://kubernetes.io/docs/concepts/overview/working-with-objects/finalizers regarding the deletion of objects and making sure the PDB is removed from the database server before it's fully removed from Kubernetes.

[mmalvezz]

and that's exactly what we have in the code ... we make sure it's removed from server before removing from k8s
`
func (r *PDBReconciler) deletePDB(ctx context.Context, req ctrl.Request, pdb *dbapi.PDB) error {

    log := r.Log.WithValues("deletePDB", req.NamespacedName)

    err := r.deletePDBInstance(req, ctx, pdb)
    if err != nil {
            log.Info("Could not delete PDB", "PDB Name", pdb.Spec.PDBName, "err", err.Error())
            return err
    }

    if controllerutil.ContainsFinalizer(pdb, PDBFinalizer) {
            log.Info("Removing finalizer")
            controllerutil.RemoveFinalizer(pdb, PDBFinalizer)
            err := r.Update(ctx, pdb)
            if err != nil {
                    log.Info("Could not remove finalizer", "err", err.Error())
                    return err
            }
            pdb.Status.Status = true
            err = r.Delete(context.Background(), pdb, client.GracePeriodSeconds(1))
            if err != nil {
                    log.Info("Could not delete PDB resource", "err", err.Error())
                    return err
            }
    }

`

[tenstad]

Thats nice, meaning it's not a technical issue! The thing we disagree about is then only declerative vs imperative management of the PDB resource. We would like it to behave like all other infrastructure managed in Kubernetes, that the existence and deletion of a PDB in the dbserver is declerative in Kubernetes. I.e. when the PDB resource exists in Kubernetes, it exists in the dbserver, and when it is deleted from Kubernetes, it is removed from the dbserver. We do not want to imperatively perform an action telling the operator to delete the PDB in the dbserver thorough an action field.

See e.g. to Crossplane and their deletionPolicy which tells the Provider what to do after deleting the managed resource. https://docs.crossplane.io/latest/concepts/managed-resources/#deletionpolicy

[mmalvezz]

We adopted the declarative approach long ago. Even if mixed object management it's not recommended https://kubernetes.io/docs/concepts/overview/working-with-objects/object-management/ I'm starting internal discussion . But just to set the expectation it will not be available in a short run.

[ilfur]

I too have the requirement to delete a PDB when it's kubernetes resource is deleted.
Please add a parameter to the PDB resource like "drop-pdb-when-resource-deleted" so we can have a choice.

My problem is that after creating and using multiple environments that include PDBs, I somehow need to drop them again. The propagation is done through helm charts. After using those installations, they get deleted/uninstalled by helm.
It's nagging me that all PDBs have to be manually deleted too and I can imagine that other customers feel the same, just look at tenstad's enquiry for that.
Actually, it's more than just nagging. After deleteing the k8s PDB resources, It's hard to find out which of the PDBs in my CDBs need to be deleted (or can be)... unused rubbish PDBs piling up over time.

[mmalvezz]

Hi Ilfur I'm testing the following option for cdb (the rest server) DeletePDBCascade (boolean) if it's true all the pdb associated to the cdb will be dropped when you delete the cdb (kubectl delete cdb mycdb....) .
It will be possible to implement your request as well .... If I have understood correctly you want us to adopt the assertive approach (kubectl delete pdb mypdb ---> drop database on the db container) .
PS no idea about the timeline

[ilfur]

Hello Matteo, yes absolutely, the declarative approach of deleting the database/pdb on the CDB as soon as the kubernetes resource is deleted is what I desire :-) Exactly as You wrote: kubectl delete pdb mypdb ---> drop database(pdb) on the db container(cdb)   I am patient about the timeline, I will manually work around the problem in my next projects: first place a "action: Delete" (not to forget INCLUDING datafiles) in the yaml and apply, then delete the application/helm chart. And forbid others to delete the helm chart by themselves.   Best regards, Marcel Gesendet: Dienstag, 13. August 2024 um 16:48 Uhr Von: "matteo malvezzi" ***@***.***> An: "oracle/oracle-database-operator" ***@***.***> Cc: "ilfur" ***@***.***>, "Comment" ***@***.***> Betreff: Re: [oracle/oracle-database-operator] PDB delete/lifecycle (Issue #84)   Hi Ilfur I'm testing the following option for cdb (the rest server) DeletePDBCascade (boolean) if it's true all the pdb associated to the cdb will be dropped when you delete the cdb (kubectl delete cdb mycdb....) . It will be possible to implement your request as well .... If I have understood correctly you want us to adopt the declarative approach (kubectl delete pdb mypdb ---> drop database on the db container) . PS no idea about the timeline — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: ***@***.***>

[mmalvezz]

Just a quick recap: in the next release there will be two parameters .

1. For PDB assertivePDBdeletion if it's true then the command kubectl delete pdb ... automatically drop crd & pdb
2. For CDB deletePdbCascade if it's true then the command kubectl delete cdb automatically delete all pdbs (CRD/PDB/datafiles) associated to the CDB

[ilfur]

Wonderful! Cant wait for it :) Thank You!     Gesendet: Dienstag, 15. Oktober 2024 um 09:53 Uhr Von: "matteo malvezzi" ***@***.***> An: "oracle/oracle-database-operator" ***@***.***> Cc: "ilfur" ***@***.***>, "Comment" ***@***.***> Betreff: Re: [oracle/oracle-database-operator] PDB delete/lifecycle (Issue #84)   Just a quick recap: in the next release there will be two parameters . For PDB assertivePDBdeletion if it's true then the command kubectl delete pdb ... automatically drop crd & pdb For CDB deletePdbCascade if it's true then the command kubectl delete cdb automatically delete all pdbs (CRD/PDB/datafiles) associated to the CDB — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: ***@***.***>