Oracle笔记（5）Oracle RAC维护
1.Voting文件
1.Voting文件中存储了节点程以信息，每个节点准备启动并且试图加入RAC集群时，都需要读取Voting文件。
2.Voting文件需要通过冗余保证其安全。
3.从11.2开始，Clusterware被修改后，Voting文件会自动被备份到OCR文件中。
4.当votedisk属于asm磁盘组时，无法通过crsctl命令进行新增、删除、移动操作
#查看
SQL> select a.name,b.name,b.path,b.voting_file from v$asm_disk b,v$asm_diskgroup a where a.group_number=b.group_number;
[grid@rac1 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE f161b94a350a4ff2bfecd933b9def84b (/dev/mapper/crs1) [CRS]
2. ONLINE 1572413690204f69bfcff159c9798cde (/dev/mapper/crs2) [CRS]
3. ONLINE a26dfe04ab4b4f5ebf89e362d897cd91 (/dev/mapper/crs3) [CRS]
#删除
[grid@rac1 ~]$ crsctl delete css votedisk a26dfe04ab4b4f5ebf89e362d897cd91
CRS-4258: Addition and deletion of voting files are not allowed because the voting files are on ASM
(存储在+ASM上，因此这里删不掉！)
#新增
[grid@rac1 ~]$ crsctl add css votedisk +DATA
CRS-4671: This command is not supported for ASM diskgroups.
CRS-4000: Command Add failed, or completed with errors.
2.OCR文件
1.OCR文件用于纯粹Clusterware及数据库配置信息
#查看 OCR位置
[root@rac1 bin]# cat /etc/oracle/ocr.loc
#Device/file +crs getting replaced by device +CRS/rac-cluster/OCRFILE/registry.255.1036255215
ocrconfig_loc=+CRS/rac-cluster/OCRFILE/registry.255.1036255215
#新增一个OCR
[root@rac1 bin]# ./ocrconfig -add +DATA
[root@rac1 bin]# cat /etc/oracle/ocr.loc
#Device/file getting replaced by device +DATA/rac-cluster/OCRFILE/registry.255.1041202749
ocrconfig_loc=+CRS/rac-cluster/OCRFILE/registry.255.1036255215
ocrmirrorconfig_loc=+DATA/rac-cluster/OCRFILE/registry.255.104120274
#删除一个OCR
[root@rac1 bin]# ./ocrconfig -delete +DATA
[root@rac1 bin]# cat /etc/oracle/ocr.loc
#Device/file +DATA/rac-cluster/OCRFILE/registry.255.1041202749 being deleted
ocrconfig_loc=+CRS/rac-cluster/OCRFILE/registry.255.1036255215
local_only=false[root@rac1 bin]#
#迁移OCR
[root@rac1 bin]# ./ocrconfig -replace +DATA -replacement +OCR
#备份
#还原
3.管理集群
https://www.cnblogs.com/-abm/p/9253594.html
1.srvctl
srvctl status instance -d racdb -i racdb1
srvctl stop instance -d racdb -i racdb1,racdb2
srvctl start instance -d racdb -i racdb1,racdb2
srvctl status database -d racdb
srvctl stop database -d racdb
srvctl stop database -d racdb -o abort
srvctl start database -d racdb
srvctl start database -d racdb -o mount
srvctl status listener
srvctl start listener -n server1
srvctl start listener -n server1,server1
srvctl stop listener -n server1
srvctl stop listener -n server1,server1
srvctl status asm
srvctl start asm -n server1
srvctl start asm -n server1,server1
srvctl stop asm -n server1
srvctl stop asm -n server1,server1
srvctl status diskgroup -g DATA
srvctl status diskgroup -g DATA -n rac1
srvctl status diskgroup -g DATA -n rac1,rac2
srvctl stop diskgroup -g DATA -n rac1,rac2
srvctl stop diskgroup -g DATA -n rac1,rac2 -f #正在访问的无法关闭 需要 -f 强制关闭
srvctl start diskgroup -g DATA -n rac1,rac2
srvctl status scan -i 1
srvctl status vip -i rac1-vip
通过enable、disable、remove、add等子命令可以对各种自由进行激活、禁止、删除、添加等操作
srvctl enable listener -l LISTENER -n rac1
srvctl disable listener -l LISTENER -n rac1
srvctl remove listener -l LISTENER
srvctl add listener -l LISTENER -p 1521 -o /u01/app/oracle
#查看RAC网络配置中的网络配置
oifcfg getif
#重启集群
在root下运行
# crsctl stop crs
# crsctl start crs
#检查状态
[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 bin]# ./crsctl stop cluster -all
[root@rac1 bin]# ./crsctl start cluster -all
[root@swnode1 ]# crsctl stop cluster -all -----停止所有节点集群服务
[root@swnode1 ]# crsctl stop cluster -------停止本节点集群服务
4.asm
1.rac集群中ASM实例有多个，但是参数文件只有一个
#查看磁盘组名称、状态、冗余级别、空间情况
SQL> select name,state,type,total_mb,free_mb from v$asm_diskgroup;
#查看磁盘组信息
SQL> select group_number,path,state,total_mb,free_mb from v$asm_disk;
GROUP_NUMBER PATH STATE TOTAL_MB FREE_MB
------------ ------------------------------ -------- ---------- ----------
2 /dev/mapper/data1 NORMAL 512000 507456
1 /dev/mapper/crs3 NORMAL 102400 102092
3 /dev/mapper/data2 NORMAL 157344 157212
1 /dev/mapper/crs1 NORMAL 102400 102096
1 /dev/mapper/crs2 NORMAL 102400 102096
#查询ASM实例对磁盘组访问情况
SQL> select group_number,instance_name,db_name,status from v$asm_client;
GROUP_NUMBER INSTANCE_NAME DB_NAME STATUS
------------ ------------------------------ -------- ------------
0 +ASM1 racdb CONNECTED
2 +ASM1 racdb CONNECTED
#磁盘组中的磁盘数目发生改变时，asm将自动对其执行重新平衡。
#平衡能力从0~11分为12级别，0代表停止平衡。
SQL> alter diskgroup DATA rebalance power 3;
SQL> alter diskgroup DATA rebalance power 3 wait;
https://www.cnblogs.com/-abm/p/9253594.html
5.asmcmd
1.asmcmd可以管理ASM实例、ASM磁盘组、ASM磁盘组文件及权限
#ASM实例启动关闭
ASMCMD> startup --nomount --pfile asm_init.ora
ASMCMD> shutdown
ASMCMD> shutdown --abort
#查看参数文件位置
ASMCMD> spget
+CRS/rac-cluster/ASMPARAMETERFILE/registry.253.1036255205
ASMCMD> spset +CRS/rac-cluster/ASMPARAMETERFILE/registry.253.1036255205
ASMCMD> spbackup +CRS/rac-cluster/ASMPARAMETERFILE/asmspfile.ora /u01/bakspfile,ora
ASMCMD> spcopy -u /u01/bakspfile,ora +DATA/testspfileASM.ora
ASMCMD> spmove /u01/bakspfile,ora +DATA/testspfileASM.ora
#通过ASMCMD管理ASM磁盘组
ASMCMD> lsdg
ASMCMD> lsdg DATA
ASMCMD> lsdg -G DATA
ASMCMD> lsdsk -G DATA
Path
/dev/mapper/data1
ASMCMD> lsdsk
Path
/dev/mapper/crs1
/dev/mapper/crs2
/dev/mapper/crs3
/dev/mapper/data1
/dev/mapper/data2
#擦看磁盘组磁盘的I/O信息
ASMCMD> lsdsk --statistics -G DATA
ASMCMD> lsdsk --statistics
Reads Write Read_Errs Write_Errs Read_time Write_Time Bytes_Read Bytes_Written Voting_File Path
17 1 0 0 .034234 .012722 69632 4096 Y /dev/mapper/crs1
20 1 0 0 .02673 .012722 81920 4096 Y /dev/mapper/crs2
1178 1 0 0 1.254718 .012722 5713920 4096 Y /dev/mapper/crs3
25035 4094 0 0 81.738371 55.008746 424436736 64561152 N /dev/mapper/data1
1191 1 0 0 1.496498 .004282 5767168 4096 N /dev/mapper/data2
#卸载挂在磁盘
ASMCMD> mount -a #挂载所有
ASMCMD> mount DATA #挂载DATA
ASMCMD> unmount -a #卸载所有
ASMCMD> ummount DATA #卸载载DATA
#脱机联机
ASMCMD> offline -G DATA -D data_001 -t 2h 使磁盘组DATA中的 磁盘data_001脱机
ASMCMD> online -G data -a
ASMCMD> online -G data -D data_001
#重新平衡
ASMCMD> rebal --power 4 data -w #-w 完成后返回提示符
ASMCMD> lsop #查看当前进行的操作
#查看磁盘IO情况（读写次数、数据量、时间、错误次数等）
ASMCMD> iostat -G DATA
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 461677568 68122624
ASMCMD> iostat -t -G DATA
Group_Name Dsk_Name Reads Writes Read_Time Write_Time
DATA DATA_0000 463594496 68292096 89.275765 60.175445
ASMCMD> iostat -e -G DATA
Group_Name Dsk_Name Reads Writes Read_Err Write_Err
DATA DATA_0000 465019904 68466688 0 0
ASMCMD> iostat --io -G DATA
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 27629 4374
#文件操作
ASMCMD> alter diskgroup DATA drop file '+DATA/myfile.ora'
ASMCMD> find +DATA TEMP21.DBF
ASMCMD> find --type controlfile +data/oracl *
ASMCMD> cp +data/oracle.ora /u01/app/oracle/oracle.ora
ASMCMD> ls -lt +DATA
ASMCMD> ls --permission +DATA
User Group Permission Name
RACDB/
#查看文件被打开的情况
ASMCMD> lsof -G DATA
DB_Name Instance_Name Path
racdb racdb1 +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1036260421
racdb racdb1 +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1036260421
racdb racdb1 +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1036260421
racdb racdb1 +DATA/RACDB/A1E860D121B4CB2EE0538D01A8C0C35F/TEMPFILE/temp.268.1036260441
racdb racdb1 +DATA/RACDB/CONTROLFILE/current.261.1036260231
racdb racdb1 +DATA/RACDB/DATAFILE/sysaux.258.1036260147
racdb racdb1 +DATA/RACDB/DATAFILE/system.257.1036260113
racdb racdb1 +DATA/RACDB/DATAFILE/undotbs1.259.1036260161
racdb racdb1 +DATA/RACDB/DATAFILE/undotbs2.269.1036260699
racdb racdb1 +DATA/RACDB/DATAFILE/users.260.1036260163
racdb racdb1 +DATA/RACDB/ONLINELOG/group_1.262.1036260233
racdb racdb1 +DATA/RACDB/ONLINELOG/group_2.263.1036260233
racdb racdb1 +DATA/RACDB/ONLINELOG/group_3.270.1036260983
racdb racdb1 +DATA/RACDB/ONLINELOG/group_4.271.1036260985
racdb racdb1 +DATA/RACDB/TEMPFILE/temp.264.1036260243
集群数据库维护
6.集群会话
#查询当前连接实例
select instance_name,status,host_name from v$instance;
select * from v$active_instances;
#查询会话
select * from v$session --查询当前实例会话
alter system kill session '112,442' immediate;
select * from gv$session --查询集群会话
alter system kill session '112,442,@2' immediate; --删除实例id为2的实例会话。
7.归档日志
select a.group#,a.thread#, a.status,b.MEMBER from v$log a ,v$logfile b where a."GROUP#"=b."GROUP#"
alter database add logfile group 5; --当前实例
alter database add logfile instance 'racdb1' group 5;
alter database add logfile instance 'racdb2' group 6;
alter system switch logfile;
8.修改为归档模式
#1.设置归档路径
alter system set log_archive_dest_1='location=+RECO/RACDB/ARCHIVE' scope=spfile sid='*';
#alter system set log_archive_format='arch%t_%s.%r'; # 设置归档文件格式
#2.停止数据库
srvctl stop database -d racdb
#3.检查后台状态
crsctl stat res -t
#4.将一个节点数据库启动到mount状态
srvctl start instance -d racdb -i racdb1 -o mount
#5.修改数据库的归档模式并启动数据库
alter database archivelog;
alter database open;
#6.检查状态
archive log list;
#7.归档
alter system archive log current;
#8.启动2节点数据库
srvctl start instance -d racdb -i racdb2
9.立即归档
SQL> alter system archive log current;
SQL> alter system archive log instance 'racdb1' current;
SQL> alter system archive log instance 'racdb2' current;
SQL> alter system switch logfile;
SQL> alter system checkpoint local; # 当前实例数据库发出检查点
SQL> alter system checkpoint; # 所有实例数据库发出检查点
SQL> alter system checkpoint global; # 所有实例数据库发出检查点
10.表空间
SQL> select tablespace_name ,file_name from dba_data_files;
SQL> create tablespace TBS01;
SQL> create bigfile tablespace TBS02 datafile '+DATA';
SQL> create tablespace TBS03 datafile '+DATA' size 100M autoextend on next 10M maxsize 1024M;
SQL> select * from database_properties where property_name='DEFAULT_TBS_TYPE';
SQL> alter database set default bigfile tablespace; #修改为默认大文件表空间
SQL> alter database set default smallfile tablespace; #修改为默认小文件表空间
SQL> alter database default tablespace tbs01;
SQL> alter user scott default tablespace tbs02;
临时表空间是多实例共享的，为了降低磁盘I/O冲突，提高数据库性能，可以创建多个临时表空间，把表空间放到一个表空间组，然后把这个表空间组作为数据库默认临时表空间。
SQL> alter tablespace temp tablespace group tmpgroup TMPG01;
SQL> create temporary tablespace temp1 tablespace group TMPG01;
SQL> create temporary tablespace temp2 tablespace group TMPG01;
SQL> alter database default temporary tablespace TMPG01;
在数据库中，如果某个用户数据库用户对数据进行类似排序的操作比较多，可以为这个用户指定不同的默认临时表空间，如果不指定，所有用户都使用默认的临时表空间。
SQL> create temporary tablespace temp04;
SQL> alter database default temporary tablespace temp04;
在数据库中undo，每个实例都需要一个undo表空间。查看当前实例使用的undo表空间
SQL> show parameter undo_tablespace;
SQL> create undo tablespace undotbs02;
SQL> alert system set undo_tablespace=undowtbs02;
*注意，初始化参数undo_tablespace参数修改只在当前实例起作用。
11.控制文件
SQL> show parameter control_files;
SQL> select name, status from v$controlfile;
SQL> alter database backup controlfile to '/tmp/controlfileback.ctl';
SQL> alter database backup controlfile to trace;
# 新增一个控制文件
# 需要在nomount状态
SQL>select name,status from v$controlfile;
[root@rac1 bin]# ./srvctlstop database -d rac
[root@rac1 bin]# ./srvctl start database -d racdb -o nomount
[oracle@rac1~]$ rman target /
RAMN> restore controlfile to '+DATA' from '+DATA/RACDB/CONTROLFILE/Current.261.1036260231'
SQL> alter system set control_files='+DATA/RACDB/CONTROLFILE/Current.261.1036260231','+DATA/RACDB/CONTROLFILE/current.281.1041273331' scope=spfile;
SQL> shutdown immediate
[root@rac1 bin]# ./srvctl stop database -d racdb
[root@rac1 bin]# ./srvctl start database -d racdb
12.rman备份
RMAN>
run {
allocate channel ch1 device type disk format '/backup/%d_%U_%T_%s';
allocate channel ch2 device type disk format '/backup/%d_%U_%T_%s';
backup database plus archivelog;
sql 'alter system arhive log current';
release channel ch1;
release channel ch2;
}
RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';
RMAN> backup archivelog from sequence 121 until sqquence 125;
RMAN> backup archivelog from scn 1000 until scn 2000;
RMAN> backup archivelog all delete input; # 备份归档日志，备份完成后删除用于备份的复制
RMAN> backup archivelog all delete all input; # 备份归档日志，备份完成后删除所有的归档复制
RMAN>
