Reflection of my thoughts and technical expriences: 2015

Thursday, December 31, 2015

Oracle Database 12c (12.1.0.2.0) Multitenant New Features with Real Application Clusters (RAC)

Article: Oracle Database 12c (12.1.0.2.0) Multitenant New Features with Real Application Clusters (RAC)

Article URL: http://www.toadworld.com/platforms/oracle/w/wiki/11437.oracle-database-12c-12-1-0-2-0-multitenant-new-features-with-real-application-clusters-rac

Thanks to Toad World

Removing Node and Adding Node from the Cluster for Oracle 12c (12.1.0.2.0) Real Application Clusters (RAC)

Article: Removing Node and Adding Node from the Cluster for Oracle 12c (12.1.0.2.0) Real Application Clusters (RAC)

Article URL: http://www.toadworld.com/platforms/oracle/w/wiki/11430.removing-node-and-adding-node-from-the-cluster-for-oracle-12c-12-1-0-2-0-real-application-clusters-rac

Thanks to Toad World.

Database upgrade from Oracle 11g (11.2.0.4.0) to Oracle 12c (12.1.0.2.0) using logical standby database method

Article: Database upgrade from Oracle 11g (11.2.0.4.0) to Oracle 12c (12.1.0.2.0) using logical standby database method

Article URL: http://www.toadworld.com/platforms/oracle/w/wiki/11429.database-upgrade-from-oracle-11g-11-2-0-4-0-to-oracle-12c-12-1-0-2-0-using-logical-standby-database-method

Thanks to Toad World.

MAA/Data Guard 12c Setup Guide-Creating a RAC Physical Standby Database for a RAC Primary Database

Article: MAA/Data Guard 12c Setup Guide-Creating a RAC Physical Standby Database for a RAC Primary Database

Article URL: http://www.ukoug.org/what-we-offer/library/maadata-guard-12c-setup-guide-creating-a-rac-physical-standby-database-for-a-rac-primary-database/

Thanks UKOUG Library

Oracle Database 12c High Availability using Global Service Manager (GSM)/ Global Data Services (GDS)

Article : Oracle Database 12c High Availability using Global Service Manager (GSM)/
Global Data Services (GDS)

Article URL: http://www.otechmag.com/magazine/2015/winter/ravikumar-yv.html

Thanks to Toad World

Tuesday, September 22, 2015

Upgrade Pluggable Database (PDB) from One Container Database (12.1.0.1) to another Container Database (12.1.0.2) In Multitenant Environment

Article: Upgrade Pluggable Database (PDB) from One Container Database (12.1.0.1) to another Container Database (12.1.0.2) In Multitenant Environment

Article URL: http://www.toadworld.com/platforms/oracle/w/wiki/11374.upgrade-pluggable-database-pdb-from-one-container-database-12-1-0-1-to-another-container-database-12-1-0-2-in-multitenant-environment

Thanks Toad World - Connected Intelligence

Creating RAC Physical Standby Container Database for a RAC Primary Container Database with Pluggable Databases (PDBs) in Multitenant Environment

Article: Creating RAC Physical Standby Container Database for a RAC Primary Container Database with Pluggable Databases (PDBs) in Multitenant Environment

Article URL: http://www.toadworld.com/platforms/oracle/w/wiki/11373.creating-rac-physical-standby-container-database-for-a-rac-primary-container-database-with-pluggable-databases-pdbs-in-multitenant-environment

Thanks to Toad World - Connected Intelligence

Wednesday, September 9, 2015

Creating Real Application Clusters (RAC) using Oracle Standard Edition SE2 (12.1.0.2.0) with Container Database (CDB) with Pluggable Database (PDB) - Multitenant Environment

Introduction:

In this article, we are creating and configuring two node RAC (Real Application Clusters) setup with container database (CDB) with pluggable database in multitenant environment using Oracle Standard Edition SE2 (12.1.0.2.0)

The information about two RAC setup with container database (CDB) with pluggable database as given below.

RAC Instance

Database Type: Container Database with two instances (racdb1 & racdb2)

Pluggable Database: pluggable database (pdb1)

Oracle_Home: /u01/app/oracle/product/12.1.0.2/db_1

GRID_Home: /u01/app/12.1.0.2/grid

Oracle Version: Oracle Standard Edition SE2 (12.1.0.2.0)

Listener: 1521

Assuming that we have installed Oracle Grid infrastructure (12.1.0.2.0) and installing Oracle Standard Edition SE2 (12.1.0.2.0) on TWO node RAC Database.

Select the option: Create and configure a database

Select the option: Oracle Real Application Clusters database installation

Select the option: Admin Managed

Select the option: Select available NODES

Database Edition: Standard Edition Two (6.1 GB) with container database (racdb) includes one pluggable database (pdb1)

Select the option: Check the database edition

Login to RAC instance-1 (racdb1) and check pluggable database status

oracle@192.168.2.101's password:

[oracle@rac1-12c ~]$ ps -ef | grep pmon

oracle 4510 1 0 11:26 ? 00:00:00 asm_pmon_+ASM1

oracle 5094 1 0 11:27 ? 00:00:00 mdb_pmon_-MGMTDB

oracle 5170 1 0 11:27 ? 00:00:00 ora_pmon_racdb1

oracle 10291 10005 0 12:47 pts/1 00:00:00 grep pmon

[oracle@rac1-12c ~]$ . oraenv

ORACLE_SID = [racdb1] ? racdb

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac1-12c ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 9 12:47:37 2015

SQL> connect sys/oracle@racdb as sysdba

Connected.

SQL> set lines 100 pages 1000

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE

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

2 PDB$SEED READ ONLY

3 PDB1 MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE

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

2 PDB$SEED READ ONLY

3 PDB1 READ WRITE

Login to RAC instance-2 (racdb2) and check pluggable database status

oracle@192.168.2.102's password:

[oracle@rac2-12c ~]$ . oraenv

ORACLE_SID = [rscdb] ? racdb

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac2-12c ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 9 12:49:59 2015

SQL> connect sys/oracle@racdb as sysdba

Connected.

SQL> set lines 100 pages 1000

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE

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

2 PDB$SEED READ ONLY

3 PDB1 MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE

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

2 PDB$SEED READ ONLY

3 PDB1 READ WRITE

Check the Product Component Version

SQL> COL PRODUCT FORMAT A38

SQL> COL VERSION FORMAT A15

SQL> COL STATUS FORMAT A18

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS

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

NLSRTL 12.1.0.2.0 Production

Oracle Database 12c Standard Edition 12.1.0.2.0 64bit Production

PL/SQL 12.1.0.2.0 Production

TNS for Linux: 12.1.0.2.0 Production

Login to pluggable Database (pdb1) from RAC Instance-1 and Create user and assign privileges and insert rows

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

Connected.

SQL> create user user1 identified by oracle;

User created.

SQL> grant connect,resource to user1;

Grant succeeded.

SQL> alter user user1 quota unlimited on users;

User altered.

SQL> connect user1/oracle@192.168.2.101:1521/pdb1

Connected.

SQL> create table orgn (no number, name varchar2(20));

Table created.

SQL> insert into orgn values (1, 'ORACLE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from orgn;

NO NAME

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

1 ORACLE

Login to pluggable Database (pdb1) from RAC Instance-2 and check the rows

SQL> connect user1/oracle@192.168.2.102:1521/pdb1

Connected.

SQL> select * from orgn;

NO NAME

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

1 ORACLE

Summary: Installed, Configured and Created TWO Node RAC Database using Oracle Standard Edition SE2 (12.1.0.2.0) with Container Database (racdb) including Pluggable Database (pdb1).

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)

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.