Shulou(Shulou.com)06/01 Report--

I. activity status check

By querying the basic view, confirm that the database and instances are in a normal running state, and data services can be provided to the outside world.

1.1 instance status

SELECT instance_name,status FROM v$instance

The query returns the name and status of the instance. The normal status should be Open.

1.2 session Information

SELECT

SESSIONS_CURRENT,SESSIONS_HIGHWATER FROM v$license

Secondary query, the current number of sessions of the instance and the maximum number of connection sessions started.

SELECT inst_id,username,COUNT (*)

FROM gv$session GROUP BYinst_id,username

Query the number of database connections grouped by instance and user.

1.3 Parameter check

SELECT value FROM v$parameter

WHERE name='open_cursors'

Query the setting value of a given parameter, the default value of the sample parameter is 300, and it is usually recommended to set it to 1000 for medium-sized databases.

1.4 Parameter modification

ALTER SYSTEM SET undo_retention=3600 COMMENT='default 900' SID='*' SCOPE=both

To modify a given initialization parameter, the RAC environment needs to pay attention to the SID parameter.

Reference:

Http://www.eygle.com/archives/2006/09/undo_retention_need_change.html

1.5 implied parameters

ALTER SYSTEM SET "_ optimizer_use_feedback" = FALSE SCOPE=spfile

To solve special problems, it is sometimes necessary to set implicit parameters at the beginning of the underscore. The example turns off the Cardinality Feedback-cardinality feedback feature introduced in version 11.2.

1.6 instance exception

When the connection to the database instance is slow, hanging and so on, it needs to be diagnosed and analyzed, and may even need to restart the database instance.

1.6.1 Information Collection

SQL > sqlplus-prelim / as sysdba

SQL > oradebug setmypid

SQL > oradebug unlimit

SQL > oradebug hanganalyze 3

SQL > oradebug dump systemstate 266

SQL > oradebug hanganalyze 3

SQL > oradebug dump systemstate 266

Demonstration command, through the collection of system Hang information, system status information, etc., can analyze the cause of the system hang, interval sampling, can be used to compare changes, assist analysis.

1.6.2 tracking

SQL > alter session set events' 10046 trace name context forever,level 12'

SQL > shutdown immedaite

SQL > startup mount

SQL > alter session set events' 10046 trace name context forever,level 12'

SQL > alter database open

If you encounter blockage or suspension during database shutdown or startup, you can trace it through the demonstration command and get the trace file for analysis.

1.6.3 safe shutdown

SQL > alter system checkpoint

SQL > alter system archive log current

SQL > shutdown immediate

If the database is abnormal and needs to be restarted, you can use the demonstration command to perform a checkpoint, archive command, and then try to shut down the database immediately.

1.6.4 forced shutdown

SQL > shutdown abort

SQL > startup nomount

SQL > alter database mount

SQL > alter database open

If the database cannot be shut down successfully immediately, the forced shutdown mode is abort. The demonstration command can perform database startup in a step-by-step manner.

1.7 connection exception

When there is an exception in the connection to the database, it is necessary to detect information including network connectivity, listener status and so on.

1.7.1 Connectivity

Tnsping tns_name

When installing an environment with Oracle clients, you can use the tnsping tool to test the configured service name to see if the network is connected and the response time.

1.7.2 listener

Lsnrctl status LISTENER

Lsnrctl status LISTENER_SCAN1

Lsnrctl service

On the database server, you can check the listening status and service information through the lsnrctl tool, and the specific listening service name can be defined and modified at the end.

1.7.3 snooping log check

Adrci > showalert

On the server, you can use the adrci tool to display all kinds of alarm files, check the listener log, and diagnose the monitoring problem.

2. Log information check

Log status check checks all kinds of log information in the database to confirm whether there are errors or alarms in database instances, clusters, and so on. If there are problems, further analysis and response are needed.

2.1 alarm Log

$ORACLE_BASE/diag/rdbms//$ORACLE_SID/

Trace/alert_$ORACLE_SID.log

SQL > show parameter background_dump_dest

Find the alarm log according to the example, and check whether there is an ORA- error prompt in the instance.

2.2 Cluster Log

$GRID_HOME/log//alert.log

$GRID_HOME/log// (crsd, cssd, evmd, ohasd) /

