By William Sescu
Sometimes you might run into situations where the controlfile does not represent the backups and archivelogs correctly, because of a mismatch of the control_file_record_keep_time and the RMAN retention. The controlfile has non circular and a circular records. Non circular are e.g. database information, redo threads, datafiles and so on. These non circular records don’t age out, however, they can be reused, e.g. when a tablespace is dropped. The circular records are e.g. the log history, archived logs, backupsets, datafile copies and so on. These records can age out. So, when you have a control_file_record_keep_time of 7 days and a RMAN recovery window of 14 days, then you obviously have a mismatch here. In 11gR2, Oracle stores 37 different record types in the control file, which can be check with:
SELECT type FROM v$controlfile_record_section ORDER BY 1;
12cR1 stores 41 different record types, where the AUXILIARY DATAFILE COPY, MULTI INSTANCE REDO APPLY, PDB RECORD and PDBINC RECORD was added. In 12cR2 there are even more. The TABLESPACE KEY HISTORY record type was added, so you end up with 42 different record types in 12cR2.
If RMAN needs to add new backup set or archive log record to the control file, any records that expired as per the control_file_record_keep_time parameter are overwritten. But coming back to my issue. My controlfile is out of sync with the recovery catalog and in some situation you can’t correct it anymore, even with delete force commands or alike, and you end up with error like the following:
ORA-19633: control file record 8857 is out of sync with recovery catalog
There might be other solutions to fix it, however, I want to have a clean control file and so I am recreating it manually. However, I don’t want to open the DB with resetlogs.
The high level steps to get this done are
- Disable everything that might interfere with your action e.g. Fast Start Failover, Broker and so on
- Adjust your control_file_record_keep_time to a higher value
- Create the controlfile to trace
- Unregister from RMAN catalog
- Shutdown immediate and re-create the controlfile
- Re-catalog your backups and archivelogs
- Re-register into the RMAN catalog
Ok, let’s get started and disable fast start failover first. We don’t want that the observer to kick in and do any nasty stuff during my action.
DGMGRL> show configuration;
Configuration - DBIT121
Protection Mode: MaxAvailability
Members:
DBIT121_SITE1 - Primary database
DBIT121_SITE2 - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 2 seconds ago)
DGMGRL> disable fast_start failover;
Disabled.
As a next step, I increase the control_file_record_keep_time to a much higher time. The formula is usually CONTROL_FILE_RECORD_KEEP_TIME = retention period + level 0 backup interval + 1. Meaning that with a retention period of 24 days and a weekly level 0 backup, it would be 24+7+1, so at least 32. But I don’t care if my controlfile is 20MB in size 30MB, so I set it directly to 72 days.
-- Primary SQL> alter system set control_file_record_keep_time=72; System altered. -- Standby SQL> alter system set control_file_record_keep_time=72; System altered.
The next important step is to create a trace of the controlfile, which can be adjusted manually later on, depending on your needs. Beforehand, I specify a tracefile identifier, so that I easily spot my trace file in the DIAG destination.
SQL> alter session set tracefile_identifier='control'; Session altered. SQL> alter database backup controlfile to trace noresetlogs; Database altered. oracle@dbidg01:/u01/app/oracle/diag/rdbms/dbit121_site1/DBIT121/trace/ [DBIT121] ls -rlt | grep control -rw-r----- 1 oracle oinstall 101 Feb 24 09:10 DBIT121_ora_25050_control.trm -rw-r----- 1 oracle oinstall 9398 Feb 24 09:10 DBIT121_ora_25050_control.trc oracle@dbidg01:/u01/app/oracle/diag/rdbms/dbit121_site1/DBIT121/trace/ [DBIT121] mv DBIT121_ora_25050_control.trc /u01/app/oracle/admin/DBIT121/create/recreate_controlfile.sql
Let’s take a look at the control file trace which was created. It contains nearly everything that we need. Some parts might have to be adjusted, and some parts do not work at all or have to be done in a different way, but we will see later. But in general it is a very good starting point to get the job done.
oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] cat recreate_controlfile.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="DBIT121_SITE1"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("DBIT121_SITE2")'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=AUTO
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=DBIT121_SITE2
--
-- LOG_ARCHIVE_DEST_2='SERVICE=DBIT121_SITE2'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR AFFIRM NOVERIFY ASYNC=0'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=DBIT121_SITE2'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBIT121" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnop9_.log',
'/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnq4o_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo42k_.log',
'/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo43q_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppn86_.log',
'/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppngb_.log'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 (
-- '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log',
-- '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t89m_.log'
-- ) SIZE 50M BLOCKSIZE 512,
-- GROUP 5 (
-- '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj3b_.log',
-- '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj8m_.log'
-- ) SIZE 50M BLOCKSIZE 512,
-- GROUP 6 (
-- '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tp52_.log',
-- '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tpb4_.log'
-- ) SIZE 50M BLOCKSIZE 512,
-- GROUP 7 (
-- '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twdq_.log',
-- '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twkt_.log'
-- ) SIZE 50M BLOCKSIZE 512
DATAFILE
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_system_d4fjt03j_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_sysaux_d4fjrlvs_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_undotbs1_d4fjvtd1_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_example_d4fjz1fz_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_users_d4fjvqb1_.dbf'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 14 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DISK');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RMAN OUTPUT','TO KEEP FOR 32 DAYS');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE1'' CONNECT IDENTIFIER ''DBIT121_SITE1''');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE2'' CONNECT IDENTIFIER ''DBIT121_SITE2''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' PARMS ''SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u99/backup/DBIT121)''');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 10
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u02/oradata/DBIT121_SITE1/changetracking/o1_mf_dbx3wgqg_.chg' REUSE;
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_temp_d4fjxn8l_.tmp'
SIZE 206569472 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t89m_.log'
TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log';
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj3b_.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj8m_.log'
TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_5_dbx3tj3b_.log';
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tp52_.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tpb4_.log'
TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_6_dbx3tp52_.log';
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twdq_.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twkt_.log'
TO '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_7_dbx3twdq_.log';
I am also stopping the broker to avoid any side effects and afterwards I unregister the database from the RMAN catalog. I will re-create it later on with the clean entries.
-- primary SQL> alter system set dg_broker_start=false; System altered. oracle@dbidg01:/home/oracle/ [DBIT121] rman target sys/manager catalog rman/rman@rman Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 24 09:16:17 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DBIT121 (DBID=172831209) connected to recovery catalog database recovery catalog schema release 12.02.00.01. is newer than RMAN release RMAN> unregister database; database name is "DBIT121" and DBID is 172831209 Do you really want to unregister the database (enter YES or NO)? YES database unregistered from the recovery catalog RMAN>
The next step is very important. We need to shutdown the DB cleanly, either with normal or immediate. Afterwards, I create a copy of the current controlfiles. You never know, it is always good to have another fallback.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> oracle@dbidg01:/home/oracle/ [DBIT121] cd /u02/oradata/DBIT121_SITE1/controlfile/ oracle@dbidg01:/u02/oradata/DBIT121_SITE1/controlfile/ [DBIT121] mv o1_mf_d4fjws55_.ctl o1_mf_d4fjws55_.ctl.old oracle@dbidg01:/u02/oradata/DBIT121_SITE1/controlfile/ [DBIT121] cd /u03/fast_recovery_area/DBIT121_SITE1/controlfile/ oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/controlfile/ [DBIT121] mv o1_mf_d4fjwsgr_.ctl o1_mf_d4fjwsgr_.ctl.old
Now we can startup nomount, and recreate our control from scratch. It is very important that you specify REUSE and NORESETLOGS here.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1325400064 bytes
Fixed Size 2924112 bytes
Variable Size 436208048 bytes
Database Buffers 872415232 bytes
Redo Buffers 13852672 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DBIT121" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnop9_.log',
'/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_1_d4fpnq4o_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo42k_.log',
'/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_2_d4fpo43q_.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppn86_.log',
'/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_3_d4fppngb_.log'
19 ) SIZE 50M BLOCKSIZE 512
DATAFILE
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_system_d4fjt03j_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_sysaux_d4fjrlvs_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_undotbs1_d4fjvtd1_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_example_d4fjz1fz_.dbf',
'/u02/oradata/DBIT121_SITE1/datafile/o1_mf_users_d4fjvqb1_.dbf'
CHARACTER SET AL32UTF8
27 ;
Control file created.
SQL>
Now we can configure the RMAN persistent settings like retention and so on.
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 14 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DISK');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RMAN OUTPUT','TO KEEP FOR 32 DAYS');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE1'' CONNECT IDENTIFIER ''DBIT121_SITE1''');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DB_UNIQUE_NAME','''DBIT121_SITE2'' CONNECT IDENTIFIER ''DBIT121_SITE2''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' PARMS ''SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u99/backup/DBIT121)''');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 10
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
The next step is to the re-create the incarnation table. This might fail with a recursive SQL error if you use the SQL provided in the trace file. Just use REGISTER PHYSICAL LOGFILE instead of REGISTER LOGFILE and then it works.
-- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_1_%u_.arc'; SQL> ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_142_dbzv31hq_.arc'; ALTER DATABASE REGISTER LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_142_dbzv31hq_.arc' * ERROR at line 1: ORA-00604: error occurred at recursive SQL level SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_142_dbzv31hq_.arc'; Database altered.
Because I have shutdown the database cleanly, there is no need to do any recovery and I can continue to enable the block change tracking file, open the database, and add my tempfile back to the database.
SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required SQL> ALTER SYSTEM ARCHIVE LOG ALL; System altered. SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/oradata/DBIT121_SITE1/changetracking/o1_mf_dbx3wgqg_.chg' REUSE; Database altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/DBIT121_SITE1/datafile/o1_mf_temp_d4fjxn8l_.tmp' 2 SIZE 206569472 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; Tablespace altered.
Regarding the Standby Redo logs, the easiest is to remove the old ones, and simply recreate them afterwards, because you can’t add them back as long as they have Oracle managed file names.
SQL> select * from v$standby_log; no rows selected SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log' SIZE 50M BLOCKSIZE 512 REUSE; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log' SIZE 50M BLOCKSIZE 512 REUSE * ERROR at line 1: ORA-01276: Cannot add file /u02/oradata/DBIT121_SITE1/onlinelog/o1_mf_4_dbx3t840_.log. File has an Oracle Managed Files file name. -- delete standby redo logs oracle@dbidg01:/u01/app/oracle/admin/DBIT121/create/ [DBIT121] cd /u02/oradata/DBIT121_SITE1/onlinelog/ oracle@dbidg01:/u02/oradata/DBIT121_SITE1/onlinelog/ [DBIT121] ls -l total 358428 -rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_1_d4fpnop9_.log -rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_2_d4fpo42k_.log -rw-r----- 1 oracle oinstall 52429312 Feb 24 09:47 o1_mf_3_d4fppn86_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_4_dbx3t840_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_5_dbx3tj3b_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_6_dbx3tp52_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_7_dbx3twdq_.log oracle@dbidg01:/u02/oradata/DBIT121_SITE1/onlinelog/ [DBIT121] rm o1_mf_4_dbx3t840_.log o1_mf_5_dbx3tj3b_.log o1_mf_6_dbx3tp52_.log o1_mf_7_dbx3twdq_.log oracle@dbidg01:/u02/oradata/DBIT121_SITE1/onlinelog/ [DBIT121] cd /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/ oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/ [DBIT121] ls -l total 358428 -rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_1_d4fpnq4o_.log -rw-r----- 1 oracle oinstall 52429312 Feb 24 09:42 o1_mf_2_d4fpo43q_.log -rw-r----- 1 oracle oinstall 52429312 Feb 24 09:47 o1_mf_3_d4fppngb_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_4_dbx3t89m_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_5_dbx3tj8m_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_6_dbx3tpb4_.log -rw-r----- 1 oracle oinstall 52429312 Feb 23 09:55 o1_mf_7_dbx3twkt_.log oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/onlinelog/ [DBIT121] rm o1_mf_4_dbx3t89m_.log o1_mf_5_dbx3tj8m_.log o1_mf_6_dbx3tpb4_.log o1_mf_7_dbx3twkt_.log -- recreate standby redo logs SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 50M BLOCKSIZE 512; Database altered. SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 50M BLOCKSIZE 512; Database altered. SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 6 SIZE 50M BLOCKSIZE 512; Database altered. SQL> alter database add STANDBY LOGFILE THREAD 1 GROUP 7 SIZE 50M BLOCKSIZE 512; Database altered.
Don’t forget to enable Flashback as well, if your DataGuard is running in Max availability mode.
SQL> alter database flashback on; Database altered.
Now we need to recatalog all our backups and archivelogs again.
oracle@dbidg01:/u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/ [DBIT121] rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 24 09:50:16 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DBIT121 (DBID=172831209) RMAN> catalog recovery area; using target database control file instead of recovery catalog searching for all files in the recovery area List of Files Unknown to the Database ===================================== File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_140_dbzswh06_.arc File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_141_dbzsxpv5_.arc File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx641px_.flb File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx642pf_.flb File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dby398lz_.flb File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbymcg20_.flb File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbyyg1r0_.flb File Name: /u03/fast_recovery_area/DBIT121_SITE1/controlfile/o1_mf_d4fjwsgr_.ctl.old File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64pz6_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64q0b_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s0z_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s3n_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnsnf_TAG20170223T090856_dbx65kmx_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_ncnnf_TAG20170223T090856_dbx65lnt_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090923_dbx65mto_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypdc_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypfp_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpysqh_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy2f_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy56_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnsnf_TAG20170224T080812_dbzpzqnz_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_ncnnf_TAG20170224T080812_dbzpzqop_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080841_dbzpzskt_.bkp Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_140_dbzswh06_.arc File Name: /u03/fast_recovery_area/DBIT121_SITE1/archivelog/2017_02_24/o1_mf_1_141_dbzsxpv5_.arc File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64pz6_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090854_dbx64q0b_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s0z_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnndf_TAG20170223T090856_dbx64s3n_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_nnsnf_TAG20170223T090856_dbx65kmx_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_ncnnf_TAG20170223T090856_dbx65lnt_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_23/o1_mf_annnn_TAG20170223T090923_dbx65mto_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypdc_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpypfp_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080806_dbzpysqh_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy2f_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnndf_TAG20170224T080812_dbzpyy56_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_nnsnf_TAG20170224T080812_dbzpzqnz_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_ncnnf_TAG20170224T080812_dbzpzqop_.bkp File Name: /u03/fast_recovery_area/DBIT121_SITE1/backupset/2017_02_24/o1_mf_annnn_TAG20170224T080841_dbzpzskt_.bkp List of Files Which Were Not Cataloged ======================================= File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx641px_.flb RMAN-07529: Reason: catalog is not supported for this file type File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbx642pf_.flb RMAN-07529: Reason: catalog is not supported for this file type File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dby398lz_.flb RMAN-07529: Reason: catalog is not supported for this file type File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbymcg20_.flb RMAN-07529: Reason: catalog is not supported for this file type File Name: /u03/fast_recovery_area/DBIT121_SITE1/flashback/o1_mf_dbyyg1r0_.flb RMAN-07529: Reason: catalog is not supported for this file type File Name: /u03/fast_recovery_area/DBIT121_SITE1/controlfile/o1_mf_d4fjwsgr_.ctl.old RMAN-07519: Reason: Error while cataloging. See alert.log. List of files in Recovery Area not managed by the database ========================================================== File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_4_dbzwt72f_.log RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_5_dbzwtgl3_.log RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_6_dbzwtn04_.log RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter File Name: /u03/fast_recovery_area/DBIT121_SITE1/onlinelog/o1_mf_7_dbzwtvc7_.log RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter number of files not managed by recovery area is 4, totaling 200.00MB RMAN>
We are nearly done. We just need to enable the Data Guard broker again and enable fast start failover.
SQL> alter system set dg_broker_start=true; System altered. SQL> alter system archive log current; System altered. DGMGRL> enable fast_start failover; Enabled. DGMGRL> show configuration; Configuration - DBIT121 Protection Mode: MaxAvailability Members: DBIT121_SITE1 - Primary database DBIT121_SITE2 - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 21 seconds ago) DGMGRL> validate database 'DBIT121_SITE2'; Database Role: Physical standby database Primary Database: DBIT121_SITE1 Ready for Switchover: Yes Ready for Failover: Yes (Primary Running)
Re-register the database into the RMAN catalog.
oracle@dbidg01:/home/oracle/ [DBIT121] rman target sys/manager catalog rman/rman@rman Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 24 09:57:34 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DBIT121 (DBID=172831209) connected to recovery catalog database recovery catalog schema release 12.02.00.01. is newer than RMAN release RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN>
Ready. That’s it. In case your Standby Controlfile is not correct as well (was not in my case), you can now simply create a new standby controlfile on the primary and move it to the standby like documented in the following MOS note (Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1).
Conclusion
As a last resort, sometimes it is needed to recreate the controlfile manually, but in case you have all the online redo logs and your datafiles in place, you can do it with noresetlogs. And take care that your RMAN retention is always higher than your control file record keep time.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)