Pre-Installation Check
Verify the primary database is in ARCHIVELOG mode
SQL> select name from v$database;
NAME
---------
DBDPROD
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14487
Next log sequence to archive 14489
Current log sequence 14489
Add at least one LOG_ARCHIVE_DEST and LOG_ARCHIVE_START to INIT FILE
You can also use Flash Recovery Area if that is already configured.
SQL > ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/DG/archive/' SCOPE=spfile;
SQL> ALTER SYSTEM ARCHIVE LOG START;
System altered.
Note: Restart Database to verify new settings are in effect
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
/u02/oradata/DBDPROD/SYSTEM01.DBF
/u02/oradata/DBDPROD/SYSAUX01.DBF
/u02/oradata/DBDPROD/UNDOTBS01.DBF
/u02/oradata/DBDPROD/USERS01.DBF
/u02/oradata/DBDPROD/XXXXX_UOFR.DBF
/u02/oradata/DBDPROD/XXXXX_01.DBF
/u02/oradata/DBDPROD/XXXXX_UOFR_02.DBF
/u02/oradata/DBDPROD/AUDIT_AUX01.DBF
/u02/oradata/DBDPROD/audit.dbf
/u02/oradata/DBDPROD/statspack_data01.dbf
/u02/oradata/DBDPROD/XXXXX_EXXS_01.DBF
NAME
--------------------------------------------------------------------------------
/u02/oradata/TST11242/XXXXX_XADR_LOB_01.DBF
/u02/oradata/DBDPROD/XXXXX_XADR_01.DBF
/u02/oradata/DBDPROD/statspack_data02.dbf
SQL> sho parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u05/fast_recovery_area/
db_recovery_file_dest_size big integer 80G
SQL>
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/DBDPROD/control01.ctl
/u02/oradata/DBDPROD/flash_recovery_area/control02.ctl
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/DBDPROD/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
SQL>
Create Standby INIT FILE from SPFILE for Standby Database
SQL> create pfile='/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/initDBDPROD.ora' from spfile;
Create Standby CONTROL FILE for Standby Database
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/DBDPRODstdby.ctl';
Create Hot Backup of the Primary Database using RMAN
[oracle@xxxx-DB-01 DBDPROD]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 5 11:17:50 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBDPROD (DBID=3489014338)
RMAN
run
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
allocate channel c1 DEVICE TYPE DISK MAXPIECESIZE = 5G;
backup full format "/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/%d_DB_%u_%s_%p" database ;
copy current controlfile to "/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/STDBY_control.ctl";
}
Starting backup at 23-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 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/oradata/DG/system01.dbf
input datafile file number=00002 name=/u01/oradata/DG/sysaux01.dbf
input datafile file number=00003 name=/u01/oradata/DG/undotbs01.dbf
input datafile file number=00004 name=/u01/oradata/DG/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-APR-14
channel ORA_DISK_1: finished piece 1 at 23-APR-14
piece handle=/u01/orabackupDG/DG_data_t845636933_s1_p1 tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-APR-14
channel ORA_DISK_1: finished piece 1 at 23-APR-14
piece handle=/u01/orabackupDG/DG_data_t845636978_s2_p1 tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-APR-14
Starting backup at 23-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/orabackup/controlfile_230414.ctl tag=TAG20140423T110941 RECID=2 STAMP=845636981
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-APR-14
RMAN> exit
Prepare the failover server (Standby Database) for restore
Create the directories that are needed for successfully restoring the database backup
****LOCATIONS NEED TO BE CREATED ON STANDBY ***
mkdir -p /u02/oradata/DBDPROD/
mkdir -p /u01/app/oracle/admin/DBDPROD/adump
mkdir -p /u01/app/oracle/admin/DBDPROD/dpdump
mkdir -p /u01/app/oracle/admin/DBDPROD/pfile
mkdir –p /u03/fast_recovery_area/DBDPROD/archivelog
Copy files to the failover server (Standby Database) for restore
At this point, you want to copy everything over to the standby server including RMAN backup, standby controlfile & Config (INIT) file
INIT FILE
[oracle@FCIMHCQADB1 ~]$ cp initDBDPROD.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
STANDBY CONTROL FILE
[oracle@FCIMHCQADB1 STDBY_files]$ cp DBDPRODstdby.ctl /u02/oradata/DBDPROD/control01.ctl
[oracle@FCIMHCQADB1 STDBY_files]$ cp DBDPRODstdby.ctl /u02/oradata/DBDPROD/flash_recovery_area/control02.ctl
Startup Standby Database in NOMONT MODE
[oracle@ dbs]$ export ORACLE_SID=DBDPROD
[oracle@ dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 29 16:17:52 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1677725072 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16904192 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL>
Restore Standby Database from RMAN backup of Primary Database
[oracle@ dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 29 16:22:58 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBDPROD (DBID=3489014338, not open)
RMAN> catalog start with '/u03/fast_recovery_area/DBDPROD/STDBY_files';
Starting implicit crosscheck backup at 29-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 29-OCT-15
Starting implicit crosscheck copy at 29-OCT-15
using channel ORA_DISK_1
Crosschecked 14 objects
Finished implicit crosscheck copy at 29-OCT-15
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u03/fast_recovery_area/DBDPROD/STDBY_files
List of Files Unknown to the Database
=====================================
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/STDBY_controlfile.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPRODstdby.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/STDBY_controlfile.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPRODstdby.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1
RMAN>
RMAN> restore database;
Starting restore at 29-OCT-15
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 /u02/oradata/DBDPROD/SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/DBDPROD/SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/DBDPROD/UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/DBDPROD/USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/DBDPROD/XXXXX_UOFR.DBF
channel ORA_DISK_1: restoring datafile 00006 to /u02/oradata/DBDPROD/XXXXX_01.DBF
channel ORA_DISK_1: restoring datafile 00007 to /u02/oradata/DBDPROD/XXXXX_UOFR_02.DBF
channel ORA_DISK_1: restoring datafile 00008 to /u02/oradata/DBDPROD/AUDIT_AUX01.DBF
channel ORA_DISK_1: restoring datafile 00009 to /u02/oradata/DBDPROD/audit.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u02/oradata/DBDPROD/statspack_data01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u02/oradata/DBDPROD/XXXXX_EITS_01.DBF
channel ORA_DISK_1: restoring datafile 00012 to /u02/oradata/TST11242/XXXXX_XADR_LOB_01.DBF
channel ORA_DISK_1: restoring datafile 00013 to /u02/oradata/DBDPROD/XXXXX_XADR_01.DBF
channel ORA_DISK_1: restoring datafile 00014 to /u02/oradata/DBDPROD/statspack_data02.dbf
channel ORA_DISK_1: reading from backup piece /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1
channel ORA_DISK_1: piece handle=/u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1 tag=TAG20151027T163936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:14:05
Finished restore at 29-OCT-15
RMAN>
Recovery Manager complete.
Create Script to ARCHIVELOGS / APPLY LOGS / RECOVER STANDBY / CLEAN ARCHIVES
******************************************************************************************
apply_archive_DBDPROD.sh
# !/bin/bash
# Declare your environment variables
#### Archive logs from production are rsync`d to standby ######
/home/oracle/scripts/mv_archive_PRDSTDBY.sh
#### Setup environment and recover database #####
ORACLE_SID=DBDPROD
export ORACLE_SID
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
cd /u01/app/oracle/product/11.2.0/dbhome_1/bin
./sqlplus sys/xxxxxx as sysdba @/home/oracle/scripts/recover_DBDPROD_STDBY.sh
rm /home/oracle/DBDPROD_stdby.lock
find /u03/fast_recovery_area/DBDPROD/archivelog/* -type d -mtime +3 -exec rm -rf {} \;
*******************************************************************************************
mv_archive_PRDSTDBY.sh
#!/bin/bash
if [ -e /home/oracle/DBDPROD_stdby.lock ]
then
echo "Rsync job already running...exiting"
exit
fi
touch /home/oracle/DBDPROD_stdby.lock
rsync -e ssh -avzh oracle@192.168.1.205:/u05/fast_recovery_area/DBDPROD/archivelog/* /u03/fast_recovery_area/DBDPROD/archivelog/.
*******************************************************************************************
recover_DBDPROD_STDBY.sh
set echo on
spool /home/oracle/DBDPROD_STDBY_apply.log
RECOVER AUTOMATIC STANDBY DATABASE until cancel;
alter DATABASE OPEN READ ONLY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
spool off
exit;
*******************************************************************************************
Verify REDO LOGS transfer to Failover Server (Standby Database)
Perform switch logfile to generate REDO LOGS and then run Shell Script (updateDG.sh) to copy Archive Logs to Standby Database
On Primary Database
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oradata/DG/archive/
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> exit
Recover Standby Database by applying all REDO LOGS from Primary Database
SQL> RECOVER AUTOMATIC STANDBY DATABASE ;
ORA-00279: change 573811485 generated at 11/06/2015 09:45:09 needed for thread
1
ORA-00289: suggestion :
/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc
ORA-00280: change 573811485 for thread 1 is in sequence #14930
ORA-00278: log file
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
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
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter DATABASE OPEN READ ONLY;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1660947856 bytes
Database Buffers 1526726656 bytes
Redo Buffers 16904192 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL> spool off
SQL> exit;
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Resolving Errors
If you get an error because the Standby DB can't recognized a database file name that was added or dropped perform the following:
alter database recover automatic standby database until cancel;
alter database create datafile '/u01/app/oracle/OraHome_1/dbs/UNNAMED00002' as '/u01/app/oracle/oradata/CCOM/undotbs01.dbf';
