study

Exp/imp

9.2.0.7上

exp 'system/"system@dbsec"' tables=SFISM4.R_WIP_TRACKING_T direct=y INDEXES=n recordlength=65535  file=wip_track.bak log=wip_track.log

 

帶query的：

exp 'system/"cpdba"' tables=gc.emp  INDEXES=n  file=gcemp.bak query=\"where emp_no=\'harddisks\'\"

exp 'system/"system@dbsec"' file=lgbt.dmp log=lgbt.log tables=logistics_dt8.lg_attachment_for_endcase query=\"where modified_date\>to_date\(\'20101231010101\',\'YYYYMMDDHH24MISS\'\)\"

 

windown中”””的格式

exp system/"""system@dbsec"""@chn_csd wner=Portal file=portalexp.dmp log=portalexp.log

 

用imp只導入數據不導標結構的方法

ignore=y

 比如：imp system file=..... fromuser=xx touser=xx

 

imp 'system/"cpdba"' tables=EMP  file=gcemp.bak fromuser=gc touser=gc ignore=y

 

 

還有主要數據庫字符集和操作系統的字符集

導入和導出庫的字符集要一致

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8  --通用

export NLS_LANG=TRADITIONAL CHINESE_TAIWAN.ZHT16BIG5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

linux開機自啟動

[root@fubaorma etc]# more /etc/rc.local

#!/bin/sh

#

# This script. will be executed *after* all the other init scripts.

# You can put your own initialization stuff in here if you don't

# want to do the full Sys V style. init stuff.

 

touch /var/lock/subsys/local

route add -net 10.0.0.0 netmask 255.0.0.0 gw 10.186.14.1 dev eth0

 

#set oracle environment

 

ORACLE_HOME=/u01/product/oracle;export ORACLE_HOME

ORACLE_SID=esvcabd2;export ORACLE_SID

 

su oracle -c "/u01/product/oracle/bin/sqlplus /nolog <

connect / as sysdba

startup;

exit

EOF"

 

su oracle -c "/u01/product/oracle/bin/lsnrctl <

start

exit

EOF"

/usr/local/rma/rma -d /usr/local/rma/rma.ini

 

 

 

 

 

 

11.2.0.1導入9.2.0.7(已有表結構)DB

Oracle9i沒有impdp工具，只能使用exp/imp,但使用exp從11g導入9i時會有報錯。所以利用中轉DB(10.2.0.1)先用expdp導出數據再impdp導入中轉DB

 

<1>11.2.0.1expdp導出

ccpttestdb<*ccptdb*/data/backup>$expdp system/system#test schemas=channel_test parallel=6 dumpfile=wmexptest_%U.dmp logfile=wmexpdp.log version=10.2.0.4.0 directory=DUMPEXP INCLUDE=table

 

<2>10.2.0.1導入中轉

CREATE USER CHANNEL_TEST IDENTIFIED BY CHANNEL_TEST

  DEFAULT TABLESPACE users

  TEMPORARY TABLESPACE TEMP;

ALTER USER CHANNEL_TEST QUOTA UNLIMITED ON USERS;

 

C:\Documents and Settings\F3677753>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

C:\Documents and Settings\F3677753>impdp system/oracle  dumpfile=wmexptest_%U.dmp logfile=wmexpdp.log directory=DUMPEXP Transform=segment_attributes:n:table

 

<3>在9.2.0.7上用exp將10.2.0.1導出

channelqc-db$exp system/oracle@MYDBWIN wner=CHANNEL_TEST file=testexp.dmp log=testexp.log

 

<4>在9.2.0.7導入

channelqc-db$imp 'system/"system@dbsec"' fromuser=CHANNEL_TEST touser=CHANNEL_TEST ignore=y  file=testexp.dmp log=testimp.log

 

 

 

 

 

 

 

 

 

10.2.0.4(ams)導入所有使用11.2.0.2(evection) DB

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

 

1.在10.2.0.4上導出根據schema導出

ams<ams*/data/backups>$expdp  'system/"system@dbsec"' parallel=2 dumpfile=ams_%U.dmp logfile=TESTTBS.log DIRECTORY=backups SCHEMAS=ams,ams_ap,enams,enams_ap

exclude=statistics

 

2.導入數據導11.2.0.2,

先創建4個schema(不創建的時候應該也可以)，創建新的tablespaces,但為了使原庫的tablespace導入指定的tablespace中需要設置參數：REMAP_TABLESPACE

，還要注意最好把統計信息關掉。

evection<*evection*/data/backups>$impdp 'system/"sys@dbsec"' parallel=2 dumpfile=ams_%U.dmp logfile=AMSTBS.log DIRECTORY=backups REMAP_TABLESPACE=AMS_SINDX:EVECTION_SINDX,AMS_SDATA:EVECTION_SDATA,AMS_LDATA:EVECTION_LDATA,AMS_LINDX:EVECTION_LINDX Transform=segment_attributes:n:table

exclude=statistics

 

注意：根據SCHEMA到數據的時候要小心建立公共同義詞，公共同義詞是在PUBLIC用戶下

