Category Archives: Dataguard

Create Physical Standby using RMAN Backup with Duplicate Command

How to Create Physical Standby Database using RMAN Backup With Duplicate Command

Table of Contents

___________________________________________________________________________________________________

0. Introduction
1. Environment

On Primary (Step 2 to Step 12)

2. Enable Forced Logging on Primary
3. Check Password File on Primary
4. Configure a Standby Redo Log on Primary
5. Verify Archive Mode Enabled on Primary
6. Set Primary Database Initialization Parameters
7. Backup Primary Database for configure Standby
8. Transfer PASSWORD FILE TO STANDBY SIDE
9. Transfer Backup from Primary to Standby
10. Transfer pfile from primary to standby
11. Configure TNS for Primary
12. Verify connection ‘AS SYSDBA’ from Primary

On STANDBY (Step 13 to Step 21)

13. Configure TNS for STANDBY
14. Configure init parameter for STANDBY
15. Add oratab entry
16. Create required directories
17. Startup Nomount
18. Verify connection ‘AS SYSDBA’ from Standby
19. Run RMAN Duplicate for standby
20. Verify Standby redo logs
21. Enable MRP on STANDBY

22. Verify Sync
23. Verify ..Lets Test
________________________________________________________________________________________________


0. Introduction

PLEASE NOTE in 12c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files.

Definition of Active Dataguard:

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.

Goal : How to Create Physical Standby Database using RMAN Backup With Duplicate Command


1. Environment

Primary:

	Platform	: Linuxx86_64
	Server Name	: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
	DB Version	: Oracle 12.2.0.1
	File system     : Normal
	Database Name	: UOIN1CON
        DB_UNIQUE_NAME  : UOIN1CON
	Flashback	: Disabled
	Oracle Home Path: /u01/app/oracle/product/12.2.0.1
		
Standby:
		
	Platform	: Linuxx86_64
	Server Name	: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
	DB Version	: Oracle 12.2.0.1
	File system     : Normal
	Database Name	: UOIN1CON
        DB_UNIQUE_NAME  : UOIN1CON_DG
        Flashback	: Disabled
	Oracle Home Path: /u01/app/oracle/product/12.2.0.1

On Primary (Step 2 to Step 12)


2. Enable Forced Logging on Primary

SQL> select name, open_mode,cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
UOIN1CON  READ WRITE           NO

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES <-----

SQL>


3. Check Password File on Primary

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/12.2.0.1/dbs
[oracle@rac1 dbs]$ ls -ltr orapwUOIN1CON
-rw-r-----. 1 oracle dba 3584 Dec 14 12:26 orapwUOIN1CON
[oracle@rac1 dbs]$


4. Configure a Standby Redo Log on Primary

-- Since we have 3 online redo log file groups, we need to create 4(3+1) Standby redo log file groups
-- Standy Redo logs files come into picture only when protection mode is Maximum Availability and Maximum Protection.

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

   THREAD#     GROUP# MEMBER                                                            BYTES
---------- ---------- ------------------------------------------------------------ ----------
         1          3 /u02/oracle/oradata/UOIN1CON/redo03.log                       209715200
         1          2 /u02/oracle/oradata/UOIN1CON/redo02.log                       209715200
         1          1 /u02/oracle/oradata/UOIN1CON/redo01.log                       209715200

SQL>

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u02/oracle/oradata/UOIN1CON/redo04.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u02/oracle/oradata/UOIN1CON/redo05.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u02/oracle/oradata/UOIN1CON/redo06.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oracle/oradata/UOIN1CON/redo07.log') SIZE 200M;

Database altered.

SQL>

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
         3         ONLINE  /u02/oracle/oradata/UOIN1CON/redo03.log                      NO           0
         2         ONLINE  /u02/oracle/oradata/UOIN1CON/redo02.log                      NO           0
         1         ONLINE  /u02/oracle/oradata/UOIN1CON/redo01.log                      NO           0
         4         STANDBY /u02/oracle/oradata/UOIN1CON/redo04.log                      NO           0
         5         STANDBY /u02/oracle/oradata/UOIN1CON/redo05.log                      NO           0
         6         STANDBY /u02/oracle/oradata/UOIN1CON/redo06.log                      NO           0
         7         STANDBY /u02/oracle/oradata/UOIN1CON/redo07.log                      NO           0

7 rows selected.

SQL>

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                            BYTES
---------- ------------------------------------------------------------ ----------
         4 /u02/oracle/oradata/UOIN1CON/redo04.log                       209715200
         5 /u02/oracle/oradata/UOIN1CON/redo05.log                       209715200
         6 /u02/oracle/oradata/UOIN1CON/redo06.log                       209715200
         7 /u02/oracle/oradata/UOIN1CON/redo07.log                       209715200

SQL>


5. Verify Archive Mode Enabled on Primary

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/UOIN1CON
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL>


6. Set Primary Database Initialization Parameters

SQL> alter system set db_unique_name='UOIN1CON' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u02/oracle/archive/UOIN1CON VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=UOIN1CON_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON_DG' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET fal_client=UOIN1CON scope=both;

System altered.

SQL>

Please note: The FAL_CLIENT database initialization parameter is no longer required from 11gR2

SQL> ALTER SYSTEM SET fal_server=UOIN1CON_DG scope=both;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>
SQL> create pfile='/home/oracle/initUOIN1CON_after.ora' from spfile;

File created.

SQL> 

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@rac1 ~]$ cat /home/oracle/initUOIN1CON_after.ora

UOIN1CON.__data_transfer_cache_size=0
UOIN1CON.__db_cache_size=369098752
UOIN1CON.__inmemory_ext_roarea=0
UOIN1CON.__inmemory_ext_rwarea=0
UOIN1CON.__java_pool_size=16777216
UOIN1CON.__large_pool_size=33554432
UOIN1CON.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
UOIN1CON.__pga_aggregate_target=587202560
UOIN1CON.__sga_target=687865856
UOIN1CON.__shared_io_pool_size=33554432
UOIN1CON.__shared_pool_size=218103808
UOIN1CON.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/UOIN1CON/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u02/oracle/oradata/UOIN1CON/control01.ctl','/u02/oracle/oradata/UOIN1CON/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON'
*.db_name='UOIN1CON'
*.db_unique_name='UOIN1CON'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=UOIN1CONXDB)'
*.fal_client='UOIN1CON'
*.fal_server='UOIN1CON_DG'
*.log_archive_config='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/UOIN1CON VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON'
*.log_archive_dest_2='SERVICE=UOIN1CON_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON_DG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON'
*.memory_target=1201m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac1 ~]$


7. Backup Primary Database for configure Standby

[oracle@rac1 ~]$ mkdir -p /u02/oracle/backup/UOIN1CON

[oracle@rac1 ~]$ mkdir -p /u02/oracle/backup/UOIN1CON
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd /u02/oracle/backup/UOIN1CON
[oracle@rac1 UOIN1CON]$ vi BACKUP_UOIN1CON.sh
[oracle@rac1 UOIN1CON]$ chmod 775 BACKUP_UOIN1CON.sh
[oracle@rac1 UOIN1CON]$

