ORACLE DATABASE Physical refresh from PROD to NON PROD || Restoring database from one system to another
Source db = ABC
Dest
db = DEF
Considering the
following:
1. the OS version and db
version of both the db is same.
2. the source db is
running in archivelog mode.
3. Backup mount points
on source is /backup and on destination is /rman_backup
Taking RMAN backup on
source database
Check whether the source
DB is running in archivelog mode or not. run the below command
su – oracle;
sqlplus / as sysdba
archive log list
In case 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
Copy the below contents into a file /tmp/archivelogdbbkp.rman
RUN {
CROSSCHECK BACKUPSET;
CROSSCHECK BACKUP;
BACKUP CURRENT CONTROLFILE FORMAT
'/backup/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;
ALLOCATE CHANNEL ch04 TYPE DISK;
ALLOCATE CHANNEL ch05 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET FORMAT
'/backup/DB_BACKUP_%d_%T_%t_%s_%p.rman' DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE FORMAT
'/backup/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
}
Allocate more or less
channel as per need.
Then run the below nohup
command to initiate the rman backup from OS prompt
nohup rman target /
cmdfile='/tmp/archivelogdbbkp.rman' log='/tmp/archivelogdbbkp.log' &
In case 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.
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 '/backup/DB_BACKUP_%d_%T_%t_%s_%p.rman'
DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
ALTER DATABASE OPEN;
}
Allocate more or less
channel as per need.
Then run the below nohup
command to initiate the rman backup from OS prompt.
nohup rman target /
cmdfile='/tmp/noarchivelogdbbkp.rman' log='/tmp/noarchivelogdbbkp.log' &
Monitor the rman log file
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.
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
/
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.
rman target /
report schema;
exit
|
Restore rman backup on destination
database (DEF)
Ensure the name of the database to
be refreshed.
sudo su - oracle; --
on DEF
sqlplus / as sysdba
select name, open_mode
from v$database;
create pfile from
spfile;
exit
Take the backup of spfile and
pfile
cp
$ORACLE_HOME/dbs/spfileDEF.ora
$ORACLE_HOME/dbs/spfileDEF.ora_date
Shutdown the DEF database
instances (stop all DB instances if it is running in cluster).
sqlplus / as sysdba
Shu immediate;
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 only on one instance. (you can drop the database by starting
database in mount state with spfile as well, but since we have here renamed
the spfile, we will be using pfile).
sqlplus / as sysdba
startup mount restrict exclusive
pfile='$ORACLE_HOME/dbs/initDEF.ora';
Drop the DEF 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.
Change the name of the pfile as
that of source db.
cp
$ORACLE_HOME/dbs/initDEF.ora $ORACLE_HOME/dbs/initABC.ora
change the value of db_name parameter to ABC in initABC.ora file.
Please note: If there is SID
mentioned in pfile before undo tablespace
than that need to be changed as well. Replace the SID from source to
target database.
Now set the environment as per the
PROD database on NON PROD DB.
export ORACLE_SID=ABC
sqlplus / as sysdba
startup nomount
pfile='$ORACLE_HOME/dbs/initABC.ora';
exit
Now restore the control files.
rman target / -- Please
note the DB name it should be ABC
restore controlfile from
'/rman_backup/controlfile_backup_piece.ctl'; --Please choose the
latest controlfile to restore here
alter database mount;
Now catalog the backup pieces:
catalog start with
'/rman_backup/';
Check the report schema output
earlier taken on ABC db.
Considering the DEF db using FS to
store the datafiles.
Now we would need to allocate the
datafiles into different FS on the destination server according the size of
each FS. e.g. datafiles1 FS has a free space of 100GB and datafile 1,
datafile 2 are of in total size 60GB, then datafile 1 to 2 will be allocated
in datafiles1 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;
Allocate channel ch03 type disk;
Allocate channel ch04 type disk;
Allocate channel ch05 type disk;
Allocate channel ch06 type disk;
SET NEWNAME FOR DATAFILE 1 TO
'/oracleDEF/DEF/datafiles1/%b;
SET NEWNAME FOR DATAFILE 2 TO '/oracleDEF/DEF/datafiles1/%b;
SET NEWNAME FOR DATAFILE 3 TO
'/oracleDEF/DEF/datafiles2/%b;
SET NEWNAME FOR DATAFILE 4 TO
'/oracleDEF/DEF/datafiles2/%b;
SET NEWNAME FOR DATAFILE 5 TO
'/oracleDEF/DEF/datafiles3/%b;
SET NEWNAME FOR DATAFILE 6 TO
'/oracleDEF/DEF/datafiles3/%b;
SET NEWNAME FOR DATAFILE 7 TO
'/oracleDEF/DEF/datafiles4/%b;
SET NEWNAME FOR DATAFILE 8 TO
'/oracleDEF/DEF/datafiles4/%b;
SET NEWNAME FOR DATAFILE 9 TO
'/oracleDEF/DEF/datafiles5/%b;
SET NEWNAME FOR DATAFILE 10 TO
'/oracleDEF/DEF/datafiles5/%b;
restore database;
switch datafile all;
recover database;
}
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.
su - oracle -- on DEF server
export ORACLE_SID=ABC
sqlplus / as sysdba
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
/
Once the restore is complete it
will update the controlfile with new datafiles with the help of "switch
datafile all" command and then it will perform database recovery by
applying archivelogs, also if you don't want to use rman for recovery or need
to apply archivelog separately in that case , Open a new session and start
the recovery from sqlplus
export
ORACLE_SID=ABC
sqlplus / as sysdba
recover database
using backup controlfile until cancel;
in case the archive logs are
requested in the sqlplus recovery session and the archivelogs are not
restored, restore them manually in another rman session and recover those
archivelogs in the recovery session.
Note: Thread can be mentioned if
RAC, if it is not a RAC system, no need to mention the thread.
RUN
{
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;
set archivelog
destination to '/rman_backup/arch/';
restore archivelog
from logseq=8619 until logseq=8632 thread=2;
}
post restoring the archivelog, you
may start the recovery again, Apply the archivelogs to the point you want to
recover your database.
Since we have not taken the redo
log backup here, hence this recovery is to the point or incomplete media
recovery.
In case of incomplete media
recovery database needs to be open with resetlog option.
Since our DB is already in mount
state and recovery is done, perform the below steps.
export
ORACLE_SID=ABC
sqlplus / as sysdba
alter database open
resetlogs;
Now your database is in open state
(if error occurs you need to fix the error accordingly).
Now we need the database to be
named as DEF not ABC, so we will be converting our database in DEF.
for that first take the trace
backup of controlfile.
export
ORACLE_SID=ABC
sqlplus / as sysdba
alter database
backup controlfile to trace as '/tmp/controle_trace.ctl';
exit
now logout and relogin on server
via oracle user and check and ensure that env is set for DEF database.
su - oracle
. oraenv
DEF
now open the
/tmp/controle_trace.ctl file and select the one portion from it, which would
be looking like below:
CREATE
CONTROLFILE REUSE DATABASE "ABC"
RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2920
LOGFILE
GROUP 1 (
'/oracleDEF/DEF/datafiles1/redo/redo01a.log',
'/oracleDEF/DEF/datafiles2/redo/redo01b.log'
) SIZE 512M
BLOCKSIZE 512,
GROUP 2 (
'/oracleDEF/DEF/datafiles1/redo/redo02a.log',
'/oracleDEF/DEF/datafiles2/redo/redo02b.log'
) SIZE 512M
BLOCKSIZE 512,
DATAFILE
'/oracleDEF/DEF/datafiles1/system01.dbf',
'/oracleDEF/DEF/datafiles1/sysaux01.dbf',
'/oracleDEF/DEF/datafiles2/undotbs01.dbf',
'/oracleDEF/DEF/datafiles2/tab1.dbf',
'/oracleDEF/DEF/datafiles3/tab2.dbf',
'/oracleDEF/DEF/datafiles3/tab3.dbf',
'/oracleDEF/DEF/datafiles4/tab4.dbf',
'/oracleDEF/DEF/datafiles4/tab5.dbf',
'/oracleDEF/DEF/datafiles5/tab6.dbf',
'/oracleDEF/DEF/datafiles5/tab7.dbf'
CHARACTER SET
AL32UTF8
;
From above set, change the 2
entries:
REUSE --> SET
ABC --> DEF
so it will look like below:
CREATE CONTROLFILE
SET DATABASE "DEF" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2920
LOGFILE
GROUP 1 (
'/oracleDEF/DEF/datafiles1/redo/redo01a.log',
'/oracleDEF/DEF/datafiles2/redo/redo01b.log'
) SIZE 512M
BLOCKSIZE 512,
GROUP 2 (
'/oracleDEF/DEF/datafiles1/redo/redo02a.log',
'/oracleDEF/DEF/datafiles2/redo/redo02b.log'
) SIZE 512M
BLOCKSIZE 512,
DATAFILE
'/oracleDEF/DEF/datafiles1/system01.dbf',
'/oracleDEF/DEF/datafiles1/sysaux01.dbf',
'/oracleDEF/DEF/datafiles2/undotbs01.dbf',
'/oracleDEF/DEF/datafiles2/tab1.dbf',
'/oracleDEF/DEF/datafiles3/tab2.dbf',
'/oracleDEF/DEF/datafiles3/tab3.dbf',
'/oracleDEF/DEF/datafiles4/tab4.dbf',
'/oracleDEF/DEF/datafiles4/tab5.dbf',
'/oracleDEF/DEF/datafiles5/tab6.dbf',
'/oracleDEF/DEF/datafiles5/tab7.dbf'
CHARACTER SET
AL32UTF8
;
save the same content in a file
say '/tmp/control.sql'
now start the DEF database
instance in nomount state with the help of pfile:
sqlplus / as sysdba
startup nomount
pfile='$ORACLE_HOME/dbs/initDEF.ora';
/tmp/control.sql will be creating
controlfile so,
sqlplus / as sysdba
@/tmp/control.sql
Now your controlfile is created
and database will get mount automatically, now open your database but again
with resetlog option will be required.
sqlplus / as sysdba
alter database open
resetlogs;
Now shutdown your database and start
you instance with spfile:
sqlplus / as sysdba
Shu immediate
startup;
verify your database details:
select
name,open_mode,database_role from v$database;
Now since your database physical
restoration completed, you may start instances on other node if this
is a RAC system.
|
Comments
Post a Comment