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

Advertisements

Author: Abdul H Khan

Trying to be cloudy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s