[oracle@rac1 UOIN1CON]$ cat BACKUP_UOIN1CON.sh
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
BACKUP_LOG_PATH=/u02/oracle/backup/UOIN1CON
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=UOIN1CON
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/BACKUP_UOIN1CON.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} << EOF 
connect target / 
run { 
allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
backup database format '/u02/oracle/backup/UOIN1CON/database_%d_%u_%s'; release channel t1; 
release channel t2; 
release channel t3; 
} 
sql 'alter system archive log current'; 
run { 
allocate channel a1 type disk; 
allocate channel a2 type disk; 
allocate channel a3 type disk; 
backup archivelog all format '/u02/oracle/backup/UOIN1CON/arch_%d_%u_%s'; release channel a1; 
release channel a2; 
release channel a3; 
} 
run { 
allocate channel c1 type disk; 
backup current controlfile format '/u02/oracle/backup/UOIN1CON/Control_%d_%u_%s'; 
release channel c1; 
} 
exit; 
EOF 
[oracle@rac1 UOIN1CON]$
[oracle@rac1 UOIN1CON]$ 
[oracle@rac1 UOIN1CON]$ nohup ./BACKUP_UOIN1CON.sh & 
[1] 6484 
[oracle@rac1 UOIN1CON]$ nohup: ignoring input and appending output to `nohup.out' 
[oracle@rac1 UOIN1CON]$ jobs -l 
[1]+ 6484 Running        nohup ./BACKUP_UOIN1CON.sh & 
[oracle@rac1 UOIN1CON]$ 
[oracle@rac1 UOIN1CON]$ jobs -l 
[1]+ 6484 Done          nohup ./BACKUP_UOIN1CON.sh 
[oracle@rac1 UOIN1CON]$ 
[oracle@rac1 UOIN1CON]$ ls -ltr
total 1614284
-rwxrwxr-x. 1 oracle dba       982 Jan  3 16:44 rmanbackup.sh
-rwxrwxr-x. 1 oracle dba       976 Jan  4 05:45 BACKUP_UOIN1CON.sh
-rw-r-----. 1 oracle dba   6463488 Jan  5 17:13 database_UOIN1CON_19tmj2i0_41
-rw-r-----. 1 oracle dba 435650560 Jan  5 17:13 database_UOIN1CON_18tmj2i0_40
-rw-r-----. 1 oracle dba 726351872 Jan  5 17:14 database_UOIN1CON_17tmj2i0_39
-rw-r-----. 1 oracle dba 112978944 Jan  5 17:14 arch_UOIN1CON_1dtmj2ja_45
-rw-r-----. 1 oracle dba 125304832 Jan  5 17:14 arch_UOIN1CON_1ctmj2ja_44
-rw-r-----. 1 oracle dba 229672448 Jan  5 17:14 arch_UOIN1CON_1btmj2j9_43
-rw-r-----. 1 oracle dba   5603328 Jan  5 17:14 arch_UOIN1CON_1etmj2jh_46
-rw-r-----. 1 oracle dba  10960896 Jan  5 17:14 Control_UOIN1CON_1gtmj2jk_48
-rw-------. 1 oracle dba        96 Jan  5 17:14 nohup.out
-rw-r--r--. 1 oracle dba      7792 Jan  5 17:14 BACKUP_UOIN1CON.log
[oracle@rac1 UOIN1CON]$
[oracle@rac1 UOIN1CON]$
 [oracle@rac1 UOIN1CON]$ cat BACKUP_UOIN1CON.log 
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jan 5 17:13:34 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. 
RMAN>
connected to target database: UOIN1CON (DBID=1821803680)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=24 device type=DISK

allocated channel: t2
channel t2: SID=91 device type=DISK

allocated channel: t3
channel t3: SID=95 device type=DISK

Starting backup at 05-JAN-19
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/UOIN1CON/system01.dbf
channel t1: starting piece 1 at 05-JAN-19
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oracle/oradata/UOIN1CON/sysaux01.dbf
input datafile file number=00007 name=/u02/oracle/oradata/UOIN1CON/users01.dbf
channel t2: starting piece 1 at 05-JAN-19
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/oracle/oradata/UOIN1CON/oggdata01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/UOIN1CON/undotbs01.dbf
channel t3: starting piece 1 at 05-JAN-19
channel t3: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_19tmj2i0_41 tag=TAG20190105T171336 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:03
channel t2: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_18tmj2i0_40 tag=TAG20190105T171336 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:26
channel t1: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_17tmj2i0_39 tag=TAG20190105T171336 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:36
Finished backup at 05-JAN-19

Starting Control File and SPFILE Autobackup at 05-JAN-19
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190105-02 comment=NONE
Finished Control File and SPFILE Autobackup at 05-JAN-19

released channel: t1

released channel: t2

released channel: t3

RMAN>
sql statement: alter system archive log current

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: SID=24 device type=DISK

allocated channel: a2
channel a2: SID=91 device type=DISK

allocated channel: a3
channel a3: SID=95 device type=DISK

Starting backup at 05-JAN-19
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=34 RECID=82 STAMP=996611417
input archived log thread=1 sequence=35 RECID=84 STAMP=996643337
input archived log thread=1 sequence=36 RECID=86 STAMP=996643513
input archived log thread=1 sequence=37 RECID=88 STAMP=996643795
input archived log thread=1 sequence=38 RECID=89 STAMP=996644743
input archived log thread=1 sequence=39 RECID=90 STAMP=996644744
input archived log thread=1 sequence=40 RECID=91 STAMP=996732232
input archived log thread=1 sequence=41 RECID=92 STAMP=996741858
channel a1: starting piece 1 at 05-JAN-19
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=4 STAMP=996595104
input archived log thread=1 sequence=5 RECID=5 STAMP=996596970
input archived log thread=1 sequence=6 RECID=6 STAMP=996596978
input archived log thread=1 sequence=7 RECID=7 STAMP=996597993
input archived log thread=1 sequence=8 RECID=8 STAMP=996597994
input archived log thread=1 sequence=9 RECID=9 STAMP=996604700
input archived log thread=1 sequence=10 RECID=11 STAMP=996607158
input archived log thread=1 sequence=11 RECID=14 STAMP=996607712
input archived log thread=1 sequence=12 RECID=15 STAMP=996608268
input archived log thread=1 sequence=13 RECID=16 STAMP=996608271
input archived log thread=1 sequence=14 RECID=17 STAMP=996608830
input archived log thread=1 sequence=15 RECID=18 STAMP=996608951
input archived log thread=1 sequence=16 RECID=19 STAMP=996609021
input archived log thread=1 sequence=17 RECID=21 STAMP=996609054
input archived log thread=1 sequence=18 RECID=23 STAMP=996609176
channel a2: starting piece 1 at 05-JAN-19
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=42 RECID=93 STAMP=996769488
input archived log thread=1 sequence=43 RECID=94 STAMP=996772311
input archived log thread=1 sequence=44 RECID=95 STAMP=996772312
input archived log thread=1 sequence=45 RECID=96 STAMP=996772455
input archived log thread=1 sequence=46 RECID=97 STAMP=996772457
channel a3: starting piece 1 at 05-JAN-19
channel a1: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_1btmj2j9_43 tag=TAG20190105T171417 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:08
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=26 STAMP=996609431
input archived log thread=1 sequence=20 RECID=36 STAMP=996609626
input archived log thread=1 sequence=21 RECID=38 STAMP=996609630
input archived log thread=1 sequence=22 RECID=41 STAMP=996609971
input archived log thread=1 sequence=23 RECID=42 STAMP=996610043
input archived log thread=1 sequence=24 RECID=43 STAMP=996610157
input archived log thread=1 sequence=25 RECID=44 STAMP=996610213
input archived log thread=1 sequence=26 RECID=46 STAMP=996610243
input archived log thread=1 sequence=27 RECID=48 STAMP=996611311
input archived log thread=1 sequence=28 RECID=70 STAMP=996611319
input archived log thread=1 sequence=29 RECID=72 STAMP=996611321
input archived log thread=1 sequence=30 RECID=74 STAMP=996611322
input archived log thread=1 sequence=31 RECID=76 STAMP=996611323
input archived log thread=1 sequence=32 RECID=78 STAMP=996611345
input archived log thread=1 sequence=33 RECID=80 STAMP=996611346
channel a1: starting piece 1 at 05-JAN-19
channel a2: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_1ctmj2ja_44 tag=TAG20190105T171417 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:07
channel a3: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_1dtmj2ja_45 tag=TAG20190105T171417 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:07
channel a1: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_1etmj2jh_46 tag=TAG20190105T171417 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JAN-19

Starting Control File and SPFILE Autobackup at 05-JAN-19
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190105-03 comment=NONE
Finished Control File and SPFILE Autobackup at 05-JAN-19

released channel: a1

released channel: a2

released channel: a3

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=24 device type=DISK

Starting backup at 05-JAN-19
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 05-JAN-19
channel c1: finished piece 1 at 05-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/Control_UOIN1CON_1gtmj2jk_48 tag=TAG20190105T171428 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JAN-19

Starting Control File and SPFILE Autobackup at 05-JAN-19
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190105-04 comment=NONE
Finished Control File and SPFILE Autobackup at 05-JAN-19

released channel: c1

RMAN>

Recovery Manager complete.
[oracle@rac1 UOIN1CON]$



8. Transfer PASSWORD FILE TO STANDBY SIDE

-- Copy the password file from the primary to standby $ORACLE_HOME/dbs and rename it to the standby database name.
-- The username is required to be SYS and the password needs to be the same on the Primary and Standby.
-- The best practice for this is to copy the passwordfile as suggested.
-- The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@rac1 UOIN1CON]$ cd /u01/app/oracle/product/12.2.0.1/dbs/
[oracle@rac1 dbs]$ ls -ltr orapwUOIN1CON
-rw-r-----. 1 oracle dba 3584 Dec 14 12:26 orapwUOIN1CON
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ scp orapwUOIN1CON oracle@rac2:/u01/app/oracle/product/12.2.0.1/dbs/orapwUOIN1CON_DG
oracle@rac2's password:
orapwUOIN1CON                       100% 3584     3.5KB/s   00:00
[oracle@rac1 dbs]$


9. Transfer Backup from Primary to Standby

[oracle@rac1 UOIN1CON]$ scp database_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON_DG/
oracle@rac2's password:
database_UOIN1CON_17tmj2i0_39            100%  693MB  53.3MB/s   00:13
database_UOIN1CON_18tmj2i0_40            100%  415MB  59.4MB/s   00:07
database_UOIN1CON_19tmj2i0_41            100% 6312KB   6.2MB/s   00:00
[oracle@rac1 UOIN1CON]$
[oracle@rac1 UOIN1CON]$ scp Control_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON_DG/
oracle@rac2's password:
Control_UOIN1CON_1gtmj2jk_48             100%   10MB  10.5MB/s   00:00
[oracle@rac1 UOIN1CON]$
[oracle@rac1 UOIN1CON]$ scp arch_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON_DG/
oracle@rac2's password:
arch_UOIN1CON_1btmj2j9_43                100%  219MB  73.0MB/s   00:03
arch_UOIN1CON_1ctmj2ja_44                100%  120MB  59.8MB/s   00:02
arch_UOIN1CON_1dtmj2ja_45                100%  108MB  53.9MB/s   00:02
arch_UOIN1CON_1etmj2jh_46                100% 5472KB   5.3MB/s   00:00
[oracle@rac1 UOIN1CON]$


10. Transfer pfile from primary to standby

[oracle@rac1 ~]$ scp initUOIN1CON_after.ora oracle@rac2:/u02/oracle/backup/UOIN1CON_DG/
oracle@rac2's password:
initUOIN1CON_after.ora                                100% 1780     1.7KB/s   00:00
[oracle@rac1 ~]$


11. Configure TNS for Primary

[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UOIN1CON)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = UOIN1CON)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

UOIN1CON_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UOIN1CON_DG)
    )
  )

UOIN1CON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = UOIN1CON)
    )
  )

LISTENER_12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))

[oracle@rac1 admin]$

[oracle@rac1 admin]$ lsnrctl status LISTENER_12C

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-JAN-2019 18:19:39

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_12C
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                03-JAN-2019 17:18:26
Uptime                    0 days 1 hr. 1 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_12c/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "UOIN1CON" has 1 instance(s).
  Instance "UOIN1CON", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$


12. Verify connection ‘AS SYSDBA’ from Primary

[oracle@rac1 ~]$ sqlplus SYS/SYS@UOIN1CON AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 17:58:18 2019

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


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

SQL>

On STANDBY (Step 12 to Step 21)


13. Configure TNS for STANDBY

[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UOIN1CON_DG)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = UOIN1CON_DG)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac2 admin]$

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

UOIN1CON_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UOIN1CON_DG)
    )
  )

UOIN1CON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UOIN1CON)
    )
  )

LISTENER_12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))

[oracle@rac2 admin]$

[oracle@rac2 admin]$ lsnrctl status LISTENER_12C

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-JAN-2019 18:14:00

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_12C
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                03-JAN-2019 18:01:29
Uptime                    0 days 0 hr. 12 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener_12c/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "UOIN1CON_DG" has 1 instance(s).
  Instance "UOIN1CON_DG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$


14. Configure init parameter for STANDBY

Modify initUOIN1CON_after.ora and renamed to initUOIN1CON_DG.ora

[oracle@rac2 UOIN1CON_DG]$ cat initUOIN1CON_DG.ora

UOIN1CON_DG.__data_transfer_cache_size=0
UOIN1CON_DG.__db_cache_size=369098752
UOIN1CON_DG.__inmemory_ext_roarea=0
UOIN1CON_DG.__inmemory_ext_rwarea=0
UOIN1CON_DG.__java_pool_size=16777216
UOIN1CON_DG.__large_pool_size=33554432
UOIN1CON_DG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
UOIN1CON_DG.__pga_aggregate_target=587202560
UOIN1CON_DG.__sga_target=687865856
UOIN1CON_DG.__shared_io_pool_size=33554432
UOIN1CON_DG.__shared_pool_size=218103808
UOIN1CON_DG.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/UOIN1CON_DG/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u02/oracle/oradata/UOIN1CON_DG/control01.ctl','/u02/oracle/oradata/UOIN1CON_DG/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u02/oracle/oradata/UOIN1CON','/u02/oracle/oradata/UOIN1CON_DG'
*.db_name='UOIN1CON'
*.db_unique_name='UOIN1CON_DG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=UOIN1CON_DGXDB)'
*.fal_client='UOIN1CON_DG'
*.fal_server='UOIN1CON'
*.log_archive_config='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/UOIN1CON_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON_DG'
*.log_archive_dest_2='SERVICE=UOIN1CON LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u02/oracle/oradata/UOIN1CON','/u02/oracle/oradata/UOIN1CON_DG'
*.memory_target=1201m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac2 UOIN1CON_DG]$


15. Add oratab entry

[oracle@rac2 ~]$ cat /etc/oratab | grep -i UOIN1CON_DG
UOIN1CON_DG:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac2 ~]$


16. Create required directories

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/UOIN1CON_DG/adump
[oracle@rac2 ~]$ mkdir -p /u02/oracle/oradata/UOIN1CON_DG
[oracle@rac2 ~]$ mkdir -p /u02/oracle/archive/UOIN1CON_DG
[oracle@rac2 ~]$


17. Startup Nomount

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [UOIN1CON_DG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 18:01:38 2019

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

Connected to an idle instance.

SQL> startup nomount pfile='/u02/oracle/backup/UOIN1CON_DG/initUOIN1CON_DG.ora';
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size                  8620272 bytes
Variable Size             939525904 bytes
Database Buffers          318767104 bytes
Redo Buffers                8155136 bytes
SQL>
SQL> create spfile from pfile='/u02/oracle/backup/UOIN1CON_DG/initUOIN1CON_DG.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size                  8620272 bytes
Variable Size             939525904 bytes
Database Buffers          318767104 bytes
Redo Buffers                8155136 bytes
SQL>


18. Verify connection ‘AS SYSDBA’ from Standby

[oracle@rac2 ~]$ sqlplus SYS/SYS@UOIN1CON_DG AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 18:18:57 2019

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


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

SQL>


19. Run RMAN Duplicate for standby

[oracle@rac2 ~]$ cd /u02/oracle/backup/UOIN1CON_DG/
[oracle@rac2 UOIN1CON_DG]$ ls -ltr
total 1614280
-rw-r--r--. 1 oracle dba      1780 Jan  3 17:06 initUOIN1CON_after.ora
-rwxrwxr-x. 1 oracle dba       503 Jan  3 19:05 restore_db.sh
-rw-r--r--. 1 oracle dba      1757 Jan  4 06:38 initUOIN1CON_DG.ora.bkp
-rw-r--r--. 1 oracle dba      1757 Jan  4 06:57 initUOIN1CON_DG.ora
-rwxrwxr-x. 1 oracle dba       670 Jan  5 17:39 duplicate_UOIN1CON_DG.sh
-rw-r-----. 1 oracle dba 726351872 Jan  5 17:43 database_UOIN1CON_17tmj2i0_39
-rw-r-----. 1 oracle dba 435650560 Jan  5 17:43 database_UOIN1CON_18tmj2i0_40
-rw-r-----. 1 oracle dba   6463488 Jan  5 17:43 database_UOIN1CON_19tmj2i0_41
-rw-r-----. 1 oracle dba  10960896 Jan  5 17:43 Control_UOIN1CON_1gtmj2jk_48
-rw-r-----. 1 oracle dba 229672448 Jan  5 17:44 arch_UOIN1CON_1btmj2j9_43
-rw-r-----. 1 oracle dba 125304832 Jan  5 17:44 arch_UOIN1CON_1ctmj2ja_44
-rw-r-----. 1 oracle dba 112978944 Jan  5 17:44 arch_UOIN1CON_1dtmj2ja_45
-rw-r-----. 1 oracle dba   5603328 Jan  5 17:44 arch_UOIN1CON_1etmj2jh_46
[oracle@rac2 UOIN1CON_DG]$

[oracle@rac2 UOIN1CON_DG]$ cat duplicate_UOIN1CON_DG.sh
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
BACKUP_LOG_PATH=/u02/oracle/backup/UOIN1CON_DG
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=UOIN1CON_DG
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/duplicate_UOIN1CON_DG_NEW.log
/u01/app/oracle/product/12.2.0.1/bin/rman msglog=${LOG_FILE} << EOF
connect auxiliary /
run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
SET NEWNAME FOR DATABASE   TO  '/u02/oracle/oradata/UOIN1CON_DG/%b';
duplicate target database for standby backup location '/u02/oracle/backup/UOIN1CON_DG' nofilenamecheck;
}
exit
EOF
[oracle@rac2 UOIN1CON_DG]$
[oracle@rac2 UOIN1CON_DG]$ chmod 775 duplicate_UOIN1CON_DG.sh
[oracle@rac2 UOIN1CON_DG]$ nohup ./duplicate_UOIN1CON_DG.sh &
[1] 8951
[oracle@rac2 UOIN1CON_DG]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@rac2 UOIN1CON_DG]$ jobs -l
[1]+  8951 Running                 nohup ./duplicate_UOIN1CON_DG.sh &
[oracle@rac2 UOIN1CON_DG]$
[oracle@rac2 UOIN1CON_DG]$ jobs -l
[1]+  Done                    nohup ./duplicate_UOIN1CON_DG.sh
[oracle@rac2 UOIN1CON_DG]$ 

[oracle@rac2 UOIN1CON_DG]$ cat duplicate_UOIN1CON_DG_NEW.log
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jan 5 18:25:40 2019

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

RMAN>
connected to auxiliary database: UOIN1CON (not mounted)

RMAN> 2> 3> 4> 5> 6> 7>
allocated channel: t1
channel t1: SID=35 device type=DISK

allocated channel: t2
channel t2: SID=36 device type=DISK

allocated channel: t3
channel t3: SID=37 device type=DISK

executing command: SET NEWNAME

Starting Duplicate Db at 05-JAN-19

contents of Memory Script:
{
   restore clone standby controlfile from  '/u02/oracle/backup/UOIN1CON_DG/Control_UOIN1CON_1gtmj2jk_48';
}
executing Memory Script

Starting restore at 05-JAN-19

channel t2: skipped, AUTOBACKUP already found
channel t3: skipped, AUTOBACKUP already found
channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:08
output file name=/u02/oracle/oradata/UOIN1CON_DG/control01.ctl
output file name=/u02/oracle/oradata/UOIN1CON_DG/control02.ctl
Finished restore at 05-JAN-19

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u02/oracle/oradata/UOIN1CON_DG/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u02/oracle/oradata/UOIN1CON_DG/system01.dbf";
   set newname for datafile  3 to
 "/u02/oracle/oradata/UOIN1CON_DG/sysaux01.dbf";
   set newname for datafile  4 to
 "/u02/oracle/oradata/UOIN1CON_DG/undotbs01.dbf";
   set newname for datafile  5 to
 "/u02/oracle/oradata/UOIN1CON_DG/oggdata01.dbf";
   set newname for datafile  7 to
 "/u02/oracle/oradata/UOIN1CON_DG/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oracle/oradata/UOIN1CON_DG/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 05-JAN-19

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /u02/oracle/oradata/UOIN1CON_DG/system01.dbf
channel t1: reading from backup piece /u02/oracle/backup/UOIN1CON_DG/database_UOIN1CON_17tmj2i0_39
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00003 to /u02/oracle/oradata/UOIN1CON_DG/sysaux01.dbf
channel t2: restoring datafile 00007 to /u02/oracle/oradata/UOIN1CON_DG/users01.dbf
channel t2: reading from backup piece /u02/oracle/backup/UOIN1CON_DG/database_UOIN1CON_18tmj2i0_40
channel t3: starting datafile backup set restore
channel t3: specifying datafile(s) to restore from backup set
channel t3: restoring datafile 00004 to /u02/oracle/oradata/UOIN1CON_DG/undotbs01.dbf
channel t3: restoring datafile 00005 to /u02/oracle/oradata/UOIN1CON_DG/oggdata01.dbf
channel t3: reading from backup piece /u02/oracle/backup/UOIN1CON_DG/database_UOIN1CON_19tmj2i0_41
channel t3: piece handle=/u02/oracle/backup/UOIN1CON_DG/database_UOIN1CON_19tmj2i0_41 tag=TAG20190105T171336
channel t3: restored backup piece 1
channel t3: restore complete, elapsed time: 00:00:07
channel t2: piece handle=/u02/oracle/backup/UOIN1CON_DG/database_UOIN1CON_18tmj2i0_40 tag=TAG20190105T171336
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:15
channel t1: piece handle=/u02/oracle/backup/UOIN1CON_DG/database_UOIN1CON_17tmj2i0_39 tag=TAG20190105T171336
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:25
Finished restore at 05-JAN-19

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=996776783 file name=/u02/oracle/oradata/UOIN1CON_DG/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=996776783 file name=/u02/oracle/oradata/UOIN1CON_DG/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=996776783 file name=/u02/oracle/oradata/UOIN1CON_DG/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=996776783 file name=/u02/oracle/oradata/UOIN1CON_DG/oggdata01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=996776783 file name=/u02/oracle/oradata/UOIN1CON_DG/users01.dbf
Finished Duplicate Db at 05-JAN-19
released channel: t1
released channel: t2
released channel: t3

RMAN>

Recovery Manager complete.
[oracle@rac2 UOIN1CON_DG]$


20. Verify Standby redo logs

SQL> set lines 190
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  MOUNTED              UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>
SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         3         ONLINE  /u02/oracle/oradata/UOIN1CON_DG/redo03.log         NO           0
         2         ONLINE  /u02/oracle/oradata/UOIN1CON_DG/redo02.log         NO           0
         1         ONLINE  /u02/oracle/oradata/UOIN1CON_DG/redo01.log         NO           0
         4         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo04.log         NO           0
         5         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo05.log         NO           0
         6         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo06.log         NO           0
         7         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo07.log         NO           0

7 rows selected.

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 /u02/oracle/oradata/UOIN1CON_DG/redo04.log          209715200
         5 /u02/oracle/oradata/UOIN1CON_DG/redo05.log          209715200
         6 /u02/oracle/oradata/UOIN1CON_DG/redo06.log          209715200
         7 /u02/oracle/oradata/UOIN1CON_DG/redo07.log          209715200

SQL>


21. Enable MRP on STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active <---- This means Standby database configured properly.


SQL>


22. Verify Sync

On Primary

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
UOIN1CON  READ WRITE           PRIMARY          UOIN1CON

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            47 <-----

SQL>

On STANDBY

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
UOIN1CON  MOUNTED              PHYSICAL STANDBY UOIN1CON_DG

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            47 <-----

SQL>


23. Verify ..Lets Test

On Primary

SQL> set lines 180
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  READ WRITE           UOIN1CON                       PRIMARY          MAXIMUM PERFORMANCE

SQL> CREATE TABLE TEST_DG (A NUMBER);

Table created.

SQL> INSERT INTO TEST_DG VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM TEST_DG;

  COUNT(*)
----------
         1 <-------

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>

On Standby

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  MOUNTED              UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>
SQL> alter database open;

Database altered.

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  READ ONLY            UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  READ ONLY WITH APPLY UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> SELECT COUNT(*) FROM TEST_DG;

  COUNT(*)
----------
         1 <----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Create Physical Standby Without RMAN Duplicate Command

How to Create Physical Standby Database using RMAN Backup Without Using Duplicate Command

Contents

___________________________________________________________________________________________________

0. Introduction
1. Environment

On Primary (Step 2 to Step 12)

2. Enable Forced Logging on Primary
3. Check Password File on Primary
4. Configure a Standby Redo Log on Primary
5. Verify Archive Mode Enabled on Primary
6. Set Primary Database Initialization Parameters
7. Backup Primary Database for configure Standby
8. Transfer PASSWORD FILE TO STANDBY SIDE
9. Transfer Backup from Primary to Standby
10. Transfer pfile from primary to standby
11. Configure TNS for Primary
12. Verify connection ‘AS SYSDBA’ from Primary

On STANDBY (Step 13 to Step 24)

13. Configure TNS for STANDBY
14. Configure init parameter for STANDBY
15. Add oratab entry
16. Create required directories
17. Startup Nomount
18. Verify connection ‘AS SYSDBA’ from Standby
19. Restore the Standby Controlfile
20. Mount standby database
21. Catalog all the backup pieces
22. Restore and Recover Database
23. Verify Standby redo logs
24. Enable MRP on STANDBY

25. Verify Sync
26. Verify ..Lets Test
________________________________________________________________________________________________


0. Introduction

PLEASE NOTE in 12c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files.

Definition of Active Dataguard:

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.

Goal : How to Create Physical Standby Database using RMAN Backup Without Using Duplicate Command


1. Environment

Source:

	Platform	: Linuxx86_64
	Server Name	: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
	DB Version	: Oracle 12.2.0.1
	File system     : Normal
	Database Name	: UOIN1CON
        DB_UNIQUE_NAME  : UOIN1CON
	Flashback	: Disabled
	Oracle Home Path: /u01/app/oracle/product/12.2.0.1
		
Target:
		
	Platform	: Linuxx86_64
	Server Name	: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
	DB Version	: Oracle 12.2.0.1
	File system     : Normal
	Database Name	: UOIN1CON
        DB_UNIQUE_NAME  : UOIN1CON_DG
        Flashback	: Disabled
	Oracle Home Path: /u01/app/oracle/product/12.2.0.1

On Primary (Step 2 to Step 12)


2. Enable Forced Logging on Primary

SQL> select name, open_mode,cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
UOIN1CON  READ WRITE           NO

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES <----

SQL>


3. Check Password File on Primary

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/12.2.0.1/dbs
[oracle@rac1 dbs]$ ls -ltr orapwUOIN1CON
-rw-r-----. 1 oracle dba 3584 Dec 14 12:26 orapwUOIN1CON
[oracle@rac1 dbs]$


4. Configure a Standby Redo Log on Primary

-- Since we have 3 online redo log file groups, we need to create 4(3+1) Standby redo log file groups
-- Standy Redo logs files come into picture only when protection mode is Maximum Availability and Maximum Protection.

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

   THREAD#     GROUP# MEMBER                                                            BYTES
---------- ---------- ------------------------------------------------------------ ----------
         1          3 /u02/oracle/oradata/UOIN1CON/redo03.log                       209715200
         1          2 /u02/oracle/oradata/UOIN1CON/redo02.log                       209715200
         1          1 /u02/oracle/oradata/UOIN1CON/redo01.log                       209715200

SQL>

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u02/oracle/oradata/UOIN1CON/redo04.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u02/oracle/oradata/UOIN1CON/redo05.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u02/oracle/oradata/UOIN1CON/redo06.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oracle/oradata/UOIN1CON/redo07.log') SIZE 200M;

Database altered.

SQL>

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
         3         ONLINE  /u02/oracle/oradata/UOIN1CON/redo03.log                      NO           0
         2         ONLINE  /u02/oracle/oradata/UOIN1CON/redo02.log                      NO           0
         1         ONLINE  /u02/oracle/oradata/UOIN1CON/redo01.log                      NO           0
         4         STANDBY /u02/oracle/oradata/UOIN1CON/redo04.log                      NO           0
         5         STANDBY /u02/oracle/oradata/UOIN1CON/redo05.log                      NO           0
         6         STANDBY /u02/oracle/oradata/UOIN1CON/redo06.log                      NO           0
         7         STANDBY /u02/oracle/oradata/UOIN1CON/redo07.log                      NO           0

7 rows selected.

SQL>

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                            BYTES
---------- ------------------------------------------------------------ ----------
         4 /u02/oracle/oradata/UOIN1CON/redo04.log                       209715200
         5 /u02/oracle/oradata/UOIN1CON/redo05.log                       209715200
         6 /u02/oracle/oradata/UOIN1CON/redo06.log                       209715200
         7 /u02/oracle/oradata/UOIN1CON/redo07.log                       209715200

SQL>


5. Verify Archive Mode Enabled on Primary

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/UOIN1CON
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL>


6. Set Primary Database Initialization Parameters

SQL> alter system set db_unique_name='UOIN1CON' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u02/oracle/archive/UOIN1CON VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=UOIN1CON_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON_DG' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET fal_client=UOIN1CON scope=both;

System altered.

SQL>

Please note: The FAL_CLIENT database initialization parameter is no longer required from 11gR2

SQL> ALTER SYSTEM SET fal_server=UOIN1CON_DG scope=both;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>
SQL> create pfile='/home/oracle/initUOIN1CON_after.ora' from spfile;

File created.

SQL> 

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@rac1 ~]$ cat /home/oracle/initUOIN1CON_after.ora

UOIN1CON.__data_transfer_cache_size=0
UOIN1CON.__db_cache_size=369098752
UOIN1CON.__inmemory_ext_roarea=0
UOIN1CON.__inmemory_ext_rwarea=0
UOIN1CON.__java_pool_size=16777216
UOIN1CON.__large_pool_size=33554432
UOIN1CON.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
UOIN1CON.__pga_aggregate_target=587202560
UOIN1CON.__sga_target=687865856
UOIN1CON.__shared_io_pool_size=33554432
UOIN1CON.__shared_pool_size=218103808
UOIN1CON.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/UOIN1CON/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u02/oracle/oradata/UOIN1CON/control01.ctl','/u02/oracle/oradata/UOIN1CON/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON'
*.db_name='UOIN1CON'
*.db_unique_name='UOIN1CON'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=UOIN1CONXDB)'
*.fal_client='UOIN1CON'
*.fal_server='UOIN1CON_DG'
*.log_archive_config='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/UOIN1CON VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON'
*.log_archive_dest_2='SERVICE=UOIN1CON_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON_DG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON'
*.memory_target=1201m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac1 ~]$


7. Backup Primary Database for configure Standby

[oracle@rac1 ~]$ mkdir -p /u02/oracle/backup/UOIN1CON

[oracle@rac1 ~]$ mkdir -p /u02/oracle/backup/UOIN1CON
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd /u02/oracle/backup/UOIN1CON
[oracle@rac1 UOIN1CON]$ vi rmanbackup.sh
[oracle@rac1 UOIN1CON]$ chmod 775 rmanbackup.sh
[oracle@rac1 UOIN1CON]$

[oracle@rac1 UOIN1CON]$ cat rmanbackup.sh
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
BACKUP_LOG_PATH=/u02/oracle/backup/UOIN1CON
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=UOIN1CON
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/backup_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} << EOF 
connect target / 
run { 
allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
backup database format '/u02/oracle/backup/UOIN1CON/database_%d_%u_%s'; release channel t1; 
release channel t2; 
release channel t3; 
} 
sql 'alter system archive log current'; 
run { 
allocate channel a1 type disk; 
allocate channel a2 type disk; 
allocate channel a3 type disk; 
backup archivelog all format '/u02/oracle/backup/UOIN1CON/arch_%d_%u_%s'; release channel a1; 
release channel a2; 
release channel a3; 
} 
run { 
allocate channel c1 type disk; 
backup current controlfile for standby format '/u02/oracle/backup/UOIN1CON/Control_%d_%u_%s'; 
release channel c1; 
} 
exit; 
EOF 
[oracle@rac1 UOIN1CON]$ 
[oracle@rac1 UOIN1CON]$ ls -ltr 
total 4 
-rwxrwxr-x. 1 oracle dba 982 Jan 3 16:44 rmanbackup.sh 
[oracle@rac1 UOIN1CON]$ 
[oracle@rac1 UOIN1CON]$ nohup ./rmanbackup.sh & 
[1] 6460 
[oracle@rac1 UOIN1CON]$ nohup: ignoring input and appending output to `nohup.out' 
[oracle@rac1 UOIN1CON]$ jobs -l 
[1]+ 6460 Running nohup ./rmanbackup.sh & <-----
[oracle@rac1 UOIN1CON]$ 
[oracle@rac1 UOIN1CON]$ ls -ltr total 1589364 
-rwxrwxr-x. 1 oracle dba 982 Jan 3 16:44 rmanbackup.sh 
-rw-r-----. 1 oracle dba 3964928 Jan 3 16:45 database_UOIN1CON_05tmdo64_5 
-rw-r-----. 1 oracle dba 395157504 Jan 3 16:46 database_UOIN1CON_04tmdo64_4 
-rw-r-----. 1 oracle dba 723779584 Jan 3 16:46 database_UOIN1CON_03tmdo64_3 
-rw-r-----. 1 oracle dba 106033664 Jan 3 16:46 arch_UOIN1CON_09tmdo7a_9 
-rw-r-----. 1 oracle dba 165638144 Jan 3 16:46 arch_UOIN1CON_08tmdo7a_8 
-rw-r-----. 1 oracle dba 221474304 Jan 3 16:46 arch_UOIN1CON_07tmdo7a_7 
-rw-r-----. 1 oracle dba 761344 Jan 3 16:46 arch_UOIN1CON_0atmdo7j_10 
-rw-r-----. 1 oracle dba 10665984 Jan 3 16:46 Control_UOIN1CON_0ctmdo7o_12 
-rw-------. 1 oracle dba 96 Jan 3 16:46 nohup.out 
-rw-r--r--. 1 oracle dba 5507 Jan 3 16:46 backup_db.log 
[oracle@rac1 UOIN1CON]$ 
[oracle@rac1 UOIN1CON]$ cat backup_db.log 
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 3 16:45:53 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. RMAN>
connected to target database: UOIN1CON (DBID=1821803680)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=75 device type=DISK

