Oracle Database Resizing Redolog files

Re size Redo log file

At this Article I'd like to present Resizing Redo log file, So 2 days ago i got a question "how do you re size redo log file" at my intern view. The simple question was good and i decided to post about this issue.

First of all lets check our redo log group.

 SQL>  select x.group#,x.member,y.bytes/1024/1024 "size"

  2  from v$logfile x, v$log y

  3  where x.group# = y.group#;

    GROUP# MEMBER                                         size

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

         3 /u01/app/oracle/oradata/orcl/redo03.log          50

         2 /u01/app/oracle/oradata/orcl/redo02.log          50

         1 /u01/app/oracle/oradata/orcl/redo01.log          50

I've 3 group and 50mb size of them.

Lets check the status 

SQL> select group#,status from v$log;

    GROUP# STATUS

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

         1 INACTIVE

         2 CURRENT

         3 INACTIVE

 Now switch the log file to the last group.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS

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

         1 INACTIVE

         2 ACTIVE

         3 CURRENT

 If we want to make active group to inactive we would use the checkpoint command.

SQL> alter system checkpoint global;

System altered.

 So I'm gonna re size first log file from 50mb to 60mb.

first make sure group 1 is inactive then drop it.

SQL> alter database drop logfile group 1;

Database altered.

 i have got 3 group that's why i should have at less 2 group at my database. if you try to drop another one you would got a error.

Now I'm going to add my new group 1 sized 60mb

SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl/redo01.log') size 60m reuse;

Database altered.

check the status.

SQL> select group#,status from v$log;

    GROUP# STATUS

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

         1 UNUSED

         2 INACTIVE

         3 CURRENT

 okay its done.

check the size of group

SQL> select x.group#,x.member,y.bytes/1024/1024 "size"

  2  from v$logfile x, v$log y

  3  where x.group# = y.group#;

    GROUP# MEMBER                                         size

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

         3 /u01/app/oracle/oradata/orcl/redo03.log          50

         2 /u01/app/oracle/oradata/orcl/redo02.log          50

         1 /u01/app/oracle/oradata/orcl/redo01.log          60

So that's it, 

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.

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!

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

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