本文档主要提供一份Oracle 11G RAC数据库存储替换操作过程
计划更换RAC数据库共享存储,即将原存储上的数据全部迁移到新的存储设备上,主要涉及到OCR,VOTING DISK迁移,DATA迁移
OS Version | Redhat Enterprise Linux 6.5 X86_64 |
DB Version | Oracle Database 11g Release 2 (11.2.0.4.0) |
GI Version | Oracle Grid Infrastructure 11g (11.2.0.4.0) |
LUN | 设备名 | 大小 | ASM磁盘名 | ASM磁盘组 | 用途 |
1 | /dev/ASM_LUN_OCR_01 | 10G | ASM_LUN_OCR_01 | OCR | 保存ocr和voting disk的信息 |
2 | /dev/ASM_LUN_OCR_02 | 10G | ASM_LUN_OCR_02 | ||
3 | /dev/ASM_LUN_OCR_03 | 10G | ASM_LUN_OCR_03 | ||
4 | /dev/ ASM_LUN_DATA | 100G | ASM_LUN_DATA | DATA | 保存数据文件 |
5 | /dev/ASM_LUN_ARCH | 50G | ASM_LUN_ARCH | ARCH | 保存归档日志 |
LUN | 设备名 | 大小 | ASM磁盘名 | ASM磁盘组 | 用途 |
1 | /dev/ASM_LUN_OCR_N_01 | 10G | ASM_LUN_OCR_N_01 | OCRN | 保存ocr和voting disk的信息 |
2 | /dev/ASM_LUN_OCR_N_02 | 10G | ASM_LUN_OCR_N_02 | ||
3 | /dev/ASM_LUN_OCR_N_03 | 10G | ASM_LUN_OCR_N_03 | ||
4 | /dev/ASM_LUN_DATA_N | 100G | ASM_LUN_DATA_N | DATAN | 保存数据文件 |
5 | /dev/ASM_LUN_ARCH_N | 50G | ASM_LUN_ARCH_N | ARCHN | 保存归档日志 |
备注:新增存储使用UDEV方式管理,本文档不记录新增存储的详细处理过程
SQL> select name, TOTAL_MB, FREE_MB, state, type, COMPATIBILITY from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE TYPE COMPATIBILITY
---------- ---------- ---------- ----------- ------ --------------------
ARCH 102400 100056 MOUNTED EXTERN 11.2.0.0.0
DATA 102400 99686 MOUNTED EXTERN 11.2.0.0.0
OCR 30720 29794 MOUNTED NORMAL 11.2.0.0.0
SQL> select GROUP_NUMBER, DISK_NUMBER, TOTAL_MB, FREE_MB, NAME, PATH from v$asm_disk order by 1,2;
GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB NAME PATH
------------ ----------- ---------- ---------- ---------- --------------------
1 0 102400 100056 ARCH_0000 /dev/ASM_LUN_ARCH
2 0 102400 99686 DATA_0000 /dev/ASM_LUN_DATA
3 0 10240 9930 OCR_0000 /dev/ASM_LUN_OCR_01
3 1 10240 9933 OCR_0001 /dev/ASM_LUN_OCR_02
3 2 10240 9931 OCR_0002 /dev/ASM_LUN_OCR_03
[root@rac2 dev]# ls -l ASM*
brw-rw---- 1 grid asmadmin 8, 96 Aug 23 12:08 ASM_LUN_ARCH
brw-rw---- 1 grid asmadmin 8, 176 Aug 23 12:08 ASM_LUN_ARCH_N
brw-rw---- 1 grid asmadmin 8, 64 Aug 23 12:24 ASM_LUN_DATA
brw-rw---- 1 grid asmadmin 8, 160 Aug 23 12:08 ASM_LUN_DATA_N
brw-rw---- 1 grid asmadmin 8, 16 Aug 23 12:08 ASM_LUN_OCR_01
brw-rw---- 1 grid asmadmin 8, 32 Aug 23 12:08 ASM_LUN_OCR_02
brw-rw---- 1 grid asmadmin 8, 48 Aug 23 12:08 ASM_LUN_OCR_03
brw-rw---- 1 grid asmadmin 8, 112 Aug 23 12:24 ASM_LUN_OCR_N_1
brw-rw---- 1 grid asmadmin 8, 144 Aug 23 12:24 ASM_LUN_OCR_N_2
brw-rw---- 1 grid asmadmin 8, 128 Aug 23 12:24 ASM_LUN_OCR_N_3
SQL> create diskgroup OCRN normal redundancy disk '/dev/ASM_LUN_OCR_N_1','/dev/ASM_LUN_OCR_N_2','/dev/ASM_LUN_OCR_N_3' attribute 'compatible.asm'='11.2';
Diskgroup created.
查询创建结果:
节点1状态
SQL> select name, TOTAL_MB, FREE_MB, state, type, COMPATIBILITY from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE TYPE COMPATIBILITY
---------- ---------- ---------- ----------- ------ --------------------
ARCH 102400 100056 MOUNTED EXTERN 11.2.0.0.0
DATA 102400 99686 MOUNTED EXTERN 11.2.0.0.0
OCR 30720 29794 MOUNTED NORMAL 11.2.0.0.0
OCRN 15360 15201 MOUNTED NORMAL 11.2.0.0.0
节点2状态
SQL> select name, TOTAL_MB, FREE_MB, state, type, COMPATIBILITY from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE TYPE COMPATIBILITY
-------------- ---------- ---------- ----------- ------ ------------
ARCH 102400 100056 MOUNTED EXTERN 11.2.0.0.0
DATA 102400 99686 MOUNTED EXTERN 11.2.0.0.0
OCR 30720 29794 MOUNTED NORMAL 11.2.0.0.0
OCRN 0 0 DISMOUNTED 0.0.0.0.0
SQL> alter diskgroup OCRN mount;
Diskgroup altered.
SQL> select name, TOTAL_MB, FREE_MB, state, type, COMPATIBILITY from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE TYPE COMPATIBILITY
-------------- ---------- ---------- ----------- ------ --------------------
ARCH 102400 100056 MOUNTED EXTERN 11.2.0.0.0
DATA 102400 99686 MOUNTED EXTERN 11.2.0.0.0
OCR 30720 29794 MOUNTED NORMAL 11.2.0.0.0
OCRN 15360 15075 MOUNTED NORMAL 11.2.0.0.0
[grid@rac1:/home/grid]$cat /etc/oracle/ocr.loc
ocrconfig_loc=+ocr
local_only=FALSE
[grid@rac1:/home/grid]$ocrcheck -config
Oracle Cluster Registry configuration is :
Device/File Name : +ocr
[root@rac1 bin]#
[root@rac1 bin]# pwd
/u01/11.2.0/grid/bin
[root@rac1 bin]# ./ocrconfig -add +OCRN
[root@rac1 bin]# ./ocrcheck -config
Oracle Cluster Registry configuration is :
Device/File Name : +ocr
Device/File Name : +OCRN
[root@rac1 bin]# more /etc/oracle/ocr.loc
#Device/file getting replaced by device +OCRN
ocrconfig_loc=+ocr
ocrmirrorconfig_loc=+OCRN
local_only=false
[root@rac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3188
Available space (kbytes) : 258932
ID : 2041187104
Device/File Name : +ocr
Device/File integrity check succeeded
Device/File Name : +OCRN
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
[root@rac1 bin]# ./crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
ONLINE 75babc4949ba4f38bf79d651db251e26 (/dev/ASM_LUN_OCR_01) [OCR]
ONLINE a585d03909864f9abf259620c302d575 (/dev/ASM_LUN_OCR_02) [OCR]
ONLINE 73541e9160e04fafbf76f73782544288 (/dev/ASM_LUN_OCR_03) [OCR]
Located 3 voting disk(s).
[root@rac1 bin]#
[root@rac1 bin]# ./crsctl replace votedisk +OCRN
Successful addition of voting disk a12f08433fdc4f50bf9db48a99779782.
Successful addition of voting disk 2b71e53bdb764fd5bf61ef3e35124048.
Successful addition of voting disk 3d59137860564f1dbf18c313f405b87f.
Successful deletion of voting disk 75babc4949ba4f38bf79d651db251e26.
Successful deletion of voting disk a585d03909864f9abf259620c302d575.
Successful deletion of voting disk 73541e9160e04fafbf76f73782544288.
Successfully replaced voting disk group with +OCRN.
CRS-4266: Voting file(s) successfully replaced
查询结果
su - grid
sqlplus / as sysasm
SQL> select group_number,name,state,type,total_mb,free_mb,voting_files,compatibility from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB V COMPATIBILITY
----------- ----- ----------- ------ --------- -------- - ----------
1 ARCH MOUNTED EXTERN 102400 100056 N 11.2.0.0.0
2 DATA MOUNTED EXTERN 102400 99686 N 11.2.0.0.0
3 OCR MOUNTED NORMAL 30720 29890 N 11.2.0.0.0
4 OCRN MOUNTED NORMAL 15360 14436 Y 11.2.0.0.0
[root@rac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3188
Available space (kbytes) : 258932
ID : 2041187104
Device/File Name : +ocr
Device/File integrity check succeeded
Device/File Name : +OCRN
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
[root@rac1 bin]# ./crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
ONLINE a12f08433fdc4f50bf9db48a99779782 (/dev/ASM_LUN_OCR_N_1) [OCRN]
ONLINE 2b71e53bdb764fd5bf61ef3e35124048 (/dev/ASM_LUN_OCR_N_2) [OCRN]
ONLINE 3d59137860564f1dbf18c313f405b87f (/dev/ASM_LUN_OCR_N_3) [OCRN]
Located 3 voting disk(s).
节点1
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +OCR/uec-cluster-db/asmparamet
erfile/registry.253.952171359
节点2
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +OCR/uec-cluster-db/asmparamet
erfile/registry.253.952171359
SQL> create pfile='/tmp/asmpfile.ora' from spfile;
File created.
SQL> create spfile='+OCRN' from pfile='/tmp/asmpfile.ora';
File created.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +OCR/uec-cluster-db/asmparamet
erfile/registry.253.952171359
SQL>
[root@rac1 bin]# ./ocrconfig -delete +OCR
[root@rac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3188
Available space (kbytes) : 258932
ID : 2041187104
Device/File Name : +OCRN
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
[root@rac1 bin]# ./crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
ONLINE a12f08433fdc4f50bf9db48a99779782 (/dev/ASM_LUN_OCR_N_1) [OCRN]
ONLINE 2b71e53bdb764fd5bf61ef3e35124048 (/dev/ASM_LUN_OCR_N_2) [OCRN]
ONLINE 3d59137860564f1dbf18c313f405b87f (/dev/ASM_LUN_OCR_N_3) [OCRN]
Located 3 voting disk(s).
cd /u01 /u01/11.2.0/grid/bin/
crsctl stop cluster –all
crsctl start cluster –all
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +OCRN/uec-cluster-db/asmparame
terfile/registry.253.952617053
SQL> select group_number,name,state,type,total_mb,free_mb,voting_files,compatibility from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB V COMPATIBILITY
------------ ----- -------- ------- ------- ------- -- -------------
1 ARCH MOUNTED EXTERN 102400 100051 N 11.2.0.0.0
2 DATA MOUNTED EXTERN 102400 99686 N 11.2.0.0.0
3 OCRN MOUNTED NORMAL 15360 14434 Y 11.2.0.0.0
0 OCR MOUNTED NORMAL 30720 29890 Y 11.2.0.0.0
SQL>
SQL> Select GROUP_NUMBER,NAME,STATE,type,TOTAL_MB,free_mb,VOTING_FILES,COMPATIBILITY from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB V COMPATIBILITY
------------ ----- -------- ------- ------- ------- -- -------------
1 ARCH MOUNTED EXTERN 102400 100051 N 11.2.0.0.0
2 DATA MOUNTED EXTERN 102400 99686 N 11.2.0.0.0
3 OCRN MOUNTED NORMAL 15360 14434 Y 11.2.0.0.0
0 OCR DISMOUNTED 0 0 N 0.0.0.0.0
将一个节点的 OCR磁盘卸载
SQL> alter diskgroup ocr dismount;
Diskgroup altered.
SQL> drop diskgroup OCR including contents;
Diskgroup dropped.
注:旧的磁盘组只允许在一个节点mount,如果发现多个节点mount,需要在其他节点dismount,否则会删除不掉旧的磁盘组。
SQL> drop diskgroup OCR including contents;
drop diskgroup OCR including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup OCR is mounted by another ASM instance
在节点1上以grid用户登陆
sqlplus / as sysasm
SQL> create diskgroup DATAN external redundancy disk '/dev/ASM_LUN_DATA_N' attribute 'compatible.asm'='11.2';
SQL> create diskgroup ARCHN external redundancy disk '/dev/ASM_LUN_ARCH_N' attribute 'compatible.asm'='11.2';
节点1查询
SQL> Select GROUP_NUMBER,NAME,STATE,type,TOTAL_MB,free_mb,VOTING_FILES,COMPATIBILITY from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB V COMPATIBILITY
------------ ------- ----------- ------ ---------- ---------- - ---------------
1 ARCH MOUNTED EXTERN 102400 97012 N 11.2.0.0.0
2 DATA MOUNTED EXTERN 102400 99686 N 11.2.0.0.0
3 OCRN MOUNTED NORMAL 15360 14434 Y 11.2.0.0.0
4 DATAN MOUNTED EXTERN 20480 20428 N 11.2.0.0.0
5 ARCHN MOUNTED EXTERN 10240 10188 N 11.2.0.0.0
节点2查询
SQL> Select GROUP_NUMBER,NAME,STATE,type,TOTAL_MB,free_mb,VOTING_FILES,COMPATIBILITY from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB V COMPATIBILITY
------------ ------- ----------- ------ ---------- ---------- - ---------------
1 ARCH MOUNTED EXTERN 102400 97012 N 11.2.0.0.0
2 DATA MOUNTED EXTERN 102400 99686 N 11.2.0.0.0
3 OCRN MOUNTED NORMAL 15360 14434 Y 11.2.0.0.0
0 DATAN DISMOUNTED 0 0 N 0.0.0.0.0
0 ARCHN DISMOUNTED 0 0 N 0.0.0.0.0
节点2上手动mount磁盘组
Alter diskgroup datan mount;
Alter diskgroup archn mount;
查询spfile和控制文件位置
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/uecdb/spfileuecdb.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/uecdb/control01.ctl, +DA
TA/uecdb/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
关闭数据库
srvctl stop database -d uecdb
在节点1上将实例启动到nomount状态
Startup nomount
创建一个pfile
create pfile='/tmp/racpfile' from spfile;
通过rman将控制文件还原到新的存储+DATAN
[oracle@rac1:/home/oracle]$rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 23 13:26:07 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: UECDB (not mounted)
RMAN> restore controlfile to '+DATAN' from '+DATA/uecdb/control01.ctl';
Starting restore at 2017-08-23 13:27:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 instance=uec1 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 2017-08-23 13:27:59
RMAN> restore controlfile to '+DATAN' from '+DATA/uecdb/control02.ctl';
Starting restore at 2017-08-23 13:28:14
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 2017-08-23 13:28:21
对pfile文件进行修改,修改控制文件位置,修改数据文件默认创建位置,修改快速恢复区路径
vi /tmp/racpfile
uec1.__db_cache_size=1560281088
uec2.__db_cache_size=1761607680
uec1.__java_pool_size=16777216
uec2.__java_pool_size=16777216
uec1.__large_pool_size=33554432
uec2.__large_pool_size=33554432
uec1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
uec1.__pga_aggregate_target=838860800
uec2.__pga_aggregate_target=838860800
uec1.__sga_target=2483027968
uec2.__sga_target=2483027968
uec1.__shared_io_pool_size=0
uec2.__shared_io_pool_size=0
uec1.__shared_pool_size=838860800
uec2.__shared_pool_size=637534208
uec1.__streams_pool_size=0
uec2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/uecdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
#*.control_files='+DATA/uecdb/control01.ctl','+DATA/uecdb/control02.ctl'
*.control_files='+DATAN/UECDB/CONTROLFILE/current.256.952781275','+DATAN/UECDB/CONTROLFILE/current.257.952781295'
*.db_block_size=8192
*.db_create_file_dest='+DATAN'
*.db_domain=''
*.db_name='uecdb'
#*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest='+ARCHN'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
uec1.instance_number=1
uec2.instance_number=2
#*.log_archive_dest_1='LOCATION=+ARCH'
*.log_archive_dest_1='LOCATION=+ARCHN'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=825229312
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=2475687936
uec2.thread=2
uec1.thread=1
uec1.undo_tablespace='UNDOTBS1'
uec2.undo_tablespace='UNDOTBS2'
在节点1上使用修改过的pfile启动到mount状态
SQL> startup force mount pfile='/tmp/racpfile';
ORACLE instance started.
Total System Global Area 2471931904 bytes
Fixed Size 2255752 bytes
Variable Size 889193592 bytes
Database Buffers 1560281088 bytes
Redo Buffers 20201472 bytes
Database mounted.
SQL>
创建spfile
SQL> create spfile='+DATAN/uecdb/spfileuecdb.ora' from pfile='/tmp/racpfile';
File created.
srvctl修改数据库参数文件位置
srvctl modify database -d uecdb -p +DATAN/uecdb/spfileuecdb.ora
srvctl config database -d uecdb
Database unique name: uecdb
Database name: uecdb
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATAN/uecdb/spfileuecdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: uecdb
Database instances: uec1,uec2
Disk Groups: DATA,ARCH,DATAN
Mount point paths:
Services:
Type: RAC
Database is administrator managed
启动数据库实例
srvctl start database -d uecdb
使用rman backup as copy迁移数据文件
[oracle@rac1:/home/oracle]$rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 23 17:29:30 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: UECDB (DBID=505357948)
RMAN> backup as copy database format '+DATAN';
Starting backup at 2017-08-23 17:29:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 instance=uec1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/uecdb/datafile/system.260.952182827
output file name=+DATAN/uecdb/datafile/system.259.952795781 tag=TAG20170823T172939 RECID=1 STAMP=952795861
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/uecdb/datafile/sysaux.261.952182897
output file name=+DATAN/uecdb/datafile/sysaux.260.952795865 tag=TAG20170823T172939 RECID=2 STAMP=952795936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/uecdb/datafile/undotbs1.262.952182955
output file name=+DATAN/uecdb/datafile/undotbs1.261.952795941 tag=TAG20170823T172939 RECID=3 STAMP=952795966
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/uecdb/datafile/undotbs2.264.952182995
output file name=+DATAN/uecdb/datafile/undotbs2.262.952795977 tag=TAG20170823T172939 RECID=4 STAMP=952796000
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/uecdb/datafile/users.265.952183015
output file name=+DATAN/uecdb/datafile/users.263.952796001 tag=TAG20170823T172939 RECID=5 STAMP=952796002
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2017-08-23 17:33:22
Starting Control File and SPFILE Autobackup at 2017-08-23 17:33:22
piece handle=+ARCHN/uecdb/autobackup/2017_08_23/s_952796002.257.952796005 comment=NONE
Finished Control File and SPFILE Autobackup at 2017-08-23 17:33:29
RMAN>
关闭数据库并启动到mount状态,对数据文件路径进行调整
srvctl stop database -d uecdb -o immediate
[oracle@rac1:/home/oracle]$srvctl stop database -d uecdb -o immediate
[oracle@rac1:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 23 17:35:45 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2471931904 bytes
Fixed Size 2255752 bytes
Variable Size 889193592 bytes
Database Buffers 1560281088 bytes
Redo Buffers 20201472 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1:/home/oracle]$rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 23 17:36:33 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: UECDB (DBID=505357948, not open)
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATAN/uecdb/datafile/system.259.952795781"
datafile 2 switched to datafile copy "+DATAN/uecdb/datafile/sysaux.260.952795865"
datafile 3 switched to datafile copy "+DATAN/uecdb/datafile/undotbs1.261.952795941"
datafile 4 switched to datafile copy "+DATAN/uecdb/datafile/undotbs2.262.952795977"
datafile 5 switched to datafile copy "+DATAN/uecdb/datafile/users.263.952796001"
RMAN>
对数据库进行recover一次
[oracle@rac1:/home/oracle]$rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 23 17:38:10 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: UECDB (DBID=505357948, not open)
RMAN> recover database;
Starting recover at 2017-08-23 17:38:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 instance=uec1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 2017-08-23 17:38:32
RMAN>
启动数据库
srvctl start database -d uecdb
SQL> select status,enabled,bytes/1024/1024,name from v$tempfile;
STATUS ENABLED BYTES/1024/1024 NAME
------- ---------- --------------- ---------------------------------------------
ONLINE READ WRITE 20 +DATA/uecdb/tempfile/temp.263.952182977
SQL> select tablespace_name,file_name,status from dba_temp_files;
TABLESPACE_NAME FILE_NAME STATUS
----------------- --------------------------------------------- -------
TEMP +DATA/uecdb/tempfile/temp.263.952182977 ONLINE
SQL> alter tablespace temp add tempfile '+DATAN/uecdb/tempfile/tempfile01.dbf' size 100M;
Tablespace altered.
SQL> alter database tempfile '+DATA/uecdb/tempfile/temp.263.952182977' drop including datafiles;
Database altered.
SQL> select a.group#,a.thread#,a.bytes/1024/1024,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# A.BYTES/1024/1024 STATUS MEMBER
---------- ---------- ----------------- ---------- -------------------------
1 1 200 CURRENT +DATA/uecdb/redo01.log
2 1 200 INACTIVE +DATA/uecdb/redo02.log
3 2 200 CURRENT +DATA/uecdb/redo03.log
4 2 200 INACTIVE +DATA/uecdb/redo04.log
SQL> alter database add logfile thread 1 group 5 '+DATAN/uecdb/redo05.log' size 200M;
Database altered.
SQL> alter database add logfile thread 1 group 6 '+DATAN/uecdb/redo06.log' size 200M;
Database altered.
SQL> alter database add logfile thread 2 group 7 '+DATAN/uecdb/redo07.log' size 200M;
Database altered.
SQL> alter database add logfile thread 2 group 8 '+DATAN/uecdb/redo08.log' size 200M;
Database altered.
SQL>
SQL> select a.group#,a.thread#,a.bytes/1024/1024,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# A.BYTES/1024/1024 STATUS MEMBER
---------- ---------- ----------------- ---------- -------------------------
1 1 200 CURRENT +DATA/uecdb/redo01.log
2 1 200 INACTIVE +DATA/uecdb/redo02.log
5 1 200 UNUSED +DATAN/uecdb/redo05.log
3 2 200 CURRENT +DATA/uecdb/redo03.log
4 2 200 INACTIVE +DATA/uecdb/redo04.log
6 1 200 UNUSED +DATAN/uecdb/redo06.log
7 2 200 UNUSED +DATAN/uecdb/redo07.log
8 2 200 UNUSED +DATAN/uecdb/redo08.log
执行日志切换,将当前使用的切换到新的redo日志
SQL> alter system archive log current;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select a.group#,a.thread#,a.bytes/1024/1024,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# A.BYTES/1024/1024 STATUS MEMBER
---------- ---------- ----------------- ---------- -------------------------
1 1 200 INACTIVE +DATA/uecdb/redo01.log
2 1 200 INACTIVE +DATA/uecdb/redo02.log
5 1 200 CURRENT +DATAN/uecdb/redo05.log
3 2 200 INACTIVE +DATA/uecdb/redo03.log
4 2 200 INACTIVE +DATA/uecdb/redo04.log
6 1 200 UNUSED +DATAN/uecdb/redo06.log
7 2 200 CURRENT +DATAN/uecdb/redo07.log
8 2 200 UNUSED +DATAN/uecdb/redo08.log
8 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select a.group#,a.thread#,a.bytes/1024/1024,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# A.BYTES/1024/1024 STATUS MEMBER
---------- ---------- ----------------- ---------- -------------------------
5 1 200 CURRENT +DATAN/uecdb/redo05.log
6 1 200 UNUSED +DATAN/uecdb/redo06.log
7 2 200 CURRENT +DATAN/uecdb/redo07.log
8 2 200 UNUSED +DATAN/uecdb/redo08.log
SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;
NAME STATE TOTAL_MB FREE_MB
------------------------------ ----------- ---------- ----------
ARCH MOUNTED 102400 97012
DATA MOUNTED 102400 99707
OCRN MOUNTED 15360 14434
DATAN MOUNTED 20480 17445
ARCHN MOUNTED 10240 6933
登陆节点2,将需要卸载的磁盘组dismount
SQL> alter diskgroup arch dismount;
Diskgroup altered.
SQL> alter diskgroup data dismount;
Diskgroup altered.
注:旧的磁盘组只允许在一个节点mount,如果发现多个节点mount,需要在其他节点dismount,否则会删除不掉旧的磁盘组。
SQL> drop diskgroup ARCH including contents;
drop diskgroup ARCH including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup ARCH is mounted by another ASM instance
删除diskgroup
SQL> drop diskgroup arch including contents;
Diskgroup dropped.
SQL> drop diskgroup data including contents;
Diskgroup dropped.
SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;
NAME STATE TOTAL_MB FREE_MB
------------------------------ ----------- ---------- ----------
OCRN MOUNTED 15360 14434
DATAN MOUNTED 20480 17445
ARCHN MOUNTED 10240 6933
[grid@rac1:/home/grid]$crs_stat -t -v
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.ARCH.dg ora....up.type 0/5 0/ ONLINE OFFLINE
ora.ARCHN.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora.DATA.dg ora....up.type 0/5 0/ ONLINE OFFLINE
ora.DATAN.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac2
ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora....N3.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1
ora.OCRN.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1
ora.cvu ora.cvu.type 0/5 0/0 ONLINE ONLINE rac1
ora.gsd ora.gsd.type 0/5 0/ OFFLINE OFFLINE
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type 1/1 0/2 ONLINE OFFLINE
ora.ons ora.ons.type 1/3 0/ ONLINE OFFLINE
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE OFFLINE
ora.rac1.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac1.ons application 1/3 0/0 ONLINE OFFLINE
ora.rac1.vip ora....t1.type 0/0 1/0 ONLINE ONLINE rac2
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.rac2.ons application 0/3 0/0 ONLINE OFFLINE
ora.rac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac2
ora....ry.acfs ora....fs.type 0/5 0/ ONLINE ONLINE rac1
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
ora.scan3.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac1
ora.uecdb.db ora....se.type 0/2 0/1 ONLINE ONLINE rac1
crsctl stop res ora.ARCH.dg -f
crsctl delete res ora.ARCH.dg -f
crsctl stop res ora.DATA.dg -f
crsctl delete res ora.DATA.dg -f