(Transfer) Oracle Database Administrator work content

xiaoxiao2021-03-06  118

Oracle Database Administrators should perform regular monitoring of Oracle database systems as follows:

(1). Every day, the operational status, log file, backup, data of the Oracle database

The space usage of the library, the use of system resources is checked, discovered and resolved

problem.

(2). Monitor the spatial extension of database objects, data growth, and perform health checks for databases, check the status of database objects.

(3). Monthly table and index, analyzes, check the table space fragment, find the database

The opportunity for performance adjustment, performing database performance adjustment, and presents the next step spatial management

plan. A comprehensive examination is performed in the Oracle database status.

Everyday work

(1). Confirm that all instance status is normal

Log in to all databases or routines, detect Oracle Background Process:

$ PS-EF | GREP ORA

(2). Check the use of the file system (remaining space). If the remaining space of the file system is less than 20%, you need to delete the unused file to release the space.

$ DF -K

(3). Check the log file and the trace file to record errors in the Alert and Trace files.

Connect to each need to manage

? Use 'telnet'

? For each database, CD to bdump directory, usually $ oracle_base // bdump

• Use the UNIX 'tail' command to view a Alert_.log file

? If you have discovered any new ORA-error, record and resolve

(4) Check the validity of the database on the day of date.

Method for RMAN backup:

Check the backup log of a third-party backup tool to determine if the backup is successful.

For Export backup:

Check the EXP log file to determine if the backup is successful

For other backup methods:

Check the corresponding log file

(5). Check the status recording status of the data file is not "Online" data file, and make recovery.

SELECT FILE_NAME from DBA_DATA_FILES where status = 'offline'

(6). Check the use of the table space

SELECT TABLESPACE_NAME, MAX_M, Count_BLOCKS Free_BLK_CNT, SUM_FREE_M, TO_CHAR (100 * SUM_FREE_M / SUM_M, '99990 ') ||'% '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 remaining tablespace

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 Generating System Report

Or use Statspack to collect statistics

(9). Check the database performance, record the CPU usage of the database, IO, Buffer hits, etc.

Use VMSTAT, IOSTAT, GLANCE, TOP, etc.

(10). Processing in daily problems.

Weekly work

(1). Space extension of the control database object

According to the daily check in this week, find a fast-expanding database object, and take corresponding measures

- Delete historical data

--- Expansion space

Alter TableSpace Add DataFile '' Size

--- Adjust the storage parameters of the data object

Next extent

PCT_INCREASE

(2). Growth of monitoring data volume

According to the daily inspection of this week, find a database object that has grown quickly, and

Take a corresponding measure

- Delete historical data

--- Expansion 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 if the invalid database object

SELECT OWNER, Object_name, Object_type from DBA_OBJECTS

Where status = 'invalid'.

(5). Check the constraint that does not work

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME,

Constraint_type, status

From DBA_CONSTRAINTS

Where status = 'disabled' and constraint_type = 'p'

(6). Check if 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 watch space fragmentation

According to the weekly check analysis of the data library fragmentation this month, find the corresponding solution

(3) Find opportunities for database performance adjustments

Compare the monitoring report for database performance daily, determine if there is necessary to adjust the database performance

(4). Database performance adjustment

Performance adjustment if necessary

(5). Propose Next Space Management Plan

Based on weekly monitoring, a method of improvement of spatial management is proposed.

Oracle DBA daily management

OBJECTIVE: This document has a very detailed information to record a daily, monthly,

The results of the annual running state and the results of the inspection, you will see all checks, modified SQL in the appendix of the document

And PL / SQL code.

table of Contents

Daily maintenance procedure

A. Check all instances

B. Find some new warning log

C. Check if dbsnmp is running

D. Check if the database backup is correct

E. Check if the file backup to the tape is correct

F. Check if the performance of the database is normal, do you have enough space and resources?

G. Copy the document log into the backup database

