duplicate target database from active database

It is always educational to get errors, problems during testing or creation of new systems
especially when you are learning new features or investigating how different configurations
behave and real lfie cases sometimes are not the same as it described in documentation.

That's why I prefer not to think about configuration of test databases on VM religiously
but to create them in minimal configuration and change it later when errors or problems
are being caught.

It helps to get problems and since they are test servers they can be easily broken, recreated
but experience gained and it can be useful for production support of databases.

Recently testing Data Guard switchover for heterogenous databases (Linux: 32-bit <-> 64-bit)
I created several standby databases on virtual machines where primary is on Linux 32-bit
but one of standby databases is on Linux 64-bit.

So I decided to test one of oracle 11g new features
"duplicate target database ... from active database"
to create standby database on Linux 64-bit from primary which is on 32-bit OS

The prerequisite for that RMAN command is to connect to target(primary) and auxiliary
(future standby) through SQL*Net and static configuration have been added to listener.ora file
on standby host:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=tstp)
      (GLOBAL_DBNAME=tstp)
      (ORACLE_HOME=/u03/app/oracle/product/11.1.0/db_1)
    )
  )

where tstp - database name for standby database (the same is for primary)

The primary database was created using dbca in silent mode

dbca -silent                      \
-createDatabase                   \
-templateName General_Purpose.dbc \
-gdbName tstp                     \
-sid tstp                         \
-SysPassword sys                  \
-SystemPassword sys               \
-emConfiguration NONE             \
-datafileDestination /u01/oradata \
-storageType FS                   \
-characterSet WE8ISO8859P15       \
-memoryPercentage 40

and was changed to archivelog mode. I created password file for standby instance
and started it in nomount mode as it required and checked connection to both instance
under sysdba:

rman target sys/sys@tstp auxiliary sys/sys@tsts
Recovery Manager: Release 11.1.0.7.0 - Production on Wed Jun 10 01:58:13 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TSTP (DBID=532361881)
connected to auxiliary database: TSTP (not mounted)

But unfortunately duplicate command failed due to errors:

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 10-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=170 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   file  '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwtstp' auxiliary format
 '/u03/app/oracle/product/11.1.0/db_1/dbs/orapwtstp'   ;
}
executing Memory Script

Starting backup at 10-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/10/2009 01:58:36
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/10/2009 01:58:36
ORA-17627: ORA-12577: Message 12577 not found;  product=RDBMS; facility=ORA

I could not find description of the error ORA-12577 but got description for 17627

oerr ora 17627
17627, 00000, "%s"
// *Cause:  An error returned by OCI while sending/receiving message from
//          remote instance
// *Action: Look at error message and take appropriate action or contact
//          Oracle Support Services for further assistance

All my further attempts somehow to get more meaning of the error failed
and I decided to create standby at the same host as primary.

Preparation steps were the same as for 64-bit server but SID of standby was different - tsts
I checked connection to both instances under sysdba and executed duplicate command.

It was completed without problem and standby database from active database had been created:

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 10-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   file  '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwtstp' auxiliary format
 '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwtsts'   ;
}
executing Memory Script
...
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/oradata/tsts/control01.ctl';
   sql clone 'alter database mount standby database';
}
executing Memory Script
...
Finished backup at 10-JUN-09

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script
...
Finished backup at 10-JUN-09

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
...
Finished Duplicate Db at 10-JUN-09

RMAN> exit

So now I have a couple of things to check and test to understand if "duplicate target database
for standby from active database" can be applied to create standby database on Linux 64-bit
from primary on Linux 32-bit:
1. Creation of standby database from active database on different from primary server but
with the same OS (Linux 32-bit)
2. Try to create standby on 64-bit from primary on 32-bit after all latest Data Guard patches applied
3. Trace RMAN sessions that are failing with ORA- errors during duplicate operation


Have a good day!

READ-ONLY standby database in Oracle 11g Data Guard

Oracle 11g came with new feature called active data guard which allows to apply redo
while database is opened for read-only access. This feature is not free and license should
be purchased, that's why I was wondering how it can be managed in dgmgrl command line
to open standby database just in read-only mode w/o active recovery and how to return it back.

I created Data Guard configuration with Oracle 11.1.0.7 on EL 5.2 32-bit

DGMGRL> show configuration verbose

Configuration
  Name:                bee_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    bee    - Primary database
    beesby - Physical standby database

Fast-Start Failover: DISABLED

Current status for "bee_dg":
SUCCESS

And using dgmgrl commands changed state of standby database to read-only:

DGMGRL> show database beesby

Database
  Name:            beesby
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  APPLY-ON
  Instance(s):
    newsby

Current status for "beesby":
SUCCESS

DGMGRL> edit database beesby set state = read-only;
Succeeded.
DGMGRL> show database beesby

Database
  Name:            beesby
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  READ-ONLY
  Instance(s):
    newsby

Current status for "beesby":
SUCCESS

In alert log the following entries appeared:

...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
...
ALTER DATABASE OPEN READ ONLY
...

So operation to change standby database to read only state finished okay
but what would happen if to change status to apply-on back.
(Oracle 11g documentation has lack of states to change in DG
"EDIT DATABASE (state)" : OFFLINE, ONLINE, READ-ONLY)

