DBA's responsibilities and tasks are from the Oracle Chinese Technical Forum)

Source: Internet
Author: User
Tags disk usage

It is from the Oracle Chinese technology forum. This section describes the responsibilities and tasks of a DBA.
The Oracle Database Administrator should perform regular monitoring on the Oracle database system as follows:
(1) daily running status, log files, backups, and data of Oracle databases

Library space usage, system resource usage check, find and solve

Problem.

(2) Monitor the space expansion and data growth of database objects every week, check the database health, and check the status of database objects.

(3) perform analyze on tables and indexes every month to check the space fragments and find databases.

Performance adjustment opportunities, database performance adjustment, and propose the next space management

Plan. Perform a comprehensive check on the Oracle database status.

Daily Work

(1) confirm that all instances are in normal status

Log on to all databases or routines to check Oracle background processes:

$ PS-Ef | grep ora

(2) Check the use of the file system (available space ). If the remaining space of the file system is less than 20%, delete unnecessary files to release space.

$ DF-K

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

Connect to each system to be managed

? Use 'telnet'

? CD to the bdump directory for each database, usually $ oracle_base/<Sid>;/bdump

? Run the Unix 'tail' command to view the alert _ <Sid>;. log file.

? If any new ora-errors are found, record and resolve them.

(4). Check the validity of the database backup on the current day.

RMAN backup method:

Check the backup logs of a third-party backup tool to check whether the backup is successful.

Export backup mode:

Check the exp log file to check whether the backup is successful

Other backup methods:

Check the corresponding log file

(5) Check the data file whose status is not "online" and restore it.

Select file_name from dba_data_files where status = 'offline'

(6). Check the usage 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, '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 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;

(. Monitor database performance

Run bstat/estat to generate a system report

Or use statspack to collect statistics.

(9) Check the database performance and record the CPU usage, Io, buffer hit rate, and so on of the database.

Use vmstat, iostat, glance, top, and other commands

(10) Handling of daily problems.

Weekly Work

(1) control the space expansion of database objects

Find the database objects that quickly expand the space according to the daily check results of this week, and take

Measures

-- Delete historical data

--- Expand table space

Alter tablespace <Name>; add datafile '<File>; 'size <size>;

--- Adjust the storage parameters of Data Objects

Next extent

Pct_increase

(2) monitoring data volume growth

Based on the daily check results of this week, find the database objects with a fast increase in the number of records, and collect

Take corresponding measures

-- Delete historical data

--- Expand table space

Alter tablespace <Name>; add datafile '<File>; 'size <size>;

(3). System Health Check

Check the following:

Init <Sid>;. 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) constraints that do not work

Select owner, constraint_name, table_name,

Constraint_type, status

From dba_constraints

Where status = 'Disabled 'and constraint_type = 'P'

(6) Check for invalid triggers

Select owner, trigger_name, table_name, status

From dba_triggers

Where status = 'Disabled'

Monthly work

(1). Analyze tables/indexes/Cluster

Analyze table <Name>; estimate statistics sample 50 percent;

(2). Check the table space fragments.

Analyze database fragmentation Based on weekly checks this month and find the corresponding solution

(3). Seek opportunities for database performance Adjustment

Compare daily monitoring reports on database performance and determine whether it is necessary to adjust database performance

(4). database performance Adjustment

Adjust the performance if necessary

(5) propose the next space management plan

Based on weekly monitoring, this paper proposes an improvement method for space management.

Routine Oracle DBA management

Objective: This document provides detailed information about one or more Oracle databases on a daily, monthly basis,

The results of the annual running status and the results of the check are displayed in the appendix of the document.

And PL/SQL code.

Directory

1. Routine Maintenance Procedures

A. Check all started instances

B. Search for some new warning logs

C. Check whether Dbsnmp is running

D. Check whether the database backup is correct

E. Check whether the files backed up to the tape are correct.

F. Check whether the database performance is normal and reasonable, and whether there is sufficient space and resources.

G. Copy the document logs to the backup database.

H. Read the DBA user manual frequently.

2. Night Maintenance Program

A. Collect Volumetric Data

3. weekly maintenance

A. Find the objects that break the rules.

B. check whether any security policy violations occur.

