Database Administrator Blogs

Networking Concepts

Mac (media access control) address: A unique identifier for a network adapter .

48 bit long (04-BF-1B-22-03-A5)

24 bit OUI - Identifies manufacture 04-BF-1B(dell) identifies manufacture intel,dell,oracle,hp etc.

24 bit - Identifies network device

Virtual Mac : In virtual world. Hypervisor manages virtual mac.

IPV4 Address: ex . 172.16.1.100

-- A unique number assigned to a network adapter.

-- 32 bit long , composed of network prefix and host number.

-- Dynamic IP (DHCP) Vs static IP- IP can be allocated by DHCP or static.

Ethernet frame header -- Ip header-- TCP Header -- Application Data-- Ethernet frame footer

<---------14------------> <--20--> <--20--> <data> <4>
<----------------------------------------Ethernet Frame------------------------------------------------->

<------------------------------ 46 to 1500 bytes -------------------------------------------------------->

Ethernet frame header--- source and destination Mac
IP Header -- Source IP and Destination IP
TCP Header -- Source PORT and Destination PORT

The Maximum Transmission Unit (MTU) is the largest amount of data that can be transmitted in a single packet on a network. It is a parameter that is determined by the underlying network technology, and can be configured on network devices such as routers and switches.

https://www.geeksforgeeks.org/what-is-mtumaximum-transmission-unit/

ARP (address resolution Protocol)

00:12:22:21:34:F4

192.168.0.111

00:12:22:21:34:F6

192.168.0.112

00:12:22:21:34:33

192.168.0.113

Subnet ---192.168.0.1/24
Arp -a
1.       M1 send ARP request who is having IP 192.168.0.112?
2.       ARP broadcast to the network
3.       M2 and M3 received ARP request
4.       Only M2 respond ARP I am at 00:12:22:21:34:F6
5.       ARP hits the wire
6.       M1 receives the reply

Oracle Interview Question- (Chapter - Data Guard)

Question 1: How to create standby database primary database backup .

Answer : Starting of 11gR2, without having Primary backup RMAN duplicate can create standby database over network via oracle database services.

Ex. If we check the log , you will below logs

Starting restore at 21-JAN-24

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service CDB_PR

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/CDB/data01/CDB/datafile/o1_mf_system_ltrj6j0p_.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45

Oracle RAC OCR and Voting Disk Replacement

1. Currently i am having one voting disk, Since the diskgroup data configured with External Redundancy .I could not add new voting disk.

[oragrid@node2 ~]$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 463612408dbd4f0cbf65955f3ac3ef9c (/dev/oracleasm/disks/ASMDISK5) [DATA]

Located 1 voting disk(s).

[oragrid@node2 ~]$ exit

2. So I need to create separate diskgroup with Normal redudancy ( It will have 2 mirror copy mean 2 failed group for each disk)

so we can have 3 Votedisk files.

3. Added a disk /dev/sdd in to my VM and created logical partion .

[root@node2 ~]# fdisk -l /dev/sdd

Disk /dev/sdd: 1073 MB, 1073741824 bytes, 2097152 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x539955a3

Device Boot Start End Blocks Id System

/dev/sdd1 2048 2097151 1047552 5 Extended

/dev/sdd5 4096 686079 340992 83 Linux

/dev/sdd6 688128 1370111 340992 83 Linux

/dev/sdd7 1372160 2054143 340992 83 Linux

4. Create asm disks.

[root@node2 ~]# oracleasm createdisk OCRVD1 /dev/sdd5

Writing disk header: done

Instantiating disk: done

[root@node2 ~]# oracleasm createdisk OCRVD2 /dev/sdd6

Writing disk header: done

Instantiating disk: done

[root@node2 ~]# oracleasm createdisk OCRVD3 /dev/sdd7

Writing disk header: done

Instantiating disk: done