select * from dba_objects where owner  in('PUBLIC');

 

select 'CREATE PUBLIC SYNONYM '||synonym_name||' FOR '||table_owner||'.'||table_name||';' from dba_synonyms

where table_owner in('DFMS','MWEB','MES');

 

 

 

 

 

 

 

 

 

 

dirty data:

 

對32位系統sga+pga<=1.7G

 

shared pool過大

在shared pool中解析，請求free空間分配、分割，產生更多、更細碎片chunk

select count(*) from x$ksmsp;--內存碎片數(chunk數)

 

shared pool內存碎片(chunk)過多

ORA-04031：當分配大塊的連續的內存時不足時出現，很多時候是因為內存碎片過多，而并非內存不足造成。oracle首先會清除shared pool中當前沒有使用的對象，使空間合并，如果仍然沒有足夠大的單塊內存可以滿足需要，就會發生04031

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

VNC

　　1. 启动 VNC Server：

 

　　打开终端执行：vncserver

 

　　第一次运行 vncserver，将会要求输入两次密码，会看到如下提示信息：

 

　　New 'localhost.localdomain:1 (root)' desktop is localhost.localdomain:1

 

　　Starting applications specified in /root/.vnc/xstartup

 

　　Log file is /root/.vnc/localhost.localdomain:1.log

 

　　上边信息中的“:1”表示启动的第一个vnc桌面。

 

　　2. 停止 vncserver：

 

　　停止 vncserver 的方法是：

 

　　vncserver -kill :1

　　注意“:1”，表示停止第一个vnc桌面

 

　　3. 修改vnc密码：

 

　　修改vnc密码的方法是执行 vncpasswd，输入两次密码即可。

 

　　4. 默认情况下VNC Viewer只能看到 VNC Server的命令行。

 /etc/init.d/vncserver start

vncserver -geometry 800x600 -depth 16t

vi ~/.vnc/xstartup    ---加入圖形頁面

twm& ---> gnome-session &

 

/etc/init.d/vncserver start

vncserver -geometry 800x600 -depth 16t

vi ~/.vnc/xstartup

twm& ---gnome-session &

 

11G STANDBY 安裝

Oracle Enterprise Linux下如何修改/dev/shm大小

/dev/shm就是个临时文件系统，修改/etc/fstab文件即可。ITPUB个人空间Q8l­Q‑V/t:`2QpTV将其中的ITPUB个人空间#k|/G~gL
tmpfs                   /dev/shm                tmpfs   defaults        0 0ITPUB个人空间N2i-H[1]X$q\@
q@.p7i D
改成
5T?
j/S#H(s8Ll_

---

f%d l0tmpfs                   /dev/shm                tmpfs   defaults,size=1024M        0 0
;q/K2Ic,Hd+O

---

^0增大到了1G，然后重新mount即可生效。
*_ L"?8o‑A9ay[1]J6K0mount -o remount /dev/shm

 

[root@PcemultiDBSTY ~]# mount -o remount /dev/shm

mount: /dev/shm not mounted already, or bad option

注：把size設置為>內存的大小 也不會有用

 

 

執行安裝時檢查內核參數不是期望的會報錯

[root@PcemultiDBSTY /]# rpm -qa | grep libaio

libaio-0.3.106-3.2

libaio-0.3.106-3.2

可以備庫看到沒有安裝

主庫：

rman>backup database format='/data/rmanbak/%U_%T.bak';

sql>alter database create standby controlfile as '/data/rmanbak/control01’;

          

備庫sql>startup nomunt

sql>alter database mount standby database;

 

standby拷贝将使用rsync来替代所有的ftp

  A： product   B： standby

  B机上使用oracle执行$ ssh-keygen -t rsa

  会自动在/home/oracle下创建一个.ssh文件夹，里面将会产生两个认证证书

  在B机上执行$ssh A date，然后输入密码，然后就能抓取到A的时间

  $ scp /home/oracle/.ssh/id_rsa.pub oracle@B:/home/oracle/.ssh/authorized_keys

  有可能会报错说A上没有.ssh路径

  那么手动创建.ssh目录

 

  然后在B上再执行$ssh A date，应该不输入密码也能看到时间

  scp那条命令，如果在RAC环境中就不能用

  而是应该用vi打开B的id_isa.pub，然后将那段文字拷贝附加到A上的authorized_keys文件后面

 

 

sync script  :

#!/bin/bash

pid=`/sbin/pidof rsync |wc | awk '{print $1}' `

#echo $pid

if [ "$pid" -gt "1" ]

then

    echo "Rsync is already running...."

else

    echo "Starting rsync `date`"

    rsync -e ssh -av oracle@10.186.1.212:/data/rma/arch/ /data/rma/arch

    echo "Completed rsync `date`"

fi

 

exit 0

job:

5 * * * * sh /data/run/prodarcsync 1>/data/run/log/prodarcsync.log 2>/data/run/log/prodarcsync.bad

 

昆山NBE2數據庫windows切換至linux平臺

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)

    )

  )

 

新備庫建立和上文一樣