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