Had a sit down last week to get Oracle DataGuard running with ASM on my laptop. Following are the results of the exercise. Basically a step by step on how to create a backup based physical standby using RMAN command where ASM is being used for storage.
Disclaimer: One or more statements in this blog might be incorrect, including this one.
Introduction
Environment Description
A. Physical Machines
B. Oracle Instances and Disk
Groups
Assumptions
DG Creation steps
1. PREPARE INFRASTRUCTURE
1.
Prepare First node (DG1) which will hold the initial Primary DB of the DG
configuration
configuration
2.
Prepare Second node (DG2)
2. CONFIGURE THE MACHINES FOR REDO TRANSPORT
1. Configure Oracle NET
2. Create Password File for Remote Admin
access
3. CONFIGURE PRIMARY AND STANDBY DATABASES FOR THE
DATAGUARD CONFIGURATION
DATAGUARD CONFIGURATION
1.
Create Standby Redo logs (DG1)
2.
Enable Archiving, Force logging and flashback on ORCLA (DG1)
3.
Set required parameters (DG1)
4. Create PFILE from SPFILE, edit it to be used as standy pfile and send
it
over to DG1 at $ORACLE_HOME/dbs/
over to DG1 at $ORACLE_HOME/dbs/
4. BACKUP PRIMARY DATABASE, ARCHIVELOGS and
CONTROLFILE FOR STANDBY
CONTROLFILE FOR STANDBY
5. START STANDBY INSTANCE (ORCLB) (DG2)
6. CREATE STANDBY USING RMAN FROM STANDBY
SITE (DG2)
7. VERIFY REDO TRANSPORT
8. START REDO APPLY (DG2)
9. ACTIVE DATAGUARD
10. REAL TIME APPLY
11. SNAPSHOT STANDBY
1. Steps to convert physical
standby to snapshot standby
2.
Steps to convert snapshot standby to physical standby
Introduction
Oracle Data Guard is a high availability, data protection, and
disaster recovery solution that comes bundled with the Oracle database 11g
Enterprise Edition.
To understand in layman terms, the Data Guard provides
redundancy at the database level, as the RAC provides redundancy at the
instance level.
Data Guard provides many services that create, maintain,
manage, and monitor various standby databases (physical/logical) to protect the
primary database from disasters and data corruption.
Data guard is an important element in the Oracle Maximum
Availability Architecture (MAA).
Environment Description
A. Physical Machines:
Two
nodes DG1 and DG2 were configured with following IP addresses:
DG1:
192.168.4.1
DG2:
192.168.4.2
B. Oracle Instances and Disk Groups
Below
are the details of the RDBMS/ASM instances, ASM Disk groups on both the nodes
ASM
instance @DG1: +ASM
ASM
instance @DG2: +ASM
RDBMS
instance @DG1: ORCLA | Disk groups: DATA (for db files), FRA (recovery files)
RDBMS
instance @DG2: ORCLB | Disk groups: DATA (for db files), FRA (recovery files)
Assumptions
It is assumed that
1. the physical machines have been setup and are able to
ping each other;
/etc/hosts file entries have been configured.
/etc/hosts file entries have been configured.
2. following groups have been created on both the
machines:
dba, oinstall, asmadmin, asmdba, asmoper
dba, oinstall, asmadmin, asmdba, asmoper
3. following users have been created on both the machines:
oracle
-g oinstall -G oinstall,dba,asmdba
grid -g oinstall -G
oinstall,asmadmin,asmdba,asmoper
4. 'oracleasm' has been configured on both the servers
and ASM disks been
created by its use thereof.
created by its use thereof.
5. the Oracle RDBMS software has been installed on both
the machines in
/u01/app/oracle/product/11.2.0/dbhome_1
6. the Grid Infrastructure has been installed on both the
machines in
/u01/app/grid/product/11.2.0/gridhome_1
7. ASM diskgroups DATA, FRA have been created on both the
machines
DG Creation steps
1. PREPARE INFRASTRUCTURE
1. Prepare First node (DG1) which will hold the initial Primary DB
of the DG configuration:
i.
Login as 'oracle'. Install Oracle RDBMS Software
ii.
Login as 'grid'. Install Grid Infrastructure Software
iii.
Login as 'root'.Configure ASM disks DATA, FRA using oracleasm
iv.
Login as 'grid'.Create ASM diskgroups DATA, FRA using ASMCA or
ASMCMD utility
v.
Login as 'oracle'.Create database using ASM as storage using the DBCA utility
2.
Prepare Second node (DG2)
i.
Login as 'oracle'. Install Oracle RDBMS Software
ii.
Login as 'grid'. Install Grid Infrastructure Software
iii.
Login as 'root'.Configure ASM disks DATA, FRA using oracleasm
iv.
Login as 'grid'.Create ASM diskgroups DATA, FRA using ASMCA or ASMCMD utility
2. CONFIGURE THE MACHINES FOR REDO TRANSPORT
1. Configure Oracle NET
i.
Create Services (ORCLA, ORCLB) in TNSNAMES.ora file on both the databases.
ORCLA =
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.4.1)(PORT = 1521))
)
(CONNECT_DATA
=
(SERVICE_NAME = orcla)
)
)
ORCLB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclb)
)
)
ii.
Create SID_LIST entries to statically register these services
in LISTENER.ora
file for both the nodes.
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcla)
(SID_NAME=orcla)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=orclb)
(SID_NAME=orclb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
.
.
)
It
is recommended to use the NETMGR utility for this as these files
are very cryptic and sensitive to whitespaces.
are very cryptic and sensitive to whitespaces.
Verify the service configuration using TNSPING utility.
2. Create
Password File for Remote Admin access
i. Create
password file for the ORCLA (DG1) database
orapwd
file=orapworcla password=mypassword
ii. Send
over this password file to DG2 at location
$ORACLE_HOME/dbs/ and rename to
orapworclb
DG1>
scp orapworcla oracle@dg2:$ORACLE_HOME/dbs/
DG2> mv orapworcla orapworclb
3. CONFIGURE PRIMARY AND STANDBY DATABASES FOR THE
DATAGUARD CONFIGURATION
DATAGUARD CONFIGURATION
1. Create
Standby Redo logs (DG1)
The standby redo logs (SRL) are just like the ordinary online redo logs
(ORL), just that they receive redo data from another (primary) database.
In case the SRL are configured, the atomic metric of redo data sent from
the primary to standby is the change vector whereas if the online redo logs
are not configured, the atomic metric of redo data sent from the primary to
standby is the archive logfile.
The standby redo logs (SRL) are just like the ordinary online redo logs
(ORL), just that they receive redo data from another (primary) database.
In case the SRL are configured, the atomic metric of redo data sent from
the primary to standby is the change vector whereas if the online redo logs
are not configured, the atomic metric of redo data sent from the primary to
standby is the archive logfile.
Oracle
recommends that the size of SRL's be equal to the size of ORL's
and that the number of SRL's be one greater than number of ORL's for
each thread.
and that the number of SRL's be one greater than number of ORL's for
each thread.
This
is to account for gap between redo generation rate on primary and
redo archival rate on standby.
redo archival rate on standby.
n -> count of ORL groups (in my
case it was 3)
n+1 -> count of SRL groups
(recommended)
size of ORL file = size of SRL file
(recommended)
Use following statement to find
the redo log groups, members and
the size of the online redo files.
the size of the online redo files.
select
a.group#, member, bytes/1024/1024 size_mb, type
from v$log a, v$logfile b
where a.group#=b.group#;
from v$log a, v$logfile b
where a.group#=b.group#;
SQL> ALTER DATABASE ADD STANDBY LOGFILE
GROUP 4 ('+DATA', '+FRA')
size 50M;
size 50M;
SQL>
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('+DATA', '+FRA')
size 50M;
size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE
GROUP 6 ('+DATA', '+FRA')
size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE
GROUP 7 ('+DATA', '+FRA')
size 50M;
2. Enable
Archiving, Force logging and flashback on ORCLA (DG1)
i.
Enable
Archiving (required)
SQL>
SHUTDOWN IMMEDIATE
SQL>
STARTUP MOUNT
SQL>
ALTER DATABASE ARCHIVELOG;
ii.
Enable
Force Logging (required)
SQL>
ALTER DATABASE FORCE LOGGING;
iii.
Turn
Flashback on (recommended and also required
for certain operations)
for certain operations)
SQL>
ALTER DATABASE FLASHBACK ON;
SQL>
ALTER DATABASE OPEN;
3. Set
required parameters (DG1)
i.
SQL> alter system set DB_UNIQUE_NAME=orcla scope=both;
ii.
SQL> alter system set
LOG_ARCHIVE_CONFIG='dg_config=(orcla,orclb)'
LOG_ARCHIVE_CONFIG='dg_config=(orcla,orclb)'
scope=both;
iii. SQL> alter system set
LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST
LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST
valid_for=(all_logfiles,all_roles)
db_unique_name=orcla'
scope=both;
iv.
SQL> alter system set
LOG_ARCHIVE_DEST_2='service=orclb
LOG_ARCHIVE_DEST_2='service=orclb
valid_for=(online_logfiles,primary_roles)
db_unique_name=orclb'
scope=both;
v. REMOTE_LOGIN_PASSWORDFILE
= EXCLUSIVE
vi.
SQL> alter system set FAL_SERVER=orclb scope=both;
vii. SQL> alter system set STANDBY_FILE_MANAGEMENT=auto
scope=both;
The parameters
DB_FILE_NAME_CONVERT | LOG_FILE_NAME_CONVERT
have not been used because we will use the DB_CREATE_FILE_DEST parameter
in the initorclb.ora file
have not been used because we will use the DB_CREATE_FILE_DEST parameter
in the initorclb.ora file
When creating standby database with ASM (which in turn uses
OMF), we can use
either DB_CREATE_FILE_DEST or the CONVERT parameters in the
standby target's init.ora file.
either DB_CREATE_FILE_DEST or the CONVERT parameters in the
standby target's init.ora file.
We'll use DB_CREATE_FILE_DEST='+DATA'
4. Create PFILE from SPFILE, edit it to
be used as standy pfile and send it over
to DG1 at $ORACLE_HOME/dbs/
i. Create PFILE
SQL>
create PFILE='$HOME/pfile/sby_pfile.ora' from spfile;
ii. Parameters of note in the standby PFILE
a.
DB_UNIQUE_NAME = orclb
b.
DB_NAME = orcla
c.
LOG_ARCHIVE_CONFIG='dg_config(orcla,orclb)'
d.
LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST
valid_for=(all_logfiles,all_roles)
db_unique_name=orclb'
e. LOG_ARCHIVE_DEST_2='service=orcla
valid_for=(online_logfiles,primary_roles)
valid_for=(online_logfiles,primary_roles)
db_unique_name=orcla'
f.
REMOTE_LOGIN_PASSWORDFILE
= EXCLUSIVE
g.
FAL_SERVER=orcla
h.
STANDBY_FILE_MANAGEMENT =
AUTO
5. Create required Directories (DG2)
Based
on the parameters present in the init.ora file for the
orclb, create the directories.
orclb, create the directories.
4. BACKUP PRIMARY
DATABASE, ARCHIVELOGS and CONTROLFILE
FOR STANDBY
FOR STANDBY
1. Backup database plus archivelog
The controlfile for standby has
to be backed up seperately because the
include current controlfile for
standby' works only with backupsets
containing only the datafiles, while we are
also backing up archivelogs.
RMAN> backup FORMAT
'$HOME/bkups/%U.dbf'
database
plus archivelog tag=for_stand;
2.
Backup controlfile for standby
RMAN>
backup FORMAT '$HOME/bkups/%U.dbf
current
controlfile for standby;
3. Copy these files to identical
location on the DG2
$
scp $HOME/bkups/*
oracle@dg2:$HOME/bkups/
5. START STANDBY
INSTANCE (ORCLB) (DG2)
Start the Standby database
instance ORCLA
SQL> startup nomount
6. CREATE STANDBY
USING RMAN FROM STANDBY SITE (DG2)
1. Connect to target and auxiliary using RMAN
RMAN>
connect target sys/mypassword@orcla
RMAN>
connect auxiliary /
2.
Run DUPLICATE Command
RMAN>
run {
allocate
channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
DUPLICATE TARGET DATABASE FOR STANDBY
nofilenamecheck;
}
allocate auxiliary channel c2 device type disk;
DUPLICATE TARGET DATABASE FOR STANDBY
nofilenamecheck;
}
7. VERIFY REDO TRANSPORT
Check that the redo transport is
happening.
1. Check
current log sequence number on the Primary
DG1_SQL>
archive log list
2. Check
current log sequence number on the Standby
DG2_SQL> archive log list
3. Switch
log group on Primary
DG1_SQL> alter system switch logfile;
4. Check
current log sequence number on the Standby.
It should have changed.
DG2_SQL> archive log list
8. START REDO APPLY
(DG2)
After confirming that redo transport is happening, we can start the redo apply
on the standby database. In Physical Standby database, the MRP process is
responsible for the redo apply.
SQL> alter database RECOVER MANAGED STANDBY DATABASE
disconnect;
After confirming that redo transport is happening, we can start the redo apply
on the standby database. In Physical Standby database, the MRP process is
responsible for the redo apply.
SQL> alter database RECOVER MANAGED STANDBY DATABASE
disconnect;
9. ACTIVE DATAGUARD
When redo apply is active, the physical standby database cannot be open because
it is constantly in recovery mode. If we OPEN the database in READ ONLY
mode, we have to stop the redo apply.
Active dataguard is a feature available in Oracle 11g Enterprise Edition that allows
the standby database to be open in READ ONLY mode with redo apply active.
Active dataguard requires a separate licence and entices extra cost.
When redo apply is active, the physical standby database cannot be open because
it is constantly in recovery mode. If we OPEN the database in READ ONLY
mode, we have to stop the redo apply.
Active dataguard is a feature available in Oracle 11g Enterprise Edition that allows
the standby database to be open in READ ONLY mode with redo apply active.
Active dataguard requires a separate licence and entices extra cost.
To enable Active Dataguard,
after STEP 8, perform the following steps
on standby db (orclb)
i.
SQL> shutdown immediate
ii.
SQL> startup -- the physical standby
will
open in READ ONLY mode
iii.
SQL> alter database RECOVER MANAGED STANDBY DATABASE
disconnect; -- start redo apply
Now the database is in 'READ ONLY WITH APPLY' mode and ACTIVE dataguard
is configured
10. REAL TIME APPLY
In normal redo apply mode, the MRP process applies the redo data from
standby's Archived logs to the DB.
With Real Time Apply, the MRP process applies the redo data directly from
the Standby Redo Logs (SRL). The Standby redo logs are archived normally.
is configured
10. REAL TIME APPLY
In normal redo apply mode, the MRP process applies the redo data from
standby's Archived logs to the DB.
With Real Time Apply, the MRP process applies the redo data directly from
the Standby Redo Logs (SRL). The Standby redo logs are archived normally.
This enables standby database to
be updated with the redo data as soon as
it is received and written to the SRL
by the RFS process.
To start Real time apply use the
"USING CURRENT LOGFILE" clause when
starting the redo apply as:
SQL>
alter database recover managed standby database USING
CURRENT LOGFILE disconnect;
11. SNAPSHOT STANDBY
A physical standby DB can be
converted to a snapshot standby which is a READ WRITE image of the primary database
in the DG configuration, from a particular instance of time.
Once the physical standby DB is
converted to the snapshot standby database, the REDO APPLY cannot be active,
but the redo transport keeps happening. The snapshot standby thus keeps
diverging from the primary database because of lack of redo apply and also
because of the changes being done on itself, since it is in Read Write mode.
Flashback database must be
turned on to convert the snapshot standby back to physical standby.
1. Steps to convert physical
standby to snapshot standby
i. Ensure that standby database is mounted but not open
SQL> select OPEN_MODE from v$database;
ii. Stop redo apply if it is active
SQL> alter database RECOVER MANAGED
STANDBY
DATABASE cancel;
DATABASE cancel;
iii. Turn Flashback on (Best Practice)
SQL> alter database flashback on;
iv. Convert physical standby to snapshot standby
SQL> ALTER DATABASE CONVERT TO SNAPSHOT
STANDBY;
STANDBY;
2. Steps to convert snapshot standby to physical standby
i. Ensure that standby database is mounted but not open
SQL> select OPEN_MODE from v$database;
ii. Convert physical standby to snapshot standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL
STANDBY;
iii. Start Redo apply
SQL> alter database recover managed
standby
database USING CURRENT LOGFILE disconnect;
database USING CURRENT LOGFILE disconnect;
