ITPub博客

unix 服务器上可能有10多个数据库，可以用脚本批量启动，停止 ： start_all_dbs.sh， shutdown_all_dbs.sh.[@more@]*****************************************************************************************start_all_dbs.sh*****************************************************************************************#!/bin/kshORATAB=/var/opt/oracle/oratab; export ORATABTEMP_DIR=/tmp ; export TEMP_DIRfunction is_db_up{rm $TEMP_DIR/delme.logsqlplus "/ as sysdba"<< EOF > $TEMP_DIR/delme.log

http://www.oracle-wiki.net/startdocsdefaultschemasACKGROUND When an 11g database is created without tweaking any of the options, using either dbca or the installer, the schema listed in the table below, 36 of them(!), are created by default. This document gives an overview of their purpose and function and, should the functionailty not be required, whether they can be safely deleted* or not without compromising the fundamental operation of the database. Additionally, having been removed,

前面我们用了rman 里的duplicate方法去在本机clone个db, 这里我们用CREATE CONTROLFILE 方法在另一台服务器上Clone. 缺点是用这个方法clone， 需要shutdown 原数据库，传输datafile到另台服务器。1- tar $ORACLE_HOME for 10.2.0 and 11.1.0 on ora6tar –cvf 2- scp them to srv7 (if the file is too large, may face File is too large issue. use ulimit –a to see the limitation). scp jxiaos@EAP-SRV6:/u01/app/oracle/product/ora11g.tar /u01scp jxiaos@EAP-SRV6:/u04/oradata/OLD_DB/*.* /u04/oradata/CLONEDDB/ [@more@]

echo "/path/scriptname.sh"|at now[@more@]echo "/path/scriptname.sh"|at now

使用for 循环，sed给文件批量改名。[@more@]FILES=/u04/oradata/BBBC1DF7/*for f in $FILESdo echo "***************************" echo "Processing $f file..." # take action on each file. $f store current file name newfile=`echo "${f}" | sed "s/BBBC1DF6/BBBC1DF7/g"` echo "old file is: " $f echo "new file is: " $newfile cd /u04/oradata/BBBC1DF7/ mv $f $newfiledone

如果scp的文件很大，想让scp在后台运行。通常我们要建立ssh 信任关系，不用输入密码以让scp在后台运行，但可能没条件建立public key，两台机器无需密码，比如root身份。用以下方法可以让不需要建立ssh信任，而让scp在后台运行。[@more@] Normal 0 false false false MicrosoftInternetExplorer4 ******************************************************************** 1. scp jxiaos@HOSTNAME:/u04/oradata/ORACLE_SID1/*.* /u04/oradata/ORACLE_SID2

In AIX, use below command to run installer, but always says jre issue. ./runInstaller -clone -silent -jreloc /usr/java5_64/jre -ignorePreReq ORACLE_HOME="/u01/app/oracle/product/11.1.0" ORACLE_HOME_NAME="OraDb11g_home1" ORACLE_BASE="/u01/app/oracle"[@more@] Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions

bash-4.2$ DATE=`date +%Y-%m-%d`bash-4.2$ echo $DATE2013-04-11[@more@]*****************************************bash-4.2$ DATE=`date +%Y-%m-%d`bash-4.2$ echo $DATE2013-04-11

First, please remember that the most difficult part is not this installation, but the preparation for this installation, like checking the hardware requirements, software requirements, and tunning the kernel parameters.Second, Oracle11g Release2 adopts different response file than Oracle10g and Oracle11g Release1.Third, the silent installation is easy, just a piece of cake! :)[@more@] You can install the Oracle Database software with/without response file in silent mode.----Without response

How to clone a db on the same server:1. First to determine the existing db's datafiles, controlfiles location. And create these folders. SQL> select DESTINATION from V$ARCHIVE_DEST;SQL> select * from V$CONTROLFILE;SQL> select MEMBER from V$LOGFILE;SQL> select name from V$TEMPFILE;[@more@]2. create these dump destination. mkdir adumpmkdir bdumpmkdir cdumpmkdir createmkdir dpdumpmkdir pflemkdir udump3. create password file.orapwd file=orapwmydb1 password=oracle entries=20;4. copy in

1. first create directories for oradata and core_dump_dest etc, set ORACLE_HOME, ORACLE_SID in .profle and create init.ora file in $ORACLE_HOME/dbsdb_name='mydatabase'memory_target=1Gprocesses = 150control_files='/ora/app/oracle/product/11.2.0/dbs/CONTROL01.CTL','/ora/data/mydatabase/CONTROL02.CTL'audit_file_dest=/ora/app/oracle/admin/mydatabase/adumpaudit_trail ='db'core_dump_dest=/ora/app/oracle/admin/mydatabase/cdump [@more@]#don't specify below 2 parameters, otherwise oracle says ORA-32

https://www.ibm.com/developerworks/mydeveloperworks/blogs/paixperiences/entry/remotex11aix?lang=enForward GUI installer to Windows from Unix. First download Cygwin or X Manager, and enable X11 fowwarding as below in PuTTY. Start X server. [@more@] v:* {behavior:url(#default#VML);} o:* {behavior:url(#default#VML);} w:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Forward GUI installer to Windows from Unix. First download Cygwin or X Manager,

Audit CREATE PROFILE Audit CREATE SESSION ...[@more@]查看哪些privs设置audit了。SQL> select * from DBA_PRIV_AUDIT_OPTS; USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- CREATE EXTERNAL JOB BY ACCESS BY ACCESS

shell 脚本读多个oracle_sid， 并连接数据库的脚本[@more@]#! /bin/kshset -xexport oratab_file=/var/opt/oracle/oratabexport ORACLE_HOME=/u01/app/oracle/product/11.1.0export PATH=$ORACLE_HOME/bin:$PATHexport SHL_DIR=/u01/app/oracle/dba/ora_shellsawk -F: '/^[^#].*:.*:*/ {print $1}' ${oratab_file} | while read sid_line do echo $ORACLE_SID export ORACLE_SID=${sid_line} sqlplus /nolog > ca.log << EOF connect / as sysdba insert into system.db_growth (select own

