The daily work of Oracle database administrator

Jan 7 posted in Oracle Database (RSS), 609 views.

Oracle Database administrator should be as follows on the Oracle database system for regular monitoring:
(1). On a daily basis running Oracle databases, log files, backups, database space usage, the use of system resources, it was discovered, and solve the problem.

(2). Weekly space for expansion of the database objects and data to monitor the growth of the database to do health checks, the state of the database objects for check-ups.

(3). On a monthly tables and indexes etc. Analyze, check the table of space debris, looking for a database performance tuning of the opportunity to conduct a database performance tuning, the next steps of space management plan. State of the Oracle database, conduct a comprehensive inspection.

Every day at work

(1). Make sure that all of the INSTANCE state of normal

Log on to all the databases or routine testing ORACLE background processes:

$ ps-ef | grep ora

(2). Check the file system used (the remaining space). If the file system free space is less than 20%, need to delete unused files to free up space.

$ df-k

(3). Check the log file and trace file record alert and trace errors in the file.

To connect to each system to be managed

Use 'telnet'

For each database, cd to the bdump directory, usually $ ORACLE_BASE / / bdump

Using the Unix 'tail' command to view the file alert_.log

If you find any new ORA-errors, record and resolve

(4). Check the validity of the database backup that day.

RMAN Backup on the way:

Check third-party backup tools, backup log to determine the success of the backup

On the EXPORT backup:

Check exp log files to determine the success of the backup

The other backup method:

Check the appropriate log file

(5). Check the status of the record data file is not "online" data file and do recovery.

Select file_name from dba_data_files where status = 'OFFLINE'

(6). Check the table space is being used

SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m, to_char (100 * sum_free_m / sum_m, '99 .99 ') | |'% 'AS pct_free

FROM (SELECT tablespace_name, sum (bytes) / 1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),

