Routine Oracle maintenance commands in Linux

 

1. Check the Oracle Process
$ PS-Ef | grep "ora _" | grep-V grep
Oracle 5998 1 0 11:15:59? 0: 01 ora_j000_pprd10
Oracle 2968 1 0 21:16:57? 0: 00 ora_q000_pprd10
Oracle 2927 1 0 21:16:33? 0: 00 ora_pmon_pprd10
Oracle 2933 1 0 21:16:34? 0: 07 ora_dbw0_pprd10
Oracle 2945 1 0 21:16:34? 0: 02 ora_mmon_pprd10
Oracle 2931 1 0 21:16:33? 0: 00 ora_mman_pprd10
Oracle 2949 1 0 21:16:34? 0: 00 ora_d000_pprd10
Oracle 2970 1 0 21:16:57? 0: 00 ora_q0020.pprd10
Oracle 2935 1 0 21:16:34? 0: 05 ora_lgwr_pprd10
Oracle 2951 1 0 21:16:34? 0: 00 ora_s000_pprd10
Oracle 2939 1 0 21:16:34? 0: 06 ora_smon_pprd10
Oracle 2957 1 0 21:16:47? 0: 00 ora_qmnc_pprd10
Oracle 2943 1 0 21:16:34? 0: 05 ora_cjq0_pprd10
Oracle 2947 1 0 21:16:34? 0: 00 ora_mmnl_pprd10
Oracle 2937 1 0 21:16:34? 0: 18 ora_ckpt_pprd10
Oracle 2941 1 0 21:16:34? 0: 00 ora_reco_pprd10
Oracle 2929 1 0 21:16:33? 0: 00 ora_psp0_pprd10

After checking the Oracle process command output, the output display should include at least the following processes:
? Oracle Data File writing process, the output is: "ora_dbw0_orcl"
? The process of writing a log file in Oracle. The output is "ora_lgwr_orcl"
? The process that listens to the instance status in Oracle. The output is "ora_smon_orcl"
? Oracle listens to the process in which the client connects to the process. The output is "ora_pmon_orcl"
? Oracle archiving process, the output is: "ora_arc0_orcl"
? Oracle Checkpoint Process, the output is: "ora_ckpt_orcl"
? Oracle performs [url = javascript:;] to restore the [/url] process. The output shows "ora_reco_orcl"

2. view database instances:
SQL> select instance_name, status, version, database_status from V $ instance;

Instance_name status version database_status
--------------------------------------------------------------
Pprd10 open 10.2.0.4.0 active

"Status" indicates the current Oracle instance status, which must be "open"; database_status indicates the current Oracle Database status, which must be "active ".

3. view the log mode of the database. Open Mode
SQL> select name, log_mode, open_mode from V $ database;

Name log_mode open_mode
-------------------------------
Pprd10 noarchivelog read write

"Log_mode" indicates the current Oracle archiving method. "Archivelog" indicates that the database runs in archive mode, and "noarchivelog" indicates that the database runs in non-archive mode.

4. view the ora-error message in the database alert_sid.log:
$ More/u01/APP/Oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep-I ora-

Warning: inbound connection timed out (ORA-3136)
Warning: inbound connection timed out (ORA-3136)
ORA-12012: Error on auto execute of job 78677
ORA-20820: ORA-20820:
ORA-06512: At "PS _ system.row_validator_t", line 912
ORA-06512: At "PS _ system.row_validator_t", line 924
ORA-06512: At "workorder. inventory_utl", line 1260
ORA-12012: Error on auto execute of job 78677

4.1 check the err information in alert_sid.log:
$ More/u01/APP/Oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep-I err

