Standby and Primary Databases on the same host + ORA-1033

As part of 10g upgrade to all our production 9i databases, Next week we planned to upgrade one of the business critical 9i database to 10g. This database is very business critical and 24x7. It hard to get longer downtime. We are lucky enough to get a hour downtime to upgrade this database. The database is size is around 100gb

We thought of minimizing the upgrade process to time by looking deeply into the following options as well thinking to rollback the upgrade in case of application not acceptable of 10g:

Method1:

Installing 10g on a new machine, and import the data. Using streams, replicate the 9i database to 10g and vice versa. This method gives us the rolling back option in case end-users find strange behavior with 9i.

We need to drop this method as we need to initially use export and import whereas we preferred the RMAN backup options. Oracle supports says, this is available with 10g database.

Method2:

Transportable Tablespaces. Installing 10g software on a new machine, export production with transportable options, copy datafiles to new machine, import the transportable tablespace.

This method involves, a database creation and schema creations.

Method3:

Create a standby database, break the standby, cover to primary and do the manual upgrade.

We decided to go with method 3 as its looks more controlled method and easy method.

This database already has a standby database in place, on different machine, DRC location.

However, we thought of creating another standby of this database on the primary database machine and do the upgrade.

In this process, we have initially, stopped the previously existing standby database, and created a new standby database on the same machine where primary database exists and make standby sync with the primary. We wanted to observe for a week and do the upgrade during the morning hours of week-end during non-peak hours.

Once the process finishes, we went home. After sometime, we have got a call from the operations team that they are not able to run the batches due to ORA-1033 error. We have connected through the VPN and investigated about the problem.

When we shutdown the standby, the ORA-1033 error was not occurring, and when standby mounts or keep in managed recovery mode, the ORA-1033 error occurs whenever trying to connect to the primary database.

After lot of investigations, we found out that we have missed to set the SERVICE_NAME parameter in the standby initialization parameter.

Upon setting SERVICE_NAME parameter in the standby, the ORA-1033 error disappears.

We couldn’t believe the this parameter can cause the error.

However, now everything is perfect and just ready for the next week upgrade.

Happy Reading,

Jaffar