rac 日常巡检

王硕 · 发表于 2015-4-19 18:30:17

10.查看spfile.ora 查看profile

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +DATA/db/spfiledb.ora

[grid@node1 grid]$ asmcmd

ASMCMD> ls

ARCH/

DATA/

OVDISK/

ASMCMD> cd DATA

ASMCMD> cd DB

ASMCMD> ls

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

spfiledb.ora

ASMCMD>

王硕 · 发表于 2015-4-19 18:30:35

11.参看监听状态

[oracle@node1 ~]$ hostname

node1

[oracle@node1 ~]$ export ORACLE_SID=node1

[oracle@node1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-APR-2015 20:16:48

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 17-APR-2015 12:59:13

Uptime 0 days 7 hr. 17 min. 35 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/grid/network/admin/listener.ora

Listener Log File /u01/app/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.31)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.131)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "db" has 1 instance(s).

Instance "db1", status READY, has 1 handler(s) for this service...

Service "dbXDB" has 1 instance(s).

Instance "db1", status READY, has 1 handler(s) for this service...

The command completed successfully

王硕 · 发表于 2015-4-19 18:31:41

12.crs 日志文件位置

[grid@node1 ~]$ cd $ORACLE_HOME

[grid@node1 grid]$ ls

assistants dbs has ldap opmn relnotes

auth dc_ocm hs lib oracore root.sh

bin deinstall install log oraInst.loc scheduler

cdata demo install.platform md ord slax

cfgtoollogs diagnostics instantclient mdns oui sqlplus

clone eons inventory mesg owm srvm

crs evm javavm network perl sysman

csmig gipc jdbc nls plsql usm

css gns jdk oc4j precomp utl

ctss gnsd jlib ons racg wwg

cv gpnp JRE OPatch rdbms xdk

[grid@node1 grid]$ cd log

[grid@node1 log]$ ls

crs diag node1

王硕 · 发表于 2015-4-19 18:32:25

13.集群中所有正在运行的实例（sysdba下运行）

SQL> set pagesize 1000

SQL> set linesize 120

SQL> set echo on ----------------------set echo on，则显示出文件中的每条命令和该命令执行的结果，如果设为set echo off，则只显示命令执行的结果，而不显示出命令本身。

SQL> COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME

SQL> COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

SQL> col tablespace_name format a15

SQL> host hostname

node1

SQL> SELECT

inst_id

, instance_number inst_no

, instance_name inst_name

, parallel

, status

, database_status db_status

, active_state state

, host_name host

FROM gv$instance

ORDER BY inst_id; 2 3 4 5 6 7 8 9 10 11

INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE

---------- ---------- ---------------- --- ------------ ----------------- ---------

HOST

----------------------------------------------------------------

1 1 db1 YES OPEN ACTIVE NORMAL

node1

2 2 db2 YES OPEN ACTIVE NORMAL

node2

SQL> SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time, status, archiver, database_status FROM gv$instance;

INST_ID INSTANCE_NAME HOST_NAME VERSION

---------- ---------------- ---------------------------------------------------------------- -----------------

STARTUP_TIME STATUS ARCHIVE DATABASE_STATUS

------------------- ------------ ------- -----------------

1 db1 node1 11.2.0.1.0

2015-04-17 13:00:33 OPEN STARTED ACTIVE

2 db2 node2 11.2.0.1.0

2015-04-17 13:05:12 OPEN STARTED ACTIVE

王硕 · 发表于 2015-4-19 18:32:47

14.检查SGA和PGA

SQL> show sga

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 603981816 bytes

Database Buffers 230686720 bytes

Redo Buffers 2396160 bytes

SQL> select name ,value/1024/1024/1024 from gv$sysstat where name like '%pga%';

NAME VALUE/1024/1024/1024

---------------------------------------------------------------- --------------------

session pga memory 1.11519291

session pga memory max 1.17171147

session pga memory .900773883

session pga memory max .925554156

