Shulou(Shulou.com)06/01 Report--

-- = = query Oracle error log and warning log

View the error log directory through the command

SQL > show parameter background_dump_dest

The error log corresponding to the PID:3159 fuzzy query according to the error prompt

Find-name "* ora*3159*.trc"

More orcl_ora_3159.trc

Query archive log

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

-- force to abort an executing SQL statement

-- 1 query the executing sql statement

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 aborted according to sid and serial# kill needs

Select sql_text, b.sidsidforceb.serialkeeper, '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 query the currently locked object of oracle

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 imported data into the database ORA-01653: unable to extend table error, online check the reason is due to insufficient tablespace

Query tablespace usage statement

Select a.tablespacecharname.bytesUniverse 1024 total, (a.bytes-b.bytes) / 1024 Placement 1024 used, b.bytes/1024/1024 free, round ((a.bytes-b.bytes) / a.bytesUniverse 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

-- 2 the above statements can query the use of the tablespace. If you find that the tablespace is insufficient, check the location and file number of the data file.

View data file location

Select file#, name from v$datafile

-- 3 there are two ways to change the size of the tablespace

Modify data file size

Alter database datafile'/ usr/oracle/app/oradata/orcl/Test.dbf' resize 20480M; the modified size should be at least 50% larger than the actual size, preferably more than double the size

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

two。 Generate an Oracle RAC AWR report:

@ $ORACLE_HOME/rdbms/admin/awrgrpt.sql

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

@ $ORACLE_HOME/rdbms/admin/awrrpti.sql

4. The method of generating AWR reports for multiple database instances in an Oracle RAC environment:

@ $ORACLE_HOME/rdbms/admin/awrgrpti.sql

5. Generate an AWR report for the SQL statement:

@ $ORACLE_HOME/rdbms/admin/awrsqrpt.sql

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

@ $ORACLE_HOME/rdbms/admin/awrsqrpi.sql

-- generate AWR period comparison report

7. Generate a single instance AWR period comparison report

@ $ORACLE_HOME/rdbms/admin/awrddrpt.sql

9. Generate Oracle RAC AWR period comparison report

@ $ORACLE_HOME/rdbms/admin/awrgdrpt.sql

10. Generate AWR period comparison report for a specific database instance

@ $ORACLE_HOME/rdbms/admin/awrddrpi.sql

-- query the status of redo log files

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 logs

Show parameter background_dump_dest

-- query the sql statement being executed by Oracle and the user who executed the statement

SELECT b.sid oracleID

B.username login Oracle user name

B.serial#

Spid operating system ID

Paddr

SQL being executed by sql_text

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 distribution procedure of the initiator who is executing sql

Login identity of SELECT OSUSER computer

The program that PROGRAM initiates the request

User name of the USERNAME login system

SCHEMANAME

B.Cpu_Time spent the time of cpu

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

-- find out the current locked object of oracle

SELECT l.session_id sid

S.serial#

L.locked_mode lock mode

L.oracle_username login user

L.os_user_name login machine user name

S.machine machine name

S.terminal terminal user name

O.object_name locked object name

S.logon_time login time to the database

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 drops the current lock object can be

Alter system kill session 'sid, s. Serializations'

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

@ $ORACLE_HOME/rdbms/admin/awrgdrpi.sql

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.