allocated channel: t2
channel t2: SID=96 device type=DISK

allocated channel: t3
channel t3: SID=97 device type=DISK

Starting backup at 03-JAN-19
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/UOIN1CON/system01.dbf
channel t1: starting piece 1 at 03-JAN-19
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oracle/oradata/UOIN1CON/sysaux01.dbf
input datafile file number=00007 name=/u02/oracle/oradata/UOIN1CON/users01.dbf
channel t2: starting piece 1 at 03-JAN-19
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/oracle/oradata/UOIN1CON/oggdata01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/UOIN1CON/undotbs01.dbf
channel t3: starting piece 1 at 03-JAN-19
channel t3: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_05tmdo64_5 tag=TAG20190103T164555 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t2: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_04tmdo64_4 tag=TAG20190103T164555 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:25
channel t1: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_03tmdo64_3 tag=TAG20190103T164555 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:35
Finished backup at 03-JAN-19

Starting Control File and SPFILE Autobackup at 03-JAN-19
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190103-01 comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-19

released channel: t1

released channel: t2

released channel: t3

RMAN>
sql statement: alter system archive log current

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: SID=75 device type=DISK

allocated channel: a2
channel a2: SID=96 device type=DISK

allocated channel: a3
channel a3: SID=97 device type=DISK

Starting backup at 03-JAN-19
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=2 STAMP=994879681
input archived log thread=1 sequence=3 RECID=3 STAMP=994928354
channel a1: starting piece 1 at 03-JAN-19
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=994859424
channel a2: starting piece 1 at 03-JAN-19
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=4 STAMP=996595104
input archived log thread=1 sequence=5 RECID=5 STAMP=996596970
input archived log thread=1 sequence=6 RECID=6 STAMP=996596978
channel a3: starting piece 1 at 03-JAN-19
channel a2: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_08tmdo7a_8 tag=TAG20190103T164634 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:07
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=7 STAMP=996597993
input archived log thread=1 sequence=8 RECID=8 STAMP=996597994
channel a2: starting piece 1 at 03-JAN-19
channel a3: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_09tmdo7a_9 tag=TAG20190103T164634 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:09
channel a1: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_07tmdo7a_7 tag=TAG20190103T164634 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:10
channel a2: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10 tag=TAG20190103T164634 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-19

Starting Control File and SPFILE Autobackup at 03-JAN-19
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190103-02 comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-19

released channel: a1

released channel: a2

released channel: a3

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=75 device type=DISK

Starting backup at 03-JAN-19
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 03-JAN-19
channel c1: finished piece 1 at 03-JAN-19
piece handle=/u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12 tag=TAG20190103T164648 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-19

Starting Control File and SPFILE Autobackup at 03-JAN-19
piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190103-03 comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-19

released channel: c1

RMAN>

Recovery Manager complete.
[oracle@rac1 UOIN1CON]$


8. Transfer PASSWORD FILE TO STANDBY SIDE

-- Copy the password file from the primary to standby $ORACLE_HOME/dbs and rename it to the standby database name.
-- The username is required to be SYS and the password needs to be the same on the Primary and Standby.
-- The best practice for this is to copy the passwordfile as suggested.
-- The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@rac1 UOIN1CON]$ cd /u01/app/oracle/product/12.2.0.1/dbs/
[oracle@rac1 dbs]$ ls -ltr orapwUOIN1CON
-rw-r-----. 1 oracle dba 3584 Dec 14 12:26 orapwUOIN1CON
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ scp orapwUOIN1CON oracle@rac2:/u01/app/oracle/product/12.2.0.1/dbs/orapwUOIN1CON_DG
oracle@rac2's password:
orapwUOIN1CON                       100% 3584     3.5KB/s   00:00
[oracle@rac1 dbs]$


9. Transfer Backup from Primary to Standby

[oracle@rac1 ~]$ cd /u02/oracle/backup/UOIN1CON/
[oracle@rac1 UOIN1CON]$ ls -ltr
total 1589364
-rwxrwxr-x. 1 oracle dba       982 Jan  3 16:44 rmanbackup.sh
-rw-r-----. 1 oracle dba   3964928 Jan  3 16:45 database_UOIN1CON_05tmdo64_5
-rw-r-----. 1 oracle dba 395157504 Jan  3 16:46 database_UOIN1CON_04tmdo64_4
-rw-r-----. 1 oracle dba 723779584 Jan  3 16:46 database_UOIN1CON_03tmdo64_3
-rw-r-----. 1 oracle dba 106033664 Jan  3 16:46 arch_UOIN1CON_09tmdo7a_9
-rw-r-----. 1 oracle dba 165638144 Jan  3 16:46 arch_UOIN1CON_08tmdo7a_8
-rw-r-----. 1 oracle dba 221474304 Jan  3 16:46 arch_UOIN1CON_07tmdo7a_7
-rw-r-----. 1 oracle dba    761344 Jan  3 16:46 arch_UOIN1CON_0atmdo7j_10
-rw-r-----. 1 oracle dba  10665984 Jan  3 16:46 Control_UOIN1CON_0ctmdo7o_12
-rw-------. 1 oracle dba        96 Jan  3 16:46 nohup.out
-rw-r--r--. 1 oracle dba      5507 Jan  3 16:46 backup_db.log
[oracle@rac1 UOIN1CON]$
[oracle@rac1 UOIN1CON]$ scp database_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON/
oracle@rac2's password:
database_UOIN1CON_03tmdo64_3                           100%  690MB  43.1MB/s   00:16
database_UOIN1CON_04tmdo64_4                           100%  377MB  26.9MB/s   00:14
database_UOIN1CON_05tmdo64_5                           100% 3872KB   3.8MB/s   00:00
[oracle@rac1 UOIN1CON]$
[oracle@rac1 UOIN1CON]$ scp Control_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON/
oracle@rac2's password:
Control_UOIN1CON_0ctmdo7o_12                           100%   10MB  10.2MB/s   00:00
[oracle@rac1 UOIN1CON]$
[oracle@rac1 UOIN1CON]$ scp arch_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON/
oracle@rac2's password:
arch_UOIN1CON_07tmdo7a_7                               100%  211MB  70.4MB/s   00:03
arch_UOIN1CON_08tmdo7a_8                               100%  158MB  52.7MB/s   00:03
arch_UOIN1CON_09tmdo7a_9                               100%  101MB  25.3MB/s   00:04
arch_UOIN1CON_0atmdo7j_10                              100%  744KB 743.5KB/s   00:00
[oracle@rac1 UOIN1CON]$


10. Transfer pfile from primary to standby

[oracle@rac1 ~]$ scp initUOIN1CON_after.ora oracle@rac2:/u02/oracle/backup/UOIN1CON/
oracle@rac2's password:
initUOIN1CON_after.ora                                100% 1780     1.7KB/s   00:00
[oracle@rac1 ~]$


11. Configure TNS for Primary

[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UOIN1CON)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = UOIN1CON)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

UOIN1CON_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UOIN1CON_DG)
    )
  )

UOIN1CON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = UOIN1CON)
    )
  )

LISTENER_12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))

[oracle@rac1 admin]$

[oracle@rac1 admin]$ lsnrctl status LISTENER_12C

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-JAN-2019 18:19:39

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_12C
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                03-JAN-2019 17:18:26
Uptime                    0 days 1 hr. 1 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_12c/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "UOIN1CON" has 1 instance(s).
  Instance "UOIN1CON", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$


12. Verify connection ‘AS SYSDBA’ from Primary

[oracle@rac1 ~]$ sqlplus SYS/SYS@UOIN1CON AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 3 18:28:47 2019

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


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

SQL>

On STANDBY (Step 13 to Step 24)


13. Configure TNS for STANDBY

[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UOIN1CON_DG)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = UOIN1CON_DG)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac2 admin]$

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

UOIN1CON_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UOIN1CON_DG)
    )
  )

UOIN1CON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UOIN1CON)
    )
  )

LISTENER_12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))

[oracle@rac2 admin]$

[oracle@rac2 admin]$ lsnrctl status LISTENER_12C

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-JAN-2019 18:14:00

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_12C
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                03-JAN-2019 18:01:29
Uptime                    0 days 0 hr. 12 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener_12c/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "UOIN1CON_DG" has 1 instance(s).
  Instance "UOIN1CON_DG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$


14. Configure init parameter for STANDBY

[oracle@rac2 UOIN1CON]$ cat initUOIN1CON_DG.ora

UOIN1CON_DG.__data_transfer_cache_size=0
UOIN1CON_DG.__db_cache_size=369098752
UOIN1CON_DG.__inmemory_ext_roarea=0
UOIN1CON_DG.__inmemory_ext_rwarea=0
UOIN1CON_DG.__java_pool_size=16777216
UOIN1CON_DG.__large_pool_size=33554432
UOIN1CON_DG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
UOIN1CON_DG.__pga_aggregate_target=587202560
UOIN1CON_DG.__sga_target=687865856
UOIN1CON_DG.__shared_io_pool_size=33554432
UOIN1CON_DG.__shared_pool_size=218103808
UOIN1CON_DG.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/UOIN1CON_DG/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u02/oracle/oradata/UOIN1CON_DG/control01.ctl','/u02/oracle/oradata/UOIN1CON_DG/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u02/oracle/oradata/UOIN1CON','/u02/oracle/oradata/UOIN1CON_DG'
*.db_name='UOIN1CON'
*.db_unique_name='UOIN1CON_DG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=UOIN1CON_DGXDB)'
*.fal_client='UOIN1CON_DG'
*.fal_server='UOIN1CON'
*.log_archive_config='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/UOIN1CON_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON_DG'
*.log_archive_dest_2='SERVICE=UOIN1CON LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u02/oracle/oradata/UOIN1CON','/u02/oracle/oradata/UOIN1CON_DG'
*.memory_target=1201m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac2 UOIN1CON]$


15. Add oratab entry

[oracle@rac2 ~]$ cat /etc/oratab | grep -i UOIN1CON_DG
UOIN1CON_DG:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac2 ~]$


16. Create required directories

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/UOIN1CON_DG/adump
[oracle@rac2 ~]$ mkdir -p /u02/oracle/oradata/UOIN1CON_DG
[oracle@rac2 ~]$ mkdir -p /u02/oracle/archive/UOIN1CON_DG
[oracle@rac2 ~]$


17. Startup Nomount

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [UOIN1CON_DG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 3 17:55:32 2019

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

Connected to an idle instance.

SQL> startup nomount pfile='/u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora';
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size                  8620272 bytes
Variable Size             939525904 bytes
Database Buffers          318767104 bytes
Redo Buffers                8155136 bytes
SQL>
SQL> create spfile from pfile='/u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size                  8620272 bytes
Variable Size             939525904 bytes
Database Buffers          318767104 bytes
Redo Buffers                8155136 bytes
SQL>


18. Verify connection ‘AS SYSDBA’ from Standby

[oracle@rac2 ~]$ sqlplus SYS/SYS@UOIN1CON_DG AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 3 18:30:01 2019

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


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

SQL>


19. Restore the Standby Controlfile

[oracle@rac2 ~]$ cd /u02/oracle/backup/UOIN1CON/
[oracle@rac2 UOIN1CON]$ ls -ltr
total 1589356
-rw-r-----. 1 oracle dba 723779584 Jan  3 17:02 database_UOIN1CON_03tmdo64_3
-rw-r-----. 1 oracle dba 395157504 Jan  3 17:02 database_UOIN1CON_04tmdo64_4
-rw-r-----. 1 oracle dba   3964928 Jan  3 17:02 database_UOIN1CON_05tmdo64_5
-rw-r-----. 1 oracle dba  10665984 Jan  3 17:02 Control_UOIN1CON_0ctmdo7o_12
-rw-r-----. 1 oracle dba 221474304 Jan  3 17:03 arch_UOIN1CON_07tmdo7a_7
-rw-r-----. 1 oracle dba 165638144 Jan  3 17:03 arch_UOIN1CON_08tmdo7a_8
-rw-r-----. 1 oracle dba 106033664 Jan  3 17:03 arch_UOIN1CON_09tmdo7a_9
-rw-r-----. 1 oracle dba    761344 Jan  3 17:03 arch_UOIN1CON_0atmdo7j_10
-rw-r--r--. 1 oracle dba      1780 Jan  3 17:06 initUOIN1CON_after.ora
-rw-r--r--. 1 oracle dba      1756 Jan  3 17:54 initUOIN1CON_DG.ora
[oracle@rac2 UOIN1CON]$
[oracle@rac2 UOIN1CON]$

[oracle@rac2 UOIN1CON]$ . oraenv
ORACLE_SID = [UOIN1CON_DG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 UOIN1CON]$
[oracle@rac2 UOIN1CON]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 3 18:36:19 2019

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

connected to target database: UOIN1CON (not mounted)

RMAN> restore standby controlfile from '/u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12';

Starting restore at 03-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/oracle/oradata/UOIN1CON_DG/control01.ctl
output file name=/u02/oracle/oradata/UOIN1CON_DG/control02.ctl
Finished restore at 03-JAN-19

RMAN>


20. Mount standby database

RMAN> sql 'alter database mount standby database';

sql statement: alter database mount standby database
released channel: ORA_DISK_1

RMAN>


21. Catalog all the backup pieces

RMAN> catalog start with '/u02/oracle/backup/UOIN1CON';

searching for all files that match the pattern /u02/oracle/backup/UOIN1CON

List of Files Unknown to the Database
=====================================
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora
File Name: /u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_after.ora

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12

List of Files Which Were Not Cataloged
=======================================
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora
  RMAN-07517: Reason: The file header is corrupted
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_after.ora
  RMAN-07517: Reason: The file header is corrupted

RMAN>


22. Restore and Recover Database

RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       101.12M    DISK        00:00:04     03-JAN-19
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20190103T164634
        Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_09tmdo7a_9

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    4       1542199    15-DEC-18 1659977    03-JAN-19
  1    5       1659977    03-JAN-19 1669907    03-JAN-19
  1    6       1669907    03-JAN-19 1669928    03-JAN-19

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       157.96M    DISK        00:00:06     03-JAN-19
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20190103T164634
        Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_08tmdo7a_8

  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       1415033    14-DEC-18 1432129    14-DEC-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       211.21M    DISK        00:00:07     03-JAN-19
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20190103T164634
        Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_07tmdo7a_7

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2       1432129    14-DEC-18 1486417    14-DEC-18
  1    3       1486417    14-DEC-18 1542199    15-DEC-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10      743.00K    DISK        00:00:00     03-JAN-19
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20190103T164634
        Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10

  List of Archived Logs in backup set 10
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7       1669928    03-JAN-19 1672222    03-JAN-19
  1    8       1672222    03-JAN-19 1672247    03-JAN-19  <--- 8+1 =9

RMAN>



[oracle@rac2 UOIN1CON]$ cat restore_db.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
BACKUP_LOG_PATH=/u02/oracle/backup/UOIN1CON
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=UOIN1CON_DG
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/restore_db.log
/u01/app/oracle/product/12.2.0.1/bin/rman msglog=${LOG_FILE} << EOF
connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
set until sequence 9; <---- 8+1
restore database;
recover database;
}
exit
EOF

[oracle@rac2 UOIN1CON]$

[oracle@rac2 UOIN1CON]$ chmod 775 restore_db.sh
[oracle@rac2 UOIN1CON]$
[oracle@rac2 UOIN1CON]$ nohup ./restore_db.sh &
[1] 13033
[oracle@rac2 UOIN1CON]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@rac2 UOIN1CON]$ jobs -l
[1]+ 13033 Running                 nohup ./restore_db.sh &
[oracle@rac2 UOIN1CON]$
[oracle@rac2 UOIN1CON]$


[oracle@rac2 UOIN1CON]$ cat restore_db.log

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 3 19:06:40 2019

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

RMAN>
connected to target database: UOIN1CON (DBID=1821803680, not open)

RMAN> 2> 3> 4> 5> 6> 7> 8>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=69 device type=DISK

allocated channel: t2
channel t2: SID=64 device type=DISK

allocated channel: t3
channel t3: SID=68 device type=DISK

executing command: SET until clause

Starting restore at 03-JAN-19

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00004 to /u02/oracle/oradata/UOIN1CON_DG/undotbs01.dbf
channel t1: restoring datafile 00005 to /u02/oracle/oradata/UOIN1CON_DG/oggdata01.dbf
channel t1: reading from backup piece /u02/oracle/backup/UOIN1CON/database_UOIN1CON_05tmdo64_5
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00003 to /u02/oracle/oradata/UOIN1CON_DG/sysaux01.dbf
channel t2: restoring datafile 00007 to /u02/oracle/oradata/UOIN1CON_DG/users01.dbf
channel t2: reading from backup piece /u02/oracle/backup/UOIN1CON/database_UOIN1CON_04tmdo64_4
channel t3: starting datafile backup set restore
channel t3: specifying datafile(s) to restore from backup set
channel t3: restoring datafile 00001 to /u02/oracle/oradata/UOIN1CON_DG/system01.dbf
channel t3: reading from backup piece /u02/oracle/backup/UOIN1CON/database_UOIN1CON_03tmdo64_3
channel t1: piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_05tmdo64_5 tag=TAG20190103T164555
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:07
channel t2: piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_04tmdo64_4 tag=TAG20190103T164555
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:40
channel t3: piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_03tmdo64_3 tag=TAG20190103T164555
channel t3: restored backup piece 1
channel t3: restore complete, elapsed time: 00:00:50
Finished restore at 03-JAN-19