(SELECT tablespace_name AS fs_ts_name, max (bytes) / 1024/1024 AS max_m, count (blocks) AS count_blocks, sum (bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name)

WHERE tablespace_name = fs_ts_name

(7). Check the remainder of the table space

SELECT tablespace_name, sum (blocks) as free_blk,

trunc (sum (bytes) / (1024 * 1024)) as free_m,

max (bytes) / (1024) as big_chunk_k, count (*) as num_chunks

FROM dba_free_space GROUP BY tablespace_name;

(8). Monitor database performance

Run bstat / estat report generation system

Or use statspack to collect statistical data

(9). Check database performance, recording database cpu use, IO, buffer hit ratio, etc.

Use vmstat, iostat, glance, top and other commands

(10). Everyday problems with processing.

The working week

(1). Controlled the space extension of the database objects

According to the inspection day this week to find space for expansion of the database objects quickly and take appropriate measures

- Remove historical data

--- Extended table space

alter tablespace add datafile''size

--- Adjust the data object storage parameters

next extent

pct_increase

(2). Monitoring the growth of the amount of data

According to the inspection day this week to find records fast growth in the number of database objects, and to take appropriate measures

- Remove historical data

--- Extended table space

alter tablespace add datafile''size

(3). System health check

Check the following:

init.ora

controlfile

redo log file

archiving

sort area size

tablespace (system, temporary, tablespace fragment)

datafiles (autoextend, location)

object (number of extent, next extent, index)

rollback segment

logging & tracing (alert.log, max_dump_file_size, sqlnet)

(4). Check invalid database objects

SELECT owner, object_name, object_type FROM dba_objects

WHERE status = 'INVALID'.

(5). Check constraint does not work

SELECT owner, constraint_name, table_name,

constraint_type, status

FROM dba_constraints

WHERE status = 'DISABLED' AND constraint_type = 'P'

(6). Check invalid trigger

SELECT owner, trigger_name, table_name, status

FROM dba_triggers

WHERE status = 'DISABLED'

Monthly work

(1). Analyze Tables / Indexes / Cluster

analyze table estimate statistics sample 50 percent;

(2). Check the table space debris

According to this month, the weekly inspection and analysis of database fragmentation to find the appropriate solution

(3). Looking for a database performance tuning opportunities

Comparison of daily database performance monitoring and reporting, to determine whether it is necessary to adjust the database performance

(4). Database Performance Tuning

If necessary, conduct performance tuning

(5). Proposed management plan for the next space

Based on weekly monitoring, space management for improved methods proposed

The daily management of Oracle DBA

Purpose: This document has a very detailed information on a record of one or more of the ORACLE database, daily, monthly, and annual operating results and check the status of the results of the appendix in the document you will see All inspections, modify the SQL and PL / SQL code.

Directory

1. Routine maintenance procedures

A. Checks all instances of the date of

B. To find some new alert log

C. Check DBSNMP is running

D. Check the database backup is correct

E. Check the backup to tape, the files are correct

F. Check whether the performance of the database proper and reasonable and whether there is sufficient space and resources

G. The document copied to the backup log database

H. Should be used to see a DBA user's manual

2. Night maintenance procedures

A. To collect the data VOLUMETRIC

3. Weekly maintenance

A. To find those breaking the rules of the OBJECT

B. To find whether there are violations of security policy issues

C. See the error log where the SQL * NET

D. All the alert log archive

E. Frequently visited provider's home page

4. Month maintenance program

A. View of the database will harm the growth rate of

B. Recalling its previous adjustment of the database to optimize performance

C. See I / O, screen neck problems

D. Review FRAGMENTATION

E. The future implementation plan

F. See set point and maintain the

1. Day maintenance process

A. See whether it has played all of the instances of

Determine the database is available to each instance and run the day to write the log report or run the test file. Of course, there are some operations we hope that it will run automatically.

Choose implementation: using ORACLE Manager 'PROBE' event to view

B. To find new warning log file

1. Join an operation management system for each

2. Use 'TELNET' or a comparable program

3. For each managed instance, a regular implementation of the $ ORACLE_BASE / / bdump operations and enable them to fall back control of the database SID.

4. At the prompt, the use of UNIX in the 'TAIL' command to view the alert_.log, or otherwise check the file in the recent period the warning log

5. If you have previously there have been some of the ORA_ERRORS appeared, it would be recorded in the database recovery logs, and careful study of them, the database recovery log in the <FILE> in the

C. See DBSNMP of operation

Check each managed machine 'DBSNMP' process and recorded them to the log.

In UNIX, in the command line, type ps-ef | grep dbsnmp, will be back to see the two DBSNMP process running. If not, restart DBSNMP.

D. Check database backup is successful

E. Check whether the backup tapes document the success of

F. Check for reasonable performance for the adequacy of resources for

1. Check the table space, there is no residual room for

For each instance, the check whether there is space in the table there is space left to meet the anticipated needs of the day. When the data already in its database is stable, the data on the average growth also can be calculated, the smallest of the remaining space for at least a day to be able to meet the data growth.

A) Run 'FREE.SQL' to check the remaining space for the table space.

B) Run 'SPACE.SQL' to check the table space in the percentage of remaining space

2. Check rollback

Rollback of the state is generally online, apart from some complex work to prepare for a special segment, which generally state is offline.

a. Each database has a list of rollback segment name.

b. You can use the V $ ROLLSTAT to look up online or offline rollback of the present state.

c. For all rollback segment storage parameters and name, can be used to query DBA_ROLLBACK_SEGS. But it is not as good as V $ ROLLSTAT accurate.

3. To identify excessive growth of

View the database in excess of the resources or growing too large segments that are the storage parameters need to be adjusted.

a. gather information on the size of daily data can be used

'ANALYZE5PCT.SQL'. If you collect the information every night, you can skip this step.

b. Check the current range of available 'NR.EXTENTS.SQL'.

c. Query the current size of the table information.

d. check the size of the current index information.

e. Look growth.

4. To determine the scope of space

