A Oracle DBA's BLOG

Welcome to my ORACLE DBA blog. You will be Amazing!!!

Wednesday, April 16, 2014

Oracle Database Audit Trail Security

Oracle Database Audit Trail Security


This article presents Oracle Audit Trail,I will demonstrate how to enable and set audit options.
We all DBA have to set database at security level, because the company doesn’t risk any problems and may not trust users. That’s why one of the best useful Oracle security tool is Audit Trail.

SQL> show parameter audit trail

NAME                           TYPE        VALUE
---------------------------- ----------- ----------------------------
audit_file_dest                string        u01/app/oracle/admin/orcl/adump           
audit_sys_operations      boolean    FALSE
audit_syslog_level          string
audit_trail                      string         NONE

Now lets make Audit Trail by basic Alter Command.
SQL> alter system set audit_sys_operations=TRUE scope=SPFILE;

System altered.

SQL> alter system set audit_trail=db_extended scope=SPFILE;

System altered.

Now close the database to set our changes
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             155192916 bytes
Database Buffers           79691776 bytes
Redo Buffers                2310144 bytes
Database mounted.
Database opened.

Now check it again
SQL> show parameter audit trail

NAME                                     TYPE                     VALUE
---------------------------         -----------       ------------------------------
audit_file_dest                      string                     /u01/app/oracle/admin/orcl/adump
audit_sys_operations            boolean                 TRUE
audit_syslog_level                string
audit_trail                             string                    DB_EXTENDED

Okay, its ready to use AUDIT TRAIL. Lets do some examples.
First give the user some privileges then give the user some object privileges
SQL> Audit create any table,drop any table by VEYSI by ACCESS;
Audit succeeded.

Now create Audit table on Veysi User.
SQL> create table audit_vey ( id number(10) );
Table created.

I check my tables for using.
SQL> SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='VEYSI';

TABLE_NAME
------------------------------
VEY_VEY
TBL_READ_ONLY
STUDENT
FLSHBCK_DB_TEST
AUDIT_VEY
REDEF_TAB2
REDEF_TAB
T2
T1
TB1_VEY1
TABLE1

TABLE_NAME
------------------------------
TAB1_2_LOG
TAB2
TAB1

14 rows selected.

Lets use the student table.
SQL> select * from student;

        ID NAME       SURNAME
---------- ---------- ----------
         1 veysi      gunay
         2 elcin      mustafayev

I have this example table. Now I'm gonna change something.
SQL> insert into student (id,name) values (3,'mustafa');

1 row created.

SQL> update student set surname='pasa' where id=2;

1 row updated.

SQL> commit;            

Commit complete.

SQL> select * from student;

        ID NAME       SURNAME
---------- ---------- ----------
         3 mustafa
         1 veysi      gunay
         2 elcin      pasa

Okay,Now connect as SYS admin and check the audit trail.
SQL> COLUMN user_name       FORMAT A10
SQL> COLUMN action_name   FORMAT A10
SQL> COLUMN object_name   FORMAT A10
SQL> COLUMN sql_text          FORMAT A35

SQL>SELECT username,obj_name,action_name, sql_tex FROM   dba_audit_trail  2  WHERE  username = 'VEYSI'  3  ORDER BY timestamp; USERNAME OBJ_NAME   ACTION_NAME  SQL_TEXT-------- ---------- ------------ -------------------------------------VEYSI     AUDIT_TEST      CREATE TABLE  create table audit_vey (id number)
VEYSI         STUDENT             SELECT         select * from student
VEYSI         STUDENT            INSERT           insert into student (id,name) values (3,'mustafa')
VEYSI         STUDENT             UPDATE       update student set surname='pasa' where id=2
VEYSI         STUDENT             SELECT        select * from student

As you See The Audit Table get the action of Veysi user on the table student. I hope it would useful.

Thursday, April 10, 2014

Data Recovery Advisor Oracle 11g

Data Recovery Advisor Oracle 11g