Starting recover at 03-JAN-19

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7
channel t1: restoring archived log
archived log thread=1 sequence=8
channel t1: reading from backup piece /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10
channel t1: piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10 tag=TAG20190103T164634
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oracle/archive/UOIN1CON_DG/1_7_994854306.dbf thread=1 sequence=7
archived log file name=/u02/oracle/archive/UOIN1CON_DG/1_8_994854306.dbf thread=1 sequence=8
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/oracle/oradata/UOIN1CON_DG/system01.dbf'

media recovery complete, elapsed time: 00:00:00
Finished recover at 03-JAN-19
released channel: t1
released channel: t2
released channel: t3

RMAN>

Recovery Manager complete.
[oracle@rac2 UOIN1CON]$

No Worries about these errors...we can safely ignore and proceed further.


23. Verify Standby redo logs

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  MOUNTED              UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>
SQL> set lines 180
SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         3         ONLINE  /u02/oracle/oradata/UOIN1CON_DG/redo03.log         NO           0
         2         ONLINE  /u02/oracle/oradata/UOIN1CON_DG/redo02.log         NO           0
         1         ONLINE  /u02/oracle/oradata/UOIN1CON_DG/redo01.log         NO           0
         4         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo04.log         NO           0
         5         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo05.log         NO           0
         6         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo06.log         NO           0
         7         STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo07.log         NO           0

7 rows selected.

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 /u02/oracle/oradata/UOIN1CON_DG/redo04.log          209715200
         5 /u02/oracle/oradata/UOIN1CON_DG/redo05.log          209715200
         6 /u02/oracle/oradata/UOIN1CON_DG/redo06.log          209715200
         7 /u02/oracle/oradata/UOIN1CON_DG/redo07.log          209715200

SQL>


24. Enable MRP on STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active <--- This means standby database configured correctly 


SQL>


25. Verify Sync

On Primary

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
UOIN1CON  READ WRITE           PRIMARY          UOIN1CON

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            21 <----

SQL>

On STANDBY

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
UOIN1CON  MOUNTED              PHYSICAL STANDBY UOIN1CON_DG

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            21 <----

SQL>


26. Verify ..Lets Test

On Primary

SQL> set lines 180
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  READ WRITE           UOIN1CON                       PRIMARY          MAXIMUM PERFORMANCE

SQL> CREATE TABLE TEST (A NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
         1 <-----

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>

On Standby

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  MOUNTED              UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>
SQL> alter database open;

Database altered.

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  READ ONLY            UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
UOIN1CON  READ ONLY WITH APPLY UOIN1CON_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
         1 <----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Convert Snapshot database to Physical Standby database

Convert Snapshot database to Physical Standby database

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. Environment
2. Configure Snapshot Standby Database (DELL_DG)
3. Verify Primary Database archive logs really applying on snapshot database?
4. Covert to physical standby from snapshot standby
5. Verify snapshot database changes flushed out after converting to physical standby
6. Verify Archive log GAP
___________________________________________________________________________________________________


0. Overview

Primary database changes will not applied to snapshot standby database why because there is no MRP process running on snapshot database.

Whatever changes done on snapshot standby will be flushed out once convert back to physical standby database from snapshot standby.


1. Environment

PRIMARY: (DELL)

Platform          : Linuxx86_64
Server Name       : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version        : Oracle 11.2.0.4
File system	  : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

STANDBY: (DELL_DG)

		
Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version        : Oracle 11.2.0.4
File system       : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4


2. Configure Snapshot Standby Database (DELL_DG)


3. Verify Primary Database archive logs really applying on snapshot database?

On Primary

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL             PRIMARY          READ WRITE

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            62 <----

SQL>

On Standby

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          SNAPSHOT STANDBY READ WRITE

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            58 <---- new archive logs not applied on standby

SQL>

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              61
ARCH      CLOSING              58
ARCH      CONNECTED             0
ARCH      CLOSING              62
RFS       IDLE                  0
RFS       IDLE                 63
RFS       IDLE                  0

7 rows selected.

SQL>
SQL> !ps -ef | grep mrp
oracle   28016  8812  0 16:12 pts/0    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle   28018 28016  0 16:12 pts/0    00:00:00 grep mrp

SQL>

Primary database changes will not applied to snapshot standby database why because there is no MRP process running on snapshot database.


4. Covert to physical standby from snapshot standby

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          SNAPSHOT STANDBY READ WRITE

SQL> 
SQL> create user raj identified by raj default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to raj;

Grant succeeded.

SQL> conn raj/raj;
Connected.
SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
         3 <-----

SQL> 

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          SNAPSHOT STANDBY READ WRITE

SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
Database mounted.
SQL>
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      DELL_DG          SNAPSHOT STANDBY MOUNTED

SQL> alter database convert to physical standby;

Database altered.

SQL>
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
select status,instance_name,database_role,open_mode from v$database,v$Instance
                                                         *
ERROR at line 1:
ORA-01507: database not mounted


SQL>
SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
Database mounted.
Database opened.
SQL>
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          PHYSICAL STANDBY READ ONLY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          PHYSICAL STANDBY READ ONLY WITH APPLY

SQL>
SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CLOSING              64
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 65
MRP0      WAIT_FOR_LOG         65

8 rows selected.

SQL>
SQL> !ps -ef | grep mrp
oracle   29573     1  4 16:47 ?        00:00:01 ora_mrp0_DELL_DG
oracle   29577  8812  0 16:48 pts/0    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle   29579 29577  0 16:48 pts/0    00:00:00 grep mrp

SQL>




Alert log


Completed: ALTER DATABASE   MOUNT
Sat Dec 29 16:43:35 2018
Using STANDBY_ARCHIVE_DEST parameter default value as /u02/oracle/archive/DELL_DG
Sat Dec 29 16:43:37 2018
RFS[1]: Assigned to RFS process 29466
RFS[1]: Selected log 5 for thread 1 sequence 64 dbid -329878705 branch 994761301
Sat Dec 29 16:43:37 2018
RFS[2]: Assigned to RFS process 29468
RFS[2]: Selected log 4 for thread 1 sequence 63 dbid -329878705 branch 994761301
Starting Data Guard Broker (DMON)
Archived Log entry 125 added for thread 1 sequence 63 ID 0xec5daac9 dest 1:
Sat Dec 29 16:43:43 2018
INSV started with pid=29, OS id=29470
Sat Dec 29 16:43:43 2018
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (DELL_DG)
Killing 3 processes with pids 29464,29466,29468 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 29462
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file /u02/oracle/oradata/fast_recovery_area/DELL_DG/flashback/o1_mf_g2h0cny8_.flb
Deleted Oracle managed file /u02/oracle/oradata/fast_recovery_area/DELL_DG/flashback/o1_mf_g2h0cpxn_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 3966853654 (0xec716216)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Shutting down archive processes
Archiving is disabled
Sat Dec 29 16:43:48 2018
ARCH shutting down
ARC3: Archival stopped
Sat Dec 29 16:43:48 2018
ARCH shutting down
ARC2: Archival stopped
Sat Dec 29 16:43:48 2018
ARCH shutting down
ARC1: Archival stopped
Sat Dec 29 16:43:48 2018
ARCH shutting down
ARC0: Archival stopped
Sat Dec 29 16:43:58 2018
Completed: Data Guard Broker cleanup
Restarting Data Guard Broker (DMON)
Sat Dec 29 16:43:58 2018
Completed: alter database convert to physical standby
Sat Dec 29 16:43:58 2018
DMON started with pid=21, OS id=29475
..
..
..
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 4
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Shutting down Data Guard Broker processes
Sat Dec 29 16:46:40 2018
Stopping background process VKTM
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sat Dec 29 16:46:42 2018
Instance shutdown complete
Sat Dec 29 16:46:45 2018
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Number of processor cores in the system is 1
Number of processor sockets in the system is 1
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4
System name:    Linux
Node name:      rac2.rajasekhar.com
Release:        4.1.12-37.5.1.el6uek.x86_64
Version:        #2 SMP Thu Jun 9 15:56:37 PDT 2016
Machine:        x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0.4/dbs/spfileDELL_DG.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 1216M
  control_files            = "/u02/oracle/oradata/DELL_DG/control01.ctl"
  control_files            = "/u02/oracle/oradata/fast_recovery_area/DELL_DG/control02.ctl"
  db_file_name_convert     = "/u02/oracle/oradata/DELL"
  db_file_name_convert     = "/u02/oracle/oradata/DELL_DG"
  log_file_name_convert    = "/u02/oracle/oradata/DELL"
  log_file_name_convert    = "/u02/oracle/oradata/DELL_DG"
  db_block_size            = 8192
  compatible               = "11.2.0.4.0"
  log_archive_dest_1       = "LOCATION=/u02/oracle/archive/DELL_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL_DG"
  log_archive_dest_2       = "SERVICE=DELL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL"
  log_archive_dest_state_1 = "ENABLE"
  log_archive_dest_state_2 = "ENABLE"
  fal_client               = "DELL_DG"
  fal_server               = "DELL"
  log_archive_config       = "DG_CONFIG=(DELL,DELL_DG)"
  log_archive_format       = "%t_%s_%r.dbf"
  db_recovery_file_dest    = "/u02/oracle/oradata/fast_recovery_area"
  db_recovery_file_dest_size= 3G
  standby_file_management  = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=DELL_DGXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/DELL_DG/adump"
  audit_trail              = "DB"
  db_name                  = "DELL"
  db_unique_name           = "DELL_DG"
  open_cursors             = 300
  dg_broker_start          = TRUE
  diagnostic_dest          = "/u01/app/oracle"
Sat Dec 29 16:46:45 2018
PMON started with pid=2, OS id=29500
Sat Dec 29 16:46:45 2018
PSP0 started with pid=3, OS id=29502
Sat Dec 29 16:46:46 2018
VKTM started with pid=4, OS id=29505 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sat Dec 29 16:46:46 2018
GEN0 started with pid=5, OS id=29509
Sat Dec 29 16:46:46 2018
DIAG started with pid=6, OS id=29511
Sat Dec 29 16:46:46 2018
DBRM started with pid=7, OS id=29513
Sat Dec 29 16:46:46 2018
DIA0 started with pid=8, OS id=29515
Sat Dec 29 16:46:46 2018
MMAN started with pid=9, OS id=29517
Sat Dec 29 16:46:46 2018
DBW0 started with pid=10, OS id=29519
Sat Dec 29 16:46:46 2018
LGWR started with pid=11, OS id=29521
Sat Dec 29 16:46:46 2018
CKPT started with pid=12, OS id=29523
Sat Dec 29 16:46:46 2018
SMON started with pid=13, OS id=29525
Sat Dec 29 16:46:46 2018
RECO started with pid=14, OS id=29527
Sat Dec 29 16:46:46 2018
MMON started with pid=15, OS id=29529
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Dec 29 16:46:46 2018
MMNL started with pid=16, OS id=29531
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Sat Dec 29 16:46:46 2018
DMON started with pid=19, OS id=29537
Sat Dec 29 16:46:46 2018
ALTER DATABASE   MOUNT
ARCH: STARTING ARCH PROCESSES
Sat Dec 29 16:46:50 2018
ARC0 started with pid=21, OS id=29544
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sat Dec 29 16:46:50 2018
ARC1 started with pid=22, OS id=29546
Successful mount of redo thread 1, with mount id 3966920310
Physical Standby Database mounted.
Lost write protection disabled
Sat Dec 29 16:46:50 2018
ARC2 started with pid=23, OS id=29548
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
Sat Dec 29 16:46:50 2018
ARC3 started with pid=24, OS id=29550
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Completed: ALTER DATABASE   MOUNT
Sat Dec 29 16:46:51 2018
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
Sat Dec 29 16:46:52 2018
Using STANDBY_ARCHIVE_DEST parameter default value as /u02/oracle/archive/DELL_DG
Sat Dec 29 16:46:52 2018
Primary database is in MAXIMUM PERFORMANCE mode
Sat Dec 29 16:46:52 2018
RFS[1]: Assigned to RFS process 29555
RFS[1]: Selected log 5 for thread 1 sequence 64 dbid -329878705 branch 994761301
RFS[2]: Assigned to RFS process 29557
RFS[2]: Selected log 4 for thread 1 sequence 65 dbid -329878705 branch 994761301
Archived Log entry 126 added for thread 1 sequence 64 ID 0xec5daac9 dest 1:
Starting Data Guard Broker (DMON)
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Sat Dec 29 16:46:56 2018
INSV started with pid=28, OS id=29559
SMON: enabling cache recovery
Dictionary check beginning
Dictionary check complete
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: ALTER DATABASE OPEN
Sat Dec 29 16:46:56 2018
db_recovery_file_dest_size of 3072 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sat Dec 29 16:47:32 2018
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (DELL_DG)
Sat Dec 29 16:47:32 2018
MRP0 started with pid=29, OS id=29573
MRP0: Background Managed Standby Recovery process started (DELL_DG)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u02/oracle/oradata/DELL_DG/redo01.log
Clearing online log 1 of thread 1 sequence number 65
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u02/oracle/oradata/DELL_DG/redo02.log
Clearing online log 2 of thread 1 sequence number 2
Clearing online redo logfile 2 complete
Media Recovery Log /u02/oracle/archive/DELL_DG/1_59_994761301.dbf
Completed: alter database recover managed standby database disconnect from session
Media Recovery Log /u02/oracle/archive/DELL_DG/1_60_994761301.dbf
Media Recovery Log /u02/oracle/archive/DELL_DG/1_61_994761301.dbf
Media Recovery Log /u02/oracle/archive/DELL_DG/1_62_994761301.dbf
Media Recovery Log /u02/oracle/archive/DELL_DG/1_63_994761301.dbf
Media Recovery Log /u02/oracle/archive/DELL_DG/1_64_994761301.dbf
Media Recovery Waiting for thread 1 sequence 65 (in transit)


5. Verify snapshot database changes flushed out after converting to physical standby

SQL> select username,account_status from dba_users where username='RAJ';

no rows selected  <----- USER RAJ not exist... all snapshot data flushed out.

SQL> select count(*) from raj.test;
select count(*) from raj.test
                         *
ERROR at line 1:
ORA-00942: table or view does not exist  <-----


SQL>

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          PHYSICAL STANDBY READ ONLY WITH APPLY

SQL>


6. Verify Archive log GAP

On PRIMARY

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            64 <-----

SQL>


On Standby

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            64 <---

SQL>

=================================== OR ===================================

How to do perform the same using Dataguard Broker

Using DG Broker Convert Snapshot standby to Physical standby

DGMGRL> SHOW CONFIGURATION;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Snapshot standby database <----

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> CONVERT DATABASE "DELL_DG" TO PHYSICAL STANDBY;
Converting database "DELL_DG" to a Physical Standby database, please wait...
Operation requires shutdown of instance "DELL_DG" on database "DELL_DG"
Shutting down instance "DELL_DG"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DELL_DG" on database "DELL_DG"
Starting instance "DELL_DG"...
ORACLE instance started.
Database mounted.
Continuing to convert database "DELL_DG" ...
Operation requires shutdown of instance "DELL_DG" on database "DELL_DG"
Shutting down instance "DELL_DG"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DELL_DG" on database "DELL_DG"
Starting instance "DELL_DG"...
ORACLE instance started.
Database mounted.
Database "DELL_DG" converted successfully
DGMGRL>
DGMGRL> SHOW CONFIGURATION;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Physical standby database <-----

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +65-94693551

Configure Snapshot Standby

Covert Physical Standby database to Snapshot Standby database R/W mode.

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. Environment
2. Configure Standby Database (DELL_DG)
3. Verify archive log GAP
4. Verify Flash Recovery Area and Flashback database
5. Cancel MRP on Standby
6. Covert to snapshot standby from physical standby
7. Testing
___________________________________________________________________________________________________


0. Overview

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database.

To Configure snapshot standby, first we need to create physical standby and then we will convert from physical standby to Snapshot standby

Developers want to testing on Fresh Live Data, but DBA can't allow them to test on Primary, then how to archive developer requirement.. DBA can convert physical standby to snapshot standby in R/W mode. Hence Developers can make their changes in Snapshot databases.

Whatever changes done on snapshot standby will be flushed out once convert back to physical standby database from snapshot standby.

Primary database changes will not applied to snapshot standby database why because there is no MRP process running snapshot database.

No need to enable flashback database.

Only need to have db_recovery_file_dest and db_recovery_file_dest_size on physical standby.


1. Environment

PRIMARY: (DELL)

Platform          : Linuxx86_64
Server Name       : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version        : Oracle 11.2.0.4
File system	  : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

STANDBY: (DELL_DG)

		
Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version        : Oracle 11.2.0.4
File system       : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4


2. Configure Standby Database (DELL_DG)

This is sample document, database names will be different

http://www.br8dba.com/active-dataguard/


3. Verify archive log GAP

On Primary

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL             PRIMARY          READ WRITE

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            57  <----

SQL>

On Standby

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            57 <-----

SQL>


4. Verify Flash Recovery Area and Flashback database

Primary

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <------

SQL>

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/oracle/oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 3G
SQL>

Standby

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>
SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/oracle/oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 3G
SQL>


5. Cancel MRP on Standby

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>


6. Covert to snapshot standby from physical standby

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      DELL_DG          SNAPSHOT STANDBY MOUNTED

SQL>
SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          SNAPSHOT STANDBY READ WRITE

SQL>

Alert log file:

Sat Dec 29 14:52:49 2018
alter database recover managed standby database cancel
Sat Dec 29 14:52:50 2018
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/dell_dg/DELL_DG/trace/DELL_DG_mrp0_3982.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (DELL_DG)
Managed Standby Recovery Canceled (DELL_DG)
Completed: alter database recover managed standby database cancel
Sat Dec 29 15:15:16 2018
alter database convert to snapshot standby
Starting background process RVWR
Sat Dec 29 15:15:16 2018
RVWR started with pid=24, OS id=25202
Allocated 4194304 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/29/2018 15:15:16
Killing 4 processes with pids 4048,4044,4046,4050 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 8813
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Sat Dec 29 15:15:20 2018
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1228396
Resetting resetlogs activation ID 3965561545 (0xec5daac9)
Online log /u02/oracle/oradata/DELL_DG/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u02/oracle/oradata/DELL_DG/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u02/oracle/oradata/DELL_DG/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1228394
Sat Dec 29 15:15:20 2018
Setting recovery target incarnation to 4
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
Sat Dec 29 15:16:01 2018
ARC1: Becoming the 'no SRL' ARCH
Sat Dec 29 15:16:22 2018
idle dispatcher 'D000' terminated, pid = (17, 1)
Sat Dec 29 15:16:30 2018
alter database open
Data Guard Broker initializing...
Data Guard Broker initialization complete
Sat Dec 29 15:16:30 2018
Assigning activation ID 3966853654 (0xec716216)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u02/oracle/oradata/DELL_DG/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Dec 29 15:16:30 2018
SMON: enabling cache recovery
Sat Dec 29 15:16:30 2018
NSA2 started with pid=25, OS id=25215
[8813] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:26075724 end:26075874 diff:150 (1 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
Database Characterset is WE8MSWIN1252
Sat Dec 29 15:16:30 2018
SMCO started with pid=26, OS id=25217
No Resource Manager plan active
Starting background process QMNC
Sat Dec 29 15:16:31 2018
QMNC started with pid=27, OS id=25219
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open


7. Testing

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          SNAPSHOT STANDBY READ WRITE

SQL> create user raj identified by raj default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to raj;

Grant succeeded.

SQL> conn raj/raj;
Connected.
SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
         3

SQL>

=================================== OR ===================================

How to do perform the same using Dataguard Broker

Using DG Broker – Convert Physical standby to Snapshot standby

DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Physical standby database <----

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


DGMGRL> CONVERT DATABASE "DELL_DG" TO SNAPSHOT STANDBY;
Converting database "DELL_DG" to a Snapshot Standby database, please wait...
Database "DELL_DG" converted successfully
DGMGRL>

DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Snapshot standby database <----

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +65-94693551

Configure Cascading Standby Database

Configure Cascading Standby Database on 11G

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. Environment
2. Configure Standby Database (DELL_DG)
3. Configure init parameter on Primary (DELL)
4. Configure init parameter on STANDBY (DELL_DG)
5. Configure init parameter on Cascaded STANDBY (DELL_CDG)
6. Create required directories for cascaded standby
7. Add oratab entry for cascaded standby
8. Startup nomount Cascaded STANDBY database
9. Configure LISTENER Configuration
10. Configure TNS Configuration
11. Backup from Primary (DELL)
12. Transfer Backup from primary (DELL) to CASCADED standby (DELL_CDG)
13. Transfer Password file from primary (DELL) to CASCADED standby (DELL_CDG)

On CASCADED STANDBY

14. Restore the Standby Controlfile
15. Mount the cascaded standby database
16. Catalog all the backup pieces
17. Restore and Recover Database
18. Verify Standby redo logs
19. Enable MRP

20. Verify ..Lets Test

___________________________________________________________________________________________________


0. Overview

What is cascaded standby ?

Cascaded standby database receives redo data from another standby database instead of directly from primary database.

PRIMARY ---> STANDBY ----> CASCADED STANDBY 

In a Data Guard configuration using a cascaded destination, a physical standby database can forward the redo data it receives from the primary database to another standby database. 
Why cascaded standby ?

To reduce the load on your primary system, or to reduce the bandwidth requirements imposed when your standbys are separated from the primary database through a Wide Area Network (WAN), you can implement cascaded destinations, whereby a standby database receives its redo data from another standby database, instead of directly from the primary database. 


Limitations:

Cascading logical standby databases from a logical standby database is not supported.
Cascading standby databases (logical or physical) from a primary database that is part of an Oracle Real Application Cluster (RAC) is not supported (This restriction has been lifted in 11.2.0.2)
Using Cascaded standby databases in a Data Guard Broker environment is not supported.


1. Environment

PRIMARY: (DELL)

Platform          : Linuxx86_64
Server Name       : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version        : Oracle 11.2.0.4
File system	  : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

STANDBY: (DELL_DG)

		
Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version        : Oracle 11.2.0.4
File system       : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

CASCADED STANDBY: (DELL_CDG)

Platform          : Linuxx86_64
Server Name       : RAC3.RAJASEKHAR.COM, IP: 192.168.2.103
DB Version        : Oracle 11.2.0.4
File system	  : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL_CDG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4


2. Configure Standby Database (DELL_DG)

This is sample document, database names will be different

http://www.br8dba.com/active-dataguard/


3. Configure init parameter on Primary (DELL)

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
DELL      READ WRITE           DELL                           PRIMARY

SQL>
SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(DELL,DELL_DG)
SQL>
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3='DEFER' SCOPE=BOTH;

System altered.

SQL>

[oracle@rac1 ~]$ cat initDELL.ora

DELL.__db_cache_size=503316480
DELL.__java_pool_size=16777216
DELL.__large_pool_size=33554432
DELL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DELL.__pga_aggregate_target=520093696
DELL.__sga_target=754974720
DELL.__shared_io_pool_size=0
DELL.__shared_pool_size=184549376
DELL.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DELL/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oracle/oradata/DELL/control01.ctl','/u02/oracle/oradata/fast_recovery_area/DELL/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u02/oracle/oradata/DELL_DG','/u02/oracle/oradata/DELL'
*.db_name='DELL'
*.db_recovery_file_dest='/u02/oracle/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=3221225472
*.db_unique_name='DELL'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DELLXDB)'
*.fal_client='DELL'
*.fal_server='DELL_DG'
*.local_listener='LISTENER_DELL'
*.log_archive_config='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/DELL VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL'
*.log_archive_dest_2='SERVICE=DELL_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL_DG'
*.log_archive_dest_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u02/oracle/oradata/DELL_DG','/u02/oracle/oradata/DELL'
*.memory_target=1259339776
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac1 ~]$


