迁移数据到新建的磁盘组
修改数据库归档模式
修改参数
export ORACLE_SID=audb021
sqlplus / as sysdba
archive log list;
select instance_name,host_name,status from gv$instance;
show parameter cluster;
create pfile='/tmp/oraAudb02.ora' from spfile;
实例1
alter system set log_archive_dest_1 = 'LOCATION=/tmp1' scope=both sid='audb021';
实例2
alter system set log_archive_dest_1 = 'LOCATION=/tmp1' scope=both sid='audb022';
在两个节点验证参数的状态
select dest_name,status,error from v$archive_dest;
修改实例为单实例模式
alter system set cluster_database=false scope=spfile sid='*';
启动数据库到mount
srvctl stop database -daudb02;
srvctl start instance -daudb02-iaudb021-o mount
sqlplus / as sysdba
SQL>select instance_name,status from v$instance;
SQL>alter database archivelog;
SQL>alter system set cluster_database=true scope=spfile sid='*';
SQL> ho srvctl stop instance -daudb02-iaudb021;
SQL> ho srvctl start database -daudb02;
SQL> archive log list;
SQL> show parameter db_recovery_file
归档验证
实例1
SQL> select inst_id,name,thread#,sequence#,status from gv$archived_log;
SQL> alter system switch logfile;
SQL> col name format a65
SQL> select inst_id,name,thread#,sequence#,status from gv$archived_log;
SQL> select name,thread#,sequence#,status from v$archived_log;
实例2
SQL> show parameter instance_name;
SQL> select inst_id,name,thread#,sequence#,status from gv$archived_log;
SQL> alter system switch logfile;
SQL> select * from v$log;
SQL> alter system archive log current;
SQL> select inst_id,name,thread#,sequence#,status from gv$archived_log;
创建ASM磁盘组
Grid用户下执行asmca,选择外部冗余,创建磁盘组DATADG2,并挂载磁盘组
SQL> select state,name,type from v$asm_diskgroup;
备份数据库控制文件
SQL> show parameter db_name
SQL> select name from v$controlfile;
SQL> create pfile='/TMP/initsid.ora' from spfile;
SQL> alter database backup controlfile to '+DATADG2';
SQL> alter system set control_files='+DATADG2\CONTROLFILE\ATADG>' SCOPE=SPFILE;
RMAN拷贝数据库
$ rman target /
RMAN > shutdown immediate;
RMAN > startup nomount;
RMAN> restore controlfile to 'ATADG2>' from '+DATADG\CONTROLFILE\mycontrol.ctl'; (specify the original (old) location of controlfile here)
RMAN > alter database mount;
RMAN> backup as copy database format '+DATADG2';
SWITCH数据库并恢复打开
RMAN> switch database to copy;
RMAN> recover database ;
RMAN> alter database open resetlogs;
重置TEMP表空间
SQL> alter tablespace TEMP add tempfile '+DATADG2'SIZE 1000M;
SQL> alter database tempfile '+DATADG/tempfile/temp.265.626631119' drop;
重置REDO
SQL> alter database add logfilethread 1 group 5'+DG2'size 100m;
Alter database drop logfile group 1;
检查数据库文件路径
SQL> select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
修改数据库启动参数文件路径