[root@node2 ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Scanning system for ASM disks...

[root@node2 ~]# oracleasm listdisks

ASMDISK10

ASMDISK11

ASMDISK12

ASMDISK13

ASMDISK14

ASMDISK5

ASMDISK6

ASMDISK7

ASMDISK8

ASMDISK9

OCRVD1

OCRVD2

OCRVD3

[root@node2 ~]#

5. CREATE DISKGROUP OCRVD NORMAL REDUNDANCY DISK '/dev/oracleasm/disks/OCRVD1' SIZE 333M

DISK '/dev/oracleasm/disks/OCRVD2' SIZE 333M

DISK '/dev/oracleasm/disks/OCRVD3' SIZE 333M

SQL> select NAME,STATE,TYPE,TOTAL_MB from v$asm_diskgroup;

NAME STATE TYPE TOTAL_MB

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

DATA MOUNTED EXTERN 18432

OCRVD MOUNTED NORMAL 996

SQL>

6. Replace new votedisk.

[oragrid@node2 trace]$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 463612408dbd4f0cbf65955f3ac3ef9c (/dev/oracleasm/disks/ASMDISK5) [DATA]

Located 1 voting disk(s).

[oragrid@node2 trace]$ crsctl replace votedisk +OCRVD

Successful addition of voting disk 4219d4f7a49b4fb3bf9601cc89f87794.

Successful addition of voting disk 95cb9d0d75384fc3bf0a5aae2752b422.

Successful addition of voting disk 715d2fbc57e44f62bf959e61915db38a.

Successful deletion of voting disk 463612408dbd4f0cbf65955f3ac3ef9c.

Successfully replaced voting disk group with +OCRVD.

CRS-4266: Voting file(s) successfully replaced

[oragrid@node2 trace]$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 4219d4f7a49b4fb3bf9601cc89f87794 (/dev/oracleasm/disks/OCRVD1) [OCRVD]

2. ONLINE 95cb9d0d75384fc3bf0a5aae2752b422 (/dev/oracleasm/disks/OCRVD2) [OCRVD]

3. ONLINE 715d2fbc57e44f62bf959e61915db38a (/dev/oracleasm/disks/OCRVD3) [OCRVD]

Located 3 voting disk(s).

[oragrid@node2 trace]$

7. OCR addition:

[oragrid@node2 trace]$ ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 491684

Used space (kbytes) : 84440

Available space (kbytes) : 407244

ID : 939418504

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

[oragrid@node2 trace]$ ocrconfig -add +OCRVD

PROT-20: Insufficient permission to proceed. Require privileged user

[oragrid@node2 trace]$ which ocrconfig

/opt/app/grid19c/bin/ocrconfig

[oragrid@node2 trace]$ exit

logout

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -add +OCRVD

[root@node2 ~]# /opt/app/grid19c/bin/ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 491684

Used space (kbytes) : 84440

Available space (kbytes) : 407244

ID : 939418504

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File Name : +OCRVD

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@node2 ~]#

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -replace +DATA -replacement +OCRVD

PROT-34: The Oracle Cluster Registry location to be deleted is not configured.

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -add +DATA

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -delete +OCRVD

[root@node2 ~]# /opt/app/grid19c/bin/ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 491684

Used space (kbytes) : 84440

Available space (kbytes) : 407244

ID : 939418504

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -replace +DATA -replacement +OCRVD

PROT-28: Cannot delete or replace the only configured Oracle Cluster Registry location.

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -add +DATA

PROT-29: The Oracle Cluster Registry location is already configured

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -add +OCRVD

[root@node2 ~]# /opt/app/grid19c/bin/ocrconfig -delete +DATA

[root@node2 ~]# /opt/app/grid19c/bin/ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 491684

Used space (kbytes) : 84440

Available space (kbytes) : 407244

ID : 939418504

Device/File Name : +OCRVD

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@node2 ~]#

Linux : Create filesystem using LVM

Add disk to your host , here for an example i have added new disk (/dev/sdd)
pvcreate initializes PhysicalVolume for later use by the Logical Volume Manager (LVM). Each PhysicalVolume can be a disk partition, whole disk, meta device, or loopback file pvcreate /dev/sdd
See the physical volumes by running

[root@node1 ~]# pvs

PV VG Fmt Attr PSize PFree

/dev/sda2 rhel lvm2 a-- <19.51g 0

