DBA Hub

📋Steps in this guide1/15

Creating Active Data Guard Physical Standby Environment

Creating Active Data Guard Physical Standby Environment AIM:Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command (No need to take backup of primary database)  Active Data Guard is a new option for Oracle Database 11g Enterprise Edition. Oracle Active Data Guard enables read-only access to a … Continue reading Active Dataguard

oracle clusteringintermediate
by OracleDba
15 views
1

Overview

Creating Active Data Guard Physical Standby Environment AIM: Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command (No need to take backup of primary database) Active Data Guard is a new option for Oracle Database 11g Enterprise Edition. 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.
2

Section 2

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. 0. Enviroment Source: APAC

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
Source:
Platform          :
Linuxx86_64
Server Name       :
RAC1.RAJASEKHAR.COM
, IP: 192.168.2.101
DB Version        :
Oracle 11.2.0.3, File system: Normal
DB Name           :
APAC,
DB_UNIQUE_NAME
:
APAC
Flashback         :
Disabled
Oracle Home Path  :
/u01/app/oracle/product/11.2.0/db_1
Target:
Platform          :
Linuxx86_64
Server Name       :
RAC2.RAJASEKHAR.COM
, IP: 192.168.2.102
DB Version        :
Oracle 11.2.0.3, , File system: Normal
DB Name           :
APAC,
DB_UNIQUE_NAME
:
EMEA
Flashback         :
Disabled
Oracle Home Path  :
/u01/app/oracle/product/11.2.0/db_1
3

Section 3

Target: EMEA 1. Enable Forced Logging on Primary NO

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
SQL>
select name, open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
APAC      READ WRITE

SQL>
select force_logging from v$database;
FOR
---
NO
SQL>
ALTER DATABASE FORCE LOGGING;
Database altered.

SQL>
select force_logging from v$database;
FOR
---
YES <----
SQL>
4

Section 4

YES <---- 2. Create Password File on Primary orapwd file=orapwapac password=sys force=y 3. Configure a Standby Redo Log on Primary

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@rac1 dbs]$
orapwd file=orapwapac password=sys force=y
[oracle@rac1 dbs]$ ls -ltr orapwapac
-rw-r----- 1 oracle oinstall 1536 Jul 25 18:21 orapwapac
[oracle@rac1 dbs]$
5

Section 5

3. Configure a Standby Redo Log on Primary ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/apac/redo07.log') SIZE 50M; <-- <----- 4. Verify Archive Mode Enabled on Primary

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
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 /u01/app/oracle/oradata/apac/redo03.log                        52428800
         1          2 /u01/app/oracle/oradata/apac/redo02.log                        52428800
         1          1 /u01/app/oracle/oradata/apac/redo01.log                        52428800

SQL>

SQL>
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/apac/redo04.log') SIZE 50M;
Database altered.

SQL>
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/apac/redo05.log') SIZE 50M;
Database altered.

SQL>
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/apac/redo06.log') SIZE 50M;
Database altered.

SQL>
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/apac/redo07.log') SIZE 50M;
Database altered.

SQL>

SQL> select * from v$logfile;

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

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 /u01/app/oracle/oradata/apac/redo04.log                        52428800
         5 /u01/app/oracle/oradata/apac/redo05.log                        52428800
         6 /u01/app/oracle/oradata/apac/redo06.log                        52428800
         7 /u01/app/oracle/oradata/apac/redo07.log
<-----
52428800

SQL>
6

Section 6

4. Verify Archive Mode Enabled on Primary Enabled 5. Set Primary Database Initialization Parameters

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
SQL> archive log list
Database log mode              Archive Mode
Automatic archival
Enabled
Archive destination            /u01/app/oracle/arch/apac
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL>

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileapac.ora
SQL>
create pfile='/home/oracle/initapac.ora' from spfile;
File created.

SQL>
alter system set db_unique_name='apac' scope=spfile;
System altered.

SQL>
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(apac,emea)' scope=both;
System altered.

SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/apac VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apac' scope=both;
System altered.

SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=emea LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=emea' 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=apac scope=both;
System altered.

SQL>
ALTER SYSTEM SET fal_server=emea scope=both;
System altered.

SQL>
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac','/u02/emea','/u02/apac' SCOPE=SPFILE;
System altered.

SQL>
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac' SCOPE=SPFILE;
System altered.

SQL>
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.

SQL>

SQL>
create pfile='/home/oracle/initapac.ora' from spfile;
File created.