4. Configure init parameter on STANDBY (DELL_DG)

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
DELL      MOUNTED              DELL_DG                        PHYSICAL STANDBY

SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(DELL,DELL_DG)
SQL>
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
SQL>
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3='ENABLE' SCOPE=BOTH;

System altered.

SQL>

[oracle@rac2 ~]$ cat initDELL_DG.ora

DELL_DG.__db_cache_size=503316480
DELL_DG.__java_pool_size=16777216
DELL_DG.__large_pool_size=33554432
DELL_DG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DELL_DG.__pga_aggregate_target=520093696
DELL_DG.__sga_target=754974720
DELL_DG.__shared_io_pool_size=0
DELL_DG.__shared_pool_size=184549376
DELL_DG.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DELL_DG/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oracle/oradata/DELL_DG/control01.ctl','/u02/oracle/oradata/fast_recovery_area/DELL_DG/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_DG'
*.db_name='DELL'
*.db_recovery_file_dest='/u02/oracle/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=3221225472
*.db_unique_name='DELL_DG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DELL_DGXDB)'
*.fal_client='DELL_DG'
*.fal_server='DELL'
*.log_archive_config='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/DELL_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL_DG'
*.log_archive_dest_2='SERVICE=DELL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL'
*.log_archive_dest_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_DG'
*.memory_target=1259339776
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac2 ~]$


5. Configure init parameter on Cascaded STANDBY (DELL_CDG)

[oracle@rac3 ~]$ cat /home/oracle/initDELLCDG.ora
DELL_CDG.__db_cache_size=469762048
DELL_CDG.__java_pool_size=16777216
DELL_CDG.__large_pool_size=33554432
DELL_CDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DELL_CDG.__pga_aggregate_target=520093696
DELL_CDG.__sga_target=754974720
DELL_CDG.__shared_io_pool_size=0
DELL_CDG.__shared_pool_size=201326592
DELL_CDG.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/DELL_CDG/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oracle/oradata/DELL_CDG/control01.ctl','/u02/oracle/oradata/fast_recovery_area/DELL_CDG/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DELL'
*.db_recovery_file_dest='/u02/oracle/oradata/fast_recovery_area'
*.db_recovery_file_dest_size=3221225472
*.db_unique_name='DELL_CDG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DELL_CDGXDB)'
*.fal_client='DELL_CDG'
*.fal_server='DELL_DG'
*.log_archive_config='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/DELL_CDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL_CDG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.db_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_CDG'
*.log_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_CDG'
*.memory_target=1259339776
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac3 ~]$


6. Create required directories (On Node 3 – DELL_CDG)

[oracle@rac3 ~]$ mkdir -p /u01/app/oracle/admin/DELL_CDG/adump
[oracle@rac3 ~]$ mkdir -p /u02/oracle/oradata/DELL_CDG/
[oracle@rac3 ~]$ mkdir -p /u02/oracle/oradata/fast_recovery_area/DELL_CDG
[oracle@rac3 ~]$ mkdir -p /u02/oracle/archive/DELL_CDG


7. Modify /etc/oratab (On Node 3 – DELL_CDG)

[oracle@rac3 ~]$ cat /etc/oratab | grep -i DELL_CDG
DELL_CDG:/u01/app/oracle/product/11.2.0.4:N
[oracle@rac3 ~]$


8. Startup nomount (On Node 3 – DELL_CDG)

[oracle@rac3 ~]$ . oraenv
ORACLE_SID = [DELL_CDG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 23 13:20:39 2018

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initDELLCDG.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL> create spfile from pfile='/home/oracle/initDELLCDG.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL>


9. Configure LISTENER Configuration

Node 1: PRIMARY (DELL)

[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_11G =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DELL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)
      (SID_NAME = DELL)
    )
  )

LISTENER_11G =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_11G = /u01/app/oracle
[oracle@rac1 admin]$

[oracle@rac1 admin]$ ps -ef | grep tns
root        15     2  0 11:46 ?        00:00:00 [netns]
oracle    7558     1  0 13:11 ?        00:00:00 /u01/app/oracle/product/11.2.0.4/bin/tnslsnr LISTENER_11G -inherit
oracle   11686  7404  0 14:28 pts/0    00:00:00 grep tns
[oracle@rac1 admin]$


[oracle@rac1 admin]$ lsnrctl status LISTENER_11G

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2018 14:26:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-DEC-2018 13:11:38
Uptime                    0 days 1 hr. 15 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_11g/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "DELL" has 2 instance(s).
  Instance "DELL", status UNKNOWN, has 1 handler(s) for this service...
  Instance "DELL", status READY, has 1 handler(s) for this service...
Service "DELLXDB" has 1 instance(s).
  Instance "DELL", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$

Node 2: STANDBY (DELL_DG)

[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0.4/network/admin/
[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_11G =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DELL_DG)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)
      (SID_NAME = DELL_DG)
    )
  )

LISTENER_11G =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_11G = /u01/app/oracle
[oracle@rac2 admin]$
[oracle@rac2 admin]$ ps -ef | grep tns
root        15     2  0 11:46 ?        00:00:00 [netns]
oracle    6971     1  0 13:13 ?        00:00:00 /u01/app/oracle/product/11.2.0.4/bin/tnslsnr LISTENER_11G -inherit
oracle   10712  6909  0 14:29 pts/0    00:00:00 grep tns
[oracle@rac2 admin]$
[oracle@rac2 admin]$ lsnrctl status LISTENER_11G

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2018 14:29:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-DEC-2018 13:13:37
Uptime                    0 days 1 hr. 15 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener_11g/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "DELL_DG" has 1 instance(s).
  Instance "DELL_DG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$

Node 3: CASCADED STANDBY (DELL_CDG)

[oracle@rac3 ~]$ cd /u01/app/oracle/product/11.2.0.4/network/admin/
[oracle@rac3 admin]$ cp listener.ora listener.ora_bkp_new

[oracle@rac3 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_11G =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DELL_CDG)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)
      (SID_NAME = DELL_CDG)
    )
  )

LISTENER_11G =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac3.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_11G = /u01/app/oracle
[oracle@rac3 admin]$


[oracle@rac3 admin]$ lsnrctl start LISTENER_11G

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2018 14:20:47

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

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac3/listener_11g/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3.rajasekhar.com)(PORT=1621)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac3.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-DEC-2018 14:20:49
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac3/listener_11g/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "DELL_CDG" has 1 instance(s).
  Instance "DELL_CDG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac3 admin]$


10. Configure TNS Configuration

Node 1: Primary (DELL)

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_11G =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))

DELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DELL)
    )
  )

DELL_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DELL_DG)
    )
  )

DELL_CDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DELL_CDG)
    )
  )

[oracle@rac1 admin]$

Node 2: STANDBY (DELL_DG)

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_11G =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))

DELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DELL)
    )
  )

DELL_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DELL_DG)
    )
  )

DELL_CDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DELL_CDG)
    )
  )

[oracle@rac2 admin]$

Node 3: CASCADED STANDBY (DELL_CDG)

[oracle@rac3 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_11G =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621))

DELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DELL)
    )
  )

DELL_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DELL_DG)
    )
  )

DELL_CDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DELL_CDG)
    )
  )

[oracle@rac3 admin]$


11. Backup from Primary (DELL)

[oracle@rac1 ~]$ cat rmanbackup.sh
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
BACKUP_LOG_PATH=/u02/oracle/backup
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=DELL
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/backup_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF
connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup database format '/u02/oracle/backup/database_%d_%u_%s';
release channel t1;
release channel t2;
release channel t3;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
allocate channel a3 type disk;
backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s';
release channel a1;
release channel a2;
release channel a3;
}
run {
allocate channel c1 type disk;
backup current controlfile for standby format '/u02/oracle/backup/Control_%d_%u_%s';
release channel c1;
}
exit;
EOF

[oracle@rac1 DELL]$ chmod 775 rmanbackup.sh
[oracle@rac1 DELL]$ ls -ltr
total 4
-rwxrwxr-x. 1 oracle dba 951 Dec 23 13:58 rmanbackup.sh
[oracle@rac1 DELL]$

[oracle@rac1 DELL]$ nohup ./rmanbackup.sh &
[1] 10351
[oracle@rac1 DELL]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@rac1 DELL]$ jobs -l
[1]+ 10351 Running nohup ./rmanbackup.sh &
[oracle@rac1 DELL]$

[oracle@rac1 DELL]$ cat backup_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 23 14:14:59 2018

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

RMAN>
connected to target database: DELL (DBID=3965088591)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=50 device type=DISK

allocated channel: t2
channel t2: SID=42 device type=DISK

allocated channel: t3
channel t3: SID=31 device type=DISK

Starting backup at 23-DEC-18
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/DELL/system01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/DELL/users01.dbf
channel t1: starting piece 1 at 23-DEC-18
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u02/oracle/oradata/DELL/sysaux01.dbf
input datafile file number=00003 name=/u02/oracle/oradata/DELL/undotbs01.dbf
channel t2: starting piece 1 at 23-DEC-18
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current control file in backup set
channel t3: starting piece 1 at 23-DEC-18
channel t3: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/database_DELL_0dtlgf74_13 tag=TAG20181223T141500 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel t3: starting piece 1 at 23-DEC-18
channel t3: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/database_DELL_0etlgf76_14 tag=TAG20181223T141500 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:03
channel t1: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/database_DELL_0btlgf74_11 tag=TAG20181223T141500 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:27
channel t2: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/database_DELL_0ctlgf74_12 tag=TAG20181223T141500 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:27
Finished backup at 23-DEC-18

released channel: t1

released channel: t2

released channel: t3

RMAN>
sql statement: alter system archive log current

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: SID=50 device type=DISK

allocated channel: a2
channel a2: SID=42 device type=DISK

allocated channel: a3
channel a3: SID=31 device type=DISK

Starting backup at 23-DEC-18
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=19 STAMP=994364876
input archived log thread=1 sequence=15 RECID=20 STAMP=994365204
input archived log thread=1 sequence=16 RECID=22 STAMP=994365247
input archived log thread=1 sequence=17 RECID=24 STAMP=994365249
input archived log thread=1 sequence=18 RECID=26 STAMP=994365252
input archived log thread=1 sequence=19 RECID=28 STAMP=994365253
input archived log thread=1 sequence=20 RECID=30 STAMP=994365253
input archived log thread=1 sequence=21 RECID=32 STAMP=994366193
input archived log thread=1 sequence=22 RECID=34 STAMP=994366194
input archived log thread=1 sequence=23 RECID=37 STAMP=994366197
input archived log thread=1 sequence=24 RECID=39 STAMP=994366557
input archived log thread=1 sequence=25 RECID=41 STAMP=994367307
input archived log thread=1 sequence=26 RECID=43 STAMP=994367413
input archived log thread=1 sequence=27 RECID=44 STAMP=994367418
input archived log thread=1 sequence=28 RECID=45 STAMP=994367503
input archived log thread=1 sequence=29 RECID=46 STAMP=994367652
input archived log thread=1 sequence=30 RECID=47 STAMP=994367750
input archived log thread=1 sequence=31 RECID=49 STAMP=994367754
input archived log thread=1 sequence=32 RECID=51 STAMP=994368247
input archived log thread=1 sequence=33 RECID=53 STAMP=994396624
input archived log thread=1 sequence=34 RECID=54 STAMP=994396628
input archived log thread=1 sequence=35 RECID=55 STAMP=994433377
input archived log thread=1 sequence=36 RECID=56 STAMP=994433381
channel a1: starting piece 1 at 23-DEC-18
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=37 RECID=57 STAMP=994433417
input archived log thread=1 sequence=38 RECID=58 STAMP=994752599
input archived log thread=1 sequence=39 RECID=59 STAMP=994752603
input archived log thread=1 sequence=40 RECID=62 STAMP=994753201
input archived log thread=1 sequence=41 RECID=69 STAMP=994754147
input archived log thread=1 sequence=42 RECID=71 STAMP=994758897
input archived log thread=1 sequence=43 RECID=73 STAMP=994761301
channel a2: starting piece 1 at 23-DEC-18
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=74 STAMP=994761304
input archived log thread=1 sequence=2 RECID=76 STAMP=994761308
input archived log thread=1 sequence=3 RECID=79 STAMP=994761665
input archived log thread=1 sequence=4 RECID=81 STAMP=994761687
input archived log thread=1 sequence=5 RECID=83 STAMP=994761689
input archived log thread=1 sequence=6 RECID=85 STAMP=994761692
input archived log thread=1 sequence=7 RECID=88 STAMP=994766042
input archived log thread=1 sequence=8 RECID=89 STAMP=994766335
input archived log thread=1 sequence=9 RECID=91 STAMP=994766336
input archived log thread=1 sequence=10 RECID=93 STAMP=994766340
input archived log thread=1 sequence=11 RECID=95 STAMP=994766736
input archived log thread=1 sequence=12 RECID=97 STAMP=994783917
input archived log thread=1 sequence=13 RECID=99 STAMP=994784010
input archived log thread=1 sequence=14 RECID=100 STAMP=994784015
input archived log thread=1 sequence=15 RECID=101 STAMP=994784051
input archived log thread=1 sequence=16 RECID=103 STAMP=994826449
input archived log thread=1 sequence=17 RECID=102 STAMP=994826448
input archived log thread=1 sequence=18 RECID=104 STAMP=994826449
input archived log thread=1 sequence=19 RECID=105 STAMP=994826923
input archived log thread=1 sequence=20 RECID=106 STAMP=994827013
input archived log thread=1 sequence=21 RECID=108 STAMP=994827017
input archived log thread=1 sequence=22 RECID=110 STAMP=994834119
input archived log thread=1 sequence=23 RECID=112 STAMP=995352031
channel a3: starting piece 1 at 23-DEC-18
channel a1: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/arch_DELL_0ftlgf81_15 tag=TAG20181223T141528 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:04
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=24 RECID=113 STAMP=995352034
input archived log thread=1 sequence=25 RECID=116 STAMP=995352079
input archived log thread=1 sequence=26 RECID=118 STAMP=995634672
input archived log thread=1 sequence=27 RECID=119 STAMP=995634676
input archived log thread=1 sequence=28 RECID=120 STAMP=995634913
input archived log thread=1 sequence=29 RECID=124 STAMP=995635432
input archived log thread=1 sequence=30 RECID=126 STAMP=995635435
input archived log thread=1 sequence=31 RECID=129 STAMP=995638037
input archived log thread=1 sequence=32 RECID=130 STAMP=995638037
input archived log thread=1 sequence=33 RECID=132 STAMP=995638172
input archived log thread=1 sequence=34 RECID=134 STAMP=995638173
input archived log thread=1 sequence=35 RECID=136 STAMP=995638177
input archived log thread=1 sequence=36 RECID=138 STAMP=995638528
input archived log thread=1 sequence=37 RECID=140 STAMP=995638528
channel a1: starting piece 1 at 23-DEC-18
channel a2: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/arch_DELL_0gtlgf81_16 tag=TAG20181223T141528 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:04
channel a3: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/arch_DELL_0htlgf82_17 tag=TAG20181223T141528 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:03
channel a1: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/arch_DELL_0itlgf85_18 tag=TAG20181223T141528 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-DEC-18

