Routine knowledge of Oracle Database Maintenance

Routine knowledge of Oracle Database Maintenance-1. check the idle tablespace of the database. The CHUNKS column indicates the number of available idle blocks in the tablespace (each idle block is composed of several consecutive Oracle data blocks). If there are too many idle blocks, select tablespace_name, count (*) chunks, max (bytes/1024/1024) max_chunk from dba_free_space group by tablespace_name; -- the result of the MAX_CHUNK column is the maximum available block size on the tablespace -- The following SQL command joins adjacent fragments in the tablespace alter tablespace USERS coalesce; -- 2. view database connection status select sid, serial #, username, program, machine, status from v $ session;/* ID of SID session (session); SERIAL # Session serial number, which is used together with SID to uniquely identify a session. USERNAME indicates the USERNAME used to establish the session; tool used to connect the session to the database; STATUS indicates the current session STATUS, ACTIVE indicates that the session is executing some tasks, and INACTIVE indicates that the current session has not performed any operations; */-- 3. control file backup-the backup method is: Execute the SQL statement: alter database backup controlfile to '/home/backup/control. bak'; alter database backup controlfile to trace; -- 4. check the database file STATUS. The SQL statement used to check the STATUS of the data file is as follows:/* If the STATUS column of the data file is not AVAILABLE, take corresponding measures, for example, restore the data file or recreate the tablespace where the data file is located. */Select file_name, status from dba_data_files; -- 5. check the completion status of database scheduled jobs/* If the database uses Oracle jobs to complete some scheduled jobs, check the running status of these jobs: if the FAILURES column is a number greater than 0, the JOB fails to run and further checks are required. */Select job, log_user, last_date, failures from dba_jobs; -- 6. database Bad Block Processing/* Oracle records bad block information in the warning log file (alert_SID.log) when the Oracle database has bad blocks: ORA-01578: ORACLE data block was upted (file #7, block # <BLOCK>) ORA-01110: data file <AFN>: '/oracle1/oradata/V920/oradata/V816/users01.dbf' where, <AFN> indicates the absolute file number of the data file where the bad BLOCK is located. <BLOCK> indicates that the bad BLOCK is the first data BLOCK on the data file, check whether the Oracle database has bad blocks due to hardware and operating system faults. After the database is excluded, the database objects with bad blocks are processed. 1. determine the database objects with bad blocks */SELECT tablespace_name, segment_type, owner, segment_nameFROM dba_extents WHERE file_id = <AFN> AND <BLOCK> between block_id AND block_id + blocks-1; /* 2. determine the solution. If the object with bad blocks is an index, you can directly DROP the index and recreate it based on the record in the table; if records of tables with bad blocks can be generated based on records of other tables, you can directly DROP the table and recreate it. If there is a database backup, then, the database is restored. If there is no other way to restore the records in the table, the records on the bad blocks will be lost. You can only retrieve records from other data blocks in the table, then the table is rebuilt. 3. use the DBMS_REPAIR package provided by Oracle to mark Bad blocks */exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ('<schema>', '<tablename>'); -- 4. use the Create table as select command to save records from other blocks in the table to the create table corrupt_table_bak as select * from corrupt_table; -- 5. use the drop table command to delete a table with bad blocks: drop TABLE corrupt_table; -- 6. use the alter table rename command to restore the original alter table corrupt_table_bak rename to corrupt_table; -- 7. if an index exists on the table, you need to re-create the index -- 7. operating System Maintenance DBA should pay attention to the monitoring of the operating system: the space usage of the file system (Df-k). If necessary, clear Oracle warning logs and TRC files. If Oracle provides network services, check whether the network connection is normal check whether the resource usage of the operating system is normal check whether the database server has hardware faults, such as disk and memory errors.