oracle 10g RAC簡單管理

$ 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.