Effect of adding datafiles on primary when you don't have sufficient space left on standby database

Effect of adding datafiles on primary when you don't have sufficient space left on standby database

In this demonstration, I will show you how standby behaves when you add a datafile on primary database where standby don't have enough space to replicate that change.

Here are my configuration details.

Primary Server: orcldbvm01 IP :192.168.0.120 Primary CDB :SRCDB

Standby Server: orcldbvm02 IP :192.168.0.121 Standby CDB :TRCDB

Primary database details-

[oracle@orcldbvm01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 1 04:09:41 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name,db_unique_name,open_mode,database_role,protection_mode from v$database;
NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- ------------------------------ -------------------- ---------------
SRCDB     SRCDB                          READ WRITE           PRIMARY          MAXIMUM AVAILABILITY
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APP1_PRD_PDB                   READ WRITE NO
SQL>

Standby database details-

[oracle@orcldbvm02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 1 04:10:25 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select name,db_unique_name,open_mode,database_role,protection_mode from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

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

SRCDB     TRCDB                          READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 APP1_PRD_PDB                   READ ONLY  NO

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL> select distinct substr ( name , 1,23) "datafile_location"  from v$datafile;

datafile_location

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

/u01/app/oracle/oradata

SQL> show parameter db_Create_file_dest

NAME                                 TYPE        VALUE

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

db_create_file_dest                  string      /u01/app/oracle/oradata

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u02/app/oracle/orafra/fast_recovery_area/

db_recovery_file_dest_size           big integer 12918M

SQL> select SOURCE_DB_UNIQUE_NAME,NAME,VALUE,TIME_COMPUTED from v$dataguard_stats;

SOURCE_DB_UNI NAME                             VALUE            TIME_COMPUTED

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

SRCDB         transport lag                    +00 00:00:00     05/01/2021 04:13:36

SRCDB         apply lag                        +00 00:00:00     05/01/2021 04:13:36

SRCDB         apply finish time                +00 00:00:00.000 05/01/2021 04:13:36

              estimated startup time           186              05/01/2021 04:13:36

SQL>

As you can see, my datafiles have been configured under /u01, let's check the free space available.

[oracle@orcldbvm02 ~]$ df -h

Filesystem      Size  Used Avail Use% Mounted on

devtmpfs        967M     0  967M   0% /dev

tmpfs           982M  9.5M  973M   1% /run

tmpfs           982M     0  982M   0% /sys/fs/cgroup

/dev/sda3        25G  7.6G   16G  33% /

/dev/sdb1        30G   16G   14G  55% /u01

tmpfs           197M   28K  197M   1% /run/user/1000

tmpfs           197M  4.0K  197M   1% /run/user/42

/dev/sda1       499M  168M  332M  34% /boot

/dev/sdc1        15G   37M   15G   1% /u02

tmpfs           197M     0  197M   0% /run/user/0

[oracle@orcldbvm02 ~]$

Now using fallocate command, create a large dummy file to fill up the /u01 filesystem.

[oracle@orcldbvm02 ~]$ cd /u01

[oracle@orcldbvm02 u01]$ fallocate -l 13G test.img

[oracle@orcldbvm02 u01]$  df -h

Filesystem      Size  Used Avail Use% Mounted on

devtmpfs        967M     0  967M   0% /dev

tmpfs           982M  9.5M  973M   1% /run

tmpfs           982M     0  982M   0% /sys/fs/cgroup

/dev/sda3        25G  7.6G   16G  33% /

/dev/sdb1        30G   29G  457M  99% /u01

tmpfs           197M   28K  197M   1% /run/user/1000

tmpfs           197M  4.0K  197M   1% /run/user/42

/dev/sda1       499M  168M  332M  34% /boot

/dev/sdc1        15G   37M   15G   1% /u02

tmpfs           197M     0  197M   0% /run/user/0

[oracle@orcldbvm02 u01]$

Now we've only 457 MB left in /u01, lets try to add one datafile with size > 457 MB.

At Primary,

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME                FILE_NAME

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

SYSAUX                         /u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j1tfb52y_.dbf

SQL>

At standby,

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME                FILE_NAME

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

SYSAUX                         /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf

SQL>

Add one datafile to SYSAUX tablespace.

SQL> alter tablespace SYSAUX add datafile size 600M autoextend on ;

Tablespace altered.

SQL>  select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME                FILE_NAME

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

SYSAUX                         /u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j1tfb52y_.dbf

SYSAUX                         /u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf

SQL>

Standby alertlog file reported following errors.

File #33 added to control file as 'UNNAMED00033'.

Originally created as:

'/u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf'

Recovery was unable to create the file as a new OMF file.

PR00 (PID:18366): MRP0: Background Media Recovery terminated with error 1274

Non critical error ORA-48113 caught while writing to trace file "/u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_pr00_18366.trc"

Error message:

Writing to the above trace file is disabled for now...

2021-05-02T05:51:22.622641-04:00

Errors in file /u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_pr00_18366.trc:

ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf'

PR00 (PID:18366): Managed Standby Recovery not using Real Time Apply

2021-05-02T05:51:35.464556-04:00

Recovery interrupted!

2021-05-02T05:51:37.129072-04:00

Non critical error ORA-48180 caught while writing to trace file "/u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_mz00_18939.trc"

Error message: Linux-x86_64 Error: 28: No space left on device

Additional information: 1

Writing to the above trace file is disabled for now...

2021-05-02T05:51:42.679478-04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

Recovered data files to a consistent state at change 4792358

stopping change tracking

2021-05-02T05:51:42.682605-04:00

Errors in file /u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_pr00_18366.trc:

ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf'

2021-05-02T05:51:42.728424-04:00

Background Media Recovery process shutdown (TRCDB)

As you could see MRP has been crashed.

SQL> select name,db_unique_name,open_mode,database_role,protection_mode from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE ---------- ---------------- -------------------- ---------------- ----------- SRCDB TRCDB READ ONLY PHYSICAL STANDBY MAXIMUM AVAILABILITY SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 60 28672 366 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 62 6144 395 ARCH CLOSING 1 59 1 32 ARCH CLOSING 1 63 8192 959 RFS IDLE 1 0 0 0 RFS IDLE 1 64 5682 1 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 10 rows selected. SQL> select file#,name from v$datafile where name like '%UNNAMED%'; FILE# NAME ---------- ------------------------------------------------------------------ 33 /u01/app/oracle/product/version/db_1/dbs/UNNAMED00033 SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX'; TABLESPACE_NAME FILE_NAME ------------------------------ ---------------------------------------------- SYSAUX /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf SQL>

Let's recover the datafile which is created with name '%UNNAMED%'. For that remove the dummy file created.

[oracle@orcldbvm02 u01]$ rm -rf test.img

[oracle@orcldbvm02 u01]$ df -h

Filesystem      Size  Used Avail Use% Mounted on

devtmpfs        967M     0  967M   0% /dev

tmpfs           982M  9.6M  973M   1% /run

tmpfs           982M     0  982M   0% /sys/fs/cgroup

/dev/sda3        25G  7.6G   16G  33% /

/dev/sdb1        30G   17G   14G  56% /u01

tmpfs           197M   28K  197M   1% /run/user/1000

tmpfs           197M  4.0K  197M   1% /run/user/42

/dev/sda1       499M  168M  332M  34% /boot

/dev/sdc1        15G   37M   15G   1% /u02

tmpfs           197M     0  197M   0% /run/user/0

[oracle@orcldbvm02 u01]$

Now we've enough space in /u01, let's create the datafile.

[oracle@orcldbvm02 u01]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 2 06:02:25 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL> alter system set standby_file_management='MANUAL';

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      MANUAL

SQL> alter database create datafile '/u01/app/oracle/product/version/db_1/dbs/UNNAMED00033' as new;

Database altered.

SQL>  select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME               FILE_NAME

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

SYSAUX                        /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf

SQL>

Start managed recovery.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

DGRD      ALLOCATED             0          0          0          0

ARCH      CLOSING               1         60      28672        366

DGRD      ALLOCATED             0          0          0          0

ARCH      CLOSING               1         62       6144        395

ARCH      CLOSING               1         59          1         32

ARCH      CLOSING               1         63       8192        959

RFS       IDLE                  1          0          0          0

RFS       IDLE                  1         64     111491          2

RFS       IDLE                  0          0          0          0

RFS       IDLE                  0          0          0          0

MRP0      APPLYING_LOG          1         64      18832     409600

11 rows selected.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME               FILE_NAME

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

SYSAUX                        /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf

SYSAUX                        /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j8wybm5n_.dbf

SQL> alter system set standby_file_management='AUTO';

System altered.

SQL>

Standby has successfully recovered the datafile which was originally created with 'UNNAMED%' name.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011