By Mouhamadou Diaw
As you may know the parameter STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
It is generally recommended to set this parameter to AUTO, but it can happen that this parameter is set to MANUAL for x reasons. If this parameter is set to MANUAL, the replication will stop if we create a new tablespace, or add new data files in the primary until we manually create the same in the standby.
In the article we are going to show an example of tablespace creation in our oracle 12c Dataguard environment.
Below our configuration.
DGMGRL> show configuration;
Configuration - prod
Protection Mode: MaxPerformance
Members:
prod - Primary database
proddr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 30 seconds ago)
StandbyFileManagement is set to manual for both primary and standby databases
DGMGRL> show database PROD StandbyFileManagement;
StandbyFileManagement = 'MANUAL'
DGMGRL> show database PRODDR StandbyFileManagement;
StandbyFileManagement = 'MANUAL'
DGMGRL>
Let’s show data files on the primary
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD READ WRITE
.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_dymg6h55_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_dymg6loy_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_dymg6nsx_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_dymg6o9o_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_data_dymwrtph_.dbf
And in the standby
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD MOUNTED
.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_system_dymtycrm_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_sysaux_dymtytz6_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_undotbs1_dymtzbbr_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_users_dymtzko2_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_data_dymz1b8y_.dbf
Now let’s create a tablespace in the primary database PROD. In this tablespace we create a table and insert some data inside.
SQL> create tablespace TABDATA datafile size 100M;
Tablespace created.
.
SQL> create table test1(id number) tablespace data;
Table created.
.
SQL> insert into test1 values(1);
1 row created.
.
SQL> commit;
Commit complete.
And then let’s do some switches
SQL> alter system switch logfile;
System altered.
.
SQL> alter system switch logfile;
System altered.
As we may expect, this new tablespace is not automatically replicated on the standby as our StandbyFileManagement is set to MANUAL and our configuration should return errors.
DGMGRL> show configuration;
Configuration - prod
Protection Mode: MaxPerformance
Members:
prod - Primary database
proddr - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 9 seconds ago)
The primary database is fine
DGMGRL> show database PROD;
Database - prod
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD
Database Status:
SUCCESS
But the standby is not synchronized
DGMGRL> show database PRODDR;
Database - proddr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 2 minutes 14 seconds (computed 1 second ago)
Average Apply Rate: 2.00 KByte/s
Real Time Query: OFF
Instance(s):
PROD
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
Database Status:
ERROR
DGMGRL>
In the standby alert log file we can see some ORA-
File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
2017-10-20T15:49:43.368202+02:00
Errors in file /u01/app/oracle/diag/rdbms/proddr/PROD/trace/PROD_mrp0_7182.trc:
ORA-01274: cannot add data file that was originally created as
'/u01/app/oracle/oradata/PROD/datafile/o1_mf_tabdata_dymzqmt4_.dbf'
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2017-10-20T15:49:43.448802+02:00
Errors in file /u01/app/oracle/diag/rdbms/proddr/PROD/trace/PROD_m000_7250.trc:
ORA-01110: data file 1: '/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_system_dymtycrm_.dbf'
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 380449 but controlfile could be ahead of datafiles.
2017-10-20T15:49:43.618264+02:00
If we check data files in the standby, we can see that a UNAMED new file is created in the $ORACLE_HOME/dbs
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD MOUNTED
.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_system_dymtycrm_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_sysaux_dymtytz6_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_undotbs1_dymtzbbr_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_users_dymtzko2_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_data_dymz1b8y_.dbf
/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00006
6 rows selected.
How can I solve the issue and restart the replication. It’s easy. As I am using Oracle-Managed Files
(OMF), I first have to move the data file in the correct directory using following command.
SQL> alter database create datafile '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00006' as new;
Database altered.
.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_system_dymtycrm_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_sysaux_dymtytz6_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_undotbs1_dymtzbbr_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_users_dymtzko2_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_data_dymz1b8y_.dbf
/u01/app/oracle/oradata/PRODDR/datafile/o1_mf_tabdata_dyn06zm2_.dbf
6 rows selected.
SQL>
If OMF is not used we have to use something like
alter database create datafile '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00006' as 'datafile_location_and_name';
After I decide to update the parameter standbyfilemanagement to AUTO on both primary and standby.
DGMGRL> edit database PROD set property StandbyFileManagement = 'AUTO';
Property "standbyfilemanagement" updated
DGMGRL> edit database PRODDR set property StandbyFileManagement = 'AUTO';
Property "standbyfilemanagement" updated
And finally I have to restart the redo apply on the standby
DGMGRL> edit database PRODDR set STATE='APPLY-ON';
Succeeded.
DGMGRL>
The synchronization should be fine now and the command show configuration should return success
DGMGRL> show configuration;
Configuration - prod
Protection Mode: MaxPerformance
Members:
prod - Primary database
proddr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 15 seconds ago)
On the primary database
DGMGRL> show database PROD;
Database - prod
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD
Database Status:
SUCCESS
On the standby database
DGMGRL> show database PRODDR;
Database - proddr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 83.00 KByte/s
Real Time Query: OFF
Instance(s):
PROD
Database Status:
SUCCESS
DGMGRL>
If we want to verify the new created table, we can open the standby database in a read only mode.
But if we don’t have the Active Dataguard option, the redo apply must be stopped for the standby database before opening it.
DGMGRL> edit database PRODDR set STATE='APPLY-OFF';
Succeeded.
And then open the database
SQL> alter database open read only;
Database altered.
.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
DATA
TABDATA
7 rows selected.
.
SQL> select * from test1;
ID
----------
1
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)