首页 > 数据库 >Oracle 12C数据库从文件系统迁移到ASM

Oracle 12C数据库从文件系统迁移到ASM

时间：2024-02-29 13:59:12浏览次数：34  
标签：datafile 12C group orcl ORCL SQL Oracle DATA 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

 

标签：datafile,12C,group,orcl,ORCL,SQL,Oracle,DATA,ASM
From： https://www.cnblogs.com/aries0228/p/18043524

相关文章

  • Oracle一次更新(UpData)多列通过Select获取到的值
    第一种MERGEINTO方法MERGEINTOSAFERUNWMS.WCSSTOCKLOCWSLUSING(SELECTWSP.LOCNO,WSP.MATERCODE,WSP.PALLETNO,WSP.BATNOFROMSAFERUNWMS.WCSSTOCKPORTWSPWHEREWSP.LOCNO=N_S......
  • oracle表空间不足报错排查及扩容
    一、报错代码：Errorupdatingdatabase. Cause:java.sql.SQLException:ORA-01691:unabletoextendlobsegmentOMSP_FLOWGINE.SYS_LOB0000035445C00014$$by1024intablespaceOMSP 二、排查执行下面sql:SELECTa.tablespace_name,TRUNC(tablespace_size......
  • Oracle-JPA扩展工具-原生SQL查询并返回自定义DTO
    Java-JPA原生SQL查询返回自定义DTO:importcom.tjgeo.njsosms.framework.repository.IBaseRepository;importcom.tjgeo.njsosms.risk.support.entity.Source;importorg.springframework.data.jpa.repository.Query;importjava.util.List;/***${Description}**@......
  • oracle增加表分区，金仓增加表分区，oracle增加数据库空间
    1.金仓增加表分区CREATETABLE"public"."分区名"PARTITIONOF"public"."表名"FORVALUESFROM('2024-02-0100:00:00')TO('2024-02-2923:59:59');COMMENTONTABLE"public"."分区名"IS'......
  • Oracle设置日志参数-ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    要实现两个数据库之间的实时同步，需要给Oracle设置参数ALTERDATABASEADDSUPPLEMENTALLOGDATA;--执行了12小时，等待数据库中的其它事务都提交以后才执行完成ALTERDATABASEADDSUPPLEMENTALLOGDATA(PRIMARYKEY)COLUMNS;ALTERDATABASEADDSUPPLEMENTALLOGDATA(......
  • centos7.8 oracle19c 安装记录
    该安装方案前提是可以连接因特网前提，因为在安装过程会自动补充一些linux的组件。下载预安装和oracle下载oracle19c的地址：https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html下载预安装oracle-database-preinstall-19c-1.......
  • mysql和oracle执行sql时弹出变量输入框
    MySQL查询条件中实现弹窗输入变量这种属于客户端软件自身的交互，是由客户端自身实现的特性，所以不同客户端的实现方式也不一样，大致列几个常用客户端的SQL语句实现方式如下：PL/SQL：使用&引用变量弹框输入变量值wherePeriod_Start_Time>=to_date(&开始时间,'yyyymmddhh24')andPeriod_S......
  • 达梦、Oracle、Mysql和PostgreSQL数据库重要参数对比
    前言数据库在安装完成之后通常都会配置一些基础的参数用于控制和管理数据库行为，其中有些参数在配置完成后若要修改则需要重启数据库才能生效，甚至一些参数在完成初始化之后无法修改，这些参数在生产环境中尤其需要关注，需要事先就确定好，避免后续遇到需要修改时影响到生产环境的使用。......
  • oracle查看触发器
    参考：https://blog.csdn.net/weixin_43487853/article/details/131085585查询所有触发器、存储过程、视图、表--所有触发器object_name触发器名称(也可以查user_triggers表)Select*Fromuser_objectsWhereobject_type='TRIGGER';--所有存储过程object_name存储名称(也......
  • 21、oracle报ORA-04091发生了变化, 触发器函数不能读它
    21、oracle报ORA-04091发生了变化,触发器函数不能读它​ 在对某表进行更新的时候，调用了一个函数，函数中又使用该表进行读的操作，会导致读取到错误的数据。所以在函数中进行事务的锁定。解决方案：在begin之前增加pragmaautonomous_transaction;,在end之前增加commit;funcation......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99