The following steps explain what needs to take place in order to recover from a crash where a DB control file gets corrupted:

The likely error thrown in this scenario is as follow:

Fri Mar 7 22:00:46 2008
Hex dump of (file 0, block 1) in trace file /u01/app/oracle/admin/teng/bdump/teng_j001_9438.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad check value found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00001501
check value in block header: 0xf8de
computed block checksum: 0x1501

Problem Description:
====================

The OS crashed and at the time two instances were running.

After correcting the OS issue then you try to start the instance(s)
and upon mounting the database you receive the following errors:

ORA-00227: corrupt block detected in controlfile: (block %s, # blocks %s)
Cause: A block header corruption or checksum error was detected on
reading the controlfile.
Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP
CONTROLFILE command.

ORA-00202: control file:
Cause: This message reports the name of the file involved in other
messages.
Action: See the associated messages for a description of the problem.

Problem Explanation:
====================

Control file is corrupt.

Solution Description:
=====================

Try to use one of the mirrored copies of the control file. You should have
mirrored images of your control file. Oracle recommends at least 2 mirrored
copies. Their locations should be listed in your “init.ora” file.

Try using one of these mirrored control files to enable you to bring up the
database by copying one of them over the bad one.

Or

Comment out the bad control file from the “control_files” parameter in your
“init.ora” file and try bringing up the database with one of your mirrored
control files.

If the above doesn’t work, then all control files are corrupt and you must
create a new control file.

Take a backup of the control files from the last backup and follow steps
below to recreate a good control file.

===========
OPTION ONE
===========

1. The alert log should identify which control file it is trying to use.

See error ora-202.
> Note this (control1).

2. Make a backup of control files as they are now.

cp controlfiles controlfile.bk

3. Take one of the other control files and copy over the bad control file
noted above.

If this does not work, then proceed to the next step.

==============
OPTION TWO
==============

Comment the bad control file from the control_files parameter.

In the init.ora:

control_file = comment out the one listed in alert log from ora-202.

Try starting the instance.

If you receive the same errors as previously with the name of the control file
changed to controlfile2, then go to the next option.

=============
OPTION THREE
=============

Issue:
1. Shutdown immediate.

2. Startup nomount pfile= .

3. Alter database mount;

4. Alter database backup controlfile to trace;

5. Shutdown.

6. Check for trace file:

Either in user_dump_dest = trace file with latest time stamp; nothing
or background_dump_dest.

The file name should be: ora
.trc.

This should contain a script to recreate the control file.

7. Save file as control.sql.

8. Edit file to start with: startup nomount.

9. Check Oracle_sid : should be set to correct sid.

10. Svrmgrl> connect internal

11. @control.sql

You will reeceive a message: Stmt processed.

waiting on svrmgrl prompt

12. Svrmgrl media recovery complete

svrmgrl> select sysdate from dual; june 4, 1999

Database is up and open.

13. Select on main application table:

sqlplus : connected ok.

Solution Explanation:
=====================

If all the control files are corrupt, then you will need to create a new control
file to be able to bring up your database.

Database Link From Oracle to SQL Server

After you succeed connecting Oracle with SQL Server probably you want to create database link between it. Oracle heterogeneous services allow you to define a database link between Oracle and SQL Server, as well as links to DB2 and other inferior databases.

Here are complete notes in creating a database link between Oracle and SQL Server.

1. Install Oracle ODBC drivers on the server (your local Oracle database) that will access the remote SQL Server database using the database link.
2. Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.
4. Ensure that your global_names parameter is set to False.
5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

6. Modify the Listener.ora file.


SID_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells Oracle to use heterogeneous services.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=Cas30C) -- Enter the DSN on this line
(ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
(PROGRAM = hsodbc) ) )




7. Modify the Tnsnames.ora file. This is the Oracle database installation accessed by the users to connect to the ODBC drivers


(DESCRIPTION=
(ADDRESS_LIST=
(Address=(PROTOCOL=TCP)
(HOST=
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services
)



8. Reload the listener on local Oracle database
9. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.
10. Run a SQL Server Select statement from the Oracle installation using the database link.

ORA-39095: Dump file space has been exhausted

ORA-39095: Dump file space has been exhausted

Encountered this error when taking dump backup which exceeds more than 99 files
$ORACLE_HOME/bin/expdp dcs_dev/*****@fdbbp directory=dcsdevdir dumpfile=dcsdev_$(date +"%d-%m-%Y" )_fdbbp%U.dmp logfile=dcsdev_$(date +"%d-%m-%Y")_fdbbp.log filesize=600M


Solution : 1
We can up-size the filesize, to bring down the number of files generated
eg
$ORACLE_HOME/bin/expdp dcs_dev/******@fdbbp directory=dcsdevdir dumpfile=dcsdev_$(date +"%d-%m-%Y" )_fdbbp%U.dmp logfile=dcsdev_$(date +"%d-%m-%Y")_fdbbp.log filesize=1024M

Each Schema's Used Space

To check space used by each user schema, you can use the following Query.

/* Space used by each Schema */
select substr(owner,1,16) owner, substr(tablespace_name,1,26) tablespace_name,substr(segment_type,1,10) segment_type,sum(bytes/(1024*1024)) Mbytes,
sum(blocks) blocks from sys.dba_extents
where owner not in ('SYS','SYSTEM','SYSADMIN','SYSMAN','SH','DBSNMP','CTXSYS','EXFSYS','WMSYS','XDB','TSMSYS','MDSYS','OLAPSYS','OUTLN',
'ORDSYS','PM','OE','SCOTT','IX','HR','DMSYS')
group by substr(owner,1,16),substr(tablespace_name,1,26),substr(segment_type,1,10)
order by 1,2,3

Listener Password

The oracle listener can be the object of attacks from someone willing to take control of the database.

Putting a password on the listener is easy.

C:\>LSNRCTL

LSNRCTL>status

If you got some instances described then this is your current listener. The default name is listener and a potential hacker is well aware of that.

It’s good to create the listener with a different name.

If you don’t know your listener’s name, go to the services and look for OracleListenerSomelistenername. Your listener name is Somelistenername.

LSNRCTL>change_password Somelistenername

old password: There was nothing so we just press enter.

new password: YourNewPassword

retype new password: YourNewPassword

LSNRCTL>save_config

The save_config command will add the password encrypted in the listener.ora file located in ORACLE_HOME\NETWORK\Admin

If that command haven’t been performed then no password have been added.

How to check expdp progress

V$SESSION_LONGOPS :This view helps determine Data Pump export progress indicator through the MESSAGE column.

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,
t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT'

Database Hanging due to Row lock contention

To check for the sessions waiting, use the query

select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle';

To check for the corresponding sql statement relating the waiting session

select sid, sql_text
from v$session s, v$sql q
where sid in (254,270)
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);


Wait event Class
select wait_class_id, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = 270;

From the above query find the highest wait class and find the cause of the wait by using the query below.

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = xyz; -- Where xyz is the highest wait_class_id from the previous query