released channel: a1

released channel: a2

released channel: a3

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=50 device type=DISK

Starting backup at 23-DEC-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 23-DEC-18
channel c1: finished piece 1 at 23-DEC-18
piece handle=/u02/oracle/backup/Control_DELL_0jtlgf89_19 tag=TAG20181223T141537 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-DEC-18

released channel: c1

RMAN>

Recovery Manager complete.
[oracle@rac1 DELL]$


12. Transfer Backup from primary to CASCADED standby

[oracle@rac1 backup]$ scp database_DELL_0* oracle@rac3:/u02/oracle/backup
oracle@rac3's password:
database_DELL_0btlgf74_11                                   100%  645MB  58.6MB/s   00:11
database_DELL_0ctlgf74_12                                   100%  414MB  46.0MB/s   00:09
database_DELL_0dtlgf74_13                                   100% 9856KB   9.6MB/s   00:00
database_DELL_0etlgf76_14                                   100%   96KB  96.0KB/s   00:00
[oracle@rac1 backup]$ scp arch_DELL_0* oracle@rac3:/u02/oracle/backup
oracle@rac3's password:
arch_DELL_0ftlgf81_15                                       100%   21MB  20.7MB/s   00:00
arch_DELL_0gtlgf81_16                                       100%   66MB  65.6MB/s   00:01
arch_DELL_0htlgf82_17                                       100%   66MB  66.2MB/s   00:01
arch_DELL_0itlgf85_18                                       100%   52MB  52.4MB/s   00:01
[oracle@rac1 backup]$
[oracle@rac1 backup]$ scp Control_DELL_0jtlgf89_19 oracle@rac3:/u02/oracle/backup
oracle@rac3's password:
Control_DELL_0jtlgf89_19                                    100% 9856KB   9.6MB/s   00:00
[oracle@rac1 backup]$


13. Transfer Password file from primary to CASCADED standby

[oracle@rac1 dbs]$ scp orapwDELL oracle@rac3:/u01/app/oracle/product/11.2.0.4/dbs/orapwDELL_CDG
oracle@rac3's password:
orapwDELL                       100% 1536     1.5KB/s   00:00
[oracle@rac1 dbs]$

On CASCADED STANDBY


14. Restore the Standby Controlfile

[oracle@rac3 backup]$ ls -ltr
total 1313304
-rw-r-----. 1 oracle dba 675995648 Dec 23 15:11 database_DELL_0btlgf74_11
-rw-r-----. 1 oracle dba 433709056 Dec 23 15:11 database_DELL_0ctlgf74_12
-rw-r-----. 1 oracle dba  10092544 Dec 23 15:11 database_DELL_0dtlgf74_13
-rw-r-----. 1 oracle dba     98304 Dec 23 15:11 database_DELL_0etlgf76_14
-rw-r-----. 1 oracle dba  21750784 Dec 23 15:13 arch_DELL_0ftlgf81_15
-rw-r-----. 1 oracle dba  68794880 Dec 23 15:13 arch_DELL_0gtlgf81_16
-rw-r-----. 1 oracle dba  69376000 Dec 23 15:13 arch_DELL_0htlgf82_17
-rw-r-----. 1 oracle dba  54898688 Dec 23 15:13 arch_DELL_0itlgf85_18
-rw-r-----. 1 oracle dba  10092544 Dec 23 15:13 Control_DELL_0jtlgf89_19
[oracle@rac3 backup]$ . oraenv
ORACLE_SID = [DELL_CDG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac3 backup]$
[oracle@rac3 backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 23 15:51:13 2018

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

connected to target database: DELL (not mounted)

RMAN> restore standby controlfile from '/u02/oracle/backup/Control_DELL_0jtlgf89_19';

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

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/oracle/oradata/DELL_CDG/control01.ctl
output file name=/u02/oracle/oradata/fast_recovery_area/DELL_CDG/control02.ctl
Finished restore at 23-DEC-18

RMAN>


15. Mount the cascaded standby database

RMAN> sql 'alter database mount standby database';

sql statement: alter database mount standby database
released channel: ORA_DISK_1

RMAN>


16. Catalog all the backup pieces

RMAN> catalog start with '/u02/oracle/backup';

Starting implicit crosscheck backup at 23-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 23-DEC-18

Starting implicit crosscheck copy at 23-DEC-18
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 23-DEC-18

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /u02/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /u02/oracle/backup/Control_DELL_0jtlgf89_19

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oracle/backup/Control_DELL_0jtlgf89_19

RMAN>


17. Restore and Recover Database

RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10      20.74M     DISK        00:00:02     23-DEC-18
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20181223T141528
        Piece Name: /u02/oracle/backup/arch_DELL_0ftlgf81_15

  List of Archived Logs in backup set 10
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    14      954005     08-DEC-18 954381     08-DEC-18
  1    15      954381     08-DEC-18 955102     08-DEC-18
  1    16      955102     08-DEC-18 955160     08-DEC-18
  1    17      955160     08-DEC-18 955164     08-DEC-18
  1    18      955164     08-DEC-18 955170     08-DEC-18
  1    19      955170     08-DEC-18 955174     08-DEC-18
  1    20      955174     08-DEC-18 955178     08-DEC-18
  1    21      955178     08-DEC-18 956913     08-DEC-18
  1    22      956913     08-DEC-18 956917     08-DEC-18
  1    23      956917     08-DEC-18 956922     08-DEC-18
  1    24      956922     08-DEC-18 957349     08-DEC-18
  1    25      957349     08-DEC-18 978443     08-DEC-18
  1    26      978443     08-DEC-18 978446     08-DEC-18
  1    27      978446     08-DEC-18 978739     08-DEC-18
  1    28      978739     08-DEC-18 978869     08-DEC-18
  1    29      978869     08-DEC-18 999033     08-DEC-18
  1    30      999033     08-DEC-18 999036     08-DEC-18
  1    31      999036     08-DEC-18 999299     08-DEC-18
  1    32      999299     08-DEC-18 999926     08-DEC-18
  1    33      999926     08-DEC-18 1001120    09-DEC-18
  1    34      1001120    09-DEC-18 1001401    09-DEC-18
  1    35      1001401    09-DEC-18 1003961    09-DEC-18
  1    36      1003961    09-DEC-18 1004255    09-DEC-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11      65.61M     DISK        00:00:03     23-DEC-18
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20181223T141528
        Piece Name: /u02/oracle/backup/arch_DELL_0gtlgf81_16

  List of Archived Logs in backup set 11
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    37      1004255    09-DEC-18 1010611    09-DEC-18
  1    38      1010611    09-DEC-18 1011007    13-DEC-18
  1    39      1011007    13-DEC-18 1011331    13-DEC-18
  1    40      1011331    13-DEC-18 1012228    13-DEC-18
  1    41      1012228    13-DEC-18 1013929    13-DEC-18
  1    42      1013929    13-DEC-18 1020556    13-DEC-18
  1    43      1020556    13-DEC-18 1023975    13-DEC-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12      66.16M     DISK        00:00:03     23-DEC-18
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20181223T141528
        Piece Name: /u02/oracle/backup/arch_DELL_0htlgf82_17

  List of Archived Logs in backup set 12
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       1023975    13-DEC-18 1023978    13-DEC-18
  1    2       1023978    13-DEC-18 1024272    13-DEC-18
  1    3       1024272    13-DEC-18 1024698    13-DEC-18
  1    4       1024698    13-DEC-18 1024733    13-DEC-18
  1    5       1024733    13-DEC-18 1024738    13-DEC-18
  1    6       1024738    13-DEC-18 1024744    13-DEC-18
  1    7       1024744    13-DEC-18 1030847    13-DEC-18
  1    8       1030847    13-DEC-18 1031174    13-DEC-18
  1    9       1031174    13-DEC-18 1031178    13-DEC-18
  1    10      1031178    13-DEC-18 1031185    13-DEC-18
  1    11      1031185    13-DEC-18 1031853    13-DEC-18
  1    12      1031853    13-DEC-18 1078584    13-DEC-18
  1    13      1078584    13-DEC-18 1078587    13-DEC-18
  1    14      1078587    13-DEC-18 1078869    13-DEC-18
  1    15      1078869    13-DEC-18 1078925    13-DEC-18
  1    16      1078925    13-DEC-18 1079499    14-DEC-18
  1    17      1079499    14-DEC-18 1079798    14-DEC-18
  1    18      1079798    14-DEC-18 1079977    14-DEC-18
  1    19      1079977    14-DEC-18 1100515    14-DEC-18
  1    20      1100515    14-DEC-18 1100518    14-DEC-18
  1    21      1100518    14-DEC-18 1100817    14-DEC-18
  1    22      1100817    14-DEC-18 1110716    14-DEC-18
  1    23      1110716    14-DEC-18 1132055    20-DEC-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
13      52.35M     DISK        00:00:02     23-DEC-18
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20181223T141528
        Piece Name: /u02/oracle/backup/arch_DELL_0itlgf85_18

  List of Archived Logs in backup set 13
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    24      1132055    20-DEC-18 1132357    20-DEC-18
  1    25      1132357    20-DEC-18 1132668    20-DEC-18
  1    26      1132668    20-DEC-18 1148516    23-DEC-18
  1    27      1148516    23-DEC-18 1148845    23-DEC-18
  1    28      1148845    23-DEC-18 1152235    23-DEC-18
  1    29      1152235    23-DEC-18 1152930    23-DEC-18
  1    30      1152930    23-DEC-18 1152936    23-DEC-18
  1    31      1152936    23-DEC-18 1157442    23-DEC-18
  1    32      1157442    23-DEC-18 1157469    23-DEC-18
  1    33      1157469    23-DEC-18 1157673    23-DEC-18
  1    34      1157673    23-DEC-18 1157677    23-DEC-18
  1    35      1157677    23-DEC-18 1157684    23-DEC-18
  1    36      1157684    23-DEC-18 1158527    23-DEC-18
  1    37      1158527    23-DEC-18 1158553    23-DEC-18 <------

RMAN>
[oracle@rac3 backup]$ cat restore_db.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
BACKUP_LOG_PATH=/u02/oracle/backup
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=DELL_CDG
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/restore_db.log
/u01/app/oracle/product/11.2.0.4/bin/rman msglog=${LOG_FILE} <<EOF
connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
set until sequence 38; <--- 37 + 1
restore database;
recover database;
}
exit
EOF

[oracle@rac3 backup]$ chmod 775 restore_db.sh

[oracle@rac3 backup]$ nohup ./restore_db.sh &
[1] 15411
[oracle@rac3 backup]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@rac3 backup]$ jobs -l
[1]+ 15411 Running nohup ./restore_db.sh &
[oracle@rac3 backup]$

[oracle@rac3 backup]$ cat restore_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 23 16:07:42 2018

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

RMAN>
connected to target database: DELL (DBID=3965088591, not open)

RMAN> 2> 3> 4> 5> 6> 7> 8>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=18 device type=DISK

allocated channel: t2
channel t2: SID=30 device type=DISK

allocated channel: t3
channel t3: SID=31 device type=DISK

executing command: SET until clause

Starting restore at 23-DEC-18

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00002 to /u02/oracle/oradata/DELL_CDG/sysaux01.dbf
channel t1: restoring datafile 00003 to /u02/oracle/oradata/DELL_CDG/undotbs01.dbf
channel t1: reading from backup piece /u02/oracle/backup/database_DELL_0ctlgf74_12
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00001 to /u02/oracle/oradata/DELL_CDG/system01.dbf
channel t2: restoring datafile 00004 to /u02/oracle/oradata/DELL_CDG/users01.dbf
channel t2: reading from backup piece /u02/oracle/backup/database_DELL_0btlgf74_11
channel t1: piece handle=/u02/oracle/backup/database_DELL_0ctlgf74_12 tag=TAG20181223T141500
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:15
channel t2: piece handle=/u02/oracle/backup/database_DELL_0btlgf74_11 tag=TAG20181223T141500
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:15
Finished restore at 23-DEC-18

Starting recover at 23-DEC-18

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=36
channel t1: restoring archived log
archived log thread=1 sequence=37
channel t1: reading from backup piece /u02/oracle/backup/arch_DELL_0itlgf85_18
channel t1: piece handle=/u02/oracle/backup/arch_DELL_0itlgf85_18 tag=TAG20181223T141528
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oracle/archive/DELL_CDG/1_36_994761301.dbf thread=1 sequence=36
archived log file name=/u02/oracle/archive/DELL_CDG/1_37_994761301.dbf thread=1 sequence=37
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/oracle/oradata/DELL_CDG/system01.dbf'

media recovery complete, elapsed time: 00:00:00
Finished recover at 23-DEC-18
released channel: t1
released channel: t2
released channel: t3

RMAN>

Recovery Manager complete.
[oracle@rac3 backup]$


18. Verify Standby redo logs

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
DELL      READ ONLY            DELL_CDG                       PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>

SQL> set lines 180
SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u02/oracle/oradata/DELL_CDG/redo03.log            NO
         2         ONLINE  /u02/oracle/oradata/DELL_CDG/redo02.log            NO
         1         ONLINE  /u02/oracle/oradata/DELL_CDG/redo01.log            NO
         4         STANDBY /u02/oracle/oradata/DELL_CDG/redo04.log            NO
         5         STANDBY /u02/oracle/oradata/DELL_CDG/redo05.log            NO
         6         STANDBY /u02/oracle/oradata/DELL_CDG/redo06.log            NO
         7         STANDBY /u02/oracle/oradata/DELL_CDG/redo07.log            NO

7 rows selected.

SQL>
SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 /u02/oracle/oradata/DELL_CDG/redo04.log              52428800
         5 /u02/oracle/oradata/DELL_CDG/redo05.log              52428800
         6 /u02/oracle/oradata/DELL_CDG/redo06.log              52428800
         7 /u02/oracle/oradata/DELL_CDG/redo07.log              52428800

SQL>


19. Enable MRP on CASCADED STANDBY

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
DELL      READ ONLY            DELL_CDG                       PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active  <-- This is expected


SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
DELL      READ ONLY WITH APPLY DELL_CDG                       PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>


20. Verify ..Lets Test

On Primary : DELL

SQL> set lines 180
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
DELL      READ WRITE           DELL                           PRIMARY          MAXIMUM PERFORMANCE

SQL> CREATE TABLE TEST (A NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
         1  <-----

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>

On STANDBY : DELL_DG

SQL> SET LINES 180
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
DELL      MOUNTED              DELL_DG                        PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
DELL      READ ONLY WITH APPLY DELL_DG                        PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
         1 <-----

SQL>

On CASCADED STANDBY: DELL_CDG

SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
DELL      READ ONLY WITH APPLY DELL_CDG                       PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)
----------
         1 <----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

Cascaded Standby Databases in Oracle 10g/11g (Doc ID 409013.1)

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com

Upgrade Database to 12.2 with Physical Standby

Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place

Table of Contents
___________________________________________________________________________________________________

0. Environment

PRE-UPGRADE TASKS

1. Disable DG Broker
2. Apply latest PSU on 11.2.0.4 on both primary and standby (click below for example)
3. Install 12.2 database software on standby
4. Install 12.2 database software on primary
5. Apply latest Release Update on 12.2.0.1 on standby
6. Apply latest Release Update on 12.2.0.1 on primary
7. Run preupgrade script
8. View Preupgrade log
9. Backup Database
10. Adjust TABLESPACE SIZES as needed
11. Run preupgrade_fixups.sql
12. Remove the EM repository
13. Remove OLAP Catalog
14. Update Processes init parameter
15. Rerun Pre-upgrade script
16. Verify archive dest size
17. Verify GAP
18. Cancel MRP (On Standby)
19. DEFER log_archive_dest_2
20. Purge DBA_RECYCLEBIN
21. Verify INVALID objects

UPGRADE TASK

22. Run DBUA from 12.2 ORACLE HOME

POST-UPGRADE TASKS WHEN DBUA USING

23. Verify /etc/oratab
24. Verify Timezone version
25. Verify INVALID objects
26. Verify DBA_REGISTRY
27. Run postupgrade_fixups.sql
28. Configure TNS entries
29. Verify TNS connectivity
30. Enable LOG_ARCHIVE_DEST_2
31. Enable MRP on Standby
32. Verify GAP
33. Verify DBA_REGISTRY
34. Update COMPATIBLE parameter
35. Configure DG Broker
___________________________________________________________________________________________________


0. Environment

On Primary

Hostname       		: RAC1.RAJASEKHAR.COM
Database Name  		: TEST
DB VERSION		: 11.2.0.4.0
DB Home Path 		: /u01/app/oracle/product/11.2.0.4
Datafile Location 	: /u02/oradata/TEST

Target DB VERSION 	: 12.2.0.1
Target DB Path		: /u01/app/oracle/product/12.2.0.1

Upgrade Method		: Database Upgrade Assistant

STANDBY

Hostname       		: RAC2.RAJASEKHAR.COM
Database Name  		: TEST_DG
DB VERSION		: 11.2.0.4.0
DB Home Path 		: /u01/app/oracle/product/11.2.0.4
Datafile Location 	: /u02/oradata/TEST_DG

Target DB VERSION 	: 12.2.0.1
Target DB Path		: /u01/app/oracle/product/12.2.0.1

Upgrade Method		: Database Upgrade Assistant


1. Disable DG Broker


2. Apply latest PSU on 11.2.0.4 on both primary and standby (click below for example)


3. Install 12.2 database software on standby


4. Install 12.2 database software on primary


5. Apply latest Release Update on 12.2.0.1 on standby


6. Apply latest Release Update on 12.2.0.1 on primary


7. Run preupgrade script

[oracle@rac1 preupgrade]$ . oraenv
ORACLE_SID = [ARC] ? TEST
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ /u01/app/oracle/product/11.2.0.4/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/PRE_UPGRADE
Preupgrade generated files:
    /home/oracle/PRE_UPGRADE/preupgrade.log
    /home/oracle/PRE_UPGRADE/preupgrade_fixups.sql
    /home/oracle/PRE_UPGRADE/postupgrade_fixups.sql
[oracle@rac1 ~]$ 


8. View Preupgrade log

