Primary-Physical Standby with different db_name

Recently, I came across a posting in Oracle Forums regarding a primary-physical standby configuration with primary and standby having different db_names. While there were many responses, questions and followups, the basic question remains.

Why would anyone want to have a standby with a different name than that of the primary? however, I wanted to test this curious scenario to see what happens.

The test was done in a 10.2.0.5 version. ***Some obvious feedbacks from the various commands are deleted to keep the output concise***

Let's start with the primary with the original db_name.


SQL> startup mount pfile='/tmp/initnxyz812.ora';
ORACLE instance started.
Database mounted.
SQL> select name,dbid,database_role,open_mode from v$database;

NAME            DBID DATABASE_ROLE    OPEN_MODE
--------- ---------- ---------------- ----------
ASML2     1922246803 PRIMARY          MOUNTED

[oracle10@linux2 10.2]$ nid target=sys/sys dbname=TEST1 setname=Y;

Connected to database ASML2 (DBID=1922246803)
Connected to server version 10.2.0
Control Files in database:
    +DATARAC10G/asm/controlfile/current.276.720729679
Change database name of database ASML2 to TEST1? (Y/[N]) => Y
Proceeding with operation
Changing database name from ASML2 to TEST1
    Control File +DATARAC10G/asm/controlfile/current.276.720729679 - modified
    Datafile +DATARAC10G/asm/datafile/system.288.720728637 - wrote new name
    Datafile +DATARAC10G/asm/datafile/undotbs1.289.720728605 - wrote new name
    Datafile +DATARAC10G/asm/datafile/sysaux.dbf.290.720728559 - wrote new name
    Datafile +DATARAC10G/asm/datafile/users01.dbf.291.720728283 - wrote new name
    Datafile +DATARAC10G/asm/datafile/example.287.720728703 - wrote new name
    Datafile +DATARAC10G/asml2/datafile/fm_auto.280.724896241 - wrote new name
    Datafile +DATARAC10G/asml2/datafile/fm_manual.279.724896573 - wrote new name
    Datafile +DATARAC10G/redl2/tempfile/temp.264.718373311 - wrote new name
    Control File +DATARAC10G/asm/controlfile/current.276.720729679 - wrote new name
    Instance shut down

Now, let's start the database and run some DMLs on primary and capture relevant information.


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           240

SQL> select * from before_all_changes;

      COL1
----------
       100
       200
       300
       400

SQL> select name,dbid,open_mode,database_role from v$database;

NAME            DBID OPEN_MODE  DATABASE_ROLE
--------- ---------- ---------- ----------------
TEST1     1922246803 READ WRITE PRIMARY

SQL> select name,value from v$parameter
  2  where name in ('instance_name',db_name','log_archive_dest_2',
  3  'log_archive_dest_state_2');

SQL> col name for a20
SQL> col value for a25
SQL> /

NAME                 VALUE
-------------------- -------------------------
log_archive_dest_2   SERVICE=NABC812
log_archive_dest_sta enable
te_2
instance_name        NXYZ812
db_name              TEST1

SQL> insert into before_all_changes values (500);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           241

On the Physical Standby Database:

SQL> startup mount pfile='/tmp/initnabc812.ora';

ORACLE instance started.

SQL> select sequence#,archived,applied,deleted,status from v$archived_log

  2  where sequence# >=238;

 SEQUENCE# ARC APP DEL S

---------- --- --- --- -

       238 YES YES NO  A

       239 YES YES NO  A

       240 YES YES NO  A

       241 YES NO  NO  A

SQL> select name,dbid,open_mode,database_role from v$database;

NAME            DBID OPEN_MODE  DATABASE_ROLE

--------- ---------- ---------- ----------------

ASML2     1922246803 MOUNTED    PHYSICAL STANDBY

SQL> select name,value from v$parameter

  2  where name in ('instance_name','db_name','fal_server','fal_client');

NAME                 VALUE

-------------------- --------------------

fal_client           NABC812

fal_server           NXYZ812

instance_name        NABC812

db_name              ASML2

SQL> host ps -ef| grep mrp

oracle10  9190  7030  0 09:16 pts/1    00:00:00 /bin/bash -c ps -ef| grep mrp

oracle10  9192  9190  0 09:16 pts/1    00:00:00 grep mrp

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select sequence#,archived,applied,deleted,status from v$archived_log

  2  where sequence#>=238;

 SEQUENCE# ARC APP DEL S

---------- --- --- --- -

       238 YES YES NO  A

       239 YES YES NO  A

       240 YES YES NO  A

       241 YES YES NO  A

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from before_all_changes;

      COL1

----------

       100

       200

       300

       400

       500

So though the controlfile, datafile and redolog files has the db_name, it seems the DBID plays a vital role than the db_name. Even creation of new datafiles/tablespaces etc works!!