RMAN Duplicate reports datafile xx not processed because file is offline

Was performing a database clone from RAC to RAC and came across this interesting issue…

RMAN duplicate has done the control file restore and started reporting below messages…and finally failed to open the database...

database mounted

datafile 73 not processed because file is offline

datafile 6 not processed because file belongs to tablespace with one or more offline immediate datafile (X_STATS_INDX)

datafile 10 not processed because file belongs to tablespace with one or more offline immediate datafile (X_STATS_INDX)

datafile 12 not processed because file belongs to tablespace with one or more offline immediate datafile (X_STATS_INDX)

…

…

…

We were doing a clone as of 40 days back but all the backups are available inline on TAPE which we verified via list backup command earlier and confirmed by storage admin so a bit confusing why it's failing here…

In the end we queried the creation timestamp of the initial datafile that's reported as 'offline' and it was added on the day we were restoring unto…however our refresh timestamp happened to be between the datafile addition timestamp and the nighty backup, a very infrequent occurrence.

We were refreshing this db unto 10th June 2015 2300 hrs and datafile was added around 11:10AM…backup happened after 2300 hrs…

SELECT FILE#,NAME,TO_CHAR(CREATION_TIME,'DD-MON-YY HH24:MI:SS') FROM V$DATAFILE WHERE FILE#=73;

When we changed the timestamp to the next day 2am, everything worked perfectly fine.

It appears to be a bug with duplicate option in 11.2.0.3 and fixed in 11.2.0.4.

However this will not be an issue if a traditional restore/recovery method is used.

CATALOG A BACKUPPIECE WITH 'SBT_TAPE'

Had another requirement today to catalog a backuppiece with my SBT_TAPE and couldn't find syntax for it readily available anywhere...

Here's the syntax that worked...

RMAN>  CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NSR_SERVER=,NSR_GROUP=,NSR_DATA_VOLUME_POOL=,NSR_CLIENT=)';

set sbt_tape as your default device type...this can be removed later if required...

RMAN>  CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '0rns43vq_1_1';

The PARMS could be different for different media vendors but the syntax is similar...

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Wish you all a very Happy 2015!

OK...here's the requirement...I've a full 'online' backup of a RAC database and need to recover from it multiple times and open the database with 'alter database open resetlogs;' statement.

It's like my gold backup which I want to restore whenever I want...

taken a backup...
rman target / nocatalog

run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset incremental level 0 database TAG FULL_BACKUP_05JAN2015
plus archivelog TAG ARCH_BACKUP_05JAN2015;
backup spfile;
backup current controlfile;
}

I get these tags above populated from variables but hard-coding them to keep it simple...

Perform 1st restore and recovery as below...

startup nomount;

set DBID xxxxxxx;

restore controlfile from 'c-xx-xxxx'; (this is from the last autobackup)

alter database mount;

list incarnation of database;

restore and recover database as below…

using tape channels as the backup has already been removed from the disks and only available on SBT_TAPE channel.