H. To see the DBA user manual

2. Night maintenance procedure

A. Collect data from Volumetric

3. Weekly maintenance work

A. Find the Object that destroyed rules

B. Find if there is a problem with violations of security strategy

C. View Error Local SQL * NET Log

D. Archive all warning logs

E. Regularly visited the supplier's homepage 4. month maintenance procedures

A. View growth rates for databases

B. Review the adjustment of previous database optimization performance

C. View I / O screen neck problem

D. Review Fragmentation

E. In the future implementation plan

F. View adjustment points and maintenance

5. Appendix

A. Month maintenance process

B. Night maintenance process

C. Week maintenance process

6. References

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

One. Day maintenance process

A. See if all instances have been

Determine the database is available, write each instance to the log and run the day report or run the test

file. Of course, some operations We hope that it can run automatically.

Optional Execution: View using 'probe' in Oracle Manager

B. Find a new warning log file

Connect each operating management system

2. Using 'Telnet' or compareable program

3. For each management instance, frequent execution $ oracle_base // bdump operation

Do it back to control the SID of the database.

4. Under the prompt, use the 'tail' command in UNIX to view Alert_.log, or

Use other ways to check the recent warning log in the file

5. If some Ora_ERRORS that appeared before, record it to the database

Recover the log and study them carefully, this database recovery log in

C. View DBSNMP operation

Check each managed 'dbsnmp' process and record them into the log.

In UNIX, in the command line, type PS-EF | GREP DBSNMP, will see 2

DBSNMP processes are running. If not, restart dbsnmp.

D. Check if the database backup is successful

E. Check if the backup tape document is successful

F. Check if there is enough resources for reasonable performance

1. Check if there is any remaining space in the table space.

For each instance, check if there is a remaining space in the table space to meet the day

The expected need. When the data existing in the database is stable, the daily growth has an average

The number is also possible to calculate, and the smallest remaining space must be at least to meet the growth of daily data.

A) Run 'Free.SQL' to check the remaining space of the table space.

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

2. Check the rollback segment

The status of the rollback segment is generally online, except for some special segments prepared for complex work, it is generally offline.

a) Each database has a list of rollback segments.

b) You can use V $ ROLLSTAT to query the current state of the online or offline retraction.

c) The storage parameters and names for all returns are available.

DBA_ROLLBACK_SEGS to query. But it is not as accurate as V $ ROLLSTAT.

3. Identify some excessive growth

The storage parameters of these segments need to be adjusted in the database.

a) Collect information on day data size, you can use

'Analyze5pct.sql'. If you collect every night, you can skip this step.

b) Check the current range and use 'nr.extents.sql'.

c) Query the size information of the current table.

d) Query information about the current index size.

e) query growth trend.

4. Determine the range of space.

If the NEXT_EXTENT of the range spatial object is the maximum pattern that can be provided by the table space

There is still big, then this will affect the run of the database. If we find this goal,

To investigate its location with 'ALTER TABLESPACE COALESCE', or add additional data files. A) Run 'SpaceBound.sql'. If it is normal, you will not return any rows.

5. Review the process of CPU, memory, network, and hardware resource arguments

A) Check the use of CPUs, go to x: .htm => system

Metrics => CPU Utilization page, the maximum number of CPUs is 400, when the occupation of the CPU

If you have more than a period of 350, we need to view and study problems.

G. Copy the archive log into the standby database

If there is an alternate database, copy the appropriate archive log to the expectation of the alternate database

The most recent data is saved in the location and spare database.

H. Review DBA User Manual

If possible, to read extensive reading, including DBA manuals, industry magazines, newsgroups or mailing lists.

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

two. Night maintenance process

Most database products will benefit from the operation of the inspection process determined every night.

A. Collect Volumetric Data

1. Analyze plan and collect data

More accurate analysis of calculations and save results.

a) If you don't do this now, use 'mk volFact.sql' to create a meter of the measurement volume.

