Learn DB Concepts with me...

Subscribe to Learn DB Concepts with me... feed
“​Perfection is not attainable, but if we chase perfection we can catch excellence” - Arvind Toorpuarvind toorpunoreply@blogger.comBlogger200125
Updated: 7 hours 13 min ago

Setting up Optach environment variable

Mon, 2017-10-16 17:22

Setting up Optach environment variable :

For Korn / Bourne shell:

% export PATH=$PATH:$ORACLE_HOME/OPatch
For C Shell:
% setenv PATH $PATH:$ORACLE_HOME/OPatch
Categories: DBA Blogs

Simple password encryption package to demonstrate how

Tue, 2017-09-12 11:51
rem -----------------------------------------------------------------------
rem Purpose:   Simple password encryption package to demonstrate how
rem                  values can be encrypted and decrypted using Oracle's
rem                  DBMS Obfuscation Toolkit
rem Note:        Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------


---- create table to store encrypted data

-- Unable to render TABLE DDL for object ATOORPU.USERS_INFO with DBMS_METADATA attempting internal generator.
CREATE TABLE USERS_INFO
(
  USERNAME VARCHAR2(20 BYTE)
, PASS VARCHAR2(20 BYTE)
)users;

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

CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors


CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- key must be exactly 8 bytes long
  c_encrypt_key varchar2(8) := 'key45678';

  function encrypt (i_password varchar2) return varchar2 is
    v_encrypted_val varchar2(38);
    v_data          varchar2(38);
  begin
     -- Input data must have a length divisible by eight
     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
        input_string     => v_data,
        key_string       => c_encrypt_key,
        encrypted_string => v_encrypted_val);
     return v_encrypted_val;
  end encrypt;

  function decrypt (i_password varchar2) return varchar2 is
    v_decrypted_val varchar2(38);
  begin
     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
        input_string     => i_password,
        key_string       => c_encrypt_key,
        decrypted_string => v_decrypted_val);
     return v_decrypted_val;
  end decrypt;


end PASSWORD;
/
show errors

-- Test if it is working...

select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;


--- Insert encrypted Password ---

insert into USERS_INFO values ('redddy',( select password.encrypt('REDDY1') from dual) );
select password.decrypt((pass)) from USERS_INFO where USERNAME='redddy';

Categories: DBA Blogs

update rows from multiple tables (correlated update)

Fri, 2017-07-14 10:01

Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL.
In this article, we are going to look at four scenarios for Oracle cross table update.

Suppose we have two tables Categories and Categories_Test. See screenshots below.

lets take two tables TABA & TABB:

Records in TABA:















Records in TABB:













1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B.

The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.

 update TABA A
set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID);















2. Update data in two columns in table A based on a common column in table B.

If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword.

update TABA A
set (a.LNAME, a.SAL) = (select B.LNAME, B.SAL FROM TABB B where A.ID=B.ID);



Categories: DBA Blogs

How to Secure our Oracle Databases

Fri, 2017-06-02 13:27

How Secure can we make our Oracle Databases??

This is a routine question that runs in minds of most database administrators.  
HOW SECURE ARE OUR DATABASES. CAN WE MAKE IT ANYMORE SECURE.

I am writing this post to share my experience and knowledge on securing databases. I personally follow below tips to secure my databases:


 1. Make sure we only grant access to those users that really need to access database.
2. Remove all the unnecessary grants/privileges from users/roles.
3. Frequently audit database users Failed Logins in order to verify who is trying to login and their actions.
4. If a user is requesting elevated privileges, make sure you talk to them and understand their requirements.
5. Grant no more access than what needed.
6. At times users might need access temporarily. Make sure these temporary access are revoked after tasks are completed.
7. Define a fine boundary on who can access what??
8. Use User profiles / Audit to ensure all activities are tracked.
9.  Enforce complex password. Here is the Link on how to do it 
10 Use Triggers to track user activity.
11. Make sure passwords are encrypted in applications, this can be potential threat if you application code has been compromised.
12. Add password to your listener.
13. Allow access only from needed/known servers/clients. Use Valid_node_checking Link on how to restrict access to servers/clients.







