Creating Standby with Active Database Duplication in 11gR2 or also known as Wading through the MOS

Several errors prevented the creation of a physical standby database (11.2.0.1) in a DataGuard configuration using the High Availability Console in Grid Control 10.2.0.5…finally resorted to manual methods to finish the task.  A combination of DATAGUARD command-line, SQLPLUS and RMAN.  The initial goal of this task was to see how automatic (less work for the DBA) 11gR2 is when creating physical standbys. This post will have expanded information when I start testing Logical Standbys in 11gR2.

A. Plan 1 – GC HA Console

(Supposed to be the Easy One)- Create a physical standby using the GC Availability console, twice. Failed at exactly the same spot the three times it was attempted.  The setup was between identical nodes (same chipset, OS, etc.)  The only option at this point is to create a Service Request. Previous recovery step is successful according to the log, this is the last step of the process. Not seeing errors in the DATAGUARD or alert logs of the primary database.  Seeing TNS Fatal connection errors (TNS-12526, TNS-12564) in the alert log of the physical standby. Initial conclusion seems to be related to this document – Connections to NOMOUNT/MOUNTED or RESTRICTED Databases Fail [ID 362656.1].

B. Plan 2  – RMAN Active Duplication using My Oracle Support (MOS) examples

Create a physical standby using RMAN using Active Duplication, one of the newer features in 11g Database. The following document was as least partly correct.. RMAN ‘Duplicate Database’ Feature in 11G [ID 452868.1] The MOS document contradicts itself when mentioning the required initialization parameters for the auxiliary database:

DB_NAME

CONTROL_FILES

DB_BLOCK_SIZE

DB_FILE_NAME_CONVERT

LOG_FILE_NAME_CONVERT

DB_RECOVERY_FILE_DEST (see RMAN DUPLICATE ERROR RMAN-06136 ORA-19801 PARAMETER DB_RECOVERY_FILE_DEST NOT SET [ID 1068315.1] for the reasoning behind this parameter)

A different list is farther down in the document outlined an example initTEST.ora file which is different than the previous list. This is the start of the wading through MOS…trying to pick the most accurate article for the task I am trying to accomplish. I don’t expect all articles to be EXACTLY what I need, but I do expect them to be reproducible (aka accurate) for the versions listed.

DB_NAME=TEST

diagnostic_dest=’E:\oracle’

DB_FILE_name_CONVERT=(‘I:\app\apadhi\oradata\amar’,’E:\oracle\oradata\test’)

LOG_FILE_NAME_CONVERT=( ‘I:\app\apadhi\oradata\amar’,’E:\oracle\oradata\test’)

SGA_TARGET=262144000

CONTROL_FILES=’E:\oracle\oradata\TEST\control01.dbf’

COMPATIBLE= 11.1.0.0.0

Farther down in the MOS document when you get to the following line:

RMAN>DUPLICATE TARGET DATABASE FOR STANDBY –(I added the for standby part of the line that isn’t in the MOS document example during testing)

TO ‘TEST’

FROM ACTIVE DATABASE

DB_FILE_NAME_CONVERT ‘I:\app\apadhi\oradata\amar’,’E:\oracle\oradata\test’;

A reproducible error occurred when I tried to use the  db_file_name_convert and log_file_name_convert initialization parameters as part of the command.

RMAN-05535: WARNING: All redo log files were not defined properly.

The above RMAN error led me to another document – duplicate for standby fails with RMAN-05535 when path the same as primary [ID 783113.1]. This document led me to make a change in the DUPLICATE command and moving the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to the initTEST.ora file.  This error led me down the wrong path didn’t encounter it using the second improved MOS document.

There were also issues with the primary finding (ORA-12154 TNS:could not resolve service name) the physical standby because I use a static listener entry with a non-default port instead of dynamic service registration.  That issue was first discovered when trying to create a configuration using the DATAGUARD command-line utility – dgmgrl.  Another issue was discovered when using the dgmgrl utility was the case sensitivity of the password file created for the physical standby.  That issue was encountered with the error –  invalid username or password. You either have to directly copy over the password file from the primary or create the password file with ignorecase=Y.  See MOS Document Changing SYS password of PRIMARY database when STANDBY in place to avoid ORA-16191 [ID 806703.1] .

The initialization parameter file and RMAN command that seemed to work for my situation (different node, different data and log file directories, different password file, static listener entry, active duplicate for standby):

db_block_size = 8192

DB_NAME=TEST

compatible = 10.2.0.4.0

CONTROL_FILES=(‘/u01/oradata/TEST/control01.ctl’,’/u01/oradata/TEST/control02.ctl’)

local_listener='(address=(protocol=tcp)(host=nodename)(port=1523))’

DB_FILE_NAME_CONVERT=(‘/u01/oradata/TRNG’,’/u01/oradata/TEST’)

LOG_FILE_NAME_CONVERT=(‘/u01/oradata/TRNG’,’/u01/oradata/TEST’)

diagnostic_dest=’/u01/app/oracle’

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

But all was not right with the world, the archived redo logs from the primary were not being applied.

I knew that by looking at the following sql output on the physical standby.

SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents  from v$managed_standby;

I started to wade through MOS again looking for a better example to follow since I had modified the original command for a physical standby.

First off I found this one to remove all remnants of any existing Dataguard configurations, this one was accurate and well-written.  How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]

While searching for the best way to remove an existing Dataguard configuration, I came across this article:

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE Without Shutting down the Primary and using Primary Active Database Files [ID 1075908.1]