[oracle@rac1 ~]$ cat /home/oracle/PRE_UPGRADE/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  TEST
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
         Compatible:  11.2.0.4.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Enterprise Manager Repository   [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Expression Filter                      [to be upgraded]  VALID
  Rule Manager                           [to be upgraded]  VALID
  Oracle Application Express             [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID


==============
BEFORE UPGRADE
==============

  Run /preupgrade_fixups.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             520 MB  ENABLED      1425 MB  None
     SYSTEM                             750 MB  ENABLED      1256 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                            50 MB  ENABLED       400 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.




   + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

     Parameter                         12.2.0.1.0 minimum
     ---------                         ------------------
     memory_target*                            1535115264
     processes                                        300

     * These minimum memory/pool sizes are recommended for the upgrade process




  RECOMMENDED ACTIONS
  ===================
   + Remove the EM repository.

     - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
     12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

     Step 1: If database control is configured, stop EM Database Control,
     using the following command

       $> emctl stop dbconsole

     Step 2: Connect to the database using the SYS account AS SYSDBA

       SET ECHO ON;
       SET SERVEROUTPUT ON;
      @emremove.sql

     Without the set echo and serveroutput commands, you will not be able to
     follow the progress of the script.

     The database has an Enterprise Manager Database Control repository.

     Starting with Oracle Database 12c, the local Enterprise Manager Database
     Control does not exist anymore. The repository will be removed from your
     database during the upgrade.  This step can be manually performed before
     the upgrade to reduce downtime.

   + Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
     $ORACLE_HOME/olap/admin/catnoamd.sql script.

     The OLAP Catalog component, AMD, exists in the database.

     Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
     desupported and will be automatically marked as OPTION OFF during the database upgrade if present. Oracle recommends removing OLAP Catalog
     (OLAP AMD) before database upgrade.

   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 11.2.0.4
     Oracle Database Performance Tuning Guide.

   + Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
     trigger or drop and re-create the trigger with a user that was granted
     directly with such. You can list those triggers using "SELECT OWNER,
     TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND
     OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
     PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')"

     There is one or more database triggers whose owner does not have the
     right privilege on the database.

     The creation of database triggers must be done by users granted with
     ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
     directly.

  INFORMATION ONLY
  ================
   + Consider upgrading APEX manually, before the database upgrade.

     The database contains APEX version 3.2.1.00.12 and will need to be
     upgraded to at least version 5.0.4.00.12.

     To reduce database upgrade time, you can upgrade APEX manually before
     the database upgrade.  Refer to My Oracle Support Note 1088970.1 for
     information on APEX installation upgrades.

   + Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
     5315 MB of archived logs.  Check alert log during the upgrade that there
     is no write error to the destination due to lack of disk space.  Execute
     'archive log list' and query v$archive_dest for more
     LOG_ARCHIVE_DEST_ destinations to check.

     Archiving cannot proceed if the archive log destination is full during
     upgrade.

     Archive Log Destination:
      Parameter    :  LOG_ARCHIVE_DEST_1
      Destination  :  /u02/oracle/archive

     The database has archiving enabled and LOG_ARCHIVE_DEST_ set.  The
     upgrade process will need free disk space in the archive log
     destination(s) to generate archived logs to.

=============
AFTER UPGRADE
=============

  Run /postupgrade_fixups.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 14 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.

   + Gather statistics on fixed objects two weeks after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

     This recommendation is given for all preupgrade runs.

     Fixed object statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans.  Those
     statistics are specific to the Oracle Database release that generates
     them, and can be stale upon database upgrade.

  INFORMATION ONLY
  ================
   + Check the Oracle documentation for the identified components for their
     specific upgrade procedure.

     The database upgrade script will not upgrade the following Oracle
     components:  OLAP Catalog,OWB

     The Oracle database upgrade script upgrades most, but not all Oracle
     Database components that may be installed.  Some components that are not
     upgraded may have their own upgrade scripts, or they may be deprecated
     or obsolete.

[oracle@rac1 ~]$


9. Backup Database

Make sure you should have valid Backup. Better to take COLD backup on both primary and standby if you complete downtime. Hence no need to rebuild entire standby in case of rollback.


10. Adjust TABLESPACE SIZES as needed

SQL> select file_name from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/sysaux01.dbf

SQL> alter database datafile '/u02/oracle/oradata/TEST/sysaux01.dbf' autoextend on maxsize 30g;

Database altered.

SQL>

SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/system01.dbf

SQL> alter database datafile '/u02/oracle/oradata/TEST/system01.dbf' autoextend on maxsize 30g;

Database altered.

SQL>

SQL> select file_name from dba_data_files where tablespace_name='UNDOTBS1';

FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/undotbs01.dbf

SQL> alter database datafile '/u02/oracle/oradata/TEST/undotbs01.dbf' autoextend on maxsize 30g;

Database altered.

SQL>

SQL> select file_name from dba_temp_files where tablespace_name='TEMP';

FILE_NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/TEST/temp01.dbf

SQL> alter database tempfile '/u02/oracle/oradata/TEST/temp01.dbf' autoextend on maxsize 30g;

Database altered.

SQL>


11. Run preupgrade_fixups.sql

[oracle@rac1 PRE_UPGRADE]$ pwd
/home/oracle/PRE_UPGRADE
[oracle@rac1 PRE_UPGRADE]$

SQL> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-11-13 09:10:06

For Source Database:     TEST
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
em_present                Failed  Manual fixup recommended.
amd_exists                Failed  Manual fixup recommended.
dictionary_stats          Passed  None
trgowner_no_admndbtrg     Failed  Manual fixup recommended.
apex_upgrade_msg          Failed  Manual fixup recommended.
min_archive_dest_size     Failed  Manual fixup recommended.

PL/SQL procedure successfully completed.

SQL>


12. Remove the EM repository

[oracle@rac1 admin]$ cp /u01/app/oracle/product/12.2.0.1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0.4/rdbms/admin
[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0.4/rdbms/admin
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 28 09:42:20 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> @emremove.sql
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

SQL>


13. Remove OLAP Catalog

[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0.4/olap/admin
[oracle@rac1 admin]$ ls -ltr catnoamd.sql
-rw-r--r--. 1 oracle dba 11916 Apr 13  2013 catnoamd.sql
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 28 09:48:15 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> @catnoamd.sql

Synonym dropped.
..
..
..
Type dropped.


PL/SQL procedure successfully completed.


Role dropped.


PL/SQL procedure successfully completed.


1 row deleted.

SQL> commit;  <--- I gave commit Commit complete. SQL>


14. Update Processes init parameter

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150
SQL>

SQL> alter system set processes=300 scope=both;
alter system set processes=300 scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set processes=300 scope=spfile;

System altered.

SQL>


15. Rerun Pre-upgrade script

[oracle@rac1 PRE_UPGRADE]$ pwd
/home/oracle/PRE_UPGRADE
[oracle@rac1 PRE_UPGRADE]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 28 16:48:32 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-11-13 09:10:06

For Source Database:     TEST
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
em_present                Passed  None
amd_exists                Passed  None
dictionary_stats          Passed  None
trgowner_no_admndbtrg     Passed  None
apex_upgrade_msg          Failed  Manual fixup recommended.
min_archive_dest_size     Failed  Manual fixup recommended.

PL/SQL procedure successfully completed.

SQL>


16. Verify archive dest size

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive
Oldest online log sequence     107
Next log sequence to archive   109
Current log sequence           109
SQL>

SQL> !df -h /u02/oracle/archive
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
                       43G  6.1G   35G  15% /u02

SQL>


17. Verify GAP

Run on Primary

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    108                   108          0
         1                    108                   108          0

SQL>

Run on Standby

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    108                   108          0

SQL>

18. Cancel MRP (Run on Standby )

[oracle@rac2 ~]$ ps -ef | grep mrp
oracle    3909     1  0 16:05 ?        00:00:00 ora_mrp0_TEST_DG
oracle    6692  3750  0 17:04 pts/0    00:00:00 grep mrp
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>


19. DEFER log_archive_dest_2 (On Primary)

SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; 
DEST_ID    DEST_NAME             STATUS    ERROR 
---------- -------------------- --------- ---------- 
1          LOG_ARCHIVE_DEST_1    VALID 
2          LOG_ARCHIVE_DEST_2    VALID 
SQL>
SQL> ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=BOTH;

System altered.

SQL>
SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; 
DEST_ID     DEST_NAME           STATUS     ERROR 
---------- -------------------- --------- ---------- 
1           LOG_ARCHIVE_DEST_1   VALID 
2           LOG_ARCHIVE_DEST_2   DEFERRED 

SQL>

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service="test_dg", LGWR ASYNC
                                                 NOAFFIRM delay=0 optional comp
                                                 ression=disable max_failure=0
                                                 max_connections=1 reopen=300 d
                                                 b_unique_name="TEST_DG" net_ti
                                                 meout=30, valid_for=(all_logfi
                                                 les,primary_role)
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL>
SQL> alter system set log_archive_dest_2='' scope=both;   <---- removed entry to avoid errors while running dbua, why because log_archive_dest_2 having many attributes System altered. 
SQL>


20. Purge DBA_RECYCLEBIN

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL>


21. Verify INVALID objects

SQL> select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
         0

SQL>

UPGRADE TASK


22. Run DBUA from 12.2 ORACLE HOME On Primary











POST-UPGRADE TASKS WHEN DBUA USING


23. Verify /etc/oratab

[oracle@rac1 admin]$ cat /etc/oratab | grep -i TEST
TEST:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac1 admin]$


24. Verify Timezone version

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        26 <-----

SQL>


25. Verify INVALID objects

SQL> select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
         0 <----

SQL>


26. Verify DBA_REGISTRY

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL> SQL>

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- -----------
CATALOG    Oracle Database Catalog Views            12.2.0.1.0      VALID
CATPROC    Oracle Database Packages and Types       12.2.0.1.0      VALID
JAVAVM     JServer JAVA Virtual Machine             12.2.0.1.0      VALID
XML        Oracle XDK                               12.2.0.1.0      VALID
CATJAVA    Oracle Database Java Packages            12.2.0.1.0      VALID
APS        OLAP Analytic Workspace                  12.2.0.1.0      VALID
OWM        Oracle Workspace Manager                 12.2.0.1.0      VALID
CONTEXT    Oracle Text                              12.2.0.1.0      VALID
XDB        Oracle XML Database                      12.2.0.1.0      VALID
ORDIM      Oracle Multimedia                        12.2.0.1.0      VALID
SDO        Spatial                                  12.2.0.1.0      VALID
XOQ        Oracle OLAP API                          12.2.0.1.0      VALID
APEX       Oracle Application Express               5.0.4.00.12     VALID

13 rows selected.

SQL>


27. Run postupgrade_fixups.sql

SQL> @postupgrade_fixups.sql

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 12.2.0.1.0 Build: 1
Generated on:            2018-11-13 09:10:06

For Source Database:     TEST
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  12.2.0.1.0

                          Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
old_time_zones_exist      Passed  None
post_dictionary           Passed  None
fixed_objects             Passed  None
upg_by_std_upgrd          Passed  None

PL/SQL procedure successfully completed.


Session altered.

SQL>


28. Configure TNS entries

On Primary

[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0.1/network/admin/
[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TEST_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_12C = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

TEST_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST_DG)
    )
  )

TEST_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DGMGRL)
    )
  )

TEST_DG_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DG_DGMGRL)
    )
  )

[oracle@rac1 admin]$

On Standby

[oracle@rac2 admin]$ pwd
/u01/app/oracle/product/12.2.0.1/network/admin
[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST_DG)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST_DG)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TEST_DG_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = TEST_DG)
    )
  )

LISTENER_12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac2 admin]$

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_12C = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

TEST_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DG)
    )
  )

TEST_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DGMGRL)
    )
  )

TEST_DG_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_DG_DGMGRL)
    )
  )

[oracle@rac2 admin]$


29. Verify TNS connectivity

On Primary

[oracle@rac1 ~]$ sqlplus sys/SYS@TEST AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:46:13 2018

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


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

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@rac1 ~]$ sqlplus sys/SYS@TEST_DG AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:46:47 2018

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


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

SQL>

On Standby

[oracle@rac2 ~]$ sqlplus sys/SYS@TEST AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:47:17 2018

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


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

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus sys/SYS@TEST_DG AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 07:47:26 2018

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


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

SQL>


30. Enable LOG_ARCHIVE_DEST_2

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TEST_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_DG' scope=both;

System altered.

SQL>

SQL> ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH;

System altered.

SQL>

SQL> SET LINES 180
SQL> COL DEST_NAME FOR A30
SQL> COL ERROR FOR A10
SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3; DEST_ID DEST_NAME STATUS ERROR ---------- ------------------------------ --------- ---------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 VALID SQL>


31. Enable MRP on Standby

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TEST      TEST_DG                        READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
DGRD      ALLOCATED             0          0
ARCH      CLOSING             156          1
ARCH      CLOSING             157          1
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
DGRD      ALLOCATED             0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                158          1
MRP0      APPLYING_LOG        158          1

35 rows selected.

SQL>


32. Verify GAP

On Primary

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    157                   157          0
         1                    157                   157          0

SQL>

On Standby

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    157                   157          0

SQL>


33. Verify DBA_REGISTRY

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL> SQL>

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- -----------
CATALOG    Oracle Database Catalog Views            12.2.0.1.0      VALID
CATPROC    Oracle Database Packages and Types       12.2.0.1.0      VALID
JAVAVM     JServer JAVA Virtual Machine             12.2.0.1.0      VALID
XML        Oracle XDK                               12.2.0.1.0      VALID
CATJAVA    Oracle Database Java Packages            12.2.0.1.0      VALID
APS        OLAP Analytic Workspace                  12.2.0.1.0      VALID
OWM        Oracle Workspace Manager                 12.2.0.1.0      VALID
CONTEXT    Oracle Text                              12.2.0.1.0      VALID
XDB        Oracle XML Database                      12.2.0.1.0      VALID
ORDIM      Oracle Multimedia                        12.2.0.1.0      VALID
SDO        Spatial                                  12.2.0.1.0      VALID
XOQ        Oracle OLAP API                          12.2.0.1.0      VALID
APEX       Oracle Application Express               5.0.4.00.12     VALID

13 rows selected.

SQL>


34. Update COMPATIBLE parameter

On Primary

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0 <----
noncdb_compatible                    boolean     FALSE
SQL>


SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE;

System altered.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup;
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1056965312 bytes
Database Buffers          587202560 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL>

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0  <---- noncdb_compatible boolean FALSE SQL>

SQL> select name,open_mode,version from v$database,v$instance;

NAME                 OPEN_MODE            VERSION
-------------------- -------------------- -----------------
TEST                 READ WRITE           12.2.0.1.0 <----

SQL>

On STANDBY

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0 <---
noncdb_compatible                    boolean     FALSE
SQL>

SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE;

System altered.

SQL>
SQL> shut immediate;
SQL> 
SQL> startup;

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0 <-----
noncdb_compatible                    boolean     FALSE
SQL>

SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TEST      TEST_DG                        READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>


35. Configure DG Broker (Primary)

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Dec 3 05:09:08 2018

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/SYS@TEST
Connected to "TEST"
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration 'TEST' as primary database is 'TEST' connect identifier is TEST;
Configuration "TEST" created with primary database "TEST"
DGMGRL> show configuration;

Configuration - TEST

  Protection Mode: MaxPerformance
  Members:
  TEST - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database 'TEST_DG' as connect identifier is TEST_DG maintained as physical;
Database "TEST_DG" added
DGMGRL> show configuration;

Configuration - TEST

  Protection Mode: MaxPerformance
  Members:
  TEST    - Primary database
    TEST_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL>

DGMGRL> show configuration;

Configuration - TEST

  Protection Mode: MaxPerformance
  Members:
  TEST    - Primary database
    TEST_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 45 seconds ago)

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

https://mikedietrichde.com/2017/09/12/upgrade-disable-data-guard-broker/
Patches to apply before upgrading Oracle GI and DB to 12.2.0.1 (Doc ID 2180188.1)
Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (Doc ID 2173141.1)
Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA (Doc ID 2189854.1)
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/upgrading-downgrading-in-data-guard-broker-configuration.html#GUID-9B424C49-A005-4106-9EF8-CB7479F9ACB8
http://docs.oracle.com/database/122/SBYDB/upgrading-patching-downgrading-oracle-data-guard-configuration.htm#SBYDB-GUID-A5226768-DB6B-4714-BB9A-0A3EF17A01C8
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/upgrading-downgrading-in-data-guard-broker-configuration.html#GUID-9B424C49-A005-4106-9EF8-CB7479F9ACB8
http://docs.oracle.com/database/122/SBYDB/upgrading-patching-downgrading-oracle-data-guard-configuration.htm#GUID-6431EFBF-6B00-4E7D-A106-63574225BDD9

Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)
Master Note for Database Proactive Patch Program (Doc ID 756671.1)

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com

Disable DG Broker

Disable Data Guard Broker Before Patch or Upgrade

Contents
___________________________________________________________________________________________________________

0. Overview

On Primary

1. Verify Configuration
2. Disable fast-start failover
3. Disable Configuration
4. Re-Verify Configuration
5. Stop DG Broker on Primary
6. Backup DB Broker Config Files

On Standby

7. Stop DG Broker on Standby
8. Backup DB Broker Configuration Files

___________________________________________________________________________________________________________

0. Overview

++ Please disable the Data Guard Broker before you do patch or upgrade. Enable the Broker after the upgrade again.
++ DGMGRL running on Oracle Database 12c Release 2 (12.2) cannot be used to manage Oracle Data Guard running on Oracle Database 10g or Oracle Database 11g.
++ Handle Data Guard Broker carefully.

On Primary:


1. Verify Configuration

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [TEST] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@TEST
Password:
Connected.
DGMGRL> SHOW CONFIGURATION;

Configuration - TEST

  Protection Mode: MaxPerformance
  Databases:
    TEST    - Primary database
    TEST_DG - Physical standby database

Fast-Start Failover: DISABLED <------

Configuration Status:
SUCCESS <----

DGMGRL>


2. Disable fast-start failover 

++ In our case it is already been disabled.

DGMGRL> DISABLE FAST_START FAILOVER;
Disabled.
DGMGRL>


3. Disable Configuration

It will disable the broker's active management of the databases in the Oracle Data Guard configuration

DGMGRL> DISABLE CONFIGURATION;
Disabled.
DGMGRL>


4. Re-Verify Configuration

DGMGRL> SHOW CONFIGURATION;

Configuration - TEST

  Protection Mode: MaxPerformance
  Databases:
    TEST    - Primary database
    TEST_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED <----

DGMGRL>


5. Stop DG Broker on Primary

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE
SQL>
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <-----
SQL>


6. Backup DB Broker Configuration Files

SQL> SHOW PARAMETER DG_BROKER

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0
                                                 .4/dbs/dr1TEST.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0
                                                 .4/dbs/dr2TEST.dat
dg_broker_start                      boolean     FALSE
SQL>

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0.4/dbs/
[oracle@rac1 dbs]$ cp dr1TEST.dat dr1TEST.dat.bkp
[oracle@rac1 dbs]$ cp dr2TEST.dat dr2TEST.dat.bkp
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr dr*
-rw-r-----. 1 oracle dba 20480 Nov 11 15:42 dr1TEST.dat
-rw-r-----. 1 oracle dba 20480 Nov 11 15:42 dr2TEST.dat
-rw-r-----. 1 oracle dba 20480 Nov 11 15:48 dr1TEST.dat.bkp
-rw-r-----. 1 oracle dba 20480 Nov 11 15:48 dr2TEST.dat.bkp
[oracle@rac1 dbs]$

On Standby:


7. Stop DG Broker on Standby

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE
SQL>
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <----
SQL>


8. Backup DB Broker Configuration Files

SQL> SHOW PARAMETER DG_BROKER

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0
                                                 .4/dbs/dr1TEST_DG.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0
                                                 .4/dbs/dr2TEST_DG.dat
dg_broker_start                      boolean     FALSE
SQL>

[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0.4/dbs/
[oracle@rac2 dbs]$ cp dr1TEST_DG.dat dr1TEST_DG.dat.bkp
[oracle@rac2 dbs]$ cp dr2TEST_DG.dat dr2TEST_DG.dat.bkp
[oracle@rac2 dbs]$
[oracle@rac2 dbs]$ ls -ltr dr*
-rw-r-----. 1 oracle dba 8192 Nov 11 15:42 dr1TEST_DG.dat
-rw-r-----. 1 oracle dba 8192 Nov 11 15:42 dr2TEST_DG.dat
-rw-r-----. 1 oracle dba 8192 Nov 11 15:50 dr1TEST_DG.dat.bkp
-rw-r-----. 1 oracle dba 8192 Nov 11 15:50 dr2TEST_DG.dat.bkp
[oracle@rac2 dbs]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com

Failover with No Broker (No Flashback)

Failover with No DG Broker (No Flashback) / Failover using Manual Method.

Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary.
to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.

0. Enviroment
1. Simulate failover
2. Initiate Failover


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

On Primary

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005pr          OPEN         PRIMARY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> 

On Standby

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005prg         OPEN         PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> 


1. Simulate failover: Bring down the primary database w5005pr

DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


2. Steps to perform Failover

On Standby (w5005prg)

SQL> alter database recover managed standby database cancel;
 
Database altered.

SQL> alter database recover managed standby database finish force;

Database altered.

SQL>

Note: If Any error from above step then only follow below command.

alter database activate physical standby database;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL>

On Old Primary (W5005PR)

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
SQL>

From New Primary Database

Rebuild Standby database for new primary

[oracle@rac2 ~]$ rman target sys/sys@w5005prg auxiliary sys/sys@w5005pr

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 12 00:22:53 2016

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

connected to target database: W5005PR (DBID=4158252169)
connected to auxiliary database: W5005PR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 12-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005pr' ;
}
executing Memory Script

