Restore archivelogs from RMAN backup

Restore archive logs from RMAN backup

rman> restore archivelog from logseq=37501 until logseq=37798 thread=1;

or

rmna> restore archivelog between sequence 37501 and 37798 ;

Adding datafile to tablespace in RAC

Adding datafile to tablespace in RAC

sql:

CREATE TABLESPACE QUIKPAY_LINDEX
    DATAFILE
        '+DATA/qpdevrac/QUIKPAY_LINDEX01.DBF' SIZE 1G;

Change SYSASM password in RAC environment.

grid@orcldev211:[/u01/app/11.2/grid_home/dbs] $ orapwd file=orapw+ASM password=Oracle

OPW-00005: File with same name exists - please delete or rename


grid@orcldev211:[/u01/app/11.2/grid_home/dbs] $ ls
ab_+ASM1.dat  hc_+ASM1.dat  init.ora  orapw+ASM

Note: Now you can either delete the existing file or use Force=Y parameter to overwrite existing file. I chose to rename existing file.

grid@orcldev211:[/u01/app/11.2/grid_home/dbs] $ mv "orapw+ASM" "orapw+ASM_BAK"

Create a  new password file:

grid@orcldev211:[/u01/app/11.2/grid_home/dbs] $ orapwd file=orapw+ASM password=Oracle

grid@orcldev211:[/u01/app/11.2/grid_home/dbs] $ ls -ll
total 20
-rw-rw----. 1 grid oinstall 1098 May 17 13:35 ab_+ASM1.dat
-rwxrwxr-x. 1 grid oinstall 1544 Oct 21 15:18 hc_+ASM1.dat
-rwxrwxr-x. 1 grid oinstall 2851 May 15  2009 init.ora
-rw-r-----. 1 grid oinstall 1536 Oct 21 16:41 orapw+ASM
-rw-r-----. 1 grid oinstall 1536 Jan 18  2016 orapw+ASM_BAK

grid@orcldev211:[/u01/app/11.2/grid_home/dbs] $ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base remains unchanged with value /u01/app/grid
grid@orcldev211:[/u01/app/11.2/grid_home/dbs] $ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 21 16:41: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 Real Application Clusters and Automatic Storage Management options

SQL>

Setting up Optach environment variable

Setting up Optach environment variable :

For Korn / Bourne shell:

% export PATH=$PATH:$ORACLE_HOME/OPatch

For C Shell:

% setenv PATH $PATH:$ORACLE_HOME/OPatch

Simple password encryption package to demonstrate how

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

update rows from multiple tables (correlated update)

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

How to Secure our Oracle Databases

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.