Query to find Active users and killing their sessions

Query to find of "Active users" :


SELECT  SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM  V$Session
WHERE
Status=‘ACTIVE’ AND
UserName IS  NOT  NULL ;
SELECT * FROM V$SESSION;
--------------------------------------------------------------------------------


The basic syntax for killing a session is shown below :


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


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

Parameters affecting SGA

The following initialization parameters affect the size of the SGA:

• db_block_buffers,
• db_block_size,
• db_cache_size,
• db_keep_cache_size,
• db_recycle_cache_size,
• java_pool_size.
• large_pool_size,
• log_buffer,
• shared_pool_size,
• streams_pool_size,

Important Parameters in the database

Important parameters in the database commands :


select name from v$database;
select status from v$instance;
select log_mode from v$database;
show parameter db_recovery_file_dest;
select dest_name,status,destination from V$ARCHIVE_DEST;
show parameter archive_dest
show parameter control_files;
show parameter audit_file_dest;
show parameter spfile;
show parameter pfile;
select name from v$datafile;
select name from v$controlfile;
select name from v$tablespace;
select * from v$log;
show parameter BACKGROUND_DUMP_DEST;
select * from v$version;

Oracle Audit Parameters

Commands to enable auditing :


SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
SQL> shut immediate
SQL> startup
-----------------------------------------------------------------------
Command to enable auditing for a schema :


sql>AUDIT ALL BY student BY ACCESS;
sql>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY student BY ACCESS;
sql>AUDIT EXECUTE PROCEDURE BY student BY ACCESS;
-----------------------------------------------------------------------
Command to check audit trail :


SELECT username, obj_name, sql_text FROM dba_audit_trail
SELECT username,
       extended_timestamp,
       owner,
       obj_name,terminal,
       action_name,SQL_TEXT
FROM   dba_audit_trail
ORDER BY timestamp;
----------------------------------------------------------------------
Commands to remove audting :


DELETE FROM sys.aud$;
DELETE FROM sys.aud$ WHERE obj$name='';
SELECT  FROM sys.aud$

Killing a running job in Oracle (scheduled job)

Action : 

Find the job:

select * from dba_scheduler_running_jobs;

Kill the job:

exec DBMS_SCHEDULER.STOP_JOB(job_name => ‘owner.job_name’,force => TRUE);

ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles

ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles

SQL> select file_name from dba_temp_files;

ERROR:

ORA-01187: cannot read from file because it failed verification tests

ORA-01110: data file 205: '/DATA/database/ifsprod/temp_01.dbf'

Drop the tempfile from the database:

SQL> alter database tempfile '/DATA/database/ifsprod/temp_01.dbf' drop;

Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/ifsprod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN

RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN 

Database Details

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

Database Name=OPSDBA

Machine Name=ITLINUXDEVBLADE07

DBID=1499754868 (select dbid from v$database)

SIMULATING CRASH

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

opsdba:/u01/ORACLE/opsdba>ls -l

total 1948980

drwxr-x---    2 oracle  dba          4096 Feb 12 13:35 arch

-rw-r-----    1 oracle   dba       7389184 Feb 12 13:57 control01.ctl

-rw-r-----    1 oracle   dba       7389184 Feb 12 13:57 control02.ctl

-rw-r-----    1 oracle   dba       7389184 Feb 12 13:57 control03.ctl

-rw-r-----    1 oracle   dba      104865792 Feb 12 13:57 drtbs1.dbf

-rw-r-----    1 oracle   dba      104865792 Feb 12 13:57 drtbs2.dbf

drwxr-xr-x    2 oracle  dba          4096 Feb 12 13:55 recovery

-rw-r-----    1 oracle   dba      52429312 Feb 12 13:34 redo01.log

-rw-r-----    1 oracle   dba      52429312 Feb 12 13:35 redo02.log

-rw-r-----    1 oracle   dba      52429312 Feb 12 13:57 redo03.log

-rw-r-----    1 oracle   dba      367009792 Feb 12 13:57 sysaux01.dbf

-rw-r-----    1 oracle   dba      513810432 Feb 12 13:57 system01.dbf

