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

This article introduces the relevant knowledge of "what is the daily inspection of Oracle database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Check the basic status of the database

Including: check the status of Oracle instance, check the Oracle service process, check the Oracle listening process, a total of three parts.

1.1. Check the status of the Oracle instance

Select instance_name,host_name,startup_time,status,database_status from v$instance

Where "STATUS" represents the current instance state of Oracle and must be "OPEN"; "DATABASE_STATUS" represents the status of Oracle's current database and must be "ACTIVE".

1.2. Check Oracle online log status

Select group#,status,type,member from v$logfile

The output should have more than 3 records (including 3), and "STATUS" should be non-"INVALID", not "DELETED". Note: the display of "STATUS" is empty to indicate normal.

1.3. Check the status of the Oracle tablespace

Select tablespace_name,status from dba_tablespaces

The STATUS in the output should all be ONLINE.

1.4. Check the status of all data files in Oracle

Select name,status from v$datafile

The "STATUS" in the output should all be "ONLINE". Or:

Select file_name,status from dba_data_files

The "STATUS" in the output should all be "AVAILABLE".

1.5. Check for invalid objects

Select owner,object_name,object_type from dba_objects where statusholders / VALIDs / and ownerships / sys' and ownerships / systems

If a record is returned, there is an invalid object. If these objects are related to the application, you need to recompile and generate the object, or:

SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID'

1.6. Check the status of all rollback segments

Select segment_name,status from dba_rollback_segs

The "STATUS" of all rollback segments in the output should be "ONLINE".

Back to the top.

two。 Check the usage of Oracle-related resources

Contains:

a. Check the relevant parameter values in the Oracle initialization file

b. Check the database connection, check the system disk space

c. Check the usage of each tablespace in Oracle and check some objects with extended exceptions

d. Check the contents of the system table space, check the next extension of the object and the maximum extension value of the table space, a total of seven parts.

2.1. Check the relevant parameter values in the Oracle initialization file

Select resource_name,max_utilization,initial_allocation, limit_valuefrom v$resource_limit

If LIMIT_VALU-MAX_UTILIZATION= 95order By Percent

If a record is returned, the extension of these objects has almost reached the maximum extension value when it was defined. For these objects, modify its storage structure parameters.

2.6. Check the contents of the system tablespace

Select distinct (owner) from dba_tableswhere tablespace_name = 'SYSTEM'and owner! =' SYS'and owner! = 'SYSTEM'unionselect distinct (owner) from dba_indexeswhere tablespace_name =' SYSTEM'and owner! = 'SYS'and owner! =' SYSTEM'

If the record returns, it indicates that there are some objects in the system table space that are not system and sys users. We should further check whether these objects are relevant to our application. If relevant, move these objects to non-System tablespaces, and check the default tablespace values of the owners of these objects.

2.7. Check the next extension of the object and the maximum extension of the tablespace

