2017年（38）
分类： Oracle
2017-12-07 13:48:13
1.原平臺：windows 2003(32-bit)+Oracle 10.2.0.4
2.新平臺：Linux 5(64-bit)+Oracle 10.2.0.4
注：機器硬件不變(主備庫均4G內存)，另外有兩臺臨時使用機器做切換使用
1.用臨時機器1建立windows的standby
PS:和原庫建立一樣的OS和Oracle,以便撤下原備庫后，當standby臨時使用
1>先安裝10201_database_win32
只安裝軟件，不建DB
2>升級至10.2.0.4
安裝patch: p6810189_10204_Win32(運行setup.exe均為圖形操作)
3>創建和主庫的文件路徑:
select * from v$parameter where value like '%\%';
select * from dba_data_files;
select * from dba_temp_files;
4>copy密碼文件和參數文件
注意：windows在ORACLE_HOME/database下
5>開啟臨時standby至nomount;
C:\Documents and Settings\Administrator>set oracle_sid=nbe2
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 11月 15 10:30:26 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:ORA-12560: TNS:協定介面程式錯誤
發現無法nomount，運行services.msc發現和原庫相比缺少OracleServicenbe2服務，重啟server后仍然不行，后使用oradim：
C:\Documents and Settings\Administrator>oradim -new -sid nbe2
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL> startup nomount;
ORACLE 執行處理已啟動.
Total System Global Area 1577058304 bytes
Fixed Size 1299216 bytes
Variable Size 360713456 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7086080 bytes
6>對主庫全備份，至臨時standby recover
主庫：
RMAN>backup database format='E:\rmanbak%U_%T.bak';
sql>alter database create standby controlfile as 'E:\control01.ctl';
備庫：
sql>startup nomunt;
sql>alter database mount standby database;
RMAN>restore database;
sql>alter system set standby_file_management=auto;
設置歸檔recover批處理
autoreco.bat:
if exist Y: then net use Y: /delete /y
net use Y: \\10.107.5.15\e$ "gcmv&246" /user:administrator
Y:
cd oradata
cd nbe2
cd arch
xcopy /D *.arc e:\oradata\nbe2\arch >>d:\auto\autocopy_nbe2.log
e:
net use Y: /delete /y
set ORACLE_SID=nbe2
sqlplus /nolog @d:\auto\autoreco.bak >d:\auto\autoreco_nbe2.log
autoreco.bak:
connect / as sysdba
set autorecovery on
recover standby database;
exit;
2.用臨時機器2建立linux的standby(先裝數據庫軟件)
[root@linuxstandby ~]# groupadd dba
[root@linuxstandby ~]# groupadd oper
[root@linuxstandby ~]# useradd -g dba -G oper oracle
[root@linuxstandby ~]# id oracle
uid=500(oracle) gid=500(dba) groups=500(dba),501(oper) context=root:system_r:unconfined_t:SystemLow-SystemHigh
[root@linuxstandby ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
[root@linuxstandby ~]# uname -r
2.6.18-194.el5
[root@linuxstandby ~]# vi /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2070204416
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
[root@linuxstandby ~]# sysctl -p
[root@linuxstandby ~]# vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
[root @linuxstandby ~]# vi /etc/pam.d/login
session required /lib/security/pam_limits.so
[oracle@linuxstandby u01]$ mkdir product
[oracle@linuxstandby u01]$ cd product/
[oracle@linuxstandby product]$ mkdir oracle
[oracle@linuxstandby oracle]$ vi ~/.bash_profile
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/bin:/sbin:/usr/bin:/usr/sbin
export BASH_ENV PATH
PAHT=$PAHT:$HOME/bin:/bin:/sbin:/usr/bin:/usr/sbin
BASH_ENV=$HOME/.BASHRC
export BASH_ENV PATH
unset USERNAME
# Set Oracle Environment
ORACLE_HOME=/u01/product/oracle;export ORACLE_HOME
ORACLE_SID=nbe2;export ORACLE_SID
ORACLE_OWNER=oracle;export ORACLE_OWNER
ORACLE_BASE=/u01/product;export ORACLE_BASE
ORACLE_TERM=vt100;export ORACLE_TERM
#NLS_LANG='traditional chinese_taiwan'.ZHT16BIG5;export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
set -u
PS1=`hostname`'<*$ORACLE_SID*$PWD>$';export PS1
EDITOR=/bin/vi; export EDITOR
JAVA_HOME=/usr/local/java;export JAVA_HOME
ORA_NLS33=/u01/product/oracle/ocommon/nls/admin/data;export ORA_NLS33
CLASSPATH=/u01/product/oracle/jdbc/lib/classesl11.zip:/usr/local/java;
export DISPLAY=127.0.0.1:0.0
export LD_ASSUME_KERNEL=2.6.18
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVA_HOME/bin:$PATH:.;export PATH
alias ll='ls -l';
alias ls='ls --color';
alias his='history';
解壓10.2.0.1安裝文件
linuxstandby<*evection*/u01/packages>$cpio -idmv < 10201_database_linux_x86_64.cpio
啟用圖形VNC
linuxstandby<*evection*/home/oracle>$vncserver
linuxstandby<*evection*/home/oracle>$vi .vnc/xstartup
#twm & -à gnome-session &
安裝oracle軟件
linuxstandby<*evection*/u01/packages/database>$./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Failed <<<<
./runInstaller -ignoreSysPreReqs
查看需要的包是否全部安裝:
#rpm-qa|grep XXXX
glibc-2.5-24.x86_64.rpm
glibc-common-2.5-24.x86_64
binutils-2.17.50.0.6-6.el5.x86_64.rpm
compat-db-4.2.52-5.1.x86_64.rpm
control-center-2.16.0-16.el5.x86_64.rpm
gcc-4.1.2-42.el5.x86_64.rpm
gcc-c++-4.1.2-42.el5.x86_64.rpm
gnome-libs-1.4.2-7.rhel5.x86_64.rpm --可不要
libstdc++-4.1.2-42.el5.x86_64.rpm
libstdc++-devel-4.1.2-42.el5.x86_64.rpm
make-3.81-3.el5.x86_64.rpm
ksh-20080202-2.el5.x86_64.rpm
sysstat-7.0.2-1.el5.x86_64.rpm
gnome-screensaver-2.16.1-8.el5
libaio-devel-0.3.106-3.2.x86_64.rpm
libaio-0.3.106-3.2.x86_64.rpm
ibXp-devel-1.0.0-8.1.el5(i386和x86_64)
libXp-1.0.0-8.1.el5(i386和x86_64)
[root@linuxstandby ~]# rpm -Uvh sysstat-7.0.2-1.el5.x86_64.rpm
解壓10.2.0.4補丁安裝文件
linuxstandby<*evection*/u01/packages>$unzip p6810189_10204_Linux_x86_64.zip
VNC安裝
3.用原standby機器建立linux的主庫(先裝數據庫軟件)
[root@linuxstandby ~]# vi /etc/sysctl.conf
kernel.shmall = 2097152 #must
kernel.shmmax = 2684354560 #2.5G sga+pgs=2G
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
[root@linuxstandby ~]# sysctl -p
其它配置和standby一樣。配置listener時注意如果配置非1521端口的話要加上：
nbe2<*nbe2*/data/nbe2>$vi /u01/product/oracle/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/product/oracle)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = nbe2)---service_name
(ORACLE_HOME = /u01/product/oracle)
(SID_NAME = nbe2)-----SID
)
)
安裝數據庫軟件后,DBCA創建數據庫
原來32位DBSGA為1304M，PGA為800M(實際使用350M左右)，
新DB新SGA設1600M，PGA800M(新DB是64位系統4G內存，保守起見SGA比原來設大一點)
另注意1526端監聽的配置
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
#(ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.5.15)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.5.15)(PORT = 1526))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
4.為新庫建立臨時standby，準備使用LGWR方式傳輸
將主庫5.15設置為強制歸檔，防止一些操作如：insert /*+append*/ create table test as
不產生redo而導致standby不同步
select force_logging from v$database;
alter database force logging;
設置主庫參數
--alter system set log_archive_dest_state_2=defer
alter system set log_archive_dest_2='SERVICE=nbe2dg OPTIONAL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=nbe2dg'
alter system set log_archive_config='dg_config=(nbe2,nbe2dg)'
--alter system set db_unique_name='nbe2'
alter system set fal_client='nbe2'
alter system set fal_server='nbe2dg'
設置主庫tnsnames.ora加入以下：
nbe2=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.107.5.15)
(PORT=1526)
)
(CONNECT_DATA=
(SERVICE_NAME=nbe2)
)
)
nbe2dg=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.107.5.54)
(PORT=1526)
)
(CONNECT_DATA=
(SERVICE_NAME=nbe2dg)
)
)
創建pfile，到standby上使用
create pfile from spfile;
scp listener.ora(修改host),tnsnames.ora,pfile,密碼文件到standby
standby需要修改的地方：
*.fal_client='nbe2dg'
*.fal_server='nbe2'
*.standby_file_management='AUTO'
*.db_unique_name='nbe2dg'
*.log_archive_dest_1='LOCATION=/data/nbe2/arch'
*.log_archive_dest_2='SERVICE=nbe2 OPTIONAL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=nbe2'
还有控制文件路径，及各dump文件路径需要修改.
注意：要在standby配置local_listener,其實是因為port不是默認的1521無法動態注冊
SQL> alter system set local_listener='LISTENER_NBE2'
$vi tnsnames.ora
LISTENER_NBE2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.107.5.54)(PORT = 1526))
為創建standby對主庫5.15主備，并創建standby control file
rman>backup database format='/data/rman/%U_%T.bak';
sql>alter database create standby controlfile as '/data/rmanbak/control01.ctl';
standby執行：
sql>startup nomunt;
sql>alter database mount standby database;
sql>alter database add standby logfile
group 11 ('/data/nbe2/log/dgredo01.log') size 100M;
sql>alter database add standby logfile
group 12 ('/data/nbe2/log/dgredo02.log') size 100M;
sql>alter database add standby logfile
group 13 ('/data/nbe2/log/dgredo03.log') size 100M;
sql>alter database add standby logfile
group 14 ('/data/nbe2/log/dgredo04.log') size 100M;
sql>alter database add standby logfile
group 15 ('/data/nbe2/log/dgredo05.log') size 100M;
采用LGWr进程传输日志，必须建立备用日志，而且日志大小与主库一样，个数比主库多一个
RMAN> restore database;
SQL>alter database recover managed standby database disconnect from session;
新主庫(5.15)開啟：
sql>alter system set log_archive_dest_state_2=enable
sql>alter system switch logfile;
5.數據的導出導入
原庫5.50 windows系統中導出6個schema
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
expdp system/"""system$oracle""" dumpfile=EXPDP_nbe2.dmp logfile=EXPDP_nbe2.log DIRECTORY=DUMP2 SCHEMAS=IMOLD,IMOLD_DEV,IMOLD_PDM,IMOLD_TEMP,RMS,RMS_DEV exclude=statistics
并對存放dmp文件的文件夾開啟共享，在我的電腦-管理-共用資料夾-共用 中可以看見，
在新庫中[root@nbe2 home]# smbclient //10.107.5.50/EXP -Uadministrator
Password:
Domain=[IMOLD-DB01] S=[Windows Server 2003 R2 3790 Service Pack 2] Server=[Windows Server 2003 R2 5.2]
smb: \> lcd /data/
expdp/ lost+found/ nbe2/ rman/ run/
smb: \> lcd /data/expdp/
smb: \> dir
. D 0 Wed Dec 7 11:33:07 2011
.. D 0 Wed Dec 7 11:33:07 2011
EXPDP_NBE2.DMP A 1647906816 Wed Dec 7 11:35:00 2011
EXPDP_nbe2.log A 23329 Wed Dec 7 11:35:00 2011
EXPDP_nbe2_.log A 23374 Wed Dec 7 11:26:11 2011
EXPDP_NBE2_01.DMP A 1227231232 Wed Dec 7 11:26:11 2011
EXPDP_NBE2_02.DMP A 420679680 Wed Dec 7 11:26:11 2011
49175 blocks of size 4194304. 31181 blocks available
smb: \> get EXPDP_NBE2.DMP
getting file \EXPDP_NBE2.DMP of size 1647906816 as EXPDP_NBE2.DMP (35998.7 kb/s) (average 35998.7 kb/s)
也可以嘗試
挂载windows文件共享:
mount -t smbfs -o username=admin,password=888888 //192.168.1.2/c$ /mnt/samba
mount -t cifs -o username=xxx,password=xxx //IP/sharename /mnt/dirname
新主庫(5.15)導入數據
nbe2<*nbe2*/home/oracle>$export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
nbe2<*nbe2*/home/oracle>$impdp 'system/"sys$oracle"' dumpfile=EXPDP_NBE2.DMP logfile=impdp_nbe2.log DIRECTORY=DUMP2
注意PUBLIC同義詞的創建
select 'CREATE PUBLIC SYNONYM '||synonym_name||' FOR '||table_owner||'.'||table_name||';' from dba_synonyms
where table_owner in('IMOLD','IMOLD_DEV','IMOLD_PDM','IMOLD_TEMP','RMS','RMS_DEV') and OWNER='PUBLIC';
注意JOB的建立是否到指定schema下
收集統計信息
exec DBMS_STATS.GATHER_DICTIONARY_STATS;
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'RMS',estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',degree=>4,cascade=>TRUE);
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'IMOLD',estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',degree=>4,cascade=>TRUE);
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
6.收尾工作
建立軟連接
nbe2dg<*nbe2*/home/oracle>$ln -s /u01/product/admin/nbe2/bdump/ bdump
7.建立新的standby
主庫需要修改的參數：
log_archive_config='dg_config=(nbe2,nbe2dg,nbe2dgnew)'
log_archive_dest_3='SERVICE=nbe2dgnew OPTIONAL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=nbe2dgnew'
log_archive_dest_state_3=ENABLE
fal_server='nbe2dg,nbe2dgnew'
修改tns文件：
nbe2dgnew=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.107.5.15)
(PORT=1526)
)
(CONNECT_DATA=
(SERVICE_NAME=nbe2dgnew)
)
)
新備庫建立和上文一樣
