Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, July 08, 2012

Oracle Data Guard Configuration (DGMGRL) (11gR2 Windows 2008R2)

Brief:
Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. DG broker does not have the ability to create standby and is used for managing the dataguard configuration.

Task:  Create  physical standby database for an existing primary database. Both primary and standby would be on same physical machine.
PRM = primary db 192.168.26.11
STBL = local Standby 192.168.26.11

Assumptions:
Following are the assumption , please change the values accordingly.
i)   Primary database is already available
ii)  FRA folder is already existing eg; d:\app\inam\FRA
iii) TNS entries are existing in tnsnames.ora eg;

PRM =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.11)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = PRM)    )  )

STBL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.11)(PORT = 1622))    (CONNECT_DATA =
      (SERVER = DEDICATED)      (SERVICE_NAME = STBL)    )  )
iv)  Listener is properly configured for both primary and standby. For standby database, static registration of database to listener is existing eg;
LISTENERDG2 =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.11)(PORT = 1622))      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1622))   )  )

ADR_BASE_LISTENERDG2 = D:\product\11.2.0.3\dg_2

SID_LIST_LISTENERDG2=   (SID_LIST=      (SID_DESC=         (SID_NAME=dg4msql)         (ORACLE_HOME=D:\product\11.2.0.3\dg_2)         (PROGRAM=dg4msql)      )      (SID_DESC=         (SID_NAME=STBL)         (ORACLE_HOME=D:\product\11.2.0.3\dg_2)      )   )

Process:
1- Make your primary (PRM) database in archive log mode (if it is not) and force logging must be true also. My primary database is already in archived log mode.
D:\app\Inam>set ORACLE_SID=PRM
D:\app\Inam>sqlplus / as sysdba
SQL> select name,log_mode,database_role,force_logging from v$database;

NAME      LOG_MODE     DATABASE_ROLE    FOR
--------- ------------ ---------------- ---
PRM       ARCHIVELOG   PRIMARY                    NO

2-  As standby database is not existing already , we have to create it. I'll create the necessary folders for standby db, instance name would be STBL on the same host.
I created the folder on the following location for Oracle Datafiles and flash recovery area
D:\app\Inam\oradata\STBL\
      - CONTROLFILE
D:\app\Inam\FRA\STBL

3- Create the pfile (for STBL) from the primary DB (PRM) spfile 

SQL> create pfile='D:\app\Inam\oradata\STBL\PFILESTBL.ora' from spfile;

File created.


4- Create the control file for standby (STBL) while being connected with primary db (PRM)

SQL> alter database create standby controlfile as 'D:\app\Inam\oradata\STBL\CONTROLEFILE\CONTROL01.CTL';

Database altered.

5- as pfile(PFILESTBL.ora)is text file, edit this file and rename/add all necessary parameters (eg;control_file, db_name,db_unique_name, db_recovery_file_dest).
Remember, db_name parameter for the standby database must be the same as primary (PRM) but db_unique_name must be different (STBL)
Following is mine pfile to be used for standby database.
############## PFILESTBL.ora##############################

stbl.__db_cache_size=872415232
stbl.__java_pool_size=16777216
stbl.__large_pool_size=16777216
stbl.__oracle_base='D:\app\Inam'#ORACLE_BASE set from environment
stbl.__pga_aggregate_target=872415232
stbl.__sga_target=1275068416
stbl.__shared_io_pool_size=0
stbl.__shared_pool_size=335544320
stbl.__streams_pool_size=16777216
*.audit_file_dest='D:\app\Inam\admin\stbl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\Inam\oradata\STBL\CONTROLEFILE\CONTROL01.CTL'
*.db_block_size=8192
*.db_domain=''
*.db_name='PRM'
*.db_unique_name='STBL'
*.db_recovery_file_dest='D:\app\Inam\FRA'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='D:\app\Inam'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stblXDB)'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=2147483648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
############################################################


6- create the Oracle instance and password file for standby database.

D:\app>ORADIM -NEW -SID STBL
Instance created.

7- Copy password file from the primary db (PRM) for standby db (STBL) and rename it accordingly.
eg;
D:\app\Inam\product\11.2.0.3\dbhome_1\database\PWDPRM.ora
copied and renamed to
D:\app\Inam\product\11.2.0.3\dbhome_1\database\PWDSTBL.ora

8- startup the database with noumount using the parameter file modified in step 5
d:\product\11.2.0.3\dg_2\BIN>set oracle_sid=STBL
d:\product\11.2.0.3\dg_2\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 4 11:32:21 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>

you can create the spfile from the pfile and startup the database