/dev/sdb op lvm2 a-- <30.00g 96.00m

/dev/sdd lvm2 --- 30.00g 30.00

4. Create Volume group

[root@node1 ~]# vgcreate oradumps /dev/sdd

Volume group "oradumps" successfully created

[root@node1 ~]# vgs

VG #PV #LV #SN Attr VSize VFree

op 1 1 0 wz--n- <30.00g 96.00m

oradumps 1 0 0 wz--n- <30.00g <30.00g

rhel 1 3 0 wz--n- <19.51g 0

5. Create Logical volume group.

[root@node1 ~]# lvcreate -L 29.9G -n oradumps1 oradumps

Rounding up size to full physical extent 29.90 GiB

Logical volume "oradumps1" created.

[root@node1 ~]# lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

op-app op -wi-ao---- 29.90g

oradumps1 oradumps -wi-a----- 29.90g

root rhel -wi-ao---- <14.51g

swap rhel -wi-ao---- 3.00g

tmp rhel -wi-ao---- 2.00g

6. Format the filesystem with xfs formating.

[root@node1 ~]# mkfs.xfs /dev/oradumps/oradumps1

meta-data=/dev/oradumps/oradumps1 isize=512 agcount=4, agsize=1959680 blks

= sectsz=512 attr=2, projid32bit=1

= crc=1 finobt=0, sparse=0

data = bsize=4096 blocks=7838720, imaxpct=25

= sunit=0 swidth=0 blks

naming =version 2 bsize=4096 ascii-ci=0 ftype=1

log =internal log bsize=4096 blocks=3827, version=2

= sectsz=512 sunit=0 blks, lazy-count=1

realtime =none extsz=4096 blocks=0, rtextents=0

7. Create a directory and mount the filesystem and add to /etc/fstab for permanent mount
[root@node1 ~]# mkdir /oradumps
[root@node1 ~]# mount /dev/oradumps/oradumps1 /oradumps
[root@node1 ~]# df -h /oradumps
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/oradumps-oradumps1 30G 33M 30G 1% /oradumps
[root@node1 ~]# grep oradumps /etc/fstab
/dev/oradumps/oradumps1 /oradumps xfs defaults 0 0
[root@node1 ~]#

Note : Command to validate the list of device with partitions.

[root@node1 ~]# lsblk

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT

sda 8:0 0 20G 0 disk

├─sda1 8:1 0 500M 0 part /boot

└─sda2 8:2 0 19.5G 0 part

├─rhel-root 253:0 0 14.5G 0 lvm /

├─rhel-swap 253:2 0 3G 0 lvm /swap

└─rhel-tmp 253:3 0 2G 0 lvm /tmp

sdb 8:16 0 30G 0 disk

└─op-op--app 253:1 0 29.9G 0 lvm /opt/app

sdc 8:32 0 30G 0 disk

├─sdc1 8:33 0 1K 0 part

├─sdc5 8:37 0 3G 0 part

├─sdc6 8:38 0 3G 0 part

├─sdc7 8:39 0 3G 0 part

├─sdc8 8:40 0 3G 0 part

├─sdc9 8:41 0 3G 0 part

├─sdc10 8:42 0 3G 0 part

├─sdc11 8:43 0 3G 0 part

├─sdc12 8:44 0 3G 0 part

├─sdc13 8:45 0 3G 0 part

└─sdc14 8:46 0 3G 0 part

sdd 8:48 0 30G 0 disk

└─oradumps-oradumps1 253:4 0 29.9G 0 lvm

sr0 11:0 1 1024M 0 rom

Oracle Interview Questions (RAC)

Oracle Interview Questionware(Chapter-2)

Question 1: What is a the local_listener parameter and and how does it differ from an ordinary listener? When do I need to specify a local listener in my spfile?

The local_listener init.ora parameter specifies a network name that resolves to an address or address list of Oracle Net local listeners and it is used used ONLY when you have multiple listeners that are running on the same machine as this instance.

The LOCAL_LISTENER parameter is a component of the cross instance registration technology for Oracle RAC (Real Application Clusters). Cross instance registration needs the local listener to help manage connections across all database instances for load balancing and failover. The local listener is particularly meaningful for the RAC configuration featured by multiple listeners on cluster nodes.

