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 ;
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:
Post a Comment