SQL>

[oracle@rac1 ~]$ cat initapac.ora 
apac.__db_cache_size=339738624
apac.__java_pool_size=4194304
apac.__large_pool_size=4194304
apac.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
apac.__pga_aggregate_target=335544320
apac.__sga_target=503316480
apac.__shared_io_pool_size=0
apac.__shared_pool_size=146800640
apac.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/apac/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/apac/control01.ctl','/u01/app/oracle/oradata/apac/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac','/u02/emea','/u02/apac'
*.db_name='apac'
*.db_unique_name='apac'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=apacXDB)'
*.fal_client='APAC'
*.fal_server='EMEA'
*.log_archive_config='DG_CONFIG=(apac,emea)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/apac VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apac'
*.log_archive_dest_2='SERVICE=emea LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=emea'
*.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='/u01/app/oracle/oradata/emea','/u01/app/oracle/oradata/apac'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac1 ~]$
7

Section 7

6. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE Copy the password file from the primary $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. 7. Set Standby Database Initialization Parameters

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
[oracle@rac1 ~]$
scp initapac.ora oracle@rac2:/home/oracle/initemea.ora
initapac.ora
100% 1487     1.5KB/s   00:00
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwemea
[oracle@rac1 dbs]$
scp orapwapac oracle@rac2:/u01/app/oracle/product/11.2.0/db_1/dbs/
orapwapac                                                    100% 1536     1.5KB/s   00:00
[oracle@rac1 dbs]$
8

Section 8

Edit shown highlighted to set it up for the standby role 8. Crete required directories on Standby 9. Add below entry in ORATAB on 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
[oracle@rac2 ~]$ cat initemea.ora
emea.
__db_cache_size=339738624
emea.
__java_pool_size=4194304
emea.
__large_pool_size=4194304
emea.
__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
emea.
__pga_aggregate_target=335544320
emea.
__sga_target=503316480
emea.
__shared_io_pool_size=0
emea.
__shared_pool_size=146800640
emea.
__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/emea/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/emea/control01.ctl','/u01/app/oracle/oradata/emea/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/apac','/u01/app/oracle/oradata/emea','/u02/apac','/u02/emea'
*.db_name='apac'
*.db_unique_name='emea'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=emeaXDB)'
*.fal_client='emea'
*.fal_server='apac'
*.log_archive_config='DG_CONFIG=(apac,emea)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/emea VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=emea'
*.log_archive_dest_2='SERVICE=apac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=apac'
*.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='/u01/app/oracle/oradata/apac','/u01/app/oracle/oradata/emea'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac2 ~]$

[oracle@rac2 ~]$
mkdir -p /u01/app/oracle/admin/emea/adump
[oracle@rac2 ~]$
mkdir -p /u01/app/oracle/oradata/emea
[oracle@rac2 ~]$
mkdir -p /u01/app/oracle/arch/emea
[oracle@rac2 ~]$
mkdir -p /u02/emea
9

Section 9

9. Add below entry in ORATAB on Standby 10. Create spfile from pfile on Standby Once the new parameter file is ready we create from it the spfile:

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
emea:/u01/app/oracle/product/11.2.0/db_1:N

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

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:34:58 2015

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

Connected to an idle instance.

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

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
SQL>
create spfile from pfile='/home/oracle/initemea.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  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
/u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileemea.ora
SQL>
10

Section 10

11. Configure TNS Entries on Primary 12. Configure TNS Entries on 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
[oracle@rac1 admin]$
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@rac1 admin]$ 

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

APAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apac)
    )
  )

EMEA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = emea)(UR = A)
    )
  )
[oracle@rac1 admin]$ 

[oracle@rac1 ~]$
lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:22:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                25-JUL-2015 19:20:00
Uptime                    0 days 0 hr. 2 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "apac" has 1 instance(s).
  Instance "apac", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$ 

[oracle@rac1 ~]$
tnsping apac
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:38:14

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac)))
OK (0 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$
tnsping emea
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:38:17

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A)))
OK (0 msec)
[oracle@rac1 ~]$

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@rac2 admin]$ 

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

APAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apac)
    )
  )

EMEA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = emea)(UR = A)
    )
  )
[oracle@rac2 admin]$ 

[oracle@rac2 ~]$
lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:24:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.102)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                25-JUL-2015 19:22:24
Uptime                    0 days 0 hr. 2 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.102)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "emea" has 1 instance(s).
  Instance "emea", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$ 


