Oracle database Daily Maintenance Knowledge points summary _oracle

The first thing to say is that the system tables provided by different versions of the database are different, and you can view the tables provided by that version of the database based on the data dictionary.
Like this:
SELECT * FROM DICT where table_name like '%session% ';
You can identify some tables and then get session information based on those tables.

Like this is querying the session that is currently being manipulated:

SELECT SID, serial#, STATUS, USERNAME, SCHEMANAME, osuser,terminal, MACHINE,
Program, A.name from V$session S, audit_actions A WHERE s.command = a.action;

First, view the connection of the database

The DBA will periodically check the connection to the database to see if the number of sessions established with the database is normal, and if too many connections are established, the resources of the database are consumed. At the same time, some "hang dead" connections may require manual cleanup by the DBA.

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

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

From V$session;

which
The ID number of the SID conversation (session);
The serial number of the serial# session, together with the SID, to uniquely identify a session;
USERNAME the user name to establish the session;
Program This session is using what tools to connect to the database;
Status of the current session, active indicates that the session is performing certain tasks, inactive that the current session is not performing any action;

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

Alter system kill session ' sid,serial# ';

Note that in the example above, a session with Sid 1 to 7 (username column null) is a background process for Oracle, and do not take any action on those sessions.

Ii. Common Commands

Select COUNT (*) from v$session  #连接数
Select COUNT (*) from v$session where status= ' ACTIVE ' #并发连接数
Show Parameter processes  #最大连接
alter system SET processes = value scope = spfile; Restart database  #修改连接

1:oracle how to see which users are in total
SELECT * from All_users;

2: View the current number of Oracle connections
How do I view the current number of Oracle connections? Just use the following SQL statement to query.

SELECT * FROM v$session where username isn't null

select Username,count (username) from v$session where username is no T NULL GROUP by username #查看不同用户的连接数 
Select COUNT (*) from v$session  #连接数
Select COUNT (*) from V$session wher E status= ' ACTIVE ' #并发连接数 show
parameter processes  #最大连接
alter system set process

3: Lists the sessions established by the current database:

Select Sid,serial#,username,program,machine,status from V$session;

Third, Oracle warning log file monitoring
Oracle will log some of the database operations in the warning log file (alert_sid.log) during the run:
 Database startup, shutdown, the start of the nondefault parameters;
 Database redo log switching, record the time of each switch, and if because the checkpoint (checkpoint) operation did not perform the completion of the result can not switch, will record can not switch the reason;
Some operations on the database, such as creating or deleting tablespaces, adding data files;
 Database error, such as insufficient table space, bad block, database internal error (ORA-600)

The DBA should periodically check the log files and process them according to the problems found in the logs
Problem handling
Startup parameter does not check initialization parameter file
Redo logs cannot be switched because the checkpoint operation or archive operation is not complete if this happens frequently, consider adding a redo log filegroup; Try to improve the efficiency of the checkpoint or archive operation;
Unauthorized deletion of table space Check database security issues, whether the password is too simple, if necessary, revoke some user's system permissions
Bad block check is a hardware problem (such as the disk has a bad block), if not, check that the database object appears bad block, the object is rebuilt
Not enough table space to add data file to the corresponding table space
Appears ORA-600 according to the contents of the log file to view the corresponding TRC file, if it is an Oracle bug, to timely play the corresponding patch

Iv. database table Space usage monitoring (Dictionary management table space)

After the database has been running for some time, due to the constant creation and deletion of objects on the tablespace, a large amount of fragmentation is generated on the tablespace, and the DBA should be aware of the fragmentation and free space in the tablespace to determine whether to defragment 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 above SQL lists the free block conditions for 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

Where the chunks column represents how many free blocks are available in the tablespace (each free block is made up of successive Oracle blocks), and if there are too many idle blocks, such as an average of more than 100 on each data file, the fragmentation of the tablespace is more serious. You can try the following SQL command to engage adjacent fragments of a table space:

Alter tablespace table space name coalesce;

Then execute the SQL statement that looks at the table space fragment to see if there is any reduction in the fragmentation of the tablespace. If there is no effect, and the fragmentation of the tablespace has seriously affected the operation of the database, consider rebuilding the table space.
The result of the Max_chunk column is the largest available block size on the table space, and if the object on the table space needs to allocate more space (next value) than the available block size, the ORA-1652, ORA-1653, ORA-1654 error messages are prompted. DBAs should expand the space in the tablespace in time to avoid these errors.
The expansion of the table space expands the size of the data file in the table space, or adds data files to the table space, as shown in the Storage Management section.

Five, control the file backup

When the structure of the database changes, such as the addition of table space, the addition of data files or redo log files of these operations, will cause the Oracle database control file changes, DBAs should and take control of the file backup, the backup method is:
Execute SQL statement:

ALTER DATABASE
backup Controlfile to '/home/backup/control.bak ';

Or:
ALTER DATABASE
Backup Controlfile to trace;
In this way, the SQL command to create the control file is generated in the user_dump_dest (specified in the initialization parameter file) directory.

Vi. checking the status of database files

The DBA should check the status of the data files in the database (such as mistakenly deleted), and decide how to process the data files according to the actual situation.
Select File_name,status
From Dba_data_files;
If the status column of the data file is not available, take the appropriate action, such as restoring the data file or rebuilding the tablespace in which the data file resides.

VII. Check the completion of the database timed operation

If the database uses Oracle's job to perform some timed jobs, check the operation of the job:
Select Job,log_user,last_date,failures
From Dba_jobs;
If the failures column is a number greater than 0, the job run fails, and further checks are needed.

Viii. processing of database bad blocks

When a bad block occurs in an Oracle database, Oracle logs the bad block information in the warning log file (alert_sid.log):
Ora-01578:oracle data Block Corrupted (file # 7, Block # <BLOCK>)
Ora-01110:data file <afn>: '/oracle1/oradata/v920/oradata/v816/users01.dbf '

Where the represents the absolute file number of the data file where the bad block is located,<block> represents the bad block is the first block of data on the data file
When this occurs, you should first check whether the hardware and the operating system failures are causing the Oracle database to appear bad blocks. After you have excluded the reason for the database, the database object that has the bad block is processed.

1. Determine the database object that 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. Deciding how to fix
If the object of the bad block is an index, then the index can be dropped directly, and then rebuilt according to the records in the table;
If a bad block of the table records can be generated according to the records of other tables, then you can drop the table directly after reconstruction;
If there is a backup of the database, then restore the database method to repair;
If there is no other way to recover the records in the table, the records on the bad block are lost, only the records on the other blocks of the tables are removed, and then the table is rebuilt.
3. Mark bad blocks with Oracle-supplied Dbms_repair packages
EXEC dbms_repair. Skip_corrupt_blocks (' <schema> ', ' <tablename> ');
4. Use the CREATE TABLE as SELECT command to save records from other blocks in a table to another table
CREATE TABLE Corrupt_table_bak
As
SELECT * from Corrupt_table;
5. Delete a table with a bad block with the drop TABLE command
drop table corrupt_table;
6. Restore the original table with ALTER TABLE rename command
ALTER TABLE Corrupt_table_bak
Rename to corrupt_table;
7. If an index exists on the table, rebuild the index on the table

Ix. operating system-related maintenance

The DBA should be aware of monitoring the operating system:
 File system space usage (DF-K), and if necessary, the Oracle warning log and TRC file cleaning
If Oracle provides network services, check if network connectivity is normal
Check the operating system resource usage is normal
Check the database server for hardware failure, such as disk, memory error