ITPub博客

ora9i,LINUXas3 ,DG归档日志管理，看书笔记，测试 ,测试前请先配置SSH密钥[@more@]#first set envORACLE_BASE=/SERVER/ora9;export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/9.2;export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATH;export PATHORACLE_SID=ora9i; export ORACLE_SID#start checkDATE=`date +%Y-%m-%d:%H:%M:%S`filepath=$ORACLE_BASE/oradata/$ORACLE_SID/archive/logpath=$ORACLE_BASE/admin/$ORACLE_SID/bdumpremotefile=`ssh -p 10088 oracle@192.168.18.91 "ls -t /SERVER/ora9/oradata/ora9i/archive/*|head -

ora9i,LINUXas3 ,DG归档日志管理，看书笔记，测试[@more@]ORACLE_BASE=/SERVER/ora9;export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/9.2;export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATH;export PATHORACLE_SID=ora9i; export ORACLE_SID#delete archive log on STANDBY database which has been appliedgrep "Media Recovery Log" $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log| awk '{print "rm -f ",$4}' >$ORACLE_BASE/rmarchlog.shcd $ORACLE_BASE/admin/$ORACLE_SID/bdumpcat alert_

环境：LINUX AS 3＋ORA 9.2.4 ＋DATA GUARDMAX LOGFILES ,RESETLOGS[@more@]经过，已经搭建好了基于归档日记传输的最大性能保护模式的DG，想把它改成基于REDO 日记传输模式。在备库新建STANDBY REDO 的时候报错：SQL> alter database add standby logfile group 4 ('/SERVER/ora9/oradata/ora9i/redo04.log') size 100M;Database altered.SQL> alter database add standby logfile group 5 ('/SERVER/ora9/oradata/ora9i/redo05.log') size 100M;Database altered.SQL> alter database add standby logfile group 6 ('/SERVER/or

报告内容:TOP等待事件,TOP SQL,TOP SQL命令类型,TOP Session内容.[@more@]具体实现方式: SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~DB Id DB Name Inst Num Instance----------- ------------ -------- ------------631770879 ORA10 1 ora10Specify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: text --输入产生的报告文件类型Type Specified: textInstances in this Workload Repository sche

10G新特性[@more@]一,查看源数据库平台信息SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;PLATFORM_NAME ENDIAN_FORMAT---------------------------------------------Linux IA (32-bit) Little二,查看目标机平台信息SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;PLATFORM_NAME ENDIAN_FORMAT-------------

刚在SUSE 9 上装完ORACLE 10G,并DBCA建了数据库,想对数据库作一个全备份.结果 oracle@linux:/> rman target /rman: can't open target[@more@]这是由于调用的是非Oracle的RMAN的缘故：oracle@linux:/> which rman/usr/X11R6/bin/rman在/usr/X11R6/bin/下存在一个名为rman的命令：oracle@linux:/> ./rman -helprman [-f <ASCII|roff|TkMan|Tk|Sections|HTML|XML|MIME|LaTeX|LaTeX2e|RTF|pod>] [-S(ource of man page passed in)] [-F(ormatted man page passed in)] [-r <man reference printf string>] [-l

文件名与目录名重名[@more@]环境:RHEL AS3 测试库12:03:07 SQL> select * from v$version; BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionPL/SQL Release 9.2.0.4.0 - ProductionCORE 9.2.0.3.0 ProductionTNS for Linux: Version 9.2.0.4.0 - ProductionNLSRTL Version 9.2.0.4.0 – Production 问题: 测试库不能正常SHUTDOWNSQL>shutdown immediate;ORA-01116: error in opening database file 15ORA-01110: data file 15: '/SERV

能自动创建过程脚本,能让你直接输入源泉表名和目标表名就能创建以下链接中的全表复制过程.(第三部分) http://steven1981.itpub.net/post/7967/413828[@more@]--表复制过程NEWcreate or replace procedure hyf_create_sql(p_sour_tab in varchar2, p_dst_tab in varchar2) as /* -- 此存过能产生:一个用来从源表复制到目标表的存过; -- 要求在目标库上布置这个代码 ;(即 p_dst_tab必须为本地表) -- 使用: exec hyf_create_sql('sour_table_name[@dblink_name]','dst_table

从代码的简易性,FORALL和bulk collect 的使用,以及分批插入这三方面考虑,这里介绍了三种写法:[@more@]1 使用了BULK COLLECT,没有使用FORALL, 一次性插入,分批COMMIT,这种方法比较适用于10万以下条数据的表; create or replace procedure cp_data2 as type TYPE_EMPLOYEES is table of EMPLOYEES%rowtype; V_EMPLOYEES TYPE_EMPLOYEES; v_table varchar2(30); v_sql varchar2(300);　v_rows number:=5000;begin execute immediate 'alter session set nls_date_format=''yyyy/mm/dd'''; v_table := 'employee_cp'; v_sq

SSH时能连,时不能连[@more@]自从某天对LINUX服务器加上itpables规则后,服务器出现了不规则的SSH连接超时. 不规则是指允许连接的时间是不定的.有时能连入,有时却不能连入.服务器有两个网卡,有时用内网能连入,外网却不行. 而且,当SSH不能连接时,其它服务也不能连入,就好像是网卡停止工作一样. 但在某个时候却又会自动恢复正常.重启服务器,刚开始是能正常连入的,但在一定的时间后却又不行了!查看/var/log/secure /var/log/messages都没有异常的错误.不知哪位高手有碰到过这样的问题... ... 是网络攻击? 还是SSH服务本身问题?

学习积累[@more@]环境:客户端IP:1.2.3.4网关#eth0 外网 220.101.222.46(此地址为防火墙上填的外网地址) 192.168.123.123(此地址为防火墙映射到网关ETH0上的地址)#eth1 内网 192.168.101.67内网数据库#192.168.101.66:1521要实现这些功能,只要在iptables列表运行以下语句:echo 1 > /proc/sys/net/ipv4/ip_forward iptables -t nat -A PREROUTING -i eth0 -p tcp -d 192.168.123.123 --dport 1521 -j DNAT --to 192.168.101.66iptables -A FORWARD -p tcp -d 192.168.101.66 --dport 1521 -j ACCEPTiptables -t nat -A POSTROU

tail -100 /var/log/messages[@more@]在服务器的/var/log/messages文件中,不时收到以下信息,但又不是开机的时候出现的.modprobe: modprobe: Can't locate module eth5modprobe: modprobe: Can't locate module eth6modprobe: modprobe: Can't locate module eth7modprobe: modprobe: Can't locate module eth8modprobe: modprobe: Can't locate module eth9开始的时候不知道,一直翻到GOOGLE第12页,看到这个是由于执行了mii-tool命令后出现的.这个命令大家知道是查看网卡的连接情况.本人猜测系统是将所有的网卡搜索一遍(但不知道为什么没有

看书而得[@more@]SQL> select * from hyf_t1 order by id2 ;ID-------10010110210310410610710810911211311411711814 rows selectedSQL> SQL> select * from (select id+1 begin,2 sum( id) over(order by id rows between 0 preceding and 1 following) - id-1 end3 ,sum( id) over(order by id rows between 0 preceding and 1 following) - id- id-1 bb4 from hyf_t1 ) where bb>05 /BEGIN END BB---------- ---------- ----------105 105 1110 111 2115 116 2这样也行:SQL> select * from (2 select id+1 begin ,3 lea

学习笔记[@more@] cume_dist()和percent_rank()函数 计算一行在该组中的相对位置,但略有不同 select deptno, ename, sal, cume_dist() over(partition by deptno order by sal) aal,percent_rank() over(partition by deptno order by sal) bbL from hyf_tDEPTNOENAMESALAALBBL0I_CDEF1500.2500TRUSTED_LIST$1000.50.3333333333333330AUDIT$1500.750.6666666666666670JOBSEQ200111I_CDEF2510.2501PROPS$1010.50.3333333333333331I_AUDIT1510.750.6666666666666671JOB$20111待续...

学习笔记[@more@]1. first_value()和last_value()select fire_date,deptno,sum(sal),first_value(sum(sal)) over(partition by fire_date order by sum(sal)) fir_sale,first_value(sum(sal)) over(partition by fire_date order by sum(sal) desc) last_salefrom hyf_tgroup by fire_date, deptnoFIRE_DATEDEPTNOSUM(SAL)FIR_SALELAST_SALE2005020505054200502151505420050225250542005023535054200502454505420050311011012502005030250101250200504210210210420050431031021042005044104102104显示每月每部门SAL汇总,及SAL最高和最低的

学习笔记[@more@]1. sum,avg,max,min移动计算select deptno, ename, sal, sum(sal) over(partition by deptno order by sal) dept_sal, sum(sal) over(order by deptno, sal) all_Total, avg(sal) over(partition by deptno order by sal) dept_avg, avg(sal) over(order by deptno, sal) all_avg, min(sal) over(partition by deptno ) min_sal, max(sal) over(partition by deptno order by sal ) max_sal from hyf_tDEPTNOENAMESALDEPT_SALALL_TOTALDEPT_AVGALL_AVGMIN_SALMAX

学习笔记[@more@]1. lag和lead函数select deptno,ename,sal,lag(sal, 2, 0) over(partition by deptno order by sal) pre_2,lag(sal, 1, 0) over(partition by deptno order by sal) pre_1,lead(sal, 2, 0) over(partition by deptno order by sal) later_2,lead(sal, 1, 0) over(partition by deptno order by sal) later_1from hyf_tDEPTNOENAMESALPRE_2PRE_1LATER_2LATER_10I_CDEF150002001000AUDIT$1000503002000JOBSEQ200501003503000I_TYPE23001002004503500KOPM$3502003005004500I_ACTOBJ4503003506005000I2_JIREFRESHSQL$50035045

学习笔记[@more@]--C、CUBEselect deptno, ename, sum(sal) salfrom hyf_twhere deptno <2group by cube(deptno, ename) order by deptnoDEPTNOENAMESAL10AUDIT$15020DBMS_STANDARD65030FLOAT600… … … 190V_$SESSION_EVENT1000200V_$SPPARAMETER85021011300221INTEGER601231I_AUDIT151… …. … 391V$SESSION_EVENT1001401V$SPPARAMETER851411V_$SESSION_OBJECT_CACHE10514211132043AUDIT$15044DBMS_STANDARD65057JOB$20158JOBSEQ20059KOPM$350… … … 78V_$OPTION90079V_$PGA_TARGET_ADVICE_HISTOGRAM70080V_$SESSION_EVENT100081

学习笔记[@more@]Ø e创建基础数据create table hyf_t as select object_name ename,mod(object_id,50) deptno, created fired_date, object_id salfrom all_objects where rownum<=200create index hyf_t on hyf_t(deptno,fired_date,ename)Ø GROUP函数A、GROUPING SETSselect deptno, ename, sum(sal) salfrom hyf_t where deptno <2group by grouping sets((deptno, ename), deptno)order by deptnoDEPTNOENAMESAL0AUDIT$1500DBMS_STANDARD6500FLOAT6000V_$PGA_TARGET_ADVICE_HISTOGRAM7000V_$SESSION_EVENT10000V_

早上发现测试库连不上, 启动时发现UNDO有坏块[@more@]SQL>startup mount;SQL> alter database open;alter database open*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forced查看alert_ora9.logThu Jul 12 09:05:59 2007Errors in file /SERVER/ora9/admin/ora9/udump/ora9_ora_1202.trc:ORA-01578: ORACLE data block corrupted (file # 2, block # 9)ORA-01110: data file 2: '/SERVER/ora9/oradata/ora9/undotbs01.dbf'Thu Jul 12 09:05:59 2007Error 1578 happened during db open, shutting down da