Moving Datafile with Physical Dataguard Environment

On Primary :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS

——— —————————— —————- ——————–

ORCL      orcl                           PRIMARY          TO STANDBY

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/

Oldest online log sequence     524

Next log sequence to archive   526

Current log sequence           526

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

—————- ——————–

PRIMARY          TO STANDBY

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      MANUAL

On Standby :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS

——— —————————— —————- ——————–

ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/

Oldest online log sequence     524

Next log sequence to archive   0

Current log sequence           526

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

—————- ——————–

PHYSICAL STANDBY NOT ALLOWED

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED

———- ———

517 YES

518 YES

519 YES

520 YES

521 YES

522 YES

523 YES

524 YES

525 IN-MEMORY

9 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

———————————— ———– ——————————

standby_file_management              string      MANUAL

SQL>

On Primary :

SQL> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;

FILE_NAME

——————————————————————————–

/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example offline;

Tablespace altered.

[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf  /tmp/askm/example01_temp.dbf

[oracle@dgaskmpri01 OPatch]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace example rename datafile ‘/home/oracle/app/oracle/oradata/orcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;

FILE_NAME

——————————————————————————–

/tmp/askm/example01_temp.dbf

SQL>

On Standby :

SQL> select ts#,name from v$tablespace where name=’EXAMPLE’;

TS# NAME

———- ——————————

6 EXAMPLE

SQL> select name from v$datafile where ts#=6;

NAME

——————————————————————————–

/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf

[oracle@dgaskmsby01 askm]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             343935672 bytes

Database Buffers          104857600 bytes

Redo Buffers                6008832 bytes

Database mounted.

SQL> alter database rename file ‘/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;

Database altered.

SQL> select name from v$datafile where ts#=6;

NAME

——————————————————————————–

/tmp/askm/example01_temp.dbf

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

On Primary :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

On Standby :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.