迁移控制文件到ASM中的方法 10g11g12c通用

利用重建控制文件脚本迁移控制文件到ASM中的方法

1.查看ASM磁盘组 

select name from v$asm_diskgroup;

2.生成控制文件建立脚本

alter database  backup controlfile to trace as'/tmp/c.sql';

3.设置control_file路径

alter system set control_files='+db','+fra' scope=spfile;

4.一致性关闭数据库

shutdown immidate

5.修改脚本c.sql 

vi打开 找到

 End of tempfile additions. 之后的行都删除

保留：

sys@CRAB< ed /tmp/c.sql 

-- The following are current System-scope REDO Log Archival related 
-- parameters and can be included in the database initialization file. 
-- 
-- LOG_ARCHIVE_DEST='' 
-- LOG_ARCHIVE_DUPLEX_DEST='' 
-- 
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf 
-- 
-- DB_UNIQUE_NAME="crab" 
-- 
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' 
-- LOG_ARCHIVE_MAX_PROCESSES=2 
-- STANDBY_FILE_MANAGEMENT=MANUAL 
-- STANDBY_ARCHIVE_DEST=?/dbs/arch 
-- FAL_CLIENT='' 
-- FAL_SERVER='' 
-- 
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' 
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY' 
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' 
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY' 
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' 
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' 
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE 

-- 
-- Below are two sets of SQL statements, each of which creates a new 
-- control file and uses it to open the database. The first set opens 
-- the database with the NORESETLOGS option and should be used only if 
-- the current versions of all online logs are available. The second 
-- set opens the database with the RESETLOGS option and should be used 
-- if online logs are unavailable. 
-- The appropriate set of statements can be copied from the trace into 
-- a script file, edited as necessary, and executed when there is a 
-- need to re-create the control file. 
-- 
-- Set #1. NORESETLOGS case 
-- 
-- The following commands will create a new control file and use it 
-- to open the database. 
-- Data used by Recovery Manager will be lost. 
-- Additional logs may be required for media recovery of offline 
-- Use this only if the current versions of all online logs are 
-- available. 

-- After mounting the created controlfile, the following SQL 
-- statement will place the database in the appropriate 
-- protection mode: 
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE 

STARTUP NOMOUNT 
CREATE CONTROLFILE REUSE DATABASE "CRAB" NORESETLOGS ARCHIVELOG 
MAXLOGFILES 16 
MAXLOGMEMBERS 3 
MAXDATAFILES 100 
MAXINSTANCES 8 
MAXLOGHISTORY 292 
LOGFILE 
GROUP 1 '/u01/app/oracle/oradata/crab/redo01.log' SIZE 50M, 
GROUP 2 '/u01/app/oracle/oradata/crab/redo02.log' SIZE 50M, 

DATAFILE 
'/u01/app/oracle/oradata/crab/system01.dbf', 
'/u01/app/oracle/oradata/crab/undotbs01.dbf', 
CHARACTER SET AL32UTF8 
; 

-- Configure RMAN configuration record 1 
VARIABLE RECNO NUMBER; 
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); 
-- Configure RMAN configuration record 2 
VARIABLE RECNO NUMBER; 
-- Commands to re-create incarnation table 
-- Below log names MUST be changed to existing filenames on 
-- disk. Any one log file from each branch can be used to 
-- re-create incarnation records. 
-- Recovery is required if any of the datafiles are restored backups, 
-- or if the last shutdown was not normal or immediate. 
RECOVER DATABASE 

-- All logs need archiving and a log switch is needed. 
ALTER SYSTEM ARCHIVE LOG ALL; 

-- Database can now be opened normally. 
ALTER DATABASE OPEN; 

-- Commands to add tempfiles to temporary tablespaces. 
-- Online tempfiles have complete space information. 
-- Other tempfiles may require adjustment. 
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/crab/temp01.dbf' 
SIZE 32505856 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; 
-- End of tempfile additions.

注释也可以删除。

6. 登陆执行脚本 

sqlplus / as sysdba

@ /tmp/c.sql

7.等待完成，查询验证。

select name from v$asm_diskgroup;

此方法10g11g12c通用.