If your Oracle configuration is not RAC enabled, you may as well remove the LOCAL_LISTENER parameter from your spfile in case for default port 1521 .

SQL> show parameter local_listener

NAME TYPE VALUE

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

local_listener string

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.2.210)(PORT=1521))';

System altered.

SQL> alter system register;

System altered.

To dynamically update the LOCAL_LISTENER parameter, use the SQL statement ALTER SYSTEM SET:

ALTER SYSTEM SET LOCAL_LISTENER='LISTENER_PRIM' scope=both sid='*' ;

If you set the parameter to null with the statement that follows, then the default local address of TCP/IP, port 1521 is assumed.

ALTER SYSTEM SET LOCAL_LISTENER=''

example : in local host tnsnames.ora file

LISTENER_PRIM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521)))

suppose we have 2-node cluster: host1 and host2, with VIP address host1-vip and host2-vip respectively.One RAC database (orcl) running on this cluster; instace 1 (orcl1) on host1, and instance 2 (orcl2) on host2 we have listener_host1 running on host1, and listener_host2 running on host2.listener_host1 is considered local listener for orcl1 instance, while listener_host2 is considered remote listener for that same orcl1 instance.similarly, listener_host2 is considered local listener for orcl2 instance, and considered as remote listener for orcl1.

Question 2: Types of Patching

1. Single patch or intrim patch --------- Opatch apply

2. Patchset(100 of patches) BUNDLE PATCH --------- Opatch napply -<Locatio> -id -skip_subset -skip_duplicate

3. Patchset update PSU=CPU(critical patch Update)+ Patchset)

Log file for Opatch utility can be found at : $ORACLE_HOME/cfgtoollogs/opatch

Question 3: How to create Local Inventory?

You can create local inventory at any location while installing Oracle Home.

You need to use -invPtrLoc variable along with runInstaller command and give the location of oraInst.loc. Otherwise the default path assumed for oraInst.loc is /etc/oraInst.loc.

./runInstaller -invPtrLoc /slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInst.loc

If you are using central inventory then -invPtrLoc variable is not required.

You can also get the detailed view of inventory using following command. In case of detail information it gives which are the files that this patch have touched.

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

Question 4: How to Apply Opatch in Oracle?

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.

2.Make sure you have a good backup of database.

3. Make a note of all Invalid objects in the database prior to the patch.

4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

5. You MUST Backup your oracle Home and Inventory

tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

7. cd to the patch direcory and do opatch -apply to apply the patch.

8. Read the output/log file to make sure there were no errors.

Error is related to timezone file

I started database in upgrade mode and fired catupgrd.sql :

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 6413680640 bytes

Fixed Size 2160112 bytes

Variable Size 1946159632 bytes

Database Buffers 4429185024 bytes

Redo Buffers 36175872 bytes

Database mounted.

Database opened.

SQL> @catupgrd.sql

DOC>#######################################################################

DOC>

DOC> The first time this script is run, there should be no error messages

DOC> generated; all normal upgrade error messages are suppressed.

DOC>

DOC> If this script is being re-run after correcting some problem, then

DOC> expect the following error which is not automatically suppressed:

DOC>

DOC> ORA-00001: unique constraint () violated

DOC>#

FROM registry$database

ERROR at line 2:

ORA-00942: table or view does not exist

This error is related to timezone file which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied.

Query to check timezone file is:

SQL> select * from v$timezone_file;

FILENAME VERSION

———— ———-

timezlrg.dat 4

SQL> select * from v$timezone_file;

FILENAME VERSION

———— ———-

timezlrg.dat 4

So I had correct version.I remember applying patch before upgrade.I got lucky because patch existed for version 10.2.0.3.

If there is no patch for your Oracle versions than patch can be download for similar version and applied manually.

Instructions are below:

1. Download the identified patch.

2. Unzip the patch, and locate the 2 files timezone.dat and timezlrg.dat in the “files/oracore/zoneinfo” directory of the