resetlogshttp://blog.csdn.net/tianlesoftware/article/details/7753546[@more@]一．创建控制文件时：Resetlogs和Noresetlogs当我们将控制文件备份到trace 文件时，可以看到里面包含了2部分的重建语句，一个是使用resetlogs，另一个是使用noresetlogs。 备份控制文件的SQL 如下：SQL>alterdatabase backup controlfile to trace 有关控制文件的详细说明，参考：Oracle 控制文件http://blog.csdn.net/tianlesoftware/article/details/4974440 Set #1. NORESETLOGS caseThe followingcommands will create a new control file and use

here are some examples on exp/imp:[@more@]1. after drop the user, use below to create first. create user cog84cs identified by welcome1 DEFAULT TABLESPACE COGNOS_APP TEMPORARY TABLESPACE TEMP;GRANT UNLIMITED TABLESPACE TO cog84cs;2. and get the privs info: select * from DBA_SYS_PRIVS where grantee in ('COG84CS','CG84ADTD');select * from DBA_TAB_PRIVS where grantee in ('COG84CS','CG84ADTD');select * from DBA_ROLE_PRIVS where grantee in ('COG84CS','CG84ADTD');nohup imp "/ as sysdba" fil

http://www.cnblogs.com/coohoo/archive/2011/01/16/1936888.html 通向架构师之路 --Victor Guan Oracle Flashback技术总结 Flashback 技术是以Undo segment中的内容为基础的， 因此受限于UNDO_RETENTON参数。要使用flashback 的特性，必须启用自动撤销管理表空间。 在Oracle 10g中， Flash back家族分为以下成员： Flashback Database， Flashback Drop，Flashback Query(分Flashback Query,Flashback Version Query， Flashback Transaction Query 三种) 和Flashback Table。[@more@]

http://hi.baidu.com/edeed/item/e6ba1fddb94bb0f793a97486PLSQL中显示Cursor、隐示Cursor、动态Ref Cursor区别 今天简单的总结一下PL/SQL中cursor(光标/游标)的用法。[@more@]相信不少做开发或维护的DBA在找工作的时候，遇到过类似的面视问题：请简单的描述一下光标的类型，说一下普通光标和REF光标之间的区别，以及什么时候该正确应用哪一个?　这 个题目，我着实难住了不少人，其实他们在具体开发的时候，也还是比较能够把握正确的光标的使用的，但就是说不出来，当然了，这与大家自身的沟流交通能力是 有关系的。有的人不善于说，

1. 外键的列没加索引，当对主表键进行DML, 会锁住整个子表。...........................[@more@] Normal 0 false false false MicrosoftInternetExplorer4 1. 外键的列没加索引，当对主表键进行DML, 会锁住整个子表。 2. 索引失效，如where lower(name)=’ssss’, name上的索引会失效。 或者隐式的数据类型转换：where item_id=12345, 这里item_id是varchar2, 就会先隐式的转换成数字型，索引失效了。 3. PLSQL里的程序给临时表转载了很多数

Expert Oracle Database Architecture 死锁的头号原因：不加索引的外键(第6章.锁)[@more@]http://www.cnblogs.com/angzi/archive/2006/12/09/587612.html据Tom的经验，导致死锁的头号原因是外键未加索引，第二号原因是表上的位图索引遭到并发更新。在以下两种情况下，Oracle在修改父表后会对子表加一个全表锁： ·如果更新了父表的主键(倘若遵循关系数据库的原则，即主键应当是不可变的，这种情况就很少见。)由于外键上没有索引，所以子表会被锁住。 ·如果删除了父表中的一行，整个子表也会被锁住(由于外键上没有索引，比如：如果EM