Oracle日常维护 - 共4页

Oracle

Oracle

Oracle DBA

Oracle

Oracle (alert_SID.log)

DBA

( )

ORA-600

DBA select tablespace_name,count(*) chunks ,max(bytes/1024/1024) max_chunk from dba_free_space

group by tablespace_name;

SQL ,

TABLESPACE_NAME CHUNKS MAX_CHUNK

-------------------- ---------- ----------

INDX 1 57.9921875

RBS 3 490.992188

RMAN_TS 1 16.515625

SYSTEM 1 207.296875

TEMP 20 70.8046875

TOOLS 1 11.8359375

USERS 67 71.3671875

CHUNKS ( Oracle ) TRC Oracle bug (checkpoint) (ORA 600)

Oracle

100 SQL

alter tablespace coalesce;

SQL

MAX_CHUNK (NEXT ) ORA-1652 ORA-1653 ORA-1654 DBA

“ ”

DBA DBA

SQL

select sid,serial#,username,program,machine,status

from v$session;

SID SERIAL# USERNAME PROGRAM MACHINE STATUS

---- ------- ---------- ----------- --------------- --------

1 1 ORACLE.EXE WORK3 ACTIVE

2 1 ORACLE.EXE WORK3 ACTIVE

3 1 ORACLE.EXE WORK3 ACTIVE

4 1 ORACLE.EXE WORK3 ACTIVE

5 3 ORACLE.EXE WORK3 ACTIVE

6 1 ORACLE.EXE WORK3 ACTIVE

7 1 ORACLE.EXE WORK3 ACTIVE

8 27 SYS SQLPLUS.EXE WORKGROUP\WORK3 ACTIVE

11 5 DBSNMP dbsnmp.exe WORKGROUP\WORK3 INACTIVE

SID

SERIAL#

USERNAME

PROGRAM

STATUS (session) ID SID ACTIVE INACTIVE

DBA

alter system kill session 'SID,SERIAL#';

SID 1 7(USERNAME ) Oracle

Oracle DBA

SQL

alter database

backup controlfile to '/home/backup/control.bak';

Oracle

alter database

backup controlfile to trace;

USER_DUMP_DEST( ) SQL

DBA SQL

select file_name,status

from dba_data_files;

STATUS AVAILABLE

Oracle JOB JOB

select job,log_user,last_date,failures

from dba_jobs;

FAILURES 0 JOB

Oracle Oracle alert_SID.log

ORA-01578: ORACLE data block corrupted (file # 7, block # <BLOCK>)

ORA-01110: data file <AFN>: '/oracle1/oradata/V920/oradata/V816/users01.dbf'

AFN <BLOCK> Oracle

1

SELECT tablespace_name,segment_type,owner,segment_name

FROM dba_extents

WHERE file_id = <AFN>

AND <BLOCK> between block_id AND block_id+blocks-1;

2

DROP DROP

3 Oracle DBMS_REPAIR

exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('<schema>','<tablename>');

4 Create table as select

create table corrupt_table_bak

as

select * from corrupt_table;

5 DROP TABLE

drop table corrupt_table;

6 alter table rename

alter table corrupt_table_bak

rename to corrupt_table;

Oracle

7

DBA

df -k Oracle TRC Oracle

本文来源：https://www.bwwdw.com/article/g0p1.html