Few days ago i got the activity to create the 2 NODE RAC DR on EXADATA for 2 NODE RAC Production.

Here I am listing few issues that made this activity perfect case for Data Guard beginners

Environment:
Production Database Name: Finance
db_unique_name : finance

Standby Database Name : Finstandby
db_unique_name : finstandby

Production OS Node: agoracledba1 agoracledba2
Standby OS Node: agoracledba3 agoracledba4

The Standby is created with below script:
startup with pfile in nomount stage

[oracle@agoracledba3 ~]$ cat pfile.ora
*.db_name='fin'
*.db_unique_name='finstandby'
*.db_name='fin'
*.db_unique_name='finp'
*.db_file_name_convert='+DATAFILE','+DATAC3/'
*.log_file_name_convert='+DATAFILE','+DATAC3/'
*.db_recovery_file_dest='+RECOC3'
*.control_files='+DATAC3'
*.log_archive_max_processes='5'
*.db_recovery_file_dest='+RECOC3'
*.db_recovery_file_dest_size='500G'
*.standby_file_management='AUTO'
*.compatible='12.2.0.1.0'
*.sga_target='5500m'  

Connect to database and start in nomunt

[oracle@agoracledba3 dbs]$ !sq
sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 8 21:31:33 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/db_1/dbs/pfile.ora'; ORACLE instance started. Total System Global Area 591396864 bytes Fixed Size 8623208 bytes Variable Size 507513752 bytes Database Buffers 67108864 bytes Redo Buffers 8151040 bytes SQL>

Now create the standby database

[oracle@agoracledba3 dbs]$ rman auxiliary sys/oracle@finstandby  target sys/oracle@finance debug trace=/expdp/rman_dupl.trc msglog /u01/rman_clone2_log  

Run the RMAN script for creating standby

RMAN> set echo on;
show all;
run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate channel p5 type disk;
allocate channel p6 type disk;
allocate auxiliary channel s1 type disk;
allocate auxiliary channel s2 type disk;
allocate auxiliary channel s3 type disk;
allocate auxiliary channel s4 type disk;
allocate auxiliary channel s5 type disk;
allocate auxiliary channel s6 type disk;
allocate auxiliary channel s7 type disk;
duplicate target database for standby from active database nofilenamecheck; }

This script will restore the standby from the live production database over the network.


Everything was fine till the recovery of the database is not started, recovery is cancelled and error is reported in alert log .
Looking in the Alert Log one will find unusual error as listed below.


WARNING: The converted filename '+DATAC3/FIN/datafile/users.363.1004613481'is an ASM fully qualified filename.          
Changing the filename to '+DATAC3/MUST_RENAME_THIS_DATAFILE_95.4294967295.4294967295'.          

Please rename it accordingly. 2019-04-07T11:54:07.770296+05:30  Errors in file /u01/app/oracle/diag/rdbms/finp/FINP1/trace/FINP1_m000_166622.trc: 
ORA-01110: data file 95: '+DATAC3/MUST_RENAME_THIS_DATAFILE_95.4294967295.4294967295' 
ORA-01565: error in identifying file '+DATAC3/MUST_RENAME_THIS_DATAFILE_95.4294967295.4294967295' 
ORA-17503: ksfdopn:2 Failed to open file +DATAC3/MUST_RENAME_THIS_DATAFILE_95.4294967295.4294967295  
ORA-15012: ASM file '+DATAC3/MUST_RENAME_THIS_DATAFILE_95.4294967295.4294967295' does not exist 
WARNING: The converted filename '+DATAC3/FIN/datafile/users.363.1004613481' is an ASM fully qualified filename.          
Changing the filename to '+DATAC3/MUST_RENAME_THIS_DATAFILE_95.4294967295.4294967295'. Please rename it accordingly.  
Checker run found 1 new persistent data failures 2019-04-07T11:54:08.466245+05:30    
 

Now connect to standby database and check the name of datafiles.

[oracle@agoracledba dbs]$ !sq 
sqlplus/ as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 8 21:31:33 2019  
Copyright (c) 1982, 2016, Oracle. All rights reserved.  

