Database In Memory

Monday, February 13, 2017

Manually deleting o.e.m agents using emcli # oem # 12.1.0.5

from $OMS_HOME/bin directory we have "emcli" utility
we can use "emcli" utility, to remove agents manually

Manually deleting o.e.m agents using emcli # oem # 12.1.0.5

[oracle@em12c bin]$ ./emcli version

Oracle Enterprise Manager 12c EM CLI Version 12.1.0.5.0

[oracle@em12c bin]$ pwd

/u01/app/oracle/oms12cr5/oms/bin

[oracle@em12c bin]$ ls -ltr emcli

-rwxr-xr-x. 1 oracle dba 15039 Feb 2 02:36 emcli

[oracle@em12c bin]$ ./emcli login -username=sysman -password=Welcome123

Login successful

[oracle@em12c bin]$ ./emcli sync

Synchronized successfully

[oracle@em12c bin]$ ./emcli version

Oracle Enterprise Manager 12c EM CLI Version 12.1.0.5.0

[oracle@em12c bin]$ ./emcli get_targets

…

…..

….

output omitted …

4 Agent Unreachable oracle_emd 192.168.1.242:3872

4 Agent Unreachable oracle_emd 192.168.1.212:3872

4 Agent Unreachable oracle_emd 192.168.1.150:3872

4 Agent Unreachable oracle_emd 192.168.1.185:3872

4 Agent Unreachable oracle_emd 192.168.1.109:3872

./emcli delete_target -name="192.168.1.242:3872" \-type="oracle_emd" -delete_monitored_targets

./emcli delete_target -name="192.168.1.212:3872" \-type="oracle_emd" -delete_monitored_targets

./emcli delete_target -name="192.168.1.150:3872" \-type="oracle_emd" -delete_monitored_targets

./emcli delete_target -name="192.168.1.185:3872" \-type="oracle_emd" -delete_monitored_targets

./emcli delete_target -name="192.168.1.109:3872" \-type="oracle_emd" -delete_monitored_targets

[oracle@em12c bin]$ ./emcli delete_target -name="192.168.1.178:3872" \-type="oracle_emd" -delete_monitored_targets

Target "192.168.1.178:3872:oracle_emd" deleted successfully

###

References#
https://laurent-leturgez.com/2012/06/11/useful-emcli-commands-in-em-cloud-control-12c/

http://blog.dbi-services.com/oracle-cloud-control-12c-removing-an-agent-is-much-easier-in-oem-12104/

http://www.gokhanatil.com/2013/02/how-to-manually-remove-the-enterprise-manager-gridcloud-control-agent.html

Sunday, January 29, 2017

ORA-24324, ORA-24323, ORA-01090

scenario #
trying to startup / shutdown database encountered with following error #

ORA-24324: service handle not initialized

ORA-24323: value not allowed

ORA-01090: shutdown in progress - connection is not permitted

[oracle@star1 dbs]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 27 18:36:01 2017

SQL> conn / as sysdba

Connected to an idle instance.

SQL> shut immediate;

ORA-24324: service handle not initialized

ORA-24323: value not allowed

ORA-01090: shutdown in progress - connection is not permitted

SQL> exit

Disconnected

[oracle@star1 dbs]$ ps -ef | grep pmon

oracle 1337 667 0 18:36 pts/0 00:00:00 grep pmon

ORA-24324 During Startup or Shutdown (Doc ID 794293.1)

checked for any oracle server process i.e is up and running on server # and kill them ..
I followed document from Metalink to resolve this issue in my environment

kill them if any oracle server process are running # ###

oracle@star1(star) ~> ps -ef | grep ora_ | grep $ORACLE_SID

oracle 1072 1 0 18:26 ? 00:00:00 ora_diag_star

oracle@star1(star) ~> ps -ef | grep ora_ | grep $ORACLE_SID

oracle 1072 1 0 18:26 ? 00:00:00 ora_diag_star

oracle@star1(star) ~> kill -9 1072

oracle@star1(star) ~> ps -ef | grep ora_ | grep $ORACLE_SID

A) Check for shared memory and semaphores

oracle@star1(star) ~> ipcs -mt

------ Shared Memory Attach/Detach/Change Times --------

shmid owner attached detached changed

0 root Nov 18 21:39:27 Nov 18 21:40:23 Nov 18 21:39:19

