Thursday, December 15, 2016

Oracle GoldenGate - Hands-On Articles


Article-1: Multiple Pluggable Database (PDBs) Replication in Multitenant Database Using Oracle GoldenGate 12c
https://community.oracle.com/docs/DOC-995763

Article-2: Data Replication with Multiple Extracts and Multiple Replicats with Integrated Capture Mode - OGG 12c
https://community.oracle.com/docs/DOC-995764

Article-3: Bi-Directional Replication with Pluggable Database (PDB) in Multitenant Database - OGG 12c
https://community.oracle.com/docs/DOC-995762

Article-4: Bi-Directional Replication with conflict detection and resolution (CDR) - Oracle GoldenGate 12c
http://otechmag.com/magazine/2015/summer/ravikumar-yv.html

Article-5: Oracle 12c (12.1.0.2.0) Standard Edition (SE2) with Multitenant Environment with HA Options
http://www.otechmag.com/magazine/2015/fall/ravikumar-yv.html

Article-6: Integrated DDL and DML with Encrypt using Oracle GoldenGate 12c.
http://allthingsoracle.com/integrated-ddl-and-dml-with-encrypt-using-oracle-goldengate-12c/

Article-7: Real-Time Downstream Integrated Capture between Oracle 11g and Oracle 12c using Oracle GoldenGate 12c
http://www.toadworld.com/platforms/oracle/w/wiki/11186.real-time-downstream-integrated-capture-between-oracle-11g-and-oracle-12c-using-oracle-goldengate-12c.aspx

Saturday, November 5, 2016

Applying July 2016 PSU Patches (GI Patch - 23273629 & RDBMS Patch - 23054246) for Oracle 12c (12.1.0.2.0) - 3 Node RAC


1. Take backup of GRID and Oracle Database Home
===============================================

1.
[root@rac1-12c u01]# cd /u01/app/12.1.0.2/grid/
[root@rac1-12c grid]# pwd
/u01/app/12.1.0.2/grid

[root@rac1-12c u01]# tar -zcvf /u01/12c_GRID_Backup .

[root@rac1-12c u01]# ls -lrth
total 5.0G
drwxr-xr-x. 5 root oinstall 4.0K Oct 19 21:20 app
-rw-r--r--  1 root root     5.0G Nov  5 16:53 12c_GRID_Backup

2.
[root@rac1-12c u01]# cd /u01/app/oracle/product/12.1.0.2/db_1/

[root@rac1-12c db_1]# tar -zcvf /u01/12c_ORACLE_DB_HOME_Backup .

[root@rac1-12c u01]# ls -lrth
total 8.1G
drwxr-xr-x. 5 root oinstall 4.0K Oct 19 21:20 app
-rw-r--r--  1 root root     5.0G Nov  5 16:53 12c_GRID_Backup
-rw-r--r--  1 root root     3.1G Nov  5 17:01 12c_ORACLE_DB_HOME_Backup

2. Upgrade OPatch version for Oracle GI Home and Oracle RDBMS Home for all 3 Nodes
===================================================================================

Using username "oracle".
oracle@192.168.2.101's password:
Last login: Fri Oct 21 07:56:09 2016 from 192.168.2.1

[oracle@rac1-12c sf_grid]$ unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/12.1.0.2/grid/
Archive:  p6880880_121010_Linux-x86-64.zip
replace /u01/app/12.1.0.2/grid/OPatch/datapatch? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: /u01/app/12.1.0.2/grid/OPatch/datapatch
  inflating: /u01/app/12.1.0.2/grid/OPatch/operr
   creating: /u01/app/12.1.0.2/grid/OPatch/modules/

---->Output Truncated----------------------->

[oracle@rac1-12c sf_grid]$ unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/oracle/product/12.1.0.2/db_1/
Archive:  p6880880_121010_Linux-x86-64.zip
replace /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch
  inflating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/operr
   creating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/modules/

---->Output Truncated----------------------->

[oracle@rac1-12c sf_grid]$ scp p6880880_121010_Linux-x86-64.zip oracle@rac2-12c:/tmp
p6880880_121010_Linux-x86-64.zip      100%   76MB  76.1MB/s   00:01

[oracle@rac1-12c sf_grid]$ scp p6880880_121010_Linux-x86-64.zip oracle@rac3-12c:/tmp
p6880880_121010_Linux-x86-64.zip      100%   76MB  76.1MB/s   00:01

[oracle@rac1-12c sf_grid]$ ssh rac2-12c
Last login: Fri Nov  4 13:51:34 2016 from rac1-12c.localdomain

[oracle@rac2-12c ~]$ cd /tmp/
[oracle@rac2-12c tmp]$ unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/12.1.0.2/grid/
Archive:  p6880880_121010_Linux-x86-64.zip
replace /u01/app/12.1.0.2/grid/OPatch/datapatch? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: /u01/app/12.1.0.2/grid/OPatch/datapatch
  inflating: /u01/app/12.1.0.2/grid/OPatch/operr
   creating: /u01/app/12.1.0.2/grid/OPatch/modules/

---->Output Truncated----------------------->

[oracle@rac2-12c tmp]$ unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/oracle/product/12.1.0.2/db_1/
Archive:  p6880880_121010_Linux-x86-64.zip
replace /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch
  inflating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/operr
   creating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/modules/

---->Output Truncated----------------------->

[oracle@rac2-12c tmp]$ ssh rac3-12c
Last login: Fri Nov  4 14:04:39 2016 from rac2-12c.localdomain

[oracle@rac3-12c ~]$ cd /tmp/
[oracle@rac3-12c tmp]$

[oracle@rac3-12c tmp]$ unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/12.1.0.2/grid/
Archive:  p6880880_121010_Linux-x86-64.zip
replace /u01/app/12.1.0.2/grid/OPatch/datapatch? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: /u01/app/12.1.0.2/grid/OPatch/datapatch
  inflating: /u01/app/12.1.0.2/grid/OPatch/operr
  creating: /u01/app/12.1.0.2/grid/OPatch/modules/

---->Output Truncated----------------------->

[oracle@rac3-12c tmp]$ unzip p6880880_121010_Linux-x86-64.zip -d /u01/app/oracle/product/12.1.0.2/db_1/
Archive:  p6880880_121010_Linux-x86-64.zip
replace /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch
  inflating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/operr
   creating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/modules/
  inflating: /u01/app/oracle/product/12.1.0.2/db_1/OPatch/modules/com.oracle.glcm.patch.opatchauto-wallet_13.9.1.1.jar