[oracle@rac2 ~]$
tnsping apac
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:37:17

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apac)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$
tnsping emea
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2015 19:37:25

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = emea)(UR = A)))
OK (10 msec)
[oracle@rac2 ~]$
11

Section 11

13. Verify connection ‘AS SYSDBA’ from Primary 14. Create Standby 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
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
[oracle@rac1 ~]$
sqlplus sys/sys@apac as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:45:37 2015

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


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

SQL>
exit
Disconnected from 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@rac1 ~]$ 
[oracle@rac1 ~]$
sqlplus sys/sys@emea as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:45:57 2015

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


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

SQL>
exit
Disconnected from 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@rac1 ~]$

[oracle@rac1 ~]$
rman target sys/sys@apac auxiliary sys/sys@emea
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 25 19:46:37 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: APAC (DBID=2914488844)
connected to auxiliary database: APAC (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 25-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwapac' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwemea'   ;
}
executing Memory Script

Starting backup at 25-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
Finished backup at 25-JUL-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/emea/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/emea/control02.ctl' from
 '/u01/app/oracle/oradata/emea/control01.ctl';
}
executing Memory Script

Starting backup at 25-JUL-15
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_apac.f tag=TAG20150725T195200 RECID=1 STAMP=886017123
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 25-JUL-15

Starting restore at 25-JUL-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 25-JUL-15

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
 "/u01/app/oracle/oradata/emea/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/emea/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/emea/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/emea/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/emea/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/emea/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/emea/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/emea/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/emea/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/emea/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 25-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/apac/system01.dbf
output file name=/u01/app/oracle/oradata/emea/system01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/apac/sysaux01.dbf
output file name=/u01/app/oracle/oradata/emea/sysaux01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/apac/undotbs01.dbf
output file name=/u01/app/oracle/oradata/emea/undotbs01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/apac/users01.dbf
output file name=/u01/app/oracle/oradata/emea/users01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 25-JUL-15

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=1 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=886017341 file name=/u01/app/oracle/oradata/emea/users01.dbf
Finished Duplicate Db at 25-JUL-15

RMAN>
12

Section 12

rman target sys/sys@apac auxiliary sys/sys@emea 15. Enable MRP on Standby PHYSICAL 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
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 19:58:37 2015

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


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

SQL>
select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC      MOUNTED
PHYSICAL STANDBY
SQL>

SQL>
alter database open;
Database altered.

[oracle@rac2 ~]$ ps -ef | grep mrp
oracle    1523   701  0 20:00 pts/1    00:00:00 grep mrp
<----
[oracle@rac2 ~]$


SQL>
alter database recover managed standby database disconnect from session;
Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APAC
READ ONLY WITH APPLY
PHYSICAL STANDBY

SQL>

SQL> !ps -ef | grep mrp
oracle    1533     1  0 20:01 ?        00:00:00 ora_mrp0_emea
<----
oracle    1539  1524  0 20:01 pts/1    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle    1541  1539  0 20:01 pts/1    00:00:00 grep mrp

SQL>
13

Section 13

alter database recover managed standby database disconnect from session; READ ONLY WITH APPLY <---- 16. Verify the Physical Standby Database Is Performing Properly On Primary
14

Section 14

On Primary 10 <---- On 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
SQL> show parameters db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      apac
SQL>
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         5 25-JUL-15 25-JUL-15
         6 25-JUL-15 25-JUL-15
         7 25-JUL-15 25-JUL-15
8 25-JUL-15 25-JUL-15
4 rows selected.
SQL>
SQL>
alter system switch logfile;
System altered.

SQL>
alter system switch logfile;
System altered.

SQL>
select max(sequence#) from v$archived_log where archived='YES';
MAX(SEQUENCE#)
--------------
10
<----
SQL>

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select * from v$logfile;

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

7 rows selected.

SQL>
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         5 25-JUL-15 25-JUL-15
         6 25-JUL-15 25-JUL-15
         7 25-JUL-15 25-JUL-15
         8 25-JUL-15 25-JUL-15
         9 25-JUL-15 25-JUL-15
10 25-JUL-15 25-JUL-15
6 rows selected.

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

MAX(SEQUENCE#)
--------------
10
<----
SQL>
15

Section 15

10 25-JUL-15 25-JUL-15 10 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: Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE FROM ACTIVE DATABASE (Doc ID 1075908.1) Reference:

Comments (0)

Please to add comments

No comments yet. Be the first to comment!