In this article we will learn DR creation with log shipping :

Prerequisites : Source Database should be in Archive log mode .

Enable Archive Log Mode In Oracle RAC

Details :

Database Name Source DB IP DR DB IP
mgr 192.168.1.10 192.168.1.181

Step 1 : To create log shipping DR server force logging and supplemental logging should be enabled, if not enabled use following command to add force logging and supplemental logging at database level.

SQL> 
SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL>  alter database add supplemental log data;

Database altered.

Step 2 :  Check size db_recovery_file_dest , it should be set according to your DB size.

SQL> show parameter db_recovery

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 4182M
SQL>

Step 3 : Connect with RMAN and set controlfile autobackup on and redundancy as per your requirement.

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure retention policy to redundancy 2;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

Step 4 : Take rman backup with backup database command .

RMAN> backup database;

Starting backup at 26-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
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=/u01/oracle/oradata/mgr/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/mgr/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/mgr/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/mgr/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-18
channel ORA_DISK_1: finished piece 1 at 26-MAR-18
piece handle=/u01/oracle/fast_recovery_area/MGR/backupset/2018_03_26/o1_mf_nnndf_TAG20180326T181929_fckv2sxd_.bkp tag=TAG20180326T181929 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 26-MAR-18

Starting Control File and SPFILE Autobackup at 26-MAR-18
piece handle=/u01/oracle/fast_recovery_area/MGR/autobackup/2018_03_26/o1_mf_s_971806856_fckv5jqw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-18

RMAN>

We can see here controlfile and spfile backup taken automatically.

Step 5 : Transfer backup to DR server.

