Oracle
Oracle DBA Day-to-day management
Modified: 2000/8/23
Author: Thomas B. cox,with Christine Choi
Objective: This document has very detailed information on one or more Oracle databases per day, monthly,
The results of the yearly running status and the results of the check, in the appendix of the document you will see all the checks, the modified SQL
and Pl/sql code.
Modifying notes: 1.1 types identified in the ' Existext.sql ' of Steve DeNunzio
1.2 Fixed type
1.3-plus GNU public License, adding PCTINCR 0 to rebuild Index
1.4 added a new point of view, from Geocities's homepage.
http://www.geocities.com/tbcox23
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
6. Reference documents
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/<sid>/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_<sid>.log, or
Other ways to check the warning log for the most recent period in a file
5. If some of the previous ora_errors appear again, log it to the database
Recovery logs and study them carefully, this 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 back 2
The DBSNMP process is 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 smallest remaining space to be able to at least meet the daily increase in data
Long.
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 a few dedicated
Segment, its general state is off-line.
A each database has a list of rollback segment names.
b You can use V$rollstat to query the current shape of the online or offline rollback segment
State.
C for all rollback segments of the storage parameters and names, available
Dba_rollback_segs to query. But it's not as v$rollstat.
Accurate.
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
Number needs 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 greater than the maximum fan of the table space can provide
Circumference is larger, then this will affect the operation of the database. If we find this goal, we can
To investigate its position with the ' ALTER tablespace coalesce ', or add another
Data files.
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:\web\phase2\default.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.
F. Frequent access to the DBA user manual
If possible, read extensively, including DBA Manual, Industry magazine, News
Group or mailing list.
Two Evening Maintenance process
Most database products will benefit from the running of the check process that is determined every night.
A. Collection of volumetric data
1. Analysis Planning and data collection
More accurate analysis of the calculations and save the results.
A If you're not doing this now, use ' MK volfact. SQL ' to create a measuring volume
Table.
b collect information on the size of the evening data, using ' ANALYZE COMP. SQL '.
C Collect statistical results, using ' POP Vol. '. SQL '.
(d) Check the data at your leisure time, if possible, weekly or monthly.
I'm using MS EXCEL and ODBC joins to check the growth of data and graphs
Three Weekly Maintenance process
A Find a damaged target
1. The size of the next_extent is the same for each object given the table space, as
12/14/98, the default next_extent Datahi is 1g,datalo to 500MB,
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
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.
The plan should be the same.
A to check the consistency of data types in different 2 running environments, available
' DATATYPE. SQL '.
B in 2 different instances to find the different points of the object, available
' Obj_coord. SQL '.
c The better approach is to use a tool that looks like a program manager for the software
Tools.
B See if there are any issues that jeopardize your security policy.
C View the Sql*net log for the error.
1. Client's log.
2. Server-side logs.
D. Archive all warning logs
E. Suppliers ' homepage
1. ORACLE Suppliers
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
Four Monthly Maintenance process
A View the growth rate that is damaging to the database
1. Review of the changes in the section from previous records or reports to determine the harm caused by segment growth
B Review the tuning of previous database optimization performance
1. Review the adjustment points of the general Oracle database to compare previous reports to identify harmful developments
Trend.
C To view I/O screen neck issues
1. View the activity of the prior database file, compare the previous output to judge the possible result of the screen neck
The trend of the problem.
D Review fragmentation
E. Planning the future performance of a database
1. Compare Oracle and operating system CPU, memory, network, and hard disk utilization to
To determine the trend of some resource contention in the near future.
2. When the system will be out of scope to the performance trend as a service level agreement to see
F. Complete adjustment and maintenance work
1. Make the changes meet the need to avoid contention for system resources, which includes adding new resources or making the expected
of downtime.
Five Appendix
A. Routine procedures
--
--Free.sql
--
--to Verify free spaces in tablespaces
--minimum Amount of free spaces
--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 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 performing the overnight
--collection of volumetric data)
--
--11/30/98
BEGIN
Dbms_utility.analyze_schema (' &owner ', ' estimate ', NULL, 5);
End;
/
3. Nr_extents.sql
--
--Nr_extents.sql
--
---to find the any object reaching <threshold>
--extents, and manually upgrade it to allow unlimited
--Max_extents (thus only objects, we *expect* to being 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), ' 999,999.90 ') 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, 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 A,
(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. per night processing procedures
1. Mk_volfact.sql
--
--Mk_volfact.sql (only run this once to set it up; does 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 (
INITIAL 128k
NEXT 128k
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 procedures
1. Nextext.sql
--
--Nextext.sql
--
--To find tables that don ' t match the tablespace default for NEXT extent.
--The implicit rule this is this every table in a given tablespace should
--Use the exact same value for NEXT, which should also is the tablespace ' s
--Default value for NEXT.
--
--This tells us what to the setting for NEXT are for all 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 the each object's extents differ in the size from
--The Tablespace ' s default size. If This is shows a lot of different
--sized extents, your free spaces are 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
--
---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
--
---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 the 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 ' &pkname% '
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 | | ' Rebuild '
, ' 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--the 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
Six. Reference documents
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 Model
