Oracle 12C数据库从文件系统迁移到ASM
查看参数文件位置
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 /dbhome_2/dbs/spfileorcl.ora
查看控制文件位置
SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/orcl/c ontrol01.ctl, /u01/app/oracle/ fast_recovery_area/orcl/contro l02.ctl
查看日志组状态
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ---------- 1 1 1 209715200 512 1 YES INACTIVE 7887970 2024-02-28:15:46:53 7997085 2024-02-28:16:16:30 0 2 1 2 209715200 512 1 YES INACTIVE 7997085 2024-02-28:16:16:30 8126420 2024-02-29:09:37:37 0 3 1 3 209715200 512 1 NO CURRENT 8126420 2024-02-29:09:37:37 2.8147E+14 0
查看日志文件位置
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ------------------------------------------------------------------------------------------------------------ --- ---------- 3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO 0 2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO 0 1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO 0
更改db_recovery_file_dest和db_create_file_dest参数，开启OMF，指向ASM磁盘组
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 2G SQL> alter system set db_recovery_file_dest='+fra'; System altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string SQL> alter system set db_create_file_dest='+data'; System altered.
迁移INACTIVE日志组文件
SQL> alter database drop logfile group 1;###必须是INACTIVE的日志组才能删除
Database altered.
SQL> alter database add logfile group 1; ###重建日志组，默认100M,可用size指定大小
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2;
Database altered.
迁移CURRENT状态日志组文件
SQL> alter system switch logfile;###切换日志文件，3号日志文件由CURRENT变成ACTIVE状态
System altered.
SQL> alter system checkpoint;###发出检查点，移动RBA指针。3号日志文件变成INACTIVE
System altered.
SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3; Database altered.
重启数据库到mount状态。
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 629145704 bytes Database Buffers 436207616 bytes Redo Buffers 5455872 bytes Database mounted.
迁移数据文件。使用RMAN备份数据库镜像拷贝到ASM。
[oracle@OraSrv ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on 星期四 2月 29 11:23:51 2024 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1578831330, not open) RMAN> backup as copy database format '+data'; Starting backup at 2024-02-29:11:25:03 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=6 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf output file name=+DATA/ORCL/DATAFILE/sysaux.270.1162207509 tag=TAG20240229T112504 RECID=1 STAMP=1162207517 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf output file name=+DATA/ORCL/DATAFILE/undotbs1.280.1162207521 tag=TAG20240229T112504 RECID=2 STAMP=1162207528 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf output file name=+DATA/ORCL/DATAFILE/system.277.1162207535 tag=TAG20240229T112504 RECID=3 STAMP=1162207541 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy copying current control file output file name=+DATA/ORCL/CONTROLFILE/backup.279.1162207543 tag=TAG20240229T112504 RECID=4 STAMP=1162207542 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf output file name=+DATA/ORCL/DATAFILE/users.283.1162207543 tag=TAG20240229T112504 RECID=5 STAMP=1162207543 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 2024-02-29:11:25:44 channel ORA_DISK_1: finished piece 1 at 2024-02-29:11:25:45 piece handle=+DATA/ORCL/BACKUPSET/2024_02_29/nnsnf0_tag20240229t112504_0.292.1162207545 tag=TAG20240229T112504 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2024-02-29:11:25:45
迁移控制文件
SQL> alter database backup controlfile to trace as '/home/oracle/controlfile_asm.sql';####生成控制文件脚本 Database altered.
控制文件脚本比较：Set #1和Set #2 。
Set #1脚本日志文件没有被破坏的情况下使用，Set #2脚本日志文件损坏的情况下使用。
-- -- Set #1. NORESETLOGS case -- STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DATA/ORCL/ONLINELOG/group_1.273.1162205629', '+FRA/ORCL/ONLINELOG/group_1.343.1162205629' ) SIZE 100M BLOCKSIZE 512, GROUP 2 ( '+DATA/ORCL/ONLINELOG/group_2.278.1162205657', '+FRA/ORCL/ONLINELOG/group_2.342.1162205657' ) SIZE 100M BLOCKSIZE 512, GROUP 3 ( '+DATA/ORCL/ONLINELOG/group_3.271.1162206395', '+FRA/ORCL/ONLINELOG/group_3.336.1162206395' ) SIZE 100M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf' CHARACTER SET AL32UTF8 ; RECOVER DATABASE ALTER DATABASE OPEN; -- -- Set #2. RESETLOGS case -- STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DATA/ORCL/ONLINELOG/group_1.273.1162205629', '+FRA/ORCL/ONLINELOG/group_1.343.1162205629' ) SIZE 100M BLOCKSIZE 512, GROUP 2 ( '+DATA/ORCL/ONLINELOG/group_2.278.1162205657', '+FRA/ORCL/ONLINELOG/group_2.342.1162205657' ) SIZE 100M BLOCKSIZE 512, GROUP 3 ( '+DATA/ORCL/ONLINELOG/group_3.271.1162206395', '+FRA/ORCL/ONLINELOG/group_3.336.1162206395' ) SIZE 100M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf', '/u01/app/oracle/oradata/orcl/sysaux01.dbf', '/u01/app/oracle/oradata/orcl/undotbs01.dbf', '/u01/app/oracle/oradata/orcl/users01.dbf' CHARACTER SET AL32UTF8 ; RECOVER DATABASE USING BACKUP CONTROLFILE ALTER DATABASE OPEN RESETLOGS;
由于日志文件没有损坏，使用Set #1脚本。编辑脚本，修改数据文件路径。
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DATA/ORCL/ONLINELOG/group_1.273.1162205629', '+FRA/ORCL/ONLINELOG/group_1.343.1162205629' ) SIZE 100M BLOCKSIZE 512, GROUP 2 ( '+DATA/ORCL/ONLINELOG/group_2.278.1162205657', '+FRA/ORCL/ONLINELOG/group_2.342.1162205657' ) SIZE 100M BLOCKSIZE 512, GROUP 3 ( '+DATA/ORCL/ONLINELOG/group_3.271.1162206395', '+FRA/ORCL/ONLINELOG/group_3.336.1162206395' ) SIZE 100M BLOCKSIZE 512 DATAFILE '+data/orcl/datafile/SYSTEM.277.1162207535', '+data/orcl/datafile/SYSAUX.270.1162207509', '+data/orcl/datafile/UNDOTBS1.280.1162207521', '+data/orcl/datafile/USERS.283.1162207543' CHARACTER SET AL32UTF8 ;
重新设置控制文件路径，指向ASM
SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/orcl/c ontrol01.ctl, /u01/app/oracle/ fast_recovery_area/orcl/contro l02.ctl SQL> alter system set control_files='+data','+fra' scope=spfile; System altered.
重启数据库到nomount
SQL> shutdown immediate
ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 629145704 bytes Database Buffers 436207616 bytes Redo Buffers 5455872 bytes
用控制文件脚本创建控制文件
SQL> @/home/oracle/controlfile_asm.sql Control file created.
查看控制文件路径
SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/ORCL/CONTROLFILE/current .291.1162211291, +FRA/ORCL/CON TROLFILE/current.697.116221129 1
迁移spfile参数文件
SQL> create pfile from spfile; File created. SQL> create spfile='+data/orcl/spfileorcl.ora' from pfile; File created.
删除dbs目录下的spfileorcl.ora，编辑initorcl.ora，指向spfile。[非必须]
spfile='+data/orcl/spfileorcl.ora'
重新启动数据，检查参数文件、控制文件、日志文件、数据文件
SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 629145704 bytes Database Buffers 436207616 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/orcl/spfileorcl.ora SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/ORCL/CONTROLFILE/current .291.1162211291, +FRA/ORCL/CON TROLFILE/current.697.116221129 1 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ---------- 1 1 4 104857600 512 2 YES INACTIVE 8129474 2024-02-29:11:03:37 8130069 2024-02-29:12:50:10 0 2 1 5 104857600 512 2 NO CURRENT 8130069 2024-02-29:12:50:10 2.8147E+14 0 3 1 0 104857600 512 2 YES UNUSED 0 0 0 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ------------------------------------------------------------------------------------------------------------ --- ---------- 1 STALE ONLINE +DATA/ORCL/ONLINELOG/group_1.273.1162205629 NO 0 1 STALE ONLINE +FRA/ORCL/ONLINELOG/group_1.343.1162205629 YES 0 3 STALE ONLINE +DATA/ORCL/ONLINELOG/group_3.271.1162206395 NO 0 3 STALE ONLINE +FRA/ORCL/ONLINELOG/group_3.336.1162206395 YES 0 2 ONLINE +DATA/ORCL/ONLINELOG/group_2.278.1162205657 NO 0 2 ONLINE +FRA/ORCL/ONLINELOG/group_2.342.1162205657 YES 0 6 rows selected. SQL> select name from v$datafile; NAME --------------------------------------------- +DATA/orcl/datafile/system.277.1162207535 +DATA/orcl/datafile/sysaux.270.1162207509 +DATA/orcl/datafile/undotbs1.280.1162207521 +DATA/orcl/datafile/users.283.1162207543
添加临时数据文件
SQL> alter tablespace temp add tempfile size 100m autoextend on; Tablespace altered.
修改闪回恢复区大小
SQL> select * from v$asm_diskgroup; GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY DATABASE_COMPATIBILITY V CON_ID ------------ ------------------------------ ----------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ - ---------- 2 FRA 512 4096 1048576 CONNECTED EXTERN 40960 33969 0 6991 0 33969 0 12.1.0.0.0 10.1.0.0.0 N 0 1 DATA 512 4096 1048576 CONNECTED NORMAL 61440 46041 0 15399 10240 17900 0 12.1.0.0.0 10.1.0.0.0 N 0 SQL> alter system set db_recovery_file_dest_size=33969M; System altered. SQL> show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 33969M
本文来自博客园，作者：霜雪吴钩，转载请注明原文链接：https://www.cnblogs.com/aries0228/p/18043524
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了