ORA-01110

My Database Setup is :

Primary : 2 Node RAC 12.1.0.2 with Multitenant Architecture and ASM file structure

Standby : 2 Node RAC 12.1.0.2 with Multitenant Architecture and ASM file structure

During Data Guard Configuration, the standby file management was kept to manual (by mistake). After that we have created one new tablespace in primary database and which has shutdown the MRP process in Data Guard with below errors in alert log of standby instance.

ORA-01111: name for data file 87 is unknown – rename to correct file

ORA-01110: data file 87: ‘/u01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00087’

ORA-01157: cannot identify/lock data file 87 – see DBWR trace file

ORA-01111: name for data file 87 is unknown – rename to correct file

ORA-01110: data file 87: ‘/u01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00087’

MRP0: Background Media Recovery process shutdown (blrdr2)

SQL> show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
http://www.oracle-ckpt.com/unnamed-file-in-standby-after-adding-new-file-to-primary-ora-01111-ora-01110-ora-01157/
http://www.oracle-ckpt.com/unnamed-file-in-standbypdb-after-adding-new-file-to-primarypdb/

https://community.oracle.com/thread/3980984

ORA-17628: Oracle error 19505 returned by remote Oracle server

Problem

Dataguard – Standby duplication process failed with ORA-17628

Hand On Senario

