Oracle 11g R2 FAQ Processing

--====================== Querying Oracle error logs and warning logs

To view the error log directory by command
Sql> Show parameter background_dump_dest;
Error log corresponding to the pid:3159 fuzzy query based on the error prompt
Find-name "*ORA*3159*.TRC"
More ORCL_ORA_3159.TRC

Querying archive logs
Sql> select * from V$recovery_file_dest;

Delete Archive Log
$ rman
Rman> Connect Target Sys/xxxxx
Rman> crosscheck Archivelog All;
rman> Delete ARCHIVELOG all completed before ' SYSDATE-7 ';

Query Warning Log
$ find-name "Alert*.log"
$ more alert_orcl.log;

--====================== forcing abort of executing SQL statement

--1 querying the SQL statement being executed
Select B.sid, B.username, b.serial#, A.spid, B.paddr, C.sql_text, B.machine
From V$process A, v$session B, V$sqlarea c
where a.addr = B.paddr
and b.sql_hash_value = C.hash_value;

--2 SQL statements that need to be aborted based on Sid and Serial# kill
Select Sql_text, b.sid,b.serial#, ' alter system kill session ' | | b.sid| | ', ' | | b.serial#| | '; ', b.username
From V$process A, v$session B, V$sqlarea c
where a.addr = B.paddr
and B.sql_hash_value = C.hash_value

--3 querying Oracle currently locked object
Select a.session_id, c.serial#, A.locked_mode, A.oracle_username, A.os_user_name, C.machine,
C.terminal, B.object_name, C.logon_time
From V$locked_object A, all_objects B, v$session c
where a.object_id = b.object_id and a.session_id = C.sid
Order by C.sid, c.serial#;

Problems caused by insufficient--====================== table space and their solutions

--1 Import data to the database times the ora-01653:unable to extend table error, online Check the following reason is due to lack of table space;

Querying table space Usage statements

Select a.tablespace_name,a.bytes/1024/1024 Total, (a.bytes-b.bytes)/1024/1024 used, b.bytes/1024/1024 free, round (( a.bytes-b.bytes)/a.bytes*100,2) used_rate
From
(
Select tablespace_name,sum (bytes) bytes
From Dba_data_files
GROUP BY Tablespace_name
A
(
Select Tablespace_name,sum (bytes) Bytes,max (bytes) Largest
From Dba_free_space
GROUP BY Tablespace_name
) b
where a.tablespace_name = B.tablespace_name;

--More than 2 statements can query out the table space usage, if you find that the tablespace capacity is insufficient, view the location of the data file file and the file number

View Data File Locations
Select file#, name from V$datafile;

--3 There are two ways to modify the tablespace size

Modify data File Size
ALTER DATABASE datafile '/USR/ORACLE/APP/ORADATA/ORCL/TEST.DBF ' resize 20480M; The modified size is greater than the actual size of at least 50%, preferably one more than

Add data files
Alter tablespace XXX add datafile '/home/oracle/data/xxx_1.dbf ' size 1024M;

1. Generate a single-instance AWR report:

@ $ORACLE _home/rdbms/admin/awrrpt.sql

2. Generate the Oracle RAC AWR report:

@ $ORACLE _home/rdbms/admin/awrgrpt.sql

3. Generate an AWR report for a specific DB instance in the RAC environment:

@ $ORACLE _home/rdbms/admin/awrrpti.sql

4. How to generate AWR reports for multiple DB instances in an Oracle RAC environment:

@ $ORACLE _home/rdbms/admin/awrgrpti.sql

5. Generate the AWR report for the SQL statement:

@ $ORACLE _home/rdbms/admin/awrsqrpt.sql

6. Generate an AWR report for an SQL statement on a specific DB instance:

@ $ORACLE _home/rdbms/admin/awrsqrpi.sql

--Generate AWR Time Comparison report

7. Generate Single instance AWR time comparison report

@ $ORACLE _home/rdbms/admin/awrddrpt.sql

9. Generate Oracle RAC AWR time Comparison report

@ $ORACLE _home/rdbms/admin/awrgdrpt.sql

10. Generate the AWR time comparison report for a specific DB instance

@ $ORACLE _home/rdbms/admin/awrddrpi.sql

--Query redo log file status

SELECT * from V$log;
SELECT * from V$logfile;


--Query the archive log directory
Select Open_mode from V$database;
Archive log list;
Show parameter log_archive_dest;


--Query Alert log
Show parameter background_dump_dest;



--Query the SQL statement that Oracle is executing and the user who executes the statement

SELECT B.sid Oracleid,
B.username log in to the Oracle user name,
b.serial#,
SPID operating system ID,
PADDR,
Sql_text the SQL that is being executed,
B.machine Computer Name
From V$process A, v$session B, V$sqlarea c
WHERE a.addr = b.paddr
and B.sql_hash_value = C.hash_value

--View the issuer's release procedure for executing SQL

SELECT osuser Computer Login status,
program that initiated the request,
USERNAME user name of the login system,
SCHEMANAME,
B.cpu_time spends Cpu time,
STATUS,
SQL executed by B.sql_text
From V$session A
Left JOIN v$sql B on a.sql_address = b.address
and A.sql_hash_value = B.hash_value
ORDER by B.cpu_time DESC

--Identify Oracle's current locked object

SELECT l.session_id SID,
s.serial#,
L.locked_mode lock mode,
L.oracle_username logged in user,
L.os_user_name Login Machine User name,
S.machine machine Name,
S.terminal End user Name,
O.object_name is locked object name,
S.logon_time Logon Database time
From V$locked_object L, all_objects O, v$session s
WHERE l.object_id = o.object_id
and l.session_id = S.sid
ORDER by Sid, S.serial#;

--kill drop the current lock object can be

Alter system kill session ' Sid, S.serial# ';

11. Generate AWR Time comparison report for specific (multiple) DB instances in an Oracle RAC environment

@ $ORACLE _home/rdbms/admin/awrgdrpi.sql

Oracle 11g R2 FAQ Processing