Watch this video to understand concept
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
## Restore RMAN backup To another New DB Server ##
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Primary Database Information
*************************************
Host : delhi.localdomain (192.168.56.103)
DB_NAME : prod
Disk_Group : /disk01, /FRA
Standby Database Information
*************************************
Host : bangalore.localdomain (192.168.56.104)
DB_NAME : prod
DB_UNIQUE_NAME: prod_sty
Disk_Group : /disk02, /FRA
========================================================
Basic pre-check before deploying Active Standby database server.
------------------------------------------------------------------------------------------------
(i) Primary Server, Instance, Databases details.
********************************************************
NAME INSTANCE_NAME HOST_NAME DATABASE_ROLE LOG_MODE LOGINS OPEN_MODE STARTUP_TIME VERSION
--------- ---------------- -------------------- -------------------- --------------- ---------- --------------- -------------------- -----------
PROD prod delhi.localdomain PRIMARY ARCHIVELOG ALLOWED READ WRITE 2017:07:31 22:54:15 11.2.0.4.0
(ii) Archive log Should be enable & Flash Recovery Area Details.
******************************************************************************
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /FRA/prod
db_recovery_file_dest_size big integer 4182M
recovery_parallelism integer 0
(iii) File-System Details of Target Side.
************************************************
[oracle@bangaloe ~]$ df -h /disk02
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_target-lv_root
86G 22G 60G 27% /
[oracle@bangaloe ~]$ df -h /FRA
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_target-lv_root
86G 22G 60G 27% /
Step: 1
==============================================
Set environments and start rman for prod ( @Primary Site).
--------------------------------------------------------------------------------
[oracle@source ~]$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle
[oracle@source ~]$
[oracle@source ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:03:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3010714195)
Step: 2
============================================================================
Now check old backup details, but we need new backup with autocontrolfile ( @Primary Site).
------------------------------------------------------------------------------------------------------------------------------------
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
Step: 3
===========================================================================
Check controlfile autobakcup details, It's should ON, if Not then enable it. ( @Primary Site).
----------------------------------------------------------------------------------------------------------------------------------
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name prod are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
Step: 4
===============================================
Take full databae backup with archivelog ( @Primary Site).
----------------------------------------------------------------------------------
RMAN> backup database plus archivelog;
Starting backup at 05-AUG-17
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=950913403
input archived log thread=1 sequence=5 RECID=2 STAMP=950913405
input archived log thread=1 sequence=6 RECID=3 STAMP=950915922
input archived log thread=1 sequence=7 RECID=4 STAMP=950915925
input archived log thread=1 sequence=8 RECID=5 STAMP=950918291
input archived log thread=1 sequence=9 RECID=6 STAMP=950918867
input archived log thread=1 sequence=10 RECID=7 STAMP=950918898
input archived log thread=1 sequence=11 RECID=8 STAMP=950919116
input archived log thread=1 sequence=12 RECID=9 STAMP=950919118
input archived log thread=1 sequence=13 RECID=10 STAMP=950919119
input archived log thread=1 sequence=14 RECID=16 STAMP=950919173
input archived log thread=1 sequence=15 RECID=18 STAMP=950919788
input archived log thread=1 sequence=16 RECID=20 STAMP=950919790
input archived log thread=1 sequence=17 RECID=22 STAMP=950919791
input archived log thread=1 sequence=18 RECID=24 STAMP=950919793
input archived log thread=1 sequence=19 RECID=26 STAMP=950919794
input archived log thread=1 sequence=20 RECID=28 STAMP=950919795
input archived log thread=1 sequence=21 RECID=30 STAMP=950919796
input archived log thread=1 sequence=22 RECID=32 STAMP=950919796
input archived log thread=1 sequence=23 RECID=34 STAMP=950919796
input archived log thread=1 sequence=24 RECID=36 STAMP=950919797
input archived log thread=1 sequence=25 RECID=38 STAMP=950919797
input archived log thread=1 sequence=26 RECID=41 STAMP=950973779
input archived log thread=1 sequence=27 RECID=42 STAMP=951224601
input archived log thread=1 sequence=28 RECID=43 STAMP=951224605
input archived log thread=1 sequence=29 RECID=44 STAMP=951224875
channel ORA_DISK_1: starting piece 1 at 05-AUG-17
channel ORA_DISK_1: finished piece 1 at 05-AUG-17
piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_annnn_TAG20170805T130755_drbxgnbo_.bkp tag=TAG20170805T130755 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 05-AUG-17
Starting backup at 05-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/disk01/prod/prod/system01.dbf
input datafile file number=00002 name=/disk01/prod/prod/sysaux01.dbf
input datafile file number=00005 name=/disk01/prod/prod/example01.dbf
input datafile file number=00003 name=/disk01/prod/prod/undotbs01.dbf
input datafile file number=00004 name=/disk01/prod/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-AUG-17
channel ORA_DISK_1: finished piece 1 at 05-AUG-17
piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_nnndf_TAG20170805T130803_drbxgwpf_.bkp tag=TAG20170805T130803 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 05-AUG-17
Starting backup at 05-AUG-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=45 STAMP=951224968
channel ORA_DISK_1: starting piece 1 at 05-AUG-17
channel ORA_DISK_1: finished piece 1 at 05-AUG-17
piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_annnn_TAG20170805T130928_drbxkk2x_.bkp tag=TAG20170805T130928 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-AUG-17
Starting Control File and SPFILE Autobackup at 05-AUG-17
piece handle=/FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-AUG-17
RMAN> exit
Step: 5
===========================================
Creare some directiories on target side ( @Target Site).
----------------------------------------------------------------------------
[oracle@target ~]$ mkdir -p /FRA/prod
[oracle@target ~]$ mkdir -p /disk02/prod
[oracle@target ~]$ mkdir -p /u01/app/oracle/admin/prod/adump
Step: 6
===================================================================
Tranfer backupset,autobackup & password file to target location ( @Primary Site).
-------------------------------------------------------------------------------------------------------------------
[oracle@source prod]$ pwd
/FRA/prod/prod
[oracle@source prod]$ ls -lrth
total 16K
drwxr-x--- 2 oracle oinstall 4.0K Aug 2 16:30 flashback
drwxr-x--- 5 oracle oinstall 4.0K Aug 5 13:03 archivelog
drwxr-x--- 3 oracle oinstall 4.0K Aug 5 13:07 backupset
drwxr-x--- 3 oracle oinstall 4.0K Aug 5 13:09 autobackup
[oracle@source prod]$ scp -r backupset autobackup oracle@192.168.56.102:/FRA/prod/prod
oracle@192.168.56.102's password:
o1_mf_annnn_TAG20170805T130755_drbxgnbo_.bkp 100% 67MB 67.3MB/s 00:01
o1_mf_annnn_TAG20170805T130928_drbxkk2x_.bkp 100% 57KB 56.5KB/s 00:00
o1_mf_nnndf_TAG20170805T130803_drbxgwpf_.bkp 100% 1085MB 60.3MB/s 00:18
o1_mf_s_951224970_drbxklvm_.bkp 100% 9888KB 9.7MB/s 00:00
[oracle@source prod]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@source dbs]$ ls -lrth
total 19M
-rw-r--r--. 1 oracle oinstall 2.8K May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Jul 26 12:46 lkPROD
-rw-r-----. 1 oracle oinstall 2.5K Jul 26 14:22 orapwprod
-rw-rw----. 1 oracle oinstall 1.6K Aug 1 00:35 hc_prod.dat
-rw-r-----. 1 oracle oinstall 3.5K Aug 1 00:36 spfileprod.ora
-rw-r-----. 1 oracle oinstall 9.4M Aug 1 00:54 snapcf_prod.f
-rw-r----- 1 oracle oinstall 24 Aug 1 21:15 lkprod
-rw-r----- 1 oracle oinstall 1.5K Aug 1 21:17 orapwprod
-rw-rw---- 1 oracle oinstall 1.6K Aug 5 13:03 hc_prod.dat
-rw-r----- 1 oracle oinstall 3.5K Aug 5 13:04 spfileprod.ora
-rw-r----- 1 oracle oinstall 9.6M Aug 5 13:09 snapcf_prod.f
[oracle@source dbs]$ scp orapwprod oracle@192.168.56.102:/u01/app/oracle/product/11.2.0/db_1/dbs/
oracle@192.168.56.102's password:
orapwprod 100% 1536 1.5KB/s 00:00
Step: 7
=============================================================================
On target server start database via rman and restore spfile from autobackup ( @Target Site).
-------------------------------------------------------------------------------------------------------------------------------------
[oracle@target ~]$ vi /etc/oratab
[oracle@target ~]$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle
[oracle@target dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:18:43 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount force;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initprod.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
RMAN> restore spfile from '/FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp';
Starting restore at 05-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 05-AUG-17
RMAN> exit
Step: 8
==========================================================================================
Come to target SQL*Plus promte and create pfile from spfile and do some modification acording to target server. ( @Target Site).
--------------------------------------------------------------------------------------------------------------------------------------------------------------
[oracle@target ~]$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle
[oracle@target ~]$ cd $ORACLE_HOME/dbs
[oracle@target dbs]$ ls -lrth
-rw-r----- 1 oracle oinstall 3.5K Aug 5 13:28 spfileprod.ora
[oracle@target dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 5 13:30:41 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile from spfile;
File created.
SQL> exit
[oracle@target dbs]$ ls -lrth
-rw-r----- 1 oracle oinstall 3.5K Aug 5 13:28 spfileprod.ora
-rw-r--r-- 1 oracle oinstall 1.4K Aug 5 13:30 initprod.ora
[oracle@target dbs]$ vi initprod.ora
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.control_files='/disk02/prod/prod/control01.ctl','/disk02/prod/prod/control02.ctl'
*.db_recovery_file_dest='/FRA/prod'
:wq
Step: 9
======================================================================================
Remove spfile from dbs location, down previous stared instnace & start it again via pfile ( @Target Site).
------------------------------------------------------------------------------------------------------------------------------------------------------
[oracle@target dbs]$ rm -rf spfileprod.ora
[oracle@target dbs]$ ps -ef | grep pmon | grep -v grep
oracle 2437 1 0 13:19 ? 00:00:00 ora_pmon_prod
[oracle@target dbs]$
[oracle@target dbs]$ !s
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 5 13:37:09 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate
ORA-01507: database not mounted
[oracle@target dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 5 13:38:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
SQL> create spfile from pfile;
File created.
SQL> exit
Step: 10
=====================================================================================
Now start rman and restore controlfile from autobackup & put database in mount stage ( @Target Site).
------------------------------------------------------------------------------------------------------------------------------------------------------
[oracle@target dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:38:27 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (not mounted)
RMAN> restore controlfile from '/FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp';
Starting restore at 05-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/disk02/prod/prod/control01.ctl
output file name=/disk02/prod/prod/control02.ctl
Finished restore at 05-AUG-17
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
Step: 11
========================================================
Now tell your newly stared db to your backup location ( @Target Site).
-------------------------------------------------------------------------------------------------
RMAN> catalog start with '/FRA/prod/prod/';
Starting implicit crosscheck backup at 05-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 05-AUG-17
Starting implicit crosscheck copy at 05-AUG-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 05-AUG-17
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp
searching for all files that match the pattern /FRA/prod/prod/
List of Files Unknown to the Database
=====================================
File Name: /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.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: /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp
Step: 12
=============================================
Finally restore & recovery your database ( @Target Site).
-------------------------------------------------------------------------------
RMAN> run {
2> SET NEWNAME FOR DATAFILE 1 TO "/disk02/prod/prod/system01.dbf";
3> SET NEWNAME FOR DATAFILE 2 TO "/disk02/prod/prod/sysaux01.dbf";
4> SET NEWNAME FOR DATAFILE 3 TO "/disk02/prod/prod/undotbs01.dbf";
5> SET NEWNAME FOR DATAFILE 4 TO "/disk02/prod/prod/users01.dbf";
6> SET NEWNAME FOR DATAFILE 5 TO "/disk02/prod/prod/example01.dbf";
7> restore database;
8> switch datafile all;
9> recover database;
10> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 05-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /disk02/prod/prod/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /disk02/prod/prod/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /disk02/prod/prod/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /disk02/prod/prod/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /disk02/prod/prod/example01.dbf
channel ORA_DISK_1: reading from backup piece /FRA/prod/prod/backupset/2017_08_05/o1_mf_nnndf_TAG20170805T130803_drbxgwpf_.bkp
channel ORA_DISK_1: piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_nnndf_TAG20170805T130803_drbxgwpf_.bkp tag=TAG20170805T130803
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 05-AUG-17
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=951227735 file name=/disk02/prod/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=951227735 file name=/disk02/prod/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=951227735 file name=/disk02/prod/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=951227735 file name=/disk02/prod/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=951227735 file name=/disk02/prod/prod/example01.dbf
Starting recover at 05-AUG-17
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30
channel ORA_DISK_1: reading from backup piece /FRA/prod/prod/backupset/2017_08_05/o1_mf_annnn_TAG20170805T130928_drbxkk2x_.bkp
channel ORA_DISK_1: piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_annnn_TAG20170805T130928_drbxkk2x_.bkp tag=TAG20170805T130928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/FRA/prod/prod/archivelog/2017_08_05/o1_mf_1_30_drc080cv_.arc thread=1 sequence=30
channel default: deleting archived log(s)
archived log file name=/FRA/prod/prod/archivelog/2017_08_05/o1_mf_1_30_drc080cv_.arc RECID=46 STAMP=951227736
unable to find archived log
archived log thread=1 sequence=31
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/05/2017 13:55:37
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 31 and starting SCN of 1044850
RMAN> exit
******************************************
Note: the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.
**********************************************
Step: 13
===================================================================================
Now try to start your database with resetlogs and will get below error, then rename your redologs. ( @Target Site).
------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/disk01/prod/prod/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
SQL> alter database rename file '/disk01/prod/prod/redo01.log' TO '/disk02/prod/prod/redo01.log';
Database altered.
SQL> alter database rename file '/disk01/prod/prod/redo02.log' TO '/disk02/prod/prod/redo02.log';
Database altered.
SQL> alter database rename file '/disk01/prod/prod/redo03.log' TO '/disk02/prod/prod/redo03.log';
Database altered.
Step: 14
========================================================================================
Again try to start your database with resetlogs and will get below error, Clear your one unclear redologfile. ( @Target Site).
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/disk02/prod/prod/redo01.log'
SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;
GROUP# THREAD# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 52428800 512 1 NO CLEARING_CURRENT 1044850 05-AUG-17 2.8147E+14
3 1 52428800 512 1 YES CLEARING 1044763 05-AUG-17 1044850 05-AUG-17
2 1 52428800 512 1 YES CLEARING 1041102 05-AUG-17 1044763 05-AUG-17
SQL> alter database clear logfile '/disk02/prod/prod/redo01.log';
Database altered.
Step: 14
===========================================================================
Again try to start your database with resetlogs and you are done here..!!. ( @Target Site).
---------------------------------------------------------------------------------------------------------------------------------
SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;
GROUP# THREAD# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 52428800 512 1 NO CURRENT 1044850 05-AUG-17 2.8147E+14
3 1 52428800 512 1 YES CLEARING 1044763 05-AUG-17 1044850 05-AUG-17
2 1 52428800 512 1 YES CLEARING 1041102 05-AUG-17 1044763 05-AUG-17
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> exit
Pradeep Kumar
+91-9972201100
