Moving a Datafile in primary
Moving Datafile with Physical Dataguard Environment
On Primary :
Primary> 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
Primary> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
—————- ——————–
PRIMARY TO STANDBY
Primary> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string AUTO
Primary> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
Primary> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string MANUAL
On Standby :
Standby> 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
Standby> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
—————- ——————–
PHYSICAL STANDBY NOT ALLOWED
Standby> 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.
Standby> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string AUTO
Standby> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
Standby>sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string MANUAL
On Primary :
Primary> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;
FILE_NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/orcl/example01.dbf
Primary> 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
Primary> alter tablespace example rename datafile ‘/home/oracle/app/oracle/oradata/orcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;
Tablespace altered.
Primary> alter tablespace example online;
Tablespace altered.
Primary> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;
FILE_NAME
——————————————————————————–
/tmp/askm/example01_temp.dbf
On Standby :
Standby> select ts#,name from v$tablespace where name=’EXAMPLE’;
TS# NAME
———- —————————--
6 EXAMPLE
Standby> select name from v$datafile where ts#=6;
NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf
Standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
[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.
Standby> alter database rename file ‘/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;
Database altered.
Standby> select name from v$datafile where ts#=6;
NAME
——————————————————————————–
/tmp/askm/example01_temp.dbf
Standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
On Primary :
Primary> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
On Standby :
Standby > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
On Primary :
Primary> 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
Primary> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
—————- ——————–
PRIMARY TO STANDBY
Primary> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string AUTO
Primary> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
Primary> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string MANUAL
On Standby :
Standby> 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
Standby> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
—————- ——————–
PHYSICAL STANDBY NOT ALLOWED
Standby> 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.
Standby> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string AUTO
Standby> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
Standby>sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
———————————— ———– —————————--
standby_file_management string MANUAL
On Primary :
Primary> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;
FILE_NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/orcl/example01.dbf
Primary> 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
Primary> alter tablespace example rename datafile ‘/home/oracle/app/oracle/oradata/orcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;
Tablespace altered.
Primary> alter tablespace example online;
Tablespace altered.
Primary> select file_name from dba_data_files where tablespace_name=’EXAMPLE’;
FILE_NAME
——————————————————————————–
/tmp/askm/example01_temp.dbf
On Standby :
Standby> select ts#,name from v$tablespace where name=’EXAMPLE’;
TS# NAME
———- —————————--
6 EXAMPLE
Standby> select name from v$datafile where ts#=6;
NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf
Standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
[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.
Standby> alter database rename file ‘/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf’;
Database altered.
Standby> select name from v$datafile where ts#=6;
NAME
——————————————————————————–
/tmp/askm/example01_temp.dbf
Standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
On Primary :
Primary> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
On Standby :
Standby > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.