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: 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: 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.