b) Collect information on the size of the evening, use 'analyze comp.sql'.

c) Collect statistics and use 'pop vol.sql'.

d) Check data, possible words, per week, or every month when free.

I use MS Excel and ODBC to check the growth of data and charts.

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

three. Weekly maintenance process

A. Find destroyed goals

1. For the object of each given space, the size of Next_EXTENT is the same, such as

12/14/98, the default Next_EXTENT DataHi is 1G, Datalo is 500MB,

INDEXES is 256MB.

A) Check the setting of Next_extent, available 'NEXTEXT. SQL '.

B) Check existing extents, available 'Existext. SQL '.

2. All tables should have a unique primary key

a) See those tables There are no primary keys, available with 'NO_PK.SQL'.

b) Find that the primary key is not played, you can use 'DIS_PK.SQL'.

c) All primary keys for indexes are unique, available in 'Nonupk. SQL 'to check.

3. All indexes are placed in the index table space. Run 'mkrebuild_idx. SQL '

4. The plan between different environments should be the same, especially between test environment and finished environment, should be the same.

a) Check if the data type in different 2 operational environments is consistent, available

'DataType.sql'.

b) Looking for different points of objects in 2 different instances, available

'Obj_coord.sql'.

c) Better approach is to use a tool that is like a tool for seeking software.

B. Check if there is a problem that harms the security policy.

C. View the error SQL * NET log.

1. Client log.

2. Server-end logs.

D. . Archive all warning logs E. Supplier's homepage

Oracle supplier

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

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

four. Month maintenance process

A. View growth rates for databases

1. From the previous record or change in the report, the growth of the segment growth is to determine the growth of the segment.

B. Review the adjustment of previous database optimization performance

1. Review the adjustment point of the general Oracle database, compare the previous report to determine harmful development trends.

C. View I / O screen neck problem

1. View the activity of the previous database file, compare the previous output to determine the trend that it is possible to lead to the problem.

D. Review Fragmentation

E. Planning database future performance

1. Compare the CPU, memory, network, and hard disk utilization of Oracle and operating systems

To determine the trend of some resources that will be in recent times

2. When the system will take advantage of the performance trend as the protocol of the service level

F. Complete adjustment and maintenance work

1. Make changes to the need to avoid the competition for system resources, including adding new resources or dispelled.

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

Fives. appendix

A. Daily program

- free.sql

--To Verify Free Space in TableSpaces

--Minimum Amount of Free Space

--Document your thresholds:

- = m

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

Space.sql

Space.sql

- To Check Free, PCT_FREE, And Allocated Space within a TableSpace

- 11/24/98

SELECT TABLESPACE_NAME, LARGEST_FREE_CHUNK

, NR_Free_chunks, Sum_Alloc_Blocks, Sum_Free_Blocks

, To_char (100 * SUM_FREE_BLOCKS / SUM_ALLOC_BLOCKS, '09.99 ') ||'% '

AS PCT_FREE

From (SELECT TABLESPACE_NAME, SUM (Blocks) As SUM_ALLOC_BLOCKS

From DBA_DATA_FILES GROUP BY TABLESPACE_NAME)

(SELECT TABLESPACE_NAME AS FS_TS_NAME

, Max (Blocks) As Largest_Free_chunk

, Count (Blocks) AS NR_FREE_CHUNKS

SUM (Blocks) AS SUM_FREE_BLOCKS from DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) Where TableSpace_name = fs_ts_name

2. Analyze5pct.sql

- Analyze5pct.sql

- To Analyze Tables and INDEXES Quickly, Using A 5% Sample Size

- (Do Not Use this script if you are performing the overnight

- Collection of Volumetric Data)

- 11/30/98

Begin

DBMS_UTILITY.Alyze_schema ('& Owner', 'Estimate', NULL, 5);

END;

/

3. NR_EXTENTS.SQL

- NR_EXTENTS.SQL