Categories: DBA Blogs

java.lang.SecurityException: The jurisdiction policy files are not signed by a trusted signer

Thu, 2016-12-15 13:18


I was trying to Install OID (Oracle Identity Manager) and I got this error :

Problem:




        at oracle.as.install.engine.modules.configuration.standard.StandardConfigActionManager.start(StandardConfigActionManager.java:186)
        at oracle.as.install.engine.modules.configuration.boot.ConfigurationExtension.kickstart(ConfigurationExtension.java:81)
        at oracle.as.install.engine.modules.configuration.ConfigurationModule.run(ConfigurationModule.java:86)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.SecurityException: Can not initialize cryptographic mechanism
        at javax.crypto.JceSecurity.<clinit>(JceSecurity.java:88)
        ... 31 more
Caused by: java.lang.SecurityException: The jurisdiction policy files are not signed by a trusted signer!
        at javax.crypto.JarVerifier.verifyPolicySigned(JarVerifier.java:328)
        at javax.crypto.JceSecurity.loadPolicies(JceSecurity.java:317)
        at javax.crypto.JceSecurity.setupJurisdictionPolicies(JceSecurity.java:262)
        at javax.crypto.JceSecurity.access$000(JceSecurity.java:48)
        at javax.crypto.JceSecurity$1.run(JceSecurity.java:80)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.crypto.JceSecurity.<clinit>(JceSecurity.java:77)



Cause:

My current version of java was 1.8.* which is not fully supported.
In this case JDK 1.8.0.1 is installed on all nodes in the cluster and JCE local policy version 6 was used for AES 256 kerberos encryption. JCE must be in sync with the JDK version.
      
[oracle@linux06 jdk1.8.0_111]$ cd ..
[oracle@linux06 java]$ ls
default  jdk1.8.0_111  latest
[oracle@linux06 java]$ cd default/
[oracle@linux06 default]$ ls
bin        javafx-src.zip  man          THIRDPARTYLICENSEREADME-JAVAFX.txt
COPYRIGHT  jre             README.html  THIRDPARTYLICENSEREADME.txt
db         lib             release
include    LICENSE         src.zip


Solution:

Download :

For Java 6 use :
http://www.oracle.com/technetwork/java/javase/downloads/jce-6-download-429243.html

For Java 7 use :
http://www.oracle.com/technetwork/java/javase/downloads/jce-7-download-432124.html

For java 8 use :
http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html        

********************    ********************    ********************
Update java with with new java unlimted jusrisdiction :
********************    ********************    ********************

After download and unzip :

[oracle@linux06 JCE]$ unzip jce_policy-8.zip
Archive:  jce_policy-8.zip
   creating: UnlimitedJCEPolicyJDK8/
  inflating: UnlimitedJCEPolicyJDK8/local_policy.jar
  inflating: UnlimitedJCEPolicyJDK8/README.txt
  inflating: UnlimitedJCEPolicyJDK8/US_export_policy.jar
[oracle@linux06 JCE]$ ls -ll
total 16
-rw-rw-r--. 1 oracle oracle 8409 Dec 14 10:39 jce_policy-8.zip
drwxrwxr-x. 2 oracle oracle 4096 Dec 20  2013 UnlimitedJCEPolicyJDK8
[oracle@linux06 JCE]$ pwd
/u01/app/SFTW/JCE
[oracle@linux06 JCE]$ ls
jce_policy-8.zip  UnlimitedJCEPolicyJDK8
[oracle@linux06 JCE]$ cd UnlimitedJCEPolicyJDK8/
[oracle@linux06 UnlimitedJCEPolicyJDK8]$ ls
local_policy.jar  README.txt  US_export_policy.jar

********************    ********************    ********************
as root user backup and replace files (US_export_policy & local_policy.jar)
********************    ********************    ********************

