Bugra Canbakal's Oracle Blog

  • Home
  • Contact
  • SITE MAP
You are here: Home / Oracle / Creating standby database fast and easy

Creating standby database fast and easy

05:17 AM By Bugra Canbakal 8 Comments

1.PURPOSE AND SCOPE

With 11gR2 rman duplication its really easy to create standby database.

2.PRACTICE

Information Primary Database SID: PRIM Primary Database Domain/IP: ABSO Standby Database SID: PRIM_SBY Standby Database Domain/IP: CROO

PRIM redo logs

Oracle PL/SQL
1
2
3
4
5
6
7
8
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
1 1 49 YES INACTIVE /oradata/PRIM/redo_pri/redo01pri.log 100
1 1 49 YES INACTIVE /oradata/PRIM/redo_sec/redo01sec.log 100
2 1 50 YES INACTIVE /oradata/PRIM/redo_pri/redo02pri.log 100
2 1 50 YES INACTIVE /oradata/PRIM/redo_sec/redo02sec.log 100
3 1 51 NO CURRENT /oradata/PRIM/redo_pri/redo05pri.log 100
3 1 51 NO CURRENT /oradata/PRIM/redo_sec/redo05sec.log 100

  PRIM  datafiles

Oracle PL/SQL
1
2
3
4
5
6
tablespace file size maxsize remaining AUT
---------- -------------------------------------------------- ----------- ----------- ----------- ---
SYSAUX /oradata/PRIM/data/sysaux01.dbf 960 65536 64576 YES
SYSTEM /oradata/PRIM/data/system01.dbf 710 65536 64826 YES
UNDOTBS1 /oradata/PRIM/undo/undotbs01.dbf 670 65536 64866 YES
USERS /oradata/PRIM/data/users01.dbf 5 65536 65531 YES

  PRIM controlfile

Oracle PL/SQL
1
2
3
4
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
/oradata/PRIM/data/control01.ctl NO 16384 612
/oradata/PRIM/fra/control02.ctl NO 16384 612

2.1. SETTING PRIMARY DATABASE

2.1.1.  At primary database we had got 3 redo log group so we need to add 4 standby redo log group to PRIM database.

Oracle PL/SQL
1
2
3
4
alter database add standby logfile group 11 ('/oradata/PRIM/redo_pri/sbyredo01pri.log','/oradata/PRIM/redo_sec/sbyredo01sec.log') size 100m;
alter database add standby logfile group 12 ('/oradata/PRIM/redo_pri/sbyredo02pri.log','/oradata/PRIM/redo_sec/sbyredo02sec.log') size 100m;
alter database add standby logfile group 13 ('/oradata/PRIM/redo_pri/sbyredo03pri.log','/oradata/PRIM/redo_sec/sbyredo03sec.log') size 100m;
alter database add standby logfile group 14 ('/oradata/PRIM/redo_pri/sbyredo04pri.log','/oradata/PRIM/redo_sec/sbyredo04sec.log') size 100m;

  2.1.2. We need to add PRIM_SBY tns to tnsnames.ora (location of it $ORACLE_HOME/network/admin/)  of PRIM database.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
PRIM_SBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM_SBY)
(UR = A)
)
)

  2.1.3.  We need to add some parameters for standby. At information page i want to show location of controlfiles,redo logs and datafiles these are locations are important for db_file_name_convert and log_file_name_convert parameters. At my case my all datafiles are located at /oradata/PRIM/data and for my standby i want to locate them /oradata/PRIM_SBY/data thats the reason only converting PRIM to PRIM_SBY is enough but if your file system locations are more complicated you need to set them one by one. Restart database after execute new parameters. PS: Alert log would show alert that it coudnt connect log_arch_dest_2 , its normal for now dont care about it.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
