Pages

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