[oracle@linux06 security]$ su root
Password:
[root@linux06 security]# ls
blacklist          java.policy    local_policy.jar
blacklisted.certs  java.security  trusted.libraries
cacerts            javaws.policy  US_export_policy.jar
[root@linux06 security]# cd /usr/java/default/jre/lib/security
[root@linux06 security]# mv US_export_policy.jar US_export_policy.jar_bak
[root@linux06 security]# mv local_policy.jar local_policy.jar_bak
[root@linux06 security]# ls -ll
total 164
-rw-r--r--. 1 root root   4054 Sep 22 18:23 blacklist
-rw-r--r--. 1 root root   1273 Sep 22 18:23 blacklisted.certs
-rw-r--r--. 1 root root 112860 Sep 22 18:23 cacerts
-rw-r--r--. 1 root root   2466 Sep 22 18:23 java.policy
-rw-r--r--. 1 root root  27358 Sep 22 18:23 java.security
-rw-r--r--. 1 root root     98 Sep 22 18:23 javaws.policy
-rw-r--r--. 1 root root   3405 Sep 22 18:35 local_policy.jar_bak
-rw-r--r--. 1 root root      0 Sep 22 18:23 trusted.libraries
-rw-r--r--. 1 root root   2920 Sep 22 18:35 US_export_policy.jar_bak
[root@linux06 security]# pwd
/usr/java/default/jre/lib/security
[root@linux06 security]# cp /u01/app/SFTW/JCE/UnlimitedJCEPolicyJDK8/US_export_policy.jar /usr/java/default/jre/lib/security
[root@linux06 security]# cp /u01/app/SFTW/JCE/UnlimitedJCEPolicyJDK8/local_policy.jar /usr/java/default/jre/lib/security
[root@linux06 security]# ls -ll
total 172
-rw-r--r--. 1 root root   4054 Sep 22 18:23 blacklist
-rw-r--r--. 1 root root   1273 Sep 22 18:23 blacklisted.certs
-rw-r--r--. 1 root root 112860 Sep 22 18:23 cacerts
-rw-r--r--. 1 root root   2466 Sep 22 18:23 java.policy
-rw-r--r--. 1 root root  27358 Sep 22 18:23 java.security
-rw-r--r--. 1 root root     98 Sep 22 18:23 javaws.policy
-rw-r--r--. 1 root root   3035 Dec 14 10:47 local_policy.jar
-rw-r--r--. 1 root root   3405 Sep 22 18:35 local_policy.jar_bak
-rw-r--r--. 1 root root      0 Sep 22 18:23 trusted.libraries
-rw-r--r--. 1 root root   3023 Dec 14 10:46 US_export_policy.jar
-rw-r--r--. 1 root root   2920 Sep 22 18:35 US_export_policy.jar_bak
Categories: DBA Blogs

bash: /bin/install/.oui: No such file or directory

Thu, 2016-12-08 21:30

 Problem:

[oracle@linux5 database]$ . runInstaller
bash: /bin/install/.oui: No such file or directory
[oracle@linux5 database]$ uname -a
Linux linux5 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013 x86_64 x86_64 x86_64 GNU/Linux


Solution:



