EasyReliableDBA: DB REFRESH USING RMAN DB RESTORATION & RECOVERY STEPS-REAL TIME

DB REFRESH USING RMAN DB RESTORATION & RECOVERY STEPS-REAL TIME

-------------------------------------------------------------------------------

Database Refresh from production to UAT Database using RMAN backup and restoration method

Current Environment : Single node Production and UAT Database

Backup Method : RMAN Backup on TAPE

Prerequisite:

Step 1) Check with the requestor if the UAT database backup is required

Step 2) During the refresh activity the UAT database should not be in use by the application team, take a confirmation e-mail from the requestor

Step 3) Check the version for both source and target database.

Step 4) Check the size of the mount point on both the target and source database

if the mount point size is not same and request platform team to add the space to the mount point of the target database.

Step 5) Take backup of DB Link using metadata and DB link

mkdir -p /backup/restoration

CREATE DIRECTORY dumpdblinkref AS '/backup/restoration/dbrefresh';

SQL> grant READ on directory IMPORT_DIR to dumpdblinkref;

SQL> grant WRITE on directory EXPORT_DIR to dumpdblinkref;

set pagesize 1000

set linesize 120

column directory_name format a25

column directory_path format a70

select directory_name directory_name, directory_path directory_path

from dba_directories order by directory_name;

DATABASE LINKS EXPORT BACKUP

--------------------------------------------------

vi dblink_1MAY2023.par

directory=dumpdblinkref

dumpfile=dblink_1MAY2023.dmp

full=y

include=DB_LINK

logfile=expdp_dblink_1MAY2023.log

expdp \"/ as sysdba\" parfile=dblink_1MAY2023.par

set lines 2000

set pages 2000

set owner for a13

set username for a12

col host for a50

col username for a15

col DB_LINK for a30

col CREATED for a30

select * from dba_db_links ;

set long 9999999

set pagesize 0

set linesize 120

select dbms_metadata.get_ddl('DB_LINK','&db_name','&owner') from dual;

Step 6) Take backup of DB users

set pagesize 500