If the range of spatial objects NEXT_EXTENT than the tablespace can offer the widest range bigger, then this will affect the database operation. If we find that goal, you can use 'ALTER TABLESPACE COALESCE' survey of its location, or add additional data files.

A) Run 'SPACEBOUND.SQL'. If you are normal, and will not return any rows.

5. Recalling the CPU, memory, networking, hardware resources, the process of argument

A) check the CPU utilization, and enter x: \ web \ phase2 \ default.htm => system

metrics => CPU utilization page, CPU's maximum of 400, when the CPU occupancy remained at more than 350 for some time, then we need to see and study the problems arose.

G. Will be archived logs copied to the standby database

If there is a standby database, copy the appropriate archived logs to the standby database, the desired position, standby database, to save the most recent data.

H. regular access to DBA User's Manual

If possible, to a wide range of reading, including DBA manuals, trade journals, newsgroups or mailing lists.

2. Night maintenance process

Most of the database products will benefit from night to determine the operation of the inspection process.

A. Data collection VOLUMETRIC

1. Analysis plan and data collection

A more accurate analysis of the calculation and save the result.

a) If you do not make these words, with 'MK VOLFACT.SQL' to create a volumetric tables.

b) to collect data on the size of the information at night, with 'ANALYZE COMP.SQL'.

c) the collection of statistical results, the 'POP VOL.SQL'.

d) In the idle time of inspection data, if possible, weekly or monthly basis.

I was using MS EXCEL and the ODBC connection to examine the growth of data and charts

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

3. A weekly maintenance procedures

A. To find the goal the destruction

1. For each object of a given table space is, NEXT_EXTENT size is the same, such as 12/14/98, the default NEXT_EXTENT of DATAHI for 1G, DATALO for 500MB, INDEXES to 256MB.

A) Check NEXT_EXTENT settings that can be used 'NEXTEXT. SQL '.

B) Check the existing EXTENTS, can be 'EXISTEXT. SQL '.

2. All tables should have a unique primary key

a. See that table does not have a primary key can be used 'NO_PK.SQL'.

b. identify the factors that play a role in the primary key is not, and can be used 'DIS_PK.SQL'.

c. All of the primary key for the index must be unique, and can be used 'NONUPK. SQL 'to check.

3. All the indexes have to put the index table space. Run 'MKREBUILD_IDX. SQL '

4. Different plans for the environment should be the same, especially the testing environment and finished plans for the environment should be the same.

a. Check the different operating environment in the two data types are the same, available 'DATATYPE.SQL'.

b. In two different instances of the object to look for a different point, can be 'OBJ_COORD.SQL'.

c. a better approach is to use a tool, such as program manager for the software as tools.

B. see if there is harm to the security policy issues.

C. See the SQL * NET Baocuo log.

1. Client-side log.

2. Server-side log.

D. All the alert log archive

E. Provider's home page

1. ORACLE Suppliers

http://www.oracle.com

http://technet.oracle.com

http://www.oracle.com/support

http://www.oramag.com

2. Quest Software

http://www.quests.com

3. Sun Microsystems

http://www. sun. com

4. Month maintenance process

A. View of the database will harm the growth rate of

1. From the previous record or report reviewed the changes in the growth segment in order to determine the growth segment harm

B. Recalling its previous adjustment of the database to optimize performance

1. Recalling the ORACLE database to adjust the general point of comparison in previous reports to identify harmful trends.

C. See I / O, screen neck problems

1. View the activity of pre-database files, comparing the previous output to determine the problem may lead to cervical screen trend.

D. Review FRAGMENTATION

E. Program database in the future performance of

1. Comparison of ORACLE and the operating system CPU, memory, network, and disk utilization in order to determine in the near future there will be a number of trends in the scramble for resources

2. When the system will exceed the scope of the performance trend as necessary to service-level agreements of view

F. The completion of the adjustment and maintenance work

1. To make changes to avoid the system resources to meet competing needs, This includes the addition of new resources or to make the expected downtime.