sys @finstandby > select file#, name from v$datafile where name like '%MUST_RENAME_THIS_DATAFILE%';  

FILE# NAME     82 
+DATAC3/MUST_RENAME_THIS_DATAFILE_82.4294967295.4294967295     86 
+DATAC3/MUST_RENAME_THIS_DATAFILE_86.4294967295.4294967295     87 
+DATAC3/MUST_RENAME_THIS_DATAFILE_87.4294967295.4294967295     88 
+DATAC3/MUST_RENAME_THIS_DATAFILE_88.4294967295.4294967295     89 
+DATAC3/MUST_RENAME_THIS_DATAFILE_89.4294967295.4294967295     90 
+DATAC3/MUST_RENAME_THIS_DATAFILE_90.4294967295.4294967295     91 
+DATAC3/MUST_RENAME_THIS_DATAFILE_91.4294967295.4294967295     92 
+DATAC3/MUST_RENAME_THIS_DATAFILE_92.4294967295.4294967295     93 
+DATAC3/MUST_RENAME_THIS_DATAFILE_93.4294967295.4294967295     94 
+DATAC3/MUST_RENAME_THIS_DATAFILE_94.4294967295.4294967295     95 
+DATAC3/MUST_RENAME_THIS_DATAFILE_95.4294967295.4294967295     96

11 rows selected. 

From Prodcution database exact names of the datafile can be find and will be used to restore the datafiles with original names.

oracle@agoracledba dbs]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 8 21:31:33 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
sys @finance> set lines 900
col file_name format a60
select file_id, file_name , tablespace_name from dba_data_files where file_id in  (82,86,87,88,89,90,91,92,93,94,95);
FILE_ID FILE_NAME  TABLESPACE_NAME
82 +DATAFILE/FIN/DATAFILE/tbs_med_data.341.982585005                   TBS_MED_DATA
86 +DATAFILE/FIN/DATAFILE/tbs_med_data.345.989420543                                TBS_MED_DATA
87 +DATAFILE/FIN/DATAFILE/tbs_med_data.346.991409717                                TBS_MED_DATA
88 +DATAFILE/FIN/DATAFILE/tbs_los_data.347.991409841                                TBS_LOS_DATA
89 +DATAFILE/FIN/DATAFILE/tbs_med_data.348.996340027                                TBS_MED_DATA
90 +DATAFILE/FIN/DATAFILE/undotbs1.349.1003074255                                   UNDOTBS1
91 +DATAFILE/FIN/DATAFILE/rst_tbl.350.1003075065                                    RST_TBL
92 +DATAFILE/FIN/DATAFILE/tbs_med_data.352.1003317241                               TBS_MED_DATA
93 +DATAFILE/FIN/DATAFILE/tbs_med_indx.365.1003782249                               TBS_MED_INDX
94 +DATAFILE/FIN/DATAFILE/tbs_med_data.364.1004177605                               TBS_MED_DATA
95 +DATAFILE/FIN/DATAFILE/users.363.1004613481                                      USERS

Same Datafiles need to be restored with new datafile name by using “SET NEW NAME” parameter as no datafile is available physically on ASM DISKs.

oracle@agoracledba dbs]$ rman target / debug trace=/expdp/rman_dupl.trc msglog /u01/rman_clone3_log  

Rerun the RMAN script

RMAN> set echo on;
show all;
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c9 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
set newname for datafile 82  to '+DATAC3/FINP/DATAFILE/tbs_data.341.982585005';
set newname for datafile 86 to '+DATAC3/FINP/DATAFILE/tbs_data.345.989420543';
set newname for datafile 87 to '+DATAC3/FINP/DATAFILE/tbs_data.346.991409717';
set newname for datafile 88 to '+DATAC3/FINP/DATAFILE/tbs_los_data.347.991409841';
set newname for datafile 89  to '+DATAC3/FINP/DATAFILE/tbs_data.348.996340027';
set newname for datafile 90  to '+DATAC3/FINP/DATAFILE/undotbs1.349.1003074255';
set newname for datafile 91  to '+DATAC3/FINP/DATAFILE/rst_tbl.350.1003075065';
set newname for datafile 92 to '+DATAC3/FINP/DATAFILE/tbs_data.352.1003317241';
set newname for datafile 93 to '+DATAC3/FINP/DATAFILE/tbs_indx.365.1003782249';
set newname for datafile 94 to '+DATAC3/FINP/DATAFILE/tbs_data.364.1004177605';
set newname for datafile 95 to '+DATAC3/FINP/DATAFILE/users.363.1004613481';
restore database from service finance using compressed backupset;
switch datafile all;
recover database from service finance using compressed backupset;
}

