RMAN Backup on Standby Database and Restoration on new server

 Overview

   In this document, I will show you the steps of backing up the oracle database using Rman on the active standby database, and then I will copy that backup set to another server for the restoration.

1. Database Backup on Standby

1.1 Consistent means, that the backup is having the datafiles and the related archived redologs, so that it can be opened with this backup. This done on the Primary database by using :

RMAN> backup database plus archivelog;

1.2 Write a shell script to switch log files from Primary database

#!/bin/ksh

#
# Change <passwd>
#        <primary_db>
#

sqlplus -s "sys/<passwd>@<primary_db> as sysdba" <<EOF
alter system archive log current;
exit
EOF

% chmod 755 /usr/local/bin/logswitch.sh

1.3 Rman backup on active data guard database

% rman target / catalog <un/pw@catalog_db>

Rman> Configure controlfile autobackup on  ;--------------> This would ensure Controlfile auto backup
RMAN> backup database plus archivelog delete input;

      host "/usr/local/bin/logswitch.sh";

      backup archivelog all delete input;

2. Copy the backup set to restore in new server

2.1 startup nomount

 startup nomount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb3.ora';

2.2 Restore control file from backup piece

restore controlfile from '/data/2022_02_03/o1_mf_s_1095663611_jzpj98qo_.bkp';

2.3 Catalog backup set and restore/recover database

 run

{

catalog start with '/data/2022_02_03/' noprompt;

Restore database;

SWITCH DATAFILE ALL;

Recover database;

}

2.4 Force Finish the Recovery

SQL> RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Media recovery complete.

2.5 Activate Standby DB

SQL> alter database activate standby database;

2.6 Open Database

SQL> alter database open;

Database altered.

oracle enterprise manager OEM13c installation

 OVERVIEW

in this article I will guide you how to install OEM 13c on redhat enterprise linux 7.8.

1. software requirement

we can download OEM 13c software from :

https://www.oracle.com/enterprise-manager/downloads/cloud-control-downloads.html

em13300_linux64-2.zi

em13300_linux64-3.zi

em13300_linux64-4.zi

em13300_linux64-5.zi

em13300_linux64-6.zi

em13300_linux64.bin

   2. Server requirement

OS	redhat:enterprise_linux:7.8
RAM	12 GB
CPU	8
HDD	80 GB
PACKAGES	-packages for OMS make-3.82-24.el7.x86_64 libXtst-1.2.3-1.el7.x86_64 binutils-2.27-43.base.el7_8.1.x86_64 gcc-4.8.5-39.el7.x86_64 libaio-0.3.109-13.el7.x86_64 glibc-common-2.17-307.el7.1.x86_64 libstdc++-4.8.5-39.el7.x86_64 sysstat-10.1.5-19.el7.x86_64 glibc-2.17-307.el7.1.x86_64 glibc-devel-2.17-307.el7.1.x86_64 glibc-devel-2.17-307.el7.1.i686
set to correct value	cat /etc/sysctl.conf | grep shmmax 4398046511104
	cat /etc/sysctl.conf | grep port_range net.ipv4.ip_local_port_range = 11000 65000

    3. DATABASE REQUIREMENT

 

 You need to create a new database for below configuration.

Change parameter optimizer_adaptive_features to false	SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 5 11:16:26 2020 Copyright (c) 1982, 2014, Oracle.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter optimizer_adaptive_features NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_adaptive_features          boolean     FALSE SQL> alter system set optimizer_adaptive_features=FALSE scope=both; System altered.
Comment # in oracle bash_profile	# export PATH export TMP=/tmp export TMPDIR=$TMP #export ORACLE_UNQNAME=orauat #export ORACLE_BASE=/u01/app/oracle #export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1 #export ORACLE_SID=orauat export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
Make required directories	mkdir -p /data/oracle/product/bip/config/ mkdir -p /data/oracle/product/bip/cluster/
Change file permission	chmod 775 em13300_linux64.bin
As Oracle user run installer	./em13300_linux64.bin

4. INSTALLATION STEPS

the installation progress would take few hours to complete.

5 . CONCLUSTION

    basically OEM 13c installation is just few simple steps, we just need to ensure that the Database is the fresh installation, if it is an old database we don't recommend to use that, otherwise you will encounter some error during EM configuration steps. beside that, OEM installation directories must also be clean up if you need to install OEM software again. Good luck for your installation steps!