Starting backup at 12-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 device type=DISK
Finished backup at 12-JAN-16

contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/w5005pr/controlfile/current.268.900456457';
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_w5005prg.f tag=TAG20160112T002322 RECID=5 STAMP=900894203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/w5005prg/datafile/system.264.900546429
output file name=+DATA/w5005pr/datafile/system.274.900894219 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/w5005prg/datafile/sysaux.265.900546465
output file name=+DATA/w5005pr/datafile/sysaux.275.900894265 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/w5005prg/datafile/undotbs1.266.900546499
output file name=+DATA/w5005pr/datafile/undotbs1.276.900894299 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/w5005prg/datafile/users.267.900546507
output file name=+DATA/w5005pr/datafile/users.277.900894303 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=900894305 file name=+DATA/w5005pr/datafile/system.274.900894219
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=900894305 file name=+DATA/w5005pr/datafile/sysaux.275.900894265
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=900894305 file name=+DATA/w5005pr/datafile/undotbs1.276.900894299
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=900894305 file name=+DATA/w5005pr/datafile/users.277.900894303
Finished Duplicate Db at 12-JAN-16

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Failover with No DG Broker using Flashback

Failover with No DG Broker using Flashback Database

Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary.
to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.

0. Environment
1. Pre-requisites
2. Simulate failover
3. Steps to perform Failover with Physical Standby database


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

On Primary

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005pr          OPEN         PRIMARY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>

On Standby

SQL> select instance_name,status,database_role from v$database,v$instance;

INSTANCE_NAME    STATUS       DATABASE_ROLE
---------------- ------------ ----------------
w5005prg         OPEN         PHYSICAL STANDBY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>


1. Pre-quesites

- Configre Physical Standby database. Please click below for step by step configuration.

Please click here

- Make sure that flashback database is enabled on both primary and standby database.


2. Simulate failover: Bring down the primary database w5005pr

DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


3. Steps to perform Failover with Physical Standby database

On Standby (w5005prg)

SQL> alter database recover managed standby database cancel;
 
Database altered.

SQL> alter database recover managed standby database finish force;

Database altered.

SQL>

Note: If Any error from above step then only follow below command.

alter database activate physical standby database;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL>

Find SCN on the new primary database (w5005prg) 

SQL> select standby_became_primary_scn from v$database;

STANDBY_BECAME_PRIMARY_SCN
--------------------------
                   1618441

SQL>


On OLD Primary (W5005PR)

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> 

SQL> select name,INSTANCE_NAME from v$database,v$instance;

NAME       INSTANCE_NAME
--------- ----------------
W5005PR     w5005pr

SQL> flashback database to scn 1618441;  <---

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

SQL>

SQL>  select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING               4
ARCH      CONNECTED             0
ARCH      CLOSING               5
ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING               6
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
MRP0      APPLYING_LOG          7 <--
RFS       IDLE                  7
RFS       IDLE                  0

37 rows selected.

SQL> 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Failover with DG Broker using Flashback

Reinstate a failed over Data Guard using Flashback Database

Overview:

In a Data Guard environment, the operations of any Oracle DBA wants to avoid is a Data Guard infrastructure failover.
Failover is an irreversible transition of a standby database to the primary role. This is only done in the event of a catastrophic failure of the primary database
However, in a MAA environments, unplanned failures can occur and they forces to do a database failover
However, if this failure is repaired in a few hours and the lost data center , is operational again.

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary.
to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.

0. Enviroment
1. Pre-quesites
2. Primary Database Crashed
3. Steps to perform Failover with Physical Standby database
4. Reinstate database old primary (w5005pr)


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Pre-quesites: We need to set up Flashback Database

On Primary

Mount the database, configure flashback retention, start flashback database and open the database.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g scope=both;
SQL> alter system set db_recovery_file_dest='+DATA' scope=both;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240; # Set up for 4 hour retention 
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

On STANDBY

Stop redo apply, configure flashback retention, start flashback database, open the database and start redo apply (Is active DG).

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g scope=both;
SQL> alter system set db_recovery_file_dest='+DATA' scope=both;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


2. Primary Database Crashed

DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


[oracle@rac1 trace]$ ps -ef | grep pmon | grep -v grep
oracle    3589     1  0 19:53 ?        00:00:00 asm_pmon_+ASM
[oracle@rac1 trace]$

USER (ospid: 6254): terminating the instance
Instance terminated by USER, pid = 6254 <-- For some reason primary instance crashed


3. Steps to perform Failover with Physical Standby database

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "w5005pr"
DGM-17017: unable to determine configuration status

DGMGRL>
DGMGRL> failover to w5005prg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "w5005prg" <----
DGMGRL>

Database alert log (w5005prg)
/*
Thu Jan 21 21:33:25 2016
Data Guard Broker: Beginning failover <---
Thu Jan 21 21:33:26 2016
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jan 21 21:33:26 2016
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/w5005prg/w5005prg/trace/w5005prg_mrp0_4750.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (w5005prg)
Managed Standby Recovery Canceled (w5005prg)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
Attempt to do a Terminal Recovery (w5005prg)
Media Recovery Start: Managed Standby Recovery (w5005prg)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '01/21/2016 21:33:27'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 25 redo required
Terminal Recovery:
Recovery of Online Redo Log: Thread 1 Group 5 Seq 25 Reading mem 0
  Mem# 0: +DATA/w5005prg/onlinelog/group_5.273.900719769
Identified End-Of-Redo (failover) for thread 1 sequence 25 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 1582299 time 01/21/2016 21:28:42
Media Recovery Complete (w5005prg)
Terminal Recovery: successful completion
Forcing ARSCN to IRSCN for TR 0:1582299
Attempt to set limbo arscn 0:1582299 irscn 0:1582299
Resetting standby activation ID 4158708098 (0xf7e0d982)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (w5005prg)
Maximum wait for role transition is 15 minutes.
Thu Jan 21 21:33:27 2016
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance w5005prg - Archival Error
ORA-16014: log 5 sequence# 25 not archived, no available destinations
ORA-00312: online log 5 thread 1: '+DATA/w5005prg/onlinelog/group_5.273.900719769'
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Thu Jan 21 21:33:27 2016
SMON: disabling cache recovery
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/w5005prg/w5005prg/trace/w5005prg_rsm0_4660.trc
Standby terminal recovery start SCN: 1582276
RESETLOGS after incomplete recovery UNTIL CHANGE 1582299
Online logfile pre-clearing operation disabled by switchover
Online log +DATA/w5005prg/onlinelog/group_1.268.900546507: Thread 1 Group 1 was previously cleared
Online log +DATA/w5005prg/onlinelog/group_2.269.900546509: Thread 1 Group 2 was previously cleared
Online log +DATA/w5005prg/onlinelog/group_3.270.900546509: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1582275
Thu Jan 21 21:33:27 2016
Setting recovery target incarnation to 4
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE OPEN
Data Guard Broker initializing...
Thu Jan 21 21:33:27 2016
Assigning activation ID 4159548412 (0xf7edabfc)
Thu Jan 21 21:33:27 2016
ARCc: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 2 (thread open)
Thu Jan 21 21:33:27 2016
ARCd: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: +DATA/w5005prg/onlinelog/group_2.269.900546509
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 21 21:33:27 2016
ARCe: Becoming the 'no SRL' ARCH
SMON: enabling cache recovery
Thu Jan 21 21:33:27 2016
.....
.....
No Resource Manager plan active
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: +DATA/w5005prg/onlinelog/group_3.270.900546509
Thu Jan 21 21:33:28 2016
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 225 added for thread 1 sequence 2 ID 0xf7edabfc dest 1:
Starting background process QMNC
Thu Jan 21 21:33:29 2016
QMNC started with pid=63, OS id=5812
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='w5005prg';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='w5005prg';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=30 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
Failover succeeded. Primary database is now w5005prg.
Thu Jan 21 21:33:31 2016
idle dispatcher 'D000' terminated, pid = (20, 1)
Starting background process CJQ0
Thu Jan 21 21:33:31 2016
CJQ0 started with pid=69, OS id=5871
Thu Jan 21 21:33:32 2016
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 2097152 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;
*/

DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005prg - Primary database
    w5005pr  - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


4. Reinstate database (w5005pr)

On New Primary

DGMGRL> reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

/*
Failing it would have needed to configure flashback database to be able to do so automatically…

If you try to start w5005pr database in MOUNT state and issue again a request to reinstate you will now get clear error message:
*/

On old Primary (W5005PR)

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size            1090519280 bytes
Database Buffers          167772160 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL>

On New Primary
DGMGRL> reinstate database w5005pr;  <---
Reinstating database "w5005pr", please wait...
Operation requires shutdown of instance "w5005pr" on database "w5005pr"
Shutting down instance "w5005pr"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "w5005pr" on database "w5005pr"
Starting instance "w5005pr"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "w5005pr" ...
Reinstatement of database "w5005pr" succeeded  <----
DGMGRL>

Database alert log. (w5005pr)
/*
Starting Data Guard Broker (DMON)
Thu Jan 21 21:43:59 2016
INSV started with pid=27, OS id=6547
Thu Jan 21 21:44:02 2016
NSV1 started with pid=28, OS id=6559
Thu Jan 21 21:44:07 2016
NSV1 started with pid=28, OS id=6567
Thu Jan 21 21:44:10 2016
RSM0 started with pid=30, OS id=6571
Thu Jan 21 21:44:10 2016
Using STANDBY_ARCHIVE_DEST parameter default value as +FRA
RFS[1]: Assigned to RFS process 6575
RFS[1]: Database mount ID mismatch [0xf7edabfc:0xf7ede817] (4159548412:4159563799)
RFS[1]: Not using real application clusters
Thu Jan 21 21:44:12 2016
RFS[2]: Assigned to RFS process 6579
RFS[2]: Database mount ID mismatch [0xf7edabfc:0xf7ede817] (4159548412:4159563799)
RFS[2]: Not using real application clusters
FLASHBACK DATABASE TO SCN 1582275
SUCCESS: diskgroup FRA was mounted
Flashback Restore Start
Thu Jan 21 21:44:13 2016
NOTE: dependency between database w5005pr and diskgroup resource ora.FRA.dg is established
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Flashback Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_21.421.901747617
Flashback Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_22.422.901747623
Recovery of Online Redo Log: Thread 1 Group 1 Seq 23 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_1.278.900894305
Recovery of Online Redo Log: Thread 1 Group 2 Seq 24 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_2.279.900894307
Incomplete Recovery applied until change 1582276 time 01/21/2016 21:28:19
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 1582275
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (w5005pr)
Flush standby redo logfile failed:1649
Clearing standby activation ID 4158708098 (0xf7e0d982)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/w5005pr/onlinelog/group_1.278.900894305
Clearing online log 1 of thread 1 sequence number 23
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/w5005pr/onlinelog/group_2.279.900894307
Clearing online log 2 of thread 1 sequence number 24
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/w5005pr/onlinelog/group_3.280.900894309
Clearing online log 3 of thread 1 sequence number 25
Clearing online redo logfile 3 complete
Completed: alter database convert to physical standby
Thu Jan 21 21:44:16 2016
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 3
All dispatchers and shared servers shutdown
alter database CLOSE NORMAL
ORA-1109 signalled during: alter database CLOSE NORMAL...
alter database DISMOUNT
Shutting down archive processes
Archiving is disabled
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 4
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 77
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 2
Thu Jan 21 21:44:17 2016
NOTE: Deferred communication with ASM instance
Completed: alter database DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Shutting down Data Guard Broker processes
Thu Jan 21 21:44:19 2016
Completed: Data Guard Broker shutdown
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Thu Jan 21 21:44:21 2016
Stopping background process VKTM
Thu Jan 21 21:44:21 2016
NOTE: Shutting down MARK background process
Thu Jan 21 21:44:23 2016
Instance shutdown complete
Thu Jan 21 21:44:23 2016
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
WARNING: db_recovery_file_dest is same as db_create_file_dest
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      rac1.rajasekhar.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Using parameter settings in server-side pfile /u01/app/oracle/product/11.2.0/db_1/dbs/initw5005pr.ora
System parameters with non-default values:
  processes                = 150
  spfile                   = "+DATA/w5005pr/spfilew5005pr.ora"
  memory_target            = 1216M
  control_files            = "+DATA/w5005pr/controlfile/current.268.900456457"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  log_archive_dest_1       = "LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=w5005pr"
  log_archive_dest_2       = "service="w5005prg""
  log_archive_dest_2       = "LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="w5005prg" net_timeout=30"
  log_archive_dest_2       = "valid_for=(all_logfiles,primary_role)"
  log_archive_dest_state_1 = "ENABLE"
  log_archive_dest_state_2 = "ENABLE"
  log_archive_min_succeed_dest= 1
  fal_client               = "W5005PR"
  fal_server               = "w5005prg"
  log_archive_trace        = 0
  log_archive_config       = "DG_CONFIG=(w5005pr,w5005prg)"
  log_archive_format       = "%t_%s_%r.dbf"
  log_archive_max_processes= 30
  archive_lag_target       = 0
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+DATA"
  db_recovery_file_dest_size= 2G
  standby_file_management  = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=w5005prXDB)"
  job_queue_processes      = 0
  audit_file_dest          = "/u01/app/oracle/admin/w5005pr/adump"
  audit_trail              = "DB"
  db_name                  = "w5005pr"
  db_unique_name           = "w5005pr"
  open_cursors             = 300
  dg_broker_start          = TRUE
  diagnostic_dest          = "/u01/app/oracle"
Thu Jan 21 21:44:24 2016
PMON started with pid=2, OS id=6641
Thu Jan 21 21:44:24 2016
PSP0 started with pid=3, OS id=6645
Thu Jan 21 21:44:25 2016
VKTM started with pid=4, OS id=6649 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Thu Jan 21 21:44:25 2016
...
...
NOTE: initiating MARK startup
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Starting background process MARK
Thu Jan 21 21:44:25 2016
MMNL started with pid=18, OS id=6711
Thu Jan 21 21:44:25 2016
MARK started with pid=19, OS id=6715
NOTE: MARK has subscribed
starting up 1 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /u01/app/oracle
Thu Jan 21 21:44:25 2016
DMON started with pid=23, OS id=6736
Thu Jan 21 21:44:25 2016
alter database  mount
Thu Jan 21 21:44:25 2016
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521))))' SCOPE=MEMORY SID='w5005pr';
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database w5005pr and diskgroup resource ora.DATA.dg is established
ARCH: STARTING ARCH PROCESSES
Thu Jan 21 21:44:32 2016
ARC0 started with pid=26, OS id=6786
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Jan 21 21:44:33 2016
ARC1 started with pid=27, OS id=6790
Successful mount of redo thread 1, with mount id 4159536441
Allocated 4194304 bytes in shared pool for flashback generation buffer
Starting background process RVWR
..
..
ARC0: STARTING ARCH PROCESSES COMPLETE
Thu Jan 21 21:44:38 2016
Starting Data Guard Broker (DMON)
Thu Jan 21 21:44:38 2016
INSV started with pid=64, OS id=6983
Thu Jan 21 21:44:43 2016
NSV1 started with pid=65, OS id=6991
Thu Jan 21 21:44:46 2016
RSM0 started with pid=66, OS id=7001
Thu Jan 21 21:44:46 2016
Using STANDBY_ARCHIVE_DEST parameter default value as +FRA
Thu Jan 21 21:44:46 2016
SUCCESS: diskgroup FRA was mounted
RFS[1]: Assigned to RFS process 7007
RFS[1]: Opened log for thread 1 sequence 25 dbid -136715127 branch 900893050
Archived Log entry 39 added for thread 1 sequence 25 rlc 900893050 ID 0xf7e0d982 dest 2:
Thu Jan 21 21:44:46 2016
NOTE: dependency between database w5005pr and diskgroup resource ora.FRA.dg is established
Thu Jan 21 21:44:46 2016
RFS[2]: Assigned to RFS process 7011
RFS[2]: Opened log for thread 1 sequence 1 dbid -136715127 branch 901748007
RFS[2]: New Archival REDO Branch(resetlogs_id): 901748007  Prior: 900893050
RFS[2]: Archival Activation ID: 0xf7edabfc Current: 0x0
RFS[2]: Effect of primary database OPEN RESETLOGS
RFS[2]: Incarnation entry added for Branch(resetlogs_id): 901748007 (w5005pr)
Thu Jan 21 21:44:46 2016
RFS[3]: Assigned to RFS process 7013
RFS[3]: Opened log for thread 1 sequence 2 dbid -136715127 branch 901748007
Thu Jan 21 21:44:46 2016
Setting recovery target incarnation to 4
Archived Log entry 40 added for thread 1 sequence 1 rlc 901748007 ID 0xf7edabfc dest 2:
Thu Jan 21 21:44:46 2016
RFS[4]: Assigned to RFS process 7015
RFS[4]: Opened log for thread 1 sequence 3 dbid -136715127 branch 901748007
Archived Log entry 41 added for thread 1 sequence 2 rlc 901748007 ID 0xf7edabfc dest 2:
Archived Log entry 42 added for thread 1 sequence 3 rlc 901748007 ID 0xf7edabfc dest 2:
RFS[2]: Selected log 4 for thread 1 sequence 4 dbid -136715127 branch 901748007
Thu Jan 21 21:44:47 2016
Archived Log entry 43 added for thread 1 sequence 4 ID 0xf7edabfc dest 1:
Thu Jan 21 21:44:47 2016
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process 7023
RFS[5]: Selected log 4 for thread 1 sequence 5 dbid -136715127 branch 901748007
Data Guard: Failover target was Real Time Query standby; attempting to open this standby after reinstatement ...
ALTER DATABASE OPEN READ ONLY
Data Guard Broker initializing...
Data Guard Broker initialization complete
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Thu Jan 21 21:44:49 2016
SMON: enabling cache recovery
Dictionary check beginning
Dictionary check complete
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: ALTER DATABASE OPEN READ ONLY
Thu Jan 21 21:44:50 2016
db_recovery_file_dest_size of 2048 MB is 5.52% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='w5005pr';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='w5005pr';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=30 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET fal_server='w5005prg' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (w5005pr)
Thu Jan 21 21:44:51 2016
MRP0 started with pid=73, OS id=7050
MRP0: Background Managed Standby Recovery process started (w5005pr)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 1582299
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_25.425.901748687
Identified End-Of-Redo (failover) for thread 1 sequence 25 at SCN 0x0.1824db
Resetting standby activation ID 4158708098 (0xf7e0d982)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_1.426.901748687
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_2.427.901748687
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_3.428.901748687
Media Recovery Log +FRA/w5005pr/archivelog/2016_01_21/thread_1_seq_4.429.901748687
Media Recovery Waiting for thread 1 sequence 5 (in transit)
Thu Jan 21 21:44:57 2016
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_4.281.900894309
Thu Jan 21 21:45:00 2016
Archived Log entry 44 added for thread 1 sequence 5 ID 0xf7edabfc dest 1:
Media Recovery Waiting for thread 1 sequence 6
Thu Jan 21 21:45:00 2016
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: Assigned to RFS process 7054
RFS[6]: Selected log 4 for thread 1 sequence 6 dbid -136715127 branch 901748007
Recovery of Online Redo Log: Thread 1 Group 4 Seq 6 Reading mem 0
  Mem# 0: +DATA/w5005pr/onlinelog/group_4.281.900894309
  
*/


DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005prg - Primary database
    w5005pr  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.