Database In Memory

Tuesday, April 25, 2017

creating common user # db12c ##

creating common user on container databases

SQL> create user c##nikhil identified by oracle;

User created.

SQL> grant sysdba to c##nikhil;

Grant succeeded.

SQL> grant create session to c##nikhil;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID

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

SYS TRUE FALSE FALSE FALSE FALSE FALSE 1

SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1

SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1

SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1

SYSTEM TRUE FALSE FALSE FALSE TRUE FALSE 1

DBSNMP TRUE FALSE FALSE FALSE TRUE FALSE 1

C##NIKHIL TRUE FALSE FALSE FALSE FALSE FALSE 1

7 rows selected.

connecting using common user as sysdba#

[oracle@kansas ~]$ sqlplus c##nikhil/oracle as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Apr 24 01:17:06 2017

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

SQL>

SQL> show user;

USER is "SYS"

Connecting as common User “c##nikhil# to manage container database #

[oracle@kansas ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Mon Apr 24 01:18:04 2017

Enter user-name: c##nikhil

Enter password:

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

SQL>

SQL> show user;

USER is "C##NIKHIL"

>> >> >>>

---Nikhil Tatineni---

---Oracle Database---

Wednesday, April 12, 2017

Encrypted tables # TDE with goldengate #

In previous releases (up to OGG 11.1.1.0.x) OGG does not support TDE completely.

TDE tablespace encryption is not supported in OGG 10.4 and 11.1.1.0.x

Starting with OGG 11.1.1.1 the behaviour is changed and TDE is supported.

TDE columns are encrypted in the data files and in the redo log, so Extract must be

configured to fetch the clear-text values from the database. To trigger this fetch, use the fetch parameters fetchcols and fetchmodcols.
FETCHCOLS forces a fetch of values that are not in the redo log, and FETCHMODCOLS or FETCHMODCOLS[EXCEPT] forces a fetch of values that are in the logs. Used together, these parameters ensure that the TDE columns are always fetched from the database. The following is an example of how to configure Extract to support TDE. In this example, the TDE column is credit_card_number.

USERID ggs, password ggs
RMTHOST sysb, mgrport 4261
RMTTRAIL C:\ggs\oracle\v8100\dirdat\td
TABLE ab.payment_info, FETCHCOLS (credit_card_number), &
FETCHMODCOLS (credit_card_number);

GoldenGate limitations with TDE:
The table that contains the TDE columns must have a primary or unique key.
Columns that use TDE cannot be part of the primary key.
If there is no primary or unique key defined in the source database, it might be added in the extract.

for more details # please see following note #

See KM 1341598.1 for detail of full TDE support. The basic setup is described in the Oracle GoldenGate 10.4 documentation:

http://download.oracle.com/docs/cd/E15881_01/doc.104/gg_ora_inst_v104.pdf

Replicating Oracle TDE data

If any tables have columns that use Transparent Data Encryption (TDE), check them

Still it is mandatory to have a non-TDE column to be unique.

See Note 1294601.1 GoldenGate workaround for replicating encrypted tables having no PK or UK To enable processing of TDE-protected data

---Nikhil Tatineni--

---TDE --Ogg--

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—

Database In Memory

Tuesday, April 25, 2017

creating common user # db12c ##

Wednesday, April 12, 2017

Encrypted tables # TDE with goldengate #

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

Querys to monitor RAC

Followers