This article present Oracle 11g new features data recovery advisor. As we all DBA know there are a lot of problems occur to be resolved, So Oracle gift us easy and perfect way to find our errors, advise to solve and the best thing automatic repair failures.
At this scenario I will do a example to be useful post.
First i'll delete a .dbf  BE CARE FULL!!
Check there is no failure.
RMAN> LIST FAILURE;

using target database control file instead of recovery catalog
no failures found that match specification..

 i deleted /u01/app/my_tbs.dbf
[oracle@ora ~]$ cd /u01/app/
[oracle@ora app]$ ls –l
total 8996
drwxrwxr-x 12 oracle oinstall      4096 Feb 28 19:43 oracle
drwxrwxr-x  5 oracle oinstall      4096 Feb 22 18:31 oraInventory
-rw-r-----  1 oracle oinstall   9183232 Apr 10 18:25 users01.dbf
drwxrwxr-x  2 root   root          4096 Feb 23 17:30 veysideneme

Now start up our database and see the error.
 SQL> startup;
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             155192916 bytes
Database Buffers           79691776 bytes
Redo Buffers                2310144 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/u01/app/my_tbs.dbf'


So you see there is no “MY_TBS.DBF”that’s why I couldn’t opened the database.
Now the game of RMAN J
RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
360        HIGH     OPEN      10-APR-14     One or more non-system datafiles are missing


As Rman show as information of missing file. So get the advise of my bro  RMAN J
RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
360        HIGH     OPEN      10-APR-14     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/my_tbs.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 14 
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_755524553.hm

Ahaa! My bro show me a script and let me check it.
RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_755524553.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 14;
   recover datafile 14;

So, I have to restore the datafile 14. My bro “RMAN” is ready for it don’t worry J
RMAN> Repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_755524553.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 14;
   recover datafile 14;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 10-APR-14
using channel ORA_DISK_1

creating datafile file number=14 name=/u01/app/my_tbs.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 10-APR-14

Starting recover at 10-APR-14
using channel ORA_DISK_1

starting media recovery
……………………………………….
media recovery complete, elapsed time: 00:00:15
Finished recover at 10-APR-14
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened

RMAN>

Okay, Now my database is opened and let me check the file is came back or not!
 [oracle@ora ~]$ ls -l /u01/app/
total 111508
-rw-r-----  1 oracle oinstall 104865792 Apr 10 18:49 my_tbs.dbf
drwxrwxr-x 12 oracle oinstall      4096 Feb 28 19:43 oracle
drwxrwxr-x  5 oracle oinstall      4096 Feb 22 18:31 oraInventory
-rw-r-----  1 oracle oinstall   9183232 Apr 10 18:49 users01.dbf
drwxrwxr-x  2 root   root          4096 Feb 23 17:30 veysideneme


Ohhh! I'm glad to use my bro RMAN J So, coffee break!

Thursday, February 20, 2014

Oracle Flashback Technology

Oracle Flashback Database

At this article I will present The Flashback Oracle Tech. As we all know this features let us view the past state of objects.
Also with this amazing technology we can perform queries that shows a details history and Recovery tables to a previous point in time.Also Rollback transaction.
Let me doing some example OF FLASHBACK DATABASE.
First of all we have to flashback ON. So, connect to SQLplus And switch the flashback ON at mount mode.However I would like to set my flashback location…
SQL>ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/veysideneme/archive' SCOPE=SPFILE;
SQL>ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;

AND now
[oracle@ora ~]$ sqlplus / as sysdba
 
SQL>STARTUP MOUNT EXCLUSIVE
SQL>ALTER DATABASE FLASHBACK ON;
SQL>ALTER DATABASE OPEN;

Now check our Db_flashback_retention_target time
SQL> show parameter flashback
 
NAME                                 TYPE        VALUE
---------------------------------- ----------- ------------------------------
db_flashback_retention_target        integer     1440

So, a day we can return back…
And Now I will connect as my user and create a dummy table to exampled.
SQL> conn veysi/veysi
Connected.
SQL> CREATE TABLE flshbck_db_test ( id NUMBER(20) );
 
Table created.
 
