Oracle数据库文件系统迁移到ASM磁盘(二)

SQL> alter database add logfile '+DG1' size 51m;
Database altered.
SQL> alter database add logfile '+DG1' size 51m;
Database altered.
SQL> alter database add logfile '+DG1' size 51m;
Database altered.

删除的时候需要日志文件组状态为inactive状态，不过删除不了，说明日志文件组不是inactive状态
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ------------------------------------------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
SQL> alter database drop logfile '/u01/app/oracle/oradata/prod/redo01.log';
Database altered.
SQL> alter database drop logfile '/u01/app/oracle/oradata/prod/redo02.log';
alter database drop logfile '/u01/app/oracle/oradata/prod/redo02.log'
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance prod (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/prod/redo02.log'

SQL> alter database drop logfile '/u01/app/oracle/oradata/prod/redo03.log';
Database altered.

遇到删除不了时使用如下命令更改日志文件状态，知道可以删除
SQL> alter system switch logfile;
SQL> alter database drop logfile '/u01/app/oracle/oradata/prod/redo02.log';
Database altered.
查看日志文件
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075

查看迁移后的文件
SQL> select name from v$datafile union select name from v$tempfile union select name from v$controlfile union
select member from v$logfile;
NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617
+DG1/prod/datafile/example.274.842151187
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/users.276.842151211
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075
+DG1/prod/tempfile/temp.279.842151759

最后迁移初始化参数文件
SQL> create pfile from spfile;
File created.
SQL> create spfile='+DG1' from pfile;
File created.

重启数据库查看是否能够正常启动
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1218556 bytes
Variable Size 71305220 bytes
Database Buffers 134217728 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
查看迁移后的文件
SQL> select name from v$datafile union select name from v$tempfile union select name from v$controlfile union
select member from v$logfile;
NAME
--------------------------------------------------------------------------------
+DG1/prod/controlfile/backup.271.842150617
+DG1/prod/datafile/example.274.842151187
+DG1/prod/datafile/sysaux.273.842151185
+DG1/prod/datafile/system.272.842151185
+DG1/prod/datafile/undotbs1.275.842151191
+DG1/prod/datafile/users.276.842151211
+DG1/prod/onlinelog/group_4.280.842152057
+DG1/prod/onlinelog/group_5.281.842152065
+DG1/prod/onlinelog/group_6.282.842152075
+DG1/prod/tempfile/temp