Oracle DBA daily management

Oracle DBA daily management of modification: 2000/8/23 Author: Thomas B. Cox, with Christine Choi Purpose: This document has very detailed information on a record even more ORACLE database daily, monthly, yearly The results of the running status and the results of the check, you will see all checks, modified SQL and PL / SQL code in the appendix of the document. Modifying notes: 1.1 In Steve Denunzio 'existext.sql' The type 1.2 fixed type 1.3 plus GNU public license; adding PCTINCR 01.4 in rebuilding index, adding a newest point, is from Geocities's homepage of. Http://www.geocities.com/tbcox23 directory 1. Daily maintenance procedure A. Check all instances B. Find some new warning log C. Check if dbsnmp is running D. Check if the database backup is correct. Check if the file backup into the tape is correct F. Check if the performance of the database is normal, there is enough space and resource G. Copy the document log to the backup database H. To see the DBA user manual 2. Night maintenance procedure A. Collect data of Volumetric 3. Maintain work a weekly weekly. Find the Objectb that destroyed rules. Find if there is a problem with violations of security policies. View the Error Local SQL * NET Log D. Archive all warning logs E. Regularly visited the supplier's home page 4. month maintenance procedure A. Viewing growth speed B for hazards to databases. Review the adjustment of previous database optimization performance. View I / O screen neck problem D. Review FragmentationE. In the future implementation plan F. View adjustment points and maintenance 5. Appendix A. Month maintenance process B. The evening maintenance process C. Week maintenance process 6. References

One. Day maintenance process A. See if all instances have been determined to be available, write each instance to the log and run the day report or run the test file. Of course, some operations We hope that it can run automatically. Optional Execution: Use the 'probe' event in the Oracle Manager to view B. Find a new warning log file 1. Connect each operational management system 2. Use the 'telnet' or compareable program 3. For each management instance, often execute $ oracle_base / / bdump operation, and make it Roll back to the SID of the Control Database. 4. Under the prompt, use the 'tail' command in UNIX to view the alert_ .log, or use other ways to check the recent warning log 5 in the file in the file. If some Ora_errors appeared again, record it To the database recovery log and study them carefully, this database recovery log in C. Check the run of DBSNMP Check the 'dbsnmp' process of each managed machine and record them into the log. In UNIX, in the command line, type PS-EF | GREP DBSNMP, see 2 DBSNMP processes are running. If not, restart dbsnmp. D. Check if the database backup is successful. Check if the backup tape document is successful. Check if there is enough resource for reasonable performance 1. Check if there is any remaining space in the table space. For each instance, check whether there is a remaining space in the table space to meet the expected needs of the day. When the data existing in the database is stable, the average of the daily growth can also be calculated, and the smallest remaining space can meet at least the growth of daily data. A) Run 'Free.SQL' to check the remaining space of the table space. B) Run 'Space.sql' to check the remaining space percentage in the table space 2. Check the state of the rollback segment to the rollback segment. In addition to some dedicated segments prepared for complex work, it is generally offline. a) Each database has a list of rollback segments. b) You can use V $ ROLLSTAT to query the current state of online or offline rollback segments .c) Use dba_rollback_segs to query for all returns of storage parameters and names. But it is not as accurate as V $ ROLLSTAT. 3. Identify some excessive growth to view a segment that exceeds resources or excessive growth rates in the database, and the storage parameters of these segments need to be adjusted. a) Collect information on day data, you can use 'analyze5pct.sql'. If you collect every night, you can skip this step. b) Check the current range and use 'nr.extents.sql'.

