Friday 14 July 2017

Bug 18371441 - RMAN-11003 / .. / ORA-19755 from RMAN recover of file with missing BCT file

Rman duplicate failed with below error
Errors with log +FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265
Recovery interrupted!
Recovered data files to a consistent state at change 97471797575
Media Recovery failed with error 19755
Errors in file /oracle/product/diag/rdbms/JAYTST/JAYTST/trace/JAYTST_pr00_7863.trc:
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '+DATA/hr91prod/changetracking/rman_change_track.f'
ORA-17503: ksfdopn:2 Failed to open file +DATA/hr91prod/changetracking/rman_change_track.f
ORA-15173: entry 'JAYPROD' does not exist in directory '/'
ORA-10877 signalled during: alter database recover logfile '+FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265'...
Fri Jul 14 19:13:58 2017


Check any data files need recovery
select status, to_char(resetlogs_change#), resetlogs_time,   to_char(checkpoint_change#), to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*) from v$datafile_header group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
 order by status, checkpoint_change#, checkpoint_time ;
STATUS  TO_CHAR(RESETLOGS_CHANGE#)  RESETLOGS TO_CHAR(CHECKPOINT_CHANGE#)  CHECKPOINT_TIME        COUNT(*)
------- --------------------------  --------- ---------------------------- -------------------- ----------
ONLINE  1                           17-MAY-12 97471797575                   13-JUL-2017 09:44:46      100 - different (scn) not consistent

ONLINE  1                           17-MAY-12 97495746853                   13-JUL-2017 17:59:21       41

Note: -- Datafile(s) are at different checkpoint_change# (scn), so not consistent and need recovery

  Recovery failed due to BCT not available at target side
  Copy bct file from source to target
· At the source, check the location of BCT file
SELECT filename bct_file, status, bytes FROM   v$block_change_tracking;
BCT_FILE                                                            STATUS       BYTES
------------------------------------------------------------------ ---------- ----------
+DATA/JAYPROD/changetracking/rman_change_track.f                     ENABLED    147915264
1 row selected.

· Copy the BCT file from ASM to a file system
ASMCMD> cp +DATA/JAYPROD/changetracking/rman_change_track.f /tmp/.
copying +DATA/JAYPROD/changetracking/rman_change_track.f -> /tmp/./rman_change_track.f
ASMCMD>

· Transfer the file to the auxiliary site using scp, ftp etc
· Auxiliary site, copy the file from the file system back into ASM:
ASMCMD> cd +DATA/JAYPROD/changetracking
ASMCMD> ls
ASMCMD> cp /tmp/rman_change_track.f .
copying /tmp/rman_change_track.f -> +DATA/JAYPROD/changetracking/rman_change_track.f

  Recover until data files are consistent
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 97471797575 generated at 07/13/2017 09:44:46 needed for thread 2
ORA-00289: suggestion : +FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265
ORA-00280: change 97471797575 for thread 2 is in sequence #201199

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265
ORA-00279: change 97471797575 generated at 07/13/2017 09:28:24 needed for thread 1
ORA-00289: suggestion : +FRA/JAYTST/archivelog/2017_07_14/thread_1_seq_200803.2075.949341807
ORA-00280: change 97471797575 for thread 1 is in sequence #200803

Check datafile check point status:
STATUS  TO_CHAR(RESETLOGS_CHANGE#)               RESETLOGS TO_CHAR(CHECKPOINT_CHANGE#)              CHECKPOINT_TIME        COUNT(*)
------- ---------------------------------------- --------- ---------------------------------------- -------------------- ----------
ONLINE  1                                        17-MAY-12 97495852724                              13-JUL-2017 18:26:31        141

   All data files are in consistent , Open database with reset logs
(control file type is in backup , recreate it to make as current  to allow open with reset logs )
SQL> create pfile='/tmp/tmp1.ora' from spfile;
File created.

Edit DB name as JAYPROD.

SQL> shutdown immediate;
ORA-01507: database not mounted

SQL> startup mount pfile=/tmp/tmp1.ora;

SQL> alter database disable block change tracking;
Database altered.

SQL> alter database backup controlfile to trace as '/tmp/tmp2.sql'; 
Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Re-control file (second case remove all the rest of the character tmp2.sql)

CREATE CONTROLFILE REUSE DATABASE "JAYPROD" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 4144


oracle> sqlplus "/as sysdba"  
SQL> startup nomount pfile=/tmp/tmp1.ora
ORACLE instance started.

Total System Global Area 2138521600 bytes
Fixed Size                  2161024 bytes
Variable Size             905971328 bytes
Database Buffers         1207959552 bytes
Redo Buffers               22429696 bytes

SQL> @tmp2.sql

SQL> alter database open resetlogs;

  Present database name is JAYPROD need to rename to target as JAYTST

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO JAYTST;
Database altered.

SQL> shutdown immediate ;
SQL>startup  mount pfile=/tmp/tmp1.ora

nid TARGET=SYS DBNAME=JAYTST   (Change DB name as per requirement )

 (Sys password  manager)

  Before restart  and resetlogs  rename redolog  ****

Edit db name JAYTST in tmp1.ora

SQL> startup  mount pfile=/tmp/tmp1.ora

  Rename redologos   
SQL> select 'Alter database rename file ''' || member || ''' to '''||member||' '||''';' from v$logfile where member like '%+DATA%';
SQL> select 'Alter database rename file ''' || member || ''' to '''||member||' '||''';' from v$logfile where member like '%+FRA%';

SQL> alter database open resetlogs;

  Add temporary space
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/' REUSE;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/' REUSE;

No comments:

Post a Comment