OracleDataGuard _ add data files or create tablespaces to the master database

Source: Internet
Author: User

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.


8.3.1 Adding a Datafile or Creating a Tablespace8.3.1 Add a data file or create a tablespace

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.dbf

Verify 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.dbf

######################################## ######################################## ########################### My experiment: Add a new tablespace to the master database, and set the STANDBY_FILE_MANAGEMENT parameter in the slave database to AUTOMaster Database: PROD slave Database: PRODSTD
1. Check whether the standby database STANDBY_FILE_MANAGEMENT parameter is AUTOSYS @ PRODSTD> show parameter STANDBY_FILE_MANAGEMENT
Name type value ------------------------------------ ----------- -------------------------------- standby_file_management string AUTO
2. Add a new tablespace SYS @ PROD> create tablespace SWTICH_TBS datafile '/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf 'size 10 m;
Tablespace created.
Bytes ---------------------------------------------------------------------------------------------------------------
Sat Mar 29 16:27:18 2014 create tablespace SWTICH_TBS datafile '/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf' size 10 mSat Mar 29 16:27:21 2014 Completed: create tablespace SWTICH_TBS datafile '/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf' size 10 m
Bytes ---------------------------------------------------------------------------------------------------------------
3. Manually archive and view the alarm log SYS @ PROD> alter system archive log current;
System altered. warning log of the primary database: LNS1 started with pid = 55, OS id = 1272sat Mar 29 16:29:54 2014 Thread 1 advanced to log sequence 32 Current log #5 seq #32 mem #0: /u01/app/oracle/oradata/PROD/Disk1/redo05.log Current log #5 seq #32 mem #1: /u01/app/oracle/oradata/PROD/Disk2/redo05_1.logSat Mar 29 16:29:56 2014LNS: Standby redo logfile selected for thread 1 sequence 32 for destination LOG_ARCHIVE_DEST_2Sat Mar 29 16:29:58 2014ARC7: standby redo logfile selected for thread 1 sequence 31 for destination LOG_ARCHIVE_DEST_2
Standby Database Alert Log: Sat Mar 29 16:29:55 2014 Redo Shipping Client Connected as PUBLIC -- Connected User is ValidRFS [2]: Assigned to RFS process 8613RFS [2]: identified database type as 'physical standby' Primary database is in maximum performance modePrimary database is in maximum performance modeRFS [2]: Successfully opened standby log 7: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby07.log' Sat Mar 29 16:29:57 2014 Redo Shipping Client Connected as PUBLIC -- Connected User is ValidRFS [3]: assigned to RFS process 8615RFS [3]: Identified database type as 'physical standby' RFS [3]: Successfully opened standby log 6: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby06.log' Created 4, verify whether new tablespace and data files exist in the master database
SYS @ PROD> select name from v $ datafile;
NAME users/u01/app/oracle/oradata/PROD/Disk1/system01.dbf/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf/u01/app/oracle/oradata/PROD /Disk1/sysaux01.dbf/u01/app/oracle/oradata/PROD/Disk1/example01.dbf/u01/app/oracle/oradata/PROD/Disk1/users/u01/app/oracle/oradata /PROD/Disk1/users01.dbf
6 rows selected.
5. check whether there are new tablespaces and data files in the 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.
8.3.1.2 When STANDBY_FILE_MANAGEMENT Is Set to MANUAL8.3.1.2 When STANDBY_FILE_MANAGEMENT Is Set to MANUAL

This 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.
8.3.1.2.1 Using the STANDBY_FILE_MANAGEMENT Parameter with Raw Devices

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...
8.3.1.2.2 Recovering From Errors8.3.1.2.2 restore the preceding Error

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 'a

On 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.

Related Article

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.