Responsibilities of Oracle database Administrator __ Database

The Oracle database system should be monitored regularly in the following manner:

?

(1). Daily operation Status of Oracle database, log files, backup, database space usage, system resource Usage check, find and solve problems. <?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>

(2). Every week to the spatial expansion of the database objects, data growth monitoring, the database to do health checks, the state of the database objects to check.

(3). Analyze table and index, check table space debris, look for the opportunity of database performance adjustment, make database performance adjustment, propose next space management plan, carry on a comprehensive check to Oracle database status.

Every day's work:

(1). Confirm that all instance are in normal condition.

Log in to all databases or routines to detect Oracle background processes:

$ps –ef|grep Ora

(2). Check the file system usage (remaining space). If the file system's remaining space is less than 20%, you need to remove unused files to free up space:

$DF –k

(3). Check the log files and trace files for errors in the alert and trace files.

Connect to each system you want to manage:

Using ' Telnet '

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

Use the Unix ' tail ' command to view the Alert_.log file, and if any new ora-errors are found, record and resolve.

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

How to Back up Rman:

Check the backup log of the Third-party Backup tool to determine if the backup was successful.

For export backup mode:

Check the exp log file to determine if the backup was successful

For other backup methods:

Check the appropriate log file

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

Select file_name from Dba_data_files where status= ' OFFLINE ';

(6). Check Table space usage:

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 to 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 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 generate system reports, or use Statspack to collect statistical data;

(9). Check database performance, record database CPU usage, IO, buffer hit ratio, etc.

Use commands such as Vmstat,iostat,glance,top.

(10). Daily problem handling.

Week's work:

(1). Monitoring the spatial expansion of database objects:

Find space-expanding database objects based on daily checks this week and take appropriate measures

--Delete historical data

---expanded table space

Alter tablespace add datafile ' xxxx.dbf ' size 10M;

---Adjust the storage parameters of a data object

Next extent

Pct_increase

(2). Monitor the growth of data volume

Find a database object with a rapid growth of records based on the daily checks this week and take appropriate measures

--Delete historical data

---expanded table space

Alter tablespace add datafile ' xxx.dbf ' size 100M;?

(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 for invalid database objects:

SELECT owner, object_name, object_type from dba_objects WHERE status= ' INVALID ';

(5). Check for 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 trigger

SELECT owner,trigger_name,table_name,status from dba_triggers WHERE status = ' DISABLED ';

The monthly work

(1). Analyze Tables/indexes/cluster

Analyze table estimate statistics sample percent;

(2). Check table space fragments

Analyze the database fragmentation according to the weekly check of this month, and find the appropriate workaround.

(3). Look for opportunities for database Performance tuning

Compare monitoring reports for database performance on a daily basis to determine if database performance needs to be adjusted

(4). Database Performance Tuning

If necessary, perform performance tuning

(5). Proposed Next space management plan

According to the weekly monitoring, proposes the improvement method of the spatial management

Oracle DBA Day-to-day management

Objective: This document contains very detailed information on the results of the daily, monthly, yearly running status of an even more Oracle database and the results of the check, and in the appendix of the document you will see all the checked, modified SQL and Pl/sql code.

Directory

1. Routine Maintenance procedures

A Check all instances that have been started

B Find some new warning logs

C Check to see if DBSNMP is running

D Check that the database backup is correct

E. Check that files backed up to tape are correct

F. Check the database for proper performance, adequate space and resources

G Copy the document log to the backed up database

H. To see the DBA user manual often

2. Evening Maintenance procedures

A Collect data from volumetric

3. Weekly Maintenance work

A Find the object that breaks the rule

B Find out if there is a security policy violation

C View the Sql*net log at the wrong place

D Archive all warning logs

E. Frequently visit the vendor's home page

4. Monthly Maintenance Procedure

A View the growth rate that is damaging to the database

B Review the tuning of previous database optimization performance

C To view I/O screen neck issues

D Review fragmentation

E. Future plan of implementation

F. View adjustment points and maintenance

5. Appendix

A Monthly Maintenance process

B Evening Maintenance process

C Week Maintenance Process

?

A Daily Maintenance Process

A To see if all instances are up

Make sure the database is available, write each instance to the log and run the daily report or run the test

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

Optional execution: View with the ' PROBE ' event in Oracle Manager

B Find a new warning log file

1. Connect each Operation management system

2. Use ' TELNET ' or comparable procedure

3. For each instance of management, regular implementation of the $oracle_base//bdump exercise

and enable it to fall back to the SID that controls the database.

4. At the prompt, use the ' TAIL ' command in UNIX to view the Alert_.log, or otherwise examine the warning log for the most recent period in the file.

5. If some of the previous ora_errors appear again, record it in the database recovery log and study them carefully, the database recovery log is in 〈file〉.

C View the operation of the DBSNMP

Check the ' DBSNMP ' process for each managed machine and log them into the journal.

In Unix, on the command line, type Ps–ef | grep DBSNMP, you will see 2 DBSNMP processes running. If not, restart DBSNMP.

D Check if the database backup was successful

E. Check that the backup tape document is successful

F. Check to see if sufficient resources are available for reasonable performance

1. Check that there is no space left in the table space.

For each instance, check that there is space in the table space to meet the day

of the expected needs. When the data already in the database is stable, the average growth of the data day

The number can also be calculated, the minimum remaining space to be able to at least meet the daily growth of data.

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

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

2. Check the rollback segment

The status of the rollback segment is generally online, except for some dedicated segments that are prepared for complex work, and the general state is offline.

A each database has a list of rollback segment names.

b You can use V$rollstat to query the current status of the online or offline rollback segment.

C for all rollback segments of the storage parameters and names, available

Dba_rollback_segs to query. But it's not as accurate as v$rollstat.

3. Identify some of the excessive growth

View the segments in the database that are out of resources or are growing too high, and the storage parameters for these segments need to be adjusted.

A to collect information on the size of the day data, you can use ' analyze5pct. SQL '. If you collect information every night, you can skip this step.

b Check the current range, available with ' NR. Extents. SQL '.

c) Query the size information for the current table.

D to query for information about the current index size.

(e) Query for Growth trends.

4. Determine the scope of the space.

If the next_extent of a range space object is larger than the maximum range that table space can provide, this affects the database's operation. If we find this goal, we can use ' ALTER tablespace coalesce ' to investigate its location, or add another data file.

A) Run ' Spacebound. SQL '. If all is normal, no rows will be returned.

5. Review the CPU, memory, network, hardware resources of the process of argument

A) Check CPU utilization and go to x:.htm =>system

METRICS=>CPU utilizes the page, the CPU maximum is 400, when the CPU occupies maintains

For a period of more than 350, we need to see and study the problems that arise.

G Copy archived logs to an alternate database

If there is an alternate database, the expectation that the appropriate archive logs will be replicated to the standby database

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

H. Frequent access to the DBA user manual

If possible, read extensively, including DBA manuals, industry magazines, newsgroups, or mailing lists.

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

Two Evening Maintenance process

Most database products will benefit from the running of the check process that is determined every night.