select 'alter user '||name||' identified by values '''||password||''';' from user$ ;

select * from user$

select * from dba_users

Take detail from UAT Database before drop/refresh:

----------------------------------------------

SQL> set pages 1999

select host_name from v$instance;

select name,open_mode from v$database;

col value for a50

sho parameter spfile

select NAME " DATA FILE "from V$datafile order by 1;

select name " Controlfile " from V$controlfile order by 1;

select MEMBER " LOGFILE " from v$logfile order by 1;

select name " TEMPFILE " from v$tempfile order by 1;

archive log list

Implementation

--------------------------

Step1 : Login on UAT database and take necessary backup

A) Login on UAT Server and set DB enviroment accordingly

{orauat@easyserver:/db1/oraredo/easyDB}pwd

/db1/oraredo/easyDB

{orauat@easyserver:/db1/oraredo/easyDB}cd

-rwxr-xr-x 1 orauat oinstall 163 Nov 16 2020 easyDB_uat.env

-rwxr-xr-x 1 orauat oinstall 523 Dec 17 2020 easyDB_12c.env

-rwxr-xr-x 1 orauat oinstall 195 Sep 15 2021 easyDB_restore.env

{orauat@easyserver:/home/orauat}cat easyDB_12c.env

export ORACLE_BASE=/oracle02

export ORACLE_HOME=/oracle02/app/orauat/product/12.2.0.1/easyDB_home

export ORACLE_SID=easyDB_uat

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/local/lib:/lib64:/lib:/usr/lib64:/usr/lib

export LD_LIBRARY_PATH

PATH=$PATH:$HOME/bin:/usr/local/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch; export PATH

TEMP=/tmp

TMPDIR=/tmp

export TEMP TMPDIR

export PS1='\u@\h:\w[$ORACLE_SID] >'

stty erase ^?

#export TZ="MUT-4:00"

#export TNS_ADMIN=

{orauat@easyserver:/home/orauat}. easyDB_12c.env

{orauat@easyserver:/home/orauat}s

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 14 01:47:47 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

USER is "SYS"

NAME UQ_NAME INSTANCE VERSION OPEN_MODE HOST_NAME ROLE DATABASE_STATUS STATUS LOGINS UPTIME

---------- --------------- ---------- ------------ --------------- --------------- ---------- ----------------- ---------- ---------- --------------------

EASYDB_UAT easyDB_uat easyDB_uat 12.2.0.1.0 READ WRITE easyserver PRIMARY ACTIVE OPEN ALLOWED 14-NOV-2022 01:37:23

INSTANCE_NAME HOST_NAME STATUS

---------------- --------------- ----------

easyDB_uat easyserver OPEN

SQL> select name, open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

easyDB_UAT READ WRITE

B.Create Pfile on UAT Database for backup:

SQL> create pfile='/tmp/initEASYDB_UAT05NOV22.ora' from spfile;

C. Create trace control file:

alter database backup controlfile to trace as '/tmp/EASYDB_UAT_control_1Jun2021.trc';

STEP: 2: Drop UAT Database

drop UAT – easyserver database(easyDBdb_uat) or clean file system related to datafiles ,redo files and control files for restoration

SQL> Shut Immediate;

SQL > STARTUP MOUNT RESTRICT

SQL>DROP DATABASE.

STEP:3:

=======

2) set environment using easyDB_restore.env . it will set environment for easyDB (not easyDBdb_uat)

{orauat@easyserver:/home/orauat}cat easyDB_restore.env

export ORACLE_BASE=/oracle02

export ORACLE_HOME=/oracle02/app/orauat/product/12.2.0.1/easyDB_home

export ORACLE_SID=easyDB

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

{orauat@easyserver:/home/orauat}. easyDB_restore.env

pfile location

---------------

/oracle02/app/orauat/product/12.2.0.1/easyDB_home/dbs

2) start database in unmount stage and check database name. it should be easyDB

SQL>startup nomount

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string easyDB

SQL>

STEP:4:

=======

Restore Controlfile on UAT Database:

===================

exit sqlprompt and restore control file from RMAN full backup

{orauat@easyserver:/home/orauat/DBBACKUP}cat restore_control_easyDB.sh

#!/bin/ksh

rman log=/home/orauat/DBBACKUP/easyDB_restore_$(date +"%Y%m%d-%H%M%S").log<< EOF

connect target /

run

{

allocate channel 'dev_0' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=easyDB,OB2BARLIST=SWIDB001_ORA_EASYDB_POST_EOD_DAILY)';

restore controlfile from 'EASYSERVER_ORA_EASYDB_POST_EOD_DAILY<easyDB_216629:1116224562:1>.dbf';

release channel 'dev_0';

}

EOF

{orauat@easyserver:/home/orauat/DBBACKUP}

Note: Once controlfile restoration is done then we need to start database restoration process.

STEP:5:

======

put the database in mount state. and run below restore command

{orauat@easyserver:/home/orauat}cd DBBACKUP

{orauat@easyserver:/home/orauat/DBBACKUP}ls -ltr

total 96

-rwxrwxrwx 1 orauat oinstall 1159 Oct 16 15:54 Restore_database_EASYDB_16oct22.sh

-rw------- 1 orauat oinstall 149 Oct 16 15:55 nohup.out

-rw-r--r-- 1 orauat oinstall 26142 Oct 16 15:56 easyDB_datafile_restore_20221016-155556.log

{orauat@easyserver:/home/orauat/DBBACKUP}cat Restore_database_EASYDB_16oct22.sh

#!/bin/ksh

rman log=/home/orauat/chan/easyDB_datafile_restore_$(date +"%Y%m%d-%H%M%S").log<< EOF

connect target /

run

{

allocate channel 'dev_0' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_1' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_2' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_3' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_4' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_5' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_6' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

allocate channel 'dev_7' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';

restore database ;

switch datafile all;

release channel 'dev_0';

release channel 'dev_1';

release channel 'dev_2';

release channel 'dev_3';

release channel 'dev_4';

release channel 'dev_5';

release channel 'dev_6';

release channel 'dev_7';

}

EOF

NOTE: Once restoration process is done then we need to check restoration logfile and verify the restoration process status

select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

STEP:6:

======

{orauat@easyserver:/home/orauat/DBBACKUP}cat Recover_database_151122.sh

#!/bin/ksh

rman log=/home/orauat/DBBACKUP/easyDB_datafile_recover_$(date +"%Y%m%d-%H%M%S").log<< EOF

connect target /

run

{

allocate channel 'dev_0' type 'sbt_tape'