Managed Recovery – interrupted….

So today I had the good fortune of watching some fun unfold.  Actually it was my fault as well, so a good lesson learned.  I have a DataGuard primary environment on RAC/ASM and a non-RAC/filesystem standby.  In migrating to a new storage array, I had created a new disk group and created a tablespace in it.  With a couple of tables created, I was able to work out our performance boost was about 6x – nice bump really.

Anyone care to guess what I had not done?

So, DataGuard managed recovery stopped and the alert.log indicated that it was having issues translating the data files that live in the new ASM disk group.  Of course!  Having not added the temporary +STEVE ASM disk group to DB_FILE_NAME_CONVERT, it had no idea what to do with that file, so marks it as missing and halted managed recovery.  Drat.

What could I have done differently?  Well, exactly that – add a translation for my +STEVE disk group so that the standby would know what to do with it.

Media Recovery Waiting for thread 1 sequence 91792 (in transit)
Thu Feb 12 12:25:06 2015
RFS[14]: Selected log 8 for thread 1 sequence 91793 dbid -742562477 branch 790859783
Thu Feb 12 12:25:06 2015
Archived Log entry 18838 added for thread 1 sequence 91792 ID 0xd411be04 dest 1:
Thu Feb 12 12:25:07 2015
RFS[13]: Selected log 12 for thread 2 sequence 100403 dbid -742562477 branch 790859783
Thu Feb 12 12:25:08 2015
Archived Log entry 18839 added for thread 2 sequence 100402 ID 0xd411be04 dest 1:
Thu Feb 12 12:25:11 2015
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_1_91792_bfsrhkwm_.arc
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_2_100402_bfsrhmch_.arc
Errors in file /app/oracle/diag/rdbms/xx/xx/trace/xx_pr00_9217.trc:
ORA-01119: error in creating database file '+steve'
ORA-17502: ksfdcre:4 Failed to create file +steve
ORA-15001: diskgroup "STEVE" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
File #34 added to control file as 'UNNAMED00034'.
Originally created as:
'+STEVE/xx/datafile/steve.256.871474827'
Recovery was unable to create the file as:
'+steve'
Errors with log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_2_100402_bfsrhmch_.arc
MRP0: Background Media Recovery terminated with error 1274
Errors in file /app/oracle/diag/rdbms/xx/xx/trace/xx_pr00_9217.trc:
ORA-01274: cannot add datafile '+STEVE/xx/datafile/steve.256.871474827' - file could not be created
Recovery interrupted!
Recovered data files to a consistent state at change 376240959889
Thu Feb 12 12:25:18 2015
MRP0: Background Media Recovery process shutdown (xx)

So the solution is actually pretty simple.  Set STANDBY_FILE_MANAGEMENT to MANUAL (you do have it set to AUTO, right?), add the new file, reset file management and proceed.  All will be well, as you will see.

SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database create datafile 34 as '/app/oracle/product/11.2.0/dbhome_1/dbs/steve.dbf'
Database altered.
SQL> alter system set standby_file_management=auto;
System altered.

Note that I chose a rather dumb place to put my file.  If I were really going to keep that tablespace around, I’d have placed the file in the correct place with the rest of the datafiles, however I had already dropped the tablespace (including contents/datafiles) so I know this was just a temporary thing.

Let’s restart managed recovery.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ORA-16136 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL...
Thu Feb 12 15:23:00 2015
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (xx)
Thu Feb 12 15:23:00 2015
MRP0 started with pid=44, OS id=14290
MRP0: Background Managed Standby Recovery process started (xx)
 started logmerger process
Thu Feb 12 15:23:05 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_2_100402_bfsrhmch_.arc
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_1_91792_bfsrhkwm_.arc
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Thu Feb 12 15:23:34 2015
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_1_91793_bfsro9nq_.arc
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_2_100403_bfsrnhbk_.arc

And finally it chews through the archived redo until it gets to my drop tablespace point (and beyond).

Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_2_100444_bfstmvk1_.arc
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_1_91808_bfstn3ym_.arc
Recovery deleting file #34:'/app/oracle/product/11.2.0/dbhome_1/dbs/steve.dbf' from controlfile.
Deleted file /app/oracle/product/11.2.0/dbhome_1/dbs/steve.dbf
Recovery dropped tablespace 'STEVE'
Thu Feb 12 15:28:37 2015
Media Recovery Log /mnt/rman/xx/xx/archivelog/2015_02_12/o1_mf_2_100445_bfstx83t_.arc

Crisis over.  Well, inconvenience really.  A little more thought might have been a good idea here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s