425985 oracle Jan 27 18:44:16 Jan 27 18:47:09 Jan 27 18:26:51

458754 oracle Jan 27 18:44:16 Jan 27 18:47:09 Jan 27 18:26:51

491523 oracle Jan 27 18:44:16 Jan 27 18:47:09 Jan 27 18:26:51

oracle@star1(star) ~> ipcrm -m 425985

oracle@star1(star) ~> ipcrm -m 458754

oracle@star1(star) ~> ipcrm -m 491523

oracle@star1(star) ~> ipcs -mt

------ Shared Memory Attach/Detach/Change Times --------

shmid owner attached detached changed

0 root Nov 18 21:39:27 Nov 18 21:40:23 Nov 18 21:39:19

removed shared memory and started database successfully
kindly go through Metalink "Doc ID 794293.1" to resolve this issue

--Nikhil Tatineni--

ARCH: Error 19809

### Scenario #

trying to startup database,

encountered with "ORA-03113: end-of-file on communication channel"

Investigated root cause from alert log file

# Instance failed with "ARCH: Error 19809"

inv

SQL> startup;

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size 2232640 bytes

Variable Size 1996492480 bytes

Database Buffers 1191182336 bytes

Redo Buffers 16928768 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 793

Session ID: 122 Serial number: 3

###

Message from database Alert log file #

ARC2: Becoming the 'no SRL' ARCH

ARC1: Becoming the heartbeat ARCH

Errors in file /u01/app/oracle/diag/rdbms/star/star/trace/star_arc1_827.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.

**********************************************************

ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/star/archivelog/2017_01_27/o1_mf_1_83467_%u_.arc'

ARC1: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/star/archivelog/2017_01_27/o1_mf_1_83468_%u_.arc'

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance star - Archival Error

ORA-16038: log 1 sequence# 83468 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: '/u01/oradata/star/redo01.log'

Errors in file /u01/app/oracle/diag/rdbms/star/star/trace/star_ora_793.trc:

ORA-16038: log 2 sequence# 83467 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 2 thread 1: '/u01/oradata/star/redo02.log'

USER (ospid: 793): terminating the instance due to error 16038

System state dump requested by (instance=1, osid=793), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/star/star/trace/star_diag_758.trc

Dumping diagnostic data in directory=[cdmp_20170127181404], requested by (instance=1, osid=793), summary=[abnormal instance termination].

Instance terminated by USER, pid = 793

######

Resolution #

Edit pfile # and increase "db_recovery_file_dest_size"

startup database with pfile and

create spfile from pfile

work on purging archive logs using RMAN and freeup space in archive log directory

#Oracle

Nikhil Tatineni

Saturday, December 10, 2016

Dataguard Broker # DMON

Anyway, steps to configure dataguard broker >

pre req's #

> Add global database name in listener on primary and standby >

> set dg_broker_start=true > and make sure dg_broker_config_file1 & dg_broker_config_file2 are created >

Step 1 >

Add global database name to listener to both source and target database >

Source >

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = db11g_dgmgrl.localdomain)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = DB11G)

)

Target >

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = sbydb11g_dgmgrl.localdomain)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = SBYDB11G)

)

(SID_DESC =

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = delta)

)

>>>

Step 2> on both primary and standby >

sql > alter system set dg_broker_start=true scope=both;

SQL> show parameter dg_broker

NAME TYPE VALUE

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

dg_broker_config_file1 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1DB11G.dat

dg_broker_config_file2 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2DB11G.dat

dg_broker_start boolean TRUE

Step 3> Register both primary and standydatabase and Enable configuration >

[oracle@dg1 admin]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL>

DGMGRL> conn sys

Unrecognized command "conn", try "help"

DGMGRL> connect sys/oracle@TO_DB11G

Connected.

DGMGRL>

>>>

DGMGRL> create configuration 'BROKER_P' as primary database is 'DB11G' connect identifier is 'TO_DB11G';

Configuration "BROKER_P" created with primary database "DB11G"

>>>>

DGMGRL> add database 'SBYDB11G' as connect identifier is 'TO_SBYDB11G' maintained as physical;

Database "SBYDB11G" added

DGMGRL> show configuration;

Configuration - BROKER_P

Protection Mode: MaxPerformance

Databases:

DB11G - Primary database

SBYDB11G - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

>>>>

DGMGRL> enable configuration;

Enabled.