SQL> create spfile from pfile='D:\app\Inam\oradata\STBL\PFILESTBL.ora';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2256912 bytes
Variable Size            1258295280 bytes
Database Buffers          872415232 bytes
Redo Buffers                4919296 bytes

Standby database started in nomount state. Show/verify  the controlfile location
SQL> show parameter control_f

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      D:\APP\INAM\ORADATA\STBL\CONTR
                                                 OLEFILE\CONTROL01.CTL

So mount the database after this verfication
SQL> alter database mount;

Database altered.

9- Now check the datafiles for standby (STBL), it will be showing the datafiles as the controlfile info, although these datafiles are not existing. You have to copy the primary database datafiles as well as logfiles to standby database location

SQL> select name from v$datafile;

NAME
--------------------------------------
D:\APP\INAM\ORADATA\PRM\SYSTEM01.DBF
D:\APP\INAM\ORADATA\PRM\SYSAUX01.DBF
D:\APP\INAM\ORADATA\PRM\UNDOTBS01.DBF
D:\APP\INAM\ORADATA\PRM\USERS01.DBF
D:\APP\INAM\ORADATA\PRM\EXAMPLE01.DBF

So just shutdown the primary database (PRM) and copy on the standby (STBL) location. Don't copy controlfile of primary database.
D:\app\Inam>set ORACLE_SID=PRM

D:\app\Inam>sqlplus / as sysdba

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

D:\>copy D:\app\Inam\oradata\PRM\*.DBF D:\app\Inam\oradata\STBL
D:\app\Inam\oradata\PRM\EXAMPLE01.DBF
D:\app\Inam\oradata\PRM\SYSAUX01.DBF
D:\app\Inam\oradata\PRM\SYSTEM01.DBF
D:\app\Inam\oradata\PRM\TEMP01.DBF
D:\app\Inam\oradata\PRM\UNDOTBS01.DBF
D:\app\Inam\oradata\PRM\USERS01.DBF
        6 file(s) copied.

D:\>copy D:\app\Inam\oradata\PRM\*.LOG D:\app\Inam\oradata\STBL
D:\app\Inam\oradata\PRM\REDO01.LOG
D:\app\Inam\oradata\PRM\REDO02.LOG
D:\app\Inam\oradata\PRM\REDO03.LOG
        3 file(s) copied.
after copying the files startup the primary database (PRM)

10- rename the files for standby database (STBL),We can find the datafile, logfile, tempfile current location in standby controlfile using v$datafile,v$logfile,v$tempfile dynamic views.


d:\product\11.2.0.3\dg_2\BIN>set oracle_sid=STBL
d:\product\11.2.0.3\dg_2\BIN>sqlplus / as sysdba

For datafile see the above step 9
SQL> select name from v$tempfile;

NAME
-------------------------------------
D:\APP\INAM\ORADATA\PRM\TEMP01.DBF

SQL> select member from v$logfile;

MEMBER
-----------------------------------
D:\APP\INAM\ORADATA\PRM\REDO03.LOG
D:\APP\INAM\ORADATA\PRM\REDO02.LOG
D:\APP\INAM\ORADATA\PRM\REDO01.LOG


11- Now change the files location in standby control file using SQL

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\SYSTEM01.DBF' to 'D:\app\Inam\oradata\STBL\SYSTEM01.DBF';
Database altered.

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\EXAMPLE01.DBF' to 'D:\app\Inam\oradata\STBL\EXAMPLE01.DBF';
Database altered.

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\SYSAUX01.DBF' to 'D:\app\Inam\oradata\STBL\SYSAUX01.DBF';
Database altered.

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\TEMP01.DBF' to 'D:\app\Inam\oradata\STBL\TEMP01.DBF';
Database altered.

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\UNDOTBS01.DBF' to 'D:\app\Inam\oradata\STBL\UNDOTBS01.DBF';
Database altered.

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\USERS01.DBF' to 'D:\app\Inam\oradata\STBL\USERS01.DBF';
Database altered.

verify the change
SQL> select name from v$datafile;

NAME
---------------------------------------
D:\APP\INAM\ORADATA\STBL\SYSTEM01.DBF
D:\APP\INAM\ORADATA\STBL\SYSAUX01.DBF
D:\APP\INAM\ORADATA\STBL\UNDOTBS01.DBF
D:\APP\INAM\ORADATA\STBL\USERS01.DBF
D:\APP\INAM\ORADATA\STBL\EXAMPLE01.DBF

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\REDO01.LOG' to 'D:\app\Inam\oradata\STBL\REDO01.LOG';
Database altered.

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\REDO02.LOG' to 'D:\app\Inam\oradata\STBL\REDO02.LOG';
Database altered.