bash-3.2$ rman TARGET sys/*******@pdb AUXILIARY sys/******@sdb
Recovery Manager: Release 11.2.0.3.0 – Production on Thu Oct 20 15:19:09 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: orcl (DBID=3171721304)
connected to auxiliary database: orcl (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 20-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=991 instance=orcl2 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcldr2’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl2’ ;
}
executing Memory Script
Starting backup at 20-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1499 instance=orcldr2 device type=DISK
Finished backup at 20-OCT-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘+ORAREDO/orcl/controlfile/current.256.925744863’;
restore clone controlfile to ‘+ORAFRA/orcl/controlfile/current.256.925744863’ from
‘+ORAREDO/orcl/controlfile/current.256.925744863’;
sql clone “create spfile from memory”;
shutdown clone immediate;
startup clone nomount;
sql clone “alter system set control_files =
”+ORAREDO/orcl/controlfile/current.256.925744863”, ”+ORAFRA/orcl/controlfile/current.256.925744863” comment=
”Set by RMAN” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 20-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=+ORAFRA/orcldr/snapshot/snapcf_orcldr.f tag=TAG20161020T151933 RECID=10 STAMP=925744774
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-OCT-16
Starting restore at 20-OCT-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-OCT-16
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 8551575552 bytes
Fixed Size 2245480 bytes
Variable Size 5066722456 bytes
Database Buffers 3472883712 bytes
Redo Buffers 9723904 bytes
sql statement: alter system set control_files = ”+ORAREDO/orcl/controlfile/current.256.925744863”, ”+ORAFRA/orcl/controlfile/current.256.925744863” comment= ”Set by RMAN” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 8551575552 bytes
Fixed Size 2245480 bytes
Variable Size 5066722456 bytes
Database Buffers 3472883712 bytes
Redo Buffers 9723904 bytes
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
“+orasys”;
switch clone tempfile all;
set newname for datafile 1 to
“+orasys”;
set newname for datafile 2 to
“+orasys”;
set newname for datafile 3 to
“+orasys”;
set newname for datafile 4 to
“+orasys”;
set newname for datafile 5 to
“+orasys”;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +orasys in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00110 name=+PUBWIFI/orcldr/datafile/elitewifi.257.866043479
output file name=+PUBWIFI/orcl/datafile/elitewifi.257.925744929 tag=TAG20161020T152038
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00035 name=+ORADATA1/orcldr/datafile/tbsidxusernamestatistics.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/20/2016 15:21:36
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run

Analysis

Some datafiles are not using OMF name while the rest of the datafiles are using OMF name

http://selvathiruppathi.blogspot.in/2015/04/ora-17628-ora-19505-during-rman.html

http://selvathiruppathi.blogspot.in/2015/04/ora-17628-ora-19505-during-rman.html

alter system set db_file_name_convert=’orcldr’,’orcl’ scope=spfile sid=’*’;
alter system set log_file_name_convert=’orcldr’,’orcl’ scope=spfile sid=’*’;

Thanks to 🙂 http://selvathiruppathi.blogspot.in/2015/04/ora-17628-ora-19505-during-rman.html

 

 

RAC to RAC DG – ARCHIVELOG GAP RECOVERY SCENARION

On primary :

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

On Standby :

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

=============================ARCHIVELOG GAP RECOVERY SCENARION====================================================

On Standby :

SELECT TO_CHAR(CURRENT_SCN) FROM v$DATABASE;

—————
24848626949

On Primary :

rman target /

run
{
allocate channel c1 type disk format ‘/SMP_Logs/rman/%U.rmb’;
backup incremental from scn 24848626949 database;
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/SMP_Logs/rman/ForStandbyCTRL.bck’;
}

SCP backup* to standby;

On Standby:

srvctl stop database -d piapdr

startup nomount

rman target /

restore standby controlfile from ‘/u01/rman/ForStandbyCTRL.bck’;

sqlplus / as sysdba >> Both node

ALTER DATABASE MOUNT;

rman target /

catalog start with ‘/u01/rman/’;

catalog start with ‘+ORADATA1’;
catalog start with ‘+ORADATA2’;

report schema;

Note : Check Size column is shouldn’t be contain zero (0) values if yes than follow below steps for each datafiles which is has 0 values.

run
{
set newname for datafile 1 to ‘+ORADATA1/piapdr/datafile/SYSTEM.326.909536701’;
}

run
{
restore datafile 2;
}
run

run

{
set newname for datafile 2 to ‘+ORADATA1/piapdr/datafile/SYSAUX.325.909536707’;
}

run
{
restore datafile 2;
}
run

{
set newname for datafile 3 to ‘+ORADATA1/piapdr/datafile/UNDOTBS1.328.909536651’;
}

run
{
restore datafile 3;
}
run

{
set newname for datafile 4 to ‘+ORADATA1/piapdr/datafile/USERS.324.909536825’;
}

run
{
restore datafile 4;
}

run

{
set newname for datafile 44 to ‘+oradata2/piapdr/datafile/WEBINPWIFI.367.909537767’;
}

run
{
restore datafile 44;
}

ALTER DATABASE OPEN READ ONLY; ===== Both nodes.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING SESSION;

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Fixing it on primary database

SQL> alter system set log_archive_dest_2=” scope=both;

System altered.

## Fixing this on our standby terminal

SQL>  alter system set log_archive_dest_2=” scope=both;

System altered.

http://karandba.blogspot.in/2014/10/far-sync-in-oracle-12c-database.html

USING ‘DELAY’ OPTION TO PROTECT LOGICAL/PHYSICAL CORRUPTIONS

You may utilize the delay option (if you have multiple standby sites) to prevent physical/logical corruption of your primary. For instance, your standby #1 may not have ‘Delay’ on to be your disaster recovery standby database. However, you may opt to implement a delay of minutes or hours on your standby #2 to allow recover from a possible physical or logical corruption on your primary database.
SQL> alter database recover managed standby database delay 5 disconnect;

http://www.pafumi.net/Standby_Concepts_Configuration.html

2 – Node RAC Stadby database datafiles recovery from Primary Site

Standby Site Recovery Senario

Environment
2-Node RAC Oracle11g 11.2.0.3.0 with ASM /RHEL 5.6 64bit/ RAC to RAC Data guard configuration

Problem:

Due to miscommunication by Junior engineer who had communication gap with Storage Admin and so Storage Administrator had perform activity for drop the live raw partitions and recreate the same partition again.

I got the call from my client that services is down I checked 360 degree to recover the services first.

–Recover the Database1 – Datafile – webinpwifi
run{
2> set newname for datafile 6 to ‘C:APPMAZARORADATAOWNnew/test01.dbf’;
3> set newname for datafile 7 to ‘C:APPMAZARORADATAOWNnewtest02.dbf’;
4> restore tablespace “TEST”;
5> switch datafile all;
6> recover tablespace “TEST”;
7> alter database open;
8> }

–Recover the Database2 which is standby database in rac mode

--Recover Standby database - ASM Diskgroup
oracleasm listdisks
oracleasm scandisks
/usr/sbin/oracleasm createdisk MYDSK /dev/mapper/MYDSKp1

CREATE DISKGROUP MYDSK EXTERNAL REDUNDANCY
DISK 'ORCL:MYDSK'
ATTRIBUTE 'au_size'='4M',
'compatible.asm' = '11.2.0.0.0',
'compatible.rdbms' = '11.2.0.0.0',
'compatible.advm' = '11.2.0.0.0';

--Recover Standby database datafile from Primary Site
Step-1 connect to the standby server and startup database in mount stage
Standby Node1
sqlplus> STARTUP MOUNT;

Standby Node2
sqlplus> STARTUP MOUNT;

or

srvctl stop database -d stydb
srvctl stop database -d stydb -o mount

Step–2 Using RMAN, connect to primary as target and standby as auxiliary

rman target / auxiliary sys/*******@duaaa

Step-3 Take image copy backup of example tablespace on primary so that backup file is created on standby
RMAN> backup as copy  tablespace ELITECG auxiliary format '+MYDSK';
inputfile name=x1
outputfile name=x2

RMAN> backup as copy  tablespace ELITEAAAPWIFI auxiliary format '+MYDSK';
inputfile name=y1
outputfile name=y2

Example
=======
-bash-3.2$ rman TARGET / AUXILIARY sys/*******@stydb

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 18 21:17:22 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DUAAA (DBID=3171721304)
connected to auxiliary database: DUAAA (DBID=3171721304, not open)

RMAN> backup as copy  tablespace ELITECG auxiliary format '+MYDSK';

Starting backup at 18-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=554 instance=duaaadr2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00109 name=+MYDSK/duaaadr/datafile/elitecg.256.866041173
output file name=+MYDSK/duaaa/datafile/elitecg.259.874703867 tag=TAG20150318T211745
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-MAR-15

RMAN> backup as copy  tablespace ELITEWIFI auxiliary format '+MYDSK';

Starting backup at 18-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00110 name=+MYDSK/duaaadr/datafile/elitewifi.257.866043479
output file name=+MYDSK/duaaa/datafile/elitewifi.257.874703897 tag=TAG20150318T211817
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-MAR-15

Step3 — Connect to standby database

rman target
RMAN > report schemas;

109 x1
110 y1

run
{
set newname for datafile 109 to x2;
switch datafile 109;
}

run
{
set newname for datafile 110 to y2;
switch datafile 110;
}

Example
=======
catalog datafilecopy '+MYDSK/duaaa/datafile/elitecg.259.874703867';
catalog datafilecopy '+MYDSK/duaaa/datafile/elitewifi.257.874703897';

run {
set newname for datafile 109 to '+MYDSK/duaaa/datafile/elitecg.259.874703867';
switch datafile 109;
}

run {
set newname for datafile 110 to '+MYDSK/duaaa/datafile/elitewifi.257.874703897';
switch datafile 110;
}

Step-4 — Wait some time and check the alert logs of standby database

archive log list
confirm the generated and apply archive of primary and standby site

Step-5 — Standby Node1

ALTER DATABASE OPEN READ ONLY;

Step-6 -- Standby Node2
ALTER DATABASE OPEN READ ONLY;

Step-7 — Only from Standby Node1

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Reference
http://oracleinaction.com/recover-standby-datafile-primary/
http://www.oraclecommunity.net/profiles/blogs/rmanrestore-datafiles-to
http://www.thesqlreport.com/?p=728
http://www.oracle-wiki.net/startdocshowtomoveanasmdatafilediskgrp
https://aprakash.wordpress.com/2011/05/29/moving-datafiles-using-asmcmd-11gr2/