SQL> conn / as sysdba
Connected.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL>startup mount exclusive;
ORACLE instance started.
 

Now lets get back to time
SQL> Flashback database to timestamp sysdate-(4/8/14);
 
Flashback complete.
 
SQL> alter database open resetlogs;
 
Database altered.

Lets check table is gone or not!
SQL> conn veysi/veysi
Connected.
SQL> desc flshbck_db_test 
ERROR:
ORA-04043: object flshbck_db_test does not exist

As we see the table already is gone.
Let me doing some other example.
First creating a table to exampled..
SQL> create table flshbck_db_test as select * from dba_objects where rownum < 501;

Table created.

Select my table to check
SQL> select count(*) from flshbck_db_test;
COUNT(*)
---------
500

I will use the SCN number to get back the table so,lets look at the SCN of database
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
 CURRENT_SCN
------------
1116143

Now truncate the table…
SQL> truncate table flshbck_db_test;
Table truncated..


Check the table
SQL> select count(*) from flshbck_db_test;

COUNT(*)
---------
0

Now close the database and use flashback to back
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Open mount mode,
SQL> startup mount;
ORACLE instance started.
Database mounted.

Now  get the table back
SQL> Flashback database to scn 1116143;
Flashback complete.


.Open the database by resetlogs.
SQL> alter database open resetlogs;
Database altered.
Select my table to check


SQL> select count(*) from flshbck_db_test;
COUNT(*)
---------
500

Wednesday, February 5, 2014

Killing Oracle Sessions

Killing Oracle Sessions

Today i had killed session and i would like to share the details with you.

The important thing is here identify session. "DO NOT KILL WRONG SESSION!" 

Connecting to sqlplus as SYS admin
[oracle@ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun feb 5 19:26:28 2014

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


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

Check user
SQL> show user
USER is "SYS"

setting format
SQL> set linesize 100
SQL> column spid format A10
SQL> column username format A10
SQL> column program format A45
SQL> set pagesize 60

Now Selecting our session to be killed

SQL> Select
  2  x.inst_id,
  3  x.sid,
  4  x.serial#,
  5  y.spid,
  6  x.username,
  7  x.program
  8  From gv$session x     
  9  Join gv$process y ON y.addr = x.paddr AND y.inst_id = x.inst_id
 10  Where x.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         55         19 4616       VEYSI      sqlplus@ora.localdomain (TNS V1-V3)
         1          1         91 4920       SYS        sqlplus@ora.localdomain (TNS V1-V3)
         1         49         23 4923       VEY        sqlplus@ora.localdomain (TNS V1-V3)
         1         51         38 5375                  oracle@ora.localdomain (J000)
         1         47         37 5377                  oracle@ora.localdomain (J001)

Or the other select way
SQL> select inst_id,sid,serial# from gv$session where username='VEYSI';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1         55         19

Here we go the session information 

Now the simple command used to killing!

Unix commands
find SPID 
% ps -ef | grep ora

Kill by Unix Command 
% kill -9 spid
OR...

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

SQL> ALTER SYSTEM KILL SESSION '55,19,1';
However Oracle 11g change the killing command

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

Saturday, January 4, 2014

Oracle Database Upgrade 10g to 11g

Oracle Database Upgrade 10g to 11g


At this article I'll present Step by step upgrading Oracle 10g database to 11g.During my testing to upgrade i have tried DBUA(database upgrade assistant) and Manual upgrade,however i would like to share Manually upgrading.

Before starting upgrade we should know that database binary,files,listeners,tnsname,spfile and passwordfile had backed.

1. Stop isqlplus  

[oracle@localhost ]$ isqlplusctl stop 
iSQL*Plus 10.2.0.4.0 Copyright (c) 2003, 2005, Oracle. All rights reserved.
 Stopping iSQL*Plus
 iSQL*Plus stopped.

2.Stop listener 
[oracle@localhost ]$lsnrctl stop Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))The command completed successfully

3.Stop Enterprise manager
 [oracle@localhost ]$ emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.


 Now Installing Oracle 11g Software 

