暂无图片
暂无图片
8
暂无图片
暂无图片
暂无图片

Oracle Rac 集群文件目录迁移

原创 布衣&凡尘 2022-07-27
1541

一、rac 环境修改spfile位置

1. 从集群的任意一个实例登录,查看spfile信息

SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/racdb/spfileracdb.ora'

2. 重新创建新的spfile

SQL> create pfile = '/home/oracle/pfile.ora' from spfile; File created. SQL> create spfile='+NEW_DATA' from pfile = '/home/oracle/pfile.ora'; File created.

3. 通过ASM命令行,查看spfile,并赋予别名。

ASMCMD> pwd +new_data/racdb/PARAMETERFILE ASMCMD> ls spfileracdb.ora.267.1085996147 ASMCMD> mkalias +new_data/racdb/PARAMETERFILE/spfileracdb.ora.267.1085996147 +NEW_DATA/racdb/spfileracdb.ora

4. 修改注册新的spfile文件:

$ srvctl modify database -d racdb-p +NEW_DATA/racdb/spfileracdb.ora

5. 重启数据库

$ srvctl stop database -d racdb $ srvctl start database -d racdb

6. 查看结果

SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +NEW_DATA/racdb/spfileracdb.ora

7. 查看参数文件

cat initracdb2.ora SPFILE='+NEW_DATA/racdb/spfileracdb.ora' # line added by Agent

二、控制文件路径修改

1、查看控制文件路径:

SQL>col NAME for a50 SQL> select status, name from v$controlfile; STATUS NAME ------- -------------------------------------------------- +DATA/racdb/control01.ctl +DATA/racdb/control02.ctl 或 SQL> show parameter control_files; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/racdb/control01.ctl, +DATA/racdb/control02.ctl

2、关闭数据库所有实例,启动一个实例到nomount,用rman备份控制文件

SQL>startup nomount; -- =>启库为nomount RMAN> restore controlfile to '+NEW_DATA/racdb/control01.ctl' from '+DATA/racdb/control01.ctl'; Starting restore at 15-OCT-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=667 instance=racdb3 device type=DISK channel ORA_DISK_1: copied control file copy Finished restore at 15-OCT-21 RMAN> restore controlfile to '+NEW_DATA/racdb/control02.ctl' from '+DATA/racdb/control02.ctl'; Starting restore at 15-OCT-21 using channel ORA_DISK_1 channel ORA_DISK_1: copied control file copy Finished restore at 15-OCT-21

3、复制控制文件使用:

ASMCMD> cp +NEW_DATA/racdb/CONTROLFILE/current.267.1086009349 control01.ctl copying +NEW_DATA/racdb/CONTROLFILE/current.267.1086009349 -> +new_data/racdb/control01.ctl ASMCMD> cp +NEW_DATA/racdb/CONTROLFILE/current.262.1086009391 control02.ctl copying +NEW_DATA/racdb/CONTROLFILE/current.262.1086009391 -> +new_data/racdb/control02.ctl

4、修改参数

SQL> alter system set control_files='+new_data/racdb/control01.ctl', '+new_data/racdb/control02.ctl' scope=spfile sid='*'; System altered.

三、数据库表空间迁移

1、表空间迁移:

– 数据库系统表空间

RMAN> backup as copy tablespace SYSTEM format '+NEW_DATA'; RMAN> switch tablespace SYSTEM to copy; RMAN> backup as copy tablespace SYSAUX format '+NEW_DATA'; RMAN> switch tablespace SYSAUX to copy; RMAN> backup as copy tablespace USERS format '+NEW_DATA'; RMAN> switch tablespace USERS to copy;

– 业务用户表空间

RMAN> backup as copy tablespace tworain_DAT format '+NEW_DATA'; RMAN> switch tablespace tworain_DAT to copy;

– undo 表空间:

RMAN> backup as copy tablespace undotbs001 format '+NEW_DATA'; RMAN> switch tablespace undotbs001 to copy;

2、验证:

SQL> select file_name from dba_data_files where tablespace_name ='SYSTEM'; FILE_NAME -------------------------------------------------------------------------------- +NEW_DATA/racdb/datafile/system.261.1086019835 SQL> select file_name from dba_data_files where tablespace_name ='SYSAUX'; FILE_NAME -------------------------------------------------------------------------------- +NEW_DATA/racdb/datafile/sysaux.260.1086019883 SQL> select file_name from dba_data_files where tablespace_name ='USERS'; FILE_NAME -------------------------------------------------------------------------------- +NEW_DATA/racdb/datafile/users.312.1086020021

3、temp 表空间,修改新路径:

SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/racdb/temp01.dbf +DATA/racdb/temp02.dbf

4、 创建新temp 表空间

SQL> create temporary tablespace temp03 tempfile '+NEW_DATA' size 30M autoextend on; Tablespace created. SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/test/temp01.dbf +DATA/test/temp02.dbf +NEW_DATA/test/tempfile/temp03.311.1084007975

5、修改默认表空间:

SQL> alter database default temporary tablespace temp03; Database altered.

