Shulou(Shulou.com)05/31 Report--

This article introduces how Oracle database daily maintenance is, the content is very detailed, interested friends can refer to it, I hope it can help you.

DBAs should monitor database log and table space usage during Oracle database runs to detect problems early on.

Oracle Warning Log File Monitoring

Oracle records some of the database's health in the alert log file (alert_SID.log) during operation:

l database startup, shutdown, startup non-default parameters;

l Database redo log switching, recording the time of each switch, and if the switch cannot be performed because the checkpoint operation is not completed, it will record the reason why the switch cannot be performed;

Certain operations on the database, such as creating or deleting tablespaces, adding data files;

l Database errors such as insufficient table space, bad blocks, internal database errors (ORA-600)

DBAs should check the log files periodically and address problems identified in the logs in a timely manner

problem

processing

Start parameters incorrect

Check initialization parameter file

Redo logs cannot be switched because checkpoint or archive operations are not completed

If this happens frequently, consider adding redo log file groups; finding ways to make checkpoint or archive operations more efficient;

Someone deleted the table space without authorization

Check database security for passwords that are too simple; revoke system privileges for some users if necessary

Bad blocks appear

Check if it is a hardware problem (e.g. disk natively has bad blocks), if not, check which database object has bad blocks, rebuild this object

Not enough table space

Add data files to the appropriate table space

ORA-600 appears

Check the corresponding TRC file according to the contents of the log file. If it is an Oracle bug, put the corresponding patch in time.

DBAs should monitor database log and table space usage during Oracle database runs to detect problems early on.

Oracle Warning Log File Monitoring

Oracle records some of the database's health in the alert log file (alert_SID.log) during operation:

l database startup, shutdown, startup non-default parameters;

l Database redo log switching, recording the time of each switch, and if the switch cannot be performed because the checkpoint operation is not completed, it will record the reason why the switch cannot be performed;

Certain operations on the database, such as creating or deleting tablespaces, adding data files;

l Database errors such as insufficient table space, bad blocks, internal database errors (ORA-600)

DBAs should check the log files periodically and address issues identified in the logs in a timely manner

problem

processing

Start parameters incorrect

Check initialization parameter file

Redo logs cannot be switched because checkpoint or archive operations are not completed

If this happens frequently, consider adding redo log file groups; finding ways to make checkpoint or archive operations more efficient;

Someone deleted the table space without authorization

Check database security for passwords that are too simple; revoke system privileges for some users if necessary

Bad blocks appear

Check if it is a hardware problem (e.g. disk natively has bad blocks), if not, check which database object has bad blocks, rebuild this object

Not enough table space

Add data files to the appropriate table space

ORA-600 appears

Check the corresponding TRC file according to the contents of the log file. If it is an Oracle bug, put the corresponding patch in time.

II. Database Tablespace Usage Monitoring (Dictionary Management Tablespace)

After the database has been running for a period of time, due to the continuous creation and deletion of objects in the table space, a large number of fragments will be generated in the table space. DBAs should know the fragmentation and available space of the table space in time to decide whether to defragment or add data files to the table space.

select tablespace_name, count(*) chunks , max(bytes/1024/1024) max_chunk from dba_free_space group by tablespace_name;

The SQL above lists the free blocks for each table space in the database, as follows:

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

The CHUNKS column indicates how many free blocks are available in the table space (each free block is composed of some consecutive Oracle data blocks). If there are too many such free blocks, for example, more than 100 free blocks per data file on average, the fragmentation of the table space is serious. You can try to join adjacent fragments of the table space with the following SQL command:

alter tablespace tablespace name coalesce;

Then execute the SQL statement that looks at the table space fragmentation to see if the table space fragmentation has decreased. If that doesn't work, and the fragmentation of the table space has seriously affected the operation of the database, consider rebuilding the table space.

The MAX_CHUNK column results in the largest available block size in the table space. If the space (NEXT value) required for the objects in the table space is larger than the available block size, error messages ORA-1652, ORA-1653, and ORA-1654 will be prompted. The DBA should expand the space in the table space in time to avoid these errors.