- To Find Out Any Object Reaching

- Extents, And Manually Upgrade It to allow unlimited

- Max_EXTENTS (Thus Only Objects WE * EXPECT * TO BE BIG

- Are ALLOWED to BECOME BIG)

- 11/30/98

Select E.WNER, E.SEGMENT_TYPE, E.SEGMENT_NAME, COUNT (*) AS NR_EXTENTS,

S.max_extents

, To_Char (SUM (E.BYTES) / (1024 * 1024), '999, 999.90') AS MB

From DBA_EXTENTS E, DBA_SEGMENTS S

WHERE E.SEGMENT_NAME = S.SEGMENT_NAME

Group by E.WNER, E.SEGMENT_TYPE, E.SEGMENT_NAME, S.MAX_EXTENTS

Having count (*)> & threshold

OR ((s.max_extents - count (*)) <&& threshold)

Order by count (*) DESC

4. SpaceBound.sql

- SpaceBound.sql

- To Identify Space-Bound Objects. If All IS Well, NO ROWS Are Returned.

- IF Any Space-Bound Objects Are Found, Look At Value of Next Extent

- Size to Figure out what happened.

- the use coalesce (Alter Tablespace coalesce).

- Lastly, Add Another DataFile To The TableSpace If Needed.

- 11/30/98

SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME

From all_tables a,

(Select TableSpace_name, max (bytes) AS BIG_CHUNK

From DBA_FREE_SPACE

Group by tablespace_name) f

Where f.tablespace_name = a.tablespace_name

And a.next_extent> F.BIG_CHUNK

B. Processing per night

1. mk_volfact.sql

- mk_volfact.sql (ONLY Run this overce to set it; do not run it it nightly!) - - TABLE UTL_VOL_FACTS

CREATE TABLE UTL_VOL_FACTS

Table_name varchar2 (30),

Num_Rows Number,

MEAS_DT DATE)

TableSpace Platab

STORAGE

Initial 128k

Next 128k

Pctincrease 0

Minextents 1

MaxExtents unlimited

)

/

- Public Synonym

CREATE PUBLIC SYNONYM UTL_VOL_FACTS for & Owner..utl_vol_facts

/

- GRANTS for UTL_VOL_FACTS

Grant SELECT ON UTL_VOL_FACTS TO PUBLIC

/

2. Analyze_comp.sql

-

- analyze_comp.sql

-

Begin

Sys.dbms_utility.Analyze_schema ('& Owner', 'Compute');

END;

/

3. Pop_vol.sql

-

Pop_vol.sql

-

INSERT INTO UTL_VOL_FACTS

SELECT TABLE_NAME

, NVL (Num_Rows, 0) as num_rows

, Trunc (last_analyzed) AS MEAS_DT

From all_tables - or Just User_Tables

WHERE OWNER IN ('& Owner') - OR a Comma-Separated List of Owners

/

Commit

/

C. Weekly handler

Nextext.sql

-

- NexText.sql

-

- To find Tables That Don't Match The Tablespace Default for next extent.

- The Implicit Rule Here Is That Every Table In A Given TableSpace Should

- Use the exact same value for next, Which shouth Also Be the tablespace's

- Default value for next.

-

- this Tells US What The Setting For Next Is for these Objects Today.

-

- 11/30/98

Select segment_name, segment_type, ds.next_extent as actual_next

, DT.TABLESPACE_NAME, DT.NEXT_EXTENT AS Default_Next

From DBA_TABLESPACES DT, DBA_SEGMENTS DS

Where dt.tablespace_name = ds.tablespace_name

And dt.next_extent! = DS.NEXT_EXTENT

And ds.owner = Upper ('& oowner')

Order by TableSpace_name, segment_type, segment_name

2. EXISTEXT.SQL

-

- EXISTEXT.SQL

-

- to check existing extents - TO CHECK EXIX

-