---->Output Truncated----------------------->

3. Apply July 2016 PSU Patch for both Oracle GI and Oracle RDBMS Home
======================================================================

[oracle@rac1-12c sf_grid]$ su - root
Password:

[root@rac1-12c ~]# cd /media/sf_grid/
[root@rac1-12c sf_grid]# ls -lrth
total 1.6G
drwxrwx--- 1 root vboxsf 4.0K Jul  5 10:07 23054246
drwxrwx--- 1 root vboxsf 4.0K Aug  1 02:08 23273629
-rwxrwx--- 1 root vboxsf 148K Aug  1 04:09 PatchSearch.xml
-rwxrwx--- 1 root vboxsf 209M Oct 29 19:44 p23054246_121020_Linux-x86-64.zip
-rwxrwx--- 1 root vboxsf 1.4G Oct 29 19:48 p23273629_121020_Linux-x86-64.zip
-rwxrwx--- 1 root vboxsf  77M Nov  1 15:45 p6880880_121010_Linux-x86-64.zip

[root@rac1-12c sf_grid]# sh /u01/app/12.1.0.2/grid/OPatch/opatchauto apply /media/sf_grid/23273629/

OPatchauto session is initiated at Fri Nov  4 14:20:13 2016

System initialization log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchautodb/systemconfig2016-11-04_02-20-24PM.log.

Session log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/opatchauto2016-11-04_02-20-42PM.log
The id for this session is I5AG

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0.2/db_1

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.2/grid
Patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Patch applicablity verified successfully on home /u01/app/12.1.0.2/grid

Verifying patch inventory on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patch inventory on home /u01/app/12.1.0.2/grid
Patch inventory verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Patch inventory verified successfully on home /u01/app/12.1.0.2/grid

Verifying SQL patch applicablity on home /u01/app/oracle/product/12.1.0.2/db_1
SQL patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Preparing to bring down database service on home /u01/app/oracle/product/12.1.0.2/db_1
Successfully prepared home /u01/app/oracle/product/12.1.0.2/db_1 to bring down database service

Bringing down CRS service on home /u01/app/12.1.0.2/grid
Prepatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/
crspatch_rac1-12c_2016-11-04_02-23-27PM.log

CRS service brought down successfully on home /u01/app/12.1.0.2/grid

Performing prepatch operation on home /u01/app/oracle/product/12.1.0.2/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /u01/app/oracle/product/12.1.0.2/db_1
Binary patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Performing postpatch operation on home /u01/app/oracle/product/12.1.0.2/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /u01/app/12.1.0.2/grid
Binary patch applied successfully on home /u01/app/12.1.0.2/grid

Starting CRS service on home /u01/app/12.1.0.2/grid
Postpatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/
crspatch_rac1-12c_2016-11-04_02-35-17PM.log

CRS service started successfully on home /u01/app/12.1.0.2/grid

Preparing home /u01/app/oracle/product/12.1.0.2/db_1 after database service restarted
No step execution required.........
Prepared home /u01/app/oracle/product/12.1.0.2/db_1 successfully after database service restarted

Trying to apply SQL patch on home /u01/app/oracle/product/12.1.0.2/db_1
SQL patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patches applied on home /u01/app/12.1.0.2/grid
Patch verification completed with warning on home /u01/app/12.1.0.2/grid

Verifying patches applied on home /u01/app/oracle/product/12.1.0.2/db_1
Patch verification completed with warning on home /u01/app/oracle/product/12.1.0.2/db_1

OPatchAuto successful.
--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:rac1-12c
RAC Home:/u01/app/oracle/product/12.1.0.2/db_1
Summary:

==Following patches were SKIPPED:

Patch: /media/sf_grid/23273629/21436941
Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /media/sf_grid/23273629/23054341
Reason: This patch is not applicable to this specified target type - "rac_database"


==Following patches were SUCCESSFULLY applied:

Patch: /media/sf_grid/23273629/23054246
Log: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_14-24-32PM_1.log

Patch: /media/sf_grid/23273629/23054327
Log: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_14-24-32PM_1.log


Host:rac1-12c
CRS Home:/u01/app/12.1.0.2/grid
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /media/sf_grid/23273629/21436941
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_14-27-20PM_1.log

Patch: /media/sf_grid/23273629/23054246
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_14-27-20PM_1.log

Patch: /media/sf_grid/23273629/23054327
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_14-27-20PM_1.log

Patch: /media/sf_grid/23273629/23054341
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_14-27-20PM_1.log


OPatchauto session completed at Fri Nov  4 14:43:05 2016
Time taken to complete the session 22 minutes, 52 seconds

4. Copy July 2016 PSU Patches to other cluster nodes (rac2-12c & rac3-12c)
===========================================================================

[root@rac1-12c sf_grid]# scp p23273629_121020_Linux-x86-64.zip oracle@rac2-12c:/u01/
oracle@rac2-12c's password:
p23273629_121020_Linux-x86-64.zip          100% 1353MB  58.8MB/s   00:23

[root@rac1-12c sf_grid]# scp p23054246_121020_Linux-x86-64.zip oracle@rac2-12c:/u01/
oracle@rac2-12c's password:
p23054246_121020_Linux-x86-64.zip          100%  209MB  69.6MB/s   00:03
You have mail in /var/spool/mail/root

[root@rac1-12c sf_grid]# scp p23273629_121020_Linux-x86-64.zip oracle@rac3-12c:/u01/
The authenticity of host 'rac3-12c (192.168.2.103)' can't be established.
RSA key fingerprint is dd:63:56:3a:97:6b:03:0c:b0:15:ea:2b:cd:a6:59:4b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac3-12c,192.168.2.103' (RSA) to the list of known hosts.
oracle@rac3-12c's password:
p23273629_121020_Linux-x86-64.zip           100% 1353MB  75.1MB/s   00:18

[root@rac1-12c sf_grid]# scp p23054246_121020_Linux-x86-64.zip oracle@rac3-12c:/u01/
oracle@rac3-12c's password:
p23054246_121020_Linux-x86-64.zip           100%  209MB  69.6MB/s   00:03