>>>>

---- Nikhil Tatineni----

how about those chiefs |||||

Tuesday, December 6, 2016

Query's Used to monitor Goldengate Integrated Extract and replicat

Integrated capture GoldenGate views on Source Database >

col CAPTURE_NAME for a20;

col QUEUE_NAME for a15;

col START_SCN for 9999999999;

col STATUS for a10;

col CAPTURED_SCN for 9999999999;

col APPLIED_SCN for 9999999999;

col SOURCE_DATABASE for a10;

col LOGMINER_ID for 9999999;

col REQUIRED_CHECKPOINTSCN for a30;

col STATUS_CHANGE_TIME for a15;

col ERROR_NUMBER for a15;

col ERROR_MESSAGE for a10;

col CAPTURE_TYPE for a10;

col START_TIME for a30

SELECT CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS,

CAPTURED_SCN, APPLIED_SCN, SOURCE_DATABASE,

LOGMINER_ID, REQUIRED_CHECKPOINT_SCN,

STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE,

CAPTURE_TYPE, START_TIME

FROM DBA_CAPTURE;

> dynamic statistics from the GoldenGate views

col state for a30;

SELECT sid, serial#, capture#, CAPTURE_NAME, STARTUP_TIME, CAPTURE_TIME,

state, SGA_USED, BYTES_OF_REDO_MINED,

to_char(STATE_CHANGED_TIME, 'mm-dd-yy hh24:mi') STATE_CHANGED_TIME

FROM V$GOLDENGATE_CAPTURE;

>>>>

col capture_message_create_time for a30;

col enqueue_message_create_time for a27;

col available_message_create_time for a30;

SELECT capture_name,

to_char(capture_time, 'mm-dd-yy hh24:mi') capture_time,

capture_message_number,

to_char(capture_message_create_time ,'mm-dd-yy hh24:mi') capture_message_create_time,

to_char(enqueue_time,'mm-dd-yy hh24:mi') enqueue_time,

enqueue_message_number,

to_char(enqueue_message_create_time, 'mm-dd-yy hh24:mi') enqueue_message_create_time,

available_message_number,

to_char(available_message_create_time,'mm-dd-yy hh24:mi') available_message_create_time

FROM GV$GOLDENGATE_CAPTURE;

>>>

Monitor the number of open transactions and LCRs for each capture processes >

SELECT component_name capture_name, count(*) open_transactions,

sum(cumulative_message_count) LCRs

FROM GV$GOLDENGATE_TRANSACTION

WHERE component_type='CAPTURE'

group by component_name;

>>> >>>

LogMiner views for LogMiner sessions and statistics >

col db_name for a15;

select INST_ID, SESSION_ID,SESSION_NAME,SESSION_STATE, DB_NAME,

NUM_PROCESS,START_SCN,END_SCN,SPILL_SCN, PROCESSED_SCN, PREPARED_SCN,

READ_SCN MAX_MEMORY_SIZE,USED_MEMORY_SIZE PINNED_TXN, PINNED_COMMITTED_TXN

from GV$LOGMNR_SESSION;

SELECT SESSION_ID, NAME, VALUE

FROM V$LOGMNR_STATS;

Integrated Capture >

SQL> select CAPTURE_NAME, QUEUE_NAME, STATUS from DBA_CAPTURE;

SQL> select OWNER, QUEUE_TABLE, QUEUE_TYPE from dba_queues where NAME='OGG$Q_TESTEXT1';

Integrated Replicat >

SQL> select REPLICAT_NAME,SERVER_NAME from DBA_GOLDENGATE_INBOUND;

SQL> select APPLY_NAME, QUEUE_NAME, status from dba_apply;

SQL> select apply_name,state from V$GG_APPLY_COORDINATOR ;

—Oracle In-Memory—

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 ---

Database In Memory

Monday, February 13, 2017

Manually deleting o.e.m agents using emcli # oem # 12.1.0.5

Sunday, January 29, 2017

ORA-24324, ORA-24323, ORA-01090

ARCH: Error 19809

Saturday, December 10, 2016

Dataguard Broker # DMON

Tuesday, December 6, 2016

Query's Used to monitor Goldengate Integrated Extract and replicat

Thursday, November 17, 2016

Keygen # GoldenGate Password Encryption

finding Culprit SQL in RAC cluster

Querys to monitor RAC

Followers