I changed state back to APPLY-ON and that's what I got in alert log:

DGMGRL> edit database beesby set state = apply-on;
Succeeded.

...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
...

Data Guard changed standby database state to active data guard becuase it was not bounced
to mount mode with further start of managed recovery but simply automatic recovery had been
started.
I tried different show commands to get information about active data guard usage
but did not find any that reflects real state of standby database only queries from
v$managed_standby and v$database helped

newsby> select (select process from v$managed_standby where process like 'MRP%'), (select open_mode from v$database) from dual;

(SELECTPR (SELECTOPE
--------- ----------
MRP0      READ ONLY

they exactly told me about active data guard option

But how to put standby database back to mount mode together with managed recovery?
One of the states that can be set is OFFLINE, so I decided to execute a couple of
"edit database" commands to change state to OFFLINE and then to APPLY-ON

DGMGRL> edit database beesby set state = offline;
Operation requires shutdown of instance "newsby" on database "beesby"
Shutting down instance "newsby"...
Database closed.
Database dismounted.
ORACLE instance shut down.

alert.log:
...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
...
Shutting down instance (immediate)
...

Unfortunately all my further attempts to startup standby instance in mount mode using dgmgrl
command "edit database beesby set state = apply-on;" were not successful even I set up static listener configuration and was able to connect through tns name to idle standby instance

DGMGRL> edit database beesby set state = apply-on;
Error: ORA-01034: ORACLE not available

Failed.

Only when I set environment variables for standby database I started instance
simply using "startup mount" in dgmgrl

DGMGRL> edit database beesby set state = apply-on;
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

DGMGRL> startup mount
ORACLE instance started.
Database mounted.

select statements showed me the status that I wanted - mounted with managed recovery

newsby> select (select process from v$managed_standby where process like 'MRP%'), (select open_mode from v$database) from dual;

(SELECTPR (SELECTOPE
--------- ----------
MRP0      MOUNTED

In addition I checked v$option trying to find information about either "Active Data Guard"
or "Real-time Query" options but did not see any of them.
Also it is not clear how to avoid installation of "Active Data Guard" option or how to disable it
if it is not purchased


Have a good day!

Changing SID for standby database in Data Guard configuration

There was a question at oracle-l forum regarding possibility to use different SID
for primary and standby databases. It should be possible and it is possible
since you can create standby database on the same host as primary.

Another thing that expressed my interest is Data Guard reaction for standby's SID change

I have created DG configuration with 2 standby databases
(11.1.0.7 on CentOS 64-bit as primary and 11.1.0.7 on OEL5 32-bit for standby databases)

DGMGRL> show configuration verbose

Configuration
 Name:                bee
 Enabled:             YES
 Protection Mode:     MaxPerformance
 Databases:
   bee64  - Primary database
   bee    - Physical standby database
          - Fast-Start Failover target
   beesby - Physical standby database

Fast-Start Failover: ENABLED
 Threshold:           30 seconds
 Target:              bee
 Observer:            oel1
 Lag Limit:           2000000000 seconds
 Shutdown Primary:    TRUE
 Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

And decided to change SID for beesby database from beesby to newsby
as long as that is not failover target database:

1. shutdown immediate
2. add new entry to /etc/oratab
3. copy spfile...ora, orapw... files to new ones changing SID name
4. change environment to set new SID
   
5. startup mount

It was mounted successfully under new SID with the same database and unique names

Periodically running "show configuration verbose" command
I got the following list of messages in dgmgrl:

Warning: ORA-16610: command "Broker automatic health check" in progress
Warning: ORA-16610: command "Instance being added to database" in progress
Warning: ORA-16610: command "Broker automatic health check" in progress
Warning: ORA-16607: one or more databases have failed

Apparently, failed one is beesby:

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   beesby (apply instance)
   newsby

 Properties:
...
Current status for "beesby":
Error: ORA-16766: Redo Apply is stopped

As you can see there are 2 instance names appeared for the database
and even state is APPLY-ON already I enabled it but with new apply instance:

DGMGRL> EDIT DATABASE beesby SET STATE = 'APPLY-ON' WITH APPLY INSTANCE = 'newsby';
Succeeded.

Execution was successful and after warning message

Warning: ORA-16610: command "EDIT DATABASE beesby SET PROPERTY" in progress

I got final SUCCESS status for standby database and for Data Guard on the whole

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   beesby
   newsby (apply instance)

...
Current status for "beesby":
SUCCESS

DGMGRL> show configuration verbose

Configuration
 Name:                bee
 Enabled:             YES
 Protection Mode:     MaxPerformance
 Databases:
   bee64  - Primary database
   bee    - Physical standby database
          - Fast-Start Failover target
   beesby - Physical standby database

Fast-Start Failover: ENABLED
 Threshold:           30 seconds
 Target:              bee
 Observer:            oel1
 Lag Limit:           2000000000 seconds
 Shutdown Primary:    TRUE
 Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

As you can see the old instance name left there but it can be easily removed:

DGMGRL> REMOVE INSTANCE beesby ON DATABASE beesby;
Removed instance "beesby" from database "beesby"

Now the instance name gone and configuration is in SUCCESS status

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   newsby

 Properties:
...
Current status for "beesby":
SUCCESS

Have a good day!