SQL> select name ,value/1024/1024/1024 from v$sysstat where name like '%pga%';

NAME VALUE/1024/1024/1024

---------------------------------------------------------------- --------------------

session pga memory 1.11802778

session pga memory max 1.17411909

王硕 · 发表于 2015-4-19 18:34:45

15.检查查询服务器的运行模式和数据库安装选项

SQL> select * from v$option;

PARAMETER VALUE

------------------------------- ----------------------------------

Objects TRUE

Real Application Clusters TRUE

Advanced replication TRUE

Bit-mapped indexes TRUE

Connection multiplexing TRUE

Connection pooling TRUE

Database queuing TRUE

Incremental backup and recovery TRUE

Instead-of triggers TRUE

Parallel backup and recovery TRUE

Parallel execution TRUE

Parallel load TRUE

Point-in-time tablespace recovery TRUE

Fine-grained access control TRUE

Proxy authentication/authorization TRUE

Change Data Capture TRUE

Plan Stability TRUE

Online Index Build TRUE

Coalesce Index TRUE

Managed Standby TRUE

Materialized view rewrite TRUE

Materialized view warehouse refresh TRUE

Database resource manager TRUE

Spatial TRUE

Automatic Storage Management TRUE

Export transportable tablespaces TRUE

Transparent Application Failover TRUE

Fast-Start Fault Recovery TRUE

Sample Scan TRUE

Duplexed backups TRUE

Java TRUE

OLAP Window Functions TRUE

Block Media Recovery TRUE

Fine-grained Auditing TRUE

Application Role TRUE

Enterprise User Security TRUE

Oracle Data Guard TRUE

Oracle Label Security FALSE

OLAP TRUE

Basic Compression TRUE

Join index TRUE

Trial Recovery TRUE

Data Mining TRUE

Online Redefinition TRUE

Streams Capture TRUE

File Mapping TRUE

Block Change Tracking TRUE

Flashback Table TRUE

Flashback Database TRUE

Transparent Data Encryption TRUE

Backup Encryption TRUE

Unused Block Compression TRUE

Oracle Database Vault FALSE

Result Cache TRUE

SQL Plan Management TRUE

SecureFiles Encryption TRUE

Real Application Testing TRUE

Flashback Data Archive TRUE

DICOM TRUE

Active Data Guard TRUE

Server Flash Cache TRUE

Advanced Compression TRUE

XStream TRUE

Deferred Segment Creation TRUE

65 rows selected.

王硕 · 发表于 2015-4-19 18:35:10

16.用户检查

SQL> select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED

------------------------------ -------------------------------- ------------------------------ --------------------- ---------

MGMT_VIEW OPEN SYSTEM TEMP 15-AUG-09

SYS OPEN SYSTEM TEMP 15-AUG-09

SYSTEM OPEN SYSTEM TEMP 15-AUG-09

DBSNMP OPEN SYSAUX TEMP 15-AUG-09

SYSMAN OPEN SYSAUX TEMP 15-AUG-09

OUTLN EXPIRED & LOCKED SYSTEM TEMP 15-AUG-09

FLOWS_FILES EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

MDSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

ORDSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

EXFSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

WMSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

APPQOSSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

APEX_030200 EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

OWBSYS_AUDIT EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

ORDDATA EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

CTXSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

ANONYMOUS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

XDB EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

ORDPLUGINS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

OWBSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

SI_INFORMTN_SCHEMA EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

OLAPSYS EXPIRED & LOCKED SYSAUX TEMP 15-AUG-09

SCOTT EXPIRED & LOCKED USERS TEMP 15-AUG-09

ORACLE_OCM EXPIRED & LOCKED USERS TEMP 15-AUG-09

XS$NULL EXPIRED & LOCKED USERS TEMP 15-AUG-09

MDDATA EXPIRED & LOCKED USERS TEMP 15-AUG-09

DIP EXPIRED & LOCKED USERS TEMP 15-AUG-09

