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

creating new database from old database cold backup.

I am writing below steps that will create a new database ICPORA that will be a complete replica of old database ICPORA.

Scenario: I have new database ROCK and old database ICPORA, I have cold backup of ICPORA, and Now I want recreate ICPORA at ROCK database. I have taken backup of both database.

1. Copy all datafiles, controlfiles, redologs, archivelogs, init files from ICPORA to new database ROCK (oradata). Delete all files from ROCK database after backup and paste ICPORA files.

2. Check init.ora file and modify/specify the correct paths for ArchiveLog, Controlfiles and other files.

3. If you have new database with the old DB name then go to step 5 otherwise step 4. In my situation I have new database with name ROCK, so I need ICPORA service/instance to operate, for that I need to create new service ICPORA.

4. create new service with old database name with ORADIM utility.

H:\>oradim -new -sid icpora -intpwd icpora -startmode m -pfile 'D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA'

Instance created.

5.
H:\>set oracle_sid=icpora

H:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 18 15:19:02 2009
Copyright (c) 1982

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes

SQL> startup mount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\DATAFILES\SYSTEM01.DBF'

Note: The above error is encountered because the contents/information stored in the control file is different from the current situation. (i.e paths of files). We need to recreate the controlfile, the below command will generate a script for recreating a controlfile in udump folder.

SQL> alter database backup controlfile to trace;
Database altered.

6. Copy the important contents of script generated in udump folder and modify with new paths and execute it. Note execute in NOMOUNT Mode.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ICPORA" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 18688
7 LOGFILE
8 GROUP 1 'D:\oracle\product\10.2.0\oradata\icpora\REDO01.LOG' SIZE 100M,
9 GROUP 2 'D:\oracle\product\10.2.0\oradata\icpora\REDO02.LOG' SIZE 100M,
10 GROUP 3 'D:\oracle\product\10.2.0\oradata\icpora\REDO03.LOG' SIZE 100M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\oracle\product\10.2.0\oradata\icpora\SYSTEM01.DBF',
14 'D:\oracle\product\10.2.0\oradata\icpora\SYSTEM02.DBF',
15 'D:\oracle\product\10.2.0\oradata\icpora\SYSAUX01.DBF',
16 'D:\oracle\product\10.2.0\oradata\icpora\ABAMCO01.DBF',
17 'D:\oracle\product\10.2.0\oradata\icpora\ABAMCO02.DBF',
18 'D:\oracle\product\10.2.0\oradata\icpora\EDP01.DBF',
19 'D:\oracle\product\10.2.0\oradata\icpora\UNDOTBS2.DBF',
20 'D:\oracle\product\10.2.0\oradata\icpora\USERS01.DBF',
21 'D:\oracle\product\10.2.0\oradata\icpora\AXIS_ALERT01.DBF',
22 'D:\oracle\product\10.2.0\oradata\icpora\RISKMETER01.DBF'
23 CHARACTER SET WE8MSWIN1252
24 ;

Control file created.

SQL> RECOVER DATABASE
Media recovery complete.
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> alter database open;
Database altered.