oracle| Admin | data | database
Responsibilities of Oracle Database administrators
Oracle database administrators should regularly monitor Oracle database systems as follows:
(1). Daily operation Status of Oracle database, log files, backup status, data
The use of the library space, the use of system resources to check, find and solve
Problem.
(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 the table and index on a monthly basis, check the table space debris, look for the database
The opportunity of performance adjustment, make the database performance adjustment, propose the next space management
Plan. Perform a comprehensive review of the 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 remaining space of the file system is less than 20%, delete the 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 that needs to be managed
? Using ' Telnet '
? For each database, CD to Bdump directory, usually $oracle_base/<sid>/bdump
? Use the Unix ' tail ' command to view Alert_<sid>.log files
? 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 a "online" data file, and do 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 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 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 Generation System report
Or use Statspack to collect statistical data
(9). Check database performance, log database CPU usage, IO, buffer hit ratio, etc.
Using commands such as Vmstat,iostat,glance,top
(10). Daily problem handling.
The work of the week
(1). Spatial expansion of control database objects
Based on this week's daily check, find space to expand the database object quickly and take the phase
Measures to be taken
--Delete historical data
---expanded table space
Alter tablespace <name> add datafile ' <file> ' size <size>
---Adjust the storage parameters of a data object
Next extent
Pct_increase
(2). Monitor the growth of data volume
According to this week's daily check, we find the database object that the record quantity grows very fast, and collect
Take the appropriate measures
--Delete historical data
---expanded 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 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 <name> 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 solution
(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 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.
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 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 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
Use ' ALTER tablespace coalesce ' to investigate its location, or add additional 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.
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.
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 table to measure the volume.
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.
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 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 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.
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 and compare previous reports to identify harmful development trends.
C To view I/O screen neck issues
1. View the activity of the prior database file, compare the previous output to judge the trend that may lead to the problem of screen neck.
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 the addition of new resources or the expected 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