Find the RAC cluster log in the appropriate path and check for error messages, etc.

2.3ASM log

$GRID_HOME/diag/asm/+asm//

Trace/alert_.log

Find the ASM log in the appropriate path and check for error messages, etc.

2.4Trace file

SQL > SELECT value FROM v$diag_info WHERE name='Default Trace File'

SQL > show parameter user_dump_dest

Get the session generation or global dump location, and check the contents of the most recent date file when diagnosing.

2.5 Cluster status

$crsctl status resource-t

Ensure that the resource status is displayed online.

2.6 errorstack analysis

When the ORA- error is encountered and the output information of the database is insufficient, errorstack can be used to track and collect more detailed dump information.

SQL > alter system set events='600 trace name

Errorstack forever, level 10'

SQL > alter system set events='600 trace name

Errorstack off'

The example shows tracking for ORA-600 error settings and turning it off.

Third, redo log maintenance

Oracle REDO log is the core component of database. It is an important work of DBA to check its status, maintain its members, monitor its archiving and audit its performance.

3.1REDO group

SELECT group#,sequence#,archived,status FROM v$log

Query log group number, sequence number, archiving completion and status information. For example, multiple groups of logs display ACTIVE status, which may indicate that there are IO performance problems in the database.

3.2REDO member

SELECT group#,member FROM v$logfile

View log group and member information.

3.3 add log groups or members

SQL > ALTER DATABASE ADD LOGFILE GROUP 10

