Reflection of my thoughts and technical expriences: Oracle Database (12.1.0.2.0) - Standard Edition (SE2) with Pluggable Database in Multitenant Environment - High Availability using Oracle GoldenGate 12c (12.1.2.1.0)

Oracle Database (12.1.0.2.0) - Standard Edition (SE2) with Pluggable Database in Multitenant Environment - High Availability using Oracle GoldenGate 12c (12.1.2.1.0)

Introduction:

Data Replication using Oracle GoldenGate 12c (12.1.2.1.0) between Oracle Database (12.1.0.2.0) Standard Edition SE2.

For this exercise, the details given below

Source Database:

Oracle 12c R1 Database (12.1.0.2.0)

Oracle GoldenGate 12c (12.1.2.1.0)

Oracle Enterprise Linux 5.8 (x86-64)

Database Name: cdb1

Pluggable Database: pdb1

Schema Name: scott

Host Name: ggnode1

IP Address: 192.168.56.120

Target Database:

Oracle 12c R1 Database (12.1.0.2.0)

Oracle GoldenGate 12c (12.1.2.1.0.)

Oracle Enterprise Linux 5.8 (x86-64)

Database Name: cdb2

Pluggable Database: pdb2

Schema Name: pdb2

Host Name: ggnode2

IP Address: 192.168.56.125

Login as ‘oracle’ user and create directories for Oracle GoldenGate 12c in source database

Create directories in the following path

[oracle@ggnode1 ~]$ cd /u01/app/ogg

[oracle@ggnode1 ~]$ mkdir 12g

[oracle@ggnode1 product]$ ls -lrth

Create profile for Oracle 12c (12.1.0.2.0) Database

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings

export TMP=/tmp

export TMPDIR=$TMP

export GG=/u01/app/ogg/12g

export JAVA_HOME=/u01/software/jdk1.7.0_45

export ORACLE_HOSTNAME=ggnode1

export ORACLE_UNQNAME=cdb1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1

export ORACLE_SID=cdb1

export ORACLE_TERM=xterm

export BASE_PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$BASE_PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Installed and configured Oracle Database 12c R1 (12.1.0.2.0) in source database server as ‘oracle’ user

Configure LISTERNER.ORA from Net Manager (netmgr)

Stop the listener and start the listener

[oracle@ggnode1 ~]$ source 12c.env

[oracle@ggnode1 database]$ lsnrctl stop

[oracle@ggnode1 database]$ lsnrctl start

Installing Oracle GoldenGate 12c as ‘oracle’user in source database

[oracle@ggnode1 12g]$ cd /media/sf_Oracle_12c_SE2_Version/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@ggnode1 Disk1]$ ./runInstaller &

Select the option “Oracle GoldenGate for Oracle Database 12c (506.0 MB)”

Select the Software Location Path /u01/app/oracle/12g (Uncheck the option “start Manager”)

Installing Oracle (12.1.0.2.0) Database Standard Edition SE2 in source Database

Configuring Oracle GoldenGate 12c in source database

[oracle@ggnode1 ~]$ cd $GG

[oracle@ggnode1 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34

Operating system character set identified as UTF-8.

GGSCI (ggnode1.oracle.com) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg/12g

Parameter files /u01/app/ogg/12g/dirprm: already exists

Report files /u01/app/ogg/12g/dirrpt: created

Checkpoint files /u01/app/ogg/12g/dirchk: created

Process status files /u01/app/ogg/12g/dirpcs: created

SQL script files /u01/app/ogg/12g/dirsql: created

Database definitions files /u01/app/ogg/12g/dirdef: created

Extract data files /u01/app/ogg/12g/dirdat: created

Temporary files /u01/app/ogg/12g/dirtmp: created

Credential store files /u01/app/ogg/12g/dircrd: created

Masterkey wallet files /u01/app/ogg/12g/dirwlt: created

Dump files /u01/app/ogg/12g/dirdmp: created

GGSCI (ggnode1.oracle.com) 2> exit

GGSCI (ggnode1.oracle.com) 1> dblogin userid c##ogguser@pdb1, password oracle

Successfully logged into database PDB1.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/PDB1) 2> ADD SCHEMATRANDATA SCOTT ALLCOLS

ERROR: Operation not supported because enable_goldengate_replication is not set to true.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/PDB1) 3> exit