C. view the SQL * Net log of the error location

D. archive all warning logs

E. Visit the supplier's home page frequently.

4. Monthly Maintenance Program

A. view the growth rate that harms the database

B. Review previous database optimization performance adjustments

C. view the I/O screen neck issues

D. Review fragmentation

E. Future execution plan

F. View adjustment points and Maintenance

5. Appendix

A. Monthly maintenance process

B. Night maintenance process

C. weekly maintenance process

6. References

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

1. Daily maintenance process

A. check whether all instances have started

Determine whether the database is available, write each instance into the log and run the daily report or run the test

File. Of course there are some operations that we want to automatically run.

Optional execution: Use the 'probe' event in the Oracle manager to view

B. Search for the new warning log file.

1. connect to each Operating System

2. Use 'telnet 'or a comparable Program

3. Perform $ oracle_base/<Sid>;/bdump for each management instance.

And enables it to return to the SID of the control database.

4. Run the 'tail' command in UNIX to view alert _ <Sid>;. Log, or

Check the warning logs of the latest period in the file in other ways

5. If some ora_errors appeared again before, record it to the database

Recover logs and study them carefully. The recovery logs of this database are in file

C. view the running status of Dbsnmp

Check the 'dbsnmp 'processes of each managed machine and record them in the log.

In UNIX, in the command line, type PS-Ef | grep Dbsnmp, and two

The Dbsnmp process is running. If no, restart Dbsnmp.

D. Check whether the database backup is successful

E. Check whether the Backup Tape document is successful.

F. Check whether sufficient resources are available for reasonable performance.

1. check whether there is any available space in the tablespace.

For each instance, check whether there is any available space in the tablespace to meet the requirements of the current day.

. When the existing data in the database is stable, the daily data growth average

The number can also be calculated. The minimum remaining space must at least meet the daily data growth requirement.

A) Run 'free. SQL 'to check the remaining space.

B) Run 'space. SQL 'to check the percentage of available space in the space.

2. Check rollback segments

The status of a rollback segment is generally online. Except for some dedicated segments prepared for complex work, it is generally offline.

A) each database has a list of rollback segment names.

B) You can use v $ rollstat to query the current status of online or offline rollback segments.

C) the storage parameters and names of all rollback segments are available.

Dba_rollback_segs. But it is not as accurate as V $ rollstat.

3. Identify excessive growth

Check the storage parameters of the segments in the database that exceed the resource or the growth rate is too high.

A) collect information about the daily data size, which can be used

'Analyze5pct. SQL '. Skip this step if you collect information every night.

B) Check the current range. 'nr. extents. SQL 'is available '.

C) query the size of the current table.

D) query the current index size.

E) query the growth trend.

4. Determine the space range.

If the next_extent of the range space object is greater than the maximum range provided by the tablespace

This will affect the operation of the database. If we find this goal

Use 'alter tablespace coaleid' to investigate its location or add another data file.

A) Run 'spacebound. SQL '. If they are all normal, no rows will be returned.

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

A) Check the CPU utilization and enter X:. htm =>; System

Metrics =>; CPU utilization page. The maximum CPU usage is 400.

For a period of time above 350, we need to check and study the problems.

G. Copy the archived logs to the standby database.

If there is a backup database, it is expected to copy the appropriate archived logs to the backup database.

Location, which saves the most recent data in the standby database.

H. Check the DBA user manual frequently.

If possible, read it extensively, including the DBA manual, industry magazine, news group, or email list.

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

Ii. Night maintenance process

Most database products will benefit from the running of check processes identified every night.

A. Collect Volumetric Data

1. Analyze plans and collect data

More Accurate Analysis and Computation and save results.

A) if you haven't done this now, use 'mk volfact. SQL 'to create a table for volume determination.

B) collect the data size in the evening, and use 'analyze Comp. SQL '.

C) Collect statistical results and use 'pop vol. SQL '.

D) Check the data when you are idle. If possible, check the data every week or every month.

I used the connection between MS Excel and ODBC to check the growth of data and charts.

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

3. weekly maintenance process

A. Search for damaged targets

1. For each given tablespace object, the next_extent size is the same, as shown in

