In this Document

Applies to:

Purpose

This note describes how Oracle Data Guard supports changing both the database and national character set of a primary database without requiring customers to recreate any physical standby databases in the configuration.

Scope

This note is intended for database administrators.

Details

Introduction:

Oracle Data Guard supports changing both the database and national character set of a primary database without requiring customers to recreate any physical standby databases in the configuration. In previous releases a physical standby database had to be recreated from a backup after a character set conversion. It describes instead a series of Oracle recommended steps that will allow you to continue operating your physical standby with minimal disruption when performing character set conversion of a primary database.

Background:

Background:

For the purposes of this support note, a character set conversion procedure at a primary database can be broadly categorized into a series of preparatory and conversion steps when using csscan/csalter or the DMU tool  
The preparatory steps involve executing several actions such as running CSSCAN tool or scanning the database using the DMU tool.

For information on using the DMU tool to go to UTF8 or AL32UTF8 please see Note 1272374.1 The Database Migration Assistant for Unicode (DMU) Tool
For information on using csscan/csalter to go to UTF8 or AL32UTF8 please see Note 260192.1 - Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode)
For information on using csscan/csalter to go to an other NLS_CHARACTERSET please see Note 225912.1 Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET ) / C4) Step by step guides to do a characterset conversion between 8 bit charactersets in 8i, 9i, 10g and 11g.
 
These steps
* prepare the primary database for character set conversion without actually converting the relevant data into the new character set.
* do the actual conversion using Csalter or the DMU tool "convert database" option

you will need to do all steps in this note up to and including [Step 6] BEFORE running csalter or or the DMU tool "convert database" option on the primary database

[Step 1]

Before you execute the conversion?? steps at the primary database, ensure that the physical standby is mounted and caught up with the primary database.

• For a physical standby in real-time query mode, it is necessary to terminate all sessions connected to the standby, cancel redo apply, and then shut down and mount the physical standby database. You can mount all standby instances, but don???t open any of them at this time. Start redo apply on the mounted standby. Refer to Oracle Data Guard Concepts & Administration documentation.
  
  Below is an example of the steps to mount a physical standby database that is in real-time query mode after terminating all the applications and sessions connected to it:

• For a Snapshot standby, terminate all sessions connected to the Snapshot standby, shut down and mount the Snapshot standby, convert the Snapshot standby into a physical standby, and shut down and mount the physical standby. Start redo apply on the mounted standby. Refer to Oracle Data Guard Concepts & Administration.
  
  Below is an example of a series of steps that can be executed on a mounted Snapshot standby database [It is assumed that user has executed all the necessary steps to terminate all sessions connected to the Snapshot standby and followed this up with a shut down and mount of the Snapshot standby database]:

• Verify that redo apply at the mounted (unopened) physical standby is reasonably caught up with the primary database in terms of redo logs applied (one or two sequences behind the current sequences of enabled threads at the primary database is considered ??reasonable??). There are various ways to determine the redo apply lag - refer to Oracle Data Guard Concepts & Administration for more information.
  
  Assume redo apply was started with the real time apply option at the physical standby database:

At the primary database query the current sequence# of the open thread:

At the physical standby database, query the sequence# redo apply is applying from:

Since redo apply is applying from sequence# 3 which is the current sequence# of the open thread at the primary, redo apply has caught up with the primary.

Note that converting a Snapshot standby to a mounted physical standby database or bringing a physical standby that is real-time query mode to the mounted state will cause downtime for applications that typically use these two features. This is unavoidable - therefore this downtime should be scheduled based on business needs and requirements.

Note that the primary database can be used for normal business until the execution of the conversion steps. So for example, if you convert a Snapshot standby to a physical standby, redo apply at the physical standby will have some catching up to do. During this catch up period, the primary database can operated normally and remain open.

[Step 2]

You can skip this step at the physical standby if a decision was made to not use flashback database technology at the physical standby database.

Once redo apply has caught up, consider enabling flashback database, if it not already enabled. If flashback database is not enabled at the physical standby, stop redo apply, enable flashback database, and restart redo apply. See various requirements needed to enable flashback database in Oracle Database Backup and Recovery User's Guide.

[Step 3]

If Data Guard Broker is enabled, disable it using DGMGRL. It will be enabled after the conversion has taken place.