6、 删除旧表空间:

SQL> drop tablespace temp including contents and datafiles;

四、redo 文件迁移

1、 创建新redo:

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+new_data') SIZE 500M; ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+new_data') SIZE 500M; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 ('+new_data') SIZE 500M; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 12 ('+new_data') SIZE 500M; ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 21 ('+new_data') SIZE 500M; ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 22 ('+new_data') SIZE 500M; ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 31 ('+new_data') SIZE 500M; ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 32 ('+new_data') SIZE 500M;

2、 查看

select g.GROUP#, g.STATUS, l.MEMBER from v$log g left join v$logfile l on g.GROUP#=l.GROUP# where g.STATUS = 'INACTIVE' and l.MEMBER like '+DATA%' order by 2; GROUP# STATUS MEMBER ---------- ---------------- ---------------------------------------------------------------------- 16 INACTIVE +DATA/racdb/onlinelog/redo16.log 17 INACTIVE +DATA/racdb/onlinelog/redo17.log 18 INACTIVE +DATA/racdb/onlinelog/redo18.log 19 INACTIVE +DATA/racdb/onlinelog/redo19.log 20 INACTIVE +DATA/racdb/onlinelog/redo20.log 21 INACTIVE +DATA/racdb/onlinelog/redo21.log

3、 删除:

alter database drop logfile group 16; alter database drop logfile group 17; alter database drop logfile group 18; alter database drop logfile group 19; alter database drop logfile group 20;

五、数据文件迁移

SQL> select FILE_ID,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='racdb_DAT'; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------------------------------------- 6 +NEW_DATA/racdb/datafile/racdb_dat.265.1083955823 10 +NEW_DATA/racdb/datafile/racdb_dat.262.1083956499 11 +NEW_DATA/racdb/datafile/racdb_dat.261.1083956723 21 +NEW_DATA/racdb/datafile/racdb_dat.267.1083955373 22 +NEW_DATA/racdb/datafile/racdb_dat.301.1083964647 23 +NEW_DATA/racdb/datafile/racdb_dat.268.1083958067 24 +NEW_DATA/racdb/datafile/racdb_dat.269.1083958291

– rman 复制数据文件 :

copy datafile 10 to '+DATA'; copy datafile 11 to '+DATA'; copy datafile 21 to '+DATA';

– 修改控制文件,数据文件路径:

alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.265.1083955823' to '+DATA/racdb/datafile/racdb_dat.334.1085682389'; alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.262.1083956499' to '+DATA/racdb/datafile/racdb_dat.333.1085682705'; alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.261.1083956723' to '+DATA/racdb/datafile/racdb_dat.332.1085682719'; alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.267.1083955373' to '+DATA/racdb/datafile/racdb_dat.296.1085682739';

                         文章推荐

PostgreSQL URL
《课程笔记:PostgreSQL深入浅出》之 初识PostgreSQL(一) https://www.modb.pro/db/475817
《课程笔记:PostgreSQL深入浅出》之 PostgreSQL源码安装(二) https://www.modb.pro/db/475933
《课程笔记:PostgreSQL深入浅出》之初始化PostgreSQL(三) https://www.modb.pro/db/479524
《课程笔记:PostgreSQL深入浅出》之PSQL管理工具-常用(四) https://www.modb.pro/db/479560
《课程笔记:PostgreSQL深入浅出》之PSQL管理工具-高级命令(四) https://www.modb.pro/db/479559
《课程笔记:PostgreSQL深入浅出》之内存与进程(五) https://www.modb.pro/db/489936
《课程笔记:PostgreSQL深入浅出》之外存&永久存储(六) https://www.modb.pro/db/502267
Oracle: URL
《Oracle 自动收集统计信息机制》 https://www.modb.pro/db/403670
《Oracle_索引重建—优化索引碎片》 https://www.modb.pro/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略测试》 https://www.modb.pro/db/414692
《FY_Recover_Data.dbf》 https://www.modb.pro/doc/74682
《Oracle RAC 集群迁移文件操作.pdf》 https://www.modb.pro/doc/72985
《Oracle Date 字段索引使用测试.dbf》 https://www.modb.pro/doc/72521
《Oracle 诊断案例 :因应用死循环导致的CPU过高》 https://www.modb.pro/db/483047
《Oracle 慢SQL监控脚本》 https://www.modb.pro/db/479620
《Oracle 慢SQL监控测试及监控脚本.pdf》 https://www.modb.pro/doc/76068
《Oracle 脚本实现简单的审计功能》 https://www.modb.pro/db/450052
《记录一起索引rebuild与收集统计信息的事故》 https://www.modb.pro/db/408934
Greenplum: URL
《PL/Java.pdf》 https://www.modb.pro/doc/70867
《GP的资源队列.pdf》 https://www.modb.pro/doc/67644
《Greenplum psql客户端免交互执行SQL.pdf》 https://www.modb.pro/doc/69806
                       欢迎赞赏支持或留言指正
最后修改时间:2022-09-27 21:33:39
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
2人已赞赏
Z
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论