12/14/98, the default next_extent datahi is 1 GB, and datalo is 500 mb,

Indexes is 256 MB.

A) Check next_extent settings and use 'nextext. SQL '.

B) Check the existing extents and use 'existext. SQL '.

2. All Tables should have unique primary keys.

A) Check the tables that do not have a primary key and can use 'no _ PK. SQL '.

B) find those primary keys that do not play a role and use 'dis _ PK. SQL '.

C) All primary keys used for indexing are unique and can be 'nonupk. SQL to check.

3. All indexes should be placed in the index tablespace. Run 'mkrebuild _ idx. SQL'

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

A) check whether the data types in the two running environments are consistent and available.

'Ype ype. SQL '.

B) Find the differences between objects in two different instances.

'Obj _ coord. SQL '.

C) A better way is to use a tool like a scheduler manager looking for software.

B. check whether there is any problem that harms the security policy.

C. view the SQL * Net log of the error.

1. client logs.

2. server logs.

D. archive all warning logs

E. Supplier Homepage

1. 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

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

4. Monthly maintenance process

A. view the growth rate that harms the database

1. Review changes in segment growth from previous records or reports to identify the hazards of segment growth

B. Review previous database optimization performance adjustments

1. Review the adjustment points of general Oracle databases and compare previous reports to identify harmful development trends.

C. view the I/O screen neck issues

1. Check the activity of the database files in the early stage and compare the previous output to determine the trend that may cause the screen neck problem.

D. Review fragmentation

E. Plan database performance in the future

1. Compare the CPU, memory, network, and hard disk usage of Oracle and the operating system.

To determine the trend of resource competition in the near future

2. When the system is out of scope, the performance trend should be regarded as a service level agreement.

F. Complete adjustment and Maintenance

1. Make modifications meet the need to avoid competition for system resources, including adding new resources or making the expected shutdown.

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

V. Appendix

A. Routine procedures

-- Free. SQL

-- To verify free space in tablespaces

-- Minimum amount of free space

-- Document your thresholds:

-- <Tablespace_name >;=< amount>; 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

1. 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_space

Group 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 inserting the overnight

-- Collection of volumetric data)

-- 11/30/98

Begin

Dbms_utility.analyze_schema ('& owner', 'estime', null, 5 );

End;

/

3. nr_extents. SQL

-- Nr_extents. SQL

-- To find out any object reaching <threshold>;

-- Extents, and manually upgrade it to allow unlimited

-- Max_extents (thus only objects we * have CT * to be big

-- Are allowed to become big)

-- 11/30/98

Select E. Owner, E. segment_type, E. segment_name, count (*) as nr_extents,

S. max_extents

, To_char (sum (E. bytes)/(1024*1024), '000000') as MB

From dba_extents E, dba_segments s

Where E. segment_name = S. segment_name

Group by E. Owner, 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.

-- Then use coalesce (alter tablespace <Foo>; 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,

(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 programs per night

1. mk_volfact. SQL

-- Mk_volfact. SQL (only run this once to set it up; do not run it nightly !)

-- Table utl_vol_facts

Create Table utl_vol_facts (

Table_name varchar2 (30 ),

Num_rows number,

Meas_dt date)

Tablespace platab

Storage (

Initialize 128 K

Next 128 K

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 Processing

1. 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 shold

-- Use the exact same value for next, which shoshould 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 ('& owner ')

Order by tablespace_name, segment_type, segment_name

2. existext. SQL

--

-- Existext. SQL

--

-- To Check Existing extents

--

-- 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

-- Sized extents, your free space is likely to become fragmented. If so,

-- This tablespace is a candidate for reorganizing.

--

-- 12/15/98

Select segment_name, segment_type

, Count (*) as nr_exts

, Sum (decode (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 owner = '& owner'

Minus

Select table_name

From all_constraints

Where owner = '& 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 = 'Disabled' 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 '& kname %'

And owner = '& owner' and uniqueness = 'nonunique'

Select C. constraint_name, I. tablespace_name, I. uniqueness

From all_constraints C, all_indexes I

Where C. Owner = upper ('& owner') and I. uniqueness = '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 | 'rebuilt'

, '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 owner = '& owner'

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. References

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 mod

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.