Summary of knowledge points of daily maintenance of Oracle Database

Time:2020-10-29

First of all, the system tables provided by different versions of the database will be different. You can view the tables provided by the database of this version according to the data dictionary
like this:
select * from dict where table_name like ‘%SESSION% ‘;
You can find some tables, and then according to these tables, you can get the session information.

This is to query the session currently in operation:

SELECT SID, SERIAL#, STATUS, USERNAME, SCHEMANAME, OSUSER,TERMINAL, MACHINE,
PROGRAM, A.NAME FROM V$SESSION S, AUDIT_ACTIONS A WHERE S.COMMAND = A.ACTION;

1、 View database connection

DBA should regularly check the database connection to see whether the number of sessions established with the database is normal. If too many connections are established, the database resources will be consumed. At the same time, some “hung” connections may need to be cleaned up manually by DBAs.

The following SQL statement lists the sessions established by the current database:

select sid,serial#,username,program,machine,status

from v$session;

Among them,
Sid session ID number;
Serial ා the serial number of the session. Together with the SID, it is used to uniquely identify a session;
Username is the user name of the session;
Program: what tool is used to connect this session to the database;
Status the status of the current session. Active indicates that the session is performing some tasks, and inactive means that the current session has not performed any operations;
 

If the DBA wants to manually disconnect a session, execute:

alter system kill session ‘SID,SERIAL#’;

Note that the sessions with SIDS from 1 to 7 (the username column is empty) in the above example are background processes of Oracle. Do not perform any operations on these sessions.

2、 Common commands

Select count (*) from V $session
Select count (*) from V $session where status =? Active '? Number of concurrent connections
Show parameter processes? Maximum connection
Alter system set processes = value scope = SPFILE; restart database and modify connection

1: How to view the total number of users in Oracle
select * from all_users;

2: View the current number of Oracle connections
How to check the current connection number of oracle? Just use the following SQL statement to query it.

select * from v$session where username is not null

Select username, count (username) from V $session where username is not null group by username ා 
Select count (*) from V $session
Select count (*) from V $session where status =? Active '? Number of concurrent connections
Show parameter processes? Maximum connection
alter system set process

3: List the sessions established by the current database:

select sid,serial#,username,program,machine,status from v$session;

3、 Oracle warning log file monitoring
During the operation of Oracle, the warning log file (alert_ SID.log )Record some running conditions of the database in
 startup and shutdown of database, non default parameters during startup;
 database redo log switch, record the time of each switch, and if the checkpoint operation is not completed, it will record the reason why it cannot be switched;
&#Some operations on the database, such as creating or deleting table spaces and adding data files;
&#Database errors, such as insufficient table space, bad block, internal database error (ora-600)

DBA should check the log file regularly and deal with the problems found in the log
Problem solving
Check the initialization parameter file if the startup parameters are not correct
Because the checkpoint operation or archive operation is not completed, the redo log cannot be switched. If this happens frequently, we can consider increasing the redo log file group, and find ways to improve the efficiency of checkpoint or archive operation;
Someone deleted the table space without authorization. Check whether the password is too simple. If necessary, revoke the system permissions of some users
If there is a bad block, check whether it is a hardware problem (for example, there is a bad block in the disk itself). If not, check that there is a bad block in the database object and rebuild the object
The table space is not enough. Add data files to the corresponding table space
If ora-600 appears, check the corresponding TRC file according to the contents of the log file. If it is an Oracle bug, it is necessary to timely patch it

4、 Database table space usage monitoring (Dictionary managed table space)

After the database has been running for a period of time, due to the continuous creation and deletion of objects in the table space, a large number of fragments will be generated in the table space. DBA should know the fragmentation and available space of the table space in time to decide whether to reorganize the fragments or add data files to the table space.


select tablespace_name,
count(*) chunks ,
max(bytes/1024/1024) max_chunk
from dba_free_space
group by tablespace_name;

The SQL above lists the free blocks of each table space in the database as follows:
TABLESPACE_NAME CHUNKS MAX_CHUNK
——————– ———- ———-
INDX 1 57.9921875
RBS 3 490.992188
RMAN_TS 1 16.515625
SYSTEM 1 207.296875
TEMP 20 70.8046875
TOOLS 1 11.8359375
USERS 67 71.3671875

Among them, the chunks list shows how many free blocks are available in the table space (each free block is composed of some consecutive Oracle data blocks). If there are too many such free blocks, such as more than 100 on each data file, the fragmentation of the table space is more serious. You can try to join adjacent fragments of the table space with the following SQL command:

