On Primary :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
PRIMARY primary PRIMARY SESSIONS ACTIVE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /syed/arch
Oldest online log sequence 219
Next log sequence to archive 221
Current log sequence 221
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
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
--------- ------------------------------ ---------------- --------------------
PRIMARY standby PHYSICAL STANDBY NOT ALLOWED
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /primary/arch
Oldest online log sequence 213
Next log sequence to archive 0
Current log sequence 221
SQL>
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
---------- ---------
206 YES
207 YES
208 YES
209 YES
210 YES
211 YES
212 YES
213 YES
214 YES
215 YES
216 YES
SEQUENCE# APPLIED
---------- ---------
217 YES
218 YES
219 YES
220 YES
15 rows selected.
9 rows selected.
SQL> sho parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL>
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>
2. Verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.
SQL> select file_name from dba_data_files where tablespace_name='EXAMPLE';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/primary/example01.dbf
SQL> alter tablespace example offline;
Tablespace altered.
3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.
[oracle@dgaskmpri01 OPatch]$ mv /u01/app/oracle/primary/example01.dbf /tmp/syed/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 '/u01/app/oracle/primary/example01.dbf' to '/tmp/syed/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/syed/example01_temp.dbf
SQL>
4. Verify the same tablespace on standby database.
SQL> select ts#,name from v$tablespace where name='EXAMPLE';
TS# NAME
---------- ------------------------------
6 EXAMPLE
SQL> select name from v$datafile where ts#=6;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/standby/example01.dbf
SQL>
5. Stop recovery on standby database and shut it down.
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>
6. Rename the datafile on standby database.
[oracle@dgaskmsby01 askm]$ mv /u01/app/oracle/standby/example01.dbf /tmp/syed/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 '/u01/app/oracle/standby/example01.dbf' to '/tmp/syed/example01_temp.dbf';
Database altered.
SQL> select name from v$datafile where ts#=6;
NAME
--------------------------------------------------------------------------------
/tmp/syed/example01_temp.dbf
7. Keep standby database in recovery mode.
SQL> SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
8. Set initialization parameter STANDBY_FILE_MANAGEMENT value back to AUTO.
On Primary :
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
On Standby :
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
Reference :
Renaming a Datafile in the Primary Database
MOS ID : 733796.1
Hope It Helps
SRI
No comments:
Post a Comment