alter database force logging;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM';
alter system set log_archive_dest_2='SERVICE=PRIM_SBY ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM_SBY';
alter system set log_archive_dest_state_1='enable';
alter system set log_archive_dest_state_2='enable';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,PRIM_SBY)';
alter system set FAL_SERVER=PRIM_SBY;
alter system set FAL_CLIENT=PRIM;
alter system set DB_FILE_NAME_CONVERT='PRIM_SBY','PRIM' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='PRIM_SBY','PRIM' scope=spfile;
alter system set log_archive_max_processes=10;

2.2. SETTING STANDBY DATABASE

2.2.1. We need to set new listener for PRIM_SBY for this purpurse add this lines to listener.ora (location of it $ORACLE_HOME/network/admin/)

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
LSNR_PRIM_SBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = PRIM_SBY))
(ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
)
)
SID_LIST_LSNR_PRIM_SBY =
(SID_LIST =
(SID_DESC =
(SID_NAME = PRIM_SBY)
(GLOBAL_DBNAME = PRIM_SBY)
(ORACLE_HOME = /oracle/11.2.0.3)
)
)

Oracle PL/SQL
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
@PRIM_SBY:/home/oracle $lsnrctl start LSNR_PRIM_SBY
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-APR-2014 10:49:07
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /oracle/11.2.0.3/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /oracle/11.2.0.3/network/admin/listener.ora
Log messages written to /oracle/orabase/diag/tnslsnr/CROORAFLP01/lsnr_PRIM_sby/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PRIM_SBY)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.42.179.164)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=PRIM_SBY)))
STATUS of the LISTENER
------------------------
Alias LSNR_PRIM_SBY
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 01-APR-2014 10:49:08
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/11.2.0.3/network/admin/listener.ora
Listener Log File /oracle/orabase/diag/tnslsnr/CROORAFLP01/lsnr_PRIM_sby/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PRIM_SBY)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.42.179.164)(PORT=1521)))
Services Summary...
Service "PRIM_SBY" has 1 instance(s).
Instance "PRIM_SBY", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

  2.2.2. Time to set tnsnames.ora (location of it $ORACLE_HOME/network/admin/) at PRIM_SBY

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ABSO)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
(UR = A)
)
)
PRIM_SBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM_SBY)
(UR = A)
)
)

  2.2.3. Now i am creating new pfile for PRIM_SBY database for this purpose i create initPRIM_SBY.ora file to $ORACLE_HOME/dbs location. PS: Please check all parameters and create necessery file locations like audit_file_dest location.

Oracle PL/SQL
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
*.audit_file_dest='/oracle/orabase/PRIM_SBY/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/PRIM_SBY/data/control01.ctl','/oradata/PRIM_SBY/fra/PRIM_SBY/control02.ctl'
*.db_block_size=16384
*.db_cache_size=128M
*.db_domain=''
*.db_name='PRIM'
*.db_recovery_file_dest='/oradata/PRIM_SBY/fra'
*.db_recovery_file_dest_size=9437184000
*.db_unique_name='PRIM_SBY'
*.diagnostic_dest='/oracle'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='service="PRIM"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="PRIM" net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.memory_max_target=1073741824
*.memory_target=1073741824
*.open_cursors=1000
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.sga_max_size=1073741824
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.fal_client='PRIM_SBY'
*.fal_server='PRIM'
*.log_file_name_convert='PRIM','PRIM_SBY'
*.db_file_name_convert='PRIM','PRIM_SBY'
*.log_archive_config='dg_config=(PRIM_SBY,PRIM)'
*.log_archive_max_processes=10

  2.2 4.  Copy oracle password file of PRIM database to PRIM_SBY server and change sid name of it.

Oracle PL/SQL
1
ABSO:oracle(PRIM):/oracle/11.2.0.3/dbs > scp orapwPRIM CROO:/oracle/11.2.0.3/dbs/orapwPRIM_SBY

  2.2 5.  Please set your ORACLE_SID , ORACLE_HOME and PATH. Now we can open PRIM_SBY instance on nomount state .

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
@PRIM_SBY:/oracle/11.2.0.3/dbs $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 1 11:13:23 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 633341112 bytes
Database Buffers 423624704 bytes
Redo Buffers 9736192 bytes

2.3. DUPLICATE RESTORE OPERATION