Select a.table_name, a.next_extent, a.tablespace_namefrom all_tables a, (select tablespace_name, max (bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunkunionselect a.index_name, a.next_extent, a.tablespace_namefrom all_indexes a, (select tablespace_name Max (bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk

If a record is returned, the next extension of these objects is greater than the maximum extension of the tablespace to which the object belongs, and the storage parameters of the corresponding tablespace need to be adjusted.

Back to the top.

3. Check the backup results of Oracle database

Contains:

a. Check database backup log information

b. Check the time when files in the backup volume are generated

c. Check the email of oracle users

3.1. Check database backup log information

Suppose: the temporary directory of the backup is / backup/hotbakup, and we need to check the backup results on July 22nd, 2009, then check it with the following command:

Cat / backup/hotbackup/hotbackup-09-7-22.log | grep-I error

The log file for the backup script is hotbackup- month-date-year .log, under the temporary directory of the backup. If "ERROR:" exists in the file, the backup is not successful and there is a problem that needs to be checked.

3.2. Check the time when files in the backup volume are generated

# ls-lt / backup/hotbackup

The backup volume is the temporary directory for backup, and the date of viewing the files in the output should be generated by the hot backup script in the early hours of the day. If the time is not correct, the hot backup script did not execute successfully.

3.3. Check the email of oracle users

# tail-n 300 / var/mail/oracle

The hot backup script is executed through the cron of the Oracle user. After the cron is executed, the operating system sends an Email informing the Oracle user that the task has been completed. Check the Oracle email early this morning to see if there are any error messages such as ORA-,Error,Failed. If so, it indicates that the backup is abnormal.

Back to the top.

4. Check Oracle database performance

The main purpose of this section is to check the performance of Oracle database, including: check database waiting events, check deadlock and processing, check cpu, Ibank O, memory performance, see if there are dead processes, check row link / migration, do regular statistical analysis, check buffer hit rate, check shared pool hit rate, check sort area, check log buffer, a total of ten parts.

4.1. Check the database for wait events

Set pages 80set lines 120col event for a40select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like' rdbms%'

If the database continues to have a large number of waiting events such as latch free,enqueue,buffer busy waits,db file sequential read,db file scattered read for a long time, it needs to be analyzed, and there may be problematic statements.

4.2. The acquisition of the highest SQL statement in Disk Read

SELECT SQL_TEXTFROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM 'CTAIS2',cascade = > TRUE,degree = > 4)

5.8 check buffer hit ratio

SELECT a.VALUE + b.VALUE logical_reads, c.VALUE phys_reads, round (1-c.value / (a.value + b.value)), 4) hit_ratio FROM v$sysstat a, v$sysstat b, v$sysstat c WHERE a.NAME ='db block gets' AND b.NAME = 'consistent gets' AND c.NAME =' physical reads'

If the hit rate is less than 90%, you need to increase the database parameter db_cache_size.

5.9 check the hit rate of shared pool

Select sum (pinhits) / sum (pins) * 100 from v$librarycache

If it is less than 95%, you need to adjust the binding variables used by the application, or resize the database parameter shared pool.

5.10 check the sort area

Select name,value from v$sysstat where name like'% sort%'

If the proportion of disk/ (memoty+row) is too high, you need to adjust sort_area_size (workarea_size_policy=false) or pga_aggregate_target (workarea_size_policy=true).

5.11 check the log buffer

Select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries')

If the redo buffer allocation retries/redo entries exceeds 1%, you need to increase the log_buffer.

Back to the top.

6. Check database security

In this section, we mainly check the security of the Oracle database, including: check the system security information, change the password regularly, a total of two parts.

6.1. Check system security log information

The directory of the system security log file is under / var/log, which mainly checks the user log information for successful or failed login.

Check the log of successful login:

[root@rac2] # grep-I accepted / var/log/secure

Jan 8 08:44:43 rac2 sshd [29559]: Accepted password for root from:: ffff:10.10.10.6 port 1119 ssh3.

Check the log of login failure:

[root@rac2] # grep-I inval / var/log/secure & & grep-I failed / var/log/secure

Jan 9 10:30:44 rac2 sshd [3071]: Invalid user ydbuser from:: ffff:192.168.3.5

Jan 9 10:30:56 rac2 sshd [3071]: Failed password for invalid user ydbuser from:: ffff:192.168.3.5 port 36005 ssh3

Jan 9 10:30:56 rac2 sshd [3071]: Failed password for invalid user ydbuser from:: ffff:192.168.3.5 port 36005 ssh3

Jan 10 22:44:38 rac2 sshd [21611]: Failed password for root from:: ffff:10.10.10.6 port 1723 ssh3

There is no error (Invalid, refused) prompt in the log message. If there is no (Invalid, refused) as the system is normal, if there is an error prompt, a system alarm notification should be made.

6.2. Check the password changed by the user

There are often a lot of users on the database system, such as the third-party database monitoring system, the demonstration user at the initial installation of the database, the administrator user and so on. The passwords of these users are often written and are known by many people. will be used by people with ulterior motives to attack the system or even modify the data. Users who need to change their passwords include:

Database administrator user SYS,SYSTEM; other users.

After logging in to the system, type cat / etc/passwd at the prompt to see if there are any unused or unfamiliar accounts among the listed users. If it exists, it is logged as an exception.

How to change the password:

Alter user USER_NAME identified by PASSWORD

Back to the top.

7. Other inspection

In this section, we mainly check whether the current crontab task is normal, check whether the Oracle Job has failed, and so on.

7.1 check whether the current crontab task is normal

[oracle@AS14 ~] $crontab-l

7.2 whether Oracle Job has failed

Select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE'

If there are any problems, it is recommended to rebuild job, such as:

Exec sys.dbms_job.remove (1); commit;exec sys.dbms_job.isubmit (1); commit

7.3. Monitor the growth of data volume

Select A.tablespace_name, (1-(A.total) / B.total) * 100used_percent from (select tablespace_name, sum (bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name, sum (bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name = B.tablespace_name

According to the daily inspection this week, find the database objects with rapid space expansion, and take appropriate measures:

-- Delete historical data

The mobile rule retains at least 6 months of historical data in the database, so the previous historical data can be backed up and then cleared to free up the resource space it occupies.

-expand the tablespace

Alter tablespace add datafile''size autoextend off

Note: when the database structure changes, such as increasing the tablespace, adding data files or redoing log files, all these operations will cause changes to the Oracle database control files. DBA should back up the control files by:

Execute the SQL statement:

Alter database backup controlfile to'/ home/backup/control.bak'

Or:

Alter database backup controlfile to trace

In this way, the SQL command to create the control file is generated in the USER_DUMP_DEST (specified in the initialization parameter file) directory.

7.4. Check for invalid indexes

Select index_name, table_name, tablespace_name, status From dba_indexes Where owner = 'CTAIS2' And status' VALID'

Note: it is normal for the index status on the partitioned table to be NCMA. If there is an invalid index, rebuild the index, such as:

Alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME

7.5. Check for constraints that do not work

SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status = 'DISABLE' and constraint_type =' P'

Enable if there are failure constraints, such as:

Alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME

7.6. Check for invalid trigger

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED'

Enable if there is a failure trigger, such as:

This is the end of alter Trigger TRIGGER_NAME Enable; 's "what is the daily inspection of Oracle database"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.