Oracle on Linux/Unix questions

20. How would you begin to troubleshoot an ORA-3113 error?

ORA-03113 –> An unexpected end-of-file was processed on the communication channel. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down. This message could occur when any of the following commands have been issued:

ALTER SYSTEM KILL SESSION … IMMEDIATE

ALTER SYSTEM DISCONNECT SESSION … IMMEDIATE

SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL

If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of Oracle Support Services.

What would you use to improve performance on an insert statement that places millions of rows into that table?

1.Disable the constraint.
2.Drop the non-unique indexes.
3.Set Undo tablespace properly.
4.Create a big redo log groups.

Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.

I think it looks like Archive log destination is full.

1.If you have seperate backup script for archive log please use that.
2.Or move some archive log fils to diffrent location to get free space.
3.Try to connect the DB now.

How would you extract DDL of a table without using a GUI tool?

Use DBMS_METADATA package and use GET_DDL procedure.

Padmanathan

Which dictionary tables and/or views would you look at to diagnose a locking issue?

1.v$locked_object
2.v$lock

2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?

issue LIST BACKUP BY FILE; and verify under the ‘List of Archived Log Backups’ for that particular file.

How would you go about verifying the network name that the local_listener is currently using?

LSNRCTL> show current_listener
Current Listener is LISTENER
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain.net)(PORT=1521)))
LISTENER parameter “log_status” set to ON
The command completed successfully
LSNRCTL>

How do you tell what your machine name is and what is its IP address?

hostname

host -T hostname

How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?

00 14 */2 * * /test/test.sh > out.log 2>&1

this logs the process executed in test.sh to out.log even if the process errors out.

What’s the benefit of “dbms_stats” over “analyze”?

The answer posted is wrong.
analyze can be used to gather statistics for tables and indexes as well

analyze table T compute statistics

analyze index i compute statistics

Here are the main differences:

with dbms_stats you can export/import stats

with dbms_stats you can gather system stats

with dbms_stats you can set stats

with dbms_stats you can gather statistics of STALE tables or indexes

with dbms_stats you can gather local partition staistics (more granularity)

with dbms_stats you can gather statistics for external tables

Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.

declare
jno number;
begin
dbms_job.submit(JNo, ’scott.procedure_name;’, TRUNC(SYSDATE+2)+9/24, ‘TRUNC(SYSDATE+1)+14/24′);
dbms_output.put_line(jno);
end;
/

What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?

Answer:
optimizer_index_cost_adj parameter can be set to help CBO to decide an execution plan, which effects the speed of SQL query.
we can also make necessary changes to the following parameters to effect CBO performance:
optimizer_search_limit & optimizer_max_permutations

An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue?

Answer:

First try to run the job manually by executing

exec dbms_job.run(jobno);

it certainly fires message but most of the times it wll not refer to the actual problem. In this case try to run the actual procedure scheduled for this job (contents of “WHAT” filed for this job no in dbms_job table)

exec procedure_name;

This will give you the exact the failure message. Once u come up with the solution for the problem try fix this back to job que by executing the job once manually by

exec dbms_job.run(jobno);

Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.

Answer:

If the database allows u to identify the corrupted blcoks and are happened to be less in no then u can go for blcok wise recovery using RMAN.
other wise go for complete recovery.

select sysdate from dual;
select to_char(sysdate,’DD-MM-YYYY HH24:MM:SS’) from dual;
drop table table_name;
select sysdate from dual;
exit;
EOF

Which environment variables are absolutely critical in order to run
the OUI?
Answer:
ORACLE_HOME, ORACLE_SID,ORACLE_BASE,PATH & LD_LIBRARY_PATH

Why does Oracle not permit the use of PCTUSED with indexes

This is because an index is a complex data structure, not randomly organized like a heap table ( a normal table u get when u type create table syntax) Data must go where it ‘belongs’. Unlike a heap where blocks are sometimes available for inserts, blocks are always available for new entries in an index. If the data belongs on a given block because of its values, it will go there regardless of how full or empty the block is.

PCTFREE will reserve space on a newly created index, but not for subsequent operations on it for much the same reason as why
PCTUSED is not used at all.
The same thigh is true even for index organized tables too…

What are the commands you’d issue to show the explain plan for “select * from dual”?

Answer:

set autotrace traceonly explain;
select * from dual;