在给表空间添加数据文件时，或者迁移需要，我们经常要做数据方件的移动需求，如果是单个库，就比较好处理，直接将tablespace offline之后在mv数据文件，再online上去即可。但是由于存在DG，我们对DG需要额外的处理一下。下面是测试环境演示操作

一、主库操作

SQL> select DB_UNIQUE_NAME,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
------------------------------ -------------------- ----------------
un_dg18 TO STANDBY PRIMARY

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------
/u01/app/oradata/orcl/users01.dbf
/u01/app/oradata/orcl/undotbs01.dbf
/u01/app/oradata/orcl/sysaux01.dbf
/u01/app/oradata/orcl/system01.dbf
/u01/app/oradata/orcl/example01.dbf
/u01/app/oradata/orcl/users02.dbf
/u01/app/oradata/orcl/users03.dbf

7 rows selected.

-----增加测试数据文件，用于移动测试
SQL> alter tablespace users add datafile '/u01/app/oradata/user04.dbf' size 10M;

Tablespace altered.

二备库操作

SQL> select DB_UNIQUE_NAME,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
------------------------------ -------------------- ----------------
un_dg19 NOT ALLOWED PHYSICAL STANDBY

SQL> select file_name from dba_data_files;

FILE_NAME

/u01/app/oradata/orcl/users01.dbf
/u01/app/oradata/orcl/undotbs01.dbf
/u01/app/oradata/orcl/sysaux01.dbf
/u01/app/oradata/orcl/system01.dbf
/u01/app/oradata/orcl/example01.dbf
/u01/app/oradata/orcl/users02.dbf
/u01/app/oradata/orcl/users03.dbf
/u01/app/oradata/user04.dbf

8 rows selected.

以上可以看出主库添加的数据文件已经同步过来了，因为我们主库的standby_file_management =auto
SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO

二主库先移动数据方件

SQL> alter tablespace users offline;

Tablespace altered.

SQL> host mv /u01/app/oradata/user04.dbf /u01/app/oradata/orcl/user04.dbf

SQL> alter database rename file '/u01/app/oradata/user04.dbf' to '/u01/app/oradata/orcl/user04.dbf';

Database altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL>

三备库移动

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> host mv /u01/app/oradata/user04.dbf /u01/app/oradata/orcl/user04.dbf

SQL> host mv /u01/app/oradata/user04.dbf /u01/app/oradata/orcl/user04.dbf
mv: 无法获取"/u01/app/oradata/user04.dbf" 的文件状态(stat): 没有那个文件或目录

SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database rename file '/u01/app/oradata/user04.dbf' to '/u01/app/oradata/orcl/user04.dbf';

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

四验证主备同步

主库
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

[oracle@primaryone trace]$ tail -f alert_orcl.log
Current log# 1 seq# 46 mem# 0: /u01/app/oradata/orcl/redo01.log
Thread 1 advanced to log sequence 47 (LGWR switch)
Current log# 2 seq# 47 mem# 0: /u01/app/oradata/orcl/redo02.log
Tue Feb 10 15:20:34 2015
Archived Log entry 82 added for thread 1 sequence 46 ID 0x5329e127 dest 1:
Tue Feb 10 15:21:23 2015
Thread 1 advanced to log sequence 48 (LGWR switch)
Current log# 3 seq# 48 mem# 0: /u01/app/oradata/orcl/redo03.log
Tue Feb 10 15:21:23 2015
Archived Log entry 83 added for thread 1 sequence 47 ID 0x5329e127 dest 1:

备库(要稍等几分钟）
[oracle@standbytwo trace]$ tail -f alert_orcl.log
RFS[7]: Selected log 4 for thread 1 sequence 48 dbid 1395237927 branch 867457193
Tue Feb 10 15:23:22 2015
Expanded controlfile section 11 from 28 to 186 records
Requested to grow by 158 records; added 6 blocks of records
Archived Log entry 44 added for thread 1 sequence 48 ID 0x5329e127 dest 1:
Media Recovery Log /u01/app/oracle/archivelogarc481867457193.arc
RFS[7]: Selected log 4 for thread 1 sequence 49 dbid 1395237927 branch 867457193
Media Recovery Waiting for thread 1 sequence 49 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 49 Reading mem 0
Mem# 0: /u01/app/oradata/orcl/stredo01.log

ITPub博客

oracle 11g Data Guard 移动数据文件

一、主库操作

二备库操作

二主库先移动数据方件

三备库移动

四验证主备同步

最新文章

ITPub博客

oracle 11g Data Guard 移动数据文件

一、主库操作

二 备库操作

二 主库先移动数据方件

三 备库移动

四 验证主备同步

最新文章

二备库操作

二主库先移动数据方件

三备库移动

四验证主备同步