uncompressed patch (or from the relevant .jar file of a patchset). If there is also a readme.txt in this location then make a note of this as well.

3. Backup your existing files in $ORACLE_HOME/oracore/zoneinfo – THIS CAN BE VITAL, DO NOT SKIP.

note:

Before going on with step 4, make sure the current files are not in use.

On Windows the files will simply refuse to be removed when the are in use.

On Unix replacing the files whilst they are in use can cause the files to become corrupt.

Use the fuser command before replacing the files to make sure they are not in use.

4. Copy the 2 .dat files and possibly the readme.txt file that were found in step 2 into the $ORACLE_HOME/oracore/zoneinfo directory.

5. Restart the database (in case of installation on a database), or restart the client applications (in case of client install).

Note that the database did not need to be down before the time zone files were applied, but it does need to be restarted afterwards.

Question 5: .What is the split brain?

When nodes of the database in a cluster can’t communicate with each other, they modify the data blocks and may continue to process independently. If more than one instance modify the same block, locking or synchronization of the blocks of the data does not occur and it may happen that the blocks get overwritten by others in the cluster. This process is called split brain.

Oracle 12c New Feacture

Prior to Oracle Database 12c the maximum length for a VARCHAR2 and NVARCHAR2 was 4,000, and the maximum length of a RAW column was 2,000. Starting with Oracle Database 12c, these data types have been extended to accommodate a length of 32,767.

Prior to Oracle Database 12c, you could not have multiple indexes defined on the exact same combination of columns in one table. This has changed in 12c. You can now have multiple indexes on the same set of columns. However, you can only do this if there is something physically different about the indexes; for example, one index is created as a B-tree index, and the second, as a bitmap index. Also, there can be only one visible index for the same combination of columns. Any other indexes created on that same set of columns must be declared invisible;
The NVARCHAR2 and NCHAR data types are useful if you have a database that was originally created with a single-byte,
If you use a VARCHAR2 for a field that contains date information, you are guaranteeing future reporting inconsistencies and needless conversion function(such as TO_DATE and TO_CHAR).
The RAW data type allows you to store binary data in a column. This type of data is sometimes used for storing globallyunique identifiers or small amounts of encrypted data. If you need to store large amounts (over 2000 bytes) of binary data then use a BLOB instead.
You can also instruct Oracle to not re-adjust the high-water mark when shrinking a table. This is done via theCOMPACT clause—for example:
SQL> alter table emp shrink space compact;

SUSE Linux Bug 208 day server crash

Environment :SUSE Linux Enterprise 11 Service Pack 1

Scenario:
The system crashes with an up time of around 209 daysThe issue happens under the following conditions:CPU vendor is Intel/proc/cpuinfo contains both of the following CPU flags:constant_tscnonstop_tscdmesg and/or /var/log/boot.msg does not contain the stringMarking TSC unstablekernel flavor is not xenOnly if all four conditions are met the system is affected.
The freeze/crash can happen anytime after 208 days uptime. It cannot happen before the system reaches 208 days uptime.

Resolution :

For critical production systems that cannot update their kernel version, we currently recommend a cold reboot before the system reaches 208 days uptime.A fix for this issue has been released since kernel version 2.6.32.59-0.7.1. We always recommend to install the latest version of the kernel.

Golden Gate Specific Errors

Golden Gate cross platform replication from MySQL to Oracle

Golden Gate Cross Platform Replication ( MySQL to Oracle)

Here is details of installed product and binary location. Here I have configured both databases on same server .

Databases	Oracle	MySQL
Version	19.3.0.0.0	5.7.28
Golden Gate	19.1.0.0.3	19.1.0.0.4
OS	Oracle Linux Server release 7.3	Oracle Linux Server release 7.3
Base Location	/u01/oracle/database19c	/u01/mysql
Golden gate base	/gg_gate/oracle	/gg_gate_mysql
Installation Link	-	https://deepakoracledba.blogspot.com/search/label/MySql

Step 1: Post configuration of both database server using the linked provided , we can proceed to to download respective certified versions of golden gate binaries from oracle e-delivery site.

Here i have downloaded and transferred to my local Vm based server

