SQL> select NAME,ALLOCATION_UNIT_SIZE from v$asm_diskgroup;
NAME ALLOCATION_UNIT_SIZE
--------------- --------------------
CRSDG 4194304
DATADG 1048576
RECODG 4194304
set lines 200 pages 999
col name for a15
col path for a30
select a.name,a.total_mb/1024 GB,b.name ,b.total_mb/1024,b.path,b.state from v$asm_diskgroup a,v$asm_disk b where a.group_number=b.group_number order by b.path desc;
NAME GB NAME B.TOTAL_MB/1024 PATH STATE
--------------- ---------- --------------- --------------- ------------------------------ --------
DATADG 29000 DATADG_0057 500 /rasm/disk32058 NORMAL
DATADG 29000 DATADG_0056 500 /rasm/disk32057 NORMAL
DATADG 29000 DATADG_0055 500 /rasm/disk32056 NORMAL
DATADG 29000 DATADG_0054 500 /rasm/disk32055 NORMAL
DATADG 29000 DATADG_0053 500 /rasm/disk32054 NORMAL
DATADG 29000 DATADG_0052 500 /rasm/disk32053 NORMAL
DATADG 29000 DATADG_0051 500 /rasm/disk32052 NORMAL
DATADG 29000 DATADG_0050 500 /rasm/disk32051 NORMAL
DATADG 29000 DATADG_0049 500 /rasm/disk32050 NORMAL
DATADG 29000 DATADG_0048 500 /rasm/disk32049 NORMAL
DATADG 29000 DATADG_0047 500 /rasm/disk32048 NORMAL
DATADG 29000 DATADG_0046 500 /rasm/disk32047 NORMAL
DATADG 29000 DATADG_0045 500 /rasm/disk32046 NORMAL
DATADG 29000 DATADG_0044 500 /rasm/disk32045 NORMAL
DATADG 29000 DATADG_0043 500 /rasm/disk32044 NORMAL
DATADG 29000 DATADG_0042 500 /rasm/disk32043 NORMAL
DATADG 29000 DATADG_0041 500 /rasm/disk32042 NORMAL
DATADG 29000 DATADG_0040 500 /rasm/disk32041 NORMAL
DATADG 29000 DATADG_0039 500 /rasm/disk32040 NORMAL
DATADG 29000 DATADG_0038 500 /rasm/disk32039 NORMAL
DATADG 29000 DATADG_0037 500 /rasm/disk32038 NORMAL
DATADG 29000 DATADG_0036 500 /rasm/disk32037 NORMAL
DATADG 29000 DATADG_0035 500 /rasm/disk32036 NORMAL
DATADG 29000 DATADG_0034 500 /rasm/disk32035 NORMAL
DATADG 29000 DATADG_0033 500 /rasm/disk32034 NORMAL
DATADG 29000 DATADG_0032 500 /rasm/disk32033 NORMAL
DATADG 29000 DATADG_0031 500 /rasm/disk32032 NORMAL
DATADG 29000 DATADG_0030 500 /rasm/disk32031 NORMAL
DATADG 29000 DATADG_0029 500 /rasm/disk32030 NORMAL
DATADG 29000 DATADG_0028 500 /rasm/disk32029 NORMAL
DATADG 29000 DATADG_0027 500 /rasm/disk32028 NORMAL
DATADG 29000 DATADG_0026 500 /rasm/disk32027 NORMAL
DATADG 29000 DATADG_0025 500 /rasm/disk32026 NORMAL
DATADG 29000 DATADG_0024 500 /rasm/disk32025 NORMAL
DATADG 29000 DATADG_0023 500 /rasm/disk32024 NORMAL
DATADG 29000 DATADG_0022 500 /rasm/disk32023 NORMAL
DATADG 29000 DATADG_0021 500 /rasm/disk32022 NORMAL
DATADG 29000 DATADG_0020 500 /rasm/disk32021 NORMAL
DATADG 29000 DATADG_0019 500 /rasm/disk32020 NORMAL
DATADG 29000 DATADG_0018 500 /rasm/disk32019 NORMAL
DATADG 29000 DATADG_0017 500 /rasm/disk32018 NORMAL
DATADG 29000 DATADG_0016 500 /rasm/disk32017 NORMAL
DATADG 29000 DATADG_0015 500 /rasm/disk32016 NORMAL
DATADG 29000 DATADG_0014 500 /rasm/disk32015 NORMAL
DATADG 29000 DATADG_0013 500 /rasm/disk32014 NORMAL
DATADG 29000 DATADG_0012 500 /rasm/disk32013 NORMAL
DATADG 29000 DATADG_0011 500 /rasm/disk32012 NORMAL
DATADG 29000 DATADG_0010 500 /rasm/disk32011 NORMAL
DATADG 29000 DATADG_0009 500 /rasm/disk32010 NORMAL
DATADG 29000 DATADG_0008 500 /rasm/disk32009 NORMAL
DATADG 29000 DATADG_0007 500 /rasm/disk32008 NORMAL
DATADG 29000 DATADG_0006 500 /rasm/disk32007 NORMAL
DATADG 29000 DATADG_0005 500 /rasm/disk32006 NORMAL
DATADG 29000 DATADG_0004 500 /rasm/disk32005 NORMAL
DATADG 29000 DATADG_0003 500 /rasm/disk32004 NORMAL
DATADG 29000 DATADG_0002 500 /rasm/disk32003 NORMAL
DATADG 29000 DATADG_0001 500 /rasm/disk32002 NORMAL
DATADG 29000 DATADG_0000 500 /rasm/disk32001 NORMAL
alter diskgroup datadg drop disk DATADG_0057;
alter diskgroup datadg drop disk DATADG_0056;
alter diskgroup datadg drop disk DATADG_0055;
alter diskgroup datadg drop disk DATADG_0054;
create diskgroup datadg1 external redundancy disk '/rasm/disk32058','/rasm/disk32057','/rasm/disk32056','/rasm/disk32055' attribute 'compatible.rdbms'='19.0.0.0.0', 'compatible.asm'='19.0.0.0.0', 'AU_SIZE'='4M';
SQL> select name,state,ALLOCATION_UNIT_SIZE from v$asm_diskgroup;
NAME STATE ALLOCATION_UNIT_SIZE
--------------- ----------- --------------------
CRSDG MOUNTED 4194304
DATADG MOUNTED 1048576
RECODG MOUNTED 4194304
DATADG1 MOUNTED 4194304
移动cdb和pdb以及pdb$seed中的数据文件到新的datadg1磁盘组
alter database move datafile 1 to '+DATADG1';
。。。
alter system check datafiles;
处理tempfile文件(这里使用的是增加在删除,因为是刚装的库,如果在生产库,则选择替换的方式)
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter tablespace temp add tempfile '+DATADG1' size 1g autoextend off;
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------------------------------------------------------------- --------------------
TEMP +DATADG/CCRMDB1/TEMPFILE/temp.266.1049536897 .067382813
TEMP +DATADG1/CCRMDB1/TEMPFILE/temp.287.1050667495 1
SQL> alter tablespace temp drop tempfile '+DATADG/CCRMDB1/TEMPFILE/temp.266.1049536897';
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------------------------------------------------------------- --------------------
TEMP +DATADG1/CCRMDB1/TEMPFILE/temp.287.1050667495 1
处理pdb中的temp
SQL> alter session set container=crmdb1;
Session altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------------------------------------------------------------- --------------------
TEMP +DATADG/CCRMDB1/ADD3C14BBF1CE143E0531671200AA6C8/TEMPFILE/temp.282.1049538783 .040039063
SQL> alter tablespace temp add tempfile '+DATADG1' size 1g autoextend off;
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------------------------------------------------------------- --------------------
TEMP +DATADG/CCRMDB1/ADD3C14BBF1CE143E0531671200AA6C8/TEMPFILE/temp.282.1049538783 .040039063
TEMP +DATADG1/CCRMDB1/ADD3C14BBF1CE143E0531671200AA6C8/TEMPFILE/temp.286.1050667851 1
SQL> alter tablespace temp drop tempfile '+DATADG/CCRMDB1/ADD3C14BBF1CE143E0531671200AA6C8/TEMPFILE/temp.282.1049538783';
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
------------------------------ ------------------------------------------------------------------------------------- --------------------
TEMP +DATADG1/CCRMDB1/ADD3C14BBF1CE143E0531671200AA6C8/TEMPFILE/temp.286.1050667851 1
1.删除recodg中的redo log file
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
1 1 1245 YES INACTIVE +RECODG/CCRMDB1/ONLINELOG/group_1.259.1050662313 4096
2 1 1246 YES INACTIVE +RECODG/CCRMDB1/ONLINELOG/group_2.261.1050662513 4096
3 1 1252 NO CURRENT +RECODG/CCRMDB1/ONLINELOG/group_3.262.1050662515 4096
4 1 1247 YES INACTIVE +RECODG/CCRMDB1/ONLINELOG/group_4.263.1050662519 4096
5 1 1248 YES INACTIVE +RECODG/CCRMDB1/ONLINELOG/group_5.264.1050662521 4096
6 1 1249 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_6.265.1050662523 4096
7 1 1250 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_7.266.1050662527 4096
8 1 1251 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_8.267.1050662529 4096
9 2 1147 YES INACTIVE +RECODG/CCRMDB1/ONLINELOG/group_9.268.1050662533 4096
10 2 1148 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_10.269.1050662535 4096
11 2 1149 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_11.270.1050662537 4096
12 2 1150 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_12.271.1050662541 4096
13 2 1151 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_13.272.1050662543 4096
14 2 1152 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_14.273.1050662547 4096
15 2 1153 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_15.274.1050662549 4096
16 2 1154 NO CURRENT +RECODG/CCRMDB1/ONLINELOG/group_16.275.1050662551 4096
17 3 1155 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_17.276.1050662555 4096
18 3 1156 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_18.277.1050662557 4096
19 3 1157 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_19.278.1050662559 4096
20 3 1158 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_20.279.1050662563 4096
21 3 1159 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_21.280.1050662565 4096
22 3 1160 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_22.281.1050662567 4096
23 3 1161 NO CURRENT +RECODG/CCRMDB1/ONLINELOG/group_23.282.1050662571 4096
24 3 1154 YES INACTIVE +RECODG/CCRMDB1/ONLINELOG/group_24.283.1050662573 4096
25 4 1147 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_25.284.1050662575 4096
26 4 1148 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_26.285.1050662579 4096
27 4 1149 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_27.286.1050662581 4096
28 4 1150 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_28.287.1050662583 4096
29 4 1151 NO CURRENT +RECODG/CCRMDB1/ONLINELOG/group_29.288.1050662587 4096
30 4 1144 YES INACTIVE +RECODG/CCRMDB1/ONLINELOG/group_30.289.1050662589 4096
31 4 1145 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_31.290.1050662591 4096
32 4 1146 YES ACTIVE +RECODG/CCRMDB1/ONLINELOG/group_32.291.1050662595 4096
alter system archive log current;(切换redo)
alter database drop logfile GROUP xxx;(删除redo)--状态是inactive
alter database drop logfile member '+RECODG/CCRMDB1/ONLINELOG/group_31.278.1049551119';
2.关闭数据库归档模式
alter system set control_files='+DATADG1/CCRMDB1/CONTROLFILE/current02.ctl','+DATADG1/CCRMDB1/CONTROLFILE/current01.ctl' scope=spfile;
ASMCMD> cp Current.257.1049536881 +datadg1/ccrmdb1/controlfile/Current01.ctl
copying +datadg/ccrmdb1/CONTROLFILE/Current.257.1049536881 -> +datadg1/ccrmdb1/controlfile/Current01.ctl
ASMCMD> cp Current01.ctl +datadg1/ccrmdb1/controlfile/Current02.ctl
copying +datadg/ccrmdb1/CONTROLFILE/Current01.ctl -> +datadg1/ccrmdb1/controlfile/Current02.ctl
SQL> startup pfile='/home/oracle/pfile20200909.ora';
ORACLE instance started.
Total System Global Area 4.2950E+11 bytes
Fixed Size 30424032 bytes
Variable Size 9.7174E+10 bytes
Database Buffers 3.3179E+11 bytes
Redo Buffers 506445824 bytes
Database mounted.
Database opened.
SQL> create spfile='+DATADG1' FROM PFILE='/home/oracle/pfile20200909.ora';
File created.
oracle@nmcrmdb11:/oracle/app/oracle/product/19.0.0/db/dbs$ cat initccrmdb11.ora
spfile='+datadg1/ccrmdb1/PARAMETERFILE/spfile.290.1050675883'
srvctl modify database -db ccrmdb1 -pwfile +datadg1/ccrmdb1/password/pwdccrmdb1.291.1050676489
oracle@nmcrmdb11:/oracle/app/oracle/product/19.0.0/db/dbs$ srvctl config database -d ccrmdb1
Database unique name: ccrmdb1
Database name:
Oracle home: /oracle/app/oracle/product/19.0.0/db
Oracle user: oracle
Spfile: +DATADG1/CCRMDB1/PARAMETERFILE/spfile.290.1050675883
Password file: +datadg1/ccrmdb1/password/pwdccrmdb1.291.1050676489
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATADG,DATADG1,RECODG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oinstall
Database instances: ccrmdb11,ccrmdb12,ccrmdb13,ccrmdb14
Configured nodes: nmcrmdb11,nmcrmdb12,nmcrmdb13,nmcrmdb14
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
3.删除recodg磁盘组
SQL> alter diskgroup datadg dismount;
Diskgroup altered.
SQL> drop diskgroup datadg force including contents;
Diskgroup dropped.
SQL> select inst_id,name,state from gv$asm_diskgroup;
INST_ID NAME STATE
---------- ------------------------------ -----------
2 CRSDG MOUNTED
2 DATADG1 MOUNTED
3 CRSDG MOUNTED
3 DATADG1 MOUNTED
4 CRSDG MOUNTED
4 DATADG1 MOUNTED
1 CRSDG MOUNTED
1 DATADG1 MOUNTED
oracle@nmcrmdb12:/home/oracle$ srvctl start database -d ccrmdb1
6.重新添加日志组成员
alter database add logfile member '+DATADG1' to group 1;