Rename Snapshot Standby database name

Reading Time: 2 minutes

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..