In this blog we’ll see how to configure 11g Data Guard using active dataguard i.e. standby database will be created from running primary database . We do not need to take backup of database or controlfile. Primary database dg01 on node1.oracle.com (192.9.201.192)
Standby database dg02 on node2.oracle.com (192.9.201.193)
Listeners :
primary : on port 1521 in grid home
on port 1522 in database home
standby : on port 1521 in grid home
on port 1522 in database home
TNS entries
primary : dg01 on node1.oracle.com on ports 1521, 1522
registered with both the listeners
dg02 on node2.oracle.com on port 1522
primary : dg02 on node2.oracle.com on ports 1521, 1522
registered with both the listeners
dg01 on node1.oracle.com on port 1522
Configuration on primary site:-
——————————————-
- As root assign
. IP address 192.9.201.192
. hostname node1.oracle.com
#service network restart
- Make entries in /etc/hosts
192.9.201.182 node1.oracle.com node1
192.168.2.102 node2.oracle.com node2
– Reboot machine (mandatory as hostname has been changed)
– create dg01 database in archivelog mode , with sample schemas, with FRA default
— Invoke netmgr from db home
- - make service dg01 available on port 1522
- - create a listener in database home on port 1522
- - statically register dg01 with listener on port 1522
- - create service for dg02 running on node2.oracle.com on port 1522
- - delete orcl database
Configuration on standby site:-
——————————————-
- As root assign
. IP address 192.9.201.193
. hostname node2.oracle.com
#service network restart
— Reboot machine (mandatory as hostname has been changed)
- Make entries in /etc/hosts
192.9.201.182 node1.oracle.com node1
192.9.201.193 node2.oracle.com node2
– Test
#ping node1
ping node2
– Invoke netmgr from db home
- - create service for dg01 running on node1.oracle.com on port 1522
- - create service for dg02 running on node2.oracle.com on port 1522
- - create a listener in database home on port 1522
- - statically register dg02 with it
—————————–
On primary node01
—————————–
— Configure the database dg01 in archive log mode and enable the force logging.
SQL>SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=6G;
ALTER DATABASE FORCE LOGGING;
select force_logging from v$database;
ALTER DATABASE OPEN;
– Configure standby redo log files to enable fast failover and real time apply. (Assume we have three redo log group)
– Find out no. and size of current redo log groups
SQL>select group#, bytes/1024/1024 MB from v$log;
– Add standby log files –
Note: – Number of standby redo log group must be more than number of redo log group and size of the standby logfile must be same as the size of redo log file.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/dg01/sredo04.log' size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/dg01/sredo05.log' size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/dg01/sredo06.log' size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/dg01/sredo07.log' size 50m;
– check that standby logfiles have been created
SQL> col member for a30
select group#, TYPE, MEMBER from v$logfile where type = 'STANDBY';
GROUP# TYPE MEMBER
———- ——- ——————————
4 STANDBY /u01/app/oracle/oradata/dg01/sredo04.log
5 STANDBY /u01/app/oracle/oradata/dg01/sredo05.log
6 STANDBY /u01/app/oracle/oradata/dg01/sredo06.log
7 STANDBY /u01/app/oracle/oradata/dg01/sredo07.log
SQL> select group#, bytes/1024/1024 MB from v$standby_log;
GROUP# MB
———- ———-
4 50
5 50
6 50
7 50
– Make a staging folder
oracle@node1$mkdir /home/oracle/stage
— CREATE PFILE FROM SPFILE.
PRI>create pfile='/home/oracle/stage/initdg01.ora' from spfile;
- Now configure following parameters for primary database:-
– add following lines to initdg01.ora
oracle@node1$vi /home/oracle/stage/initdg01.ora
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(dg01,dg02)’
DB_UNIQUE_NAME=’dg01′
SERVICE_NAMES=’dg01′
DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02′,’/u01/app/oracle/oradata/dg01′
LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02′,’/u01/app/oracle/oradata/dg01′
LOG_ARCHIVE_DEST_1=’location=USE_DB_RECOVERY_FILE_DEST’
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_2=’service=dg02 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=dg02′
LOG_ARCHIVE_DEST_STATE_2=enable
STANDBY_FILE_MANAGEMENT=auto
FAL_CLIENT=’dg01′
FAL_SERVER=’dg02′
LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’
Note:
In case source and destination databases are ASM, parameters db_file_name_convert and log_file_name_convert may be defined as follows:
db_file_name_convert = (“+DATA/dg01″,”+DATA/dg02″)
log_file_name_convert = (“+DATA/dg01″,”+DATA/dg02″, “+FRA/dg01″,”+FRA/dg02″)
– create spfile from pfile and start dg01 using new parameters
PRI>shu immediate;
create spfile from pfile='/home/oracle/stage/initdg01.ora';
startup;
—– Take the entire database backup on pre configured staging directory(optional with active dataguard)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
BACKUP FORMAT '/home/oracle/stage/%U' DATABASE PLUS ARCHIVELOG;
BACKUP FORMAT '/home/oracle/stage/%U' CURRENT CONTROLFILE FOR STANDBY;
sql "alter system archive log current";
– create staging folder on node2
oracle@node2$mkdir /home/oracle/stage
—- Copy the following items from node1 to node2.
PASSWORD FILE
[oracle@node1 ~]$scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdg01 node2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdg02
PARAMETER FILE and rman backups(optional with active dataguard)
[oracle@node1 ~]$ scp /home/oracle/stage/* node2:/home/oracle/stage/
Note: – RMAN backup must be copied at the same directory on machine2
i.e. /home/oracle/stage
– Configure net service for dg01 (primary database) on node1 at port 1522
as well as for dg02 (standby database) on node2 at port 1522
set environment to dg01 and invoke netmgr
DG02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dg02)
)
)
DG01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg01)
)
)
– Create a listener in database home on port 1522 and statically register dg01 with it.
—————————-
On standby node02
—————————–
- Edit Pfile for Standby Database:
oracle@node2$mv /home/oracle/stage/initdg01.ora /home/oracle/stage/initdg02.ora
vi initdg02.ora
— Final pfile should look like this
dg02.__db_cache_size=440401920
dg02.__java_pool_size=4194304
dg02.__large_pool_size=4194304
dg02.__shared_pool_size=163577856
dg02.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/dg02/adump’
*.control_files=’/u01/app/oracle/oradata/dg02/control01.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/dg02/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’dg01′
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=6G
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg02XDB)’
*.job_queue_processes=10
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=204472320
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=613416960
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/dg02/udump’
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(dg01,dg02)’
DB_UNIQUE_NAME=’dg02′
SERVICE_NAMES=’dg02′
DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02,’/u01/app/oracle/oradata/dg01′
LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02′,’/u01/app/oracle/oradata/dg01′
LOG_ARCHIVE_DEST_1=’location=USE_DB_RECOVERY_FILE_DEST’
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_2=’service=dg01 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=DG01′
LOG_ARCHIVE_DEST_STATE_2=enable
STANDBY_FILE_MANAGEMENT=auto
FAL_CLIENT=’dg02′
FAL_SERVER=’dg01′
LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’
@node2: Create the required directory for standby database
[oracle@node2 ~]$ mkdir -p /u01/app/oracle/admin/dg02/adump
mkdir -p /u01/app/oracle/oradata/dg02/
mkdir -p /u01/app/oracle/flash_recovery_area/dg02
@node2:– Configure net service for dg01 (primary database) on node1 at port 1522
as well as for dg02 (standby database) on node2 at port 1522
– set oracle home to database home and start netmgr
DG02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dg02)
)
)
DG01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg01)
)
)
@node2 –create a listener on port 1522 in database home and statically dg02 service with it
@node2 : Start dg02 at nomount stage
[oracle@node2 ~]$ export ORACLE_SID=dg02
SBY> CREATE SPFILE FROM PFILE='/home/oracle/stage/initdg02.ora';
STARTUP NOMOUNT ;
EXIT
@node2: Now using RMAN duplicate command create standby database
[oracle@node2 ~]$ export ORACLE_SID=dg02
RMAN TARGET SYS/oracle@dg01
RMAN> CONNECT AUXILIARY SYS/oracle@dg02
DUPLICATE TARGET DATABASE FOR STANDBY from active database;
– Switch logs on primary
PRI>alter system switch logfile;
– start redo apply on standby
SBY>alter database recover managed standby database using current logfile disconnect;
– Verify that standby database is performing correctly
SBY>select sequence#, first_time, next_time from v$archived_log order by sequence#;
– Perform a few log switches on primary
PRI>alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
– On standby database verify that new redo data was received , archived and applied
SBY>select sequence#, first_time, next_time,applied from v$archived_log order by sequence#;
——————————
Related links:
Home
11g Dataguard Index
11g Dataguard: Automatic Block Media Recovery (BMR)
11g Dataguard: Automatic Client Failover
11g Dataguard: Automatic Job Failover
11g Dataguard: Flashback Standby After Resetlogs On Primary
ORA-12528: TNS:listener: all appropriate instances are blocking new connection
Recover Standby Datafile From Primary
Warning: ORA-16792: configurable property value is inconsistent with database setting
————