案例:資料庫windows切換至linux平臺

guocun09發表於2012-01-04

1.原平臺:windows 2003(32-bit)+Oracle 10.2.0.4

2.新平臺:Linux 5(64-bit)+Oracle 10.2.0.4

注:機器硬體不變(主備庫均4G記憶體),另外有兩臺臨時使用機器做切換使用

 

1.用臨時機器1建立windowsstandby

PS:和原庫建立一樣的OSOracle,以便撤下原備庫後,當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密碼檔案和引數檔案

注意:windowsORACLE_HOME/database

5>開啟臨時standbynomount;

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建立linuxstandby(先裝資料庫軟體)

[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`'$';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$cpio -idmv < 10201_database_linux_x86_64.cpio

 

啟用圖形VNC

linuxstandby$vncserver

linuxstandby$vi .vnc/xstartup

#twm &  -à  gnome-session &

安裝oracle軟體

linuxstandby$./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(i386x86_64)

libXp-1.0.0-8.1.el5(i386x86_64)

 

[root@linuxstandby ~]#  rpm -Uvh sysstat-7.0.2-1.el5.x86_64.rpm

 

解壓10.2.0.4補丁安裝檔案

linuxstandby$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$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建立資料庫

 

原來32DBSGA1304MPGA800M(實際使用350M左右)

DBSGA1600MPGA800M(DB64位系統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系統中匯出6schema

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$export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

nbe2$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$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)

    )

  )

 

新備庫建立和上文一樣

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25583515/viewspace-714406/,如需轉載,請註明出處,否則將追究法律責任。

相關文章