If you're read some of my older stories on this blog you'll know I've had some interesting battles with flashback over the past couple of years... don't get me wrong i think it's (arguably) the best feature introduced over the past 10 years (i'm talking about all the different flavours of flashback with that comment).
Today i hit a whole series of random errors discovered a minor new (well new to me) syntax to a very old command and managed for a second time with flashback to bring a database back from the dead.
Read on for the saga....
The series of events started with me just trying to do a simple flashback database, so shutdown, startup mount and then this:
SQL> flashback database to restore point DRYRUN1;
Flashback complete.
Normal so far - but then i tried this to being the db open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
not so good
lets check the alert log
*************************************************************
Unable to allocate flashback log of 43056 blocks from
current recovery area of size 322122547200 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
Cannot open flashback thread due to an error when trying
to switch into a new flashback log.
Database mounted in Exclusive Mode
Lost write protection disabled
Ping without log force is disabled.
Completed: ALTER DATABASE MOUNT
2015-10-06 09:43:29.018000 +01:00
flashback database to restore point DRYRUN1
ORA-16433 signalled during: flashback database to restore point DRYRUN1...
2015-10-06 09:43:42.109000 +01:00
alter database open
Errors in file /oracle/admin/DBNAME/diag/rdbms/DBNAME/DBNAME/trace/DBNAME_ora_3274.trc:
ORA-38760: This database instance failed to turn on flashback database
ORA-38760 signalled during: alter database open...
If we scroll a little bit further back we see this
*************************************************************
Unable to allocate flashback log of 43056 blocks from
current recovery area of size 322122547200 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
2015-10-06 09:33:17.114000 +01:00
Errors in file /oracle/admin/DBNAME/diag/rdbms/DBNAME/DBNAME/trace/DBNAME_arc1_17003.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 322122547200 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
OK - so it's just out os space - not a big problem to fix...?
Lets add some more and try again
SQL> alter system set db_recovery_file_dest_size=350G;
System altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
hmm - odd lets try noresetlogs
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
right.......
Lets try turning flashback off completely
SQL> alter database flashback off;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcvcrv_fb_inc_mismatch], [2127],
[1894563532], [892373920], [2124], [645743042], [889783889], [], [], [], [], []
This is looking pretty bad - lets ask
Lets follow the steps in that then...
Well step 1 was turn off flashback - so that's already done - next step is to backup controlfile to trace (with the noresetlogs option on the end - which was a new one on me - i'd been manually deleting all the extra lines for years!)
SQL> alter database backup controlfile to trace noresetlogs;
alter database backup controlfile to trace noresetlogs
*
ERROR at line 1:
ORA-16433: The database or pluggable database must be opened in read/write
mode.
Now that's not in the MOS notes......
What to do then - seem to be running out of options.....
I decided to drop the restore point as we seemed to have flashed back ok earlier just not been able to open the db - maybe getting rid of the flashback logs will help?
SQL> drop restore point dryrun1;
Restore point dropped.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcfckde-mismatch-rlc], [892373920],
[889783889], [], [], [], [], [], [], [], [], []
Process ID: 10753
Session ID: 488 Serial number: 21
Well it's changed the error but we're still nowhere.....
What options do i have left? The MOS note seems to imply that recreating the controlfile will fix this and i can still do this even without the trace file - i can just handcraft it - not something you do every day - but certainly possible - lets give that a try.....
First i track down the actual binary controlfile and run these two commands to generate me a list of the files the create controlfile command needs to contain
strings o1_mf_bts8jbgh_.ctl |grep .dbf | sort -u
gives me datafiles and this gives me logfiles
strings o1_mf_bts8jbgh_.ctl |grep onlinelog | sort -u
I then do a backup controlfile to trace on another database just to give me a template to work with - i then just paste in the datafile/logfile/db name and I'm ready to manually recreate the controlfiles
Here is a selection of errors i got when getting the content incorrect (included just to show that the command can be quite forgiving.....most of these were completely new to me)
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00357: too many members specified for log file, the maximum is 2
ORA-01517: log member:
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01224: group number in header 3 does not match GROUP 1
ORA-01517: log member:
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 204800 (blocks), but should match header
2097152
When i finally got everything correct i ran this
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 60
MAXINSTANCES 1
MAXLOGHISTORY 4672
LOGFILE
GROUP 3 (
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/onlinelog/o1_mf_3_btsn335r_.log',
'/oracle/DBNAME/oradata/DBNAME/recovery_area/DBNAME/onlinelog/o1_mf_3_btsn399g_.log'
) SIZE 1G BLOCKSIZE 512,
GROUP 4 (
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/onlinelog/o1_mf_4_btsn4fxg_.log',
'/oracle/DBNAME/oradata/DBNAME/recovery_area/DBNAME/onlinelog/o1_mf_4_btsn4mxo_.log'
) SIZE 1G BLOCKSIZE 512,
GROUP 5 (
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/onlinelog/o1_mf_5_btsn4w5r_.log',
'/oracle/DBNAME/oradata/DBNAME/recovery_area/DBNAME/onlinelog/o1_mf_5_btsn5274_.log'
) SIZE 1G BLOCKSIZE 512
DATAFILE
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_aif_btsbh902_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_aif_data_btsbk9fo_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_aif_defa_btsbkm3c_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_align_btsbkoj4_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_aligne_btsbh1to_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_common_btsbkztq_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_common_d_btsbkn9b_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_common_i_c02b4r3m_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_crd_btsbknw7_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_crd_data_btsbkynw_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_crd_defa_btsbkwtf_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_data01_btsbkw86_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_data01_btsbl9p1_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_data02_btsbkz7w_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_data03_btsbk8t3_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_eis_dba_btsbl0fz_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_eisdba_btsdqg8r_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_index01_btsbky1r_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_netaarch_btsbkxfv_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_sysaux_bts8jhts_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_system_bts8jf72_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_system_bttjmdn7_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_sys_undo_bts8jk15_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_tools01_btsbkqw6_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_users_btsbkppn_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_zainet_btsbk874_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_zn_extra_btsbkb14_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_zn_extra_btsbkmp7_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_zn_extra_btsbkp3p_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_zn_extra_btsbkq9n_.dbf',
'/oracle/DBNAME/oradata/DBNAME/oradata/DBNAME/datafile/o1_mf_zn_extra_btsbkvn7_.dbf'
CHARACTER SET WE8ISO8859P1
;
Control file created.
then this
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
And it's only worked - back from the dead yet again.
The lesson here is never give up i guess (well and check your FRA isn't full before you start messing about.....)
Comments
Post a Comment