Errors in file/u01/APP/Oracle/admin/pinnsoft/bdump/pinnsoft_j000_2666.trc:
ORA-12012: Error on auto execute of job 78677
: 6000101: Error occurred when rolling inventory date
Errors in file/u01/APP/Oracle/admin/pinnsoft/bdump/pinnsoft_j000_11886.trc:
ORA-12012: Error on auto execute of job 78677
: 6000101: Error occurred when rolling inventory date
Errors in file/u01/APP/Oracle/admin/pinnsoft/bdump/pinnsoft_j000_21375.trc:
ORA-12012: Error on auto execute of job 78677
: 6000101: Error occurred when rolling inventory date

4.2 query fail information of alert_sid.log:
$ More/u01/APP/Oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep-I fail

Pmon failed to acquire latch, see pmon dump
Pmon failed to acquire latch, see pmon dump
Pmon failed to acquire latch, see pmon dump

5. Check the Control File status
SQL> select status, name from V $ controlfile;
Status name
-------------------------------------------------------------------------------
/Data/APP/Oracle/oradata/pprd10/control01.ctl
/Data/APP/Oracle/oradata/pprd10/control02.ctl
/Data/APP/Oracle/oradata/pprd10/control03.ctl

The output result should have more than three records (including three records), and "status" should be blank. If the status is null, the control file is in normal state.

6. query log status
SQL> select group #, status, member from V $ logfile;

Group # status Member
------------------------------------------------------------------------------
1/data/APP/Oracle/oradata/pprd10/redo01.log
2/data/APP/Oracle/oradata/pprd10/redo02.log
3/data/APP/Oracle/oradata/pprd10/redo03.log

There should be more than three output records (including three records), "status" should be non-"invalid", not "deleted ".

7. query data status:
SQL> SELECT FILE #, status, name from V $ datafile;

File # status name
-------------------------------------------------------------------
1 system/u01/APP/Oracle/oradata/orcl/system01.dbf
2 online/u01/APP/Oracle/oradata/orcl/undotbs01.dbf
3 online/u01/APP/Oracle/oradata/orcl/sysaux01.dbf
4 online/u01/APP/Oracle/oradata/orcl/users01.dbf
5 online/u01/APP/Oracle/oradata/orcl/example01.dbf
6 online/u01/APP/Oracle/oradata/orcl/perfstat. DBF
7 online/u01/APP/Oracle/oradata/orcl/risenet. DBF

"Online" indicates normal online status

8. query the table space status
SQL> select tablespace_name, status from dba_tablespaces;

Tablespace_name status
---------------------------------------
System Online
Undotbs1 online
Sysaux online
Temp online
Users online
Example online
Perfstat online
Risenet online

"Online" indicates a normal online status

9. Check the status of all Oracle rollback segments

SQL> select segment_name, status from dba_rollback_segs;

Segment_name status
----------------------------------------------
System Online
_ Syssmu10 $ online
_ Syssmu9 $ online
_ Syssmu8 $ online
_ Syssmu7 $ online
_ Syssmu6 $ online
_ Syssmu5 $ online
_ Syssmu4 $ online
_ Syssmu3 $ online
_ Syssmu2 $ online
_ Syssmu1 $ online

11 rows selected.

10. Check the parameter values in the Oracle initialization file.

SQL> select resource_name, current_utilization, max_utilization, initial_allocation, limit_value from V $ resource_limit;

Resource_name current_utilization max_utilization initial_al Li
----------------------------------------------------------------------------
Processes 26 31 150
Sessions 30 37 170
Enqueue_locks 2331 2300
Enqueue_resources 23 968 U
Ges_procs 0 0 0
Ges_ress 0 0 0 U
Ges_locks 0 0 0 U
Ges_cache_ress 0 0 0 U
Ges_reg_msgs 0 0 0 U
Ges_big_msgs 0 0 0 U
Ges_rsv_msgs 0 0 0

Resource_name current_utilization max_utilization initial_al Li
----------------------------------------------------------------------------
Gcs_resources 0 0 0
Gcs_shadows 0 0 0
Dml_locks 0 68 748 u
Temporary_table_locks 0 3 unlimited u
Transactions 2 11 187 U
Branches 0 0 187 U
Cmtcallbk 0 2 187 U
Sort_segment_locks 0 3 unlimited u
Max_rollback_segments 11 11 187
Max_shared_servers 1 1 unlimited u
Parallel_max_servers 0 0 40

