kill session -

Killing session - I want to kill all sessions of DB user abm_test.

alter system kill session 'session-id,session-serial' immediate;
This command kills a session. The session-id and session-serial parameters are found in the v$session view (columns sid and serial#).

SQL> show user
USER is "SYS"
SQL>

SQL> select * from v$session
where username = 'ABM_TEST';

SQL> select SID, SERIAL#, USERNAME, osuser, PROGRAM from v$session where username = 'ABM_TEST';

SID SERIAL# USERNAME OSUSER PROGRAM
-------- --------- --------------------- --------------------------- -------------
184 54 ABM_TEST rakesh.kumar sqlplus.exe

SQL> alter system kill session '184,54' immediate;
System altered.
SQL>

Auto start of cmd file after Machine Reboot

Yesterday, Systems department has restarted the machine after their activity, Databases restarted automatically but there are some other batch files & Authentication servers exe file which needs to be run for ERP connectivity. So I have configured for auto execution of batch files whenever the Machine is restarted, check the below steps.

run >
regedit
HKEY_LOCAL_MACHINE>
SOFTWARE>
MICROSOFT>
WINDOWS>
CURRENVERSION>
RUN> Then right click new>
string value >
assigned a name to file,
specified complete path of cmd file in double quotes.

DONE

RMAN - Recovery Catalog

I am trying to configure RMAN Catalog..
target db in linux = db name rock, db id 3304705878
recovery cat in xp = db name rock db id 3255117147 (rocklinux in tns entry for accessing Linux Dabaase)
Enter user-name: sys@rock as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

1. sql> create tablespace rman_ts
datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\rman_01.dbf' size 20m
autoextend on

2. create user rman1
identified by rman1
quota unlimited on rman_tsd
efault tablespace rman_ts;

3.SQL> grant recovery_catalog_owner to rman1;
Grant succeeded.
SQL> grant connect, resource to rman1;
Grant succeeded.

H:\>rman catalog rman1/rman1@rock log=D:\catalog.log
RMAN> create catalog tablespace rman_ts;
RMAN> exit

H:\>rman target sys/rock@rocklinux

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 22 12:49:23 2009

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

connected to target database: ROCK (DBID=3304705878)

RMAN> connect catalog rman1/rman1@rock

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

SQL> conn rman1/rman1@rock
Connected.
SQL> select * from rc_database;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 3304705878 ROCK 516893 20-APR-09

Oracle Tuning..

Oracle top down tuning tips
http://www.dba-oracle.com/art_tuning1.htm
Oracle Database Performance Tuning FAQs http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

when your query takes too long:
http://forums.oracle.com/forums/thread.jspa?threadID=501834


Oracle Tuning Tips
http://it.toolbox.com/blogs/all-about-dev/oracle-tuning-tips-8151
What's New in Oracle Performance? (10g)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/whatsnew.htm



AWR - Automatic Workload Repository

Learn to use the new feature that collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information When you have a database performance problem, what is the first thing you do to address it? One common approach is to see if a pattern exists: Answering questions such as "Is the same problem recurrent?", "Does it occur during a specific time period?", and "Is there a link between two problems?" will almost always lead to a better diagnosis.



As a DBA you probably have invested in a third-party or homegrown tool to collect elaborate statistics during database operation and derive performance metrics from them. In a crisis, you access those metrics for comparisons to the present. Replaying these past events can shed light on current problems, so continuously capturing relevant statistics becomes important for performance analysis. For some time, Oracle's solution in this area has been its built-in tool, Statspack. While it can prove invaluable in certain cases, it often lacks the robustness required by performance troubleshooting exercises. Oracle Database 10g offers a significant improvement: the Automatic Workload Repository (AWR). The AWR installs along with the database and captures not only statistics, but the derived metrics as well.

http://www.oracle.com/technology/pub/articles/10gdba/week6_10gdba.html


Database General - Oracle Performance Tuning – Part 1
http://www.databasejournal.com/features/oracle/article.php/3548291/Oracle-Performance-Tuning--Part-1.htm

Oracle 10.2 - (RMAN + SYS user Audit_Trail = XML, Extended) bug

After enabling auditing for sys users, as discussed in my below post.
http://rakeshocp.blogspot.com/2008/12/auditing-sys-user-in-10g.html
When we are trying to connect database with RMAN utility, it fails and gives below error.

H:\>rman
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 7 14:59:17 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target sys/rock@rock

RMAN-00571: =======================================================
RMAN-00569: ============= ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: =======================================================
ORA-09817: Write to audit file failed.
RMAN>

I got to know its bug in Oracle 10g Release 10.2.0.1.0. Check my post at oracle forums .
http://forums.oracle.com/forums/message.jspa?messageID=3282187#3282187

...

Undo tablespace issue....

There were some issues in HARD Diskk of Test Machine, and systems department wanted to format/replace the hard disk (F Drive, where Oracle is installed). So I closed the test database, and later systems departmentd copied all folders, including Oracle folder, where all datafiles, control files, redo logs are stored. They restored all folders after completing their activity.

After that the systems department wanted the Database team to verify whether the Oracle is working properly or not. When we started Oracle services, and Oracle Database, it was automatically shuting down the instance, after checking the alert.log file we got below error:

DEBUG: Replaying xcb 0x4e7d126c, pmd 0x4deeb4b8 for failed op 8
Errors in file f:\oracle\product\10.2.0\admin\icpora\udump\icpora_ora_4192.trc:
ORA-00600: internal error code, arguments: [4194], [49], [44], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [49], [44], [], [], [], [], []

Metalink says:

A mismatch has been detected between Redo records and rollback (Undo) records.

We are validating the Undo record number relating to the change being applied againstthe maximum undo record number recorded in the undo block.This error is reported when the validation fails.

ARGUMENTS:

Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block

IMPACT:PROCESS FAILUREPOSSIBLE ROLLBACK SEGMENT CORRUPTION

SUGGESTIONS:This error may indicate a rollback segment corruption.This may require a recovery from a database backup depending on the situation.

We deleted untobs01.dbf and created undotbs2 tablespace undotbs02.dbf

1. alter database datafile '/home/u01/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;
2. create undo tablespace undotbs2 datafile '/home/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 1024m;
3. ALTER SYSTEM SET undo_tablespace='UNDOTBS2'

The system started working properly, the Full Database export batch file runs in nights, when we checked the log of export file it indicated below error:

EXP-00008: ORACLE error 376 encountered
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ICPORA\UNDOTBS01.DBF'
EXP-00000: Export terminated unsuccessfully

when we checked the v$recover_file, data file 2 is not found for recovery. so when we issued the drop tablespace undotbs1 command, we got error

ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU01$’ found, terminate dropping tablespace

select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU01$ NEEDS RECOVERY UNDOTBS1
_SYSSMU02$ NEEDS RECOVERY UNDOTBS1
_SYSSMU03$ NEEDS RECOVERY UNDOTBS1
_SYSSMU04$ NEEDS RECOVERY UNDOTBS1
_SYSSMU05$ NEEDS RECOVERY UNDOTBS1
_SYSSMU06$ NEEDS RECOVERY UNDOTBS1
_SYSSMU07$ NEEDS RECOVERY UNDOTBS1
_SYSSMU08$ NEEDS RECOVERY UNDOTBS1

We closed the, database edited init.ora file and added parameters (comment undo_management=auto and entry for corrupted_rollback_segments
#undo_management=AUTO
undo_tablespace=UNDOTBS2
_corrupted_rollback_segments =('_SYSSMU01$','_SYSSMU02$','_SYSSMU03$','_SYSSMU04$','_SYSSMU05$','_SYSSMU06$','_SYSSMU07$','_SYSSMU08$'

1. STARTUP RESTRICT MOUNT pfile=C:\Oracle\init.ora
2. Drop rollback segment "_SYSSMU01$"
3. Droped all segments one by one that we entered in initfile. (step 2 drop command)
4. drop TABLESPACE UNDOTBS1;
5. shutdown
6. deleted entry of _corrupted_rollback_segments and removed comment from undo_management
7. STARTUP MOUNT pfile=C:\Oracle\init.ora8 and later opened the database.
8. Taken Full database export, it was successfully exported.

Speciall thanks to the writer/owner of below page, It helped me alot.
http://www.my-whiteboard.com/oracle-dba/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data-files.html