2.3.1.   I suggest to start rman duplicate restore operation on standby servers. I experienced some bugs when i start it on primary side.  I am creating a simple rman script file with 4 channel.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
CROO:oracle(PRIM_SBY):/home/oracle > cat  /home/oracle/duplicate.rmn
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
;
}

  2.3.2. Now i am connectiong rman at PRIM_SBY server while connecting i am setting PRIM database as my target and PRIM_SBY as auxiliary database. And then i am executing rman script which i created previous step.

Oracle PL/SQL
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
CROO:oracle(PRIM_SBY): $rman target sys/"***PASS***"@PRIM auxiliary sys/"***PASS***"@PRIM_SBY
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 1 11:20:08 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=439056344)
connected to auxiliary database: PRIM (not mounted)
RMAN> @/home/oracle/duplicate.rmn
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target database for standby from active database
8> ;
9> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=762 device type=DISK
allocated channel: prmy2
channel prmy2: SID=3 device type=DISK
allocated channel: prmy3
channel prmy3: SID=36 device type=DISK
allocated channel: prmy4
channel prmy4: SID=70 device type=DISK
allocated channel: stby
channel stby: SID=694 device type=DISK
Starting Duplicate Db at 01-04-14 11:20
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/11.2.0.3/dbs/orapwPRIM' auxiliary format
'/oracle/11.2.0.3/dbs/orapwPRIM_SBY' ;
}
executing Memory Script
Starting backup at 01-04-14 11:20
Finished backup at 01-04-14 11:20
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oradata/PRIM_SBY/data/control01.ctl';
restore clone controlfile to '/oradata/PRIM_SBY/fra/control02.ctl' from
'/oradata/PRIM_SBY/data/control01.ctl';
}
executing Memory Script
Starting backup at 01-04-14 11:20
channel prmy1: starting datafile copy
copying standby control file
output file name=/oracle/11.2.0.3/dbs/snapcf_PRIM.f tag=TAG20140401T112025 RECID=1 STAMP=843736825
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-04-14 11:20
Starting restore at 01-04-14 11:20
channel stby: copied control file copy
Finished restore at 01-04-14 11:20
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
"/oradata/PRIM_SBY/data/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradata/PRIM_SBY/data/system01.dbf";
set newname for datafile 2 to
"/oradata/PRIM_SBY/data/sysaux01.dbf";
set newname for datafile 3 to
"/oradata/PRIM_SBY/undo/undotbs01.dbf";
set newname for datafile 4 to
"/oradata/PRIM_SBY/data/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradata/PRIM_SBY/data/system01.dbf" datafile
2 auxiliary format
"/oradata/PRIM_SBY/data/sysaux01.dbf" datafile
3 auxiliary format
"/oradata/PRIM_SBY/undo/undotbs01.dbf" datafile
4 auxiliary format
"/oradata/PRIM_SBY/data/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/PRIM_SBY/data/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-04-14 11:20
channel prmy1: starting datafile copy
input datafile file number=00002 name=/oradata/PRIM/data/sysaux01.dbf
channel prmy2: starting datafile copy
input datafile file number=00001 name=/oradata/PRIM/data/system01.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/oradata/PRIM/undo/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/oradata/PRIM/data/users01.dbf
output file name=/oradata/PRIM_SBY/data/users01.dbf tag=TAG20140401T112032
channel prmy4: datafile copy complete, elapsed time: 00:00:01
output file name=/oradata/PRIM_SBY/undo/undotbs01.dbf tag=TAG20140401T112032
channel prmy3: datafile copy complete, elapsed time: 00:00:25
output file name=/oradata/PRIM_SBY/data/sysaux01.dbf tag=TAG20140401T112032
channel prmy1: datafile copy complete, elapsed time: 00:00:35
output file name=/oradata/PRIM_SBY/data/system01.dbf tag=TAG20140401T112032
channel prmy2: datafile copy complete, elapsed time: 00:00:35
Finished backup at 01-04-14 11:21
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=843736868 file name=/oradata/PRIM_SBY/data/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=843736868 file name=/oradata/PRIM_SBY/data/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=843736868 file name=/oradata/PRIM_SBY/undo/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=843736868 file name=/oradata/PRIM_SBY/data/users01.dbf
Finished Duplicate Db at 01-04-14 11:21
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>
RMAN> **end-of-file**
RMAN> exit