22 rows selected.

11. Check the growth of each Oracle tablespace

Select a. tablespace_name, (1-(A. Total)/B. Total) * 100 used_percent
From (select tablespace_name, sum (bytes) Total from dba_free_space group by tablespace_name) A, (select tablespace_name, sum (bytes) Total from dba_data_files group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name;

SQL> select a. tablespace_name, (1-(A. Total)/B. Total) * 100 used_percent
2 from (select tablespace_name, sum (bytes) Total from dba_free_space group by tablespace_name) A, (select tablespace_name, sum (bytes) Total from dba_data_files group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name;
3

Tablespace_name used_percent
------------------------------------------
Undotbs1 33.25
Sysaux 99.0364583
Risenet 0125
Users' 62.5
System 98.6067708
Examples 68.25
Perfstat 28.7625

7 rows selected.

12. check some objects with extended exceptions
Select segment_name, segment_type, tablespace_name,
(Extents/max_extents) * 100 percent from SYS. dba_segments
Where max_extents! = 0 and (extents/max_extents) * 100> = 95
Order by percent;

SQL> select segment_name, segment_type, tablespace_name,
2 (extents/max_extents) * 100 percent
3 from SYS. dba_segments
4 where max_extents! = 0 and (extents/max_extents) * 100> = 95
5 order by percent;

No rows selected

If a record is returned, the extension of these objects is almost reached the maximum extension value it defines. Modify the storage structure parameters of these objects.

13. Check the content in the system tablespace.
Select distinct (owner) from dba_tables
Where tablespace_name = 'system' and owner! = 'Sys'
And owner! = 'System'
Union
Select distinct (owner) from dba_indexes
Where tablespace_name = 'system'
And owner! = 'Sys 'and owner! = 'System ';
SQL> select distinct (owner) from dba_tables
2 Where tablespace_name = 'system' and
3 owner! = 'Sys 'and owner! = 'System'
4 union
5 select distinct (owner) from dba_indexes
6 where tablespace_name = 'system' and
7 owner! = 'Sys 'and owner! = 'System ';

Owner
------------------------------
MDSYS
Olapsys
Outln

If a record is returned, the system tablespace contains objects other than the system and SYS users. We should further check whether these objects are related to our application. If so, move these objects to a non-system tablespace and check the default tablespace values of the objects that belong to the primary table,

14. Check the next extension of the object and the maximum extension value of the tablespace.
Select a. table_name, A. next_extent, A. tablespace_name
From all_tables,
(Select tablespace_name, max (bytes) as big_chunk
From dba_free_space group by tablespace_name) f
Where F. tablespace_name = A. tablespace_name
And a. next_extent> F. big_chunk
Union
Select a. index_name, A. next_extent, A. tablespace_name
From all_indexes,
(Select tablespace_name, max (bytes) as big_chunk
From dba_free_space
Group by tablespace_name) f
Where F. tablespace_name = A. tablespace_name
And a. next_extent> F. big_chunk;

SQL> select a. table_name, A. next_extent, A. tablespace_name
2 from all_tables,
3 (select tablespace_name, max (bytes) as big_chunk
4 From dba_free_space group by tablespace_name) f
5 where F. tablespace_name = A. tablespace_name
6 and A. next_extent> F. big_chunk
7 Union
8 select a. index_name, A. next_extent, A. tablespace_name
9 from all_indexes,
10 (select tablespace_name, max (bytes) as big_chunk
11 from dba_free_space
12 Group by tablespace_name) f
13 where F. tablespace_name = A. tablespace_name
14 and A. next_extent> F. big_chunk;

No rows selected

If a record is returned, it indicates that the next extension of these objects is greater than the maximum extension value of the tablespace to which the object belongs. You need to adjust the storage parameters of the corresponding tablespace.