Routine work of Oracle 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 the 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, check table space fragments, find opportunities for database performance adjustment, adjust database performance, and propose the next space management plan. Pair

Perform a comprehensive check on the Oracle database status.
　　
Daily Work
　　
(1) confirm that all instances are logged on to all databases or routines, and 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') | '%'

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 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 corresponding 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
　　
Find the database objects with fast-growing records based on the daily check results of this week and take appropriate 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 to 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 on the results of daily, monthly, and annual running of one or more Oracle databases and the results of the check.

You will see all the check, modified SQL and PL/SQL code in the appendix.
　　
1. Daily maintenance process
A. check whether all instances have started
　　
Determine whether the database is available and 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. for each management instance, the $ oracle_base/<Sid>/bdump operation is often executed, and the SID of the control database can be returned.
　　
4. At the prompt, run the 'tail' command in UNIX to view alert _ <Sid>. Log, or use other methods to check the recent warning logs in the file.
　　
5. If some ora_errors appeared again before, record it to the database recovery log and carefully study them. The database recovery log is 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 Dbsnmp processes are 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 expected needs of the day. When the existing data in the database is stable, the daily data growth average

The number can also be calculated, and the minimum remaining space must at least meet the daily data growth.
　　
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) You can use dba_rollback_segs to query the storage parameters and names of all rollback segments. 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 increase the speed.
　　
A) You can use 'analyze5pct. SQL 'to collect the daily data size '. 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 larger than the maximum range provided by the tablespace, the operation of the database will be affected. If we find this target, we can use

'Alter tablespace coaleid' investigates its location or adds 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