Home » Server Options » Data Guard » Delete the datafile from Standby (ORACLE 10g Release 2 )
Delete the datafile from Standby [message #428930] Fri, 30 October 2009 16:23 Go to next message
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 Go to previous messageGo to next message
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 #432857 is a reply to message #432828] Thu, 26 November 2009 15:18 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
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?


Have you configured STANDBY_FILE_MANAGEMENT parameter in your primary database?

http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/initparams206.htm

- Babu
Re: Delete the datafile from Standby [message #432867 is a reply to message #432857] Thu, 26 November 2009 22:47 Go to previous messageGo to next message
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
Re: Delete the datafile from Standby [message #432917 is a reply to message #432867] Fri, 27 November 2009 04:59 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Quote:
In my case i resolved this by creating i new standby controlfile on primary db and copied on standby system.


Bit confused. Why you need to re-create controfile in standby database.

We are discussing about how to drop tablespace in standby database.

- Babu
Previous Topic: Create Logical Standby coexisting with Physical Standby database
Next Topic: Physical Standby, using ASM (!?)
Goto Forum:
  


Current Time: Thu Mar 28 04:37:06 CDT 2024