Oracle11g Dataguard环境下备库迁移数据文件
Oracle11g dataguard环境下备库迁移数据目录
有时候,因为空间容量问题或更换磁盘等原因。我们需要把备库的数据文件迁移到新的目录和磁盘。下面是操作的实时过程。
这次迁移的文件主要包括controller_file和datafile。
###数据迁移部分###
首先在备库上取消备库应用日志
SQL> alter database recover managed standby database cancel;

查看相关表空间、数据文件、控制文件信息

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
——————————————————————————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
ORATEST

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————————————————————————————————————————————–
/data/oracle/oratest/system01.dbf
/data/oracle/oratest/sysaux01.dbf
/data/oracle/oratest/undotbs1.dbf
/data/oracle/oratest/oratest.dbf

SQL> show parameters control_file

NAME TYPE VALUE
———————————— ——————————— ——————————
control_file_record_keep_time integer 7
control_files string /data/oracle/oratest/control01
.ctl, /data/oracle/oratest/con
trol02.ctl, /data/oracle/orate
st/control03.ctl
control_management_pack_access string DIAGNOSTIC+TUNING

关闭掉该数据库
SQL> shutdown immediate;

手动拷贝数据文件到新的位置上
[oracle@db-9-36 dbs]$ cd /data/oracle/oratest/
[oracle@db-9-36 oratest]$ ll
total 5461020
-rw-r—– 1 oracle oinstall 20955136 May 22 18:01 control01.ctl
-rw-r—– 1 oracle oinstall 20955136 May 22 18:01 control02.ctl
-rw-r—– 1 oracle oinstall 20955136 May 22 18:01 control03.ctl
-rw-r—– 1 oracle oinstall 1073750016 May 22 17:55 oratest.dbf
-rw-r—– 1 oracle oinstall 524296192 May 22 17:59 sysaux01.dbf
-rw-r—– 1 oracle oinstall 2147491840 May 22 17:59 system01.dbf
-rw-r—– 1 oracle oinstall 524296192 May 22 16:43 temp01.dbf
-rw-r—– 1 oracle oinstall 209723392 May 22 17:59 undotbs1.dbf

[oracle@db-9-36 oracle]$ cp -rf oratest ssd/

创建pfile文件用于手动编辑,编辑pfile文件手动调整控制文件到新的位置
SQL> create pfile from spfile;
[oracle@db-9-36 dbs]$ vim initoratest.ora
*.control_files=’/data/oracle/ssd/oratest/control01.ctl’,’/data/oracle/ssd/oratest/control02.ctl’,’/data/oracle/ssd/oratest/control03.ctl’

启动数据库到nomount 查看控制文件,可以看到已经使用移动后的控制文件了
SQL> create spfile from pfile;
SQL> startup nomount
SQL> show parameter control_file

NAME TYPE VALUE
———————————— ——————————— ——————————
control_file_record_keep_time integer 7
control_files string /data/oracle/ssd/oratest/contr
ol01.ctl, /data/oracle/ssd/ora
test/control02.ctl, /data/orac
le/ssd/oratest/control03.ctl

启动数据库到mount状态
SQL> alter database mount;

在线rename数据文件和临时文件
SQL> select name from v$datafile;

NAME
—————————————————-
/data/oracle/oratest/system01.dbf
/data/oracle/oratest/sysaux01.dbf
/data/oracle/oratest/undotbs1.dbf
/data/oracle/oratest/oratest.dbf

SQL> alter database rename file ‘/data/oracle/oratest/system01.dbf’ to ‘/data/oracle/ssd/oratest/system01.dbf’;
SQL> alter database rename file ‘/data/oracle/oratest/sysaux01.dbf’ to ‘/data/oracle/ssd/oratest/sysaux01.dbf’;
SQL> alter database rename file ‘/data/oracle/oratest/undotbs1.dbf’ to ‘/data/oracle/ssd/oratest/undotbs1.dbf’;
SQL> alter database rename file ‘/data/oracle/oratest/oratest.dbf’ to ‘/data/oracle/ssd/oratest/oratest.dbf’;

SQL> select name from v$tempfile;
SQL> alter database rename file ‘/data/oracle/oratest/temp01.dbf’ to ‘/data/oracle/ssd/oratest/temp01.dbf’;

上述rename执行提示如下错误,原因是standby_file_management 在auto模式下备库是无法进行rename的:
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

解决方式,将standby_file_management 设置为manual后再次执行即可:
SQL> alter system set standby_file_management = manual;

重新完成后将standby_file_management 重新修改为auto
SQL> alter system set standby_file_management=auto scope=both;

重建完后就可以打开数据库了
SQL> alter database open;
SQL> select open_mode from v$database;

OPEN_MODE
————————————————————
READ ONLY

SQL>
SQL> alter system set standby_file_management=auto scope=both;

应用日志进行同步
SQL> alter database recover managed standby database using current logfile disconnect;

###调整同步,配置数据文件路径自动转换### 

上面的重建已经完成,但是我们在主库添加新数据文件后,备库会出现一些问题
首先在主库添加数据库
SQL> alter tablespace ORATEST add datafile ‘/data/oracle/oratest/oratest_02.dbf’ size 500M;

备库alert日志提示仍然创建到了原来的目前,这是因为主备库数据库文件位置已经不一样了,但是我们没有配置路径转换
Recovery created file /data/oracle/oratest/oratest_02.dbf
Successfully added datafile 5 to media recovery
Datafile #5: ‘/data/oracle/oratest/oratest_02.dbf’

这里我们再次手工迁移下这个数据文件,步骤简洁概括如下:
SQL> create pfile from spfile;
SQL> shtudown immediate;
$ mv /data/oracle/oratest/oratest_02.dbf /data/oracle/ssd/oratest/ 

SQL> create spfile from pfile;
SQL> startup mount
SQL> alter system set standby_file_management=manual scope=both;
SQL> alter database rename file ‘/data/oracle/oratest/oratest_02.dbf’ to ‘/data/oracle/ssd/oratest/oratest_02.dbf’;
SQL> alter system set standby_file_management=auto scope=both; 

修改参数文件配置数据文件路径转换
pfile文件里加入下面的内容
*.db_file_name_convert=’/data/oracle/oratest/’,’/data/oracle/ssd/oratest/’

重建spfile并启动数据库
SQL> create spfile from pfile;
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect;

主库上我们创建2个数据文件,并重新激活日志传输状态:
SQL> alter tablespace ORATEST add datafile ‘/data/oracle/oratest/oratest_03.dbf’ size 500M;
SQL> alter tablespace ORATEST add datafile ‘/data/oracle/oratest/oratest_04.dbf’ size 500M;
SQL> alter system set log_archive_dest_state_2=defer;
SQL> alter system set log_archive_dest_state_2=enable;

查询备库,创建的数据文件都一致了。现在一起都正常了。

SQL> select file_name from dba_data_files;
————————————————————-
/data/oracle/ssd/oratest/system01.dbf
/data/oracle/ssd/oratest/sysaux01.dbf
/data/oracle/ssd/oratest/undotbs1.dbf
/data/oracle/ssd/oratest/oratest.dbf
/data/oracle/ssd/oratest/oratest_02.dbf
/data/oracle/ssd/oratest/oratest_03.dbf
/data/oracle/ssd/oratest/oratest_04.dbf

共有 0 条评论

Top