[oracle@ggnode1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 3 15:19:02 2015

SQL> connect sys/oracle@cdb1 as sysdba

Connected.

SQL> show parameter enable_

NAME TYPE VALUE

-------------------------------------------------------------------------------------

enable_goldengate_replication boolean FALSE

enable_pluggable_database boolean TRUE

SQL> alter system set enable_goldengate_replication=TRUE scope=both;

System altered.

SQL> show parameter enable_

NAME TYPE VALUE

-------------------------------------------------------------------------------------

enable_goldengate_replication boolean TRUE

enable_pluggable_database boolean TRUE

SQL> exit

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/PDB1) 2> ADD SCHEMATRANDATA SCOTT ALLCOLS

2015-09-03 15:21:35 INFO OGG-01788 SCHEMATRANDATA has been added on schema SCOTT.

2015-09-03 15:21:35 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema SCOTT.

2015-09-03 15:21:35 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema SCOTT.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/PDB1) 3> dblogin userid c##ogguser@cdb1, password oracle

Successfully logged into database CDB$ROOT.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 2> register extract escott database container (pdb1)

ERROR: Database container PDB1 is not open.

[oracle@ggnode1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 3 15:34:47 2015

SQL> connect sys/oracle@cdb1 as sysdba

Connected.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> exit

[oracle@ggnode1 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34

Operating system character set identified as UTF-8.

GGSCI (ggnode1.oracle.com) 1> dblogin userid c##ogguser@cdb1, password oracle

Successfully logged into database CDB$ROOT.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 2> register extract escott database container (pdb1)

Extract ESCOTT successfully registered with database at SCN 1726667.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 3> ADD EXTRACT escott INTEGRATED TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 4> ADD EXTTRAIL ./dirdat/lt EXTRACT escott

EXTTRAIL added.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 5> ADD EXTRACT pscott EXTTRAILSOURCE ./dirdat/lt BEGIN NOW

EXTRACT added.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 6> ADD RMTTRAIL ./dirdat/rt EXTRACT pscott

RMTTRAIL added.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 7> edit param mgr

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 8> view param mgr

PORT 7809

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 12> edit param escott

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 13> view param escott

EXTRACT escott

USERID C##OGGUSER@cdb1, PASSWORD oracle

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

EXTTRAIL ./dirdat/lt

SOURCECATALOG pdb1

TABLE scott.*;

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 14> edit param pscott

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 15> info all

Program Status Group Lag at Chkpt Time Since Chkpt

--------------------------------------------------------------------------------------------------------------

MANAGER STOPPED

EXTRACT STOPPED ESCOTT 00:00:00 00:04:00

EXTRACT STOPPED PSCOTT 00:00:00 00:03:43

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 18> start mgr

Manager started.

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 19> start er *

Sending START request to MANAGER ...

EXTRACT ESCOTT starting

Sending START request to MANAGER ...

EXTRACT PSCOTT starting

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 20> info all

Program Status Group Lag at Chkpt Time Since Chkpt

--------------------------------------------------------------------------------------------------------------

MANAGER RUNNING

EXTRACT STARTING ESCOTT 00:00:00 00:04:25

EXTRACT RUNNING PSCOTT 00:00:00 00:04:07

GGSCI (ggnode1.oracle.com as c##ogguser@cdb1/CDB$ROOT) 23> info all

Program Status Group Lag at Chkpt Time Since Chkpt

--------------------------------------------------------------------------------------------------------------

MANAGER RUNNING

EXTRACT RUNNING ESCOTT 00:00:07 00:00:04

EXTRACT RUNNING PSCOTT 00:00:00 00:04:28

Log into source database server as ‘sysdba’

SQL> connect sys/oracle@cdb1 as sysdba

Connected.

SQL> archive log list;

SQL> sho parameter db_reco

SQL> shu immediate;

SQL> connect sys/oracle as sysdba

SQL> startup mount;

SQL> alter database archivelog;

SQL> shu immediate;

SQL> connect sys/oracle as sysdba

SQL> startup;

SQL> archive log list;

SQL> select file_name from dba_data_files;

SQL> CREATE TABLESPACE GOLDENGATE DATAFILE '/u01/app/oracle/oradata/cdb1/goldengate01.dbf' SIZE 100M AUTOEXTEND ON;

Tablespace created.

SQL> CREATE USER c##ogguser IDENTIFIED BY oracle container=all;

User created.

SQL> GRANT CONNECT TO c##ogguser container=all;

Grant succeeded.

SQL> GRANT DBA TO c##ogguser container=all;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO c##ogguser container=all;

Grant succeeded.

SQL> grant create session to c##ogguser;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO c##ogguser container=all;

Grant succeeded.

SQL> grant create session to c##ogguser container=all;

Grant succeeded.

SQL> grant alter session to c##ogguser container=all;

Grant succeeded.

SQL> alter user c##ogguser default tablespace users container=all;

User altered.

SQL> alter user c##ogguser quota unlimited on users container=all;

User altered.

SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGGUSER',container=> 'all');

PL/SQL procedure successfully completed.

SQL> alter database force logging;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system set streams_pool_size=256M scope=both;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> connect sys/oracle@192.168.56.120:1521/pdb1 as sysdba

Connected.

SQL> alter user scott identified by oracle account unlock;

User altered.

SQL> connect scott/oracle@192.168.56.120:1521/pdb1

Connected.

SQL> create table inventory (

prod_id number,

prod_category varchar2(20),

qty_in_stock number,

last_dml timestamp default systimestamp);

Table created.

SQL> alter table inventory add constraint pk_inventory primary key (prod_id);

Table altered.

SQL> grant all on inventory to c##ogguser;

Grant succeeded.

SQL> exit

Installed and configured Oracle Database 12c R1 (12.1.0.2.0) in target database server as ‘oracle’ user

Configure LISTERNER.ORA from Net Manager (netmgr)

[oracle@ggnode2 database]$ lsnrctl stop

[oracle@ggnode2 database]$ lsnrctl start

Installing Oracle GoldenGate 12c as ‘oracle’ user in target database

[oracle@ggnode2 12g]$ cd /media/sf_Oracle_12c_SE2_Version/fbo_ggs_Linux_x64_shiphome/Disk1/

[oracle@ggnode2 Disk1]$ ./runInstaller &

Select the option “Oracle GoldenGate for Oracle Database 12c (506.MB)”

Select the Software Location: /u01/oracle/12g

[oracle@ggnode2 12g]$ pwd

/u01/oracle/12g

[oracle@ggnode2 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34

Operating system character set identified as UTF-8.

GGSCI (ggnode2.oracle.com) 1> create subdirs

Creating subdirectories under current directory /u01/oracle/12g

Parameter files /u01/oracle/12g/dirprm: already exists

Report files /u01/oracle/12g/dirrpt: created

Checkpoint files /u01/oracle/12g/dirchk: created

Process status files /u01/oracle/12g/dirpcs: created

SQL script files /u01/oracle/12g/dirsql: created

Database definitions files /u01/oracle/12g/dirdef: created

Extract data files /u01/oracle/12g/dirdat: created

Temporary files /u01/oracle/12g/dirtmp: created

Credential store files /u01/oracle/12g/dircrd: created

Masterkey wallet files /u01/oracle/12g/dirwlt: created

Dump files /u01/oracle/12g/dirdmp: created

GGSCI (ggnode2.oracle.com) 2> exit

Log into target database server as ‘sysdba’

[oracle@ggnode2 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 3 15:59:45 2015

SQL> connect sys/oracle@cdb2 as sysdba

Connected.

SQL> archive log list;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> connect sys/oracle as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3154116608 bytes

Fixed Size 2929352 bytes

Variable Size 771755320 bytes

Database Buffers 2365587456 bytes

Redo Buffers 13844480 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> connect sys/oracle as sysdba

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 3154116608 bytes

Fixed Size 2929352 bytes

Variable Size 771755320 bytes

Database Buffers 2365587456 bytes

Redo Buffers 13844480 bytes

Database mounted.

Database opened.

SQL> alter database force logging;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> show parameter enable_

NAME TYPE VALUE

-------------------------------------------------------------------------------------

enable_goldengate_replication boolean FALSE

enable_pluggable_database boolean TRUE

SQL> alter system set enable_goldengate_replication=TRUE scope=both;

System altered.

SQL> show parameter enable_

NAME TYPE VALUE

-------------------------------------------------------------------------------------

enable_goldengate_replication boolean TRUE

enable_pluggable_database boolean TRUE

SQL> show parameter streams_

NAME TYPE VALUE

-------------------------------------------------------------------------------------

streams_pool_size big integer 0

SQL> alter system set streams_pool_size=256M scope=both;

System altered.

SQL> show parameter streams_

NAME TYPE VALUE

-------------------------------------------------------------------------------------

streams_pool_size big integer 256M

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> CREATE TABLESPACE GOLDENGATE DATAFILE '/u01/app/oracle/oradata/cdb2/pdb2/goldengate01.dbf' SIZE 100M AUTOEXTEND ON;

Tablespace created.

SQL> CREATE USER c##ogguser IDENTIFIED BY oracle container=all;

User created.

SQL> GRANT CONNECT TO c##ogguser container=all;

Grant succeeded.

SQL> GRANT DBA TO c##ogguser container=all;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO c##ogguser container=all;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO c##ogguser container=all;

Grant succeeded.

SQL> grant create session to c##ogguser container=all;

Grant succeeded.

SQL> grant alter session to c##ogguser container=all;

Grant succeeded.

SQL> alter user c##ogguser default tablespace users container=all;

User altered.

SQL> alter user c##ogguser quota unlimited on users container=all;

User altered.

SQL> alter system switch logfile;

System altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE,FORCE_LOGGING FROM v$database;

SUPPLEME LOG_MODE FORCE_LOGGING

-------------------------------------------------------------------

YES ARCHIVELOG YES

SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGGUSER',container=> 'all');

PL/SQL procedure successfully completed.

Login to Oracle GoldenGate 12c in Target database

[oracle@ggnode2 12g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34

Operating system character set identified as UTF-8.

GGSCI (ggnode2.oracle.com) 1> dblogin userid c##ogguser@pdb2, password oracle

Successfully logged into database PDB2.

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/PDB2) 2> ADD SCHEMATRANDATA SCOTT ALLCOLS

2015-09-04 11:08:32 INFO OGG-01788 SCHEMATRANDATA has been added on schema SCOTT.

2015-09-04 11:08:32 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema SCOTT.

2015-09-04 11:08:32 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema SCOTT.

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/PDB2) 3> dblogin userid c##ogguser@cdb2, password oracle

Successfully logged into database CDB$ROOT.

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 4> ADD REPLICAT rscott INTEGRATED EXTTRAIL ./dirdat/rt

REPLICAT (Integrated) added.

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 5> edit param rscott

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 6> view param rscott

REPLICAT rscott

DBOPTIONS INTEGRATEDPARAMS

USERID C##OGGUSER@pdb2, PASSWORD oracle

ASSUMETARGETDEFS

MAP pdb1.scott.*, TARGET pdb2.scott.*;

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 7> edit param mgr

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 8> view param mgr

PORT 7810

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 9> start mgr

Manager started.

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 10> start replicat rscott

Sending START request to MANAGER ...

REPLICAT RSCOTT starting

GGSCI (ggnode2.oracle.com as c##ogguser@cdb2/CDB$ROOT) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

-------------------------------------------------------------------------------------------------------------

MANAGER RUNNING

REPLICAT RUNNING RSCOTT 00:00:00 00:02:30

Testing the transactions between source database and target database

Login to Source Database – Pluggable Database (PDB1)

[oracle@ggnode1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 4 10:58:35 2015

SQL> connect sys/oracle@cdb1 as sysdba

Connected.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> connect scott/oracle@192.168.56.120:1521/pdb1

Connected.

SQL> insert into inventory values (1,'ORACLE',10,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> set lines 200 pages 1000

SQL> select * from inventory;

PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML

------------------------------------------------------------------------------------------------------------

1 ORACLE 10 04-SEP-15 11.16.08.000000 AM

Login to Target Database – Pluggable Database (PDB1)

SQL> connect sys/oracle@cdb2 as sysdba

Connected.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> connect scott/oracle@192.168.56.125:1521/pdb2

Connected.

SQL> select * from inventory;

PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML

---------------------------------------------------------------------------------------------------------------------

1 ORACLE 10 04-SEP-15 11.16.08.000000 AM

Summary:

Data Replication using Oracle GoldenGate 12c (12.1.2.1.0) between Oracle Database (12.1.0.2.0) Standard Edition SE2.

Reflection of my thoughts and technical expriences

Friday, September 4, 2015

Oracle Database (12.1.0.2.0) - Standard Edition (SE2) with Pluggable Database in Multitenant Environment - High Availability using Oracle GoldenGate 12c (12.1.2.1.0)

1 comment: