8.3 Managing Primary Database Events That Affect the Standby Database8.3 Managing the master Database can Affect the Events of the slave Database
To prevent possible problems, you must be aware of events on the primary database that affect a standby database and learn how to respond to them. this section describes these events and the recommended responses to these events.
In some cases, the events or changes that occur on a primary database are automatically propagated through redo data to the standby database and thus require no extra action on the standby database. in other cases, you might need to perform maintenance tasks on the standby database.
Table 8-1 indicates whether or not a change made on the primary database requires additional intervention by the database administrator (DBA) to be propagated to the standby database. it also briefly describes how to respond to these events. detailed descriptions of the responses are described in the section references provided.
The following events are automatically administered by redo transport services and Redo Apply, and therefore require no intervention by the database administrator:
A SQL ALTER DATABASE statement is issued with the ENABLE THREAD or DISABLE THREAD clause.
The status of a tablespace changes (changes to read/write or read-only, placed online or taken offline ).
A datafile is added or tablespace is created when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
Table 8-1 Actions Required on a Standby Database After Changes to a Primary Database
| Reference | Change Made on Primary Database | Action Required on Standby Database |
|---|---|---|
Section 8.3.1 |
Add a datafile or create a tablespace Add a file or create a tablespace |
If you did not set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, you must copy the new datafile to the standby database. If the STANDBY_FILE_MANAGEMENT initialization parameter is not set to AUTO, you must copy the new data file to the slave database. |
Section 8.3.2 |
Drop or delete a tablespace or datafile Delete a tablespace or data file |
Delete datafiles from primary and standby databases after the archived redo log file containing the DROP or DELETE command was applied. DELETE data files from the master database. The backup database will use archive redo logs containing the DROP or DELETE commands to apply them. |
Section 8.3.3 |
Use transportable tablespaces Use transfer tablespace |
Move tablespaces between the primary and standby databases. Move the tablespace between the master and slave Databases |
Section 8.3.4 |
Rename a datafile Rename a data file |
Rename the datafile on the standby database. Recommand the data file in the slave Database |
Section 8.3.5 |
Add or drop redo log files Add or delete redo log files love you |
Synchronize changes on the standby database. Synchronize changes on the slave Database |
Section 8.3.6 |
Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause Use NOLOGGING or UNRECOVERABLE to perform DML or DDL operations, |
Send the datafile containing the unlogged changes to the standby database. Send a file containing no log records to the slave Database |
Chapter 13 |
Change initialization parameters Change initialization parameters |
Dynamically change the standby parameters or shut down the standby database and update the initialization parameter file. Dynamically change the backup parameters or close the backup database, and then update the initialization parameter file. |
The initialization parameter, STANDBY_FILE_MANAGEMENT, enables you to control whether or not adding a datafile to the primary database is automatically propagated to the standby database, as follows:
STANDBY_FILE_MANAGEMENT initializes the parameter file, which can be used to control whether the data file is automatically transmitted to the slave database when it is added to the master database, as follows:
If you set the STANDBY_FILE_MANAGEMENT initialization parameter in the standby database server parameter file (SPFILE) to AUTO, any new datafiles created on the primary database are automatically created on the standby database as well.
If the spfile of the STANDBY_FILE_MANAGEMENT initialization parameter file in the slave database is AUTO, any data files created on the master database will be automatically created in the slave database.
If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database.
If STANDBY_FILE_MANAGEMENT is not set or is set to MANUAL, when you add a data file to the master database, you must manually copy the new data file to the slave database.
Note that if you copy an existing datafile from another database to the primary database, then you must also copy the new datafile to the standby database and re-create the standby control file, regardless of the setting of STANDBY_FILE_MANAGEMENT initialization parameter.
Note: If you copy an existing data file from another database to the master database, you must also copy it to the inverted database and recreate the backup control file, unless you have set the STANDBY_FILE_MANAGEMENT initialization parameter.
The following sections provide examples of adding a datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENTinitialization parameter is set to AUTO and MANUAL, respectively.
Add a data file to the master database as follows. STANDBY_FILE_MANAGEMENT of the slave database is set to AUTO and MANUAL respectively.
8.3.1.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO8.3.1.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO,The following example shows the steps required to add a new datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENTinitialization parameter is set to AUTO.
The following example shows how to add a data file to the master database and set STANDBY_FILE_MANAGEMENT to AUTO in the slave database.
Add a new tablespace to the primary database:
1. Add a new tablespace to the master database:
SQL> CREATE TABLESPACE new_ts DATAFILE '/disk1/oracle/oradata/payroll/t_db2.dbf' 2> SIZE 1 m AUTOEXTEND ON MAXSIZE UNLIMITED;Archive the current online redo log file so the redo data will be transmitted to and applied on the standby database:
Archive the current redo log. In this way, the redo log will be transmitted to the slave database, and the slave database will apply the transferred logs:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;Verify the new datafile was added to the primary database:
3. Verify that the new data file is added to the master database:
SQL> SELECT NAME FROM V $ DATAFILE; NAME partition/disk1/oracle/oradata/payroll/t_db1.dbf/disk1/oracle/oradata/payroll/t_db2.dbfVerify the new datafile was added to the standby database:
The data file of the verification letter is added to the slave database:
SQL> SELECT NAME FROM V $ DATAFILE; NAME partition/disk1/oracle/oradata/payroll/s2t_db1.dbf/disk1/oracle/oradata/payroll/s2t_db2.dbfThis section shows how to add a new datafile to the primary and standby database when the STANDBY_FILE_MANAGEMENT initialization parameter is set to MANUAL. you must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL when the standby datafiles reside on raw devices. this section also describes how to recover from errors after they have occurred.
This section shows how to add a new data file to the master database. The STANDBY_FILE_MANAGEMENT parameter of the slave database is set to MAUNAL. When the data file of the slave database is placed on a bare device, you must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL. This section also describes how to restore the errors encountered.
Note:
Do not use the following procedure with databases that use Oracle Managed Files. also, if the raw device path names are not the same on the primary and standby servers, use the DB_FILE_NAME_CONVERT initialization parameter to convert the path names. do not use the following steps for database OMF management. Similarly, if the path of the bare device is not the same as that of the master database and the slave database, use the DB_FILE_NAME_CONVERT initialization parameter to convert the path name.By setting the STANDBY_FILE_MANAGEMENT parameter to AUTO whenever new datafiles are added or dropped on the primary database, corresponding changes are made in the standby database without manual intervention. this is true as long as the standby database is using a file system. if the standby database is using raw devices for datafiles, then the STANDBY_FILE_MANAGEMENT initialization parameter will continue to work, but manual intervention is needed. this manual intervention involves ensuring the raw devices exist before log apply services on the standby database recover the redo data that will create the new datafile. on the primary database, create a new tablespace where the datafiles reside in a raw device. at the same time, create the same raw device on the standby database. for example:
STANDBY_FILE_MANAGEMENT is set to AUTO. No matter when new data files are added or deleted in the master database, the slave database can occur in the slave database without manual intervention. This is for the file system of the slave database. If the slave database uses a bare device to store data files, the STANDBY_FILE_MANAGEMENT parameter will continue to work, but manual intervention is required. This manual intervention includes determining whether the raw device exists on the slave database. log application service restores the redo logs for creating new data files. On the master database, create a new tablespace on the bare device and create the same bare device in the slave database. For example:
SQL> create tablespace MTS2 DATAFILE '/dev/raw/raw100' size 1 m; Tablespace created. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.The standby database automatically adds the datafile as the raw devices exist. The standby alert log shows the following:
The slave database automatically adds data files to an existing bare device. The backup alert date is as follows:
Fri Apr 8 09:49:31 2005 Media Recovery Log/u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o%mf_%7_15ffgt0z _. arcRecovery created file/dev/raw/raw100Successfully added datafile 6 to media recoveryDatafile #6: '/dev/raw/raw100' Media Recovery Waiting for thread 1 sequence 8 (in transit)However, if the raw device was created on the primary system but not on the standby, then the MRP process will shut down due to file-creation errors. for example, issue the following statements on the primary database:
However, if the raw device is created on the master database but the raw device is not created on the slave database, the MRP process will be closed due to a file creation error. For example, the following statement is issued on the master database:
SQL> create tablespace MTS3 DATAFILE '/dev/raw/raw101' size 1 m; Tablespace created. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.The standby system does not have the/Dave/raw/raw101 raw device created. The standby alert log shows the following messages when recovering the archive:
The raw device/dev/raw/raw101 is not created in the backup system. The backup alarm log is as follows:
Fri Apr 8 10:00:22 2005 Media Recovery Log/u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o%mf_%8_15ffjrov _. arcFile #7 added to control file as 'unnamed00007 '. originally created as: '/dev/raw/raw101' Recovery was unable to create the file as: '/dev/raw/raw101' MRP0: background Media Recovery terminated with error 1274Fri Apr 8 10:00:22 2005 Errors in file/u01/MILLER/MTS/dump/mts_mrp0_21851.trc: ORA-01274: cannot add datafile '/dev/raw/raw101'-file cocould not be createdORA-01119: error in creating database file'/dev/raw/raw101 'ora-27041: unable to open fileLinux Error: 13: Permission deniedAdditional information: 1 Some recovered datafiles maybe left media fuzzyMedia recovery may continue but open resetlogs may failFri Apr 8 10:00:22 2005 Errors in file/u01/MILLER/MTS/dump/mts_mrp0_21851.trc: ORA-01274: cannot add datafile '/dev/raw/raw101'-file cocould not be createdORA-01119: error in creating database file '/dev/raw/raw101' ORA-27041: unable to open fileLinux Error: 13: Permission deniedAdditional information: 1Fri Apr 8 10:00:22 2005MTS; MRP0: background Media Recovery process shutdownARCH: Connecting to console port...To correct the problems described in Section 8.3.1.2.1, perform the following steps:
To modify the error mentioned in the previous section, perform the following steps:
Create the raw device on the standby database and assign permissions to the Oracle user.
Create a bare device on the slave database and grant the oracle user permission
Query the V $ DATAFILE view. For example:
SQL> SELECT NAME FROM V $ DATAFILE; NAME extensions/u01/MILLER/MTS/system01.dbf/u01/MILLER/MTS/undotbs01.dbf/u01/MILLER/MTS/sysaux01.dbf/u01/MILLER/MTS/users01.dbf/u01/MILLER/MTS /mts. dbf/dev/raw/raw100/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007 SQL> Alter system set STANDBY_FILE_MANAGEMENT = MANUAL;SQL> ALTER DATABASE CREATE DATAFILE 2'/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007' 3 4'/dev/raw/raw101 ';In the standby alert log you shoshould see information similar to the following:
You should see the following similar information in the backup alarm log:
Fri Apr 8 10:09:30 2005 alter database create datafile '/dev/raw/raw101 'as'/dev/raw/raw101 'fri Apr 8 10:09:30 2005 Completed: alter database create datafile '/dev/raw/raw101 'aOn the standby database, set STANDBY_FILE_MANAGEMENT to AUTO and restart Redo Apply:
In the slave database, set STANDBY_FILE_MANAGEMENT to AUTO and re-enable the redo application:
SQL> alter system set STANDBY_FILE_MANAGEMENT = AUTO; SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;At this point Redo Apply uses the new raw device datafile and recovery continues.
At this point, redo the application to use the data files on the new bare device and continue the restoration.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