[oracle@localhost ~]$ rsync -azvhr --progress /u01/oracle/fast_recovery_area/MGR/ 192.168.1.181;/u01/oracle/fast_recovery_area/MGR/
sending incremental file list
created directory 192.168.1.181
./
autobackup/
autobackup/2018_03_26/
autobackup/2018_03_26/o1_mf_s_971806856_fckv5jqw_.bkp
       9.83M 100%   37.23MB/s    0:00:00 (xfer#1, to-check=2/8)
backupset/
backupset/2018_03_26/
backupset/2018_03_26/o1_mf_nnndf_TAG20180326T181929_fckv2sxd_.bkp
       1.06G 100%    5.26MB/s    0:03:12 (xfer#2, to-check=0/8)
onlinelog/

sent 257.18M bytes  received 73 bytes  1.33M bytes/sec
total size is 1.07G  speedup is 4.17

Step 6 : Now connect to DR server.

Step 7 : Create necessary directory at DR server to restore database.

[oracle@rac2 ~]$ mkdir -p /u01/oracle/admin/mgr/adump
[oracle@rac2 ~]$ mkdir -p /u01/oracle/admin/mgr/dpdump
[oracle@rac2 ~]$ mkdir -p /u01/oracle/admin/mgr/pfile
[oracle@rac2 ~]$ mkdir -p /u01/oracle/fast_recovery_area/MGR
[oracle@rac2 ~]$ mkdir -p /u01/oracle/admin/mgr/pfile
[oracle@rac2 ~]$ mkdir -p /u01/arc/mgr

Step 8 : Transfer spfile from Source database . Before that as it log shipping server  we need to set user equivalence from DR to Source database .

[oracle@rac2 .ssh]$ ssh-copy-id 192.168.1.10
oracle@192.168.1.10's password: 
Now try logging into the machine, with "ssh '192.168.1.10'", and check in:

  .ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

[oracle@rac2 .ssh]$ ssh 192.168.1.10
Last login: Mon Mar 26 18:11:14 2018 from 192.168.1.182
[oracle@localhost ~]$

User equivalence will allow us to password-less login that will help us to set automatic transfer of Archive logs.

Step 9 : Transfer spfile from source database to DR server.

[oracle@rac2 .ssh]$ rsync -azvrh --progress 192.168.1.10:/u01/oracle/product/11.2.0/db_1/dbs/spfilemgr.ora $ORACLE_HOME/dbs
receiving incremental file list
spfilemgr.ora
       2.56K 100%    2.44MB/s    0:00:00 (xfer#1, to-check=0/1)

sent 30 bytes  received 715 bytes  1.49K bytes/sec
total size is 2.56K  speedup is 3.44
[oracle@rac2 .ssh]$

We can see here , rsync has not asked us for password.

Step 10 : Start database in nomount stage.

[oracle@rac2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 26 18:25:08 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  392495104 bytes
Fixed Size		    2253584 bytes
Variable Size		  188746992 bytes
Database Buffers	  197132288 bytes
Redo Buffers		    4362240 bytes
SQL>

Step 11 : Login to RMAN :

[oracle@rac2 dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Mar 26 18:30:28 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MGR (not mounted)

RMAN>

Step 12 : Restore controlfile from Autobackup :

RMAN> restore controlfile from autobackup;

Starting restore at 26-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/oracle/fast_recovery_area
database name (or database unique name) used for search: MGR
channel ORA_DISK_1: AUTOBACKUP /u01/oracle/fast_recovery_area/MGR/autobackup/2018_03_26/o1_mf_s_971806856_fckv5jqw_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/oracle/fast_recovery_area/MGR/autobackup/2018_03_26/o1_mf_s_971806856_fckv5jqw_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oracle/oradata/mgr/control01.ctl
output file name=/u01/oracle/oradata/mgr/control02.ctl
Finished restore at 26-MAR-18

RMAN>

Step 13 :  Change database stage to mount stage.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>

Step 14 : Restore database :

RMAN> restore database;

Starting restore at 26-MAR-18
Starting implicit crosscheck backup at 26-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 26-MAR-18

Starting implicit crosscheck copy at 26-MAR-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-MAR-18

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oracle/fast_recovery_area/MGR/autobackup/2018_03_26/o1_mf_s_971806856_fckv5jqw_.bkp

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 /u01/oracle/oradata/mgr/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/mgr/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/mgr/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/mgr/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/MGR/backupset/2018_03_26/o1_mf_nnndf_TAG20180326T181929_fckv2sxd_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/fast_recovery_area/MGR/backupset/2018_03_26/o1_mf_nnndf_TAG20180326T181929_fckv2sxd_.bkp tag=TAG20180326T181929
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 26-MAR-18

RMAN>

Step 15 : Transfer Archive logs generated after taking backup and recover database.

[oracle@rac2 mgr]$ rsync -azvrh --progress 192.168.1.10:/u01/arc/mgr/ /u01/arc/mgr/
receiving incremental file list
./
1_2_971806268.dbf
      36.75M 100%   10.47MB/s    0:00:03 (xfer#1, to-check=4/6)
1_3_971806268.dbf
       1.02K 100%    2.88kB/s    0:00:00 (xfer#2, to-check=3/6)
1_4_971806268.dbf
       1.54K 100%    4.14kB/s    0:00:00 (xfer#3, to-check=2/6)
1_5_971806268.dbf
       1.02K 100%    2.51kB/s    0:00:00 (xfer#4, to-check=1/6)
1_6_971806268.dbf
       1.02K 100%    2.30kB/s    0:00:00 (xfer#5, to-check=0/6)

sent 109 bytes  received 5.14M bytes  1.14M bytes/sec
total size is 36.76M  speedup is 7.15
[oracle@rac2 mgr]$ 
RMAN> recover database;

Starting recover at 26-MAR-18
using channel ORA_DISK_1

starting media recovery

archived log file name=/u01/arc/mgr/1_2_971806268.dbf thread=1 sequence=2
archived log file name=/u01/arc/mgr/1_3_971806268.dbf thread=1 sequence=3
archived log file name=/u01/arc/mgr/1_4_971806268.dbf thread=1 sequence=4
archived log file name=/u01/arc/mgr/1_5_971806268.dbf thread=1 sequence=5
archived log file name=/u01/arc/mgr/1_6_971806268.dbf thread=1 sequence=6
unable to find archived log
archived log thread=1 sequence=7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/26/2018 18:35:47
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7 and starting SCN of 933930

RMAN>

Step 16 : Transfer incremental archive logs from Source to DR and recover using following command :

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 933930 generated at 03/26/2018 18:47:40 needed for thread 1
ORA-00289: suggestion : /u01/arc/mgr/1_7_971806268.dbf
ORA-00280: change 933930 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 933982 generated at 03/26/2018 18:50:01 needed for thread 1
ORA-00289: suggestion : /u01/arc/mgr/1_8_971806268.dbf
ORA-00280: change 933982 for thread 1 is in sequence #8
ORA-00278: log file '/u01/arc/mgr/1_7_971806268.dbf' no longer needed for this
recovery


ORA-00279: change 933985 generated at 03/26/2018 18:50:02 needed for thread 1
ORA-00289: suggestion : /u01/arc/mgr/1_9_971806268.dbf
ORA-00280: change 933985 for thread 1 is in sequence #9
ORA-00278: log file '/u01/arc/mgr/1_8_971806268.dbf' no longer needed for this
recovery


ORA-00279: change 933988 generated at 03/26/2018 18:50:02 needed for thread 1
ORA-00289: suggestion : /u01/arc/mgr/1_10_971806268.dbf
ORA-00280: change 933988 for thread 1 is in sequence #10
ORA-00278: log file '/u01/arc/mgr/1_9_971806268.dbf' no longer needed for this
recovery


ORA-00279: change 933991 generated at 03/26/2018 18:50:02 needed for thread 1
ORA-00289: suggestion : /u01/arc/mgr/1_11_971806268.dbf
ORA-00280: change 933991 for thread 1 is in sequence #11
ORA-00278: log file '/u01/arc/mgr/1_10_971806268.dbf' no longer needed for this
recovery


ORA-00279: change 933994 generated at 03/26/2018 18:50:03 needed for thread 1
ORA-00289: suggestion : /u01/arc/mgr/1_12_971806268.dbf
ORA-00280: change 933994 for thread 1 is in sequence #12
ORA-00278: log file '/u01/arc/mgr/1_11_971806268.dbf' no longer needed for this
recovery


ORA-00308: cannot open archived log '/u01/arc/mgr/1_12_971806268.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL>

Give auto when “recover database using backup controlfile ” command ask for it. It will automatically take path of next archive logs.

Now our DR server is all set for log shipping .

We just need to add script to make it Automatic , so it dont need much human intervene.I have added two scripts :

[oracle@rac2 logship]$ cat logship_auto.sh 
while [ 1 = 1 ]
do
rsync -azvrh --progress 192.168.1.10:/u01/arc/mgr /u01/arc/mgr

sleep 20
/u01/logship/recover.sh
sleep 10
done
[oracle@rac2 logship]$ cat recover.sh 
export ORACLE_SID=mgr
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus -s /nolog <<EOF
CONN / AS SYSDBA
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
RECOVER DATABASE USING BACKUP CONTROLFILE until cancel;
auto
exit;
[oracle@rac2 logship]$

Run this script at Server’s console.

[oracle@rac2 logship]$ ./logship_auto.sh 
receiving incremental file list
mgr/
mgr/1_22_971806268.dbf
       1.02K 100% 1000.00kB/s    0:00:00 (xfer#1, to-check=11/25)
mgr/1_23_971806268.dbf
       1.02K 100%  333.33kB/s    0:00:00 (xfer#2, to-check=10/25)
mgr/1_24_971806268.dbf
       1.02K 100%  166.67kB/s    0:00:00 (xfer#3, to-check=9/25)
mgr/1_25_971806268.dbf
       1.02K 100%   71.43kB/s    0:00:00 (xfer#4, to-check=8/25)

sent 91 bytes  received 1.67K bytes  3.53K bytes/sec
total size is 36.89M  speedup is 20898.59
/u01/logship/recover.sh: line 8: warning: here-document at line 2 delimited by end-of-file (wanted `EOF')
ORA-01081: cannot start already-running ORACLE - shut it down first
ALTER DATABASE MOUNT
*
ERROR at line 1:
ORA-01100: database already mounted


ORA-00279: change 933994 generated at 03/26/2018 18:50:03 needed for thread 1
ORA-00289: suggestion : /u01/arc/mgr/1_12_971806268.dbf
ORA-00280: change 933994 for thread 1 is in sequence #12


ORA-00308: cannot open archived log '/u01/arc/mgr/1_12_971806268.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/u01/arc/mgr/1_12_971806268.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


receiving incremental file list
mgr/
mgr/1_26_971806268.dbf
       1.02K 100% 1000.00kB/s    0:00:00 (xfer#1, to-check=9/27)
mgr/1_27_971806268.dbf
       1.02K 100%  250.00kB/s    0:00:00 (xfer#2, to-check=8/27)

sent 53 bytes  received 1.23K bytes  2.56K bytes/sec
total size is 36.89M  speedup is 28818.80

Stay tuned for More articles on Oracle RAC

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel : https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.