分类目录归档:Oracle

Oracle SQL*Plus Variable Setting

在Oracle数据库的日常管理维护中,对于DBA而言,无论是通过CRT/Putty等远程连接工具还是直接登录服务器方式,对数据库所做的很多操作都是在SQL*Plus下进行的,因此设置SQL*Plus的运行环境有助于美化输出结果,提高SQL代码的编写效率!

要设置SQL*Plus的运行环境,我们可以在当前session中执行某一条或几条命令,但是如果每次都需要在当前session中执行这些命令去设置SQL*Plus运行环境则无疑很繁琐,因此可以把最常用SQL*Plus环境设置写在$ORACLE_HOME/sqlplus/admin/glogin.sql文件中,这样每次使用SQL*Plus对数据库进行操就可以避免重复输入设置SQL*Plus运行环境的命令。

关于SQL*Plus环境的基本设置命令如下:

$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
define _editor=vi
set serveroutput on size 1000000
set trimspool on 
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '
set termout on 

简要解释:
define _editor=vi:设置SQL*Plus使用的默认编辑器;
set serveroutput on size unlimited:默认打开DBMS_OUTPUT同时将默认缓冲区大小设置尽可能大;
set trimspool on:假脱机输出文本时,会除去文本行两端的空格而且行宽不定;
set long 5000:设置选择LONG和CLOB列时显示的默认字节数;
set linesize 100:设置SQL*Plus显示的文本行宽度;
set pagesize 9999:控制SQL*Plus多少行记录时打印一次标题;
column plan_plus_exp format a80:设置AUTOTRACE得到的解释计划输出(explain plan output)的默认宽度;

define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '

以上这部分用于设置SQL*Plus提示符,显示效果如下:

sys@10G> 

从上提示符则很清晰知道以哪个用户登录哪个数据库!

PS: This article summarized in 《Oracle Database 9i/10g/11g编程艺术-深入数据库体系结构(第2版)》

Clear up failed 10g or 11.1 Oracle Clusterware(CRS)Installation

这篇文章[239998.1]可以帮助DBA分析和理解如何将安装失败的10g和11.1版本的CRS成功卸载,该篇文章适用于清除安装失败的10.1.0.2 to 11.1.0.7版本的CRS,对于清除安装失败11.2版本的CRS请参考文档[942166.1 How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation]

Not cleaning up a failed CRS install can cause problems like node reboots. Follow these steps to clean up a failed CRS install:
1. Run the rootdelete.sh script then the rootdeinstall.sh script from the $ORA_CRS_HOME/install directory on any nodes you are removing CRS from. Running these scripts should be sufficent to clean up your CRS install. Rootdelete.sh accepts options like nosharedvar/sharedvar, and nosharedhome/sharedhome. Make yourself familiar with these options by reading the Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide. If you have any problems with these scripts please open a service request.

If for some reason you have to manually remove the install due to problems with the scripts, continue to step 2:
2. Stop the Nodeapps on all nodes:
srvctl stop nodeapps -n
3. Prevent CRS from starting when the node boots. To do this issue the following as root:
Sun:

rm /etc/init.d/init.cssd 
rm /etc/init.d/init.crs 
rm /etc/init.d/init.crsd 
rm /etc/init.d/init.evmd 
rm /etc/rc3.d/K96init.crs 
rm /etc/rc3.d/S96init.crs 
rm -Rf /var/opt/oracle/scls_scr 
rm -Rf /var/opt/oracle/oprocd 
rm /etc/inittab.crs 
cp /etc/inittab.orig /etc/inittab 

Linux:

rm /etc/oracle/* 
rm -f /etc/init.d/init.cssd 
rm -f /etc/init.d/init.crs 
rm -f /etc/init.d/init.crsd 
rm -f /etc/init.d/init.evmd 
rm -f /etc/rc2.d/K96init.crs 
rm -f /etc/rc2.d/S96init.crs 
rm -f /etc/rc3.d/K96init.crs 
rm -f /etc/rc3.d/S96init.crs 
rm -f /etc/rc5.d/K96init.crs 
rm -f /etc/rc5.d/S96init.crs 
rm -Rf /etc/oracle/scls_scr 
rm -f /etc/inittab.crs 
cp /etc/inittab.orig /etc/inittab

HP-UX:

rm /sbin/init.d/init.cssd 
rm /sbin/init.d/init.crs 
rm /sbin/init.d/init.crsd 
rm /sbin/init.d/init.evmd 
rm /sbin/rc2.d/K960init.crs 
rm /sbin/rc2.d/K001init.crs 
rm /sbin/rc3.d/K960init.crs 
rm /sbin/rc3.d/S960init.crs 
rm -Rf /var/opt/oracle/scls_scr 
rm -Rf /var/opt/oracle/oprocd 
rm /etc/inittab.crs 
cp /etc/inittab.orig /etc/inittab

HP Tru64:

rm /sbin/init.d/init.cssd 
rm /sbin/init.d/init.crs 
rm /sbin/init.d/init.crsd 
rm /sbin/init.d/init.evmd 
rm /sbin/rc3.d/K96init.crs 
rm /sbin/rc3.d/S96init.crs 
rm -Rf /var/opt/oracle/scls_scr 
rm -Rf /var/opt/oracle/oprocd 
rm /etc/inittab.crs 
cp /etc/inittab.orig /etc/inittab 

IBM AIX:

rm /etc/init.crs 
rm /etc/init.crsd 
rm /etc/init.evmd 
rm /etc/rc.d/rc2.d/K96init.crs 
rm /etc/rc.d/rc2.d/S96init.crs 
rm -Rf /etc/oracle/scls_scr 
rm -Rf /etc/oracle/oprocd 
rm /etc/inittab.crs 
cp /etc/inittab.orig /etc/inittab

4. If they are not already down, kill off EVM, CRS, and CSS processes or reboot the node:

ps -ef | grep crs
kill -9 procid
ps -ef | grep evm
kill -9 procid
ps -ef | grep css
kill -9 procid

Do not kill any OS processes, for example icssvr_daemon process !
5. If there is no other Oracle software running (like listeners, DB’s, etc…), you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:

rm -f /var/tmp/.oracle/* 或者 rm -f /tmp/.oracle/*

6. Remove the ocr.loc, usually the ocr.loc can be found at /etc/oracle
7. De-install the CRS home in the Oracle Universal Installer
8. Remove the CRS install location.
9. Clean out the OCR and Voting Files with dd commands. Example:

dd if=/dev/zero of=/dev/rdsk/V1064_vote_01_20m.dbf bs=1M count=256
dd if=/dev/zero of=/dev/rdsk/ocrV1064_100m.ora bs=1M count=256

See the Clusterware Installation Guide for sizing requirements…
If you placed the OCR and voting disk on a shared filesystem, remove them.
If you are removing the RDBMS installation, also clean out any ASM disks if they have already been used.
10. The /tmp/CVU* dir should be cleaned also to avoid the cluvfy misreporting.
11. It is good practice to reboot the node before starting the next install.
12. If you would like to re-install CRS, follow the steps in the Oracle Clusterware Installation manual.

PS: This article comes from MOS…