SQL> alter database rename file 'D:\app\Inam\oradata\PRM\REDO03.LOG' to 'D:\app\Inam\oradata\STBL\REDO03.LOG';
Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------
D:\APP\INAM\ORADATA\STBL\REDO03.LOG
D:\APP\INAM\ORADATA\STBL\REDO02.LOG
D:\APP\INAM\ORADATA\STBL\REDO01.LOG

SQL> select name from v$tempfile;

NAME
------------------------------------
D:\APP\INAM\ORADATA\STBL\TEMP01.DBF

12- Creating Data Guard Configuration using DGMGRL
D:\app\Inam>set ORACLE_SID=PRM

D:\app\Inam>DGMGRL /
DGMGRL for 64-bit Windows: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>

see the existing configurations
DGMGRL> show configuration
Error:
ORA-16525: the Data Guard broker is not yet available

Configuration details cannot be determined by DGMGRL
DGMGRL>

To add new configuration on the database (PRM) parameter dg_broker_start must be true; It must also be set on the standby database (STBL) also.

SQL> show parameter dg_bro

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      D:\APP\INAM\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\DR1PRM.DAT
dg_broker_config_file2               string      D:\APP\INAM\PRODUCT\11.2.0.3\D BHOME_1\DATABASE\DR2PRM.DAT
dg_broker_start                      boolean     FALSE

SQL> alter system set dg_broker_start=true; -- done on both primary and standby
System altered.

13- There is important requirement of Data Guard to add standby logs on primary (PRM) and standby (STBL) databases.
--- adding to primary
D:\app\Inam>set ORACLE_SID=PRM
D:\app\Inam>sqlplus / as sysdba
SQL> alter database add standby logfile ('D:\app\Inam\oradata\PRM\STBLOG01.LOG') size 500M;
Database altered.
SQL> alter database add standby logfile ('D:\app\Inam\oradata\PRM\STBLOG02.LOG') size 500M;
Database altered.
SQL> alter database add standby logfile ('D:\app\Inam\oradata\PRM\STBLOG03.LOG') size 500M;
Database altered.
-- adding to standby
d:\product\11.2.0.3\dg_2\BIN>set oracle_sid=STBL
d:\product\11.2.0.3\dg_2\BIN>sqlplus / as sysdba
D:\app\Inam>set ORACLE_SID=PRM
D:\app\Inam>sqlplus / as sysdba
SQL> alter database add standby logfile ('D:\app\Inam\oradata\STBL\STBLOG01.LOG') size 500M;
Database altered.
SQL> alter database add standby logfile ('D:\app\Inam\oradata\STBL\STBLOG02.LOG') size 500M;
Database altered.
SQL> alter database add standby logfile ('D:\app\Inam\oradata\STBL\STBLOG03.LOG') size 500M;
Database altered.

14- Now try to create the Data Guard configuration again
D:\app\Inam>set ORACLE_SID=PRM
D:\app\Inam>DGMGRL /
DGMGRL> help create

Creates a broker configuration

Syntax:

  CREATE CONFIGURATION AS
    PRIMARY DATABASE IS
    CONNECT IDENTIFIER IS ;

DGMGRL> create configuration DGC as
> primary database is PRM
> connect identifier is PRM;
Configuration "dgc" created with primary database "prm"
DGMGRL>

DGMGRL> show configuration

Configuration - dgc

  Protection Mode: MaxPerformance
  Databases:
    prm - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

-- add the standby database to the existing configuration
DGMGRL> help add

Adds a standby database to the broker configuration

Syntax:

  ADD DATABASE
    [AS CONNECT IDENTIFIER IS ]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];

DGMGRL> add database STBL as
> connect identifier is STBL
> maintained as physical;
Database "stbl" added
 
DGMGRL> show configuration

Configuration - dgc

  Protection Mode: MaxPerformance
  Databases:
    prm  - Primary database
    stbl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

-- enable the configuration
DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration

Configuration - dgc

  Protection Mode: MaxPerformance
  Databases:
    prm  - Primary database
    stbl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

TESTING
Do some testing like below and see the impact on standby db
SQL> create user dgtest1 identified by dgtest1;

User created.

SQL> drop user dgtest1;

User dropped.
 THEN
SQL> create table dgtest1.t(id number);  -- it was created on standby

Table created.
SQL> insert into dgtest1.t values (1);  --- now in standby

1 row created.

SQL> commit;  -- now row in standby

Commit complete.


Checking the status
you can check the status from the following views
select * from v$archive_dest_status

select * from v$archived_log

select * from  v$archive_gap 



No comments: