1.AMAÇ VE KAPSAM
ASM kurulu bir veritabanına duplicate komutuyla non-ASM standby yaratmak.
2.UYGULAMA
Database Özellikleri
Database Name :- bugra
Primary db_unique_name : bugra // ASM
Standby db_unique_name : stdby // non-ASM
Primary Hostname :- bugra.localdomain // 192.168.2.101
Standby Hostname :- stdby.localdomain // 192.168.2.102
2.1 Oluşturacağımız dosyalar için primaryde ve standbyda lokasyonları yaratıyoruz.
mkdir –p /home/oracle/stage
2.2 Primary makinasında force loggingi açıyoruz.
ALTER DATABASE FORCE LOGGING;
2.3 Primary makinasında standby için pfile yaratıyoruz..
create pfile=’/home/oracle/stage/stdby.ora’ from spfile;
2.4 Primary makinasında rmane bağlanıp backup alıyoruz.(Repositoryye kayıtlı eski backuplar varsa bunların silinmesi gerekir size önerim bunları başka bir klasöre kopyalayıp rman içersinde DELETE BACKUP diyip Y ye basmanızdır böylece repositoryye kayıtlı backup kalmıyacaktır ve standby bu repositorye baglandığında eski backupları aramıyacaktır.)
Rman connect /
Rman
connect target.run{
backup device type disk format=’/home/oracle/stage/%U’ incremental level 0 database include current controlfile for standby plus archivelog;
}
2.5 Backup işlemi bittikten sonra /home/oracle/stage/ lokasyonundaki backup dosyalarını standby makinasında aynı lokasyona aktarıyoruz.
2.6 Standby makinasında bazı dosyalar yaratmamız gerekiyor
mkdir –p /u01/app/oracle/product/11.1.0/oradata/stdby/
cd /u01/app/oracle/product/11.1.0/oradata/stdby/
mkdir redo data ctrl bdump udump srl arc1
2.7 Standby makinası için yarattığımız stdby.ora dosyasını vi veya geditle açıyoruz.
gedit /home/oracle/stage/stdby.ora
2.8 Aşağıdaki parametreleri ekliyoruz veya var olanları değiştiriyoruz.
db_name=bugra
db_unique_name=stdby
log_archive_config=’DG_CONFIG=(bugra,stdby)’
*.db_create_file_dest=’/u01/app/oracle/product/11.1.0/oradata/stdby/data/’
*.db_create_online_log_dest_1=’/u01/app/oracle/product/11.1.0/oradata/stdby/redo/’
*.db_file_name_convert=’+DATA/bugra/datafile/’,’/u01/app/oracle/product/11.1.0/oradata/stdby/data/’,’+DATA/bugra/tempfile/’,’/u01/app/oracle/product/11.1.0/oradata/stdby/data/’
*.log_file_name_convert=’+DATA/bugra/onlinelog/’,’/u01/app/oracle/product/11.1.0/oradata/stdby/redo’
standby_file_management=AUTO
fal_client=stdby
fal_server=bugra
control_files=’/u01/app/oracle/product/11.1.0/db_2/dbs/cntrlbugra.dbf’
compatible=11.1.0.0.0
2.9 Standby makinasında ve Product makinesinde $ORACLE_HOME/dbs klasörünün içinde orapwd dosyası yaratmamız gerekiyor böylelikle serverlar birbirlerine authorize olabilsinler.
Standby Makinası
export ORACLE_SID=stdby
orapwd file=orapwstdby password=oracle
Product Makinesi
export ORACLE_SID=bugra
orapwd file=orapwbugra password=oracle
2.10 Standby makinasında tnsnames ve listener ayarlarını yapmamız gerekiyor.
cat >> $ORACLE_HOME/network/admin/listener.ora <<EOF
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(HOST = 192.168.2.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby)
)
)
BUGRA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(HOST = 192.168.2.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bugra)
)
)
EOF
cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby)
)
)
BUGRA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bugra)
)
)
EOF
2.11 Standby makinasını için hazırladığımız init dosyasını sisteme tanıdıp nomount modunda açıyoruz.
Sqlplus / as sysdba
SQL> create spfile from pfile=’/home/oracle/stage/stdby.ora’;
File created.
SQL> startup nomount
2.12 Standby makinasına Rman aracılığıyla primery servera ve şuanki auxiliary serverımıza bağlanmamız gerekiyor, daha sonrada duplicate komutuyla primary serverdan clone çalışmasına başlıyoruz.
[oracle@stdby ~]$ export ORACLE_SID=stdby
[oracle@stdby ~]$ rman target sys/oracle@bugra auxiliary /
Recovery Manager: Release 11.1.0.6.0 – Production on Thu Jun 10 03:16:19 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Recovery Manager: Release 11.1.0.6.0 – Production on Sat Jun 12 02:11:50 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: BUGRA (DBID=772585308)
connected to auxiliary database: BUGRA (not mounted)
RMAN> duplicate target database for standby dorecover;
Starting Duplicate Db at 12-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=99 device
contents of Memory Script:
{
set until scn 816762;
restore clone standby controlfile;
sql clone ‘alter database mount standby database’;
}
executing Memory Script
executing command: SET until clause
Starting restore at 12-JUN-10
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/1vlg17uh_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/stage/1vlg17uh_1_1 tag=TAG20100612T054956
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/product/11.1.0/db_1/dbs/cntrlbugra.dbf
Finished restore at 12-JUN-10
sql statement: alter database mount standby database
contents of Memory Script:
{
set until scn 816762;
set newname for tempfile 1 to
“/u01/app/oracle/product/11.1.0/oradata/stdby/data/temp.268.720476923”;
switch clone tempfile all;
set newname for datafile 1 to
“/u01/app/oracle/product/11.1.0/oradata/stdby/data/system.256.720476647”;
set newname for datafile 2 to
“/u01/app/oracle/product/11.1.0/oradata/stdby/data/sysaux.257.720476649”;
set newname for datafile 3 to
“/u01/app/oracle/product/11.1.0/oradata/stdby/data/undotbs1.258.720476653”;
set newname for datafile 4 to
“/u01/app/oracle/product/11.1.0/oradata/stdby/data/users.259.720476655”;
set newname for datafile 5 to
“/u01/app/oracle/product/11.1.0/oradata/stdby/data/example.269.720476937”;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/product/11.1.0/oradata/stdby/data/temp.268.720476923 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 12-JUN-10
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/product/11.1.0/oradata/stdby/data/system.256.720476647
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/product/11.1.0/oradata/stdby/data/sysaux.257.720476649
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/product/11.1.0/oradata/stdby/data/undotbs1.258.720476653
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/product/11.1.0/oradata/stdby/data/users.259.720476655
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/product/11.1.0/oradata/stdby/data/example.269.720476937
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/1ulg17o4_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/stage/1ulg17o4_1_1 tag=TAG20100612T054956
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:40
Finished restore at 12-JUN-10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=721448270 file name=/u01/app/oracle/product/11.1.0/oradata/stdby/data/system.256.720476647
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=721448270 file name=/u01/app/oracle/product/11.1.0/oradata/stdby/data/sysaux.257.720476649
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=721448270 file name=/u01/app/oracle/product/11.1.0/oradata/stdby/data/undotbs1.258.720476653
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=721448270 file name=/u01/app/oracle/product/11.1.0/oradata/stdby/data/users.259.720476655
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=721448270 file name=/u01/app/oracle/product/11.1.0/oradata/stdby/data/example.269.720476937
contents of Memory Script:
{
set until scn 816762;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 12-JUN-10
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/20lg17vg_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/stage/20lg17vg_1_1 tag=TAG20100612T055352
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.1.0/db_1/dbs/arch1_20_720476831.dbf thread=1 sequence=20
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.1.0/db_1/dbs/arch1_20_720476831.dbf RECID=1 STAMP=721448283
media recovery complete, elapsed time: 00:00:02
Finished recover at 12-JUN-10
Finished Duplicate Db at 12-JUN-10
2.14 Duplicate işlemi bittikten sonra standby makinasında redo loglar yaratmamız gerekiyor.
SQL> alter database add standby logfile group 4 ‘/u01/app/oracle/product/11.1.0/oradata/stdby/srl/srl4a.log’ size 150m;
Database altered.
SQL> alter database add standby logfile group 5 ‘/u01/app/oracle/product/11.1.0/oradata/stdby/srl/srl5a.log’ size 150m;
Database altered.
SQL> alter database add standby logfile group 6 ‘/u01/app/oracle/product/11.1.0/oradata/stdby/srl/srl6a.log’ size 150m;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
alter system archive log current;
2.15 Standb-primary ilişkisi kurulması için standbyda bazı ayarlar yapmamız gerekiyor.Aşağıdakileri Sqlplusa giriyoruz.
SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
——— —————-
stdby PHYSICAL STANDBY
SQL> alter system set standby_archive_dest=’/u01/app/oracle/product/11.1.0/oradata/stdby/arc1/’;
System altered.
SQL> alter system set log_archive_dest_1=’location=/u01/app/oracle/product/11.1.0/oradata/stdby/arc1/ valid_for=(all_logfiles,all_roles) db_unique_name=stdby’;
SQL> alter system set log_archive_config=’dg_config=(bugra,stdby)’;
System altered.
SQL> alter system set log_archive_dest_2=’service=bugra lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=bugra’;
System altered.
SQL> alter system set fal_client=stdby;
System altered.
SQL> alter system set fal_server=bugra;
SQL> alter system set standby_file_management=auto;
2.16 Primary serverın tnsnames ve listener ayarlamamız gerekiyor.
cat >> $ORACLE_HOME/network/admin/listener.ora <<EOF
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(HOST = 192.168.2.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby)
)
)
BUGRA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(HOST = 192.168.2.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bugra)
)
)
EOF
cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bugra)
)
)
BUGRA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bugra)
)
)
EOF
2.17Primary serverın standbyla bağlantı kurabilmesi için bazı ayarlar yapmamız gerekiyor.
SQL> alter system set log_archive_config=’dg_config=(bugra,stdby)’;
System altered.
SQL> alter system set log_archive_dest_2=’service=stdby lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=stdby’;
System altered.
SQL> alter system set fal_client=bugra;
System altered.
SQL> alter system set fal_server=stdby;
System altered.
SQL> select name,database_role from v$database;
System altered.
SQL> alter system set db_file_name_convert=’/u01/app/oracle/product/11.1.0/oradata/stdby/data/’,’+DATA/bugra/datafile/’,’/u01/app/oracle/product/11.1.0/oradata/stdby/data/’,’+DATA/bugra/tempfile/’ scope=spfile;
System altered.
SQL> alter system set log_file_name_convert=’/u01/app/oracle/product/11.1.0/oradata/stdby/redo/’,’+DATA/chicago/onlinelog/’ scope=spfile;
System altered.
SQL> alter system set standby_file_management=auto;
NAME DATABASE_ROLE
——— —————-
bugra PRIMARY
2.18Son olarak primaryde standby için redologları yaratıyoruz.
SQL> alter database add standby logfile group 3 size 150m;
Database altered.
SQL>alter database add standby logfile group 4 size 150m
Database altered.
SQL>alter database add standby logfile group 5 size 150m
3. Primarylen bağlantının koparılıp standbyın aktif edilmesi
3.1 Artık standby aktif hale geçiriyoruz.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 2564096 bytes
SQL> Alter database mount standby database;
Database altered.
SQL> recover managed standby database disconnect from session
Media recovery complete.
SQL> select log_mode,open_mode , database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
———— ———- —————-
ARCHIVELOG READ ONLY PHYSICAL STANDBY
Leave a Reply