create STANDBY DATABASE step-by-step
Koristan link - procitaj ( skripte i logovi svih operacija )
# -- Linux 5u5
[root@dbsrvprim ~]# uname -a --- provera kernela
Linux dbsrvprim 2.6.18-194.el5 #1 SMP Mon Jun 11 22:15:29 EDT 2010
x86_64 x86_64 x86_64 GNU/Linux
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
serveri
10.1.11.100 knjaz11
10.1.11.135 standby135
Basic Standby (manual standby)
podesi listener i tnsnames
VAZNO: linije ispod , pusti jednu po jednu sa PRODUKCIJE
export LOCAL=10.1.11.100 --- produkcija IP
export REMOTE1=10.1.11.135 --- standby IP
ssh $REMOTE1 "rm -rf /home/oracle/.ssh"
rm -rf /home/oracle/.ssh
ssh-keygen -t rsa -f ~/.ssh/id_rsa -N '' -q
ssh -o StrictHostKeyChecking=no $REMOTE1 "ssh-keygen -t rsa -f ~/.ssh/id_rsa -N '' -q"
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
scp -o StrictHostKeyChecking=no $REMOTE1:~/.ssh/id_rsa.pub ~/.ssh/id_rsa.pub.tmp
cat ~/.ssh/id_rsa.pub.tmp >> ~/.ssh/authorized_keys
ssh -o StrictHostKeyChecking=no -q $LOCAL /bin/true
scp -o StrictHostKeyChecking=no -q ~/.ssh/authorized_keys $REMOTE1:~/.ssh/authorized_keys
provera: ako sve radi , konektovaces se sa jednog na drugi bez PASSWORD-a
putty na jedan od ta dva servera i konektuj se na drugi
ssh 10.1.11.135 date;date
ssh 10.1.11.100 date;date
4.korak podesavanje vremena - oba servera
kao root : su -
date -s 15:17
hwclock -r
hwclock --systohc
hwclock -r
5.korak kreiranje foldera za arhive na primarnom serveru
SQL>!mkdir -p /home/oracle/archives/knjaz11 --- pravi folder na OS u
prebacivanje u arhiv log mode / "force logging" !: --- treba za primarnu bazu kada se bude nova instalirala
shutdown/startup mount
10:33:58 prim1 >alter database archivelog; --- u slucaju da nije u archivelog u treba poterati
10:34:48 prim1 >archive log list; --- podaci o arhivama
provera:
SQL>ALTER DATABASE FORCE LOGGING;
6.korak listener provera
#lsnrctl status
----------------------------------------------------------------------------------------------------------
STATUS of the LISTENER
----------------------------------------------------------------------------------------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 11-JUN-2015 13:37:46
Uptime 0 days 2 hr. 7 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
/u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File
/u01/app/oracle/diag/tnslsnr/standbypr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standbypr)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "prim1" has 1 instance(s).
Instance "prim1", status READY, has 1 handler(s) for this service...
Service "prim1XDB" has 1 instance(s).
Instance "prim1", status READY, has 1 handler(s) for this service...
Service "prim1_XPT" has 1 instance(s).
Instance "prim1", status READY, has 1 handler(s) for this service...
The command completed successfully
-------------------------------------------------------------------------------------------------------------
rlwrap --- paket u linuxu koji u linuxu omogucava da se na strelice vracaju komande
rpm -ivh rlwrap-0.37-1.el5.x86_64.rpm --- redhatpackagemanager
7.korak fajlovi primarne baze
RMAN> report schema; ---prikaz o datafile , njihove lokacije
Report of database schema for database with db_unique_name KNJAZ11
List of Permanent Datafiles
==============================================================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------------------------------------------------
1 690 SYSTEM ***
/u01/app/oracle/oradata/KNJAZ11/datafile/o1_mf_system_6czxgq7j_.dbf
2 561 SYSAUX ***
/u01/app/oracle/oradata/KNJAZ11/datafile/o1_mf_sysaux_6czxgq7s_.dbf
3 40 UNDOTBS1 ***
/u01/app/oracle/oradata/KNJAZ11/datafile/o1_mf_undotbs1_6czxgqdo_.dbf
4 5 USERS ***
/u01/app/oracle/oradata/KNJAZ11/datafile/o1_mf_users_6czxgqgy_.dbf
List of Temporary Files
=============================================================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- ---------------------------------------
1 20 TEMP 32767
/u01/app/oracle/oradata/KNJAZ11/datafile/o1_mf_temp_6czxlmby_.tmp
8.korak kreiranje - init file za standby bazu
kreiranje - control file za standby bazu
SQL> create pfile from spfile; --- ako je rekreiranje u pitanju ova linija mi ne treba
SQL>alter database create standby controlfile as '/u01/app/oracle/oradata/KNJAZ11/stand_by_.ctl'; --- promeni lokaciju i ime
9.korak kopiranje kreiranih fajlova backup-a, control file-a, i pi file na standby server:
control file --- o1_mf_6j1bvc87_.ctl je ime file-a sa produkcije, mora da se rename kada se kopira na standby
scp /u01/app/oracle/oradata/KNJAZ11/stand_by_.ctl oracle@10.1.11.126:/u01/app/oracle/oradata/KNJAZ11/controlfile/o1_mf_6j1bvc87_.ctl
--- kopiranje stanby control file a na lokaciju navedenu u standby
pi file ako je rekreiranje onda se ovaj korak ne radi
scp $ORACLE_HOME/dbs/initKNJAZ11.ora oracle@10.1.11.126:$ORACLE_HOME/dbs/initKNJAZ11.ora --- kopiranje pi file- a
kopiranje password fajla ako je rekreiranje onda se ovaj korak ne radi, tj . ako nije menjan password za sys usera
scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwknjaz11 oracle@10.1.11.135:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwknjaz11 --- kopiranje password file a
10.korak pi file za standby
[oracle@standbysek dbs]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initknjaz11.ora
===========================================================
prim1.__db_cache_size=327155712
prim1.__java_pool_size=12582912
prim1.__large_pool_size=4194304
prim1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prim1.__pga_aggregate_target=180355072
prim1.__sga_target=545259520
prim1.__shared_io_pool_size=0
prim1.__shared_pool_size=192937984
prim1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/knjaz11/adump' --- ova putanja mora da ima na standby, ako je nema baza se ne dize
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/KNJAZ11/controlfile/o1_mf_6j1bvc87_.ctl' --- putanja na koju baza ocekuje da vidi control file
*.log_archive_dest='/home/oracle/archives/knjaz11'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata' ---ista putanja mora da bude i na primarnoj i na standby (tu ce da se prave data fileovi)
*.db_domain=''
*.db_name='knjaz11' --- isto ime i na standby i na primarnoj
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prim1XDB)'
*.memory_target=725614592
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO' --- mora da bude na auto na obe
===========================================================
Pokrenuti standby instancu
SQL>sqlplus /nolog
SQL> conn / as sysdba
Connected to an idle instance.
select status from v$instance; -- vidi u kom je statusu baza
SQL> startup nomount pfile='initKNJAZ11.ora'; ---ako radim rekreiranje ova komanda se ne koristi ( samo na `svez` STANDBY )
SQL> alter database mount standby database; --- radi se i kad je rekreiranje
11.korak kreiranje backup-a primarne baze
mkdir -p /u01/app/oracle/oradata/KNJAZ11/datafile/ --- radi se ako lokacija ne postoji ( to je samo kad je nov STANDBY )
SQL>alter database begin backup; --- otvara bazu za backup
SQL>!scp /u01/app/oracle/oradata/KNJAZ11/datafile/*.dbf oracle@10.1.11.126:/u01/app/oracle/oradata/KNJAZ11/datafile/ ---kopiranje
je najbolje pustiti sa neke virtuelne masine , da slucajno tvoja `ne otkine` i sve iz pocetka
SQL>alter database end backup; --- kada zavrsi kopiranje datafile, komanda koja vraca bazu u osnovni mod
12.korak provera/podesavanje "parametra standby_file_management":
SQL> show parameter standby;
NAME TYPE VALUE
---------------------------------------------------- --------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL --- mora da bude AUTO , komanda ispod
SQL>alter system set standby_file_management='AUTO' SCOPE=BOTH; ---komanda sa MANUAL u AUTO , inace se to radi u pfile
13.korak dodatni folderi za stby server: --- napravi foldere za bazu koju imas i na primarnom -- prave se samo kada je nova masina
mkdir -p /u01/app/oracle/admin/knjaz11/adump
mkdir -p /u01/app/oracle/admin/knjaz11/dpdump
mkdir -p /u01/app/oracle/admin/knjaz11/pfile
mkdir -p /u01/app/oracle/diag/rdbms/knjaz11/prim1/
mkdir -p /u01/app/oracle/oradata/KNJAZ11/datafile
mkdir -p /u01/app/oracle/oradata/KNJAZ11/controlfile
mkdir -p /home/oracle/archives/knjaz11
~ --- ovaj znak oznacava ORACLE_HOME
1. pusti skriptu na produkciji za kopiranje arhiva CRONTAB -L
2. kad se zavrsi kopiranje arhiva `odglavi` skriptu na standby za applay ovanje arhiva
3. proveri da li se arhive apllay i to je to tail -n 1000 -f /home/oracle/logs/stby_apply.log
================================================= skripte za manual stby ================================================
rsync
> /home/oracle/archives/prim1/
rsync -t -e ssh -Pazv /home/oracle/archives/prim1/*
oracle@standbysek:/home/oracle/archives/prim1
skripta
[oracle@standbysek ~]$ mkdir scripts
[oracle@standbysek ~]$ cd scripts
touch apply_man_stby.sh
chmod +x apply_man_stby.sh
vi apply_man_stby.sh
mkdir ~/logs
apply_man_stby.sh
=====================================================================
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=prim1
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LOG01=/home/oracle/logs/stby_apply.log
echo "----------------" >> $LOG01
echo "Start apply:" >> $LOG01
date >> $LOG01
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF >> $LOG01
recover standby database;
AUTO
exit
EOF
echo "----------------" >> $LOG01
echo "Finished apply:" >> $LOG01
date >> $LOG01
echo "----------------" >> $LOG01
=====================================================================
mount_stby.sh zahteva spfile
create spfile='/u01/app/oracle/product/11.1.0/db_1/dbs/spfileprim1.ora'
from pfile='stby2_init_00';
touch mount_stby.sh
chmod +x mount_stby.sh
vi mount_stby.sh
mount_stby.sh
=====================================================================
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=prim1
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LOG01=/home/oracle/logs/stby_mount.log
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF >> $LOG01
shutdown immediate;
startup nomount;
alter database mount standby database;
exit
EOF
====================================================================
touch open_ro_stby.sh
chmod +x open_ro_stby.sh
vi open_ro_stby.sh
open_ro_stby.sh
====================================================================
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=prim1
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LOG01=/home/oracle/logs/stby_open_ro.log
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF >> $LOG01
alter database open read only;
exit
EOF
===================================================================
primarna
mkdir ~/scripts
cd ~/scripts
touch send_archs.sh
chmod +x send_archs.sh
vi send_archs.sh
[oracle@standbypr prim1]$ mkdir ~/logs
send_archs.sh
===================================================================
#!/bin/bash
export LOG01=/home/oracle/logs/stby_sync.log
export PRIMARY_SERVER_ARCH_DIR=/home/oracle/archives/prim1
export STBY_SERVER=oracle@standbysek
export STBY_SERVER_ARCH_DIR=/home/oracle/archives/prim1
rsync -t -e ssh -Pazv $PRIMARY_SERVER_ARCH_DIR/*
$STBY_SERVER:$STBY_SERVER_ARCH_DIR >> $LOG01
===================================================================
[oracle@standbypr scripts]$ /home/oracle/scripts/send_archs.sh
[oracle@standbypr scripts]$ ls -lrth ~/logs
total 4.0K
-rw-r--r-- 1 oracle oinstall 403 Oct 21 16:17 stby_sync.log
[oracle@standbypr scripts]$ cat ~/logs/stby_sync.log
building file list ...
6 files to consider
1_10_732968021.dbf
334848 100% 5.14MB/s 0:00:00 (xfer#1, to-check=5/6)
1_9_732968021.dbf
13312 100% 224.14kB/s 0:00:00 (xfer#2, to-check=0/6)
sent 93402 bytes received 64 bytes 186932.00 bytes/sec
total size is 18842624 speedup is 201.60
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE
from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
---------- -------------------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY
test create new tablespace
SQL>create tablespace test;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_system_6czxgq7j_.dbf
2 564 SYSAUX ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_sysaux_6czxgq7s_.dbf
3 40 UNDOTBS1 ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_undotbs1_6czxgqdo_.dbf
4 5 USERS ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_users_6czxgqgy_.dbf
5 100 TEST ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_test_6d0n2qb3_.dbf
ok na standby
[oracle@standbysek dbs]$ tail -f
/u01/app/oracle/diag/rdbms/prim1/prim1/trace/alert_prim1.log
Media Recovery Log /home/oracle/archives/prim1/1_12_732968021.dbf
Successfully added datafile 5 to media recovery
Datafile #5:
'/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_test_6d0n83hp_.dbf'
standby
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name PRIM1
List of Permanent Datafiles
==========================================================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_system_6czxgq7j_.dbf
2 564 SYSAUX ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_sysaux_6czxgq7s_.dbf
3 40 UNDOTBS1 ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_undotbs1_6czxgqdo_.dbf
4 5 USERS ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_users_6czxgqgy_.dbf
5 100 TEST ***
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_test_6d0n83hp_.dbf
List of Temporary Files
==========================================================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767
/u01/app/oracle/oradata/PRIM1/datafile/o1_mf_temp_6d0ng2vn_.tmp
[oracle@standbysek scripts]$ ./open_ro_stby.sh
ok
alert
Physical standby database opened for read only access.
Thu Jun 11 16:45:08 2015
Completed: alter database open read only
kada se standby otvara u read-only - treba disable-ovati skript koji aplicira iz crontab-a
[oracle@standbysek scripts]$ ./mount_stby.sh
ok.
[oracle@standbysek scripts]$ ls -lrth /home/oracle/archives/prim1
total 116M
-rw-r----- 1 oracle oinstall 4.4M Oct 21 14:35 1_5_732968021.dbf
-rw-r----- 1 oracle oinstall 10M Oct 21 15:27 1_6_732968021.dbf
-rw-r----- 1 oracle oinstall 1.0K Oct 21 15:27 1_7_732968021.dbf
-rw-r----- 1 oracle oinstall 3.4M Oct 21 16:07 1_8_732968021.dbf
-rw-r----- 1 oracle oinstall 13K Oct 21 16:08 1_9_732968021.dbf
-rw-r----- 1 oracle oinstall 327K Oct 21 16:11 1_10_732968021.dbf
-rw-r----- 1 oracle oinstall 1.3M Oct 21 16:26 1_11_732968021.dbf
-rw-r----- 1 oracle oinstall 573K Oct 21 16:39 1_12_732968021.dbf
-rw-r----- 1 oracle oinstall 48M Oct 21 22:15 1_13_732968021.dbf
-rw-r----- 1 oracle oinstall 48M Oct 22 07:49 1_14_732968021.dbf
[oracle@standbysek scripts]$
#58 8 * * * /home/oracle/scripts/start_ro_open.sh >/dev/null 2>&1
#58 15 * * * /home/oracle/scripts/restart_from_ro.sh >/dev/null 2>&1
skript za brisanje arhiva
del_archs.sh
touch del_archs.sh
chmod +x del_archs.sh
vi del_archs.sh
ok
del_archs.sh
==================================================================
#!/bin/bash
export LOG_01=/home/oracle/logs/del_archs_log_`date -I`
# Cuva arhive 5 dana :
export DAYS=5
export ARCHS_DIR=/home/oracle/archives/prim1
cd $ARCHS_DIR
echo "-------- Script start ----------" > $LOG_01
echo "--------------------------------" >> $LOG_01
date >> $LOG_01
echo "--------------------------------" >> $LOG_01
echo "Stanje pre brisanja:" >> $LOG_01
echo "--------------------------------" >> $LOG_01
echo "Br. fajlova :" >> $LOG_01
echo "--------------------------------" >> $LOG_01
ls -lrth $ARCHS_DIR|wc -l >> $LOG_01
echo "--------------------------------" >> $LOG_01
echo "Najstarije arhive :" >> $LOG_01
echo "--------------------------------" >> $LOG_01
ls -lrth $ARCHS_DIR|head -4 >> $LOG_01
echo "--------------------------------" >> $LOG_01
echo "--------------------------------" >> $LOG_01
# Brisanje :
find $ARCHS_DIR -name '*.dbf' -mtime +$DAYS -print0 | xargs -0 rm -f
echo "--------------------------------" >> $LOG_01
echo "--------------------------------" >> $LOG_01
echo "Stanje posle brisanja u $DB_SID:" >> $LOG_01
echo "--------------------------------" >> $LOG_01
echo "Najstarije arhive :" >> $LOG_01
echo "--------------------------------" >> $LOG_01
ls -lrth $ARCHS_DIR|head -3 >> $LOG_01
echo "Stanje posle brisanja u $ASM_SID:" >> $LOG_01
echo "--------------------------------" >> $LOG_01
echo "Br. fajlova :" >> $LOG_01
echo "--------------------------------" >> $LOG_01
ls -lrth $ARCHS_DIR|wc -l >> $LOG_01
echo "-------- Script end ----------" >> $LOG_01
=================================================================
20 12 * * * /home/oracle/scripts/del_archs.sh >/dev/null 2>&1
cron:
p
0,12,24,36,48 * * * * /home/oracle/scripts/send_archs.sh >/dev/null 2>&1
s:
2,14,26,38,50 * * * * /home/oracle/scripts/apply_man_stby.sh >/dev/null 2>&1
pracenje "live" kroz alert logove
tail -f /u01/app/oracle/diag/rdbms/prim1/prim1/trace/alert_prim1.log