APEX_PUBLIC_USER EXPIRED & LOCKED USERS TEMP 15-AUG-09

SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED USERS TEMP 15-AUG-09

SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED USERS TEMP 15-AUG-09

30 rows selected.

SQL> select a.username , a.temporary_tablespace "Temporary Tablespace" , b.contents

from dba_users a , dba_tablespaces b

where a.temporary_tablespace=b.tablespace_name

and b.contents <> 'TEMPORARY';

no rows selected

王硕 · 发表于 2015-4-19 18:35:28

17.控制文件检查

SQL> col name for a60

SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS

------- ------------------------------------------------------------ --- ---------- --------------

+DATA/db/controlfile/current.260.776365491 NO 16384 1130

+ARCH/db/controlfile/current.256.776365491 YES 16384 1130

王硕 · 发表于 2015-4-19 18:35:40

18.无效对象检查

SQL> col OBJECT_NAME for a24

SQL> SELECT owner , object_name, object_type,status ,LAST_DDL_TIME FROM dba_objects WHERE status like 'INVALID';

no rows selected

王硕 · 发表于 2015-4-19 18:36:19

19.表空间和数据文件检查

1）数据文件

SQL> col file_name for a56

SQL> set linesize 300

SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files;

select count(*) from v$datafile;

FILE_ID FILE_NAME TABLESPACE_NAME AUT

---------- -------------------------------------------------------- --------------- ---

4 +DATA/db/datafile/users.259.776365283 USERS YES

3 +DATA/db/datafile/undotbs1.258.776365283 UNDOTBS1 YES

2 +DATA/db/datafile/sysaux.257.776365283 SYSAUX YES

1 +DATA/db/datafile/system.256.776365281 SYSTEM YES

5 +DATA/db/datafile/undotbs2.264.776365753 UNDOTBS2 YES

SQL> show parameter db_files;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_files integer 200

SQL> select name from v$datafile

2 ;

NAME

--------------------------------------------------------------------------------

+DATA/db/datafile/system.256.776365281

+DATA/db/datafile/sysaux.257.776365283

+DATA/db/datafile/undotbs1.258.776365283

+DATA/db/datafile/users.259.776365283

+DATA/db/datafile/undotbs2.264.776365753

SQL> select member from v$logfile

2 ;

MEMBER

--------------------------------------------------------------------------------

+DATA/db/onlinelog/group_2.262.776365501

+ARCH/db/onlinelog/group_2.258.776365505

+DATA/db/onlinelog/group_1.261.776365493

+ARCH/db/onlinelog/group_1.257.776365499

+DATA/db/onlinelog/group_3.265.776365887

+ARCH/db/onlinelog/group_3.259.776365889

+DATA/db/onlinelog/group_4.266.776365891

+ARCH/db/onlinelog/group_4.260.776365903

8 rows selected.

SQL> select name from v$controlfile

2 ;

NAME

--------------------------------------------------------------------------------

+DATA/db/controlfile/current.260.776365491

+ARCH/db/controlfile/current.256.776365491

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

+DATA/db/tempfile/temp.263.776365543

SQL> col name for a20

SQL> SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafile UNION ALL SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$tempfile;

FILE# TS# NAME STATUS SIZE_MB

---------- ---------- -------------------- ------- ----------

1 0 +DATA/db/datafile/sy SYSTEM 680

stem.256.776365281

2 1 +DATA/db/datafile/sy ONLINE 570

saux.257.776365283

3 2 +DATA/db/datafile/un ONLINE 95

dotbs1.258.776365283

4 4 +DATA/db/datafile/us ONLINE 5

ers.259.776365283

FILE# TS# NAME STATUS SIZE_MB

---------- ---------- -------------------- ------- ----------

5 5 +DATA/db/datafile/un ONLINE 25

dotbs2.264.776365753

1 3 +DATA/db/tempfile/te ONLINE 32

mp.263.776365543

6 rows selected.

帐号		自动登录	找回密码
密码			注册