Oracle: July 2023

When datafiles are added on primary I see 2 datafiles are in WRONG Location in standby database after the new standby build

On Standby :
09:52:55 SYSTEM@devdb_std >select file_name from dba_data_files order by 1;

FILE_NAME
--------------------------------------------------------------------------------
/acfs_data/devdb_std/datafile/DEVDB_STD/datafile/o1_mf_it2_lbtw9k_.dbf
/acfs_data/devdb_std/datafile/DEVDB_STD/datafile/o1_mf_it2_lbtymt_.dbf
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-A_FNO-8
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-AA_FNO-9
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-AD_FNO-10
…… Trimmed ….

09:54:26 SYSTEM@devdb_std >sho parameter convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +DATAC1, /acfs_data/devdb_std/datafile
log_file_name_convert                string      +RECOC1, /acfs_reco/devdb_std/onlinelog
pdb_file_name_convert                string

09:53:19 SYSTEM@devdb_std >sho parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby              string      *
standby_db_preserve_states           string      NONE
standby_file_management              string      AUTO
standby_pdb_source_file_dblink       string
standby_pdb_source_file_directory    string

Reason :

The reason being by default when “standby_file_management” is AUTO OMF creates files in “db_file_name_convert” parameter in below location

<db_file_name_convert PATH>/<CAPS DBNAME>/datafile/

FIX:

Check the file number and status

10:31:33 SYSTEM@devdb_std > select file#, error, name from v$datafile_header where ERROR='FILE MISSING';

     FILE# ERROR
---------- -----------------------------------------------------------------
NAME
------------------------------------------------------------------------------------------------------------------------------------------
       781 FILE MISSING

10:34:39 SYSTEM@devdb_std >select name from v$datafile where file#=781 ;

NAME
-------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781

10:34:53 SYSTEM@devdb_std >alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW;
alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic.

Cannot run when standby_file_management is AUTO so disable and rerun

10:36:13 SYSTEM@devdb_std >alter system set standby_file_management=MANUAL scope=both;

System altered.

10:36:25 SYSTEM@devdb_std >alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW;

Database altered.

10:36:31 SYSTEM@devdb_std >alter system set standby_file_management=AUTO scope=both;

System altered.

Check the paths again

SQL >   select file_name from dba_data_files order by 1;

SQL >   select name from v$datafile where file#=779;

set the path correctly based on your location so that we cannot ran into this issue again

10:17:19 SYSTEM@devdb_std >alter system set db_create_file_dest='/acfs_data';

System altered.

10:23:41 SYSTEM@devdb_std >sho parameter file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u02/app/oracle/product/19.0.0.0/dbhome_1/rdbms/audit
db_create_file_dest                  string      /acfs_data
db_recovery_file_dest                string      /acfs_reco/devdb_std/archivelog
db_recovery_file_dest_size           big integer 3500G
remote_recovery_file_dest            string

Now add couple of datafiles in primary and verify.

10:52:44 SYSTEM@devdb_std >select name from v$datafile where file#=779;

NAME
------------------------------------------------------------------------------------------------------------------------------------------
/acfs_data/DEVDB_STD/datafile/o1_mf_imm2_lc2vtbcd_.dbf

Saturday, July 15, 2023

Recover Standby DB when standby_file_management is MANUAL when new files added in primary 19c