oracle 10g RAC简单管理

oracle 10g RAC简单管理(一)

1、检查应用程序资源的状态

$ who i am
oracle pts/1 Nov 21 09:18 (192.168.1.88)
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.devdb.db application ONLINE ONLINE rac1
ora....b1.inst application ONLINE ONLINE rac1
ora....b2.inst application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

$ srvctl status nodeapps -n rac1
VIP is running on node: rac1
GSD is running on node: rac1
Listener is running on node: rac1
ONS daemon is running on node: rac1

$ srvctl status nodeapps -n rac2
VIP is running on node: rac2
GSD is running on node: rac2
Listener is running on node: rac2
ONS daemon is running on node: rac2

$ srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.

$ srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.

$ srvctl status database -d devdb
Instance devdb1 is running on node rac1
Instance devdb2 is running on node rac2

2、检查Oracle集群件的状态

在rac1主机上运行
$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

在rac2主机上运行
$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

3、列出RAC实例

在rac1或rac2运行都可以

SQL> select instance_name,host_name,archiver,thread#,status from gv$instance;

INSTANCE_NAME HOST_NAME ARCHIVE THREAD# STATUS
---------------- ---------------------------------------------------------------- ------- ---------- ------------
devdb1 rac1.mycorpdomain.com STARTED 1 OPEN
devdb2 rac2.mycorpdomain.com STARTED 2 OPEN

4、检查数据库配置

$ export ORACLE_SID=devdb1
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 21 10:16:27 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show sga

Total System Global Area 209715200 bytes
Fixed Size 1218556 bytes
Variable Size 100665348 bytes
Database Buffers 104857600 bytes
Redo Buffers 2973696 bytes

SQL> select file_name,bytes/1024/1024 from dba_data_files;
FILE_NAME BYTES/1024/1024
-------------------------------------------------- ---------------
+DG1/devdb/datafile/users.259.639177583 5
+DG1/devdb/datafile/sysaux.257.639177581 260
+DG1/devdb/datafile/undotbs1.258.639177583 25
+DG1/devdb/datafile/system.256.639177579 480
+DG1/devdb/datafile/undotbs2.264.639177793 25

SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -------------------------------------------------------------------------
2 ONLINE +DG1/devdb/onlinelog/group_2.262.639177703
2 ONLINE +RECOVERYDEST/devdb/onlinelog/group_2.258.639177707
1 ONLINE +DG1/devdb/onlinelog/group_1.261.639177695
1 ONLINE +RECOVERYDEST/devdb/onlinelog/group_1.257.639177701
3 ONLINE +DG1/devdb/onlinelog/group_3.265.639177909
3 ONLINE +RECOVERYDEST/devdb/onlinelog/group_3.259.639177915
4 ONLINE +DG1/devdb/onlinelog/group_4.266.639177917
4 ONLINE +RECOVERYDEST/devdb/onlinelog/group_4.260.639177923

$ export ORACLE_SID=+ASM1
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 21 10:26:30 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show sga

Total System Global Area 92274688 bytes
Fixed Size 1217884 bytes
Variable Size 65890980 bytes
ASM Cache 25165824 bytes
SQL> show parameter asm_disk

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string DG1, RECOVERYDEST
asm_diskstring string

SQL> select group_number,name,state,type,total_mb,usable_file_mb from v$asm_diskgroup;

GROUP_NUMBER NAME STATE TYPE TOTAL_MB USABLE_FILE_MB
------------ ------------------------- ----------- ------ ---------- --------------
1 DG1 MOUNTED NORMAL 6134 1701
2 RECOVERYDEST MOUNTED EXTERN 2047 1713

SQL> select
name,path,header_status,total_mb free_mb,trunc(bytes_read/1024/1024) read_mb,trunc(bytes_written/1024/1024) write_mb
from v$asm_disk;

NAME PATH HEADER_STATU FREE_MB READ_MB WRITE_MB
------------------------- -------------------- ------------ ---------- ---------- ----------
DG1_0000 /dev/raw/raw1 MEMBER 3067 487 1735
DG1_0001 /dev/raw/raw2 MEMBER 3067 1235 1734
RECOVERYDEST_0000 /dev/raw/raw3 MEMBER 2047 40 675

5、表空间的创建和使用

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 21 10:41:08 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn system/*******@devdb
Connected.
SQL> create tablespace TEST datafile '+DG1' size 2m;

Tablespace created.

SQL> select file_name,tablespace_name,bytes from dba_data_files where tablespace_name='TEST';
FILE_NAME TABLESPACE_NAME BYTES
-------------------------------------------------- ------------------------------ ----------
+DG1/devdb/datafile/test.272.639225741 TEST 2097152

6、日志组的增加

SQL> select * from v$log
2 ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 52428800 2 YES INACTIVE 464826 20-NOV-07
2 1 3 52428800 2 NO CURRENT 648820 21-NOV-07
3 2 3 52428800 2 NO CURRENT 653850 21-NOV-07
4 2 2 52428800 2 YES INACTIVE 545721 21-NOV-07

SQL> alter database add logfile thread 1 group 5 size 50m;

Database altered.

SQL> alter database add logfile thread 2 group 6 size 50m;

Database altered.

SQL> select group#,thread#,bytes,members,status from v$log;

GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 52428800 2 INACTIVE
2 1 52428800 2 CURRENT
3 2 52428800 2 CURRENT
4 2 52428800 2 INACTIVE
5 1 52428800 2 UNUSED
6 2 52428800 2 UNUSED

6 rows selected.

oracle 10g RAC简单管理(二)

SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ----------- ---------- ----------------- ---------------
+RECOVERYDEST 1572864000 331366400 0 7

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .97 0 1
ONLINELOG 20 0 6
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

2、启动和停止应用程序资源。

srvctl start nodeapps -n
srvctl start nodeapps -n
srvctl start asm -n
srvctl start asm -n
srvctl start database -d
srvctl start service -d -s
crs_stat -t

srvctl stop service -d -s
srvctl stop database -d
srvctl stop asm -n
srvctl stop asm -n
srvctl stop nodeapps -n
srvctl stop nodeapps -n
crs_stat -t