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
