[Oracle 10g RAC 구축] 7. RAC 데이터베이스 환경 확인

1. 애플리케이션 리소스의 상태 확인

rac1-> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.....CRM.cs application    ONLINE    ONLINE    rac1
ora....db1.srv application    ONLINE    ONLINE    rac1
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

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

rac1-> 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

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

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

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

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

rac1-> srvctl status service -d devdb
Service CRM is running on instance(s) devdb1

 

rac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 2 18:43:42 2010

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> col host_name for a20
SQL> select instance_name, host_name, archiver, thread#, status
  2  from gv$instance;

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

 

rac1-> sqlplus system@devdb1

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 2 18:45:22 2010

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

Enter password:

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>

 

rac1-> sqlplus system@devdb2

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 2 18:45:53 2010

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

Enter password:

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> 

 

 

rac1-> sqlplus system@devdb

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 2 18:46:17 2010

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

Enter password:

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>

 

 

rac1-> export ORACLE_SID=devdb1
rac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 2 18:46:52 2010

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             138414084 bytes
Database Buffers           67108864 bytes
Redo Buffers                2973696 bytes

SQL> col file_name for a60
SQL> select file_name,bytes/1024/1024 from dba_data_files;

FILE_NAME                                                    BYTES/1024/1024
------------------------------------------------------------ ---------------
+DG1/devdb/datafile/users.259.722275859                                    5
+DG1/devdb/datafile/sysaux.257.722275859                                 250
+DG1/devdb/datafile/undotbs1.258.722275859                                30
+DG1/devdb/datafile/system.256.722275859                                 480
+DG1/devdb/datafile/example.264.722275961                                100
+DG1/devdb/datafile/undotbs2.265.722276093                                25
+DG1/devdb/datafile/test_d.269.722277577                                  10

7 rows selected.

 

SQL> col member for a55
SQL> select group#, type, member, is_recovery_dest_file
  2  from v$logfile
  3  order by group#;

    GROUP# TYPE    MEMBER                                                  IS_
---------- ------- ------------------------------------------------------- ---
         1 ONLINE  +DG1/devdb/onlinelog/group_1.261.722275929              NO
         1 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_1.257.722275931     YES
         2 ONLINE  +DG1/devdb/onlinelog/group_2.262.722275933              NO
         2 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_2.258.722275935     YES
         3 ONLINE  +DG1/devdb/onlinelog/group_3.266.722276403              NO
         3 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_3.259.722276407     YES
         4 ONLINE  +DG1/devdb/onlinelog/group_4.267.722276409              NO
         4 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_4.260.722276413     YES
         5 ONLINE  +DG1/devdb/onlinelog/group_5.270.722277677              NO
         5 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_5.261.722277679     YES
         6 ONLINE  +DG1/devdb/onlinelog/group_6.271.722277947              NO
         6 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_6.262.722277949     YES

12 rows selected.

 

rac1-> export ORACLE_SID=+ASM1
rac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 2 18:57:12 2010

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>

 

SQL> set lines 150
SQL> col name for a15
SQL> select group_number, name, allocation_unit_size alloc_unit_size, state,
  2  type, total_mb, usable_file_mb
  3  from v$asm_diskgroup;

GROUP_NUMBER NAME            ALLOC_UNIT_SIZE STATE       TYPE     TOTAL_MB USABLE_FILE_MB
------------ --------------- --------------- ----------- ------ ---------- --------------
           1 DG1                     1048576 MOUNTED     NORMAL       6134           1575
           2 RECOVERYDEST            1048576 MOUNTED     EXTERN       2047           1601

 

SQL> col name for a20
SQL> col path for a15

SQL> select name, path, header_status, total_mb free_mb,
  2  trunc(bytes_read/1024/1024) read_mb,

  3  trunc(bytes_written/1024/1024) write_mb
  4  from v$asm_disk;

NAME                 PATH            HEADER_STATU    FREE_MB    READ_MB   WRITE_MB
-------------------- --------------- ------------ ---------- ---------- ----------
                     ORCL:VOL1       UNKNOWN            3067
                     ORCL:VOL2       UNKNOWN            3067
                     ORCL:VOL3       UNKNOWN            2047
DG1_0000             /dev/raw/raw1   MEMBER             3067         67         50
RECOVERYDEST_0000    /dev/raw/raw3   MEMBER             2047          5         30
DG1_0001             /dev/raw/raw2   MEMBER             3067        128         50

6 rows selected.

 

2. 테이블 스페이스 생성

SQL> connect system/oracle@devdb
Connected.
SQL> create tablespace test_d datafile '+DG1' size 10M;

Tablespace created.

SQL> col file_name for a30
SQL> select file_name, tablespace_name, bytes
  2  from dba_data_files
  3  where tablespace_name='TEST_D';

FILE_NAME                      TABLESPACE_NAME                     BYTES
------------------------------ ------------------------------ ----------
+DG1/devdb/datafile/test_d.269 TEST_D                           10485760
.723326475

 

3. 온라인 리두 로그파일 그룹 생성

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

 

SQL> col member for a55
SQL> select group#, type, member, is_recovery_dest_file
  2  from v$logfile
  3  where group# in (5,6)
  4  order by group#;

    GROUP# TYPE    MEMBER                                                  IS_
---------- ------- ------------------------------------------------------- ---
         5 ONLINE  +DG1/devdb/onlinelog/group_5.271.723326785              NO
         5 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_5.262.723326789     YES
         6 ONLINE  +DG1/devdb/onlinelog/group_6.270.723326803              NO
         6 ONLINE  +RECOVERYDEST/devdb/onlinelog/group_6.261.723326807     YES

 

4. 플래시 복구 영역의 공간 사용 현황 점검.

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.

 

5, 애플리케이션 리소스의 시작/중단.

srvctl start nodeapps -n <node1 hostname>
srvctl start nodeapps -n <node2 hostname>
srvctl start asm -n <node1 hostname>
srvctl start asm -n <node2 hostname>
srvctl start database -d <database name>
srvctl start service -d <database name> -s <service name>
crs_stat -t

srvctl stop service -d <database name> -s <service name>
srvctl stop database -d <database name>
srvctl stop asm -n <node1 hostname>
srvctl stop asm -n <node2 hostname>
srvctl stop nodeapps -n <node1 hostname>
srvctl stop nodeapps -n <node2 hostname>
crs_stat -t