JDBC – Handle ORA-01033 Error After Database Switchover

dataguardhigh-availabilityjdbcoracle

We are using Oracle Data Guard and currently testing Disaster Recovery scenarios, which is starting the application after switching over from the Primary Database to the Secondary Database.

I am using the following connection string as per Oracle Documentation:

(DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD_DB))
    )

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DR_DB))
    )

)

The scenario is that we are now switched over to the secondary database, so the application should now connect to DR_DB. However, when we try to connect, it still connects to the PROD_DB, because we're getting the following exception:

ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

This may be answered already in some threads, and I may not have searched hard enough, but I also do not know if I am expecting the correct result.

I do not expect the application's database connection to stay alive after a switch over; I only want to avoid reconfiguring the application whenever we do DR, and I expect that the above connection string will connect to whichever is the primary database. Is this correct?

Thank you guys for your help.

Best Answer

And that is why we use role-based services instead of the default service derived from instance_name and db_unique_name.

If your primary database is called PROD_DB (db_unique_name), there will be service with that name at the primary site, and that service will be available even after a failover or switchover. The listener receives your request, sees that you are requesting to connect using the service PROD_DB, and forwards your request to the instance(s) that provide that service. The instance is in MOUNT stage, and you receive ORA-01033.

Create the role based service at both sites (replace -db and -service values with your current ones):

srvctl add service -db ORCL -service myservice -role primary
srvctl start service -db ORCL -service myservice -role primary

The start step will fail at the standby site, and the service remains in INTERMEDIATE state, but that is normal.

With this, the service is automatically relocated (stopped at old primary and started at new primary) when you perform a role transition with Data Guard broker.

And use this service for connecting:

(DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice))
    )

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice))
    )

)

When you try to connect using the service myservice, and your client tries the first entry (old primary, currently standby), the listener refuses the connection as there will be no myservice service running at that site, and your client tries the next available address.

Related Question