5.unzip the folders Oracle GI and Oracle RDBMS July 2016 PSU Patches for Node-2 (rac2-12c)
==================================================================== =====================

[root@rac1-12c sf_grid]# ssh rac2-12c
root@rac2-12c's password:
Last login: Thu Oct 20 11:18:15 2016 from rac1-12c.localdomain

[root@rac2-12c ~]# su - oracle
[oracle@rac2-12c ~]$ cd /u01/
[oracle@rac2-12c u01]$ ls -lrth
total 1.6G
drwxrwxr-x. 5 oracle oinstall 4.0K Oct 19 21:52 app
-rwxr-x---  1 oracle oinstall 1.4G Nov  4 14:52 p23273629_121020_Linux-x86-64.zip
-rwxr-x---  1 oracle oinstall 209M Nov  4 14:52 p23054246_121020_Linux-x86-64.zip

[oracle@rac2-12c u01]$ unzip p23273629_121020_Linux-x86-64.zip
Archive:  p23273629_121020_Linux-x86-64.zip
   creating: 23273629/
   creating: 23273629/23054327/
   creating: 23273629/23054327/files/
   creating: 23273629/23054327/files/inventory/

---->Output Truncated----------------------->

[oracle@rac2-12c u01]$ unzip p23054246_121020_Linux-x86-64.zip
Archive:  p23054246_121020_Linux-x86-64.zip
   creating: 23054246/
   creating: 23054246/20299023/
   creating: 23054246/20299023/etc/
   creating: 23054246/20299023/etc/config/
  inflating: 23054246/20299023/etc/config/inventory.xml
 extracting: 23054246/README.txt
replace PatchSearch.xml? [y]es, [n]o, [A]ll, [N]one, [r]ename: n
[oracle@rac2-12c u01]$

---->Output Truncated----------------------->

[root@rac2-12c ~]# cd /u01/
[root@rac2-12c u01]# ls -lrth
total 1.6G
drwxrwxr-x  9 oracle oinstall 4.0K Jul  5 10:07 23054246
drwxr-xr-x  7 oracle oinstall 4.0K Aug  1 02:08 23273629
-rw-rw-r--  1 oracle oinstall 148K Aug  1 04:09 PatchSearch.xml
drwxrwxr-x. 5 oracle oinstall 4.0K Oct 19 21:52 app
-rwxr-x---  1 oracle oinstall 1.4G Nov  4 14:52 p23273629_121020_Linux-x86-64.zip
-rwxr-x---  1 oracle oinstall 209M Nov  4 14:52 p23054246_121020_Linux-x86-64.zip

[root@rac2-12c u01]# sh /u01/app/12.1.0.2/grid/OPatch/opatchauto apply /u01/23273629/

OPatchauto session is initiated at Fri Nov  4 15:04:59 2016

System initialization log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchautodb/systemconfig2016-11-04_03-05-01PM.log.

Session log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/opatchauto2016-11-04_03-05-32PM.log
The id for this session is 368E

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0.2/db_1

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.2/grid
Patch applicablity verified successfully on home /u01/app/12.1.0.2/grid

Patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patch inventory on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patch inventory on home /u01/app/12.1.0.2/grid
Patch inventory verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Patch inventory verified successfully on home /u01/app/12.1.0.2/grid

Verifying SQL patch applicablity on home /u01/app/oracle/product/12.1.0.2/db_1
SQL patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Preparing to bring down database service on home /u01/app/oracle/product/12.1.0.2/db_1
Successfully prepared home /u01/app/oracle/product/12.1.0.2/db_1 to bring down database service

Bringing down CRS service on home /u01/app/12.1.0.2/grid
Prepatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/
crspatch_rac2-12c_2016-11-04_03-10-33PM.log

CRS service brought down successfully on home /u01/app/12.1.0.2/grid

Performing prepatch operation on home /u01/app/oracle/product/12.1.0.2/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /u01/app/oracle/product/12.1.0.2/db_1
Binary patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Performing postpatch operation on home /u01/app/oracle/product/12.1.0.2/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /u01/app/12.1.0.2/grid
Binary patch applied successfully on home /u01/app/12.1.0.2/grid

Starting CRS service on home /u01/app/12.1.0.2/grid
Postpatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/
crspatch_rac2-12c_2016-11-04_03-19-23PM.log

CRS service started successfully on home /u01/app/12.1.0.2/grid

Preparing home /u01/app/oracle/product/12.1.0.2/db_1 after database service restarted
No step execution required.........
Prepared home /u01/app/oracle/product/12.1.0.2/db_1 successfully after database service restarted

Trying to apply SQL patch on home /u01/app/oracle/product/12.1.0.2/db_1
SQL patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patches applied on home /u01/app/12.1.0.2/grid
Patch verification completed with warning on home /u01/app/12.1.0.2/grid

Verifying patches applied on home /u01/app/oracle/product/12.1.0.2/db_1
Patch verification completed with warning on home /u01/app/oracle/product/12.1.0.2/db_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:rac2-12c
RAC Home:/u01/app/oracle/product/12.1.0.2/db_1
Summary:

==Following patches were SKIPPED:

Patch: /u01/23273629/21436941
Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /u01/23273629/23054341
Reason: This patch is not applicable to this specified target type - "rac_database"

==Following patches were SUCCESSFULLY applied:

Patch: /u01/23273629/23054246
Log: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-11-28PM_1.log

Patch: /u01/23273629/23054327
Log: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-11-28PM_1.log

Host:rac2-12c
CRS Home:/u01/app/12.1.0.2/grid
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /u01/23273629/21436941
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-13-47PM_1.log

Patch: /u01/23273629/23054246
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-13-47PM_1.log

Patch: /u01/23273629/23054327
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-13-47PM_1.log

Patch: /u01/23273629/23054341
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-13-47PM_1.log

OPatchauto session completed at Fri Nov  4 15:24:22 2016
Time taken to complete the session 19 minutes, 23 seconds

[root@rac2-12c u01]# ps -ef | grep pmon
oracle   14443     1  0 15:21 ?        00:00:00 asm_pmon_+ASM2
oracle   14832     1  0 15:21 ?        00:00:00 ora_pmon_orcl2
root     21076 17699  0 15:25 pts/0    00:00:00 grep pmon