run {

allocate channel t1 type 'SBT_TAPE';

send 'NSR_ENV=(NSR_SERVER=xxxx,NSR_GROUP=xxxx,NSR_DATA_VOLUME_POOL=xxxxx,NSR_CLIENT=xxxxxx,NSR_SAVESET_BROWSE="xxxxx",NSR_SAVESET_RETENTION="xxxxx")';

SET UNTIL TIME "TO_DATE('06-JAN-2015 11:15:00','DD-MON-YYYY HH24:MI:SS')"; (I've all the archive logs upto and after this point on disk and tape)

restore database;

recover database;

release channel t1;

}

You can allocate multiple channels depending on your media/tape system configuration.

This worked perfectly fine…and I've opened the database as below…

rman> alter database open resetlogs;

Come next day …I've the requirement to restore from the same backup so I run the same process again…and this time it fails...

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 01/09/2015 14:26:50

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Now it failed with the above errors...

The cause of the issue is the implicit crosscheck and cataloging being done by rman during restore and recover phase…

….

….

….

executing command: SET until clause

Starting restore at 09-JAN-2015 14:26:45

Starting implicit crosscheck backup at 09-JAN-2015 14:26:45

Crosschecked 16 objects

Finished implicit crosscheck backup at 09-JAN-2015 14:26:47

Starting implicit crosscheck copy at 09-JAN-2015 14:26:47

Finished implicit crosscheck copy at 09-JAN-2015 14:26:47

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +XXX/XXXXXX/AUTOBACKUP/2015_01_08/s_868464001.6080.868464003

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_8.1623.868492825

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_2_seq_5.1489.868492827

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_9.5858.868528837

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_10.5893.868532601

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_2_seq_6.5852.868536051

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_09/thread_1_seq_11.5800.868536649

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_1.6077.868463987

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_6110.536.868462613

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_1.6085.868465953

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_2.6141.868465963

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_6111.6443.868462613

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_3.6625.868465973

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_2.529.868465975

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_4.528.868465985

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_5.527.868465995

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_6.1982.868466007

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_3.5702.868466011

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_1_seq_7.531.868466023

File Name: +XXX/XXXXXX/ARCHIVELOG/2015_01_08/thread_2_seq_4.1877.868485729

released channel: t1

released channel: t2

released channel: t3

released channel: t4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 01/09/2015 14:26:50

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

RMAN> **end-of-file**

As you can see above, RMAN is cataloging the archive logs generated  post-resetlogs and getting the new incarnation information…

The way I’ve resolved the issue is as follows…

First delete the archive logs which are generated after the last resetlogs operation…this timestamp is after our recovery point...

So that’s basically the highlighted files above…

Try running the same process again…this time, rman couldn't catalog these new archive logs…and it worked perfectly fine.

This is especially useful if you need to restore and recover from a gold backup multiple times and don’t want to worry about multiple incarnations of the database…and try resetting them etc.

EM12c agent deployment fails with agent port passed by user is busy...

EM12c agent deployment fails with "ERROR: Agent Configuration Failed SEVERE: Agent port passed by user is busy and cannot proceed with the configuration. Pass a free port and retry the configuration.”
netstat -an doesnt show the port being in use and rerun of the deployment fails consistently with the same error.

In the end, it's the hostname mismatch issue where the hostname in '/etc/hosts' file is different to what's configured in '/etc/sysconfig/network'.

Once that's fixed by updating the entry in the network file, agent deployment succeeded.

This is another error that's not straight forward to resolve as nowhere as it gives an indication that there's a hostname conflict.

rconfig fails with ORA-25152: TEMPFILE cannot be dropped at this time

Was doing a single instance db to RAC conversion on a 11.2.0.3 cluster using rconfig and the process fails with ORA-25152 during "Setting TEMP tablespace" step.
Verified if the temp segments are in use but no processes are accessing.
The fix that finally worked is 

• drop the tempfile using 'alter database tempfile 'xxx/temp01.dbf' drop including datafiles;
• run rconfig and it worked fine this time
• add tempfile back using 'alter tablespace temp add tempfile size 4000M;

Did notice a few bug reports on Metalink however there's no workaround available so the above should do the trick if you happen run into this issue.

Connecting to RAC 11gR2 SCAN using SQLDeveloper

Here are a few options to connect to a 11gR2 RAC Database (using SCAN address) using SQLDeveloper, couldn't find this info handy anywhere so thought of posting it which could be of some help to developers/DBA's out there.

11gR2 RAC Connection to SCAN Hostname using JDBC Option

11gR2 RAC Connection to SCAN Hostname using SERVICE Name Option

11gR2 RAC Connection to SCAN Hostname using TNSNAMES Option

11gR2 Uninstall aka Deinstall procedure

Uninstall procedure has changed in 11gR2 and it's not the traditional OUI based uninstall anymore.

Oracle binaries include 'deinstall' utility which is by default available under $ORACLE_HOME/deinstall directory.
However it's recommended to download the latest one from OTN website which comes as a separate utility.

That's available under the 'see all' page of  http://bit.ly/bxzTd3 for your platform.


Once downloaded, it can be unzipped at a temporary location and can be used as below.

There's a checkonly option which checks the status of the Oracle software home configuration and recommended to run prior to actually removing the software.
Of course, it's always recommended to backup the binaries and inventory anyway.

./deinstall   -home   /u00/app/oracle/product/11.2.0/dbhome_1    -checkonly


Once the above runs successfully, run the command without the -checkonly option

./deinstall   -home   /u00/app/oracle/product/11.2.0/dbhome_1

and it cleanly removes the software and inventory entries which allows you perform another clean installation again!

However one issue here....the deinstall utility prompts for the listener (you cant avoid that) and it can stop the listener process and delete the listener.ora even if the listener is running from a different ORACLE_HOME and this can be an issue in a multi home environment.

The workaround for this at the moment is to "unset TNS_ADMIN" as per Metalink Note.1067622.1.