oracle| Admin | data | 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.
(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;?
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.
1. For each object given a tablespace, the next_extent size is the same, such as 12/14/98, the default next_extent Datahi is 1g,datalo 500MB, and INDEXES is 256MB.
A) Check the next_extent settings and use ' Nextext '. SQL '.
(B) Check existing extents and use ' existext '. SQL '.
2. All tables should have a unique primary key
A view those tables that do not have a primary key, available ' NO_PK. SQL '.
b Find those primary keys that are not functioning, available ' DIS_PK. SQL '.
c all indexed primary keys are unique and can be ' NONUPK. SQL ' to check.
3. All indexes are to be placed in the index table space. Run ' Mkrebuild_idx. SQL '
4. The plan between different environments should be the same, especially between the test environment and the finished product environment.
(a) Check that the data types in different 2 operating environments are consistent and can be ' DATATYPE. SQL '.
B in 2 different instances to find the different points of the object, available ' Obj_coord. SQL '.
c A better approach is to use a tool that looks like a tool for the software's program manager.
B See if there are any issues that jeopardize your security policy.
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.