[root@rac1-12c sf_grid]# ps -ef | grep pmon
oracle   23966     1  0 14:37 ?        00:00:00 asm_pmon_+ASM1
oracle   24334     1  0 14:37 ?        00:00:00 ora_pmon_orcl1
root     25031   342  0 15:25 pts/0    00:00:00 grep pmon
oracle   25319     1  0 14:38 ?        00:00:00 mdb_pmon_-MGMTDB

6. Login cluster node (rac3-12c) and unzip Oracle GI and Oracle RDBMS July 2016 Patches for Node-3 (rac3-12c)
=============================================================================================================

[root@rac1-12c sf_grid]# ssh rac3-12c
root@rac3-12c's password:
Last login: Thu Oct 20 11:18:30 2016 from rac2-12c.localdomain

[root@rac3-12c ~]# su - oracle
[oracle@rac3-12c ~]$ cd /u01/

[oracle@rac3-12c u01]$ ls -lrth
total 1.6G
drwxrwxr-x. 5 oracle oinstall 4.0K Oct 19 21:52 app
-rwxr-x---  1 oracle oinstall 1.4G Nov  4 14:57 p23273629_121020_Linux-x86-64.zip
-rwxr-x---  1 oracle oinstall 209M Nov  4 14:59 p23054246_121020_Linux-x86-64.zip

[oracle@rac3-12c u01]$ unzip p23273629_121020_Linux-x86-64.zip
Archive:  p23273629_121020_Linux-x86-64.zip
   creating: 23273629/
   creating: 23273629/23054327/
   creating: 23273629/23054327/files/
   creating: 23273629/23054327/files/inventory/
   creating: 23273629/23054327/files/inventory/Scripts/
   creating: 23273629/23054327/files/inventory/Scripts/ext/
  inflating: 23273629/23054246/23054246/etc/config/actions.xml
  inflating: PatchSearch.xml

---->Output Truncated----------------------->


[oracle@rac3-12c u01]$ unzip p23054246_121020_Linux-x86-64.zip
Archive:  p23054246_121020_Linux-x86-64.zip
   creating: 23054246/
   creating: 23054246/20299023/
   creating: 23054246/20299023/etc/
   creating: 23054246/20299023/etc/config/
  inflating: 23054246/20299023/etc/config/inventory.xml
  inflating: 23054246/20299023/etc/config/actions.xml
   creating: 23054246/20299023/files/
   creating: 23054246/20299023/files/rdbms/

---->Output Truncated----------------------->

[root@rac3-12c u01]# ls -lrth
total 1.6G
drwxrwxr-x  9 oracle oinstall 4.0K Jul  5 10:07 23054246
drwxr-xr-x  7 oracle oinstall 4.0K Aug  1 02:08 23273629
-rw-rw-r--  1 oracle oinstall 148K Aug  1 04:09 PatchSearch.xml
drwxrwxr-x. 5 oracle oinstall 4.0K Oct 19 21:52 app
-rwxr-x---  1 oracle oinstall 1.4G Nov  4 14:57 p23273629_121020_Linux-x86-64.zip
-rwxr-x---  1 oracle oinstall 209M Nov  4 14:59 p23054246_121020_Linux-x86-64.zip

[root@rac3-12c u01]# sh /u01/app/12.1.0.2/grid/OPatch/opatchauto apply /u01/23273629/

OPatchauto session is initiated at Fri Nov  4 15:28:18 2016

System initialization log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchautodb/systemconfig2016-11-04_03-28-20PM.log.

Session log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/opatchauto2016-11-04_03-28-38PM.log
The id for this session is CJDU

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0.2/db_1

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.2/grid
Patch applicablity verified successfully on home /u01/app/12.1.0.2/grid

Patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patch inventory on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patch inventory on home /u01/app/12.1.0.2/grid
Patch inventory verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Patch inventory verified successfully on home /u01/app/12.1.0.2/grid

Verifying SQL patch applicablity on home /u01/app/oracle/product/12.1.0.2/db_1
SQL patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Preparing to bring down database service on home /u01/app/oracle/product/12.1.0.2/db_1
Successfully prepared home /u01/app/oracle/product/12.1.0.2/db_1 to bring down database service

Bringing down CRS service on home /u01/app/12.1.0.2/grid
Prepatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/
crspatch_rac3-12c_2016-11-04_03-33-05PM.log

CRS service brought down successfully on home /u01/app/12.1.0.2/grid

Performing prepatch operation on home /u01/app/oracle/product/12.1.0.2/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /u01/app/oracle/product/12.1.0.2/db_1
Binary patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Performing postpatch operation on home /u01/app/oracle/product/12.1.0.2/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /u01/app/12.1.0.2/grid
Binary patch applied successfully on home /u01/app/12.1.0.2/grid

Starting CRS service on home /u01/app/12.1.0.2/grid
Postpatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/
crspatch_rac3-12c_2016-11-04_03-42-01PM.log

CRS service started successfully on home /u01/app/12.1.0.2/grid

Preparing home /u01/app/oracle/product/12.1.0.2/db_1 after database service restarted
No step execution required.........
Prepared home /u01/app/oracle/product/12.1.0.2/db_1 successfully after database service restarted

Trying to apply SQL patch on home /u01/app/oracle/product/12.1.0.2/db_1
SQL patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1

Verifying patches applied on home /u01/app/12.1.0.2/grid
Patch verification completed with warning on home /u01/app/12.1.0.2/grid

Verifying patches applied on home /u01/app/oracle/product/12.1.0.2/db_1
Patch verification completed with warning on home /u01/app/oracle/product/12.1.0.2/db_1

OPatchAuto successful.
--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:rac3-12c
RAC Home:/u01/app/oracle/product/12.1.0.2/db_1
Summary:

==Following patches were SKIPPED:

Patch: /u01/23273629/21436941
Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /u01/23273629/23054341
Reason: This patch is not applicable to this specified target type - "rac_database"

==Following patches were SUCCESSFULLY applied:

Patch: /u01/23273629/23054246
Log: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-34-01PM_1.log

Patch: /u01/23273629/23054327
Log: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-34-01PM_1.log

Host:rac3-12c
CRS Home:/u01/app/12.1.0.2/grid
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /u01/23273629/21436941
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-36-23PM_1.log

Patch: /u01/23273629/23054246
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-36-23PM_1.log

Patch: /u01/23273629/23054327
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-36-23PM_1.log

Patch: /u01/23273629/23054341
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2016-11-04_15-36-23PM_1.log