Easier to read and understand from beginning to end than 452868.1, but there are inconsistencies (wouldn’t really call them errors) in this article. In order to do active database duplication the COMPATIBLE initialization parameter needs to be set at a minimum of 11.1.0.0 on both the primary and standby or the following error occurs on creation of the standby with active database duplication:

ERROR when missing COMPATIBLE parameter:

connected to auxiliary database (not started)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 09/01/2010 08:32:41

RMAN-03015: error occurred in stored script Memory Script

RMAN-04014: startup failed: ORA-32012: SPFILE format is inconsistent with value of COMPATIBLE parameter

Since my primary database was still at 10.2.0.4….I have to change its parameter first. The active database duplication process will bring over that same compatible parameter for the physical standby. You cannot change this parameter in memory, it has to be added to the spfile or pfile and the primary database restarted as shown in the following example:

PRIMARY> alter system set compatible=’11.1.0.0.0′ scope=spfile;

System altered.

Last step in the article is to start the recovery process….

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Ok, querying V$MANAGED_STANDBY on the standby database will indicate the progress of archived logs being applied from the primary database. There is a problem, it is waiting. This is know as a gap. The primary most current log is 338 and the physical standby is looking for 337. I also encounter errors about inconsistent properties when trying to adjust settings using the dgmgrl utility.

STANDBY> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

——— ———— ——– ———- ———- ————- ————

ARCH CONNECTED ARCH 0 0 0 0

ARCH CONNECTED ARCH 0 0 0 0

ARCH CONNECTED ARCH 0 0 0 0

ARCH CONNECTED ARCH 0 0 0 0

ARCH CONNECTED ARCH 0 0 0 0

MRP0 WAIT_FOR_LOG N/A 337 0 3 3

This is where I realize the article (1075908.1) has some inconsistencies…or let’s say it left out some important information. I was not able to validate the DATAGUARD configuration following the instructions as written –

1. Initialization parameter for Dataguard broker had to be changed – dg_broker_start=TRUE.

DGMGRL> show configuration verbose;

Configuration – PRMY,STBY

Protection Mode: MaxPerformance

Databases:

PRMY – Primary database

STBY – Physical standby database

Error: ORA-16525: the Data Guard broker is not yet available

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

2. Received errors related to inconsistent properties between DataGuard and what was set with SQLPLUS.

I start referring back to the  11.2 documentation (Creating a Standby Database with Recovery Manager appendix) located at

http://download.oracle.com/docs/cd/E11882_01/server.112/e10700/rcmbackp.htm#SBYDB4988

I modify the instructions from article 1075908.1 to include the dorecover command and change some of the other parameters slightly..

duplicate target database for standby from active database

dorecover

spfile

parameter_value_convert ‘PRMY’,’STBY’

set db_unique_name=’STBY’

set db_file_name_convert=’/PRMY/’,’/STBY/’

set log_file_name_convert=’/PRMY/’,’/STBY/’

set control_files=’/u01/oradata/PRMY/control01.ctl’

set log_archive_dest_2=”service=PRMY ASYNC REGISTER VALID_FOR=(online_logfile,primary_role)”  –required for archive log shipping

set fal_server=’PRMY’

set standby_file_management=’AUTO’;

I can now also verify  DATAGUARD  for this configuration is correct using dgmgrl>

DGMGRL> show configuration verbose;

Configuration – PRMY,STBY

Protection Mode: MaxPerformance

Databases:

PRMY – Primary database

STBY – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

And also verify the redo logs are being applied by querying the standby and switching logs on the primary.

STBY>select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
——— ———— ——– ———- ———- ————- ————
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CLOSING      ARCH            350          1             0            0
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CLOSING      ARCH            348          1             0            0
RFS       IDLE         N/A               0          0             0            0
RFS       IDLE         LGWR            351          7             0            0
RFS       IDLE         UNKNOWN           0          0             0            0
MRP0      APPLYING_LOG N/A             351          7             3            3

PRMY> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 347

Next log sequence to archive 351

Current log sequence 351

Adding the dorecover command applies any outstanding archived logs, starting the managed recovery process so you don’t have to do the last command in the document (1075908.1) as follows:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

11g : Active Database Duplication [ID 568034.1] – You might find this article as well…but not as good as an example as [ID 1075908.1].

Final Conclusions – Since the COMPATIBLE initialization parameter is required to be at 11.1…I will not be using this process for my initial migration/recreation of our physical standbys to 11gR2. We plan on staying at the COMPATIBLE parameter of 10.2.x for several weeks in the primary database so that we can accomplish a downgrade if need be. Staying at 10.2.x will also allow more time to upgrade the Optimizer enabling the newer features in a gradual, controlled process.


Advertisements

About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in 11g, DATA GUARD, grid control, my oracle support, Physical Standby, RMAN. Bookmark the permalink.

One Response to Creating Standby with Active Database Duplication in 11gR2 or also known as Wading through the MOS

  1. charlesdschultz says:

    Thanks for fulling documenting this. We have done several RAC (and non RAC) rman duplications, and the lack of a simple, straight-forward, bullet-proof cookbook has always astounded me. All these things that are supposed to make things easier seem to somehow complicate and infuriate. Not only GC, but the DG Broker as well.

    I would love to see a video of an Oracle Exec trying to follow these metalink notes. I want to see Dr. DBA use Grid Control to dup a RAC database! *grin*

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