- This Tells US How Many of Each Object's Extents Differ in size from-- The tableSpace's default size. If this Report shows a Lot of Different

- Size Extens, Your Free Space Is Likey to Become Fragment. If So,

- this TableSpace is a Candidate for reorganizing.

-

- 12/15/98

Select segment_name, segment_type

COUNT (*) AS NR_EXTS

, SUM (DX.BYTES, DT.NEXT_EXTENT, 0, 1)) AS NR_ILLSIZED_EXTS

, DT.TABLESPACE_NAME, DT.NEXT_EXTENT AS DFLT_EXT_SIZE

From DBA_TABLESPACES DT, DBA_EXTENTS DX

Where dt.tablespace_name = dx.tablespace_name

And dx.owner = '& owner'

Group by segment_name, segment_type, dt.tablespace_name, DT.NEXT_EXTENT

3. NO_PK.SQL

-

- no_pk.sql

-

- To Find Tables without PK Constraint

-

- 11/2/98

SELECT TABLE_NAME

From all_tables

Where = '& oowner'

Minus

SELECT TABLE_NAME

From all_constraints

Where = '&& owner'

And constraint_type = 'p'

4. Dispk.sql

-

DISPK.SQL

-

- To Find Out Which Primary Keys Are Disabled

-

- 11/30/98

SELECT OWNER, Constraint_name, Table_name, Status

From all_constraints

WHERE OWNER = '& OWNER' and status = 'disk' and constraint_type = 'p'

5. Nonupk.sql

-

- nonupk.sql

-

- To Find Tables with Nonunique PK Indexes. Requires That PK Names

- Follow a naming convention. An Alternative Query Follows That

- Does Not Have this Requirement, But Runs More Slowly.

-

- 11/2/98

SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS

From all_indexes

WHERE INDEX_NAME LIKE '& PKNAME%'

And Owner = '& Owner' and uniqueess = 'NonUniQue'

Select c.constraint_name, i.tablespace_name, i.uniquenessfrom all_constraints c, all_indexes i

WHERE C.OWNER = Upper ('& oowner') And i.unique = 'NonUnique'

And c.constraint_type = 'p' and I.index_name = c.constraint_name

6. mkrebuild_idx.sql

-

- mkrebuild_idx.sql

-

- Rebuild Indexes To Have Correct Storage Parameters

-

- 11/2/98

SELECT 'ALTER INDEX' || index_name || 'Rebuild'

, 'TableSpace Indexes Storage'

|| '(Initial 256 K Next 256 K Pctincrease 0);'

From all_indexes

WHERE (TableSpace_name! = 'Indexes'

Or next_extent! = (256 * 1024)

)

And Owner = '& Owner'

/

7. DataType.sql

-

- DataType.sql

-

- To Check DataType Consistency Between Two Environments

-

--11 / 30/98

SELECT

Table_name,

COLUMN_NAME,

DATA_TYPE,

Data_length,

Data_PRecision,

Data_scale,

Nullable

From all_tab_columns - first environment

Where = '& oowner'

Minus

SELECT

Table_name,

COLUMN_NAME,

DATA_TYPE,

Data_length,

Data_PRecision,

Data_scale,

Nullable

From all_tab_columns @ & my_db_link - second environment

WHERE OWNER = '& Owner2'

Order by Table_name, Column_name

8. Obj_coord.sql

-

- Obj_coord.sql

-

- To Find Out Any Difference In Objects Between Two Instances

-

- 12/08/98

Select Object_name, Object_Type

From user_Objects

Minus

Select Object_name, Object_Type

From user_Objects @ & my_db_link

VI. Reference

1. LONEY, Kevin Oracle8 DBA HANDBOOK

2. Cook, David Database Management from Crisis To Confidence

[

http://www.orapub.com/]

3. COX, Thomas B. The Database Administration Maturity Model

转载请注明原文地址:https://www.9cbs.com/read-125335.html

New Post(0)