Routine ORACLE Database Maintenance

First of all, the system tables provided by databases of different versions are 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 obtain session information based on these tables.

Query the currently active sessions as follows:

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 connections

DBAs should regularly check the database connection to check 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, DBA may need to manually clean up some "dead" connections.

The following SQL statements List the sessions created by the current database:

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

From v $ session;

Where,
ID of the SID session (session;
SERIAL # session SERIAL number, which is used together with SID to uniquely identify a session;
USERNAME: the user name used to establish the session;
What tool is used to connect to the database using the PROGRAM session;
STATUS: the STATUS of the current session. ACTIVE indicates that the session is executing some tasks. INACTIVE indicates that the current session has not executed any operations;

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

Alter system kill session 'sid, SERIAL #';

Note: In the preceding example, sessions with SID ranging from 1 to 7 (the USERNAME column is empty) are the background processes of Oracle. do not perform any operations on these sessions.

Ii. Common commands

Select count (*) from v $ session # Number of connections

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 # 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 can I view the current number of connections in oracle? You only need to 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 # view the number of connections of different users
Select count (*) from v $ session # Number of connections

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;

 

Iii. Oracle warning Log File monitoring
Oracle records the running status of the database in the warning log file (alert_SID.log) during running:
The non-default parameter for the startup and shutdown of the slave database;
The redo log switching of the standby database records the time of each switchover, and records the reason why the switchover fails if the checkpoint operation is not completed;
Operations performed by the tablespace on the database, such as creating or deleting tablespaces and adding data files;
Errors that occur in the primary database, such as insufficient tablespace, Bad blocks, internal database errors (ORA-600)

DBA should regularly check log files and handle problems found in logs in a timely manner
Troubleshooting
The startup parameter does not check the initialization parameter file
The redo log cannot be switched because the checkpoint operation or archive operation is not completed. If this happens frequently, you can consider adding a redo log file group. You can find a way to improve the efficiency of the checkpoint or archive operation;
The tablespace has been deleted without authorization to check the database security. The password is too simple. If necessary, revoke the system permissions of some users.
Check whether the bad block is a hardware problem (for example, if the disk has a bad block). If not, check that the database object has a bad block and recreate the object.
The tablespace is not enough to add data files to the corresponding tablespace.
The ORA-600 appears according to the contents of the log file to view the corresponding TRC file, if it is Oracle bug, to timely add the corresponding patch

Iv. Monitoring of database tablespace usage (dictionary management tablespace)
After the database has been running for a period of time, the constant creation and deletion of objects in the tablespace will generate a large number of fragments in the tablespace. the DBA should know the shards and available space in a timely manner, to determine whether to organize shards or add data files to the tablespace.

Select tablespace_name,
Count (*) chunks,
Max (bytes/1024/1024) max_chunk
From dba_free_space
Group by tablespace_name;

The preceding SQL statement lists the idle blocks of each tablespace in the database, as shown below:
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

The CHUNKS column indicates the number of available idle blocks in the tablespace (each idle block is composed of several consecutive Oracle data blocks). If there are too many idle blocks, for example, if there are more than 100 data files on average, the fragmentation of the tablespace is serious. You can use the following SQL command to join adjacent fragments of the tablespace:

Alter tablespace name coalesce;

Then execute the SQL statement to check whether the table space fragments have been reduced. If there is no effect, and the tablespace fragments have seriously affected the operation of the database, the tablespace should be rebuilt.
The result of the MAX_CHUNK column is the maximum available block size on the tablespace. If the space (NEXT value) to be allocated for the objects in the tablespace is greater than the available block size, an error message is prompted for the ORA-1652, ORA-1653, and ORA-1654. DBA should promptly expand the table space to avoid these errors.
Expand the table space to expand the data file size of the table space, or add data files to the tablespace. For details, see "Storage Management.

V. Backup of Control Files
When the database structure changes, such operations as adding tablespaces, adding data files, or redoing log files will change the control file of the Oracle database, DBAs should back up the control files as follows:
Execute the SQL statement:
Alter database
Backup controlfile to '/home/backup/control. Bak ';

Or:
Alter database
Backup controlfile to trace;
In this way, the SQL command for creating the control file will be generated in the directory USER_DUMP_DEST (specified in the initialization parameter file.

6. Check the database file status
DBAs should promptly check the status of data files in the database (such as deleted by mistake) and decide how to process the data files according to the actual situation. The SQL statement used to check the status of the data files is as follows:
Select file_name, status
From dba_data_files;
If the STATUS column of the data file is not AVAILABLE, you must take appropriate measures, such as restoring the data file or recreating the tablespace where the data file is located.

VII. Check the completion of database scheduled jobs
If the database uses Oracle jobs to complete some scheduled jobs, check the running status of these jobs:
Select job, log_user, last_date, failures
From dba_jobs;
If the FAILURES column is a number greater than 0, the JOB fails to run and further checks are required.

8. Handling of bad database Blocks
When the Oracle database contains bad blocks, Oracle records the Bad blocks in the warning log file (alert_SID.log:
ORA-01578: ORACLE data block upted (file #7, block # <BLOCK>)
ORA-01110: data file <AFN>: '/oracle1/oradata/V920/oradata/V816/users01.dbf'

<AFN> indicates the absolute file number of the data file where the bad BLOCK is located. <BLOCK> indicates that the bad BLOCK is the first data BLOCK on the data file.
In this case, check whether the Oracle database has bad blocks due to hardware and operating system faults. After the database is excluded, the database objects with bad blocks are processed.
1. Determine the database objects with bad Blocks
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 solution
If the object with bad blocks is an index, you can DROP the index directly and recreate it based on the records in the table;
If records of tables with bad blocks can be generated based on records of other tables, you can directly DROP the table and recreate it;
If there is a database backup, the database will be restored;
If there is no other way to restore the records in the table, the records on the bad block will be lost. You can only retrieve the records from other data blocks in the table and recreate the table.
3. Mark the bad block with the DBMS_REPAIR package provided by Oracle
Exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ('<schema>', '<tablename> ');
4. Use the Create table as select command to save records from other blocks in the table to another table.
Create table corrupt_table_bak
As
Select * from corrupt_table;
5. Run the drop table command to delete a TABLE with bad blocks.
Drop table corrupt_table;
6. Run the alter table rename command to restore the original table.
Alter table corrupt_table_bak
Rename to corrupt_table;
7. If an index exists on the table, you need to recreate the index on the table.

9. Operating System Maintenance
DBA should pay attention to the monitoring of the operating system:
Memory file system space usage (df-k). If necessary, clear Oracle warning logs and TRC files.
 If Oracle provides network services, check whether the network connection is normal.
 Check whether the resource usage of the operating system is normal
 Check whether the database server has hardware faults, such as disk and memory errors.



This article is from Zhang Bing's personal space of 1989.