Database In Memory : November 2016

Thursday, November 17, 2016

Keygen # GoldenGate Password Encryption

Advantages #

password protection

Trails can be encrypted

> PROCEDURE to implement >

using "keygen" utility, we can generate hex keys

after Enter the hex keys in ASCII file in goldengate home directory and encrypt the password

Step 1# create hex keys using keygen utility # keygen utility exists in goldengate home; key length is max 256 bits

Syntax # ./keygen <key_length> <Number of keys>

[oracle@tnc1 home]$ ./keygen 128 4

0x05A5A84484FA4824B0BF2528ADC0E461

0xD392635258E8AC4D0C417D054988F659

0xA1801E602CD6107767C2D462E54F0852

0x6F6ED96D01C47420C3432C4081171A4A

Step 2 # Enter all 4 keys in ASCII file ENKEYS and < Name key value with key Name as follows >

[oracle@tnc1 home]$touch ENCKEYS

[oracle@tnc1 home]$ cat ENCKEYS

# Key name key Value

key128_1 0x05A5A84484FA4824B0BF2528ADC0E461

key128_2 0xD392635258E8AC4D0C417D054988F659

key128_3 0xA1801E602CD6107767C2D462E54F0852

key128_4 0x6F6ED96D01C47420C3432C4081171A4A

Usually we encrypt the ggsuser password with <encrypt_type>

encrypt_type = AES128, AES192, AES256
Syntax #

GGSCI> encrypt password <GOLDENGATE Password> <encrypt_type> ENCRYPTKEY <keyname>

GGSCI (tnc1.localdomain) 2> encrypt password oracle aes128 encryptkey key128_2

Encrypted password: AADAAAAAAAAAAAGAKHGAGDRIUAZDVENBDIJHSCIEVDFJCAFJUBXIQDEDXHLHIBHJPICCJILGSEOATIJJ

Algorithm used: AES128

Testing database login from goldengate command line utility # ggsci >

GGSCI (tnc1.localdomain) 5> dblogin userid ggsuser password AADAAAAAAAAAAAGAKHGAGDRIUAZDVENBDIJHSCIEVDFJCAFJUBXIQDEDXHLHIBHJPICCJILGSEOATIJJ, encryptkey key128_2

Successfully logged into database.

STEP3 # copy ENKEYS file to source and Target goldengate homes #

--Nikhil Tatineni--

--Oracle in memory---

finding Culprit SQL in RAC cluster

Following Query’ Tells about > SQL Query's in RAC which are waiting to get CPU (or) Query is waiting on CPU >

Depend on result of following query

we will dive in and find set of sql’s which is waiting to get CPU

or which is waiting on CPU :)

SQL> select * from (

select

SQL_ID ,

sum(decode(session_state,'ON CPU',1,0)) as CPU,

sum(decode(session_state,'WAITING',1,0)) -

sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),

0)) as WAIT,

sum(decode(session_state,'WAITING', decode(wait_class, 'User

I/O',1,0),0)) as IO,

sum(decode(session_state,'ON CPU',1,1)) as TOTAL

from v$active_session_history

where SQL_ID is not NULL

group by sql_id

order by sum(decode(session_state,'ON CPU',1,1))

)

where rownum <11;

following .......

Run following Query to get wait events on database and match Query is waiting on CPU

SQL> SELECT DISTINCT wait_class FROM gv$event_name ORDER BY 1;

Get wait_class from gv$event_name & replace wait_class and find out culprit SQL on database >

SQL> SELECT sql_id, COUNT(*)

FROM gv$active_session_history ash, v$event_name evt

WHERE ash.sample_time > SYSDATE - 3/24

AND ash.session_state = 'WAITING'

AND ash.event_id = evt.event_id

AND evt.wait_class = 'System I/O'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

From gv$active_session_history" we will get sql_id and helps to check execution plan of sql statement :) and Investigate further to decrease waits on cluster

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('xxxxxxxxxxxx'));

---Nikhil Tatineni---

---Oracle Database Admin ---

Thursday, November 10, 2016

Remove DDL replication # GoldenGate

Removing DDL replication from GoldenGate >

[oracle@red goldengate]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 2 22:16:31 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> @ddl_disable

Trigger altered.

SQL> @ddl_remove.sql

DDL replication removal script.

WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggsuser

Working, please wait ...

Spooling to file ddl_remove_spool.txt

Script complete.

SQL> @marker_remove.sql

Marker removal script.

WARNING: this script removes all marker objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggsuser

PL/SQL procedure successfully completed.

Sequence dropped.

Table dropped.

Script complete.

DDL replication dropped successfully <<

---Nikhil Tatineni--

--GoldenGate--

Replicat struck on trail # goldengate

Scenario # Replicat struck on trail and data is out of sync with source and target

> Root cause > Replicat unable to perform " ETROLLOVER "

GGSCI (tnc0624) 5> info REP1

REPLICAT REP1 Last Started 2016-11-06 08:07 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Log Read Checkpoint File /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026

2016-11-06 08:21:23.805171 RBA 1534

GGSCI (tnc0624) 6> stats REP2

Sending STATS request to REPLICAT REP2 ...

No active replication maps.

GGSCI (tnc0624) 7> stats REP1

Sending STATS request to REPLICAT REP1 ...

No active replication maps.

GGSCI (tnc0624) 8> sh ls -ltr /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8*

-rw-rw-rw- 1 ggs01g ggs01g 399999709 Nov 3 15:30 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000018

-rw-rw-rw- 1 ggs01g ggs01g 19982829 Nov 4 04:46 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000019

-rw-rw-rw- 1 ggs01g ggs01g 174809829 Nov 6 07:53 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000020

-rw-rw-rw- 1 ggs01g ggs01g 1289 Nov 6 07:57 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000021

-rw-rw-rw- 1 ggs01g ggs01g 1338 Nov 6 08:03 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000022

-rw-rw-rw- 1 ggs01g ggs01g 1387 Nov 6 08:09 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000023

-rw-rw-rw- 1 ggs01g ggs01g 1436 Nov 6 08:15 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000024

-rw-rw-rw- 1 ggs01g ggs01g 1485 Nov 6 08:21 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000025

-rw-rw-rw- 1 ggs01g ggs01g 1534 Nov 6 08:21 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026
-rw-rw-rw- 1 ggs01g ggs01g 1119647 Nov 6 11:17 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000027

Resolution #

GGSCI (tnc0624) 8> info REP1

REPLICAT REP1 Last Started 2016-11-06 08:07 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Log Read Checkpoint File /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026

2016-11-06 08:21:23.805171 RBA 1534

-rw-rw-rw- 1 ggs01g ggs01g 1534 Nov 6 08:21 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026

-rw-rw-rw- 1 ggs01g ggs01g 1119647 Nov 6 11:17 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000027

Here it is unable to rollover from trail sequence number 26 to 27 :)

perform ETROLLOVER >

GGSCI (tnc0624) 9> stop REP1
GGSCI (tnc0624) 10> stop REP2

GGSCI (tnc0624) 11> alter REP1, extseqno 27 extrba 0

GGSCI (tnc0624) 12> alter REP2, extseqno 27 extrba 0

GGSCI (tnc0624) 13> start REP1

--Nikhil Tatineni--

--Oracle GoldenGate--

Database In Memory