Sunday, January 26, 2020

Oracle 11 g data guard configuration for windows server

We will discuss here oracle 11g data guard configuration for windows server. Primary and Standby server information is given below
Primary Server:
====================
IP: 192.168.1.100
db_name=db11g
db_unique_name=db11g
Stand by Server:
====================
IP: 192.168.1.103
db_name=db11g
db_unique_name=db11g_stby
Primary Server Setup:
=====================
We already installed RDBMS software with one database in primary server.
First check the primary database is in archivelog mode.
SQL>select log_mode from v$database;
if database is in noarchivelog mode, performe below step to switch database in archive mode
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL> alter database open;
Check database is force logging mode using below command
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
if database is not force logging mode change it using below command
SQL>ALTER DATABASE FORCE LOGGING;
Verify DB_NAME and DB_UNIQUE_NAME using below command and both query output will be db11g
SQL> show parameter db_name
SQL> show parameter db_unique_name
Now change archive log configuration using below command here db11g_stby is Standby database unique name
SQL> alter system set log_archive_config=’DG_CONFIG=(db11g,db11g_stby)’; 
Note: both server db_name will be same that is db11g
Set suitable remote archive log destinations.Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The LOG_ARCHIVE_FORMAT and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
SQL>ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
We need to set the following parameters.
SQL> ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
SERVICE SETUP
=====================
both server (Primary & Standby) tns file have same entry that are given below
DB11G =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = db11g)
  )
 )
DB11G_STBY =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVICE_NAME = db11g_stby)
Backup Primary Database using rman 
===================================
$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Create Standby Controlfile and PFILE using following command
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\db11g_stby.ctl';
SQL>CREATE PFILE='D:\initDB11G_stby.ora' FROM SPFILE;
Standby Server Configuration
===============================
Standby server installed RDBMS software only and create listener and tns file conatain same as primary server
copy RMAN backup, control file and pfile to Standby server
Amend following paramiter to pfile for the standby database.
*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
Standby server create same direcotry as primary server and copy files from primary server to standby server. Server's directory are same which are given below
Control file: D:\app\Masud\oradata\db11g 
RMAN Backup: D:\app\Masud\product\11.2.0\dbhome_1\database
Tns file: D:\app\Masud\product\11.2.0\dbhome_1\NETWORK\ADMIN
pfile: D:\app\Masud\product\11.2.0\dbhome_1\dbs
Restore Backup in Standby Server
======================================
START Listener
SQL> sqlplus / as sysdba
SQL>CREATE spfile from pfile='D:\app\Masud\product\11.2.0\dbhome_1\dbs\initDB11G_stby.ora';
Restore backup using rman
$rman target=/
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
Create Redo Logs
==================
Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
SQL>ALTER DATABASE ADD LOGFILE ('D:\app\Masud\oradata\db11g\online_redo01.log') SIZE 50M;
SQL>ALTER DATABASE ADD LOGFILE ('D:\app\Masud\oradata\db11g\online_redo02.log') SIZE 50M;
SQL>ALTER DATABASE ADD LOGFILE ('D:\app\Masud\oradata\db11g\online_redo03.log') SIZE 50M;
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). 
SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('D:\app\Masud\oradata\db11g\standby_redo01.log') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('D:\app\Masud\oradata\db11g\standby_redo02.log') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('D:\app\Masud\oradata\db11g\standby_redo03.log') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('D:\app\Masud\oradata\db11g\standby_redo04.log') SIZE 50M;
Create Standby Redo Logs on Primary Server
The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.
SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo01.log') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo02.log') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo03.log') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE ('D:\app\Masud\oradata\db11g\standby_redo04.log') SIZE 50M;
Start Apply Process
============================
# Background redo apply. Control is returned to the session once the apply process is started.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, issue the following command.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
check the sequence detail using below command to primary server
SQL>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
Now go to the standby server and check the applied log sequence
SQL>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
Steps to configure read only STANDBY
======================================
SQL>Shu immediate
SQL>startup mount;
SQL>alter database open read only;

Oracle 11 g data guard configuration for windows server

We will discuss here oracle 11g data guard configuration for windows server. Primary and Standby server information is given below Primar...