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
|
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
|
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
|
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.
|
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.
|
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.
|
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/)
|
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) ) ) |
|
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
|
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.
|
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.
|
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 .
|
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.
|
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.
|
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.
|
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
|
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/
Bugra çok güzel olmuş Eline sağlık.
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
Teşekkür ederim Ertuğrul ve Samet hocam.
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.
Buğra Bey elinize sağlık. Rman yedeğini direk dönemden yapılmış güzel bir çalışma.
Merhaba Ahmet bey begendiginize sevindim.
Elinize sağlık Bugra hocam
Beğendiğinize sevindim Sadık hocam.