The Oracle 10g  had installed on /u01/app/oracle/oracle/product/10.2.0/db_1
The Oracle 11g may install on  /u01/app/oracle/oracle/product/11.2.0/db_1

1. Go to /etc/sysctl.conf  Put the following setting in.
fs.file-max = 65536
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

2. Run /sbin/sysctl -p 

3. Now Create New directory for new version Oracle 11g as above
 [oracle@localhost ]$ echo $ORACLE_BASE
/u01/app/oracle
 [oracle@localhost ]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
 [oracle@localhost ]$ cd /u01/app/oracle/product
 [oracle@localhost ]$ mkdir -p 11.1.0/db_1
 [oracle@localhost ]$ mkdir -p 11.1.0/oraInventory

4   4. Now we should change the Bash_profile 
      [oracle@localhost ]$ vi .bash_profile

      # User specific environment and start up programs
PATH=$PATH:$HOME/bin

export PATH

# Oracle settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# Oracle 11g Home settings
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl11g; export ORACLE_SID
# Oracle 10g Home settings
#ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
#ORACLE_SID=orcl10g; export ORACLE_SID
#ORACLE_TERM=xterm; export ORACLE_TERM
#PATH=/usr/sbin:$PATH; export PATH
#PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; 
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; 
export CLASSPATH

if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
            ulimit -p 16384
            ulimit -n 65536
      else
            ulimit -u 16384 -n 65536
      fi
fi


So its time to install Oracle 11g  
Go to Oracle 11g database file and 
[oracle@localhost ]$ ./runinstaller

2.Pre-Upgrade Information Tools


After installation Oracle 11g we should analyze our database. 
This is checking the database for upgrading and replay a report for us
$cp /u01/app/oracle/oracle/product/product/11.2.0/db_1/rdbms/admin/utlu112i.sql /tmp   

Now Lets start. Log in as Oracle 10g Binary
$/u01/app/oracle/oracle/product/product/10.2.0/db_1/bin/sqlplus / as sysdba 

Use the Spool for a report. SQL> SPOOL upgrade_check_veysi.log 
Run the script to check database
SQL> @/tmp/utlu112i.sql
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
So put down the important information, As you see the all should  Valid, otherwise we have to make valid manually the invalid tools.
Now close spool.
SQL> SPOOL OFF
SQL> exit
If we want to looking at the check log,

$vi upgrade_check_veysi.log

Now remove dump_dest parameters from spfile.
SQL> alter system reset background_dump_dest scope=spfile sid='*'; System altered. SQL> alter system reset core_dump_dest scope=spfile sid='*'; System altered. SQL> alter system reset user_dump_dest scope=spfile sid='*'; System altered.

 Shutdown the database and create pfile from spfile.


SQL> shutdown immediate

SQL> create pfile from spfile;

File created.

SQL> exit

Now Copy the init.ora and oraclepassword under Oracle 11g binary

cp initora10g.ora orapwora10g /u01/app/oracle/product/11.1.0/db_1/dbs/

$cd $ORACLE_HOME/rdbms/admin
$sqlplus / as sysdba
Open the database upgrade mode

SQL> STARTUP UPGRADE; 

Use spool again for report
SQL> SPOOL upgrade.log 

Now run the upgrade after it finished we should check upgrade log if we got any error we would fix it.
SQL> @catupgrd.sql 

Close the database normally
SQL> SHUTDOWN IMMEDIATE; 
and startup normally,
SQL> STARTUP;

And now again check the upgrade summary if there is any invalid we would set it valid.
SQL> @utlu112s.sql 

Run the 
SQL> @catuppst.sql 

Compile the invalid objects
SQL> @utlrp.sql

Now check your object.
SQL> SELECT count(*) FROM dba_invalid_objects; 

Now change the listener to Oracle 11g
# listener.ora Network Configuration:
 /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = Orcl11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = xxxxxxxxx))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl11g.oracle.com)(PORT = 1521))
    )
  )
And Start Listener
[oracle@localhost ]$ Start lsnrctl

I hope you will successful on this important issue... Have a nice oracle day :)