-rw-r-----    1 oracle   dba      20979712 Jan 30 12:32 temp01.dbf

-rw-r-----    1 oracle   dba       5251072 Feb 12 13:57 undonew.dbf

-rw-r-----    1 oracle   dba      634396672 Feb 12 13:57 undotbs01.dbf

-rw-r-----    1 oracle   dba      31465472 Feb 12 13:57 users01.dbf

-rw-r-----    1 oracle   dba      10493952 Feb 12 13:57 users02.dbf

-rw-r-----    1 oracle   dba      10493952 Feb 12 13:57 users03.dbf

-rw-r-----    1 oracle   dba      10493952 Feb 12 13:57 users04.dbf

-rw-r-----    1 oracle   dba      10493952 Feb 12 13:57 users05.dbf

-rw-r-----    1 oracle   dba       5251072 Feb 12 13:57 users06.dbf

-rw-r-----    1 oracle   dba       5251072 Feb 12 13:57 users07.dbf

opsdba:/u01/ORACLE/opsdba>rm *.dbf

                                                                                                                                *FILES REMOVED

opsdba:/u01/ORACLE/opsdba>ls -l *.dbf

ls: *.dbf: No such file or directory

opsdba:/opt/oracle/product10gpr2/dbs>mv spfileopsdba.ora spfileopsdba.org

opsdba:/opt/oracle/product10gpr2/dbs>ls -lt spfile*   *SPFILE REMOVED

-rw-r-----    1 oracle   dba          2560 Feb 12 13:06 spfileopsdba.org

-rw-r-----    1 oracle   dba          2560 Jan 23 11:32 spfilerman10d.ora

-rw-r-----    1 oracle   dba          4608 Dec 18 09:28 spfileerpmig.ora

-rw-r-----    1 oracle   dba          3584 Nov 21 08:22 spfileprimary.ora

-rw-r-----    1 oracle   dba          3584 Oct 22 14:32 spfilebozo.ora

-rw-r-----    1 oracle   dba          4608 Oct  8 15:24 spfilestreams2.ora

-rw-r-----    1 oracle   dba          2560 Oct  1 11:46 spfilevaultdb.ora

THE FOLLOWING STEPS WILL OUTLINE THE RECOVERY PROCESS:

Step 1:   RECOVERY OF SPFILE

Create spfile.rcv as:

set dbid= 1499754868

run {

startup nomount force  ;

 };

opsdba:/u01/ORACLE/opsdba/recovery>rman target / catalog rman10/rman10@rman10p cmdfile=spfile.rcv

Recovery Manager: Release 10.2.0.2.0 - Production on Mon Feb 12 14:02:26 2007

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

connected to target database (not started)

connected to recovery catalog database

RMAN>

executing command: SET DBID

database name is "OPSDBA" and DBID is 1499754868

Oracle instance started

Total System Global Area     264241152 bytes

Fixed Size                     2070416 bytes

Variable Size                113248368 bytes

Database Buffers             142606336 bytes

Redo Buffers                   6316032 bytes

Recovery Manager complete.

opsdba:/u01/ORACLE/opsdba/recovery>

Now restore the spfile

set dbid=1499754868

run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

restore spfile ;

release channel ch1 ;

}

Step 2:   RESTORE OF CONTROLFILES

Same Steps as spfile with the restore command changed. So the new script is

set dbid=1499754868

run {

allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

restore controlfile ;

release channel ch1 ;

}

Step 3:   RESTORE OF DATABASE

Since you have the controlfiles now mount the database

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> alter database mount;

Database altered.

Now get the log sequence number of the database from the catalog database:

select sequence# from rc_backup_redolog where db_name=’OPSDBA’;

RMAN> run {

2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

3> restore database ;

4> recover database until logseq=6;                                -- GOT FROM THE ABOVE QUERY

5> release channel ch1 ;

6> }

7>

allocated channel: ch1

channel ch1: sid=156 devtype=SBT_TAPE

channel ch1: Data Protection for Oracle: version 5.2.4.0

Starting restore at 12-FEB-07

channel ch1: starting datafile backupset restore

channel ch1: specifying datafile(s) to restore from backup set

Step 4:   alter database open resetlogs;