Oracle 11gR2 Dataguard using Duplicate Active Standby

In this activity we will create Oracle 11gR2 Datagaurd using active duplication. In active duplication, we don’t need to take rman backup and copy the backup to the Standby Server. The database can be duplicated over the network. Its an amazing feature which saves you much time and effort. The only thing you have to be careful while duplicating using this active feature is to make sure the LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT parameters are set properly. And all the requisite directories are created on standby server.

Primary SID : ecc

Standby SID: eccstandby

DB Unique name : ecc

In regards to Oracle 11gR2 take take and set the parameter sec_case_sensitive_logon is set to false.

Let us begin now the excercise.

ORACLE 11GR2 DATAGUARD USING DUPLICATE ACTIVE STANDBY

—————————————————————————————————————————-
Primary Database Steps:
—————————————————————————————————————————-:

1. Put database in archive log mode

Archive log list;

alter system set log_archive_dest_1=’LOCATION=E:\archivelogs’;

shutdown immediate

startup mount

alter database archivelog;

#Enable force logging

alter database force logging;

2. Create password file

select * from v$pwfile_users;

If password file doesnt exist then make the file
If it doesn’t exist, use the following command to create one:
– On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=’F:/ECCPWD.ora’ password=xxxxxxxx ignorecase=y force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)

3. Create standby redo logs;

ALTER DATABASE ADD STANDBY LOGFILE
GROUP 4  ‘E:\oradata\ecc\redo04.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE
GROUP 5  ‘E:\oradata\ecc\redo05.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE
GROUP 6  ‘E:\oradata\ecc\redo06.log’ size 50m;

select * from v$logfile;

select * from v$standby_log;

you have to create these standby logs on Standby Server as well

ADD TNS entry to tnsnames.ora
====================================

eccstandby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdev.qiibonline.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ecc)(UR=A)
)
)
=====================================

4. Edit the newly created pfile and add following parameters to the pfile

show parameter spfile;

create pfile=’F:/eccPRIM.ora’ from spfile;

#########################STANDBY PARAMETERS########################
*.DB_UNIQUE_NAME=’ecc’
*.INSTANCE_NAME=’ecc’
*.DB_FILE_NAME_CONVERT=’eccstandby’,’ecc’*.INSTANCE_NAME=’ecc’
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ecc,eccstandby)’
*.LOG_ARCHIVE_DEST_1=’LOCATION=E:\archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ecc’
*.LOG_ARCHIVE_DEST_2=’SERVICE=eccstandby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eccstandby’
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.LOG_FILE_NAME_CONVERT=’ecc’,’eccstandby’
*.REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’
*.SERVICE_NAMES=’ecc’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.sec_case_sensitive_logon=’FALSE’
*.FAL_CLIENT=’ecc’
*.FAL_SERVER=’eccstandby’
########################################

4. Start database using this pfile and then open database from spfile.

shutdown immediate

startup pfile=’F:\eccPRIM.ora’

create spfile from pfile=’F:\eccPRIM.ora’;

shutdown immediate

startup

5. Backup ECC database using RMAN

rman target /

connected to target database: ECC (DBID=997720117)

show all;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘E:\rmanbkp\%F’;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘E:\rmanbkp\%d_%s_%p.rman’;

show all;

##############RMAN configuration parameters are ######################
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘E:\ORA_BACKUPS\
%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘E:\ORA_BACKUPS\%d_%s_%p.rman’;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFECC.ORA’; # default
#########################################################################

sql “alter system switch logfile”;

sql “alter system archive log current”;

—————————————————————————————————————————-
Standby Database Steps:
—————————————————————————————————————————-

create folders of adump, diag

Create archive log folder ‘F:\archivelogs’

copy the previously create pfile called eccPRIM.ora to standby server and rename it to eccSTANDBY.ora

Add the following parameters to the pfile called eccSTANDBY.ora

