DBA Hub

📋Steps in this guide1/24

Configure Cascading Standby Database - Bright DBA

Configure Cascading Standby Database on 11G

oracle clusteringintermediate
by OracleDba
12 views
1

Overview

Configure Cascading Standby Database on 11G On CASCADED STANDBY 0. Overview
2

Section 2

0. Overview What is cascaded standby ? PRIMARY ---> STANDBY ----> CASCADED STANDBY Why cascaded standby ? Limitations:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.
3

Section 3

1. Environment PRIMARY: (DELL) STANDBY: (DELL_DG)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
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
4

Section 4

STANDBY: (DELL_DG) CASCADED STANDBY: (DELL_CDG) 2. Configure Standby Database (DELL_DG)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

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
5

Section 5

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)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
This is sample document, database names will be different
http://www.br8dba.com/active-dataguard/

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 ~]$
6

Section 6

4. Configure init parameter on STANDBY (DELL_DG) 5. Configure init parameter on Cascaded STANDBY (DELL_CDG)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
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 ~]$

[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 ~]$
7

Section 7

6. Create required directories (On Node 3 – DELL_CDG) 7. Modify /etc/oratab (On Node 3 – DELL_CDG)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
[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

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

Section 8

8. Startup nomount (On Node 3 – DELL_CDG) 9. Configure LISTENER Configuration Node 1: PRIMARY (DELL)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[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

Section 9

Node 1: PRIMARY (DELL) Node 2: STANDBY (DELL_DG) Node 3: CASCADED STANDBY (DELL_CDG)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
[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]$

[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]$
10

Section 10

Node 3: CASCADED STANDBY (DELL_CDG) 10. Configure TNS Configuration Node 1: Primary (DELL)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
[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]$
11

Section 11

LISTENER_11G = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621)) Node 2: STANDBY (DELL_DG) LISTENER_11G = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621))

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
[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]$

[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]$
12

Section 12

Node 3: CASCADED STANDBY (DELL_CDG) LISTENER_11G = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621)) 11. Backup from Primary (DELL)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[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]$
13

Section 13

nohup ./rmanbackup.sh & [1]+ 10351 Running nohup ./rmanbackup.sh & cat backup_db.log 12. Transfer Backup from primary to CASCADED standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
[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]$
14

Section 14

scp database_DELL_0* oracle@rac3:/u02/oracle/backup scp arch_DELL_0* oracle@rac3:/u02/oracle/backup scp Control_DELL_0jtlgf89_19 oracle@rac3:/u02/oracle/backup 13. Transfer Password file from primary to CASCADED standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[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]$
15

Section 15

On CASCADED STANDBY 14. Restore the Standby Controlfile

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
[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]$

[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>
16

Section 16

rman target / connected to target database: DELL (not mounted) restore standby controlfile from '/u02/oracle/backup/Control_DELL_0jtlgf89_19'; 15. Mount the cascaded standby database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
RMAN>
sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1

RMAN>
17

Section 17

sql 'alter database mount standby database'; 16. Catalog all the backup pieces catalog start with '/u02/oracle/backup'; 17. Restore and Recover Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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>
18

Section 18

17. Restore and Recover Database 37 <------ <--- 37 + 1 nohup ./restore_db.sh &

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
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]$
19

Section 19

[1]+ 15411 Running nohup ./restore_db.sh & 18. Verify Standby redo logs DELL_CDG 19. Enable MRP on CASCADED STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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>
20

Section 20

19. Enable MRP on CASCADED STANDBY <-- This is expected READ ONLY WITH APPLY 20. Verify ..Lets Test

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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>
21

Section 21

On Primary : DELL READ WRITE On STANDBY : DELL_DG

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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>
22

Section 22

MOUNTED ALTER DATABASE OPEN; ERROR at line 1: ORA-10456: cannot open standby database; media recovery session may be in progress ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; READ ONLY WITH APPLY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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>
23

Section 23

On CASCADED STANDBY: DELL_CDG SELECT COUNT(*) FROM TEST; 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. Caution

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>
24

Section 24

: 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)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!