Redo logs switch too frequently

1. Observation

I do select :

SELECT dat,num FROM
(SELECT TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24:Mi') dat,
COUNT(1) num
FROM V$LOGHIST
WHERE trunc(FIRST_TIME) = trunc ( SYSDATE )
GROUP BY TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24:Mi')
HAVING COUNT(1) >= 8
ORDER BY dat desc;

As a result, I 've found that redo logs switch more than 8 every minutes,

2. Get information about DB configuration

set markup html on spool on

SPOOL logswitch.HTML

set echo on

set pagesize 30;

select systimestamp from dual;

select * from v$version;

select name,LOG_MODE from v$database;

select * from v$instance_recovery;

select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;

show parameter log_buffer;

show parameter log_checkpoint_interval;

show parameter log_checkpoint_timeout;

show parameter fast_start_mttr_target;

show parameter archive_lag_target;

show parameter filesystem

show parameter disk

select name,value from v$parameter where name like '%log_archive%' and value <> 'enable';

select name from v$controlfile;

select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member

from v$logfile lf, v$log lg

where lg.group# = lf.group#

order by 1, 2;

select to_char(first_time,'YYYY-MON-DD') "Date", to_char(first_time,'DY') day,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",

to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",

to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",

to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23" ,

count(*) Total

from v$log_history

group by to_char(first_time,'YYYY-MON-DD'), to_char(first_time,'DY')

order by to_date(to_char(first_time,'YYYY-MON-DD'),'YYYY-MON-DD')

/

select to_char(COMPLETION_TIME,'DD/MON/YYYY') Day,

trunc(sum(blocks*block_size)/1048576/1024,2) "Size(GB)",count(sequence#) "Total Archives"

from

(select distinct sequence#,thread#,COMPLETION_TIME,blocks,block_size from v$archived_log)

group by to_char(COMPLETION_TIME,'DD/MON/YYYY')

order by to_date(to_char(COMPLETION_TIME,'DD/MON/YYYY'),'DD/MON/YYYY')

;

spool off

set markup html off spool off

3. we got output

SQL> select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member

2 from v$logfile lf, v$log lg

3 where lg.group# = lf.group#

4 order by 1, 2;

GROUP# MB STATUS ARC MEMBER

1 50 ACTIVE YES /data01/oradata/TWCMS/redo01.log

1 50 ACTIVE YES /data01/oradata/TWCMS/redo01a.log

2 50 ACTIVE YES /data01/oradata/TWCMS/redo02a.log

2 50 ACTIVE YES /data01/oradata/TWCMS/redo02.log

3 50 ACTIVE YES /data01/oradata/TWCMS/redo03.log

3 50 ACTIVE YES /data01/oradata/TWCMS/redo03a.log

4 50 ACTIVE YES /data01/oradata/TWCMS/redo04.log

4 50 ACTIVE YES /data01/oradata/TWCMS/redo04a.log

5 50 ACTIVE YES /data01/oradata/TWCMS/redo05.log

5 50 ACTIVE YES /data01/oradata/TWCMS/redo05a.log

6 50 CURRENT NO /data01/oradata/TWCMS/redo06a.log

6 50 CURRENT NO /data01/oradata/TWCMS/redo06.log

As a result, redo logs file is very small (50 mb).

4. action to be taken

Reviewed the uploaded files and found the most of the redo logs are always ACTIVE and the no of log switches are at particular times.

This is due to small redo log file size 50MB. I would request you to resize redo log file size to 500MB and monitor your database.

gather stat

begin
DBMS_STATS.GATHER_DICTIONARY_STATS();
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
dbms_stats.gather_schema_stats(OWNNAME=>'XXX',
estimate_percent=>100,
METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
CASCADE=>true, DEGREE=>16);
end;

check indexes status

CREATE OR REPLACE PROCEDURE P$check_ind_status
IS
  v_html  clob   :=' ';
  v_n     NUMBER := 0;

BEGIN
   --1) let loop to get email html
   v_html := '<h4>INDEXES</h4>'
               ||'<table style="border:1px solid lightgrey">'
               ||'<tr style="background-color:#85C1E9; font-family:verdana; font-size:80%;">'
               ||'<th>No</th>'
               ||'<th>Index Name</th>'
               ||'<th>Status</th>'
             ||'</tr>';

   FOR i IN (
              select index_name,status from user_indexes
               where (status <> 'VALID' and status <> 'N/A')
               union
              select index_name||':'||partition_name,status from user_ind_partitions
               where status <> 'USABLE')
   LOOP
         v_n := v_n +1;
         v_html := v_html || '<tr style="font-family:verdana; font-size:80%;">'
                            ||'<TD>'||v_n ||'</TD>'
                            ||'<TD>'|| i.index_name ||'</TD>'
                            ||'<TD>'|| i.status ||'</TD>'
                          ||'</tr>';
   END LOOP;

   --4) require send?
   IF  v_n > 0
   THEN
      v_html := v_html || '</table>';
      UTL_MAIL.send(sender => 'oracle@ababank.com',
                    recipients => '',
                    subject => '[' || SYS_CONTEXT ('USERENV', 'DB_NAME') || '] Index unusable alert',
                    message => v_html,
                    mime_type => 'text/html;charset=us-ascii');
   END IF;
    dbms_output.put_line('html: ' || v_html);
END;

backup pro eod . par

[oracle@garuda-2-17 script]$ cat FCCHOST_FCCLIVE.par
DIRECTORY=DATA_PUMP_DIR3
DUMPFILE=FCCHOST_FCCLIVE_PREEOD_201812112310_%U.DMP
LOGFILE=FCCHOST_FCCLIVE_PREEOD_201812112310.log
SCHEMAS=FCCHOST
FILESIZE=64G
EXCLUDE=statistics
EXCLUDE=grant
CONTENT=ALL
PARALLEL=8
QUERY=FCCHOST.SVTM_CIF_SIG_DET:"WHERE 1=2 "
QUERY=FCCHOST.IFTB_BRN_RECON_HIST:"WHERE 1=2 "
QUERY=FCCHOST.MSTB_ARCHIVE_OUT:"WHERE 1=2 "

pre eod backup and copy to new server

[oracle@garuda-2-17 ~]$ cat /u01/app/script/FCCHOST_FCCLIVE_PREEOD_expdp.sh
#!/bin/bash
. /home/oracle/.bash_profile
export script_dir=/u01/app/script
export DATA_PUMP_DIR=/backup/data_pump_dir
export DEST_DIR=/datastore/FCCLIVE

cd /u01/app/script
datevar=`date +%Y%m%d%H%M`

sed 's/<DATE>/'$datevar'/g' <FCCHOST_FCCLIVE_template.par >FCCHOST_FCCLIVE.par

export ORACLE_SID=FCCLIVE

sqlplus /nolog <<EOF
conn /as sysdba
set feedback off
set head off
set line 200
set pagesize 100
col object_type format a18
col status format a15
select object_type,status,count(*)
from dba_objects
where owner = 'FCCHOST'
group by object_type,status
/
exit;
EOF

expdp \' \/as sysdba \' parfile=FCCHOST_FCCLIVE.par


# Sending mail #
sqlplus /nolog <<EOF
conn /as sysdba
      BEGIN
         UTL_MAIL.send(sender => 'oracle@ababank.com',
                    recipients => 'dba@ababank.com;FlexcubeSupport@ababank.com',
                    subject => '[' || SYS_CONTEXT ('USERENV', 'DB_NAME') || '] Backup PREEOD '|| $(echo $datevar)||' Completed',
                    message => '<PRE>"$(tail -40 ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}.log)"</PRE>',
                    mime_type => 'text/html;charset=us-ascii');
     END;
/
--create restore point BEFORE_RUN_EOD guarantee flashback database;
exit;
EOF

# Copy to Backup 10.152.1.52 #
scp ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}* oracle@10.152.1.52:${DEST_DIR}

# deleting part #
grep "FCCHOST_FCCLIVE" ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_"$datevar".log|awk -F'/' '{print $4}' > filelist.txt

for filename in $(cat filelist.txt); do
a=`ssh oracle@10.152.1.52 ${DEST_DIR}"/FCCLIVE_dump_exist_check.sh "$filename`
if [ "$a" = "File is existing" ]
then
  rm ${DATA_PUMP_DIR}/$filename
fi
done

touch $script_dir/running_preeod_expdp_done.flg
if [ -f $script_dir/running_preeod_expdp.flg ]
then
  rm $script_dir/running_preeod_expdp.flg
fi

exit 1