################ Standby Parameters #####
*.DB_FILE_NAME_CONVERT=’ecc’,’eccstandby’
*.DB_NAME=’ecc’
*.DB_UNIQUE_NAME=’eccstandby’
*.INSTANCE_NAME=’eccstandby’
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ecc,eccstandby)’
*.LOG_ARCHIVE_DEST_1=’LOCATION=F:\archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=eccstandby’
*.LOG_ARCHIVE_DEST_2=’SERVICE=ecc VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ecc’
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.LOG_FILE_NAME_CONVERT=’ecc’,’eccstandby’
*.REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’
*.SERVICE_NAMES=’eccstandby’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.sec_case_sensitive_logon=’FALSE’
*.FAL_CLIENT=’eccstandby’
*.FAL_SERVER=’ecc’
###############################################

2. Create listener entires and tns entries on both ECC and ECCSTANDBY server

==================
On Primary Server
==================

###Listener Entry###

Take backup of previous listener file. Change remove old entry and add this new entry

ECC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eccdbtest.qiibonline.com)(PORT = 1521))
)
SID_LIST_ECC =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ecc_DGMGRL)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(SID_NAME = ecc)
)
)

lsnrctl start

lsnrctl status

connect a toad session from your pc and check if listener is able to establish the connection.

### TNS entry ###

ECC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eccdbtest.qiibonline.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ecc)(UR=A)
)
)

eccstandby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdev.qiibonline.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = eccstandby)(UR=A)
)
)

==================
On Standby Server
==================

### Listener entry ###

ECCSTANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdev.qiibonline.com)(PORT = 1521))
)
SID_LIST_ECCSTANDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = eccstandby_DGMGRL)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(SID_NAME = eccstandby)
)
)

### TNS Entries ####

ECCSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdev.qiibonline.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = eccstandby)(UR=A)
)
)

ECC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = eccdbtest.qiibonline.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ecc)(UR=A)
)
)

Stop and Start the Listeners on both servers using command “LSNRCTL STOP”
“LSNRCTL START” wait for few seconds to check services on listener on the primary server

3. Create password file for the Standby server

orapwd file=F:\ECCPWD.ora password=sys ignorecase=y force=y

note: sys password must be identical for both primary and standby database

4. Create Service on Windows

Since we are creating data guard on windows we need to manually create a service using oradim utility. On unix you can safely skip this step

## FORMAT ## oradim -new -sid %ORACLE_SID% -intpwd MYSECRETPASSWORD -startmode MANUAL -pfile

c:\>oradim -new -sid ECCSTANDBY -intpwd sys -startmode AUTO -pfile ‘F:\eccSTANDBY.ora’

#pfile here is the pfile you created for the Standby server previously called eccSTANDBY.ora’

PASSWORD is the password that is used to connect / as sysdba.
MODE indicates how the service is started. It must either be auto or manual. auto tells Windows to automatically start the service when Windows is booted. manual, on the other hand, requires someone to manually start the service.

set ORACLE_SID=ECCSTANDBY

sqlplus “/as sysdba”

startup nomount pfile=’F:\eccSTANDBY.ora’

create spfile from pfile=’F:\eccSTANDBY.ora’

show parameter fal;

shutdown immediate

startup nomount

show parameter fal;

5. Creating Standby using RMAN

rman target sys/sys@ECC auxiliary sys/sys@eccstandby

connected to target database: ECC (DBID=997720117)
connected to auxiliary database: ECC (not mounted)

confirm from primary the DBID of ECC target (DBID=997720117) is same

go to primary server and crosscheck archivelogs and backupsets and delete expired or obsolete objects and relogin to auxiliary

Also if you get some errors make sure you create the subfolders “eccstandby” and “ecc” in following path : E:\oracle\product\10.2.0\oradata

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

##################################################
starting media recovery

archive log thread 1 sequence 9 is already on disk as file F:\ARCHIVELOGS\LOG1_9
_737729399.ARC
archive log thread 1 sequence 10 is already on disk as file F:\ARCHIVELOGS\LOG1_
10_737729399.ARC
archive log thread 1 sequence 11 is already on disk as file F:\ARCHIVELOGS\LOG1_
11_737729399.ARC
archive log filename=F:\ARCHIVELOGS\LOG1_9_737729399.ARC thread=1 sequence=9
archive log filename=F:\ARCHIVELOGS\LOG1_10_737729399.ARC thread=1 sequence=10
archive log filename=F:\ARCHIVELOGS\LOG1_11_737729399.ARC thread=1 sequence=11
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-DEC-10
Finished Duplicate Db at 15-DEC-10
####################################################