('/ oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500m

SQL > ALTER DATABASE ADD LOGFILE MEMBER

'/ oracle/dbs/log3c.rdo'TO GROUP 10

When log switching is frequent, you may need to increase the log group or increase the log size.

3.4 switch log

SQL > ALTER SYSTEM SWITCH LOGFILE

Switch log groups and start writing to the next log group.

3.5 perform archiving

SQL > ALTER SYSTEM ARCHIVE LOG CURRENT

Archive the current log group and switch to the next log group

All instances are archived in RAC, and the Thread parameter specifies the archive instance.

3.6 Delete log groups or members

SQL > ALTER DATABASE DROP LOGFILE GROUP 10

SQL > ALTER DATABASE DROP LOGFILE MEMBER'/ oracle/dbs/redo03.log'

Delete the specified log group or log member, and note that you can only delete logs with INACTIVE status.

3.7 Archive inspection

SQL > archive log list

Check to see if the database is in archive mode.

3.8 Archive status change

SQL > alter database archivelog | noarchivelog

The example steps change the archiving mode in the MOUNT state. Note that after starting the archiving mode

Be sure to develop a daily strategy for backup and archiving to prevent disk space from being exhausted.

3.9 adjust the archive path

SQL > alter system set log_archive_dest_2='location=&path' sid='&sid'

If the database runs out of space due to archiving, you can specify another archiving path to archive the logs as soon as possible and restore the database to run.

Reference:

Http://www.eygle.com/archives/2009/09/oracle_redo_log.html

Http://www.eygle.com/archives/2012/03/how_drop_logfile_member.html

IV. Spatial information inspection

Ensure that data storage space is available, check tablespace margin regularly, and maintain tablespace and files.

4.1 Space usage query

SQL > SELECT * FROM sys.sm$ts_used

View the usage information of database tablespaces.

SQL > SELECT * FROM sys.sm$ts_free

View the remaining space in the database tablespace.

4.2 File Information

SELECT tablespace_name,file_name

FROM dba_data_files

View data file information for database tablespaces.

4.3 File maintenance

Alter database datafile'& path' resize 900m

Alter tablespace & tbs_name add datafile'& path' size 900m

Expand the tablespace capacity of the database.

Lock / latch information check

Lock/Latch is the core means of database concurrency control. Checking relevant information can monitor the transaction and operation status of the database.

5.1 Lock information

SQL > SELECT sid, type, lmode, ctime, block

FROMv$lock WHERE type not in ('MR','AE')

Check lock session ID, type, hold time, etc.

Note that if block > 1, it may mean that other sessions are blocked.

5.2 Lock troubleshooting

When there is lock contention and blocking in the database, you need to troubleshoot and handle the lock, and if necessary, remove the lock through the Kill blocking process.

5.2.1 query blocking session

SQL > SELECT sid,sql_id,status,blocking_session

FROMv$session WHERE sid in (SELECT session_id FROMv$ locked_object)

Query the sid,sql_id and status information of blocked and blocked sessions in the current lock transaction

5.2.2 blocking SQL text

SQL > SELECT sql_id,sql_text FROMv$sqltext

WHERE sql_id='&sql_id' ORDER BYpiece

Get the SQL text through the sql_id query, such as the blocked SQL statement through the sql_id query.

5.2.3 Lock blocking object information

SQL > SELECT owner,object_name,object_type FROM dba_objects

WHEREobject_id in (SELECT object_id FROM v$locked_object)

Use sid to query the details of blocking objects, such as object name, user, etc.

5.2.4 kill blocking session

SQL > altersystem kill session 'sid,serial#'

Kill the blocked session process within the Oracle instance, where sid,serial# is the corresponding information for aborting the session, which comes from v$session.

5.2.5 kill system process

SQL > SELECTpro.spid,pro.program

FROMv$session ses,v$process pro

WHEREses.sid=&sid and ses.paddr=pro.addr

# kill-9 spid

Sometimes for an active process, it is faster and safer to abort at the system level. The example finds the system process number, and then kill aborts.

Note: at any time, you need to analyze carefully and avoid accidentally killing important background processes.

5.3 latch check

SELECTname,gets,misses,immediate_gets,spin_gets

FROMv$latch ORDER BY 2

Check the use of database latches, misses, SPIN_GETS statistics are high, need to pay attention to.

5.3.1 latch usage check

SQL > SELECT addr,gets FROM v$latch_children

WHEREname='cache buffers chains'

SQL > SELECT hladdr,file#,dbablk FROM x$bh

WHEREhladdr in (SELECT addr FROM v$latch_children WHERE addr='&addr')

For learning only: find the relevant Buffer in the X$BH of the Latch daemon by obtaining the address of the Latch.

VI. Waiting and statistics

Wait and Statistics data represent the waiting and running data of the database, respectively, and observe these data to understand the waiting bottleneck and health of the database.

6.1 wait for event query

SELECT sid,event,wait_time_micro

FROM v$session_wait ORDER BY 3

Learn about the wait of the current connection session of the database through the wait event and wait time.

Note that if there are many sessions, you need to limit the number of query output lines.

6.2TOP10 wait event

SQL > SELECT * FROM (

SELECTEVENT,TOTAL_WAITS,AVERAGE_WAIT,TIME_WAITED

FROM v$system_event WHEREwait_class'Idle'

ORDER BY time_waited desc) WHERE rownumSELECT s. Sidgery s. Statisticism recorder n.name1. Value

FROM v$sesstat spark vault statname n

WHERE s.statistic#=n.statistic# andn.name='redo size' and sid='&sid'

Query the statistics of the database session. The example queries the size of Redo, which needs to be provided by SID.

6.4 system-level statistics

SQL > SELECT * FROM v$sysstat WHERE name='redo size'

Query the statistics of the entire system, and the example shows the number of REDO logs generated since the database instance was started.

VII. Object inspection

Tables, indexes, partitions and constraints are the core storage objects of the database, and its core information and object maintenance is an important daily work of DBA.

7.1 Table Information data

SQL > SELECT * FROM (

SELECTowner,table_name,num_rows

FROMdba_tables ORDER BY num_rows desc nulls last)

WHERErownum

< 11; 查看表的基本信息数据：属主，表名，记录行数等。 7.2表结构查询 SQL>

Set long 12000

SQL > SELECTdbms_metadata.get_ddl ('TABLE','&table_name','&user')

FROM dual

Query the table structure information (table-building statement) of the table according to the table name and user (uppercase) provided.

7.3 Table Statistics

SQL > SELECT owner,table_name,last_analyzed FROM dba_tab_statistics

WHEREowner='&owner' and table_name='&table_name'

Query the given user, the given table (in uppercase), and the final statistical analysis collection time of the query. Statistics affect the execution plan, and when the SQL execution is abnormal, you need to focus on analyzing the statistics.

7.4 Table statistics collection

SQL > exec dbms_stats.gather_table_stats (ownname= >'& owner'

Tabname= >'& table_name')

Collecting statistical information is a complex task, which requires detailed design. The example collects statistical information from objects that give users and table names.

7.5 Index information data

SQL > SELECT * FROM (

SELECTindex_name,table_name,num_rows,leaf_blocks,clustering_factor

FROMdba_indexes ORDER BY 5 desc nulls last)

WHERErownumset long 12000

SQL > SELECT dbms_metadata.get_ddl ('INDEX','&table_name','&user')

FROM dual

Query the index creation statement based on the table name and user name provided.

7.7 Index statistics and collection methods

SQL > SELECT owner,index_name,last_analyzed FROM dba_ind_statistics

WHEREowner='&owner' and table_name='&table_name'

Query the index information according to the given user name and table name (uppercase), especially the last analysis time.

SQL > exec dbms_stats.gather_index_stats (ownname= >'& owner'

Indname= >'& index_name')

Collecting statistics is a complex task. The example collects statistics from objects that give users and index names.

7.8 Partition object check

SQL > SELECT table_name,partitioning_type,partition_count,status

FROM dba_part_tables

SQL > SELECT table_name,partition_name,high_value

FROM dba_tab_partitions WHERE rownum SELECTdbms_metadata.get_ddl ('TABLE'

'& part_table_name','user') FROM dual

Query the structure information (table-building statement) of the partition table according to the given partition table name and user (uppercase).

7.10 Zone statistics related

SQL > SELECT owner,table_name,partition_name,last_analyzed

FROMdba_tab_statistics

WHERE owner='&owner' andtable_name='&table_name'

SQL > exec dbms_stats.gather_table_stats (ownname= >'& owner'

Tabname= >'& table_name')

Check the collection time of the statistics of the partition table, and manually collect the statistics of the partition table. Note that the collection of statistics of the partition table is very complex and requires in-depth research to make the correct strategy. The example only provides the simplest collection command.

7.11 constraint Information

SQL > SELECTconstraint_name,constraint_type FROM DBA_CONSTRAINTS

WHEREtable_name='&table_name'

Query constraint information for the specified data table, including name and type.

7.12 failure object check

SQL > SELECT owner,object_name,object_type,status

FROMdba_objects WHERE status' VALID'

ORDERBY owner,object_name

Check the information of failed objects in the database. Generally speaking, there should be no failed objects in a healthy database.

7.13 Flashback query

Flashback query function is very convenient for recovering DML and some misoperations of DDL, and it is a necessary skill for DBA.

7.13.1 time flashback

SQL > SELECT * FROM & table_name as of timestamp

To_timestamp ('2015-02-0400 velcro 02purl 09mm hh34:mi:ss')

Flashback table data, table data flashback query based on point in time.

7.13.2 SCN flashback

SQL > SELECT * FROM & table_name as of scn & scn

Flashback table data, scn-based table data query, you need to provide SCN, if the SCN is not clear, you can flash the query through a point in time.

7.13.3 Flashback DROP

SQL > flashback table & old_table to before drop rename to & new_table

Flashback delete operation, flashback reply and rename the deleted table.

Reference:

Http://www.eygle.com/archives/2009/11/scn_to_timestamp.html

Http://www.eygle.com/archives/2005/03/eoaoracle10gaef.html

VIII. AWR report check

Through the AWR report to understand the daily peak hours of the database indicators and operation status, through the comparative report observation and baseline changes, through trend analysis to continue to pay attention to the daily operation status of the database.

8.1 Local AWR

SQL > @? / rdbms/admin/awrrpt

To generate local AWR report information, you need to enter the corresponding information according to the prompts.

8.2 specify instance AWR

SQL > @? / rdbms/admin/awrrpti

Generate a specified instance AWR report

8.3AWR comparison report

SQL > @? / rdbms/admin/awrddrpt

Generate local AWR time period comparison report

8.4 specified instance comparison

SQL > @? / rdbms/admin/awrddrpi

Generate a comparison report for the specified instance AWR time period

8.5AWR information extraction

SQL > @? / rdbms/admin/awrextr

Use awrextr scripts to export AWR performance data, which can be used for error correction or remote analysis.

8.6AWR information loading

SQL > @? / rdbms/admin/awrload

With awrload, exported AWR performance data can be imported into other databases for centralization and analysis.

9. SQL report inspection

Continuously pay attention to and analyze TOP SQL, analyze the efficiency and performance of SQL through SQL report, and make reports and optimization recommendations.

9.1 Local SQLReport

SQL > @? / rdbms/admin/awrsqrpt

Generate a local SQLReport report

9.2 specify instance SQLReport

SQL > @? / rdbms/admin/awrsqrpi

Generate a specified instance SQLRerport report

9.3 current session SQL Monitor Report

SELECT

Dbms_sqltune.report_sql_monitor (session_id= >'& sid'

Report_level= > 'ALL', TYPE= >' & type') as report

FROM dual

Generate the SQL Monitor Report of the current session

9.4 specify SQLID SQL Monitor Report

SELECT

Dbms_sqltune.report_sql_monitor (sql_id= >'& sql_id'

Report_level= > 'ALL', TYPE= >' & type') as report FROM dual

Generate the SQL Monitor Report of the specified SQL_ID

Reference: http://www.eygle.com/archives/2012/09/sqloracle_sql_monitor_report.html

9.5 Explain SQL execution Plan

Explain plan for

SELECT count (*) FROM user_objects

SELECT * FROM table (dbms_xplan.display)

The example obtains the SQL execution plan through the explain plan for method

9.6 Autotrace SQL execution Plan

SQL > set autotrace traceonly explain

SQL > SELECT count (*) FROM user_objects

SQL > set autotrace off

Obtain the SQL execution plan through the autotrace function of SQL*Plus.

9.7 DBMS_XPLAN SQL execution Plan

SQL > SELECT * FROM

Table (dbms_xplan.display_cursor ('& sql_id',null,'advanced')

Get the SQL execution plan through the DBMS_XPLAN package, which sql_id needs to provide.

9.8 10053 event tracking

SQL > alter session set tracefile_identifier='10053'

SQL > alter session set events' 10053 trace name context forever, level 1'

SQL >

SQL > alter session set events' 10053 trace name context off'

The 10053 event is used to view the execution plan and the detailed SQL parsing process, and the trace file provides the reason why Oracle chose the execution plan.

Reference: http://www.eygle.com/archives/2011/02/dba_event_10046_10053.html

9.9 bind variables

SELECT

Dbms_sqltune.extract_bind (bind_data, 1). Value_string

FROM wrh$_sqlstat WHERE sql_id ='& sql_id'

SELECT snap_id,name,position,value_string

FROM dba_hist_sqlbind WHERE sql_id='& sql_id'

The SQL_ID information is required to query the binding variables of the SQL statement and the value information of the historical binding variables.

Reference: http://www.eygle.com/archives/2010/11/dbms_sqltune_bindata.html

10. Scheduled task check

Check the execution of the scheduled tasks in the database to ensure that the background tasks are executed correctly, especially the core tasks such as statistics collection.

10.1 user scheduled tasks

SQL > SELECTjob,log_user,last_date,next_date,interval,broken,failures

FROM dba_jobs

Query the user's scheduled task (job) information to ensure that the task is executed successfully at the expected time, which is one of the important tasks of DBA.

10.2 system timing tasks

SQL > SELECTjob_name,start_date,repeat_interval

FROM dba_scheduler_jobs

Query the timing scheduling information of the system, and the query shows the task name, initial start date and repeat interval.

10.3 system timing tasks-11g

SQL > SELECTclient_name,mean_job_duration

FROM dba_autotask_client

A dictionary table added after 11g, recording statistics for each maintenance task at 7 and 30 days, query display name and average execution time.

10.4 start and stop Statistics Task-10g

SQL > execdbms_scheduler.disable ('SYS.GATHER_STATS_JOB')

SQL > execdbms_scheduler.enable ('SYS.GATHER_STATS_JOB')

Turn off and enable the automatic collection of oralce 10g statistics

10.5 start and stop Statistics Task-11g

SQL > exec DBMS_AUTO_TASK_ADMIN.DISABLE (client_name= > 'auto

Optimizerstats collection', operation = > NULL, window_name = > NULL)

SQL > execDBMS_AUTO_TASK_ADMIN.ENABLE (client_name = > 'auto

Optimizer stats collection', operation= > NULL, window_name = > NULL)

Turn off and enable the automatic collection of Oralce 11g statistics

11. Backup

Data backup is more important than anything else. Every day, we should check the implementation of the backup and check the effectiveness of the backup to ensure that the backup can ensure data security. Backup security and encryption should also be taken into account.

11.1 full library exp/imp

$exp system/managerfile=/full.dmp log=/full.log full=y

$imp system/managerfile=/full.dmp log=/full.log full=y

Import a full database export (the example and the following example show a non-Windows environment)

Perform a full library export through the provided users. You need to pay attention to the storage location and space.

11.2 user mode exp/imp

$exp enmo/enmo file=/enmo.dmp log=/enmo.log owner=enmo

$imp enmo/enmo file=/enmo.dmp log=/enmo.log fromuser=enmo

Touser=enmo

Export the database as specified user

11.3 Table mode exp/imp

$exp enmo/enmo file=/tables.dmplog=/tables.log tables=table_name

$imp enmo/enmo file=/tables.dmplog=/tables.log tables=table_name

Export the database to the specified table

11.4 full library mode expdp/impdp

$expdp system/managerdirectory=svr_dir dumpfile=full.dmp full=y

$impdb system/managerdirectory=svr_dir dumpfile=full.dmp full=y

Export and import the full database, and note that directory is an object created in the database, specifying the storage location on the server.

11.5 user mode-expdp/impdp

$expdp system/manager directory=svr_dirschemas=scott

Dumpfile=expdp.dmp

$impdp system/managerdirectory=svr_dir schemas=scott

Dumpfile=expdp.dmpremap_schema=scott:enmo

Remap_tablespace=users:testtbs

Export and import the database by user. In the impdp example, the imported Schema and tablespaces are remapped respectively.

11.6 Table mode-expdp/impdp

$expdp scott/tiger directory=svr_dirtables=emp,dept

Dumpfile=tables.dmp

$impdp scott/tigerdirectory=svr_dir dumpfile=tables.dmp

Tables=emp,dept

Export and import the database by table

11.7 physical backup check

SQL > SELECTbackup_type,start_time,completion_time,block_size

FROM v$backup_set

Checking the backup set information to ensure that the backup is effective and timely is one of the important tasks of DBA. The backup information of RMAN is recorded in the control file.

11.8 automatic control of file backup

RMAN > show all

RMAN > CONFIGURE CONTROLFILEAUTOBACKUP ON

Control files are very important to the database. It is recommended to start automatic backup of control files. The demonstration shows the settings through RMAN.

11.9 manually control file backup

RMAN > backup currentcontrolfile

SQL > alter database backupcontrolfile to'/ back/control.bak'

Back up the control file manually through the RMAN or SQL command, backing up the binary copy of the control file.

11.10 dump control files

SQL > alter session set events'immediate trace name controlf level 8'

Using the above command to dump the control file binary information to the text, studying this information can greatly deepen the understanding of the database.

SQL > alter database backupcontrolfile to trace

Use the SQL command to dump the control file to text, which can be used to rebuild the control file.

11.11RMAN backup database

RMAN > backup format'/data/backup/%U' database plus archivelog

DBA backup is the first important task, in archive mode

Performing a full library backup can be reduced to a command of the example (which needs to be shredded according to capacity).

Reference: http://www.eygle.com/archives/2004/11/use_rman_plusarchivelog_option.html

XII. Basic information check

Basic information includes version, component, patch set and other information. Checking database information regularly and registering it is one of the important contents of database life cycle management.

12.1 version check

SQL > SELECT * FROM v$version

View the version information of the database

12.2 component check

SQL > SELECT * FROM v$option

View the component information of the database

12.3 capacity check

# asmcmd

ASMCMD > lsdg

SELECT group_number,disk_number

Mount_status,total_mb,free_mb

FROM v$asm_disk

SELECT group_number,name,state,total_mb,free_mb

FROM v$asm_diskgroup

"if you use ASM management, you can view information such as ASM disks and disk group capacity through examples."

12.4PSU check

SQL > SELECT * FROM dba_registry_history

Query the version upgrade history of the database.

# $ORACLE_HOME/OPatch/opatchlsinventory

Query database patch history information, is a system-level command tool.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.