Monday, July 28, 2014

RMAN-03009: failure of delete command ... ORA-19606: Cannot copy or restore to snapshot control file

This error happen when a snapshot controlfile becomes obsolete, but rman is using it and cannot delete it. The problem that comes out of it is that all other obsolete backups are not deleted also since rman tries first to delete the controlfile copy.

Solution :

Take a note of the cuurent location of the snapshot controlfile and set a new location :

RMAN>show all;

RMAN configuration parameters for database with db_unique_name mydb;

.....

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/myloc/mysnapc.f'; # OLD

RMAN>

RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/mylocation/mysnapc.f'; # Configure new location


RMAN>host 'rm /myloc/mysnapc.f'; # delete old file from disk 

RMAN> corsscheck copy; # Mark the deleted file expired

RMAN> delete expired copy; # Delete the expired file from rman catalog/db control file

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/myloc/mysnapc.f'; # Restore to old location

RMAN>exit;

REFERENCES: Oracle Support Doc ID 1215493.1

Wednesday, July 16, 2014

Converting a Physical Standby to Snapshot Standby using Data Guard Broker (11gR2)

The following steps show how easy is to use Data Guard Broker (dgmgrl) to manage states of the database in a Data Guard (DG) configuration. In the next example the configuration is : 

ACTIVE DATA GUARD CONFIGURATION - Maximum Performance
Primary Database : prmdb
Physical Standby Database : phstdb -- Read Only with Apply
Fast-Start Failover: Enabled

Use the following procedure to convert phstdb to Snapshot Standby :

1. Connect to DG Broker and check the active configuration :

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@prmdb
Password:
Connected.
DGMGRL> show configuration verbose;

Configuration - DG_CONF_FSF

  Protection Mode: MaxPerformance
  Databases:
    prmdb - Primary database
    phstdb - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           phstdb
  Observer:         observer.host
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

2. Disable Fast Start Failover otherwise you will get : ORA-16668: operation cannot be performed on the fast-start failover target standby database

DGMGRL> disable FAST_START failover;
Disabled.
DGMGRL> show configuration verbose;

Configuration - DG_CONF_FSF

  Protection Mode: MaxPerformance
  Databases:
    prmdb - Primary database
    phstdb - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

3. Issue the convert to snapshot standby statement;

DGMGRL> convert database phstdb to snapshot standby;
Converting database "phstdb" to a Snapshot Standby database, please wait...
Database "phstdb" converted successfully

DGMGRL> show configuration verbose;

Configuration - DG_CONF_FSF

  Protection Mode: MaxPerformance
  Databases:
    prmdb - Primary database
    phstdb - Snapshot standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

You are done and you can check it with sqlplus 

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

From this point and forward you can treat phstdb as a normal Oracle Database.

You can go back and converting it to Physical Standby at any time as long as you have the space in FRA to keep the flashback logs required. 

The space is needed because going back is accomplished by creating a guaranteed restore point thus you need the space to keep the flashback logs.

You must also take in mind the space needed for the archive logs coming from the Primary (Log Shipping is not disabled) and for the new Archive logs produced by the Snapshot Standby.

After returning to the initial state the latest are deleted.

Now in order to return to the previous state do the following :

1. From the same DGMGRL session (or a new one) issue the convert to physical standby command:

DGMGRL> convert database phstdb to physical standby;
Converting database "phstdb" to a Physical Standby database, please wait...
Operation requires shutdown of instance "phstdb" on database "phstdb"
Shutting down instance "phstdb"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "phstdb" on database "phstdb"
Starting instance "phstdb"...
ORACLE instance started.
Database mounted.
Continuing to convert database "phstdb" ...
Operation requires shutdown of instance "phstdb" on database "phstdb"
Shutting down instance "phstdb"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "phstdb" on database "phstdb"
Starting instance "phstdb"...
ORACLE instance started.
Database mounted.
Database "phstdb" converted successfully

2. Enable the Fast Start failover and check the active configuration

DGMGRL> enable FAST_START FAILOVER;
Enabled.
DGMGRL> show configuration verbose;

Configuration - DG_CONF_FSF

  Protection Mode: MaxPerformance
  Databases:
    prmdb - Primary database
    phstdb - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           phstdb
  Observer:         observer.host
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

3. Using SQL Plus you can check that you are back to the initial configuration:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY


REFERENCES
Oracle® Data Guard Broker 11g Release 2 (11.2) -- 6.11 Scenario 10: Converting a Physical Standby to a Snapshot Standby