Expanding Tablespaces Expands the size of data files in tablespaces, or adds data files to tablespaces, as described in the Storage Management section.

Third, check the database connection

DBAs regularly check the connection to the database to see if the number of sessions established with the database is normal. If too many connections are established, the database resources will be consumed. At the same time, some "dead" connections may need to be cleaned manually by DBAs.

The following SQL statement lists the sessions established for the current database:

select sid,serial#,username,program,machine,status from v$session;

The output is:

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

Among them,

SID session ID number;

SERIAL#The serial number of the session, which together with the SID uniquely identifies a session;

USERNAME The username with which the session was established;

PROGRAM What tools are used to connect this session to the database;

STATUS The status of the current session, ACTIVE indicates that the session is performing some tasks, INACTIVE indicates that the current session is not performing any operations;

If the DBA wants to manually disconnect a session, execute:

alter system kill session 'SID,SERIAL#';

Note that the sessions with SIDs 1 through 7(USERNAME column is empty) in the example above are Oracle background processes and do not do anything with these sessions.

IV. Backup of control files

When the database structure changes, such as adding table spaces, adding data files or redo log files, these operations will cause changes in Oracle database control files. DBAs should backup the control files as soon as possible. The backup methods are:

Execute SQL statement: alter database backup controlfile to '/home/backup/control.bak';

or: alter database backup controlfile to trace;

This generates SQL commands to create control files in the USER_DUMP_DEST directory (specified in the initialization parameter file).

5. Check the status of database files

DBA to timely view the status of the data file in the database (such as being deleted by mistake), according to the actual situation to decide how to deal with, check the status of the data file SQL is as follows:

select file_name,status from dba_data_files;

If the STATUS column of the data file is not AVAILABLE, then appropriate measures should be taken, such as restoring the data file or rebuilding the table space in which the data file is located.

VI. Check the completion of scheduled database operations

If the database uses Oracle JOBs to complete some scheduled jobs, check the health of these JOBs:

select job,log_user,last_date,failures from dba_jobs;

If the FAILURES column is a number greater than 0, JOB failed to run, and further checks are required.

7. Dealing with bad database blocks

When a bad block occurs in an Oracle database, Oracle records information about the bad block in the alert log file (alert_SID.log):

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

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

where, represents the absolute file number of the data file where the bad block is located, and represents the number of data blocks on the data file where the bad block is located.

When this occurs, you should first check whether a hardware and operating system failure is causing the bad block in the Oracle database. After eliminating the causes outside the database, the database objects with bad blocks are processed.

1. Identify database objects where bad blocks occur

SELECT tablespace_name,segment_type,owner,segment_name

FROM dba_extents WHERE file_id =

AND between block_id AND block_id+blocks-1;

2. Decide how to repair

If the object of the bad block is an index, you can directly DROP the index and rebuild it according to the records in the table.

If the records of the table with bad blocks can be generated according to the records of other tables, then the table can be directly DROP and rebuilt;

If there is a backup of the database, restore the database method to repair;

If there is no other way to recover the records in the table, then the records on the bad block are lost, and the records on the other data blocks in the table can only be taken out, and then the table is rebuilt.

3. Mark bad blocks with the DBMS_REPAIR package provided by Oracle

exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('','');

4. Use the Create table as select command to save records from other blocks in a table to another table

create table corrupt_table_bak as select * from corrupt_table;

5. DROP TABLE to delete tables with bad blocks

drop table corrupt_table;

6. Restore the original table with the alter table rename command

alter table corrupt_table_bak rename to corrupt_table;

7. If an index exists on the table, rebuild the index on the table

VIII. Maintenance related to operating system

DBAs should pay attention to the monitoring of the operating system:

l File system space usage (df -k), cleaning Oracle warning logs and TRC files if necessary

l If Oracle provides network services, check whether the network connection is normal

l Check if the operating system resource usage is normal

l Check whether there is any hardware failure in the database server, such as disk and memory error.

About Oracle database daily maintenance is how to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.