2.4. STARTING STANDBY DATABASE

2.4.1. Our duplicate restore operation finish. Lets try is our standby working.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CROO:oracle(PRIM_SBY):/home/oracle > sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 27 14:39:31 2014
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 and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 734004408 bytes
Database Buffers 322961408 bytes
Redo Buffers 9736192 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

  PRIM_SBY alertlog

Oracle PL/SQL
1
2
3
4
5
6
7
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (PRIM_SBY)
Thu Mar 27 14:41:21 2014
MRP0 started with pid=34, OS id=23379
MRP0: Background Managed Standby Recovery process started (PRIM_SBY)
started logmerger process

  PS: I suggest to activate dataguard broker for your prim/standby instance. You can find out how to install,monitor,switchover etc from here.  https://canbakal.com/en/2014/03/28/data-guard-broker-install-kurulum-setting/

Share this:

  • LinkedIn
  • Twitter

Konuyla ilgili diğer yazılar

  • Duplicate komutuyla primary asm database’inden non-asm standby database kurulumu
  • Data Guard Broker installation, configuration, monitoring and deactiving

Filed Under: Oracle, Standby - Dataguard Tagged With: oracle duplicate, oracle standby installation, standby, switchover

Comments

  1. AvatarERTUĞRUL says

    Friday April 4th, 2014 at 08:16 AM

    Bugra çok güzel olmuş Eline sağlık.

  2. AvatarSamet Eryavuz says

    Friday April 4th, 2014 at 08:21 AM

    Ellerine sağlık hocam çok iyi oldu denedim sorunsuz olarak kurulumu tamamladım. Blogunu devamlı takip etmekteyim ve paylaşımlar için teşekkürler

  3. AvatarBugra Canbakal says

    Friday April 4th, 2014 at 08:28 AM

    Teşekkür ederim Ertuğrul ve Samet hocam.

  4. AvatarFethullah ÇABUK says

    Friday April 4th, 2014 at 09:29 AM

    Merhabalar Hocam, ellerinize sağlık çok faydalı bir döküman olmuş. Kurulum prosedürünüz çok başarılı. Türkçe kaynak bulmak oldukça zor bundan dolayı çalışmalarınızın devamını dilerim.

  5. AvatarAhmet says

    Monday April 7th, 2014 at 07:49 AM

    Buğra Bey elinize sağlık. Rman yedeğini direk dönemden yapılmış güzel bir çalışma.

  6. AvatarBugra Canbakal says

    Tuesday April 8th, 2014 at 09:01 AM

    Merhaba Ahmet bey begendiginize sevindim.

  7. AvatarSadık ASİL says

    Saturday May 31st, 2014 at 10:41 AM

    Elinize sağlık Bugra hocam

  8. AvatarBugra Canbakal says

    Saturday May 31st, 2014 at 05:33 PM

    Beğendiğinize sevindim Sadık hocam.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About Me



Language:

  • English
  • Turkish

Categories

  • Oracle
    • RAC – Real Application Cluster
    • RMAN
    • Single Node
    • Standby – Dataguard
  • OS
    • Linux

Blogroll

  • Emre Baransel
  • Gökhan Atıl
  • H. Tonguc Yılmaz
  • Kamil Türkyılmaz
  • Tanel Poder
  • Turkce Oracle
  • Turkish Oracle User Group
  • Uwe Hesse
  • Zekeriya Beşiroğlu

Archives

  • April 2014
  • March 2014
  • August 2013
  • June 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • November 2011
  • October 2011
  • August 2011
  • July 2011
  • June 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • June 2010
OCP
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

[footer_backtotop]

Copyright © 2010-2014 Bugra Canbakal. All rights reserved.