[oracle@xcell oradba]$ ls -lrt *ggs*
-rw-r--r--. 1 root root 71654538 Apr 21 19:20 191003_ggs_Linux_x64_MySQL_64bit.zip
-rw-r--r--. 1 root root 556240981 Apr 21 19:21 191004_fbo_ggs_Linux_x64_shiphome.zip

Step 2: Installation of golden gate quite simple , we just need to unzip the binaries to respective golden gate base locations.

In 19c Oracle binary , we have GUI based installation option is available , that can be used .

MYSQL_GOLDENGATE INSTALLATION

first unzip the zip files

[oracle@xcell oradba]$ unzip -l 191003_ggs_Linux_x64_MySQL_64bit.zip

Archive: 191003_ggs_Linux_x64_MySQL_64bit.zip

Length Date Time Name

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

289024000 09-07-2019 21:22 ggs_Linux_x64_MySQL_64bit.tar

1413 05-30-2019 05:19 OGG-19.1.0.0-README.txt

326332 09-17-2019 23:38 OGG_WinUnix_Rel_Notes_19.1.0.0.3.pdf

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

289351745 3 files

2. tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C /gg_gate/mssql/

ORACLE _GOLDENGATE INSTALLATION

Unzip 191004_fbo_ggs_Linux_x64_shiphome.zip -d /gg_gate/oracle

Step 3: Configuring golden gate for MySQL and Oracle

MYSQL_GOLDENGATE Configuration

Ø Golden gate config

Go to /gg_gate/mysql Location

[root@xcell mysql]# ./ggsci

Oracle GoldenGate Command Interpreter for MySQL

Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144

Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32

Operating system character set identified as UTF-8.

GGSCI (xcell.home.com) 1> show

Parameter settings:

SET DEBUG OFF

Current directory: /gg_gate/mssql

Editor: vi

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirrpt does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirprm does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirchk does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirpcs does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirsql does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirdef does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirdmp does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirwlt does not exist yet.

2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dircrd does not exist yet.

GGSCI (xcell.home.com) 2> create subdirs

Creating subdirectories under current directory /gg_gate/mssql

Parameter file /gg_gate/mssql/dirprm: created.

Report file /gg_gate/mssql/dirrpt: created.

Checkpoint file /gg_gate/mssql/dirchk: created.

Process status files /gg_gate/mssql/dirpcs: created.

SQL script files /gg_gate/mssql/dirsql: created.

Database definitions files /gg_gate/mssql/dirdef: created.

Extract data files /gg_gate/mssql/dirdat: created.

Temporary files /gg_gate/mssql/dirtmp: created.

Credential store files /gg_gate/mssql/dircrd: created.

Masterkey wallet files /gg_gate/mssql/dirwlt: created.

Dump files /gg_gate/mssql/dirdmp: created.

GGSCI (xcell.home.com) 3> show

Parameter settings:

SET DEBUG OFF

Current directory: /gg_gate/mssql

Editor: vi

Reports (.rpt) /gg_gate/mssql/dirrpt

Parameters (.prm) /gg_gate/mssql/dirprm

Replicat Checkpoints (.cpr) /gg_gate/mssql/dirchk

Extract Checkpoints (.cpe) /gg_gate/mssql/dirchk

Process Status (.pcs) /gg_gate/mssql/dirpcs

SQL Scripts (.sql) /gg_gate/mssql/dirsql

Database Definitions (.def) /gg_gate/mssql/dirdef

Dump files (.dmp) /gg_gate/mssql/dirdmp

Masterkey wallet files (.wlt) /gg_gate/mssql/dirwlt

Credential store files (.crd) /gg_gate/mssql/dircrd

GGSCI (xcell.home.com) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

GGSCI (xcell.home.com) 6> edit param mgr

GGSCI (xcell.home.com) 1> view param mgr

PORT 7811

GGSCI (xcell.home.com) 2>

GGSCI (xcell.home.com) 7> start mgr

Manager started.

GGSCI (xcell.home.com) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

Ø MYSQL database base config

- Create a new datbase

- Create necessary golden gate users.

- Create few tables