Shutdown the primary database and mount it. Oracle recommends taking a backup of the primary database at this time.

[Step 4]

You can skip this step if you have decided not to use flashback database at your primary database.

• If flashback database is not enabled at the primary database, enable it and create a guaranteed restore point ???prod_grsp???.
  
  On the mounted primary, issue the following:

[Step 5]

Open the primary database in RESTRICTED session mode but do not do the conversion in the DMU tool yet or , when using csscan/Csalter, don't run CSALTER yet .

[Step 6]

Switch out of the current log sequence at the primary database and verify that redo apply has applied this log sequence.

At the primary database:

At the physical standby database, query the sequence# redo apply is applying from:

Since redo apply is applying from sequence# 3 which is the current sequence# of the open thread at the primary, redo apply has caught up with the primary.

After verifying redo apply has caught up with the primary, do one of the two steps below at the standby:

• [Step 6a] If you did not perform the flashback steps, stop redo apply. Redo apply will not be started unless the primary database is properly converted to the new character set.

• [Step 6b] If you have performed the flashback steps, stop redo apply, create a guaranteed restore point ??standby_grsp??, and restart redo apply.

[Step 7]

Execute the final conversion steps on the primary database by doing the conversion of the primary database using the  DMU tool "convert database" option or running CSALTER .
Redo generated during this step will be shipped to the standby. It will be applied at the physical standby database if you had redo apply running (which is the case if you had performed Step 6b).
It will not be applied at the physical standby database if redo apply had been stopped (which is the case if you had performed Step 6a).

Once the ??conversion steps?? have been run successfully at the primary database, the primary database can be opened.

If there are any issues with the conversion, and you decide to undo the conversion steps, please refer to the steps in Section ??"Failover Steps"??.

[Step 8]

Start redo apply at the physical standby database if it is not active. Note that redo apply will not be active if you performed Step 6a above.

Verify that all of the redo logs generated during the ???conversion??? procedure have been applied at the physical standby database.

If you use Data Guard Broker, enable the configuration using DGMGRL.

[Step 9]

Once the standby has applied all redo logs generated by the conversion procedure, it can be opened in real-time query mode or converted to a Snapshot Standby. Even if you don'??t intend to open the standby in real-time query mode or convert it to a Snapshot standby, open it read only once after stopping redo apply. The act of opening the standby read only from the mounted state will perform the final actions necessary to update the standby control file sections to reflect the new character set reflected in the SYS.PROPS$ tables.

Below is an example of the message you will see in the standby alert log when you open it read only and after redo apply has applied all redo logs generated during the conversion procedure:

At the mounted standby, issue:

The standby alert log will display message "??Updating character set in controlfile to UTF8"?? (in this example, the new character set is UTF8):

For confirmation:

• Run query select value$ from sys.props$ where name='NLS_CHARACTERSET' at the primary and at the open physical standby database and verify that the value$ value in sys.props$ is same at both databases.
• Run query select dicid from x$kccdi at the primary and at the open physical standby database and verify that the dicid value recorded in the control file is the same at both databases.

Failover Steps

Note the fallback plan below in case of disaster:

• In case there is an issue during the execution of the ???conversions steps??? that leaves the primary database accessible for a reboot, the fallback procedure is to flash the primary database back to the guaranteed restore point ??prod_grsp?? and ??open resetlogs?? the primary database. Flashing back to the guaranteed restore point "prod_grsp"?? ensures that the primary database has undone any changes performed during the failed ??conversion?? steps.

• In case there is a failure at the primary database during the execution of the ??conversion?? steps that causes you to completely lose your primary database, the physical standby database can be switched to the primary role.
• If redo apply was running (Step 6b), it is required to stop redo apply and flashback the physical standby database to guaranteed restore point ??standby_grsp?? before switching the physical standby database to the primary role.

• The switch itself is accomplished by using the alter database activate standby database DDL and subsequently opening the new primary. The new primary database will continue to operate with the previous character set data.

• The old primary would have to be manually reinstated or recreated from a backup as a new physical standby for the new primary. If Data Guard Broker was being used, the broker configuration would also have to be recreated. Refer to Oracle Data Guard Concepts & Administration and Oracle Data Guard Broker documentation.

 

References