OracleDataGuard _ the primary database deletes the tablespace.

Source: Internet
Author: User
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 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:

8.3.2Dropping Tablespaces and Deleting Datafiles

8.3.2 Delete tablespaces and data files

When you delete one or moredatafiles 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 theSTANDBY_FILE_MANAGEMENTinitialization parameter is set toAUTOorMANUAL.

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 following section provides an example:

8.3.2.1When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL

8.3.2.1 when STANDBY_FILE_MANAGEMENT is set to AUTO or MANUAL

The following procedure works whether theSTANDBY_FILE_MANAGEMENTinitialization parameter is set to eitherMANUALorAUTO, 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 theV $ DATAFILEview.

    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.2Using DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES

    8.3.2.2 use DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES

    You can issue the sqldrop tablespace including contents and DATAFILESstatement on the primary database to delete the datafiles on both the primary and standby databases. to use this statement, theSTANDBY_FILE_MANAGEMENTinitialization parameter must be set toAUTO. 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 AUTO

    SYS @ PRODSTD> show parameter STANDBY_FILE_MANAGEMENT

    NAME TYPE VALUE

    -----------------------------------------------------------------------------

    Standby_file_management string AUTO

    2. View tablespaces and data files on both sides

    Master database:

    SYS @ PROD> select file_name, tablespace_name from dba_data_files;

    FILE_NAME TABLESPACE_NAME

    --------------------------------------------------------------------------------

    /U01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM

    /U01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1

    /U01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf 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_TBS

    F

    6 rows selected.

    Slave database:

    SYS @ PRODSTD> select name from v $ datafile;

    NAME

    --------------------------------------------------------------------------------

    /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/o1_mf_swtich_t_9m21f1f0 _.

    Dbf

    /U01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf

    6 rows selected.

    3. Delete the tablespace and data files in the master database.

    SYS @ PROD> drop tablespace SWTICH_TBS including contents and datafiles;

    Tablespace dropped.

    Bytes ------------------------------------------------------------------------------------------

    Master database alarm log:

    Sun Mar 30 10:47:37 2014

    Drop tablespace SWTICH_TBS including contents and datafiles

    Sun Mar 30 10:47:42 2014

    Deleted file/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf

    Completed: drop tablespace SWTICH_TBS including contents and datafiles

    Bytes ------------------------------------------------------------------------------------------

    4. view the tablespace and data files on both sides again

    Master database:

    SYS @ PROD> select file_name, tablespace_name from dba_data_files;

    FILE_NAME TABLESPACE_NAME

    --------------------------------------------------------------------------------

    /U01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM

    /U01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1

    /U01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf 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

    --------------------------------------------------------------------------------

    /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/o1_mf_swtich_t_9m21f1f0 _. dbf

    /U01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf

    6 rows selected.

    At this time, the archive 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.

    Bytes -------------------------------------------------------------------------------------------

    Master database alarm log:

    LNS1 started with pid = 57, OS id = 3000

    Sun 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.log

    Sun Mar 30 10:58:10 2014

    LNS: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2

    Sun Mar 30 10:58:11 2014

    ARC5: 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 Valid

    RFS [3]: Assigned to RFS process 2939

    RFS [3]: Identified database type as 'physical standby'

    Sun Mar 30 10:58:11 2014

    Redo Shipping Client Connected as PUBLIC

    -- Connected User is Valid

    RFS [4]: Assigned to RFS process 2941

    RFS [4]: Identified database type as 'physical standby'

    Primary database is in maximum performance mode

    Primary database is in maximum performance mode

    RFS [4]: Successfully opened standby log 6: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby06.log'

    Sun Mar 30 10:58:12 2014

    RFS [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/1_33_8%2%1.arc

    Recovery ing 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 _. dbf

    Recovery dropped tablespace 'swtich _ TBS'

    Media Recovery Waiting for thread 1 sequence 34 (in transit)

    Bytes -----------------------------------------------------------------------------------------------------------

    6. View backup database data files

    SYS @ PRODSTD> select file #, name from v $ datafile;

    FILE # NAME

    -----------------------------------------------------------------

    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.

    References:

    Important configuration parameters of Oracle Data Guard

    Configure Oracle 11g Data Guard based on the same host

    Explore Oracle 11g elastic uard

    Oracle Data Guard (RAC + DG) archive deletion policies and scripts

    Role conversion for Oracle Data Guard

    FAL gap in Oracle Data Guard logs

    Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.