c) Query the size information of the current table. d) Query information about the current index size. e) query growth trend. 4. Determine the range of space. If the NEXT_EXTENT of the range space object is larger than the maximum range that can be provided by the table space, this will affect the run of the database. If we find this goal, you can investigate its location with 'ALTER TABLESPACE COALESCE', or add additional data files. A) Run 'SpaceBound.sql'. If it is normal, you will not return any rows. 5. Review the process of CPU, memory, network, and hardware resource arguments. When the occupation of the CPU has been more than 350, we need to view and study problems. G. Copy the archive log into the standby database If there is an alternate database, copy the appropriate archive log to the desired location of the alternate database, save the most recent data in the standby database. F. Review DBA User Manual frequently, if possible, a wide range of reading, including DBA manuals, industry magazines, newsgroups, or mailing lists. two. Database products in the evening maintenance process will benefit from the operation of the inspection process per day. A. Collect Volumetric Data 1. Analyze the plan and collect data more accurate analysis and save the results. a) If you don't do this now, use 'mk volFact.sql' to create a meter of the measurement volume. b) Collect information on the size of the evening, use 'analyze comp.sql'. c) Collect statistics and use 'pop vol.sql'. d) Check data, possible words, per week, or every month when free. I used MS Excel and ODBC to check the growth of data and charts. Weekly maintenance process A. Find the destroyed target 1. For each given object, NEXT_EXTENT size is the same, such as 12/14/98, the default Next_EXTENT DataHi is 1G, Datalo is 500MB, indexes is 256MB. A) Check the setting of Next_extent, available 'NEXTEXT. SQL '. B) Check existing extents, available 'Existext. SQL '. 2. All tables should have a unique primary key a) See those tables There are no primary keys, and 'NO_PK.SQL' can be used. b) Find that the primary key is not played, you can use 'DIS_PK.SQL'. c) All primary keys for indexes are unique, available in 'Nonupk. SQL 'to check. 3. All indexes are placed in the index table space. Run 'mkrebuild_idx. SQL'4. The plan between different environments should be the same, especially between test environment and finished environment.

The plan should be the same. a) Check if the data type in different 2 operational environments is consistent, you can use 'Datatype.sql'. b) Looking for different points of objects in 2 different instances, you can use 'obj_coord.sql'. c) Better approach is to use a tool that is like a tool for seeking software. B. Check if there is a problem that harms the security policy. C. View the error SQL * NET log. 1. Client log. 2. Server-end logs. D. . Archive all warning logs E. Homepage 1. Oracle supplier http://www.racle.comHttp: //technet.Oracle.com/supporthttp: //www.oramag.com2. Quest SoftwareHttp: / / www.quests.com3. Sun MicrosystemShttp://www.sun.com IV. Month maintenance process A. Viewing growth rates for databases 1. From previous records or changes in the growth of segments in the previous record or change to determine the growth of B. B. Review the adjustment of previous database optimization performance 1. Review the adjustment point of the general Oracle database, compare the previous report to determine harmful development trends. C. View I / O Screen Neck Problem 1. View the activity of the previous database file, compare the previous output to determine the trend that it is possible to cause the screen neck problem. D. Review FragmentationE. Planning Databases Future Performance 1. Compare the Usage of Oracle and Operations CPUs, memory, networks, and hard drives to determine trends in some resource compensations in recent times 2. When the system will exceed the range Trend as a service level agreement to see F. Complete adjustment and maintenance 1. Make changes to the need to avoid the need for system resources, including adding new resources or downsworked expected. Fives. Appendix A. Daily procedure

