Source db name = ABC
Destination db name = DEF
Considering the following:
1. OS version and db version of both the dbs is same.
Taking RMAN backup on source database
Check whether the source DB is running in archivelog mode or not. run the below command
sudo su – <dbuser>
sqlplus / as sysdba
archive log list
Incase the source DB is running in archivelog mode, then do the below steps to take the online backup of source DB using rman:
Copy the below contents into a file /tmp/archivelogdbbkp.rman
RUN {
CROSSCHECK BACKUPSET;
CROSSCHECK BACKUP;
BACKUP CURRENT CONTROLFILE FORMAT '<directory_name>/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUPSET;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET FORMAT '<directory_name>/DB_BACKUP_%d_%T_%t_%s_%p.rman' DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE FORMAT '<directory_name>/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
}
Allocate more channel as per need.
Then run the below nohup command to initiate the rman backup
nohup rman target / cmdfile='/tmp/archivelogdbbkp.rman' log='/tmp/archivelogdbbkp.rman' &
Incase the source DB is running in NOARCHIVELOG mode, do the below steps to take the offline rman backup of source DB:
Take the rman backup of ABC database.
Copy the below contents into a file /tmp/noarchivelogdbbkp.rman
RUN{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
CROSSCHECK BACKUP;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUPSET;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET FORMAT '<directory_name>/DB_BACKUP_%d_%T_%t_%s_%p.rman' DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '<directory_name>/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
ALTER DATABASE OPEN;
}
Allocate more channel as per need.
Then run the below nohup command to initiate the rman backup
Then run the below nohup command to initiate the rman backup
nohup rman target / cmdfile='/tmp/noarchivelogdbbkp.rman' log='/tmp/noarchivelogdbbkp.log' &
Monitor the rman logfile and make sure that there is no ORA or RMAN error occurs in it.
Also, run the below sql query to check the backup progress.
Also, run the below sql query to check the backup progress.
set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) "Percent Complete",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) "Percent Complete",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/
Once the rman backup is complete, scp all the rman backup files onto DEF db server. Make sure the db user of DEF db server has got the read access on each of these rman backup files.
Run report schema on ABC db and save its output. it will be used while restoring this backup on DEF db.
Run report schema on ABC db and save its output. it will be used while restoring this backup on DEF db.
On DEF db:
select name,open_mode from v$database;
Take the backup of spfile and pfile
sudo su - <dbuser>cp $ORACLE_HOME/dbs/spfileDEF.ora /tmp/
sqlplus / as sysdba
show parameter pfile;
create pfile='/tmp/initDEF.ora' from spfile;
create pfile='/tmp/initDEF.ora' from spfile;
Shutdown all the DEF database instances.
Change the cluster_database parameter to false in $ORACLE_HOME/dbs/initDEF.ora. (it is only needed if db has more than 1 instances).
Change the cluster_database parameter to false in $ORACLE_HOME/dbs/initDEF.ora. (it is only needed if db has more than 1 instances).
startup and mount the database in exclusive mode
sqlplus / as sysdba
startup mount restrict exclusive pfile='$ORACLE_HOME/dbs/initDEF.ora';
sqlplus / as sysdba
startup mount restrict exclusive pfile='$ORACLE_HOME/dbs/initDEF.ora';
--Drop the DEF database
drop database;
drop database;
Now check that all space has been cleared. (datafiles, logfiles, and control files should be deleted.) Delete them manually if something left.
Make sure your datafiles filesystem or diskgroup of DEF db server has sufficient space to store data of Live database.
Make sure your datafiles filesystem or diskgroup of DEF db server has sufficient space to store data of Live database.
Change the name of the pfile as that of source db.
cp /tmp/initDEF.ora $ORACLE_HOME/dbs/initABC.ora
Change the value of db_name parameter to ABC in initABC.ora
Make sure
that name of the undo tablespace is same as the name in Live
Make sure
that the shared_pool_size is set like *.shared_pool_size.
change the ORACLE_SID env variable to ABC
setenv ORACLE_SID ABC
or
export ORACLE_SID=ABC
Start up the database in nomount state
Now catalog rman backup files.
catalog start with '<directory location where rman backup files are present on DEF db server>/';"
Check the report schema output earlier taken on ABC db. Get the file_ids and their size from it.
IF DEF db resides on file system based server to store the datafiles
we would need to allocate the datafiles into different FS on the destination server according the size of each FS. e.g. sapdata1 FS has a free space of 100GB and datafile 1, datafile 2 and datafile3 and datafile 4 are of in total size 95GB, then datafile 1 to 4 will be allocated in sapdata1 FS. similary the subsequent datafiles are allocated in the remaining FSs. once this calculation is done, the below rman script should be prepared for reference.
RUN
{
Allocate channel ch00 type disk;
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
SET NEWNAME FOR DATAFILE 1 TO '/oracleDEF/DEF/sapdata1/datafiles/system.270.831670025';
SET NEWNAME FOR DATAFILE 2 TO '/oracleDEF/DEF/sapdata1/datafiles/sysaux.271.831670051';
SET NEWNAME FOR DATAFILE 3 TO '/oracleDEF/DEF/sapdata1/datafiles/psapundo.267.831669899';
SET NEWNAME FOR DATAFILE 4 TO '/oracleDEF/DEF/sapdata1/datafiles/psapsr3.260.831669393';
SET NEWNAME FOR DATAFILE 5 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3.272.831670075';
SET NEWNAME FOR DATAFILE 6 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3702.259.831669277';
SET NEWNAME FOR DATAFILE 7 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3702.261.831669489';
{
Allocate channel ch00 type disk;
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
SET NEWNAME FOR DATAFILE 1 TO '/oracleDEF/DEF/sapdata1/datafiles/system.270.831670025';
SET NEWNAME FOR DATAFILE 2 TO '/oracleDEF/DEF/sapdata1/datafiles/sysaux.271.831670051';
SET NEWNAME FOR DATAFILE 3 TO '/oracleDEF/DEF/sapdata1/datafiles/psapundo.267.831669899';
SET NEWNAME FOR DATAFILE 4 TO '/oracleDEF/DEF/sapdata1/datafiles/psapsr3.260.831669393';
SET NEWNAME FOR DATAFILE 5 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3.272.831670075';
SET NEWNAME FOR DATAFILE 6 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3702.259.831669277';
SET NEWNAME FOR DATAFILE 7 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3702.261.831669489';
...
..
.
SET NEWNAME FOR DATAFILE 55 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.312.944651039';
SET NEWNAME FOR DATAFILE 56 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.313.946635317';
SET NEWNAME FOR DATAFILE 57 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.314.947955021';
SET NEWNAME FOR DATAFILE 58 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.315.950772363';
restore database;
switch datafile all;
SET NEWNAME FOR DATAFILE 55 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.312.944651039';
SET NEWNAME FOR DATAFILE 56 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.313.946635317';
SET NEWNAME FOR DATAFILE 57 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.314.947955021';
SET NEWNAME FOR DATAFILE 58 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.315.950772363';
restore database;
switch datafile all;
recover database;
}
allocate more channels as per need.
Now start the restore from the backup. Copy the above contents into a file /tmp/restore.rman
Then run the below nohup command to initiate the rman restore on DEF db server.
nohup rman target / cmdfile='/tmp/restore.rman' log='/tmp/restore.log' &"
Monitor the logfile /tmp/restore.log and make sure that there is no ORA or RMAN error occurs in it.
Also, run the below sql query to check the restore progress.
Also, run the below sql query to check the restore progress.
set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) ""Percent Complete"",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) ""Percent Complete"",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/
Once it is completed, check the /tmp/restore.log file and make sure that there is no ORA error came in it.
also, make sure that the all archivelogs present in the rman backup have been successfully applied. otherwise explicitily restore the archivelogs from the backupand recover them manually.
IF DEF db resides on diskgroup based server to store the datafiles,
normally there is only 1 diskgroup is present to store all the datafiles, so make sure that diskgroup has sufficient space to store all the datafiles of ABC database.
Say the diskgroup name is +DATA, the below rman script should be prepared for restore:
RUN
{
Allocate channel ch00 type disk;
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA';
SET NEWNAME FOR DATAFILE 2 TO '+DATA';
SET NEWNAME FOR DATAFILE 3 TO '+DATA';
SET NEWNAME FOR DATAFILE 4 TO '+DATA';
SET NEWNAME FOR DATAFILE 5 TO '+DATA';
...
..
.
SET NEWNAME FOR DATAFILE 55 TO '+DATA';
SET NEWNAME FOR DATAFILE 56 TO '+DATA';
SET NEWNAME FOR DATAFILE 57 TO '+DATA';
SET NEWNAME FOR DATAFILE 58 TO '+DATA';
restore database;
switch datafile all;
recover database;
}
allocate more channels as per need.
Now copy the above contents into a file /tmp/restore.rman
Then run the below nohup command to initiate the rman restore on DEF db server.
nohup rman target / cmdfile='/tmp/restore.rman' log='/tmp/restore.log' &"
Monitor the logfile /tmp/restore.log and make sure that there is no ORA or RMAN error occurs in it.
Also, run the below sql query to check the restore progress.
Also, run the below sql query to check the restore progress.
set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) ""Percent Complete"",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) ""Percent Complete"",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/
Once it is completed, check the /tmp/restore.log file and make sure that there is no ORA error came in it.
also,
make sure that the all archivelogs present in the rman backup have been
successfully applied. otherwise explicitily restore the archivelogs
from the backupand recover them manually.
now, check the status of redo log file using
select * from v$log;
if any of the redo log group is in CLEARING status, manually clear that redo log using below command:
alter database clear logfile group < group id of redo log group>;
Also, check the file location of all redo log files using below command:
select * from v$logfile;
Rename all the logfiles as per the FS structure or disk group of DEF database server using below command for each of those logfile:
alter database rename logfile
Then open the database with resetlogs option.
alter database open resetlogs;
