一、AMDU工具用法
0racle在11g中提供了AMDU免费的工具，可用于协助诊断，在ASM磁盘加载前，可以将ASM元数据从磁盘中抽取出来，用于数据库诊断此工具可以向后兼容，引入到10g。amdu可以跳过磁盘头信息抽取数据文件至本地。
AMDU用于分析ASM磁盘组的元数据以及从不能mount的磁盘组中往外抽取数据文件,它的原理就是跳过磁盘头直接解析磁盘中的filedirectory部分的内容，然后找到文件和目录结构，根据数据文件号，直接将文件抽取出来。使用AMDU一般要满足以下三个情况:
- 1.ASM实例无法启动;
- 2.磁盘中的filedirectory信息完整;
- 3.在mount的情况下，知道数据文件号，这样我们就可以尝试把数据文件直接抽取出来，在本地启动数据库实例。
- 4.不能mount的情况下通过其它方式获取数据文件号
如果磁盘头损坏，一个磁盘组多个磁盘，不同时间加入的。修复很麻烦
二.案例模拟
场景:asm不可以mount，数据库不能open，通过定位ASM文件的编号从ASM裸盘中来抽取数据文件，实现重组后，打开数据库
1、离线磁盘组
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
关闭数据库
SQL> alter diskgroup data dismount;
Diskgroup altered.
SQL> alter diskgroup arch dismount;
Diskgroup altered.
卸载磁盘组模拟磁盘组无法挂载
[grid@rac01:/home/grid]$asmcmd lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 512 4096 4194304 29280 28300 9760 9270 0 Y OCR/
2、使用kfod获取磁盘信息
[grid@rac01:/home/grid]$kfod disks=all
--------------------------------------------------------------------------------
Disk Size Path User Group
================================================================================
1: 19520 MB /dev/mapper/asm-arch01 grid asmadmin
2: 19520 MB /dev/mapper/asm-arch02 grid asmadmin
3: 19520 MB /dev/mapper/asm-arch03 grid asmadmin
4: 9760 MB /dev/mapper/asm-data01 grid asmadmin
5: 9760 MB /dev/mapper/asm-data02 grid asmadmin
6: 19520 MB /dev/mapper/asm-data03 grid asmadmin
7: 19520 MB /dev/mapper/asm-data04 grid asmadmin
8: 19520 MB /dev/mapper/asm-data05 grid asmadmin
9: 19520 MB /dev/mapper/asm-ocr01 grid asmadmin
10: 19520 MB /dev/mapper/asm-ocr02 grid asmadmin
11: 9760 MB /dev/mapper/asm-ocr03 grid asmadmin
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
3、使用amdu读取磁盘组信息
[grid@rac01:/home/grid/backup]$amdu -diskstring='/dev/mapper/*'
amdu_2023_10_08_11_36_10/
4、获取控制文件
找到控制文件的磁盘组+ASM文件编号
-
A.日志文件 alert
-
B.spfile文件 ASM (忽略)
-
C.ODU可以提取控制文件号
[oracle@rac01:/home/oracle]$tar -zvxf odu-linux_x86.tar.gz
odu/
odu/asmdisk.txt
odu/config.txt
odu/control.txt
odu/data/
odu/odu
此处模拟极端情况，使用odu工具获取控制文件
[grid@rac01:/home/grid]$kfod disks=all
--------------------------------------------------------------------------------
Disk Size Path User Group
================================================================================
1: 19520 MB /dev/mapper/asm-arch01 grid asmadmin
2: 19520 MB /dev/mapper/asm-arch02 grid asmadmin
3: 19520 MB /dev/mapper/asm-arch03 grid asmadmin
4: 9760 MB /dev/mapper/asm-data01 grid asmadmin
5: 9760 MB /dev/mapper/asm-data02 grid asmadmin
6: 19520 MB /dev/mapper/asm-data03 grid asmadmin
7: 19520 MB /dev/mapper/asm-data04 grid asmadmin
8: 19520 MB /dev/mapper/asm-data05 grid asmadmin
9: 19520 MB /dev/mapper/asm-ocr01 grid asmadmin
10: 19520 MB /dev/mapper/asm-ocr02 grid asmadmin
11: 9760 MB /dev/mapper/asm-ocr03 grid asmadmin
将获取到的磁盘写入odu文件夹中的asmdisk.txt文件，其中磁盘号全部写0，odu会自动补全，只需要补充磁盘path即可
[grid@rac01:/home/grid/odu]$./odu
Oracle Data Unloader trial version 4.1.3
Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.
Web: http://www.oracleodu.com
Email: magic007cn@gmail.com
loading default config.......
byte_order little
block_size 8192
block_buffers 1024
error at line 3.
db_timezone -7
Invalid db timezone:-7
client_timezone 8
Invalid client timezone:8
asmfile_extract_path /asmfile
data_path data
lob_path /odu/data/lob
charset_name US7ASII
charset name 'US7ASII' not found,will use default charset ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted no
file_header_offset 0
is_tru64 no
record_row_addr no
convert_clob_charset yes
use_scanned_lob yes
trim_scanned_blob yes
lob_switch_dir_rows 20000
db_block_checksum yes
db_block_checking yes
rdba_file_bits 10
compatible 10
load config file 'config.txt' successful
can not open trace file,filename: 'odu_trace.txt',error message:Permission denied
loading default asm disk file ......
grp# dsk# bsize ausize disksize diskname groupname path
---- ---- ----- ------ -------- --------------- --------------- --------------------------------------------
1 0 4096 4096K 4880 ARCH_0000 ARCH /dev/mapper/asm-arch01
1 1 4096 4096K 4880 ARCH_0001 ARCH /dev/mapper/asm-arch02
1 2 4096 4096K 4880 ARCH_0002 ARCH /dev/mapper/asm-arch03
2 0 4096 4096K 2440 OCR_0000 OCR /dev/mapper/asm-data01
2 1 4096 4096K 2440 OCR_0001 OCR /dev/mapper/asm-data02
3 0 4096 1024K 19520 DATA_0000 DATA /dev/mapper/asm-data03
3 1 4096 1024K 19520 DATA_0001 DATA /dev/mapper/asm-data04
3 2 4096 1024K 19520 DATA_0002 DATA /dev/mapper/asm-data05
3 3 4096 4096K 4880 DATA_0003 DATA /dev/mapper/asm-ocr01
3 4 4096 4096K 4880 DATA_0004 DATA /dev/mapper/asm-ocr02
2 2 4096 4096K 2440 OCR_0002 OCR /dev/mapper/asm-ocr03
load asm disk file 'asmdisk.txt' successful
loading default control file ......
ts# fn rfn bsize blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
load control file 'control.txt' successful
loading dictionary data......done
loading scanned data......done
ODU> asmcmd
Entering asmcmd module.
ASMCMD> ls
Current directory: <root>
Disk Group
------------------------------------
ARCH
OCR
DATA
ASMCMD>
ASMCMD> cd +data
Current directory: +DATA
ASMCMD> ls
Current directory: +DATA
Name
----------------------------------------
ORCL <DIR>
ASM <DIR>
ASMCMD> cd orcl
Current directory: +DATA/ORCL
ASMCMD> ls
Current directory: +DATA/ORCL
Name
----------------------------------------
PASSWORD <DIR>
DATAFILE <DIR>
CONTROLFILE <DIR>
ONLINELOG <DIR>
TEMPFILE <DIR>
PARAMETERFILE <DIR>
ASMCMD> cd controlfile
Current directory: +DATA/ORCL/CONTROLFILE
ASMCMD> ls
Current directory: +DATA/ORCL/CONTROLFILE
Name
----------------------------------------
Current.262.1148569199
Current.261.1148569201
执行odu命令，同时结合asmcmd命令即可找到控制文件，建议此处一并找出spfile文件以便后续创建参数文件
[grid@rac01:/home/grid/backup]$amdu -diskstring='/dev/mapper/*' -dump 'DATA'
amdu_2023_10_08_15_35_29/
这条命令将data磁盘全部dump出来
[grid@rac01:/home/grid/backup/amdu_2023_10_08_15_35_29]$strings DATA_0001.img |grep FILE
DATAFILE
PARAMETERFILE
CONTROLFILE
CONTROLFILE
TEMPFILE
PARAMETERFILE
ASMPARAMETERFILE
ASMPARAMETERBAKFILE
VOTINGFILE
CONTROLFILE
FLASHFILE
DATAFILE
TEMPFILE
AUDIT_SPILLFILES
OCRFILE
DATAFILE
CONTROLFILE
TEMPFILE
PARAMETERFILE
通过dump的img文件获取各个文件目录
[grid@rac01:/home/grid/backup]$amdu -diskstring='/dev/mapper/*' -extract DATA.261
amdu_2023_10_08_15_39_15/
抽取控制文件
5、通过控制文件获取数据库号
[grid@rac01:/home/grid/backup]$strings amdu_2023_10_08_15_39_15/DATA_261.f |grep + >dbfile.txt
[grid@rac01:/home/grid/backup]$cat dbfile.txt
+DATA/ORCL/ONLINELOG/group_2.264.1148569205
+DATA/ORCL/ONLINELOG/group_2.266.1148569211
+DATA/ORCL/ONLINELOG/group_1.263.1148569205
+DATA/ORCL/ONLINELOG/group_1.265.1148569211
+DATA/ORCL/DATAFILE/users.260.1148569149
+DATA/ORCL/DATAFILE/undotbs1.259.1148569147
+DATA/ORCL/DATAFILE/system.257.1148569037
+DATA/ORCL/DATAFILE/sysaux.258.1148569113
+DATA/ORCL/TEMPFILE/temp.267.1148569225
+DATA/ORCL/DATAFILE/undotbs2.268.1148570375
+DATA/ORCL/ONLINELOG/group_3.269.1148570523
+DATA/ORCL/ONLINELOG/group_3.270.1148570527
+DATA/ORCL/ONLINELOG/group_4.271.1148570533
+DATA/ORCL/ONLINELOG/group_4.272.1148570537
+DATA/ORCL/ONLINELOG/group_2.264.1148569205
+DATA/ORCL/ONLINELOG/group_2.266.1148569211
+DATA/ORCL/ONLINELOG/group_1.263.1148569205
+DATA/ORCL/ONLINELOG/group_1.265.1148569211
+DATA/ORCL/DATAFILE/users.260.1148569149
+DATA/ORCL/DATAFILE/undotbs1.259.1148569147
+DATA/ORCL/DATAFILE/system.257.1148569037
+DATA/ORCL/DATAFILE/sysaux.258.1148569113
+DATA/ORCL/TEMPFILE/temp.267.1148569225
+DATA/ORCL/DATAFILE/undotbs2.268.1148570375
+DATA/ORCL/ONLINELOG/group_3.269.1148570523
+DATA/ORCL/ONLINELOG/group_3.270.1148570527
+DATA/ORCL/ONLINELOG/group_4.271.1148570533
+DATA/ORCL/ONLINELOG/group_4.272.1148570537
6、抽取数据文件
amdu -diskstring='/dev/mapper/*' -extract DATA.264
amdu -diskstring='/dev/mapper/*' -extract DATA.266
amdu -diskstring='/dev/mapper/*' -extract DATA.263
amdu -diskstring='/dev/mapper/*' -extract DATA.265
amdu -diskstring='/dev/mapper/*' -extract DATA.260
amdu -diskstring='/dev/mapper/*' -extract DATA.259
amdu -diskstring='/dev/mapper/*' -extract DATA.257
amdu -diskstring='/dev/mapper/*' -extract DATA.258
amdu -diskstring='/dev/mapper/*' -extract DATA.267
amdu -diskstring='/dev/mapper/*' -extract DATA.268
amdu -diskstring='/dev/mapper/*' -extract DATA.269
amdu -diskstring='/dev/mapper/*' -extract DATA.270
amdu -diskstring='/dev/mapper/*' -extract DATA.271
amdu -diskstring='/dev/mapper/*' -extract DATA.272
amdu -diskstring='/dev/mapper/*' -extract DATA.273
[grid@rac01:/home/grid/backup/file]$find ./ -name 'DATA*'
cp ./amdu_2023_10_08_15_53_47/DATA_264.f ./data/redo2
cp ./amdu_2023_10_08_15_54_04/DATA_263.f ./data/redo1
cp ./amdu_2023_10_08_15_54_21/DATA_260.f ./data/users
cp ./amdu_2023_10_08_15_54_28/DATA_259.f ./data/undotbs1
cp ./amdu_2023_10_08_15_54_40/DATA_257.f ./data/system
cp ./amdu_2023_10_08_15_54_57/DATA_258.f ./data/sysaux
cp ./amdu_2023_10_08_15_55_11/DATA_267.f ./data/temp
cp ./amdu_2023_10_08_15_55_18/DATA_268.f ./data/undotbs2
cp ./amdu_2023_10_08_15_55_33/DATA_270.f ./data/redo3
cp ./amdu_2023_10_08_15_55_42/DATA_271.f ./data/redo4
+DATA/ORCL/ONLINELOG/group_2.264.1148569205
+DATA/ORCL/ONLINELOG/group_2.266.1148569211
+DATA/ORCL/ONLINELOG/group_1.263.1148569205
+DATA/ORCL/ONLINELOG/group_1.265.1148569211
+DATA/ORCL/DATAFILE/users.260.1148569149
+DATA/ORCL/DATAFILE/undotbs1.259.1148569147
+DATA/ORCL/DATAFILE/system.257.1148569037
+DATA/ORCL/DATAFILE/sysaux.258.1148569113
+DATA/ORCL/TEMPFILE/temp.267.1148569225
+DATA/ORCL/DATAFILE/undotbs2.268.1148570375
+DATA/ORCL/ONLINELOG/group_3.269.1148570523
+DATA/ORCL/ONLINELOG/group_3.270.1148570527
+DATA/ORCL/ONLINELOG/group_4.271.1148570533
+DATA/ORCL/ONLINELOG/group_4.272.1148570537
7、创建参数文件
[grid@rac01:/home/grid/backup/file/data]$ls
redo1 redo2 redo3 redo4 sysaux system temp undotbs1 undotbs2 users
SQL> startup nomount pfile='/home/oracle/data/pfile.ora';
ORACLE instance started.
Total System Global Area 3019895288 bytes
Fixed Size 8901112 bytes
Variable Size 687865856 bytes
Database Buffers 2315255808 bytes
Redo Buffers 7872512 bytes
8、重建控制文件
SQL> alter database mount;
Database altered.
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/home/oracle/data/redo1'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'/home/oracle/data/redo2'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'/home/oracle/data/redo3'
) SIZE 200M BLOCKSIZE 512,
GROUP 4 (
'/home/oracle/data/redo4'
) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/data/system',
'/home/oracle/data/sysaux',
'/home/oracle/data/undotbs1',
'/home/oracle/data/undotbs2',
'/home/oracle/data/users'
CHARACTER SET ZHS16GBK
;
9、启动
SQL> alter database open;
Database altered.
SQL> create spfile from pfile='/home/oracle/data/pfile.ora';
File created.
三、重建asm磁盘组
1、删除磁盘组
SQL> drop diskgroup data including contents;
Diskgroup dropped.
SQL> alter diskgroup arch mount;
Diskgroup altered.
SQL> drop diskgroup arch;
Diskgroup dropped.
2、创建磁盘组
SQL> create diskgroup data disk '/dev/mapper/asm-data03','/dev/mapper/asm-data04';
Diskgroup created.
SQL> create diskgroup arch disk '/dev/mapper/asm-arch01','/dev/mapper/asm-arch02';
Diskgroup created.
四、迁移数据库至ASM
1、迁移控制文件文件
SQL> startup nomount pfile='/home/oracle/data/pfile.ora';
ORACLE instance started.
Total System Global Area 3019895288 bytes
Fixed Size 8901112 bytes
Variable Size 620756992 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
SQL> create spfile='+DATA' from pfile='/home/oracle/data/pfile.ora';
File created.
SQL> shutdown immediate
[oracle@rac01:/home/oracle]$rman target /
restore controlfile to '+data' from '/home/oracle/data/control01.ctl';
Starting restore at 2023-10-09 07:57:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 2023-10-09 07:57:54
2、迁移参数文件
因为参数文件中的重要部分是control_files参数,所以先迁移控制文件再修改参数文件
[oracle@rac01:/home/oracle]$rman target /
restore controlfile to '+data' from '/home/oracle/data/control01.ctl';
Starting restore at 2023-10-09 07:57:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 2023-10-09 07:57:54
修改参数文件中control_files参数
control_files='+DATA/CONTROLFILE/current.257.1149753473'
SQL> create spfile from pfile='/home/oracle/data/pfile.ora';
File created.
3、迁移数据文件
SQL> shutdown immediate;
SQL> startup mount
ORACLE instance started.
Total System Global Area 3019895288 bytes
Fixed Size 8901112 bytes
Variable Size 620756992 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
Database mounted.
[oracle@rac01:/home/oracle/data]$rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Oct 9 08:10:28 2023
Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1676349935, not open)
RMAN> backup as copy database format '+DATA';
Starting backup at 2023-10-09 08:10:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/data/system
output file name=+DATA/ORCL/DATAFILE/system.264.1149754241 tag=TAG20231009T081041 RECID=1 STAMP=1149754261
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/data/sysaux
output file name=+DATA/ORCL/DATAFILE/sysaux.265.1149754267 tag=TAG20231009T081041 RECID=2 STAMP=1149754280
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/data/undotbs1
output file name=+DATA/ORCL/DATAFILE/undotbs1.266.1149754283 tag=TAG20231009T081041 RECID=3 STAMP=1149754293
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/data/undotbs2
output file name=+DATA/ORCL/DATAFILE/undotbs2.267.1149754297 tag=TAG20231009T081041 RECID=4 STAMP=1149754297
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/home/oracle/data/users
output file name=+DATA/ORCL/DATAFILE/users.268.1149754299 tag=TAG20231009T081041 RECID=5 STAMP=1149754298
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2023-10-09 08:11:39
Starting Control File and SPFILE Autobackup at 2023-10-09 08:11:39
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-1676349935-20231009-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2023-10-09 08:11:40
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/ORCL/DATAFILE/system.264.1149754241"
datafile 3 switched to datafile copy "+DATA/ORCL/DATAFILE/sysaux.265.1149754267"
datafile 4 switched to datafile copy "+DATA/ORCL/DATAFILE/undotbs1.266.1149754283"
datafile 5 switched to datafile copy "+DATA/ORCL/DATAFILE/undotbs2.267.1149754297"
datafile 7 switched to datafile copy "+DATA/ORCL/DATAFILE/users.268.1149754299"
RMAN> alter database open;
Statement processed
RMAN>
4、迁移redo文件
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------- --- ----------
3 STALE ONLINE /home/oracle/data/redo3 NO 0
4 STALE ONLINE /home/oracle/data/redo4 NO 0
1 ONLINE /home/oracle/data/redo1 NO 0
2 ONLINE /home/oracle/data/redo2 NO 0
SQL> alter database add logfile member '+data' to group 1;
Database altered.
SQL> alter database add logfile member '+data' to group 2;
Database altered.
SQL> alter database add logfile member '+data' to group 3;
Database altered.
SQL> alter database add logfile member '+data' to group 4;
Database altered.
SQL> /
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
3 STALE ONLINE /home/oracle/data/redo3 NO 0
4 STALE ONLINE /home/oracle/data/redo4 NO 0
1 ONLINE /home/oracle/data/redo1 NO 0
2 ONLINE /home/oracle/data/redo2 NO 0
1 ONLINE +DATA/ORCL/ONLINELOG/group_1.269.1149754511 NO 0
2 ONLINE +DATA/ORCL/ONLINELOG/group_2.270.1149754521 NO 0
3 INVALID ONLINE +DATA/ORCL/ONLINELOG/group_3.271.1149754539 NO 0
4 INVALID ONLINE +DATA/ORCL/ONLINELOG/group_4.272.1149754549 NO 0
8 rows selected.
启动二节点发现报错
2023-10-09T08:46:45.608523+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_lgwr_16076.trc:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '/home/oracle/data/redo3'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2023-10-09T08:46:45.624354+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_16122.trc:
ORA-00313: open failed for members of log group 2 of thread
ORA-00312: online log 3 thread 2: '/home/oracle/data/redo3'
ORA-00312: online log 3 thread 2: '+DATA/ORCL/ONLINELOG/group_3.271.1149754539'
USER (ospid: ): terminating the instance due to ORA error
既然读不到redo，可以复制给他让他读
[oracle@rac02:/home/oracle]$mkdir data
[oracle@rac02:/home/oracle]$ls
data Desktop Documents Downloads Music Pictures Public Templates Videos
[oracle@rac02:/home/oracle]$cd data/
[oracle@rac02:/home/oracle/data]$scp oracle@rac01:/home/oracle/data/redo* ./
redo1 100% 200MB 79.2MB/s 00:02
redo2 100% 200MB 94.6MB/s 00:02
redo3 100% 200MB 115.5MB/s 00:01
redo4 100% 200MB 113.8MB/s 00:01
[oracle@rac02:/home/oracle/data]$sqlplus / as sysdba
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
/
/
System altered.
SQL>
System altered.
SQL>
System altered.
SQL> SQL> set linesize 1000
SQL> col member for a100
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------------------------------------------------------------------- --- ----------
3 ONLINE /home/oracle/data/redo3 NO 0
4 ONLINE /home/oracle/data/redo4 NO 0
1 ONLINE /home/oracle/data/redo1 NO 0
2 ONLINE /home/oracle/data/redo2 NO 0
1 ONLINE +DATA/ORCL/ONLINELOG/group_1.269.1149754511 NO 0
2 ONLINE +DATA/ORCL/ONLINELOG/group_2.270.1149754521 NO 0
3 ONLINE +DATA/ORCL/ONLINELOG/group_3.271.1149754539 NO 0
4 ONLINE +DATA/ORCL/ONLINELOG/group_4.272.1149754549 NO 0
8 rows selected.
完美
SQL> alter database drop logfile member '/home/oracle/data/redo3';
Database altered.
SQL> alter database drop logfile member '/home/oracle/data/redo1';
alter database drop logfile member '/home/oracle/data/redo1'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/home/oracle/data/redo1'
ORA-00312: online log 1 thread 1: '+DATA/ORCL/ONLINELOG/group_1.269.1149754511'
SQL> alter database drop logfile member '/home/oracle/data/redo2';
Database altered.
...
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile member '/home/oracle/data/redo4';
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------------------------------------------------------------------- --- ----------
1 ONLINE /home/oracle/data/redo1 NO 0
1 ONLINE +DATA/ORCL/ONLINELOG/group_1.269.1149754511 NO 0
2 ONLINE +DATA/ORCL/ONLINELOG/group_2.270.1149754521 NO 0
3 ONLINE +DATA/ORCL/ONLINELOG/group_3.271.1149754539 NO 0
4 ONLINE +DATA/ORCL/ONLINELOG/group_4.272.1149754549 NO 0
至此全部替换完成
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
