This is the 8th day of my participation in the August More Text Challenge. For details, see:August is more challenging
The foreword 🌲 🌲
If you were given a new Oracle stand-alone database environment, what would you focus on as a DBA? This article discusses only the Linux host ~
📢 first of all, this article is not a standard answer, just some personal opinions, welcome 👏🏻 everybody complement perfect ~
First of all, of course, is to confirm the database is single machine or cluster mode! This article mainly explains the single machine database.
I. ⭐️ Host layer ⭐️
1. 📚 Host version and Oracle version
Host version:
cat /etc/system-release
cat /etc/redhat-release
Copy the code
sqlplus -version
opatch lspatches
Copy the code
2. 💻 Host hardware resources
These include CPU load, physical memory, and disk usage.
CPU load and memory:
top
free -m
Copy the code
Disk usage:
lsblk
fdisk -l
df -Th
Copy the code
3. 📒 Scheduled task crontab
Generally, a scheduled job will assign some backup policies or archive deletion policies, which can be viewed by using crontab:
crontab -l
Copy the code
4. 🌿 Check the Hosts file and network configuration
cat /etc/hosts
ip addr
nmcli connection show
Copy the code
5. 🍄 Check the system parameter file
cat /etc/sysctl.conf
Copy the code
6. 🌻 check the rc.local file
The rc.local file is used to configure the script for automatic startup upon startup. Generally, the transparent large page is closed or the Oracle database is automatically started upon startup.
cat /etc/rc.local
Copy the code
7. 🍁 Configure environment variables
View the configuration of environment variables to get familiar with the environment.
cat ~/.bash_profile
cat /home/oracle/.bash_profile
Copy the code
8. 🌵 Check system services
systemctl status firewalld.service
getenforce
cat /proc/cmdline
cat /etc/sysconfig/network
Copy the code
Two, 💫 database level 💫
1. 🍔 View database instances and listeners
ps -ef|grep smon
su - oracle
lsnrctl status
Copy the code
2. 🍖 Database tablespace usage
sqlplus / as sysdba
col TABLESPACE_NAME for a20
select tbs_used_info.tablespace_name,
tbs_used_info.alloc_mb,
tbs_used_info.used_mb,
tbs_used_info.max_mb,
tbs_used_info.free_of_max_mb,
tbs_used_info.used_of_max || The '%' used_of_max_pct
from (select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) alloc_mb,
round((a.bytes_alloc - nvl(b.bytes_free,
0)) / 1024 / 1024) used_mb,
round((a.bytes_alloc - nvl(b.bytes_free,
0)) * 100 / a.maxbytes) used_of_max,
round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,
0)) / 1048576) free_of_max_mb,
round(a.maxbytes / 1048576) max_mb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
order by tbs_used_info.used_of_max desc;
Copy the code
3. 🍢 check the RMAN backup status
rman target /
list backup;
sqlplus / as sysdba
col status for a10
col input_type for a20
col INPUT_BYTES_DISPLAY for a10
col OUTPUT_BYTES_DISPLAY for a10
col TIME_TAKEN_DISPLAY for a10
select input_type,
status,
to_char(start_time,
'yyyy-mm-dd hh24:mi:ss'),
to_char(end_time,
'yyyy-mm-dd hh24:mi:ss'),
input_bytes_display,
output_bytes_display,
time_taken_display,
COMPRESSION_RATIO
from v$rman_backup_job_details
where start_time > date '2021-07-01'
order by 3 desc;
Copy the code
4. 🍡 Check the redundancy of control files
Check whether the number and location of control files are in the redundant state.
sqlplus / as sysdba
show parameter control_files
select name from v$controlfile;
Copy the code
5. 🍭 Check parameter files
View the database parameter file to check whether the parameters are used properly.
sqlplus / as sysdba
show parameter spfile
create pfile='/home/oracle/pfile.ora' from spfile;
strings /home/oracle/pfile.ora
Copy the code
6. Check whether 🍬 archiving and flashback are enabled
sqlplus / as sysdba
archive log list
select open_mode,log_mode,flashback_on,force_logging from v$database;
Copy the code
7. 🍗 Check online logs and switchover frequency
View online log size:
set line222
col member for a100
select f.group#,f.member,l.sequence#,l.bytes/1024/1024,l.archived,l.status,l.first_time
from v$logfile f,v$log l
where f.group# = l.group#
order by f.group#,f.member;
Copy the code
col day for a30
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'00', 1, 0)) H00, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'01', h01-2 are 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'02', 1, 0)) H02, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'03', 1, 0)) H03, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'04'H04, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'05', 1, 0)) H05, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'06', 1, 0)) H06, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'07', 1, 0)) H07, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'08', 1, 0)) H08, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'09', 1, 0)) H09, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'10'H10, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'11'H11, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'12'H12, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'13'H13, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'14'H14, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'15', 1, 0)) H15, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'16'H16, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'17', 1, 0)) H17, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'18'H18, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'the', 1, 0)) H19, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'20'H20, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'21', 1, 0)) H21, SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'22'H22, 1, 0)), SUM (DECODE (SUBSTR (TO_CHAR (first_time,'MM/DD/RR HH24:MI:SS'), 10, 2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM
v$log_history a where SYSDATE - first_time < 35
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5) order by 1;Copy the code8. 🍕 View the database character set
select * from nls_database_parameters;
Copy the code
9. 🍯 Check invalid objects
SELECT owner,object_name,object_type,status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY owner,object_name;
Copy the code
10. 🍋 Check partition table objects
set line222
col high_value for a100
select t2.TABLE_OWNER,t1.table_name, t1.max_partition_name, t2.high_value
from (select table_name, max(partition_name) as max_partition_name
from dba_tab_partitions
group by table_name) t1,
(select TABLE_OWNER,table_name, partition_name, high_value
from dba_tab_partitions
where tablespace_name not in ('SYSAUX'.'SYSTEM')) t2
whereT1. table_name = t2.table_name and t1.max_partition_name = t2.partition_name order by 1,2;Copy the code
Third, ❄️ report level ❄️
Oracle provides awR, ASH, and AWRSQRPT reports to clearly understand the current database status.
1. 🍉 AWR report
AWR contains detailed collection of database running information and is often used to analyze collection performance issues.
sqlplus / as sysdba @? /rdbms/admin/awrrpt.sqlCopy the codeYou can use the above command to generate AWR report. During the process, you need to fill in the generated report type and capture time. Please query by yourself for specific usage.
2. 🍊 ash report
Ash captures much more detailed information than AWR reports, can be accurate to the minute, and is commonly used.
sqlplus / as sysdba @? /rdbms/admin/ashrpt.sqlCopy the codeThe report generation mode is as follows. The report generation mode is optional. By default, the report generation mode is from the current time to 15 minutes ago.
3, 🍒 awrSQrpt report
The SQL_ID is used to analyze the report when a single SQL item has a performance problem.
sqlplus / as sysdba @? /rdbms/admin/awrsqrpt.sqlCopy the codeYou need to enter the time period and SQL_ID to obtain the related SQL report.
4. 🍑 SQLTRPT report
The aWRSQRPT report is used in conjunction with the AWRSQRPT report to obtain SQL optimization advice from Oracle. Indexes and profiles are recommended.
sqlplus / as sysdba @? /rdbms/admin/sqltrpt.sqlCopy the codeAll you need is the SQL_ID.
5. 🍍 addMRpt report
Addmrpt is a report generated by Oracle by automatically diagnosing awR reports.
sqlplus / as sysdba @? /rdbms/admin/addmrpt.sqlCopy the codeFor reference only, the meaning of real help is not great. The process requires input of a time range.
6. 🌽 Health check report
This type of health check report, which is usually a report produced by the script written for the individual to execute, is defined by the individual and usually contains the required information. Of course, Oracle official also provides a complete database report generation method, here do not do too much introduction, need friends can contact me to obtain.
⚡️ at the end ⚡️
Through these checks, I believe that you have a general understanding of the new contact with the database system, then, only need to slowly in-depth analysis, and then develop a set of operation and maintenance specifications in line with the actual situation.
ヾ (◍ ° ∇ ° ◍) ノ ゙
That’s the end of this sharing
If you think the article is helpful to you, like, collect, follow, comment, one key four support, your support is the biggest motivation for my creation.