一、把数据库从asm迁移到文件系统
数据库版本是:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
shutdown数据库之后,在asmcmd中利用cp命令复制出参数文件:
ASMCMD> cp '+DG/orcl/parameterfile/spfile.265.791417205' to '/u01/app/oradata/spfile.ora'
把ORACLE_HOME/dbs/initorcl.ora改为:
#SPFILE='+DG/orcl/spfileorcl.ora'
#SPFILE='+dg/orcl/parameterfile/spfile.265.791417205'
SPFILE='/u01/app/oradata/spfile.ora'
#control_files='/u01/app/oradata/controlfile.ora'
nomount数据库之后,发现不能修改数据库初始化参数,一直报:“ORA-27069: 试图超出文件范围执行 I/O”。怀疑是spfile的问题,然后采取的做法是:
SQL> startup nomount pfile='/u01/app/oradata/pfile.ora'
SQL> create spfile='/u01/app/oradata/spfile.ora' from pfile='/u01/app/oradata/pfile.ora';
nomount数据库之后,再asmcmd中利用cp命令复制出控制文件:
ASMCMD> cp '+DG/orcl/controlfile/current.260.773992045' to '/u01/app/oradata/controlfile.ora'
SQL> alter system set control_files='/u01/app/oradata/controlfile.ora' scope=spfile;
SQL> alter system set db_create_file_dest='';
mount数据库之后,移动数据文件:
ASMCMD> cp '+DG/orcl/datafile/system.256.773991911' to '/u01/app/oradata/system.ora'
SQL> alter
database orcl rename file
'+DG/orcl/datafile/system.256.773991911'
to
'/u01/app/oradata/system.ora';
后面就换rman来移动数据文件了,因为cp命令是11G才支持的命令:
RMAN> backup
as copy datafile
'+DG/orcl/datafile/sysaux.257.773991911' format '/u01/app/oradata/sysaux.ora';
RMAN> backup
as copy datafile
'+DG/orcl/datafile/undotbs1.258.773991911' format '/u01/app/oradata/undotbs1.ora';
RMAN> backup
as copy datafile
'+DG/orcl/datafile/users.259.773991911' format '/u01/app/oradata/users.ora';
RMAN> backup
as copy datafile
'+DG/orcl/datafile/tab1.267.794450025' format '/u01/app/oradata/tab1.ora';
RMAN> backup
as copy datafile
'+DG/orcl/datafile/tab2.266.794450025' format '/u01/app/oradata/tab2.ora';
RMAN> backup
as copy datafile
'+DG/orcl/datafile/assm.268.806611299' format '/u01/app/oradata/assm.ora';
RMAN> backup
as copy datafile
'+DG/orcl/datafile/mssm.269.806611309' format '/u01/app/oradata/mssm.ora';
RMAN> backup
as copy datafile
'+DG/orcl/datafile/rman_tb.270.818005311' format '/u01/app/oradata/rman_tb.ora';
SQL> alter
database orcl rename file
'+DG/orcl/datafile/sysaux.257.773991911
'
to
'/u01/app/oradata/sysaux.ora';
SQL>
alter database orcl rename file '+DG/orcl/datafile/undotbs1.258.773991911' to '/u01/app/oradata/undotbs1.ora';
SQL>
alter
database orcl rename file
'+DG/orcl/datafile/users.259.773991911' to '/u01/app/oradata/users.ora';
SQL>
alter
database orcl rename file
'+DG/orcl/datafile/tab1.267.794450025' to '/u01/app/oradata/tab1.ora';
SQL>
alter
database orcl rename file
'+DG/orcl/datafile/tab2.266.794450025' to '/u01/app/oradata/tab2.ora';
SQL>
alter
database orcl rename file
'+DG/orcl/datafile/assm.268.806611299' to '/u01/app/oradata/assm.ora';
SQL>
alter
database orcl rename file
'+DG/orcl/datafile/mssm.269.806611309' to '/u01/app/oradata/mssm.ora';
SQL>
alter
database orcl rename file
'+DG/orcl/datafile/rman_tb.270.818005311' to '/u01/app/oradata/rman_tb.ora';
open数据库之后,重建联机日志文件:
SQL> alter
database
add logfile
group 4(
'/u01/app/oradata/group_4.ora
')
size 50m;
SQL> alter
database
add logfile
group 5(
'/u01/app/oradata/group_5.ora
')
size 50m;
SQL> alter
database
drop logfile
group 2;
SQL> alter
database
drop logfile
group 3;
SQL> alter system switch logfile;
SQL> alter system checkpoint;
SQL> alter
database
drop logfile
group 1;
open数据库之后,重建临时表空间:
SQL> create
temporary tablespace temp2 tempfile
'/u01/app/oradata/temp.ora
'
size 5G;
SQL> alter
database
default
temporary tablespace temp2;
SQL>
drop tablespace temp including contents
and datafiles;
open数据库之后,更改闪回恢复区:
SQL> alter system set db_recovery_file_dest='/u01/app/oradata/flash_area' scope=spfile;
二、完全卸载asm实例
shutdown数据库之后,nomount asm实例,卸载asm实例: (在grid用户中)
SQL> drop diskgroup bg including contents;
在drop dg磁盘组的时候会报错:“ORA-15027: active use of diskgroup "DG" precludes its dismount”。默认情况下,Oracle使用asm的第一个磁盘组存放asm实例的参数文件,也就是说这个磁盘组处于使用状态,是无法删除的,这也就是上面报错的原因,知道了原因 ,那解决起来就是很简单的事情了。
SQL> create pfile='/u01/app/oradata/init+ASM.ora' from spfile;
SQL> shutdown immediate
SQL> startup pfile='/u01/app/oradata/init+ASM.ora'
SQL> alter diskgroup dg mount;
SQL> drop diskgroup dg including contents;
卸载grid:
Oracle 11g
之后提供了卸载
grid
和
database
的脚本,可以卸载的比较干净,不需要手动删除
crs等。
运行:./deinstall (在$ORACLE_HOME/deinstall目录下,
grid
和
database都可以找到。
)
三、最后:
启动数据库,然后直接敲lsnrctl start 发现也能启动listener,但此时明明没有listener的,如图:
使用netca配置一个listener,其实就是加入一个配置文件:
再次启动listener,看见这次有不一样的地方: