Pages

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
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
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--