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;