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
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
