1.AMAÇ VE KAPSAM
Hizli ve kolay bir sekilde 11gR2’in rman duplication özelliğini kullanarak standby database kurulumu.
2.UYGULAMA
Gerekli bilgiler Primary Database SID: PRIM Primary Database Domain/IP: ABSO Standby Database SID: PRIM_SBY Standby Database Domain/IP: CROO
PRIM redo logs
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
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
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. PRIMARY DATABASE AYARLARI
2.1.1. Prımary database’ imiz üstünde 3 tane redo log group var bundan doları bizim 4 tane standby redo log groupu oluşturmamız gerekiyor.
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. PRIM_SBY tns bilgilerini PRIM instanceın tnsnames.ora’ sına($ORACLE_HOME/network/admin/) ekliyoruz.
PRIM_SBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIM_SBY) (UR = A) ) )
2.1.3. Primary database parametreleri üstünde bazı oynamalar yapıp standby için hazırlık yapmamız gerekiyor. Gerekli bilgiler kısmında primary databaseimn controlfile,datafile ve redo log file gibi özelliklerini belirtmiştim. Benim bu dosyalarım /oradata/PRIM/* tarzi lokasyonlarda bulunuyor ve kurmak istedigim standbyda datafile lokasyonlarım ise /oradata/PRIM_SBY/* şeklinde olacak bu yüzden db_file_name_convert ve log_file_name_convert parametrelerini basit bir şekilde ayarladım, eğer sizlerin lokasyonları daha karmaşık bir yapıdaysa hepsini teker teker belirmeniz gerekiyor. Bu parametrelerin aktif olması için set ettikten sonra dbye restart atınız. PS: Bu parametrelerden sonra alert log üstünde log_arch_dest_2 ile ilgili hatalar göreceksiniz, standbyı daha kurmadığımız için bu hatalar normaldir.
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. STANDBY DATABASE AYARLARI
2.2.1. PRIM_SBY listenerini ayarlayip baslatiyoruz.Bunun icin listener.ora ($ORACLE_HOME/network/admin/) editlememiz gerekiyor.
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) ) )
@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. Simdide 2 database’in tnslerini PRIM_SBY ye yaziyoruz tnsnames.ora ($ORACLE_HOME/network/admin/)
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. Şimdi PRIM_SBY için yeni bir pfile olusturuyorum bu dosyayı $ORACLE_HOME/dbs lokasyonunda initPRIM_SBY.ora isminde olusturmaniz gerekiyor. (aksi taktirde dbyi nomount yaparken pfile lokasyonu vermeniz gerekir) PS: Lütfen bütün parametreleri inceleyiniz ve sizin serverinizda var olmayan lokasyonları manuel olusturuz, bir çoğu restore operasyonu kısmında olusacaktır ama audit_file_dest gibi lokasyonların manuel olusturulması gerekmekte.
*.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. Oracle password dosyasını PRIM veritabanından PRIM_SBY le kopyaliyorum kopyalama esnasında dosya ismini PRIM_SBY ye gore degistiriyorum.
ABSO:oracle(PRIM):/oracle/11.2.0.3/dbs > scp orapwPRIM CROO:/oracle/11.2.0.3/dbs/orapwPRIM_SBY
2.2 5. ORACLE_SID , ORACLE_HOME ve PATH parametrelerini set edip, PRIM_SBY veritabanını PRIM_SBY nomount modda açıyorum.
@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 OPERASYONU
2.3.1. Size önerim restore operasyonunu standby serverı üstünden başlatmanız. Primary tarafında başladıp bazı bugların gerçekleştiğini gördüm. 4 channel kullanarak basit bir rman restore scripti olusturdum bunu duplicate.rmn dosyasına kaydettim.
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. Şimdi PRIM_SBY serverı üstünden rman bağlanıyorum target olarak PRIM database’ini auxiliary olarak standby database’ini gösterdim. Bağlandıktan sonra bir önceki adımda oluşturmuş olduğum duplicate scriptini başlatıyorum.
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. STANDBY DATABASE ÇALIŞIR HALE GETİRMEK
2.4.1. Standby database’in restore operasyonu bitti. Şimdi standby’ı aktif bir hale getirip redo log apply başlatalım.
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
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: Kurulumunuz bittikten sonra size önerim dataguard brokerıda aktif bir hale getirmeniz. Bu sayfa üstünden kurulum,monitor ve switchover gibi senaryolara erişebilirsiniz. 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.