8.3.2 Dropping Tablespaces and Deleting Datafiles8.3.2 Delete Tablespaces and data files
When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles to the standby database. the following sections provide examples of dropping tablespaces and deleting datafiles when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO or MANUAL.
When you delete one or more data files in the master database or delete one or more tablespaces, you also need to delete the relevant data files in the slave database. The example provided in the following section is as follows, when the STANDBY_FILE_MANAGEMENT parameter is set to AUTO or MANUAL, you can delete tablespaces and data files.
8.3.2.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL8.3.2.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL
The following procedure works whether the STANDBY_FILE_MANAGEMENT initialization parameter is set to either MANUAL or AUTO, as follows:
The following steps can work regardless of whether the STANDBY_FILE_MANAGEMENT parameter is set to MANUAL or AUTO:
Drop the tablespace from the primary database:
1. delete a tablespace from the master database
SQL> drop tablespace tbs_4; SQL> ALTER SYSTEM SWITCH LOGFILE;
Make sure that Redo Apply is running (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, Redo Apply is running.
Make sure that the redo application is running, so that the slave database will apply these changes. If the MRP or MRP0 process returned by the following query, the redo application is running.
SQL> SELECT PROCESS, STATUS FROM V $ MANAGED_STANDBY;
To verify that deleted datafiles are no longer part of the database, query the V $ DATAFILE view.
Verify that the deleted data file no longer exists in the Database. You can query the V $ DATAFILE view.
Delete the corresponding datafile on the standby system after the archived redo log file was applied to the standby database. For example:
After the archived redo log is applied to the standby database, delete the corresponding data files in 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:
In the primary database, after the standby database application deletes the tablespace redo information, you can remove data files from the tablespace. For example:
% Rm/disk1/oracle/oradata/payroll/tbs_4.dbf
8.3.2.2 Using drop tablespace including contents and DATAFILES8.3.2.2 use 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:
You can use drop tablespace including contents and datafiles on the master database to delete the data files of the master AND slave databases, provided that STANDBY_FILE_MANAGEMENT must be set to AUTO. For example, you can delete a TABLESPACE on the master database:
SQL> drop tablespace including contents and datafiles tbs_4; SQL> ALTER SYSTEM SWITCH LOGFILE; ######################################## ######################################## ###########################
My experiment: the master database uses drop tablespace including contents and datafiles to delete tablespaces. The STANDBY_FILE_MANAGEMENT parameter in the slave database is set to AUTO
Master Database: PROD
Slave Database: PRODSTD
1. First check whether the STANDBY_FILE_MANAGEMENT parameter is set to AUTOSYS @ PRODSTD> show parameter STANDBY_FILE_MANAGEMENT
Name type value ------------------------------------ ----------- -------------------------------- standby_file_management string AUTO
2. view the tablespace on both sides and the data file master database: SYS @ PROD> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME guest/u01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1/u01/app/oracle /oradata/PROD/Disk1/SYSAUX/u01/app/oracle/oradata/PROD/Disk1/example01.dbf EXAMPLE/u01/app/oracle/oradata/PROD/Disk1/users01.dbf USERS/ u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.db SWTICH_TBSf
6 rows selected.
Slave Database: SYS @ PRODSTD> select name from v $ datafile;
NAME users/u01/app/oracle/oradata/PRODSTD/Disk1/system01.dbf/u01/app/oracle/oradata/PRODSTD/Disk1/undotbs01.dbf/u01/app/oracle/oradata/PRODSTD /Disk1/sysaux01.dbf/u01/app/oracle/oradata/PRODSTD/Disk1/example01.dbf/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/partition _. dbf
/U01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf
6 rows selected.
3. Delete the tablespace and data file SYS @ PROD> drop tablespace SWTICH_TBS including contents and datafiles in the master database;
Tablespace dropped.
Warning log of the primary database: Sun Mar 30 10:47:37 2014 drop tablespace SWTICH_TBS including contents and datafilesSun Mar 30 10:47:42 2014 Deleted file/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbfCompleted: drop tablespace SWTICH_TBS including contents and datafiles created 4. view the tablespace and data file master database on both sides again: SYS @ PROD> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME guest/u01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1/u01/app/oracle /oradata/PROD/Disk1/SYSAUX/u01/app/oracle/oradata/PROD/Disk1/example01.dbf EXAMPLE/u01/app/oracle/oradata/PROD/Disk1/users01.dbf USERS
Slave Database: SYS @ PRODSTD> select name from v $ datafile;
NAME users/u01/app/oracle/oradata/PRODSTD/Disk1/system01.dbf/u01/app/oracle/oradata/PRODSTD/Disk1/undotbs01.dbf/u01/app/oracle/oradata/PRODSTD /Disk1/sysaux01.dbf/u01/app/oracle/oradata/PRODSTD/Disk1/example01.dbf/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/partition _. dbf/u01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf
6 rows selected. At this time, the archived redo log of the master database has not been uploaded to the slave database. 5. manually switch logs, synchronize the master and slave databases, and redo the logs for the slave database applications. SYS @ PROD> alter system switch logfile;
System altered. warning log of the primary database: LNS1 started with pid = 57, OS id = 3000Sun Mar 30 10:58:08 2014 Thread 1 advanced to log sequence 34 Current log #2 seq #34 mem #0: /u01/app/oracle/oradata/PROD/Disk1/redo02.log Current log #2 seq #34 mem #1: /u01/app/oracle/oradata/PROD/Disk2/redo02_1.logSun Mar 30 10:58:10 2014LNS: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2Sun Mar 30 10:58:11 2014ARC5: standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
Slave database alarm log:
Sun Mar 30 10:58:11 2014 Redo Shipping Client Connected as PUBLIC -- Connected User is ValidRFS [3]: Assigned to RFS process 2939RFS [3]: identified database type as 'physical standby' Sun Mar 30 10:58:11 2014 Redo Shipping Client Connected as PUBLIC -- Connected User is ValidRFS [4]: Assigned to RFS process 2941RFS [4]: identified database type as 'physical standby' Primary database is in maximum performance modePrimary database is in maximum performance modeRFS [4]: Successfully opened standby log 6: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby06.log' Sun Mar 30 10:58:12 44rfs [3]: Successfully opened standby log 7: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby07.log' Sun Mar 30 10:58:17 2014 Media Recovery Log/u01/app/oracle/oradata/PRODSTD/Disk2/arch/g033_81_21_1.arcrecovery deleting file #5: '/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/o1_mf_swtich_t_9mh1rzf6 _. dbf' from controlfile. deleted Oracle managed file/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/o1_mf_swtich_t_9mh1rzf6 _. dbfRecovery dropped tablespace 'swtich _ TBS 'Media Recovery Waiting for thread 1 sequence 34 (in transit) limit 6. view the data file SYS @ PRODSTD> select file #, name from v $ datafile;
FILE # NAME ---------- lifecycle 1/u01/app/oracle/oradata/PRODSTD/Disk1/system01.dbf 2/u01/app/oracle/oradata/PRODSTD/Disk1/undotbs01.dbf 3/u01/app /oracle/oradata/PRODSTD/Disk1/sysaux01.dbf 4/u01/app/oracle/oradata/PRODSTD/Disk1/example01.dbf 7/u01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf
The SWITCH_TBS tablespace of the standby database has been deleted. ######################################## ######################################## ###########################