---- Free.SQL ---- To Verify Free Space in TableSpaces - Minimum Amount of Free Space - Document Your Thresholds: - = 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_chunksFROM dba_free_spaceGROUP BY tablespace_name1. Space.sql ---- space.sql- --- To check free, pct_free, and allocated space within a tablespace ---- 11/24 / 98SELECT 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_freeFROM (SELECT tablespace_name, sum (blocks) AS sum_alloc_blocksFROM dba_data_filesGROUP 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_blocksFROM dba_free_spaceGROUP BY tablespace_name) WHERE tablespace_name = FS_TS_NAME2. Analyze5pct.sql ---- Analyze5Pct.sql ---- To Analyz ZE 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 / 98BEGINDBMS_UTILITY.AALYZE_SCHEMA ('& Owner', 'Estimate', NULL, 5); END; / 3. NR_EXTENTS.SQL ---- NR_EXTENTS.SQL --- - to find out any object reaching - extents, and manually upgrade it to allow unlimited-- max_extents (thus only objects we * expect * to be big-- are allowed to become big) ---- 11/30 / 98SELECT 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 MBFROM dba_extents e, dba_segments sWHERE e.segment_name = s.segment_nameGROUP BY e.owner, e.segment_type, e.segment_name, s.max_extentsHAVING count (*)> & THRESHOLDOR ((s.max_extents - count (*)) <&& THRESHOLD) ORDER BY count (*) DESC4. 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 .-- The use coalesce (Alter TableSpace) coalesce;) .-- Lastly, add another datafile to the tablespace if needed .---- 11/30 / 98SELECT a.table_name, a.next_extent, a.tablespace_nameFROM all_tables a, (SELECT tablespace_name, max (bytes ) AS BIG_CHUNK

FROM dba_free_spaceGROUP BY tablespace_name) fWHERE f.tablespace_name = a.tablespace_nameAND a.next_extent> f.big_chunkB night handler 1. mk_volfact.sql ---- mk_volfact.sql (only run this once to set it up;. Do not run it nightly) ---- -! Table UTL_VOL_FACTSCREATE TABLE utl_vol_facts (table_name VARCHAR2 (30), num_rows NUMBER, meas_dt DATE) TABLESPACE platabSTORAGE (INITIAL 128kNEXT 128kPCTINCREASE 0MINEXTENTS 1MAXEXTENTS unlimited) / - Public SynonymCREATE PUBLIC SYNONYM utl_vol_facts FOR & OWNER..utl_vol_facts / - Grants for UTL_VOL_FACTSGRANT SELECT ON utl_vol_facts TO public / 2 analyze_comp.sql ---- analyze_comp.sql - BEGINsys.dbms_utility.analyze_schema ( '& OWNER', 'COMPUTE');. END; / 3 pop_vol.sql-. --- pop_vol.sql - insert into utl_vol_factsselect table_name, NVL (num_rows, 0) as num_rows, trunc (last_analyzed) as meas_dtfrom all_tables - or just user_tableswhere owner in ( '& oWNER') - or a comma-separated list of Owners / commit / c. weekly handler 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 should-- use the exact same value for NEXT, which should also be the tablespace's-- default value for NEXT .---- This tells us what the setting for NEXT is for these objects today .---- 11/30 / 98SELECT segment_name, segment_type, ds.next_extent as Actual_Next, dt.tablespace_name, dt.next_extent as Default_NextFROM dba_tablespaces dt, dba_segments dsWHERE dt.tablespace_name = DS.TABLESPACE_NAMEAND DT.NEXT_EXTENT! = DS.NEXT_EXTENTAND DS.OWNER = Upper ('& Owner') Order by TableSpace_name, segment_type, segment_name2. 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 / 98select segment_name, segment_type, count (*) as nr_exts, sum (DX.BYTES, DT.NEXT_EXTENT, 0, 1 )) as nr_illsized_exts, dt.tablespace_name, dt.next_extent as dflt_ext_sizeFROM dba_tablespaces dt, dba_extents dxWHERE dt.tablespace_name = dx.tablespace_nameAND dx.owner = '& OWNER'GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent3. No_pk.sql ---- no_pk.sql ---- To find tables without PK constraint ---- 11/2 / 98SELECT table_nameFROM all_tablesWHERE owner = '& OWNER'MINUSSELECT table_nameFROM all_constraintsWHERE 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, statusFROM all_constraintsWHERE 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 / 98SELECT index_name, table_name, uniquenessFROM all_indexesWHERE index_name like '& PKNAME%' AND owner = '& oWNER' AND uniqueness = 'NONUNIQUE'SELECT c.constraint_name, i.tablespace_name, i.uniquenessFROM all_constraints c, all_indexes iWHERE c.owner = UPPER (' & oWNER ') AND i.uniqueness =' ​​NONUNIQUE'AND c. constraint_type = 'P' AND i.index_name = c.constraint_name6. mkrebuild_idx.sql ---- mkrebuild_idx.sql ---- Rebuild indexes to have correct storage parameters ---- 11/2 / 98SELECT 'alter index' || Index_name || 'Rebuild', 'TableSpace Indexes Storage' || '(Initial 256 K Next 256 K Pctincrease 0);' from all_indexeswhere (Tab Lespace_name! = 'indexes'or next_extent! = (256 * 1024)) And Owner =' & Owner '/ 7. DataType.sql

---- datatype.sql ---- To check datatype consistency between two environments ---- 11/30 / 98SELECTtable_name, column_name, data_type, data_length, data_precision, data_scale, nullableFROM all_tab_columns - first environmentWHERE owner = '& OWNER'MINUSSELECTtable_name , column_name, data_type, data_length, data_precision, data_scale, nullableFROM all_tab_columns @ & my_db_link - second environmentWHERE owner = '& OWNER2'order by table_name, column_name8 obj_coord.sql ---- obj_coord.sql ---- To find out any difference in. .. objects between two instances ---- 12/08 / 98SELECT object_name, object_typeFROM user_objectsMINUSSELECT object_name, object_typeFROM user_objects @ & my_db_link six References 1. Loney, Kevin Oracle8 DBA Handbook2 Cook, David Database Management from Crisis to Confidence [http: // Www.orapub.com/]3. Cox, Thomas B. The Database Administration Maturity Model