| Delete the datafile from Standby [message #428930] |
Fri, 30 October 2009 16:23  |
stonevishcool
Messages: 29
Registered: June 2008
Location: Mumbai
|
Junior Member |
|
|
Hi,
On production i ran "alter database datafile...offline drop" command to drop the datafile from tablespace. But didn't worked. then i again made the same datafile online by performing media recovery and dropped the datafile using "alter tablespace drop datafile" command.
But on standby system it is still showing in v$datafile table. The standby system is properly sync with production.
Can you please suggest how can i drop the same from standby system?
Regards,
Vish
|
|
|
|
| Re: Delete the datafile from Standby [message #432828 is a reply to message #428930] |
Thu, 26 November 2009 08:18  |
Sahba1969
Messages: 14
Registered: March 2009
Location: Austria
|
Junior Member |
|
|
hi,
the folowing extract ist from Oracle doc, assuming you have oracle 10gR2 or 11g:
Dropping Tablespaces and Deleting Datafiles
When a tablespace is dropped or a datafile is deleted from a primary database, the corresponding datafile(s) must be deleted from the physical standby database. The following example shows how to drop a tablespace:
SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
To verify that deleted datafiles are no longer part of the database, query the V$DATAFILE view.
Delete the corresponding datafile on the standby system after the redo data that contains the previous changes is applied to the standby database. For example:
% rm /disk1/oracle/oradata/payroll/s2tbs_4.dbf
On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:
% rm /disk1/oracle/oradata/payroll/tbs_4.dbf
Using DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the datafiles on both the primary and standby databases. To use this statement, the STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO. For example, to drop the tablespace at the primary site:
SQL> DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
regards
|
|
|
|
|
|
| Re: Delete the datafile from Standby [message #432867 is a reply to message #432857] |
Thu, 26 November 2009 22:47 |
stonevishcool
Messages: 29
Registered: June 2008
Location: Mumbai
|
Junior Member |
|
|
Quote:But on standby system it is still showing in v$datafile table. The standby system is properly sync with production.
In my case i resolved this by creating i new standby controlfile on primary db and copied on standby system.
i am not sure whether it is proper way to rectify the problem but it worked.
Regards,
Vish
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
