Primary Database Changes That Require Manual Intervention at a Physical Standby
Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database,
but there are some changes that require manual intervention.
Case 1=> When STANDBY_FILE_MANAGEMENT=Manual & UNNAMED Datafile issue on Standby Side.
The STANDBY_FILE_MANAGEMENT database initialization parameter controls whether the addition of a data file to the primary database is automatically propagated to a physical standby databases.
=> If the STANDBY_FILE_MANAGEMENT set to AUTO on the physical standby database ,
any new data files created on the primary database are automatically created on the physical standby database.
=> If the STANDBY_FILE_MANAGEMENT set to MANUAL on the physical standby database,
any new data file must be manually copied from the primary database to the physical standby databases after it is added to the primary database.
On a physical standby for which the Oracle Active Data Guard option has been enabled, you cannot use the manual copy method.
Instead, you must execute the following SQL statement on the standby to create an empty data file:
SQL> ALTER DATABASE CREATE DATAFILE [filename | filenumber] AS [NEW | new_filename];
You must specify which one to rename: the filename or the filenumber.
Also specify either the new filename or NEW. The NEW keyword lets Oracle automatically choose a name, if Oracle Managed Files (OMF) is enabled.
If an existing data file from another database is copied to a primary database, it must also be copied to the standby database and the standby control file must be re-created,
regardless of the setting of STANDBY_FILE_MANAGEMENT parameter.
Demonstration
Let we test the sam as below
On Standby Side
QL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> @name
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> @sync
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 225 225 0
2 162 162 0
On Primary Side
SQL> create tablespace manual_add datafile '+DATA' size 100M;
Tablespace created.
SQL> alter system switch all logfile;
SQL> select NAME from v$datafile;
NAME
----------------------------------------------
+DATA/DBSET/DATAFILE/system.257.1107118267
+DATA/DBSET/DATAFILE/test_ts.278.1109780335
+DATA/DBSET/DATAFILE/sysaux.258.1107118303
+DATA/DBSET/DATAFILE/undotbs1.259.1107118317
+DATA/DBSET/DATAFILE/undotbs2.265.1107118599
+DATA/DBSET/DATAFILE/users.260.1107118319
+DATA/DBSET/DATAFILE/manual_add.279.1109784107
Check Standby Side for Alert log and MRP process.
Alert Log
File #8 added to control file as 'UNNAMED00008' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
PR00 (PID:3588): MRP0: Background Media Recovery terminated with error 1274
2022-07-11T17:21:53.931110+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/dbtar/DBTAR1/trace/DBTAR1_pr00_3588.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/DBSET/DATAFILE/manual_add.279.1109784107'
2022-07-11T17:21:53.932698+05:30
.... (PID:28180): Managed Standby Recovery not using Real Time Apply
2022-07-11T17:21:54.231043+05:30
Recovery interrupted!
...
stopping change tracking
2022-07-11T17:21:57.778547+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/dbtar/DBTAR1/trace/DBTAR1_pr00_3588.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/DBSET/DATAFILE/manual_add.279.1109784107'
2022-07-11T17:21:57.803574+05:30
Background Media Recovery process shutdown (DBTAR1)
Check Datafile details on Standby as below.
SQL> select NAME from v$datafile;
NAME
-----------
+DATA/DBTAR/DATAFILE/system.261.1108233369
+DATA/DBTAR/DATAFILE/test_ts.276.1109780343
+DATA/DBTAR/DATAFILE/sysaux.260.1108233369
+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369
+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369
+DATA/DBTAR/DATAFILE/users.257.1108233369
/ora_home/app/19c/db/dbs/UNNAMED00008
Now we will create above UNNAMED file with NEW Clause on Standby Site
SQL> alter database create datafile '/ora_home/app/19c/db/dbs/UNNAMED00008' as NEW;
Database altered.
Verify Details on Standby Site.
SQL> select NAME from v$datafile;
NAME
---------------------
+DATA/DBTAR/DATAFILE/system.261.1108233369
+DATA/DBTAR/DATAFILE/test_ts.276.1109780343
+DATA/DBTAR/DATAFILE/sysaux.260.1108233369
+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369
+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369
+DATA/DBTAR/DATAFILE/users.257.1108233369
+DATA/DBTAR/DATAFILE/manual_add.277.1109784595
7 rows selected.
Verify from Fixed view
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
----------------
+DATA/DBTAR/DATAFILE/system.261.1108233369
+DATA/DBTAR/DATAFILE/sysaux.260.1108233369
+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369
+DATA/DBTAR/DATAFILE/users.257.1108233369
+DATA/DBTAR/DATAFILE/test_ts.276.1109780343
+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369
As we can see it's still not available , Let we start MRP and check,
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
-----------------
+DATA/DBTAR/DATAFILE/system.261.1108233369
+DATA/DBTAR/DATAFILE/sysaux.260.1108233369
+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369
+DATA/DBTAR/DATAFILE/users.257.1108233369
+DATA/DBTAR/DATAFILE/test_ts.276.1109780343
+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369
+DATA/DBTAR/DATAFILE/manual_add.277.1109784595
7 rows selected.
So post starting MRP issue resolved now.
Case 2=>Add or Drop a Redo Log File Group On Primary Site
The configuration of the redo log and standby redo log on a physical standby database should be reevaluated and adjusted
as necessary after adding or dropping a log file group on the primary database.
In Oracle RAC environments, keep the following in mind:
When an online redo log group is added to a primary database, you must manually add an online redo log group to the standby database. It is not done automatically.
When a new redo thread is added to a primary database, a new redo thread is automatically added to the standby.
By default, the new thread is configured with 2 log groups of 100 MB each. This cannot be changed or overridden.
When a new log group is added to an existing redo thread, a new log group is not automatically added to its existing thread.
On Primary
SQL> SELECT group#,thread# ,MEMBERs FROM V$LOG;
GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 2
2 1 2
3 1 2
4 2 2
11 2 2
SQL>
SQL> alter database add logfile thread 2 group 12 ('+DATA/DBSET/ONLINELOG/online_2_12.ora','+FRA/DBSET/ONLINELOG/online_2_12.ora') size 200M;
Database altered.
SQL> SELECT group#,thread# ,MEMBERs FROM V$LOG;
GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 2
2 1 2
3 1 2
4 2 2
11 2 2
12 2 2
6 rows selected.
On Standby
SQL> show parameter standby_file;
NAME TYPE VALUE
------------------------------------ ----------- ----
standby_file_management string AUTO
SQL> SELECT group#,thread# ,MEMBERs FROM V$LOG;
GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 2
2 1 2
3 1 2
4 2 2
11 2 2
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL> alter database add logfile thread 2 group 12 ('+DATA/DBTAR/ONLINELOG/online_2_12.ora','+FRA/DBTAR/ONLINELOG/online_2_12.ora') size 200M;
Database altered.
SQL> SELECT group#,thread# ,MEMBERs FROM V$LOG;
GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 2
2 1 2
3 1 2
4 2 2
11 2 2
12 2 2
6 rows selected.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> @sync
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 234 234 0
2 170 170 0
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Creating a Tablespace OR Adding a Data File
he STANDBY_FILE_MANAGEMENT database initialization parameter controls whether the addition of a data file to the primary database is automatically propagated to a physical standby databases.
If the STANDBY_FILE_MANAGEMENT database parameter on the physical standby database is set to AUTO, any new data files created on the primary database are automatically created on the physical standby database.
If the STANDBY_FILE_MANAGEMENT database parameter on the physical standby database is set to MANUAL, a new data file must be manually copied from the primary database to the physical standby databases after it is added to the primary database.
Note:
On a physical standby for which the Oracle Active Data Guard option has been enabled, you cannot use the manual copy method. Instead, you must execute the following SQL statement on the standby to create an empty data file:
SQL> ALTER DATABASE CREATE DATAFILE [filename | filenumber] -
AS [NEW | new_filename];
You must specify which one to rename: the filename or the filenumber.
Also specify either the new filename or NEW. The NEW keyword lets Oracle automatically choose a name, if Oracle Managed Files (OMF) is enabled.
If an existing data file from another database is copied to a primary database, it must also be copied to the standby database and the standby control file must be re-created, regardless of the setting of STANDBY_FILE_MANAGEMENT parameter.
Dropping Tablespaces and Deleting Data Files
When a tablespace is dropped or a data file is deleted from a primary database, the corresponding data file(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 data files are no longer part of the database, query the V$DATAFILE view.
Delete the corresponding data file 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 data file 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 data files 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 tbs_4 INCLUDING CONTENTS AND DATAFILES; SQL> ALTER SYSTEM SWITCH LOGFILE;
Renaming a Data File in the Primary Database
When you rename one or more data files in the primary database, the change is not propagated to the standby database. It must be done manually.
To rename the same data files on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
The following steps describe how to rename a data file in the primary database and manually propagate the changes to the standby database.
To rename the data file in the primary database, take the tablespace offline:
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the data file on the primary system:
% mv /disk1/oracle/oradata/payroll/tbs_4.dbf
/disk1/oracle/oradata/payroll/tbs_x.dbf
Rename the data file in the primary database and bring the tablespace back online:
SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE -
> '/disk1/oracle/oradata/payroll/tbs_4.dbf' -
> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
SQL> ALTER TABLESPACE tbs_4 ONLINE;
Note:
An alternative to these first three steps is to use the ALTER DATABASE MOVE DATAFILE command to rename a datafile. This command lets you rename a datafile while allowing read/write access to the datafile. Adequate storage area is a prerequisite for moving a datafile because during the execution of the MOVE DATAFILE command, the database maintains both the original and the renamed datafiles as two separate files. See Moving the Location of Online Data Files for more information.
Connect to the standby database and stop Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Shut down the standby database:
SQL> SHUTDOWN;
Rename the data file at the standby site using an operating system command, such as the UNIX mv command:
% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
Start and mount the standby database:
SQL> STARTUP MOUNT;
Rename the data file in the standby control file. To rename a data file, you must set the STANDBY_FILE_MANAGEMENT database initialization parameter to MANUAL. You can then reset the parameter to its previous value after renaming the data file.
SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' -
> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
On the standby database, restart Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -
> DISCONNECT FROM SESSION;
If you do not rename the corresponding data file at the standby system, and then try to refresh the standby database control file, the standby database attempts to use the renamed data file, but will not find it. Error messages similar to the following are written to the alert log:
ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock datafile 4 - see DBWR trace file
ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf'
Note:
An alternative to steps 4-9 is to use the ALTER DATABASE MOVE DATAFILE command to rename a datafile at the standby. See Moving the Location of Online Data Files for more information.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444