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