[root@xcell ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.28-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> select @@VERSION;

+------------+

| @@VERSION |

+------------+

| 5.7.28-log |

+------------+

1 row in set (0.00 sec)

mysql> select user();

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

mysql> create database mydb; -- create a new database

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mydb |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.59 sec)

mysql> use mydb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE USER 'ogguser1'@'localhost' IDENTIFIED BY 'WElcome@@1';

Query OK, 0 rows affected (0.61 sec)

mysql> CREATE USER 'oggadm1'@'localhost' IDENTIFIED BY 'WElcome@@1';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'ogguser1'@'localhost';

Query OK, 0 rows affected (0.05 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'oggadm1'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> use mydb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE TABLE `DEPT` (

`DEPTNO` tinyint(4) DEFAULT NULL,

`DNAME` varchar(14) DEFAULT NULL,

`LOC` varchar(13) DEFAULT NULL,

UNIQUE KEY `PK_DEPT` (`DEPTNO`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

mysql> show tables;

+-----------------------+

| Tables_in_mydb (DEPT) |

+-----------------------+

| DEPT |

+-----------------------+

1 row in set (0.00 sec)

Ø Golden gate extract and Pump process configuration

Edit param file and add below lines.

GGSCI (xcell.home.com) 2> view param EXTMYSQL

extract extmysql

SETENV(MYSQL_HOME='/u01/mysql/mysql-5.7.28-el7-x86_64/bin')

SETENV(MYSQL_UNIX_PORT='/u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock')

dboptions host localhost, connectionport 3307

sourcedb mydb, userid oggadm1, password WElcome@@1

exttrail /gg_gate/mysql/dirdat/lt

TRANLOGOPTIONS ALTLOGDEST /u01/mysql/mysql-5.7.28-el7-x86_64/data/mysql.index

table mydb.*;

GGSCI (xcell.home.com) 3> view param DPMYSQL

EXTRACT DPMYSQL

RMTHOST 192.168.10.151,MGRPORT 7809

RMTTRAIL /gg_gate/oracle/dirdat/mysql/rt

PASSTHRU

table mydb.*;

GGSCI (xcell.home.com) 4>

GGSCI (xcell.home.com) 1> start mgr

Manager started.

GGSCI (xcell.home.com) 4> add extract extmysql,tranlog,begin now

EXTRACT added.

GGSCI (xcell.home.com) 4> add exttrail /gg/gate/mysql/dirdat/lt,extract extmysql

EXTTRAIL added.

GGSCI (xcell.home.com) 3> start extmysql

Sending START request to MANAGER ...

EXTRACT EXTMYSQL starting

GGSCI (xcell.home.com) 4> add extract dpmysql,exttrailsource /gg/gate/mysql/dirdat/lt

EXTRACT added.

GGSCI (xcell.home.com) 4>add rmttrail /gg/gate/oracle/dirdat/rt,extract dpmysql

RMTTRAIL added.

GGSCI (xcell.home.com) 4>start dpmysql

Before starting pump need to start manager process on remote site i.e oracle end .

Oracle GG_configuration

1. Here is have created a database called PCGB(container)/POGB(pluggable database)

2. Make sure both database and listener up and running

3. Enable the parameter for GoldenGate replication

alter system set enable_goldengate_replication=true;

4. Create golden gate user and grant below roles under pluggable database

create user ggadmin identified by ggadmin;

exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggadmin',privilege_type=>'CAPTURE',grant_optional_privileges=>'*');

5. Add supplemental logdata on columns

alter database add supplemental log data(all) columns;

6. Create separate schema called myora where dept table will be replicated.

Create user myora identified by myodadb;

create table dept (deptno number,dname varchar2(10),loc varchar2(15));

7. Set Oracle _home and then Go to Golden gate binary location ,

GGSCI (xcell.home.com) 2> dblogin userid ggadmin@POGB, password ggadmin

Successfully logged into database POGB.

GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 3> create wallet

Created wallet.

GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 3> add credentialstore

Credential store created.

GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 3> alter credentialstore add user ggadmin@pogb,password ggadmin,alias oggadmin_ggs

Credential store altered.

GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 4> view param REPMYSQL

REPLICAT repmysql

SETENV(ORACLE_HOME='/u01/oracle/database19c')

SETENV(ORACLE_SID='pogb')

SETENV(TNS_ADMIN='/u01/oracle/database19c/network/admin/')

useridalias oggadmin_ggs

ASSUMETARGETDEFS

MAP mydb.*,target MYORA.*;

GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 4>

GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 4> add replicat repmysql,integrated exttrail /gg_gate/oracle/dirdat/rt

GGSCI (xcell.home.com) 2> start REPMYSQL

Sending START request to MANAGER ...

REPLICAT REPMYSQL starting

GGSCI (xcell.home.com) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPMYSQL 00:00:00 46:37:47

GGSCI (xcell.home.com) 4>

Step 4: Add some rows on dept table in mysql and check the table .

mysql> use mydb;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+

| Tables_in_mydb |

+----------------+

| CORONA |

| DEPT |

| state_wise |

| travel_history |

+----------------+

4 rows in set (0.00 sec)

mysql> desc DEPT;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| LOC | varchar(13) | YES | | NULL | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.24 sec)

mysql> insert into DEPT values (10,'CLERK','BANGALORE');

Query OK, 1 row affected (0.25 sec)

mysql> select * from DEPT;

+--------+-------+-----------+

| DEPTNO | DNAME | LOC |

+--------+-------+-----------+

| 10 | CLERK | BANGALORE |

+--------+-------+-----------+

1 row in set (0.00 sec)

mysql>

GGSCI (xcell.home.com) 3> stats EXTMYSQL

Sending STATS request to EXTRACT EXTMYSQL ...

Start of Statistics at 2020-04-28 21:33:55.

Output to /gg_gate/mysql/dirdat/lt:

Extracting from mydb.DEPT to mydb.DEPT:

*** Total statistics since 2020-04-28 21:33:08 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2020-04-28 21:33:08 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2020-04-28 21:33:08 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2020-04-28 21:33:08 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (xcell.home.com) 4>GGSCI (xcell.home.com) 5> stats DPMYSQL

Sending STATS request to EXTRACT DPMYSQL ...

Start of Statistics at 2020-04-28 21:34:15.

Output to /gg_gate/oracle/dirdat/mysql/rt:

Extracting from mydb.DEPT to mydb.DEPT:

*** Total statistics since 2020-04-28 21:33:09 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2020-04-28 21:33:09 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2020-04-28 21:33:09 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2020-04-28 21:33:09 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (xcell.home.com) 6>

Ø CHECK IN REMOTE END

GGSCI (xcell.home.com) 4> stats REPMYSQL

Sending STATS request to REPLICAT REPMYSQL ...

Start of Statistics at 2020-04-28 21:35:01.

Integrated Replicat Statistics:

Total transactions 1.00

Redirected 0.00

Replicated procedures 0.00

DDL operations 0.00

Stored procedures 0.00

Datatype functionality 0.00

Operation type functionality 0.00

Event actions 0.00

Direct transactions ratio 0.00%

Replicating from mydb.DEPT to POGB.MYORA.DEPT:

*** Total statistics since 2020-04-28 21:34:22 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2020-04-28 21:34:22 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2020-04-28 21:34:22 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2020-04-28 21:34:22 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total upserts 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (xcell.home.com) 5>

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 28 21:35:52 2020

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> alter session set container=POGB;

Session altered.

SQL> select * from MYORA.DEPT;

DEPTNO DNAME LOC

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

10 CLERK BANGALORE

SQL>

Database Administrator Blogs

Ask Me

Networking Concepts

Oracle Interview Question- (Chapter - Data Guard)

Oracle RAC OCR and Voting Disk Replacement

Linux : Create filesystem using LVM

Oracle Interview Questions (RAC)

Oracle Interview Questionware(Chapter-2)

Oracle 12c New Feacture

SUSE Linux Bug 208 day server crash

Golden Gate Specific Errors

Golden Gate cross platform replication from MySQL to Oracle

Popular Posts

Followers

Total Pageviews

About Me