[oracle@linux5 database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 20461 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4031 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-11-22_09-46-02AM. Please wait ...[oracle@linux5 database]$
Categories: DBA Blogs

uninstall java on linux

Fri, 2016-12-02 12:43
If you are not sure of what the dependent packages that might be blocking java then you can also use yum remove jdk*
This will also take care of dependent rpms.

[root@linux06 usr]# yum remove jdk1.8.0_111-1.8.0_111-fcs.i586

Loaded plugins: refresh-packagekit, security
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package jdk1.8.0_111.i586 2000:1.8.0_111-fcs will be erased
--> Processing Dependency: java for package: jna-3.2.4-2.el6.x86_64
--> Running transaction check
---> Package jna.x86_64 0:3.2.4-2.el6 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================
 Package           Arch        Version                 Repository                                                Size
======================================================================================================================
Removing:
 jdk1.8.0_111      i586        2000:1.8.0_111-fcs      @/jdk-8u111-linux-i586                                   259 M
Removing for dependencies:
 jna               x86_64      3.2.4-2.el6             @anaconda-OracleLinuxServer-201311252058.x86_64/6.5      298 k

Transaction Summary
======================================================================================================================
Remove        2 Package(s)

Installed size: 259 M
Is this ok [y/N]: Y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Erasing    : jna-3.2.4-2.el6.x86_64                                                                             1/2
  Erasing    : 2000:jdk1.8.0_111-1.8.0_111-fcs.i586                                                               2/2
  Verifying  : 2000:jdk1.8.0_111-1.8.0_111-fcs.i586                                                               1/2
  Verifying  : jna-3.2.4-2.el6.x86_64                                                                             2/2

Removed:
  jdk1.8.0_111.i586 2000:1.8.0_111-fcs                                                                               

Dependency Removed:
  jna.x86_64 0:3.2.4-2.el6                                                                                           

Complete!
Categories: DBA Blogs

Is it safe to move/recreate alertlog while the database is up and running

Mon, 2016-11-14 19:00


 Is it safe to move/recreate alertlog while the database is up and running??


It is totally safe to "mv" or rename it while we are running. Since chopping part of it out would be lengthly process, there is a good chance we would write to it while you are editing it so I would not advise trying to "chop" part off -- just mv the whole thing and we'll start anew in another file.

If you want to keep the last N lines "online", after you mv the file, tail the last 100 lines to "alert_also.log" or something before you archive off the rest.



[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle   
488012 Nov 14 10:23 alert_orcl.log

I will rename the existing alertlog file to something
 
[oracle@Linux03 trace]$ mv alert_orcl.log alert_orcl_Pre_14Nov2016.log

[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log
[oracle@Linux03 trace]$ ls -ll alert_*


Now lets create some activity that will need to update the alertlog.

[oracle@Linux03 bin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 14 16:23:02 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

lets see if the new alertlog file has been created.[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle    249 Nov 14 16:23 alert_orcl.log
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log
Categories: DBA Blogs

querying directory permissions granted to a user

Thu, 2016-11-03 16:05
Querying directory permissions granted to a user

SELECT grantee, table_name directory_name, LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY grantee)
  FROM dba_tab_privs
 WHERE table_name ='
DPUMP' group by GRANTEE,TABLE_NAME;




SAMPLE output:

GRANTEE              DIRECTORY_NAME                 GRANTS            
-------------------- ------------------------------ --------------------
SCOTT                  DPUMP                       READ,WRITE         
TIGER                   DPUMP                       READ,WRITE         
TOM                      DPUMP                       READ,WRITE         
CAM                      DPUMP                       READ,WRITE         
SAM                      DPUMP                       READ,WRITE
Categories: DBA Blogs

Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr

Thu, 2016-11-03 11:10


Usage : we can use dbshut script file in $ORACLE_HOME/bin to shutdown  database & listener.

 [oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl
oracle   21133 19211  0 11:01 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ dbshut
Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/shutdown.log

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   21287 19211  0 11:09 pts/0    00:00:00 grep pmon
[oracle@Linux03 bin]$





Error : Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr

[oracle@Linux03 bin]$ dbshut

Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr


 Solution (same as above): edit dbshut script and change

From : ORACLE_HOME_LISTNER=$1
 To  :  ORACLE_HOME_LISTNER=$ORACLE_HOME 




Note : 
One pre-req for this script to run is set 'Y' in /etc/oratab. This basically tell the script to start all database instance running of this home. while 'N' tells not to start db using dbshut script.

[oracle@Linux03 Desktop]$ grep 'orcl' /etc/oratab
orcl:/u01/app/oracle/product/12.1.0.2/db_1:Y
Categories: DBA Blogs

dbstart: line 275: ORACLE_HOME_LISTNER: command not found

Thu, 2016-11-03 11:00

Usage : we can use dbstart script file in $ORACLE_HOME/bin to start database & listener.

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20588 19211  0 10:56 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ dbstart
Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/startup.log

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl
oracle   21035 19211  0 10:57 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ 



Common error with dbstart script :


Error : /u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found

[oracle@Linux03 bin]$ dbstart

/u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found


Solution : Edit dbstart script and change (~ line 275)

 From : ORACLE_HOME_LISTNER=$1
 To   : ORACLE_HOME_LISTNER=$ORACLE_HOME



Note : 
One pre-req for this script to run is set 'Y' in /etc/oratab. This basically tell the script to start all database instance running of this home. while 'N' tells not to start db using dbshut script.

[oracle@Linux03 Desktop]$ grep 'orcl' /etc/oratab
orcl:/u01/app/oracle/product/12.1.0.2/db_1:Y

Categories: DBA Blogs

expdp content=data_only

Tue, 2016-10-11 23:19
[oracle@oracle1 dpump]$ expdp atest/password directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log

Export: Release 11.2.0.1.0 - Production on Wed Feb 11 10:58:23 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATEST"."SYS_EXPORT_TABLE_01":  atest/******** directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "ATEST"."TEST_TAB1"                         5.937 KB      11 rows
Master table "ATEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATEST.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/test_tab1.dmp
Job "ATEST"."SYS_EXPORT_TABLE_01" successfully completed at 10:58:26

[oracle@oracle1 dpump]$ clear
[oracle@oracle1 dpump]$ impdp atest2/password directory=dpump dumpfile=test_tab1.dmp content=data_only logfile=test_tab1_imp.log TABLE_EXISTS_ACTION=truncate remap_schema=atest:atest2

Import: Release 11.2.0.1.0 - Production on Wed Feb 11 10:58:50 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATEST2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ATEST2"."SYS_IMPORT_FULL_01":  atest2/******** directory=dpump dumpfile=test_tab1.dmp content=data_only logfile=test_tab1_imp.log TABLE_EXISTS_ACTION=truncate remap_schema=atest:atest2
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATEST2"."TEST_TAB1"                        5.937 KB      11 rows
Job "ATEST2"."SYS_IMPORT_FULL_01" successfully completed at 10:58:52
Categories: DBA Blogs

ORA-14074: partition bound must collate higher than that of the last partition

Tue, 2016-10-11 23:16

I have a table AUDIT_LOGONS, it has 5 partitions in it and one partition is defined as MAXVALUE. All partitions has some data (see below screen) in it except the MAXVALUE partition. Now I want to add a new partition which has date values less than 2016-05-31



 But I am getting error ORA-14074

sql :

alter table AUDIT_LOGONS add partition AUDIT_LOGONS_P1 VALUES LESS THAN (TO_DATE(' 2016-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));

and I get this error :

SQL Error: ORA-14074: partition bound must collate higher than that of the last partition
14074. 00000 -  "partition bound must collate higher than that of the last partition"
*Cause:    Partition bound specified in ALTER TABLE ADD PARTITION

Solution 1:

We can add a sub-partition to the partition that was set with MAXVALUE (AUDIT_LOGONS5 in this case). In below sql we are modifying the partition audit_logons5 adding a sub-parition audit_logons6 which will have all the data which has date below "2016-09-30"


ALTER TABLE MONTHLY_SALES MODIFY PARTITION AUDIT_LOGONS5 ADD SUB-PARTITION AUDIT_LOGONS6  VALUES LESS THAN (TO_DATE('2016-09-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));

Note : the partition can be renamed anytime

Solution 2 (This will not work for all):

One solution is to drop that Maxvalue (AUDIT_LOGONS5 in this case) partition if there is no data in it and then we can recreate another partitions with defined dates like below.

ALTER TABLE monthly_sales DROP PARTITION AUDIT_LOGONS5;

ALTER TABLE monthly_sales ADD PARTITION AUDIT_LOGONS5 VALUES LESS THAN (TO_DATE('2016-09-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));








Categories: DBA Blogs

oracle real time apply on standby

Sun, 2016-10-09 11:47
 
                   Oracle Real Time Apply on Standby


By default, log apply services wait for the full archived redo log file to arrive on the standby database before applying it to the standby database. If the real-time apply feature is enabled, log apply services can apply redo data as it is received from the Primary DB, without waiting for the current standby redo log file to be archived. We can use the ALTER DATABASE statement to enable the real-time apply feature, as below:

  • For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.

  • For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

NOTE : Standby redo log files are required to use real-time apply.


Lets Test it:

oracle@ORCLSTDBY:[~] $ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 4 10:57:52 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  IDLE

-- Dest_id can be different in your database. but mostly it will be set to local.
-- Lets start applying logs and start the recovery mode Default (apply on log fill up)

SQL> recover managed standby database disconnect from session;
Media recovery complete.

-- Query the Recovery Mode now:

SQL> col DEST_NAME format A20
col status format A10
col type format A10
col recovery_mode format A30

select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  MANAGED

-- See that Recovery Mode will be just Managed. 

-- Lets stop log Apply and change it the recovery mode to Real-Time Apply

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME                       STATUS      TYPE       SRL        RECOVERY_MODE
--------------------                   ----------       ----------     ---          ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL   NO  MANAGED REAL TIME APPLY


-- We can also check this in alertlog_File.log


Completed: ALTER DATABASE RECOVER  managed standby database cancel
Tue Oct 04 11:00:47 2016
.
.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLSTDBY)
Tue Oct 04 11:00:47 2016
MRP0 started with pid=58, OS id=40557
MRP0: Background Managed Standby Recovery process started (ORCLSTDBY)
 started logmerger process
Tue Oct 04 11:00:52 2016
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...


Reference Oracle Docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1034632


 Similar Posts :


Categories: DBA Blogs

ALTER SYSTEM KILL SESSION

Thu, 2016-09-29 13:45

Note : Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed.
 
There could be a number of reasons to kill non-essential Oracle user processes. In Oracle the alter system kill session command allows us to kill these Oracle sessions. 

The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number.

First you have to identify the session to be killed with alter system kill session.

select SID,SERIAL#,STATUS,SCHEMANAME,PROGRAM from v$session;


The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';


Sometimes Oracle is not able to kill the session immediately with the alter system kill session  command alone. Upon issuing the alter system kill session  command, the session will be 'marked for kill'. It will then be killed as soon as possible.


In the case of a session being 'marked for kill' after issuing the alter system kill session  command and not killed immediately, the alter system kill session  command can be forced by adding the immediate keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 


In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';


References :


Categories: DBA Blogs

Automate Kill SNIPED SESSION

Thu, 2016-09-29 13:24

If you have configured IDLE_TIME inr your user profile.

IDLE_TIME 

Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit

Lets say a session has been idle for 10 Minutes. Session will continue to show as idle even after the idle_time for that user,as specified in that user's profile, has expired. When the user attempts to run a transaction against the database after the idle_time has expired, the database will disconnect the user by terminating the session. After this, the
session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your 10 minutes -- it is already "dead", it just doesn't show as dead yet. PMON will eventually snipe the session, marking it dead in v$session.

Reference this oracle Document for more information.

Once the oracle session is changed to SNIPED status, we can kill that session without any problem. How ever this be done manually, but watching for these SNIPED sessions every time can be irritating, we can automate the job of killing SNIPED session as below.


-- This is an optional table. Create this only if you want to Audit the killed session.

CREATE
  TABLE "AUDIT_KILL_SNIPED_SESSIONS"
  (
    "SID"        NUMBER,
    "SERIAL#"    NUMBER,
    "TIME_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
    "USERNAME"   VARCHAR2(50 BYTE)
  )TABLESPACE "USERS" ;


Note :     Inorder to create below procedure you will need DBA role & "ALTER SYSTEM" grant. If you are creating this procedure in non DBA user, you will need grant select on V$SESSION & "ALTER SYSTEM" grant


-- Procedure to kill sessions that are in sniped state

create or replace Procedure KILL_SNIPED_SESSIONS as
cursor SEL_SID is select SID,SERIAL#,USERNAME from v$session where status='SNIPED';
SEL_REC SEL_SID%ROWTYPE;
V_SQL varchar2(100);
V_SQL1 varchar2(100);

Begin
OPEN SEL_SID;
LOOP
FETCH SEL_SID INTO SEL_REC;
EXIT WHEN SEL_SID%NOTFOUND;

V_SQL :='ALTER SYSTEM KILL SESSION '''||SEL_REC.SID||','||SEL_REC.SERIAL#||''' IMMEDIATE';
-- DBMS_OUTPUT.PUT_LINE(V_SQL);
execute immediate V_SQL;
--- LETS AUDIT THE KILLED SESSIONS DATA HERE
-- COMMENT LINES BELOW THIS TO REMOVE AUDITING


V_SQL1:= 'insert INTO AUDIT_KILL_SNIPED_SESSIONS (SID,SERIAL#,USERNAME) VALUES ('''||SEL_REC.SID||''','''||SEL_REC.SERIAL#||''','''||SEL_REC.USERNAME||''')';
-- DBMS_OUTPUT.PUT_LINE(V_SQL1);
COMMIT;
execute immediate V_SQL1;

-- COMMENT UNTILL THIS LINE TO REMOVE AUDITING

END LOOP;
CLOSE SEL_SID;

END;


-- YOU CAN ALSO SETUP A JOB TO RUN EVERY 30 MINS OR HOUR TO EXECUTE THIS PROCEDURE

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"EXEC_KILL_SNIPED_SESSIONS"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'KILL_SNIPED_SESSIONS',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP_TZ('2016-09-29 13:07:38.837143000 AMERICA/CHICAGO','YYYY-MM-DD HH24:MI:SS.FF TZR'),
            repeat_interval => 'FREQ=HOURLY',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'Job to run KILL_SNIPED_SESSIONS procedure that kills SNIPED SESSIONOS ');

       
    DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"EXEC_KILL_SNIPED_SESSIONS"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
     
 
   
    DBMS_SCHEDULER.enable(
             name => '"EXEC_KILL_SNIPED_SESSIONS"');
END;


References :

http://arvindasdba.blogspot.com/2016/09/alter-system-kill-session.html
Categories: DBA Blogs

Using special characters in ORACLE DB passwords

Thu, 2016-07-21 11:37

Using special characters in ORACLE DB passwords  
Using special characters in ORACLE DB passwords. Most times we restrict our selves from using complicated special characters as we think that we can't use them in passwords, but in oracle DB world we are allowed to use most special characters as passwords. See this simple example to see how to use special char's in passwords and login.




To log into DB using cmdline we need to use single quote ''. else oracle some times doesn't recognize the password. See below example :

I created a user with password using special characters.

SQL> alter user C##atest identified by "atest113..";

User C##ATEST altered.

[oracle@Linux03 admin]$ sqlplus C##atest/atest113..

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:39:56 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Solution to this is to use single quote:

[oracle@Linux03 admin]$ sqlplus C##atest/"atest113$$"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:41:44 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C

press ctrl+c to escape from this line


[oracle@Linux03 admin]$ sqlplus C##atest/'atest113$$&&'

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:43:20 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Jul 20 2016 16:42:54 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "C##ATEST"

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@Linux03 admin]$ sqlplus C##atest/atest113$$&&

Categories: DBA Blogs

configure complex password (password verify function) in oracle database

Wed, 2016-07-20 15:38

 configure password verify function in oracle database
We can enable the oracle provided "password verify function" to enforce strong password restrictions for our DB users. This function with other profile parameters can create a strong security for the database.To enable the oracle password verification function you need to execute the utlpwdmg.sql file from ORACLE_HOME/rdbms/admin as sysdba.
 

[oracle@Linux03 home]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/
[oracle@Linux03 admin]$ ls utlpwdmg.sql

[oracle@Linux03 admin]$ sqlplus / as sysdba
SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql

SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Function created.

Grant succeeded.

Function created.

Grant succeeded.

Profile altered.

Note : You can query current default profile settings using as below

select * from DBA_PROFILES where profile='DEFAULT';

Starting from 12c this file (utlpwdmg.sql) creates four functions under user SYS. Here we have an option to select one from four functions.

ORA12C_STRONG_VERIFY_FUNCTION
ORA12C_VERIFY_FUNCTION
VERIFY_FUNCTION
VERIFY_FUNCTION_11G

This function makes the minimum complexity checks like the minimum length of the password, password not same as the
username, etc. The user may enhance this function according to your need.

You can change the password verify function by using below sql :

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION

IT SETS UP FOLOWING PARAMETERS IN DEFAULT PROFILE:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1

-- This script alters the default parameters for Password Management. This means that all the users on the system have Password Management enabled and set to the following values unless another profile is created with parameter values set to different value or UNLIMITED  is created and assigned to the user.

LETS TEST THE PASSWORD FUNCTION BY CREATING A NEW USER.

SQL> create user C##atest identified by atest11;

Error starting at line : 2 in command -
create user C##atest identified by atest11
Error report -
SQL Error: ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
28003. 00000 -  "password verification for the specified password failed"
*Cause:    The new password did not meet the necessary complexity
           specifications and the password_verify_function failed
*Action:   Enter a different password. Contact the DBA to know the rules for
           choosing the new password

          
While using length of 8 chars I was able to create user.

SQL> create user C##atest identified by atest113;

User C##ATEST created.

Categories: DBA Blogs

TNS-00583: Valid node checking: unable to parse configuration parameters

Tue, 2016-07-12 11:19
 TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking: unable to parse configuration parametersI  Received following errors when trying to startup my listener. I verified the listener.ora and sqlnet.ora files and everything seemed to look normal.
 
[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:05:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters



Listener failed to start. See the error message(s) above...

[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:07:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters



Listener failed to start. See the error message(s) above...


I had below line in my sqlnet.ora file.

[oracle@Linux03 admin]$ cat sqlnet.ora_bak
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)  
  

Solution (In my case):

1. Tried to remove the ENCRYPTION_WALLET_LOCATION info and it worked for me. I knew there was somthing wrong with the syntax.
2. Copied the content from ENCRYPTION_WALLET* into notepad ++ . It helped me realize that I was missing two closing parenthesis )) at end.
3. Added them at end. Started the listener and worked.


[oracle@Linux03 encryption_keystore]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:31:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                12-JUL-2016 10:31:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


Also try these if above trick doesn't help.

1.  Oracle listener.ora or sqlnet.ora file contains any special characters.
2.  Oracle Listener.ora or sqlnet.ora file are in wrong format or syntax.
3.  Oracle listener.ora or sqlnet.ora file have some left justified parenthesis which are not accepted by oracle parser.
Categories: DBA Blogs

Enable auditing in oracle database

Thu, 2016-07-07 13:54

ENABLE AUDITING IN ORACLE DATABASE
SERVER SETUP FOR DB AUDITING


Auditing is a default feature available in Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command.

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

SQL>

Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { NONE | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting (choose one based on your requirement):


NONE or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml - Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
OS - Auditing is enabled, with all audit records directed to the operating system's audit trail.


To enable auditing and direct audit records to the database audit trail, we would do the following.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248600 bytes
Variable Size              71303848 bytes
Database Buffers          213909504 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.

SQL>

Note : Enabling and Disabling the auditing in database will only take affect after a db restart.
Categories: DBA Blogs

Pages