1. Patching
When patching a database it is normal to bring down the database, patch the ORACLE_HOME and finally run ‘datapatch’ to apply any database changes introduced with the patch to the database.
With Out-Of-Place patching a new ORACLE_HOME is installed next to the existing and the database is started with that HOME.
But how do we do that with Dataguard?
Where we know that Dataguard applies changes done in the primary database onto the standby database using the redo-apply, we can’t patch the Dataguard the regular way.
If we would patch the primary database the usual way, it will apply database changes to the standby database of a patch that may be unknown to the ORACLE_HOME of this standby database.
1.1. The basics
This is why Oracle has introduced the so-called “Standby First Patching method”.
With this method, the ORACLE_HOME of the standby database is patched first. But we can’t run datapatch on this ORACLE_HOME as the database is not open to receive these changes.
So, after we have patched the ORACLE_HOME of the standby server, we switch roles, making the standby the primary. Then we can patch the ORACLE_HOME of the previous primary, now standby server.
Finally we run datapatch on the new primary and switch back.
In steps:
1) Disable redo apply on standby database
2) Shutdown standby database
3) Patch the ORACLE_HOME on the standby database
4) Start the standby database
5) Start the redo apply
6) Perform a switchover. The patched standby database now becomes primary database, but without that datapatch has- run.
7) Now repeat step 1 to 5 for the “new” standby database
8) When finished, switch back roles
9) Finally run datapatch from the primary database.
That concludes the in-place patching of a dataguard environment.
The above steps are only useful when patching an existing ORACLE_HOME, so-called in-place patching.
At Kramp we prefer- however out-of-place patching, where we install a new ORACLE_HOME with the patch already applied.
With out-of-place patching the steps needed to be done are:
1) Install a new patched home on both the primary and secondary server into a new directory.
2) Disable redo apply on standby database
3) Shutdown standby database
4) Copy password file, spfile and dataguard config files from the
5) Change environment to the new ORACLE_HOME
6) OLD_ORACLE_HOME/dbs to the NEW_ORACLE_HOME/dbs
7) Start the database using the new ORACLE_HOME in mount mode
8) Start redo-apply
9) Switchover the primary database role to the new started standby database.
10) Repeat steps 1 to 7 for the former primary database server, now being the standby
11) When ready, run datapatch from the primary database
12) Switchover to let the initial primary database taking this role again
On the next pages both processes are seen with commands and screen output.
1.2. In place patching
In this section the real patch process of in-place patching is discussed.
1) The first step is to disable the redo apply on the standby database. The command to use from SQLplus is:
2) Now we can shutdown the Standby database following step 2:
Note that the “ORA-01109” confirms that we are connected to the standby database.
Stopping the database includes stopping the listener:
3) At this point we assume that we have extracted the patch on a location with sufficient space, e.g. /opt/tsmt01/exp.
Also the OPatch version in the ORACLE_HOME has been replaced with the newest version.
Now we can apply the patches for database and ojvm:
When finished, optionally clean out old replace patch-sets and release updates.
The statements to use are :
opatch util listorderedinactivepatches
This lists the inactive patches and release updates.
You can actually delete them, using the command:
opatch util deleteinactivepatches
4) Then start the listener again.
Also start the standby database in MOUNT mode.
Also start the standby database in MOUNT mode.
Do NOT run datapatch !!!
5) Start the redo apply services again:
It may, like in this screenshot, give an error is redo apply has already been started.
6) At this point we can switch over to make the current Standby database the Primary database, after which we can repeat the above steps, which we will not show here again
7) So… step 7 is skipped, as it would display everything from step 1 to 5 again
Step 7 finishes with the switchover to the initial primary database again.
8) In step 8 we’re going to run datapatch to apply the database changes that have come with the patch
Step into the ORACLE_HOME/OPatch directory and run ‘datapatch -verbose’:
This process may take a while. Because of the redo apply, both the Primary and Standby database are now supplied with the changes coming with the patch.
1.3. Out of Place patching
With out of place patching the downtime can be even more minimized. This method of patching uses a new patched installation as a new ORACLE_HOME.
The running database from the 1st ORACLE_HOME has to be brought down and can be immediately started from the 2nd ORACLE_HOME.
Because downtime is only very short, it is questionable if a switchover would not result in the same amount of downtime, as there also both databases are briefly down together while switching roles.
1) In this section we assume that the new ORACLE_HOME is already in place.
2) The first step is to disable the redo apply on the standby database. The command to use from SQLplus is:
3) Now we can shutdown the database and the listener
4) Copy all parameter files, both for the database and the dataguard configuration, and the password file to the new ORACLE_HOME/dbs, e.g.
$ cd $OLD_ORACLE_HOME/dbs
$ cp * $NEW_ORACLE_HOME/dbs
5) Set the environment tot the new ORACLE_HOME:
Also change the /etc/oratab and re-login to make sure that also the PATH variable is set correctly. Otherwise binaries from the OLD_ORACLE_HOME will be used to start the database again.
6) Edit the listener.ora file and update the ORACLE_HOME to point to the new location.
Then start the listener again with the new HOME.
Also start the standby database in MOUNT mode.
Do NOT run datapatch !!!
7) Start the redo apply services again:
It may, like in this screenshot, give an error is redo apply has already been started.
8) At this point we can switch over to make the current Standby database the Primary database, after which we can repeat the above steps, which we will not show here again
9) Repeat steps 1 through 7, to relocate the now running standby database to the new ORACLE_HOME
10) Switchover to let the initial primary database take this role again
11) Run datapatch to apply the database changes that have come with the patch
Step into the ORACLE_HOME/OPatch directory and run ‘datapatch -verbose’:
This process may take a while. Because of the redo apply, both the Primary and Standby database are now supplied with the changes coming with the patch.
2. Troubleshooting
In this section, issues encountered during testing and writing of this blog are discussed.
Actually only two issues occurred.
2.1. Listener configuration
The first was during the out-of-place patching where the primary was unable to find the standby database, showing:
If you encounter this error, when having done out of place patching of the standby and the database and listener have been started on the standby server, then you probably have not updated the listener.ora file, having the ORACLE_HOME pointing to the new location in the static listener entry:
If the above is true, this is confirmed in the dataguard broker logfile “drcTSMT01C.log”
There is may say:
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration tsmt01c_config Warning ORA-16607: one or more members have failed
Primary Database tsmt01c_ams Error ORA-16778: redo transport error for one or more members
Physical Standby Database tsmt01c_ens Error ORA-01034: ORACLE not available
To solve this problem, stop the Standby database and listener again, and update the ORACLE_HOME entry in the listener.ora as seen here. After updating the listener.ora restart the listener and database and all should work as expected.
2.2. Switchover failing
During the patch process the configuration is switched over from primary to standby two times. This ensures that the database will remain available for the clients
It may happen that a switchover fails, in restarting the two databases in the correct mode. An error in Dataguard Broker is then seen:
Somehow the switchover was partly done.
Before acting upon this and blindly follow the action step, make sure that you check the other database if it is open in read-write mode and also if it is primary.
This can be done using this query:
SQL> column open_mode format a20
SQL> column database_role format a20;
SQL> set linesize 100
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- --------------------
READ WRITE PRIMARY
It should return the same as seen here. Otherwise manual intervention is needed to really dig in to logfiles to find out where the switchover has gone of track.
If it returns the same output as seen here, you can safely start the standby in mount mode as indicated which will solve the problem.
No comments:
Post a Comment