this will restore all the missing datafiles and can be check at the standby database by running the below query.

For Consistency in the database I am suggesting to restore the whole database using rman by restoring CONTROLFILE first and then FULL DATABASE RESTORE to avoid any other unusual error.

At standby, rerun the above query again

[oracle@agoracledba ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 8 21:55:55 2019  
Copyright (c) 1982, 2016, Oracle.  All rights reserved.  
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 
SQL> set lines 900
col file_name format a60
select file_id, file_name , tablespace_name from dba_data_files where file_id in (82,86,87,88,89,90,91,92,93,94,95);
FILE_ID FILE_NAME TABLESPACE_NAME 90 +DATAC3/FINP/DATAFILE/undotbs1.274.1005013271 UNDOTBS1 95 +DATAC3/FINP/DATAFILE/users.289.1005013531 USERS 93 +DATAC3/FINP/DATAFILE/tbs_med_indx.262.1005013301 TBS_MED_INDX 82 +DATAC3/FINP/DATAFILE/tbs_med_data.346.1005011325 TBS_MED_DATA 86 +DATAC3/FINP/DATAFILE/tbs_med_data.259.1005012389 TBS_MED_DATA 87 +DATAC3/FINP/DATAFILE/tbs_med_data.268.1005012423 TBS_MED_DATA 89 +DATAC3/FINP/DATAFILE/tbs_med_data.275.1005013125 TBS_MED_DATA 92 +DATAC3/FINP/DATAFILE/tbs_med_data.273.1005013245 TBS_MED_DATA 94 +DATAC3/FINP/DATAFILE/tbs_med_data.263.1005013385 TBS_MED_DATA 88 +DATAC3/FINP/DATAFILE/tbs_los_data.269.1005013111 TBS_LOS_DATA 91 +DATAC3/FINP/DATAFILE/rst_tbl.272.1005013237 RST_TBL 11 rows selected.

Now, check the errors datafile again with below query.

SQL> select file#, name from v$datafile where name like '%MUST_RENAME_THIS_DATAFILE%'; 
no rows selected 

See all the required datafiles are restored and the standby database is consistent.


While restoring db or required datafiles , restoration progress can be checked by the following query:

SELECT SID, SERIAL#, MESSAGE, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Error No 2

ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/app/oracle/system01.dbf'

The ORA-19909 error occurs when you have made an error choosing a restore file that is part of the current (or prior) incarnation of the database. The oerr utility show this for the ORA-19909 error:

ORA-19909: datafile %s belongs to an orphan incarnation

Cause: Either the specified datafile was restored from a backup that was taken during a period of time that has already been discarded by a resetlogs operation, or Oracle cannot identify which database incarnation the file belongs to. The alert log contains more information.

Action: Restore a backup of this file that belongs to either the current or a prior incarnation of the database. If you are using RMAN to restore, RMAN will automatically select a correct backup.

You can run this command to see the correct incarnation:

RMAN> list incarnation of database; 

To verify that you have the correct incarnation, you can display the checkpoint_change# and checkpoint_time columns in v$datafile and v$datafile_header and compare these values with those found in v$database.

One solution is to reset the standby database’s incarnation to match the primary database incarnation:

RMAN> reset database to incarnation 2; 

This blog is completed.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

For any assistance: http://www.facebook.com/er.arungupta
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Comments

  1. SriHarsha

    You can use db_create_file_dest and db_create_online_log_dest*. No need to use file_convert for both data and log files.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.