Alter tablespace: coalesce;

Then execute the SQL statement to view the table space fragmentation to see whether the table space fragmentation has been reduced. If there is no effect and the fragmentation of the table space has seriously affected the operation of the database, it is considered to rebuild the table space.
MAX_ The result of the chunk column is the largest available block size on the table space. If the space allocated by the object on the table space (next value) is larger than the size of the available block, the error messages of ora-1652, ora-1653 and ora-1654 will be prompted. DBA should expand the table space in time to avoid these errors.
To expand the table space, expand the data file size of the table space, or add data files to the table space. See the “storage management” section for specific operations.

5、 Control file backup

When the database structure changes, such as adding table space, adding data files or redoing log files, these operations will change the control file of Oracle database. DBA should back up the control file. The backup method is as follows:
Execute SQL statement:


alter database
backup controlfile to '/home/backup/control.bak';

Or:
alter database
backup controlfile to trace;
In this way, the_ DUMP_ Generate SQL command to create control file under DeST (specified in initialization parameter file).

6、 Check the status of the database file

DBA should check the status of the data file in the database in time (if it is deleted by mistake), and decide how to handle it according to the actual situation. The SQL for checking the status of the data file is as follows:
select file_name,status
from dba_data_files;
If the status column of the data file is not available, corresponding measures should be taken, such as restoring the data file or rebuilding the table space where the data file is located.

7、 Check the completion of database timing jobs

If the database uses Oracle’s jobs to complete some scheduled jobs, check the operation of these jobs
select job,log_user,last_date,failures
from dba_jobs;
If the failures column is a number greater than 0, it indicates that the job failed to run, and further check is required.

8、 Processing of bad block in database

When there is a bad block in the Oracle database, Oracle will alert the_ SID.log )Record bad block information in:
ORA-01578: ORACLE data block corrupted (file # 7, block # <BLOCK>)
ORA-01110: data file <AFN>: ‘/oracle1/oradata/V920/oradata/V816/users01.dbf’

Among them, < AFN > represents the absolute file number of the data file where the bad block is located, < block > represents the number of data blocks in the data file
When this happens, you should first check whether it is the hardware and operating system failure that causes the Oracle database to appear bad blocks. After excluding the reasons beyond the database, the database objects with bad blocks are processed.

1. Determine the database object where the bad block occurred


SELECT tablespace_name,
segment_type,
owner,
segment_name
FROM dba_extents
WHERE file_id = <AFN>
AND <BLOCK> between block_id AND block_id+blocks-1;

2. Determine the repair method
If the object with a bad block is an index, you can drop the index directly and rebuild it according to the records in the table;
If the records of the table with bad blocks can be generated from the records of other tables, the table can be directly dropped and rebuilt;
If there is a database backup, restore the database to repair;
If there is no other way to recover the records in the table, then the records on the bad block will be lost. You can only take out the records on other data blocks in the table, and then rebuild the table.
3. Use DBMS provided by Oracle_ The repair packet is marked with a bad block
exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘<schema>’,'<tablename>’);
4. Use the create table as select command to save the records in other blocks of the table to another table
create table corrupt_table_bak
as
select * from corrupt_table;
5. Use the drop table command to delete tables with bad blocks
drop table corrupt_table;
6. Use the alter table rename command to restore the original table
alter table corrupt_table_bak
rename to corrupt_table;
7. If there is an index on the table, rebuild the index on the table

9、 Operating system related maintenance

DBA should pay attention to the monitoring of the operating system
 file system space usage (DF – K), clean Oracle warning log and TRC file if necessary
 if Oracle provides network service, check whether the network connection is normal
&#Check whether the resource usage of the operating system is normal
&#Check the database server for hardware failure, such as disk, memory error

Recommended Today

PHP 12th week function learning record

sha1() effect sha1()Function to evaluate the value of a stringSHA-1Hash. usage sha1(string,raw) case <?php $str = “Hello”; echo sha1($str); ?> result f7ff9e8b7bb2e09b70935a5d785e0cc5d9d0abf0 sha1_file() effect sha1_file()Function calculation fileSHA-1Hash. usage sha1_file(file,raw) case <?php $filename = “test.txt”; $sha1file = sha1_file($filename); echo $sha1file; ?> result aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d similar_text() effect similar_text()Function to calculate the similarity between two strings. usage similar_text(string1,string2,percent) case […]