Oracle Dataguard Broker 12c with password file in ASM

Configure 12c dataguard broker using the below command onPrimary database server

primeprd – > tnsnames for primary database

primedr-> tnsnames for standby database

/home/oracle::11g–>dgmgrl

DGMGRL> connect sys/********

DGMGRL> create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;

Configuration “dg_primeprd” created with primary database “primeprd”

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
DGMGRL> remove configuration;
Removed configuration
Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing.

alter system set log_archive_dest_2=” scope=both sid=’*’;
Disable then Enable the broker parameter on both the Primary and Standby databases.

— Primary
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;

— Standby
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;

On the Primary database create the broker configuration for the Primary and Standby database and this time it should work fine with no issues since the log archive destination 2 setting is not set, this is the workaround/solution.

/home/oracle::11g–>dgmgrl

DGMGRL> connect sys/**********

DGMGRL> create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;

DGMGRL> enable configuration;
If in Broker you get below error Error: ORA-16664: unable to receive the result from a database

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 40 seconds ago)

Check in Broker Log File.

Mon Jul 04 14:41:46 2016
Error 1017 received logging on to the standby
————————————————————
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191

Failed to send message to site . Error code is ORA-01017.

Re-create password files in ASM in 12c like below, the correct format is pwd<dbuniquename>

Primary

srvctl config database -d primeprd

orapwd file=’+DATAC1/PRIMEPRD/pwdprimeprd’ entries=10 dbuniquename=primeprd password=*** force=y ignorecase=y
Standby

srvctl config database -d primedr
orapwd file=’+DATAC1/PRIMEDR/pwdprimedr’ entries=10 dbuniquename=primedr password=*** force=y ignorecase=y

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 18 seconds ago)

— Try Switchover Now —
DGMGRL> switchover to primedr
Performing switchover NOW, please wait…
Error: ORA-16644: apply instance not available

Failed.
Unable to switchover, primary database is still “primeprd”

DGMGRL> edit database primedr set state=’APPLY-ON’ with apply instance=’primedr1′;

DGMGRL> show database verbose primedr

Database – primedr

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 6.00 KByte/s
Active Apply Rate: 755.00 KByte/s
Maximum Apply Rate: 827.00 KByte/s
Real Time Query: OFF
Instance(s):
primedr1 (apply instance)
primedr2
DGMGRL> switchover to primedr
Performing switchover NOW, please wait…
New primary database “primedr” is opening…
Oracle Clusterware is restarting database “primeprd” …
Switchover succeeded, new primary is “primedr”
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr – Primary database
primeprd – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 40 seconds ago)
DGMGRL> edit database primeprd set state=’APPLY-ON’ with apply instance=’primeprd1′;

DGMGRL> show database verbose primeprd

Database – primeprd

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 307.00 KByte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
primeprd1 (apply instance)
primeprd2

DGMGRL>
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr – Primary database
primeprd – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 55 seconds ago)

Connect to DR site broker to intiate switchover

DGMGRL> switchover to primeprd ;
Performing switchover NOW, please wait…
Operation requires a connection to instance “primeprd1” on database “primeprd”
Connecting to instance “primeprd1″…
Connected as SYSDBA.
New primary database “primeprd” is opening…
Oracle Clusterware is restarting database “primedr” …
Switchover succeeded, new primary is “primeprd

DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 11 seconds ago

Advertisements

Active – Active (Bi-Directional) Replication using Oracle Golden Gate 12c

Since it was announced in Early 2010 that Oracle GoldenGate will be the RoadMap for Replication for Oracle Database, The adoption of GoldenGate over Streams has been quite Rapid. The complexity of Streams has intimidated many adopters, specially for Multi-Master or Peer-to-Peer Replication. GoldenGate not only makes MultiMaster Replication easy but is so full of options and topologies that anybody who has used Golden Gate would most likely never go back to Oracle Streams.

In the Example below we will create a Bi-Directional Model where both the Tables have same name in different databases and can be used for INSERT’ing transactions. The CR( Conflict Resolution) is handled by GG itself.

You can download the latest version of GoldenGate from OTN >> http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

We will use the Sample Table EMP in Sample Schema SCOTT . Before we begin, the assumption is that you have installed and created Oracle Database 12c in both Site A and Site B for creating the replication, (This setup was done on Windows platform, there isn’t much difference between the Linux and Windows setup)

Site A : GGPRIM

————————

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

Site A : GGSTANDBY

————————-

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

With 12c the GoldenGate installation is GUI based. GG12c will need to be installed on both Sites and ensure that both databases are in archive log mode.

1. Enable Supplemental Logging
** Do Below Steps in Both Databases **

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;
Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> create user ggate identified by ggate;

SQL> grant dba to ggate;
SQL> conn scott/scott

2. At SITE A, Create the extract (EXT1) and data pump (DPUMP1) —

Site A Home Directory of GG : “D:\app\sql_admin\product\12.1.2\oggcore_1”

add extract ext1 tranlog begin now

add exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa extract ext1

add extract dpump1 exttrailsource D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa

— The below Directory is remote directory on other Database Server —

ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

edit params ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;

3. On SITE B Add Replicat (REP2) —

Site A Home Directory of GG : “F:\app\sql_admin\product\12.1.2\oggcore_1”

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep2 exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab checkpointtable ggate.ggschkpt
REPLICAT added
.

edit params rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

Create the extract (EXT2) and data pump (DPUMP2) on Site B

add extract ext2 tranlog begin now
EXTRACT added.

add exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac extract ext2
EXTTRAIL added.

add extract dpump2 exttrailsource F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
EXTRACT added.

— Below Directory is Remote Directory of Other Database Server —

add rmttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, extract dpump2
RMTTRAIL added.

edit params ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;

4. On SITE A, Add replicat (REP1) —

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep1 exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad checkpointtable ggate.ggschkpt
REPLICAT added.

edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

5. On both SITE A AND SITE B, add trandata, this steps will be done on both sites —

dblogin userid ggate password ggate
Successfully logged into database.

add trandata scott.emp

info trandata scott.emp

exit

6. At SITE A, Start the Extract and Data Pump process —

start mgr

start extract ext1
start extract dpump1
info extract ext1
info all

7. SITE B, Start the Extract and Data Pump process on Site B —

start extract ext2
start extract dpump2
info all

— SITE A —

start replicat rep1

status replicat rep1

— SITE B —

start replicat rep2

status replicat rep2

8. — ERROR in ggserr when starting extract —

If you get below error in starting extract on both sites “Operation not supported because enable_goldengate_replication is not set to true.” then set the parameter enable_goldengate_replication=true

— On Both Sites A and B —

sqlplus "/as sysdba"

alter system set enable_goldengate_replication=true scope=spfile;

Restart both the databases since it is a static parameter(this parameter is only in 11.2.0.4 onwards and in 12c)
Now restart the extract process on both Sites

start extract ext1

start extract ext2

— If you get Directory Level Errors, check on Both sides the RMTTRAIL is pointing to Remote Directory of other server respectively and Remove any Firewall Service blocking the ports servers on both —

-- In Site A --
ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

-- In Site B --
ADD RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, EXTRACT DPUMP2

Do this and Start the extract dpump1 and dpump2, replicat rep1 and rep2 in Both sites A and B

9. — Testing The Active Active Replication —

on Site A

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1111, 'SHADAB', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site b

On Site B

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1112, 'Yusuf', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site A

10.
————————————–
|SUMMARY OF DEFINITIONS ON BOTH SITES |
————————————–

There is a slight modification in definitions below, I created another table called tb_table_1 (code below) on both sites to demonstrate you can add as many tables as you want or even the full schema using * wildcard.

create table Tb_table_1(
user_id number primary key,
field_1 varchar2(30)
);

—- SITE A DEFINITIONS —

edit param mgr

PORT 7809

edit param ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;
TABLE scott.tb_table_1;

—- SITE B DEFINITIONS —

edit param mgr

PORT 7809

edit param ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;

Schema Replication using Oracle Goldengate 11g (Installation & Configuration)

GoldenGate is touted by Oracle as its future for data replication. It will slowly phase out Oracle Streams and will be more closely coupled with Oracle products. Golden Gate is relatively simpler to use and monitor than Oracle streams. Below I will demonstrate how to configure ASYNC Schema Replication one side using Oracle GoldenGate 11g on a Oracle 11gR2 database.

SID of Source : IVRPROD
SID of Target : IVRDR
Source Server name : ivrhodb
Target Server name : ivrdrdb
Source Schema name : IVR
Target Schema name : IVR
Tables : All tables

1. Tell database to log more (supplemental logging ~10% of more redo
writing) on source database only.

alter database add supplemental log data;

2. GoldenGate user creation on source and target side (if on Unix, on windows use same user in ora_dba group with which you installed Oracle)

useradd -d /data/home/gguser gguser

3. Copy the downloaded installation zip file ogg112101_ggs_Windows_x64_ora11g_64bit.zip) to a directory and unzip it.

4. Set the Environment variables in .bash_profile of user gguser on
both source and target database servers.

— source —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRPROD
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

— Detination —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRDR
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

5. create the tablespace for both databases (source + target ) like:

Create tablespace ggate datafile ‘/u01/oradata/ggate01.dbf’ size 1000M autoextend on next 100M;

6. GoldenGate schema creation into source and target database

create user ggate identified by ggate default tablespace ggate
temporary tablespace TEMP profile DEFAULT;
alter user ggate QUOTA UNLIMITED ON ggate;
grant CONNECT to ggate;
grant CREATE SESSION to ggate;
grant RESOURCE to ggate;
grant SELECT ANY TABLE to ggate;
grant ALTER SESSION to ggate;
grant CREATE TABLE to ggate;
grant FLASHBACK ANY TABLE to ggate;
grant SELECT ANY DICTIONARY to ggate;
grant DBA to ggate;

logis as sysdba on both source and target run following steps…

SQL> grant execute on utl_file to ggate;

SQL> @C:\ggs_Windows_x64_ora11g_64bit/marker_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/ddl_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

And more important grant on dictionay views otherwise extract will keep on abending on both source and target schema (in our case IVR and IVR on both source and target. Schema name is same on both databases)

— on source —

SQL> grant select any dictionary to ivr;

— on target —

SQL> grant select any dictionary to ivr;

7. Go to installation directory and execute ./ggsci to see if your able to run the golden gate console on both source and target

8.
// Name of schema to be replicated will be IVR residing on IVRPROD database, it will be replicated to same schema name in IVRDR database.Make sure the characterset is same on Source and Target databases. //

— Source Configuration —

GGSCI (ivrhodb) 1> create subdirs
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> edit params mgr

port 7809
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrhodb) 1> start mgr
GGSCI (ivrhodb) 1> info all

If manager is running then manager configuration is ok.

GGSCI (ivrhodb) 1> dblogin userid ggate, password ggate
GGSCI (ivrhodb) 1> list tables ivr.*

If you see the list of tables then your configuration is good and you can continue:

GGSCI (ivrhodb) 1> add trandata IVR.*
GGSCI (ivrhodb) 1> info trandata IVR.*

Lets create and configure now the extractor process, edit add these files and save:

GGSCI (ivrhodb) 1> edit params ext1

extract ext1
userid ggate, password ggate
discardfile ./dirrpt/ext1.dsc,purge
reportcount every 15 minutes, rate
exttrail ./dirdat/t1
table IVR.*;

GGSCI (ivrhodb) 1> add extract ext1, tranlog, begin now
GGSCI (ivrhodb) 1> add exttrail ./dirdat/t1, extract ext1, megabytes 100
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> start ext1

Extractor created, now we create the data pump process:

GGSCI (ivrhodb) 1> edit params dpe1

extract dpe1
passthru
rmthost ivrdrdb, mgrport 7809
rmttrail ./dirdat/t1
table IVR.*;

//rmthost is the target server name or ip address. Ensure port 7809 is open on target server

GGSCI (ivrhodb) 1> add extract dpe1, exttrailsource ./dirdat/t1
GGSCI (ivrhodb) 1> add rmttrail ./dirdat/t1, extract dpe1, megabytes 100
GGSCI (ivrhodb) 1> status all

We are not going to start pump process yet, because destination is not configured.

9.
— Destination Configuration —
Target side on your ggsci prompt:

GGSCI (ivrdrdb) 1> create subdirs
GGSCI (ivrdrdb) 1> edit params mgr

port 7809
dynamicportlist 7900-7950
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrdrdb) 1> start mgr
GGSCI (ivrdrdb) 1> status all
GGSCI (ivrdrdb) 1> view report mgr

Manager created and verified, now we are going to create replicat processes. We’ll create the replicat with the 5 minutes lag, purposely for replication to be behind 5 minutes :

GGSCI (ivrdrdb) 1> edit params rep1

replicat rep1
userid ggate, password ggate
discardfile ./dirrpt/rep1.dsc, purge
assumetargetdefs
reportcount every 15 minutes, rate
batchsql
deferapplyinterval 5 mins
map IVR.*, target IVR.*;

GGSCI (ivrdrdb) 1> add replicat rep1, exttrail ./dirdat/t1
GGSCI (ivrdrdb) 1> ADD CHECKPOINTTABLE GGATE.CHKPTAB

That should be all about configuration , now last two steps, lets start data pump process on source side and replicat process on destination.

— on source —
GGSCI (ivrhodb) 1> start dpe1

— on target —
GGSCI (ivrdrdb) 1> start rep1

Now just check statuses on both sides with “status all” command. If everything is running and nothing abandoned you can check event logs on both sides and activity:

stats ext1, totalsonly *, reportrate sec
stats rep1, totalsonly *, reportrate sec
send rep1, status

And we have configured Oracle Golden Gate installation and setup forone asynchronous data replication.