7. Put the Standby into Managed standby mode:
ON standby server

shutdown immediate;

STARTUP NOMOUNT

alter database mount standby database;

alter database recover managed standby database disconnect from session;

Go to the Primary Server:

SQL> alter system switch logfile;
SQL> alter system archive log current;

—————————————————————————————————————————-Verify the sync between primary and standby:
—————————————————————————————————————————-

From primary
sql > select max(sequence#) as “PRIMARY” from v$log_history;

From Standby

SQL > select max(sequence#) as “STANDBY”,applied from v$archived_log group by applied ;

#####Creating any tablespace on primary server will automatically create those same tablespace datafiles on new server. But this is not applicable to temporary files and undo files. It is good practise to make temporary file size same on standby server manually. Also resize your undo tablespace accordingly.

Also Restart the MRP process on Standby using below command if datafile on primary didnt transfer to secondary.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
=====================================================================================

Invoking a DR Standby Database
Overview

There are different methods you can use to invoke a standby database depending on the typical DR scenario you are facing.

Scenario for usage.

1. Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment.

This scenario can be used if both the Primary and Standby Servers are available so that you can connect to both environments simultaneously and perform a clean switchover.

2. Activating a Standby Database

This method will activate the Standby Database as the primary database recovering up to the point of the last log shipment.  After activating a standby database as the primary the original primary server becomes obsolete and will need to be rebuilt as a standby database (e.g. you can not just switch the servers back to act as a primary or standby database and will need to rebuild the data-guard environments).

This scenario can be used if the primary server is not available and you need to force the standby database to become the primary.

3. Opening the standby Database in Read-Only Mode

This method involves stopping data-guard replication and opening the standby database in read-only mode for querying.  The database can then be shutdown and reopened in standby mode so that data-guard replication can resume (assuming all required archive logs are still available)

This scenario can be used if you want to open the standby database temporarily for querying data and then wish to put the database back into standby mode.

The following section describes how to perform each of the above methods of invoking a standby database:

1. Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment:

On Primary Server:

SQL> alter database commit to switchover to standby;

This may cause the following error to be generated:

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

If this does occur then restart the database, as below, before retrying the above command:

SQL> shutdown immediate

SQL> startup

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

The primary server is now configured as a DR standby database.

On DR Server:

SQL> alter database recover managed standby database cancel;

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate

SQL> startup

The DR server is now configured as the primary database.

To switch back you just need to repeat the above process but the other way around (e.g. convert the DR database back to a standby and the primary database back to primary).

2. Activating a Standby Database

If the primary database is not available the standby database can be converted into the primary database as follows:

SQL> alter database recover managed standby database cancel;

SQL> alter database activate standby database;

SQL> shutdown immediate

SQL> startup

The original primary database is now obsolete and can be rebuilt as a standby database once it is available again.

3. Opening the Standby Database in Read Only Mode

The standby database can be opened in read only mode for querying and then converted back into a standby database without affecting the primary.

On standby server:

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

The standby database is now open and available for querying in read only mode.

To put the standby database back into standby mode:

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

=====================================================================================

How to check whether the Standby Database is in Sync

On the primary server:

SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history;

On the standby server:

SQL> SELECT max(sequence#) AS “STANDBY”, applied  FROM v$archived_log GROUP BY applied;

The standby database is in sync with the primary database if the above PRIMARY value matches the above STANDBY value where applied = ‘YES’.

Advertisements

5 thoughts on “Oracle 11gR2 Dataguard using Duplicate Active Standby

    1. Dataguard is pretty agnostic to platforms. Specially Linux, Solaris and AIX doesn’t seem to have any special considerations. The only difference is windows. When you are creating the standby database using RMAN.First you have to create the instance using oradim.

      I think this article was written when I was doing testing on a windows server 2003 for data guard.

      Like

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