Going deep into Oracle-DBA's daily work and responsibilities-[1]

The daily work and responsibilities of DBAs in Oracle --- [1] The following summary is a summary written in "Deep Oracle", which corresponds to the amount of funds learned. 1. Let's take a look at Oracle's learning route: 2. learning basics: I have learned basic Oracle knowledge and have a general understanding of Oracle. 3. Objective: To give students who have learned the basics of Oracle a better understanding of the Oracle mechanism. Understand the work and capability requirements of OracleDBA. We have a further in-depth understanding of Oracle in terms of technology. 4. expected goals: to have a deep understanding of Oracle technology and strive to reach the level of intermediate Oracle DBA 5. course introduction: lesson 1 DBA daily work content and responsibilities Lesson 2 Oracle memory structure and background processes [basic components of Oracle instances] Lesson 3 redo log and log mining [important components of Oracle, record data changes and perform log mining] Lesson 4 rollback [very important, related transactions, etc, important Aspect] Lesson 5 Oracle Data Structure [Composition of Oracle data blocks] Lesson 6 ASM management [solved many Oracle problems, solved the storage of tablespaces, etc.] Lesson 7 Oracle backup recovery and data migration. Lesson 8 Rman backup, recovery and management Lesson 9 flash back mechanism and usage Lesson 10 transaction and data consistency (I) [important] Lesson 11th transaction and data consistency (II) 12th Oracle Security Management and Audit (1) 13th Oracle Security Management and Audit (2) 14th common tools SQL * loader and Data Pump 15th Oracle character set 16th Data gaurd and stream replication Introduction 6. Duties of Oracle DBA▶System Construction Period: Database Design Database Modeling database security-[backup solution, Disaster Tolerance solution] database performance▶O & M period: data security-[backup and disaster tolerance solutions] availability of databases Performance Analysis of routine faults handling Database Upgrade and transformation-[patching, upgrading and Transformation] 7. Oracle DBA daily Work▶Common Database monitoring-Monitoring of tablespace (disk, ASM) Capacity popular practices, a whole block of storage [one cabinet, disk Array] --- ASM --- tablespace --- automatic expansion of data files ---> monitor the usage of ASM [relatively easy, flexible, and dynamic expansion of space]. -The alarm log file (alert_sid.ora) is used to write a program for Automatic Analysis of files, so as to generate real-time alerts for alert errors.▶Custom monitoring metrics-data growth rate of a tablespace-data growth rate of an object (table, index. -Congestion (v $ lock)-CPU-I/O-memory (SGA + PGA)-session 8, forming good habits▶DBAs should use the SQLPlus tool more-SQLPlus is the most basic configuration of Oracle. In most cases, it exists, and DBAs themselves are the ones who can solve the problem by accident, be familiar with Oracle internal views [including tables and views starting with V $ and DBA _] 9. SQL statements commonly used by Oracle DBAs should be executed as DBA.

▶Tablespace size [SQL] select tablespace_name, sum (user_bytes) from dba_data_files group by tablespace_name union select tablespace_name, sum (user_bytes) from dba_temp_files group by tablespace_name/[SQL] select tablespace_name, sum (user_bytes) from dba_data_files group by tablespace_name union select tablespace_name, sum (user_bytes) from dba_temp_files group by tablespace_name/▶Data Object size (space occupied by the Data Object) [SQL] select segment_name, segment_type, bytes from user_segments where segment_name in ('T', 'idx _ t ') /[SQL] select segment_name, segment_type, bytes from user_segments where segment_name in ('T', 'idx _ t ')/▶Session current SQL statement [SQL] select sid, status, SQL _id from v $ session where sid = 68/[SQL] select sid, status, SQL _id from v $ session where sid = 68/▶Process ID corresponding to the session [SQL] select spid from v $ process where addr = (select paddr from v $ session where sid = 68) /[SQL] select spid from v $ process where addr = (select paddr from v $ session where sid = 68 )/▶Session blocking [SQL] select sid, lmode, type, request, block from v $ lock where type in ('tx ', 'Tm ') order by 1, 3/[SQL] select sid, lmode, type, request, block from v $ lock where type in ('tx ', 'Tm') order by 1, 3/▶SQL Execution Plan-set autotrace [SQL] set autotrace trace exp; select * from dual; [SQL] set autotrace trace exp; select * from dual; -explain plan [SQL] explain plan for select * from dual; select * from table (dbms_xplan.display); [SQL] explain plan for select * from dual; select * from table (dbms_xplan.display );▶Trace SQL statement [SQL] alter session set SQL _trace = true; select * from dual; [SQL] alter session set SQL _trace = true; select * from dual;▶Another method is [SQL] alter session set events '10046 trace name context forever, level 12'; select * from dual; [SQL] alter session set events '10046 trace name context forever, level 12'; select * from dual;