郑重声明:由于本人水平有限,如有不对的地方请大神指正不甚感激(327568824@qq.com)
由于最近种种原因没有更新博客,本打算更新索引使用方法,但由于最近对RAC操作比较多,记录了一些常用的命令。索引后序增加
1.1.1.1 查看spfile位置
SQL> show parameter spfile;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
spfile string +DATA0/racdb007/spfileracdb007.ora
1.1.1.2 修改spfile参数
SQL> show parameter session;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 247
shared_server_sessions integer
SQL> alter system set sessions=250 scope=spfile;
System altered.
注意:有些可以马上生效的参数不用加scope参数即可使用,如果加了scope命令那么就必须重启数据库才能生效
1.1.1.3 设置SPFILE路径
[oracle@rac4 dbs]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/dbs
[oracle@rac4 dbs]$ catinitemp2.ora
SPFILE='+DATA0/emp/spfileemp.ora'
1.1.2RAC环境
1.1.2.1 查看ORACLE守护进程
[oracle@rac3 ~]$ ls/etc/init.d/init.*
/etc/init.d/init.ohasd
10G为
1.1.2.2 查看节点信息
[grid@rac3~]$ olsnodes --help
Usage: olsnodes [ [-n] [-i] [-s] [-t][<node> | -l [-p]] | [-c] ] [-g] [-v]
where
-n print node number with the node name显示节点名
-p print private interconnect address for the local node显示priIP
-i print virtual IP address with the node name显示VIP
<node> print information for the specified node
-l print information for the local node 显示自己节点信息
-s print node status - active or inactive 显示节点状态
-t print node type - pinned or unpinned 显示节点类型(固定、不固定)
-g turn on logging 打开日志记录
-v Run in debug mode; use at direction of Oracle Support only.开启debug模式
-c print clusterware name输出集群名字
[grid@rac3~]$ olsnodes -i -n -s -t -g
rac3 1 rac3-vip Active Unpinned
rac4 2 rac4-vip Active Unpinned
1.1.2.3 查看集群网络状态
[grid@rac3~]$ oifcfg
Name:
oifcfg - Oracle Interface Configuration Tool.
Usage: oifcfg iflist [-p [-n]]
oifcfg setif {-node <nodename> | -global}{<if_name>/<subnet>:<if_type>}..设置网卡
oifcfg getif [-node <nodename> | -global] [ -if<if_name>[/<subnet>] [-type <if_type>] ]输出网卡信息
oifcfg delif {{-node <nodename> | -global}[<if_name>[/<subnet>]] [-force] | -force}
oifcfg [-help]
<nodename> - name of the host, as known to a communicationsnetwork
<if_name> - name by whichthe interface is configured in the system
<subnet> - subnet addressof the interface
<if_type> - type of theinterface { cluster_interconnect | public }
[grid@rac3~]$ oifcfg getif
eth0 10.22.19.0 global public
eth1 172.16.6.0 global cluster_interconnect
1.1.2.4 查看CRS状态
[root@rac3cssd]# crs_stat -v -t
Name Type R/RA F/FT Target(目标状态)State(当前状态) Host
----------------------------------------------------------------------
ora.DATA.dg ora....up.type 0/5 0/ ONLINE ONLINE rac3
ora.DATA0.dg ora....up.type 0/5 0/ ONLINE ONLINE rac3
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac3 监听器
ora....N1.lsnrora....er.type 0/5 0/0 ONLINE ONLINE rac4
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac3 asm实例
ora.cvu ora.cvu.type 0/5 0/0 ONLINE ONLINE rac4
ora.gsd ora.gsd.type 0/5 0/ OFFLINE OFFLINE CRS的global的进程
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac3
ora.oc4j ora.oc4j.type 0/1 0/2 ONLINE ONLINE rac4
ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac3
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac3
ora....C3.lsnr application 0/5 0/0 ONLINE ONLINE rac3
ora.rac3.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac3.ons application 0/3 0/0 ONLINE ONLINE rac3
ora.rac3.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac3
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac4
ora....C4.lsnr application 0/5 0/0 ONLINE ONLINE rac4
ora.rac4.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac4.ons application 0/3 0/0 ONLINE ONLINE rac4
ora.rac4.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac4
ora....b007.db ora....se.type 0/2 0/1 ONLINE ONLINE rac3 数据库(只有一个)
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac4
1.1.2.5 查看CRS某个详细资源
[root@rac3cssd]# crs_stat -p ora.DATA.dg
NAME=ora.DATA.dg
TYPE=ora.diskgroup.type
ACTION_SCRIPT=
AUTO_START=never
CHECK_INTERVAL=300
DESCRIPTION=CRS resource type definition for ASMdisk group resource
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
START_TIMEOUT=900
STOP_TIMEOUT=180
UPTIME_THRESHOLD=1d
1.1.2.6 查看CRS权限
[root@rac3cssd]# crs_stat -ls
Name Owner PrimaryPrivGrp Permission
-----------------------------------------------------------------
ora.DATA.dg grid oinstall rwxrwxr--
ora.DATA0.dg grid oinstall rwxrwxr--
ora....ER.lsnr grid oinstall rwxrwxr--
ora....N1.lsnr grid oinstall rwxr-xr--
ora.asm grid oinstall rwxrwxr--
ora.cvu grid oinstall rwxrwxr--
ora.gsd grid oinstall rwxr-xr--
ora....network root root rwxr-xr--
ora.oc4j grid oinstall rwxrwxr--
ora.ons grid oinstall rwxr-xr--
ora....SM1.asm grid oinstall rwxrwxr--
ora....C3.lsnr grid oinstall rwxrwxr--
ora.rac3.gsd grid oinstall rwxr-xr--
ora.rac3.ons grid oinstall rwxr-xr--
ora.rac3.vip root root rwxr-xr--
ora....SM2.asm grid oinstall rwxrwxr--
ora....C4.lsnr grid oinstall rwxrwxr--
ora.rac4.gsd grid oinstall rwxr-xr--
ora.rac4.ons grid oinstall rwxr-xr--
ora.rac4.vip root root rwxr-xr--
ora....b007.db oracle oinstall rwxrwxr--
ora.scan1.vip root root rwxr-xr—
1.1.2.7 将所有命令的运行情况都输出出来
[root@rac4rpm]# export SRVM_TRACE=TRUE
那么后面的命令会全部输出出来而不是隐藏执行
1.1.2.8 启动CRS
[grid@rac4~]$crsctl start crs
1.1.2.9 启动实例
[grid@rac4~]$ srvctl start instance -d racdb007 –i racdb0071
1.1.2.10 数据库启动
[grid@rac4~]$ srvctl start database -d racdb007
1.1.2.11 启动数据库到nomount状态
[grid@rac4~]$ srvctl start database -d racdb007 -o nomount
1.1.2.12 查看数据库运行状态
[grid@rac4~]$ srvctl status database -d racdb007
实例 racdb0071 没有在 rac3 节点上运行
实例 racdb0072 没有在 rac4 节点上运行
1.1.2.13 数据库关闭
[grid@rac4 ~]$ srvctl stop database -dracdb007
1.1.2.14 正常数据库关闭
[grid@rac4 ~]$ srvctl stop database -dracdb007 -o immediate
1.1.2.15 在某个节点添加某个数据库的某个实例
[root@rac4rpm]# srvctl add instance -d database -n rac2 -i racdb007
1.1.2.16 添加服务
[root@rac4rpm]# srvctl add service -d newdb -s myservice -r rac3 -a rac4 -P BASIC
1.1.2.17 查看数据库创建时间
SQL>select name,created from v$database;
NAME CREATED
--------- ---------
RACDB007 28-JAN-15
1.1.2.18 查看集群在线日志
SQL> select * from gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- -------------------- ---------- ---------- --- ---------------- ------------- --------------------- ---------
2 1 1 169 52428800 512 1 YES INACTIVE 12563837 12-APR-15 12688114 13-APR-15
2 2 1 170 52428800 512 1 NO CURRENT 1268811413-APR-15 2.8147E+14
2 3 2 179 52428800 512 1 NO CURRENT 1273651113-APR-15 2.8147E+14
2 4 2 178 52428800 512 1 YES INACTIVE 12729778 13-APR-15 12736511 13-APR-15
1 1 1 169 52428800 512 1 YES INACTIVE 12563837 12-APR-15 12688114 13-APR-15
1 2 1 170 52428800 512 1 NO CURRENT 1268811413-APR-15 2.8147E+14
1 3 2 179 52428800 512 1 NO CURRENT 1273651113-APR-15 2.8147E+14
1 4 2 178 52428800 512 1 YES INACTIVE 12729778 13-APR-15 12736511 13-APR-15
8 rows selected.
1.1.2.19 查看实例运行状态
SQL>select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb0072 OPEN
racdb0071 OPEN
1.1.2.20 查看实数据库是否归档
SQL> select name,log_mode from gv$database;
NAME LOG_MODE
--------- ------------
RACDB007 ARCHIVELOG
RACDB007 ARCHIVELOG
1.1.2.21 查看数据库是否开启闪回功能
SQL>select name,flashback_on from gv$database;
NAME FLASHBACK_ON
---------------------------
RACDB007 NO
RACDB007 NO
1.1.2.22 RAC创建表空间
CREATE TABLESPACE rman_tablespace
LOGGING
DATAFILE '+DATA'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
1.1.2.23 RAC创建临时表空间
CREATE TEMPORARY TABLESPACE rman_tablespace_temp
TEMPFILE '+DATA'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
1.1.2.24 RAC创建用户、用户设置密码、用户解锁
SQL> CREATE USER rman IDENTIFIED BY cat TEMPORARY TABLESPACE rman_tablespace_tempDEFAULT TABLESPACE rman_tablespace ACCOUNT UNLOCK;
User created.
Logout
1.1.2.25 查看模块
[root@rac3 ~]# crsctl lsmodules css
List CSSD Debug Module: CLSF
List CSSD Debug Module: CSSD
List CSSD Debug Module: GIPCCM
List CSSD Debug Module: GIPCGM
List CSSD Debug Module: GIPCNM
List CSSD Debug Module: GPNP
List CSSD Debug Module: OLR
List CSSD Debug Module: SKGFD
1.1.2.26 对CSSD进程Debug
[root@rac3 ~]# crsctl debug logcss "CSSD:1"
CRS-4151:DEPRECATED: use crsctl set log {css|crs|evm}
Set CSSD Module:CSSD Log Level: 1
[root@rac3 cssd]# tail -10f ocssd.log
2015-05-2719:16:51.361: [ CSSD][4283377408]clssnmSendingThread: sent 5 status msgs to all nodes
2015-05-2719:16:56.364: [ CSSD][4283377408]clssnmSendingThread: sending status msg to all nodes
2015-05-2719:16:56.364: [ CSSD][4283377408]clssnmSendingThread:sent 5 status msgs to all nodes
2015-05-2719:17:01.425: [ CSSD][4283377408]clssnmSendingThread: sending status msg to all nodes
2015-05-2719:17:01.425: [ CSSD][4283377408]clssnmSendingThread: sent 5 status msgs to all nodes
2015-05-2719:17:05.510: [ CSSD][4283377408]clssnmSendingThread: sending status msg to all nodes
2015-05-2719:17:05.511: [ CSSD][4283377408]clssnmSendingThread: sent 4 status msgs to all nodes
2015-05-2719:17:09.514: [ CSSD][4283377408]clssnmSendingThread:sending status msg to all nodes
2015-05-2719:17:09.514: [ CSSD][4283377408]clssnmSendingThread: sent 4 status msgs to all nodes
2015-05-2719:17:11.486: [ CSSD][219125504]setting log of CLSF to 0
^C
[root@rac3 cssd]# pwd
/u01/11.2.0.3/grid/log/rac3/cssd
1.1.2.27 查看OCR里面信息
[root@rac3cssd]# ocrdump -stdout|more
05/27/2015 21:21:11
/u01/11.2.0.3/grid/bin/ocrdump.bin -stdout
[SYSTEM]
UNDEF :
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS,GROUP_PERMISSION : PROCR_READ, OTHER_PERMISSION : PROCR_READ, USER_NAME : root,GROUP_NAME : root}
[SYSTEM.version]
UB4 (10) : 5
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS,GROUP_PERMISSION : PROCR_READ, OTHER_PERMISSION : PROCR_READ, USER_NAME : root,GROUP_NAME : root}
[SYSTEM.version.activeversion]
ORATEXT : 11.2.0.3.0
SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS,GROUP_PERMISSION : PROCR_READ, OTHER_PERMISSION : PROCR_READ, USER_NAME : root,GROUP_NAME : root}
1.1.2.28 查看OCR状态
[root@rac3 cssd]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2860
Available space (kbytes) : 259260
ID : 40124081
Device/File Name : +DATA
Device/Fileintegrity check succeeded
Device/Filenot configured
Device/Filenot configured
Device/Filenot configured
Device/Filenot configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
1.1.2.29 备份OCR
一般资源变的时候OCR的信息会改变
将OCR信息保存到当前路径
[root@rac3 cssd]# ocrconfig -export test.dmp
1.1.2.30 恢复OCR
[root@rac3 cssd]# ocrconfig -import test.dmp