Changing SID for standby database in Data Guard configuration

There was a question at oracle-l forum regarding possibility to use different SID
for primary and standby databases. It should be possible and it is possible
since you can create standby database on the same host as primary.

Another thing that expressed my interest is Data Guard reaction for standby's SID change

I have created DG configuration with 2 standby databases
(11.1.0.7 on CentOS 64-bit as primary and 11.1.0.7 on OEL5 32-bit for standby databases)

DGMGRL> show configuration verbose

Configuration
 Name:                bee
 Enabled:             YES
 Protection Mode:     MaxPerformance
 Databases:
   bee64  - Primary database
   bee    - Physical standby database
          - Fast-Start Failover target
   beesby - Physical standby database

Fast-Start Failover: ENABLED
 Threshold:           30 seconds
 Target:              bee
 Observer:            oel1
 Lag Limit:           2000000000 seconds
 Shutdown Primary:    TRUE
 Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

And decided to change SID for beesby database from beesby to newsby
as long as that is not failover target database:

1. shutdown immediate
2. add new entry to /etc/oratab
3. copy spfile...ora, orapw... files to new ones changing SID name
4. change environment to set new SID
   
5. startup mount

It was mounted successfully under new SID with the same database and unique names

Periodically running "show configuration verbose" command
I got the following list of messages in dgmgrl:

Warning: ORA-16610: command "Broker automatic health check" in progress
Warning: ORA-16610: command "Instance being added to database" in progress
Warning: ORA-16610: command "Broker automatic health check" in progress
Warning: ORA-16607: one or more databases have failed

Apparently, failed one is beesby:

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   beesby (apply instance)
   newsby

 Properties:
...
Current status for "beesby":
Error: ORA-16766: Redo Apply is stopped

As you can see there are 2 instance names appeared for the database
and even state is APPLY-ON already I enabled it but with new apply instance:

DGMGRL> EDIT DATABASE beesby SET STATE = 'APPLY-ON' WITH APPLY INSTANCE = 'newsby';
Succeeded.

Execution was successful and after warning message

Warning: ORA-16610: command "EDIT DATABASE beesby SET PROPERTY" in progress

I got final SUCCESS status for standby database and for Data Guard on the whole

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   beesby
   newsby (apply instance)

...
Current status for "beesby":
SUCCESS

DGMGRL> show configuration verbose

Configuration
 Name:                bee
 Enabled:             YES
 Protection Mode:     MaxPerformance
 Databases:
   bee64  - Primary database
   bee    - Physical standby database
          - Fast-Start Failover target
   beesby - Physical standby database

Fast-Start Failover: ENABLED
 Threshold:           30 seconds
 Target:              bee
 Observer:            oel1
 Lag Limit:           2000000000 seconds
 Shutdown Primary:    TRUE
 Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

As you can see the old instance name left there but it can be easily removed:

DGMGRL> REMOVE INSTANCE beesby ON DATABASE beesby;
Removed instance "beesby" from database "beesby"

Now the instance name gone and configuration is in SUCCESS status

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   newsby

 Properties:
...
Current status for "beesby":
SUCCESS

Have a good day!