Rename Snapshot Standby database name

Recently we had requirement to change database name to look like instance name once we convert Physical Standby to Snapshot Standby.   So, basically when user query v$database view it shows instance name rather unique database name:

cattach is custom

Here are steps I used to accomplish the goal..

SQL> select name , database_role from v$database;

NAME      DATABASE_ROLE

——— —————-

XONPROD   PHYSICAL STANDBY

SQL>

Converted Database to Snapshot Standby:

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name , database_role from v$database;

NAME      DATABASE_ROLE

——— —————-

XONPROD   SNAPSHOT STANDBY

SQL> @custom_view.sql

View created.

Grant succeeded.

Synonym dropped.

Synonym created.

SQL> show user

USER is “SYS”

SQL> select name , database_role from v$database;

NAME      DATABASE_ROLE

——— —————-

XONPROD   SNAPSHOT STANDBY

SQL> conn system

Enter password:

Connected.

SQL> select name , database_role from v$database;

NAME             DATABASE_ROLE

—————- —————-

xonprod2         SNAPSHOT STANDBY

SQL> select instance_name from v$instance;

INSTANCE_NAME

—————-

xonprod2

SQL>

attach is custom view script:

CREATE OR REPLACE FORCE VIEW V_CUST_VDATABASE

(

DBID,

NAME,

CREATED,

RESETLOGS_CHANGE#,

RESETLOGS_TIME,

PRIOR_RESETLOGS_CHANGE#,

PRIOR_RESETLOGS_TIME,

LOG_MODE,

CHECKPOINT_CHANGE#,

ARCHIVE_CHANGE#,

CONTROLFILE_TYPE,

CONTROLFILE_CREATED,

CONTROLFILE_SEQUENCE#,

CONTROLFILE_CHANGE#,

CONTROLFILE_TIME,

OPEN_RESETLOGS,

VERSION_TIME,

OPEN_MODE,

PROTECTION_MODE,

PROTECTION_LEVEL,

REMOTE_ARCHIVE,

ACTIVATION#,

SWITCHOVER#,

DATABASE_ROLE,

ARCHIVELOG_CHANGE#,

ARCHIVELOG_COMPRESSION,

SWITCHOVER_STATUS,

DATAGUARD_BROKER,

GUARD_STATUS,

SUPPLEMENTAL_LOG_DATA_MIN,

SUPPLEMENTAL_LOG_DATA_PK,

SUPPLEMENTAL_LOG_DATA_UI,

FORCE_LOGGING,

PLATFORM_ID,

PLATFORM_NAME,

RECOVERY_TARGET_INCARNATION#,

LAST_OPEN_INCARNATION#,

CURRENT_SCN,

FLASHBACK_ON,

SUPPLEMENTAL_LOG_DATA_FK,

SUPPLEMENTAL_LOG_DATA_ALL,

DB_UNIQUE_NAME,

STANDBY_BECAME_PRIMARY_SCN,

FS_FAILOVER_STATUS,

FS_FAILOVER_CURRENT_TARGET,

FS_FAILOVER_THRESHOLD,

FS_FAILOVER_OBSERVER_PRESENT,

FS_FAILOVER_OBSERVER_HOST,

CONTROLFILE_CONVERTED,

PRIMARY_DB_UNIQUE_NAME,

SUPPLEMENTAL_LOG_DATA_PL,

MIN_REQUIRED_CAPTURE_CHANGE#

)

AS

SELECT a.DBID,

(select INSTANCE_NAME from v$instance) as NAME,

a.CREATED,

a.RESETLOGS_CHANGE#,

a.RESETLOGS_TIME,

a.PRIOR_RESETLOGS_CHANGE#,

a.PRIOR_RESETLOGS_TIME,

a.LOG_MODE,

a.CHECKPOINT_CHANGE#,

a.ARCHIVE_CHANGE#,

a.CONTROLFILE_TYPE,

a.CONTROLFILE_CREATED,

a.CONTROLFILE_SEQUENCE#,

a.CONTROLFILE_CHANGE#,

a.CONTROLFILE_TIME,

a.OPEN_RESETLOGS,

a.VERSION_TIME,

a.OPEN_MODE,

a.PROTECTION_MODE,

a.PROTECTION_LEVEL,

a.REMOTE_ARCHIVE,

a.ACTIVATION#,

a.SWITCHOVER#,

a.DATABASE_ROLE,

a.ARCHIVELOG_CHANGE#,

a.ARCHIVELOG_COMPRESSION,

a.SWITCHOVER_STATUS,

a.DATAGUARD_BROKER,

a.GUARD_STATUS,

a.SUPPLEMENTAL_LOG_DATA_MIN,

a.SUPPLEMENTAL_LOG_DATA_PK,

a.SUPPLEMENTAL_LOG_DATA_UI,

a.FORCE_LOGGING,

a.PLATFORM_ID,

a.PLATFORM_NAME,

a.RECOVERY_TARGET_INCARNATION#,

a.LAST_OPEN_INCARNATION#,

a.CURRENT_SCN,

a.FLASHBACK_ON,

a.SUPPLEMENTAL_LOG_DATA_FK,

a.SUPPLEMENTAL_LOG_DATA_ALL,

a.DB_UNIQUE_NAME,

a.STANDBY_BECAME_PRIMARY_SCN,

a.FS_FAILOVER_STATUS,

a.FS_FAILOVER_CURRENT_TARGET,

a.FS_FAILOVER_THRESHOLD,

a.FS_FAILOVER_OBSERVER_PRESENT,

a.FS_FAILOVER_OBSERVER_HOST,

a.CONTROLFILE_CONVERTED,

a.PRIMARY_DB_UNIQUE_NAME,

a.SUPPLEMENTAL_LOG_DATA_PL,

a.MIN_REQUIRED_CAPTURE_CHANGE#

FROM v$database a

;

GRANT SELECT ON V_CUST_VDATABASE TO SELECT_CATALOG_ROLE;

DROP PUBLIC SYNONYM V$DATABASE;

CREATE OR REPLACE PUBLIC SYNONYM V$DATABASE FOR SYS.V_CUST_VDATABASE;

Let me know if this helps or if we have better workaround..