OPatchauto session completed at Fri Nov  4 15:47:52 2016
Time taken to complete the session 19 minutes, 35 seconds
[root@rac3-12c u01]#

7. Login to ORCL database and check the database instance (orcl)
==================================================================
[oracle@rac1-12c ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
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 Fri Nov 4 16:01:16 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@orcl as sysdba
Connected.

SQL> col action_time format a30
SQL> col description  format a60
SQL> set lines 300

SQL> select action_time, patch_id, patch_uid, version, status, bundle_series, description from dba_registry_sqlpatch;

ACTION_TIME                      PATCH_ID  PATCH_UID VERSION              STATUS          BUNDLE_SERIES                  
------------------------------ ---------- ---------- -------------------- --------------- ------------------------------ 
04-NOV-16 03.47.30.689909 PM     23054246   20213895 12.1.0.2             SUCCESS         PSU                            

DESCRIPTION
------------------------------------------------------
Database Patch Set Update : 12.1.0.2.160719 (23054246)

Tuesday, November 1, 2016

Oracle GoldenGate - LOG dump utility


Login to Source Database (ORCL) as a user ‘scott’

SQL> connect scott/oracle@orcl
Connected

SQL> insert into dept values (75,'SQL SERVER','NY');
1 row created.

SQL> insert into dept values (76,'IBM DB2','NJ');
1 row created.

SQL> insert into dept values (77,'SYBASE','VA');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from dept;

DEPTNO  DNAME            LOC
----------  --------------   ----------
75   SQL SERVER       NY
76   IBM DB2          NJ
77   SYBASE           VA

3 rows selected.

Login to GGSCI from Source database:

[oracle@linux66-ggs-11g-12c ~]$ source 11g.env
[oracle@linux66-ggs-11g-12c ~]$ cd $GG
[oracle@linux66-ggs-11g-12c 11g]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

Logdump 89 >open dirdat/lt000007
Current LogTrail is /u01/app/ogg/11g/dirdat/lt000007

Logdump 90 >pos 0
Reading forward from RBA 0
Logdump 91 >detail data
Logdump 92 >ghdr on
Logdump 93 >filter include filename SCOTT.DEPT;filter string "VA";filter match all
Logdump 94 >n


Search-1: Finding one more transaction using the string “NJ” Logdump 95 >filter include filename SCOTT.DEPT;filter string "NJ";filter match all Logdump 96 >n Note: Make it Position “0” and search again Logdump 97 >pos 0 Reading forward from RBA 0 Logdump 98 >filter include filename SCOTT.DEPT;filter string "NJ";filter match all Logdump 99 >n
Search-2: Find the transaction using the HEX decimal using “DEPT ID” coulmn (Dept ID : 77 and convert into HEX using calc utility) Logdump 105 >pos 0 Reading forward from RBA 0 Logdump 106 >filter clear Logdump 107 >filter filename SCOTT.DEPT; filter HEX "4D"; filter match all Logdump 108 >n
Logdump 109>n
Logdump 110>filter clear Note: Find the transaction using all the trail files Logdump 115 >ghdr on Logdump 116 >detail on Logdump 117 >filter filename SCOTT.DEPT; filter HEX "4D"; filter string "SYBASE"; filter match all Logdump 118 >count log /u01/app/ogg/11g/dirdat/lt* Current LogTrail is /u01/app/ogg/11g/dirdat/lt000003 LogTrail /u01/app/ogg/11g/dirdat/lt000003 has 0 records LogTrail /u01/app/ogg/11g/dirdat/lt000003 closed Current LogTrail is /u01/app/ogg/11g/dirdat/lt000001 LogTrail /u01/app/ogg/11g/dirdat/lt000001 has 0 records LogTrail /u01/app/ogg/11g/dirdat/lt000001 closed Current LogTrail is /u01/app/ogg/11g/dirdat/lt000005 LogTrail /u01/app/ogg/11g/dirdat/lt000005 has 0 records LogTrail /u01/app/ogg/11g/dirdat/lt000005 closed Current LogTrail is /u01/app/ogg/11g/dirdat/lt000007 LogTrail /u01/app/ogg/11g/dirdat/lt000007 has 1 records LogTrail /u01/app/ogg/11g/dirdat/lt000007 closed Current LogTrail is /u01/app/ogg/11g/dirdat/lt000006 LogTrail /u01/app/ogg/11g/dirdat/lt000006 has 0 records LogTrail /u01/app/ogg/11g/dirdat/lt000006 closed Current LogTrail is /u01/app/ogg/11g/dirdat/lt000002 LogTrail /u01/app/ogg/11g/dirdat/lt000002 has 0 records LogTrail /u01/app/ogg/11g/dirdat/lt000002 closed Current LogTrail is /u01/app/ogg/11g/dirdat/lt000000 LogTrail /u01/app/ogg/11g/dirdat/lt000000 has 0 records LogTrail /u01/app/ogg/11g/dirdat/lt000000 closed Current LogTrail is /u01/app/ogg/11g/dirdat/lt000004 LogTrail /u01/app/ogg/11g/dirdat/lt000004 has 0 records LogTrail /u01/app/ogg/11g/dirdat/lt000004 closed LogTrail /u01/app/ogg/11g/dirdat/lt* has 1 records Total Data Bytes 38 Avg Bytes/Record 38 Insert 1 After Images 1 Filtering matched 1 records suppressed 25 records Average of 1 Transactions Bytes/Trans ..... 86 Records/Trans ... 1 Files/Trans ..... 1 SCOTT.DEPT Partition 4 Total Data Bytes 38 Avg Bytes/Record 38 Insert 1 After Images 1 Login to Target Database (ORCLDB) as a user ‘scott’ SQL> connect scott/oracle@orcl Connected SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ---------- 75 SQL SERVER NY 76 IBM DB2 NJ 77 SYBASE VA 3 rows selected. Login to GGSCI from Target database: [oracle@linux66-ggs-11g-12c ~]$ source 11g.env [oracle@linux66-ggs-11g-12c ~]$ cd $GG [oracle@linux66-ggs-11g-12c 11g]$ ./logdump Oracle GoldenGate Log File Dump Utility for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1 Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. Logdump 89 >open dirdat/lt000007 Current LogTrail is /u01/app/ogg/11g/dirdat/lt000007 Logdump 90 >pos 0 Reading forward from RBA 0 Logdump 91 >detail data Logdump 92 >ghdr on Logdump 93 >filter include filename SCOTT.DEPT;filter string "VA";filter match all Logdump 94 >n Finding one more transaction Logdump 95 >filter include filename SCOTT.DEPT;filter string "NJ";filter match all Logdump 96 >n Logdump 97 >pos 0 Reading forward from RBA 0 Logdump 98 >filter include filename SCOTT.DEPT;filter string "NJ";filter match all Logdump 99 >n

Tuesday, October 11, 2016

OTN Appreciation Day: Oracle Database 12c (12.1.0.2.0) Multi-tenant New Features with Real Application Clusters (RAC)


OTN Appreciation is really an great idea by Tim Hall especially for bloggers, being blogger i really love to add my post 
i.e on Oracle Database 12c (12.1.0.2.0) has released some of the new features related to Multi-tenant Databases.

I have already wrote full article on this new feature and here is the URL for full article
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

Introduction:
 

Oracle Database 12c (12.1.0.2.0) has released some of the new features related to Multi-tenant Databases.
 
1. Faster Pluggable Database (PDB) Startups
2. Metadata Only Clone using Pluggable Database (PDB)
3. Pluggable Database (PDB) Logging Clause
4. Cross-Pluggable Database (PDB) Queries
5. Pluggable Database (PDB) Remote Clone: Non-CDB Adopt to CDB as a PDB using Database Link (dblink)
6. Modifying Initialization Parameters for CDB level and PDB level
7. Ability to open or close a set of PDBs on a set of RAC instances
8. Ability to relocate a PDB (close it on one node and open it on another)
9. Creating, Configuring, Relocating service for a Pluggable Database (PDB) in RAC environment
10. In-Memory and Multitenant with Real Application Clusters (RAC) with pluggable databases (PDBs) using Resource Manager

Thursday, July 28, 2016

Creating database with "oakcli" command in Oracle Database Appliance (ODA)


[oracle@server01 ~]$ sudo su -

[sudo] password for oracle:
[ root@server01 : Thu Jul 28, 09:20 AM : /root ]

$ oakcli create database -db contdb -oh OraDb11204_home1

INFO: 2016-07-28 09:20:31: Please check the logfile  '/opt/oracle/oak/log/server01/tools/12.1.2.7.0
/createdb_contdb_45344.log'  for more details
INFO: 2016-07-28 09:20:32: Database parameter file is not provided. Will be using default parameters for DB creation

Please enter the 'SYSASM'  password : (During deployment we set the SYSASM password to 'welcome1'):
Please re-enter the 'SYSASM' password:
Please select one of the following for Database type  [1 .. 2] :
1    => OLTP
2    => DSS
1
The selected value is : OLTP
Please select one of the following for Database Deployment  [1 .. 3] :
1    => EE : Enterprise Edition
2    => RACONE
3    => RAC
1
The selected value is : EE
Please select one of the following for Node Number  [1 .. 2] :
1    => server01
2    => server02
2

The selected value is : server02
...
Specify the  Database Class (1. odb-01 '1 core, 8 GB memory'   2. Others) [1] : 1
The selected value is : odb-01 '1 core, 8 GB memory'

Do you want to setup the EM DB Console for this database [ Y | N ] ? : N
INFO   : Logging all actions in the file /opt/oracle/oak/log/server01/patch/12.1.2.7.0/server01-20160728093352.log and 
traces in  the file /opt/oracle/oak/log/server01/patch/12.1.2.7.0/server01-20160728093352.trc
INFO   : Loading the configuration file /opt/oracle/oak/onecmd/create_database.params...
INFO   : Creating the node list files...
INFO   : Setting up ssh for root...
INFO   : Setting up SSH across the Private Network...
...INFO   : Running as root: /usr/bin/ssh -l root 192.168.16.24 /root/DoAllcmds.sh
INFO   : Running as root: /usr/bin/ssh -l root 192.168.16.25 /root/DoAllcmds.sh
INFO   : Background process 80671 (node: 192.168.16.24) gets done with the exit code 0
INFO   : Background process 80694 (node: 192.168.16.25) gets done with the exit code 0
INFO   : Setting up SSH completed successfully
INFO   : Running the command /usr/bin/rsync -tarqvz /opt/oracle/oak/onecmd/ root@192.168.16.25:/opt/oracle/oak/onecmd 
--exclude=*zip --exclude=*gz --exclude=*log --exclude=*trc --exclude=*rpm to sync directory 
on node <192.168.16.25>

SUCCESS: Ran /usr/bin/rsync -tarqvz /opt/oracle/oak/onecmd/ root@192.168.16.25:/opt/oracle/oak/onecmd 
--exclude=*zip --exclude=*gz --exclude=*log --exclude=*trc --exclude=*rpm and it returned: RC=0

...INFO   : Did not do scp for node : server01
INFO   : Running as root: /usr/bin/ssh -l root server01 /root/DoAllcmds.sh
INFO   : Running as root: /usr/bin/ssh -l root server02 /root/DoAllcmds.sh
INFO   : Background process 81640 (node: server01) gets done with the exit code 0
INFO   : Background process 81667 (node: server02) gets done with the exit code 0
INFO   : Did not do scp for node : server01
INFO   : Running as root: /usr/bin/ssh -l root server01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20160728093433.sh
INFO   : Running as root: /usr/bin/ssh -l root server02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20160728093433.sh
INFO   : Background process 81713 (node: server01) gets done with the exit code 0
INFO   : Background process 81736 (node: server02) gets done with the exit code 0
INFO   : Setting up SSH for user oracle...
...INFO   : checking nodes in /opt/oracle/oak/onecmd/tmp/db_nodes...
...
SUCCESS: All nodes in /opt/oracle/oak/onecmd/tmp/db_nodes are pingable and alive.
INFO   : Checking SSH setup for user (oracle) on nodes in /opt/oracle/oak/onecmd/tmp/db_nodes...
INFO   : Did not do scp for node : server01
INFO   : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle server01 /opt/oracle/oak
/onecmd/tmp/DoAllcmds-20160728093452.sh
INFO   : Running on the local node: /bin/su oracle -c /opt/oracle/oak/onecmd/tmp/DoAllcmds-20160728093452.sh
INFO   : Background process 82624 (node: server01) gets done with the exit code 0
INFO   : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle server02 /opt/oracle/oak
/onecmd/tmp/DoAllcmds-20160728093452.sh
INFO   : Background process 82649 (node: server02) gets done with the exit code 0
INFO   : Setting up ACFS storage
INFO   : Did not do scp for node : server01
INFO   : Running as root: /usr/bin/ssh -l root server01 /opt/oracle/oak/onecmd/tmp/acfsm_45344.sh
INFO   : Running as root: /usr/bin/ssh -l root server02 /opt/oracle/oak/onecmd/tmp/acfsm_45344.sh
INFO   : Background process 82768 (node: server01) gets done with the exit code 0
INFO   : Background process 82791 (node: server02) gets done with the exit code 0
INFO: 2016-07-28 09:35:05: Successfully setup the storage structure for the database 'contdb'
SUCCESS: Successfully setup ACFS storage for the database contdb
INFO   : Creating Database using DBCA...
INFO   : Did not do scp for node : server01
INFO   : Running as root: /usr/bin/ssh -l root server01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20160728093505.sh
INFO   : Running as root: /usr/bin/ssh -l root server02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20160728093505.sh
INFO   : Background process 84414 (node: server01) gets done with the exit code 0
INFO   : Background process 84439 (node: server02) gets done with the exit code 0
INFO   : Running DBCA using /opt/oracle/oak/onecmd/tmp/dbca-contdb.sh on server02 as oracle...
INFO   : Check output in /opt/oracle/oak/onecmd/tmp/dbca-contdb-20160728093352.log on server02
...
INFO   : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle server02 /opt/oracle/oak
/onecmd/tmp/dbca-contdb.sh
         Instance contdb is running on node server02
INFO   : One or more Instances running on the cluster nodes.
INFO   : Running export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1;/u01/app/oracle/product/11.2.0.4/
dbhome_1/bin/srvctl setenv database -d contdb -t 'TZ=America/New_York' to set DB timezone
INFO   : Running the command /u01/app/12.1.0.2/grid/bin/crsctl stat resource ora.contdb.db -p
...
INFO   : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle server02 /opt/oracle/oak
/onecmd/tmp/dbupdates-contdb.sh
INFO   : Running export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1;/u01/app/oracle/product/11.2.0.4/
dbhome_1/bin/srvctl stop database -d contdb
INFO   : Running export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1;/u01/app/oracle/product/11.2.0.4/
dbhome_1/bin/srvctl start database -d contdb
INFO: 2016-07-28 09:42:41: Successfully set the RMAN SNAPSHOT control file
SUCCESS: 2016-07-28 09:42:45: Successfully created the Database : contdb
[ root@server01 : Thu Jul 28, 09:42 AM : /root ]
$
[ root@server01 : Thu Jul 28, 09:44 AM : /root ]
$

Saturday, June 4, 2016

Oracle Active Data Guard and Oracle GoldenGate


Oracle Active Data Guard and Oracle GoldenGate
http://www.oracle.com/technetwork/database/features/availability/dataguardgoldengate-096557.html

Uni-Directional Replication b/n Oracle 11g and Oracle 12c Using Oracle GoldenGate


Source Database:

Operating System:  Oracle Enterprise Linux 5.8 (x86-64)
Oracle Database:  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Oracle GoldenGate:  Oracle GoldenGate for Oracle - 11.2.1.0.0

Target Database:

Operating System:  Oracle Enterprise Linux 5.8 (x86-64)
Oracle Database:  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Oracle GoldenGate:  Oracle GoldenGate for Oracle - 12.1.2.0.0

Note-1: No need to set the parameter "enable_goldengate_replication"

Note-2: If target database version in Oracle 12c (12.1.0.2.0) then we have to set TRUE for the following parameter 
        otherwise Replicat Process (rorcl) will not start and you can find the error in ggserr.log 

SQL> alter system set enable_goldengate_replication=TRUE scope=both;
System altered.

SQL> sho parameter enable_

NAME                                   TYPE         VALUE
------------------------------------------  -----------  -----------
enable_goldengate_replication                 boolean      TRUE

Thursday, June 2, 2016

Creating Pluggable Database (PDB) and import data to PDB


Creating PDB and Import Data

[oracle@localhost ~]$ ps -ef | grep pmon
oracle    1756     1  0 23:16 ?        00:00:00 ora_pmon_cdb1
oracle    2538     1  0 23:19 ?        00:00:00 ora_pmon_cdb2
oracle    2970  2945  0 23:37 pts/1    00:00:00 grep pmon

[oracle@localhost ~]$ cd /u01/app/oracle/oradata/cdb2
[oracle@localhost cdb2]$ ls -lrth
[oracle@localhost cdb2]$ mkdir pdb3

[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [oracle] ? cdb2
The Oracle base has been set to /u01/app/oracle

[oracle@localhost cdb2]$ sqlplus /nolog

SQL> connect sys/oracle@192.168.56.101:1521/cdb2 as sysdba
Connected.

SQL> CREATE PLUGGABLE DATABASE pdb3
ADMIN USER pdb3_admin
IDENTIFIED BY pdb3 ROLES=(CONNECT)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb2/pdbseed', '/u01/app/oracle/oradata/cdb2/pdb3'); 

Pluggable database created.

(OR) - > If it is OMF format check the below script

SQL> connect sys/oracle@192.168.56.101:1521/cdb3 as sysdba
Connected.

SQL> CREATE PLUGGABLE DATABASE pdb3
ADMIN USER pdb3_admin
IDENTIFIED BY pdb3 ROLES=(CONNECT)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB3/datafile', '/u01/app/oracle/oradata/CDB3/pdb3/datafile/');
  2    3    4  CREATE PLUGGABLE DATABASE pdb3
*
ERROR at line 1:
ORA-01276: Cannot add file
/u01/app/oracle/oradata/CDB3/pdb3/datafile//o1_mf_system_b7chl7yq_.dbf.  File
has an Oracle Managed Files file name.

SQL> CREATE PLUGGABLE DATABASE pdb3
  2  ADMIN USER pdb3_admin
  3  IDENTIFIED BY pdb3 ROLES=(CONNECT)
  4  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB3/datafile/pdbseed_temp01.dbf', 
                        '/u01/app/oracle/oradata/CDB3/pdb3/datafile/temp01.dbf',
  5  '/u01/app/oracle/oradata/CDB3/datafile/o1_mf_system_b7chl7yq_.dbf', 
     '/u01/app/oracle/oradata/CDB3/pdb3/datafile/system01.dbf',
  6  '/u01/app/oracle/oradata/CDB3/datafile/o1_mf_sysaux_b7chl7xy_.dbf', 
     '/u01/app/oracle/oradata/CDB3/pdb3/datafile/sysaux01.dbf');

Pluggable database created.

SQL> !
[oracle@localhost datafile]$ cd /u01/app/oracle/oradata/CDB3/pdb3/datafile/
[oracle@localhost datafile]$ ls -lrth
total 841M
-rw-r-----. 1 oracle oinstall  21M Nov 26 14:12 temp01.dbf
-rw-r-----. 1 oracle oinstall 251M Nov 26 14:12 system01.dbf
-rw-r-----. 1 oracle oinstall 591M Nov 26 14:12 sysaux01.dbf

[oracle@localhost datafile]$

Check the open mode of PDB3 in CDB2:
====================================

SQL> select CON_ID, NAME, OPEN_MODE from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                        READ ONLY
         3 PDB1                            MOUNTED
         4 PDB2                            MOUNTED
         5 PDB3                            MOUNTED

SQL> alter pluggable database pdb3 open;
Pluggable database altered.

SQL> exit

Note: Add the entries in tnsnames.ora for the database ‘PDB3’

[oracle@localhost cdb2]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
[oracle@localhost admin]$ ls -lrth
[oracle@localhost admin]$ vi tnsnames.ora
[oracle@localhost admin]$ exit

SQL> !lsnrctl services

Connect to PDB3 under the pdb3_admin user
=========================================
SQL> connect pdb3_admin/pdb3@pdb3
Connected.

List the datafiles created
===========================
SQL> !ls /u01/app/oracle/oradata/cdb2/pdb3
pdbseed_temp01.dbf  sysaux01.dbf  system01.dbf

Before importing data into pdb3 & Create the directory on the filesystem.
=========================================================================
SQL> !mkdir /u01/app/oracle/oradata/cdb2/pdb3/dump
SQL> !ls /u01/app/oracle/oradata/cdb2/pdb3/dump
SQL> exit

Create a directory in noncdb
=============================
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [cdb2] ? noncdb
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle as sysdba
SQL> startup;
SQL> exit

[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect system/oracle@192.168.56.101:1521/noncdb
Connected.

SQL> create directory dpdump_pdb1 as '/u01/app/oracle/oradata/cdb2/pdb3/dump';
Directory created.

SQL> grant read, write on directory dpdump_pdb1 to PUBLIC;
Grant succeeded.

SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.

SQL> create directory dpdump_pdb1 as '/u01/app/oracle/oradata/cdb2/pdb3/dump';
Directory created.

SQL> grant read, write on directory dpdump_pdb1 to PUBLIC;
Grant succeeded.

SQL> create user SOE identified by soe;
User created.

SQL> grant dba to SOE;
Grant succeeded.

SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.

SQL> connect system/oracle@192.168.56.101:1521/noncdb
Connected.

SQL> create user SOE identified by soe;
User created.

SQL> grant dba to SOE;
Grant succeeded.

SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.

SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/noncdb/system01.dbf
/u01/app/oracle/oradata/noncdb/sysaux01.dbf
/u01/app/oracle/oradata/noncdb/undotbs01.dbf
/u01/app/oracle/oradata/noncdb/users01.dbf
/u01/app/oracle/oradata/noncdb/example01.dbf
/u01/app/oracle/oradata/noncdb/sh_001.dbf
/u01/app/oracle/oradata/noncdb/sh_index_001.dbf
/u01/app/oracle/oradata/noncdb/ilmtbs1.dbf
/u01/app/oracle/oradata/noncdb/lowcoststore1.dbf

9 rows selected.

SQL> alter tablespace example read only;
Tablespace altered.

SQL> exit

[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [noncdb] ? noncdb
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@localhost cdb2]$ expdp system/oracle DIRECTORY= dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_TABLESPACES=EXAMPLE TRANSPORT_FULL_CHECK=YES

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory= DUMPFILE=tts_mydump TRANSPORT_TABLESPACES=EXAMPLE TRANSPORT_FULL_CHECK=YES
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/oradata/cdb2/pdb3/dump/tts_mydump.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  /u01/app/oracle/oradata/noncdb/example01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Nov 12 04:21:44 2014 elapsed 0 00:01:37

Note: Before importing the tablespace, copy the datafile from noncbd to pdb3 directory

[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [noncdb] ? cdb2

[oracle@localhost cdb2]$ cp /u01/app/oracle/oradata/noncdb/example01.dbf /u01/app/oracle/oradata/cdb2/pdb3/example01.dbf

[oracle@localhost cdb2]$ sqlplus /nolog

SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.

SQL> create user hr identified by oracle;
User created.

SQL> grant dba to hr;
Grant succeeded.

SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.

SQL> grant connect,resource to hr;
Grant succeeded.

SQL> create user pm identified by oracle;
SQL> create user oe identified by oracle;
SQL> create user ix identified by oracle;
SQL> exit

[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@localhost cdb2]$ impdp system/oracle@PDB3 DIRECTORY=dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_DATAFILES='/u01/app/oracle/oradata/cdb2/pdb3/example01.dbf'

Import: Release 12.1.0.1.0 - Production on Wed Nov 12 04:31:49 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@PDB3 DIRECTORY=dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_DATAFILES=/u01/app/oracle/oradata/cdb2/pdb3/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 33 error(s) at Wed Nov 12 04:32:55 2014 elapsed 0 00:01:04

[oracle@localhost cdb2]$ sqlplus /nolog

SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.

SQL> connect hr/oracle@pdb3
Connected.
SQL> select table_name from user_tables ;

TABLE_NAME
--------------------------------------------------------------------------------
JOB_HISTORY
EMPLOYEES
DEPARTMENTS
LOCATIONS
REGIONS
JOBS
COUNTRIES

7 rows selected.

SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.

SQL> connect hr/hr@noncdb
Connected.

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
JOB_HISTORY
EMPLOYEES
JOBS
DEPARTMENTS
LOCATIONS
REGIONS
EMP_TEMP

8 rows selected.

SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.

SQL> alter tablespace example read write;
Tablespace altered.

Cheers !!!!!!