Oracle DBA遇到频次最高的五十多个问题

         *

Oracle DBA遇到频次最高的五十个问题*

随着数据价值在企业地位越来越高，DBA这一职位更是受到重视和支持，如何帮助各位数据达人维护好数据，也是ITPUB社区一直秉承的理念，本期我们将社区DBA关注最多的50个问题，已经各位大师对问题的解决方式进行了汇总和整理，期待大家更多给与问题和反馈，以下是问题和解决方案。

问题1：如何收集统计信息不影响数据库？

解决方案
大多数情况下，表的统计信息不准导致了优化器对于执行计划的错误计算，因此需要对表的统计信息进行更正，以便让优化器重新选择准确的 执行计划。但是在生产情况下，随意的收集统计信息，则会给数据库带来隐患：

1、对重新收集统计信息的表，对应的一些SQL可能需要重新硬解析生成执行计划。
2、对于重新收集统计信息的表的部分SQL来说，可能会出现收集完统计信息了，但是执行计划更差的情况。
3、在业务高峰期收集统计信息，会需要额外的资源开销，影响数据库的性能。在进行SQL优化时，通过查看执行计划，表的统计信息以及表的具体情况，去分析是否是由于统计信息不准导致执行计划有
问题，当确定了是统计信息 的问题时，不能盲目的去收集统计信息，需要进一步验证“重新收集统计信息可以提升SQL性能”。
因此在针对“重新收集统计信息可以提升SQL性能”时， 主要介绍一下如何去重新收集统计信息而不影响数据库中正在运行的SQL。
在Oracle中，统计信息的收集，都是存储在对应的数据字典里，因此正常收集完统计信息， 就会被对应的SQL去用来生成执行计划。 但是，Oracle也提供了一种收集完统计信息却不会被记录在数据字典里，因此也不会被对应的SQL使用， 只有在需要使用这些统计信息的时候， 通过设置一些参数，才可以正常的使用这些统计信息。

文章详情链接：http://blog.itpub.net/31536355/viewspace-2691584/

问题2：Oracle安装完成后的初始口令?

解决方案

internal/oracle
sys/change_on_install
system/manager
sysman/oem_temp

问题3：怎样计算一个表占用的空间的大小？

解决方案

select owner,table_name,
NUM_ROWS,BLOCKS*AAA/1024/1024 "Size M",EMPTY_BLOCKS,LAST_ANALYZED from dba_tables
where table_name='XXX';

Here: AAA is the value of db_block_size ;
XXX is the table name you want to check

问题4：如何查看最大会话数？

解决方案

SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%%%%';

　　SQL> show parameter processes
　　NAME TYPE VALUE
　　------------------------------------ ------- -----------------　aq_tm_processes integer 1

　　db_writer_processes integer 1
　　job_queue_processes integer 4
　　log_archive_max_processes integer 1
　　processes integer 200

这里的200是指200个用户。

select * from v$license;
　　其中sessions_highwater纪录曾经到达的最大会话数

问题5：通过PL/SQL连接数据库，出现错误ORA-12154:TNS：无法解析指定的连接标识符

解决方案
1、使用sqlplus登陆。是否能登陆上，若能登陆上，说明tns和监听文件没有问题，那就是路径的问题了，检查PATH和TNS_ADMIN，是否正确，若不正确，修改正确，再查看pl/sql加载，工具-选项-OCI库里面的路径是否正确。
2、使用sqlplus不能登陆，检查服务是否开启，然后再检查tns和监听里面的host是否正确。

问题6：PL/SQL查询出来中文乱码

解决方案

PL/SQL查询出来中文乱码，一把是需要修改PL/SQL Developer 编码格式：在windows中创 建一个名为“NLS_LANG”的系统环境变量，设置其值为“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”，然后重新启动PL/SQL Developer，这样检索出来的中文内容就不会是乱码了。如果想转换为UTF8字符集，可以赋予“NLS_LANG”为 “AMERICAN_AMERICA.UTF8”，然后重新启动 pl/sql developer。

问题7：GoldenGate目标端检查发现错误ogg-01296

解决方案

view report RORA_001找到错误ogg-01296对应的map表名,可以使用如下方式快速检查:

Tail -3000 RORA_001.rpt >> /tmp/ RORA_001.rpt
Cat  /tmp/ RORA_001.rpt |grep -i OGG-  会出现ogg-01296错误

edit report RORA_001在对应的map语句加–注释掉，启动复制进程，一般没有问题,如果修改的表比较多，可以使用脚本检查

问题8：用什么语句查询字段

解决方案

desc table_name 可以查询表的结构
select field_name,... from ... 可以查询字段的值
select * from all_tables where table_name like '%%%%'
select * from all_tab_columns where table_name='??'

问题9：如何在Oracle服务器上通过SQLPLUS查看本机IP地址 解决方案

select sys_context('userenv','ip_address') from dual;
如果是登陆本机数据库，只能返回127.0.0.1

问题10：如何查看各个表空间占用磁盘情况

解决方案

SQL> col tablespace format a20 
SQL> select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes 字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余空间, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id

问题11：怎样查看哪些用户拥有SYSDBA、SYSOPER权限？ 解决方案

SQL>conn sys/change_on_install
SQL>select * from V_$PWFILE_USERS;

问题12：如何查看现有回滚段及其状态

解决方案

SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS

问题13：如何改变一个字段初始定义的Check范围

解决方案

SQL> alter table xxx drop constraint constraint_name;
之后再创建新约束:
SQL> alter table xxx add constraint constraint_name check();

问题14：Oracle常用系统文件有哪些

解决方案

通过以下视图显示这些文件信息：
v$database,v$datafile,v$logfile v$controlfile v$parameter;

问题15：表和索引收集信息如何立马生效：

请问大家有什么办法让手工进行表和索引收集的信息立马生效，让正在执行的SQL根据最新的统计信息 走正确的执行计划？

解决方案
已经在执行SQL的没法使用刚采集到的统计信息，只能等下一次执行，用DBMS_stats包采集时，添加参数 NO_INVALIDATE=>false 即可

问题16：如何测硬盘IO

一般如何粗略查看硬盘IO的，有个存储盘，这样测试：
dd if=/dev/zero of=/dev/sdxx bs=8k count=250000 oflag=direct
返回用时200多秒，速度还不到 10 MB/s,但bs换成2M,速度可以达到700 MB/s，按这个算，iops应该是1000左右吧，考虑数据库的块大小是8k，一开始就用8k去测,这样测合理么，这种iops做生产库适合么

解决方案
一般I/O类型是混合型的。
你这个存储是多个磁盘组成的吗、raid5 还是别的。样看不出来。
运行oracle程序，一般可以考虑使用orion测试

问题17：不使用索引消除排序为什么性能更好

解决方案

这种属于稀疏结果的场景，不走这个避免排序的索引会更好些。
同样是扫描全表数据，一般FTS比FS更快。
fts=full table scan;
fs=full index scan;
虽然fts免不了order操作，但没结果或结果很少的话，这不会成为性能问题，显然，这个案例系统生成计划跑偏了。

问题18：ORA–00604怎么解决

节点Alert日志爆出如下错误
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20099: Loginclosed,please connect to dba
ORA-06512:在 line

两个问题：
1、什么是递归SQL级别；
2，怎么消除这种错误，怎么排查？

解决方案
ORA-20099: Loginclosed,please connect to dba
这明显是人为在触发器里定义的错误，先看看触发器脚本怎么写！

问题19：如何做到让scanip开始不自动启动

解决方案
$GI_HOME/bin/srvctl disable scan -i 1 $GI_HOME/bin/srvctl disable scan -i 2 $GI_HOME/bin/srvctl disable scan -i 3

问题20：12C中能否单独给PDB做DG

解决方案
可以针对某几个PDB做dataguard，有一个参数 enabled_pdbs_on_standby 来控制.

问题21：DataGuard是否需要配置静态监听

解决方案
不是一定需要配,但是最好配
duplicate 方式建立standby，如果主库执行需要静态监听
broker管理，低版本需要静态监听

问题22：Oracle在生产环境如何划分表空间

解决方案

1.索引与数据分开没有必要.
2.你可以根据业务来分.比如:财务1个表空间 销售另外1个表空间.
3.另外一些大表也可以单独分配一个表空间.
4.基础表,业务字典 一个表空间等等.
5.合理的是使用分区技术.

问题23：正式环境asm要加盘，需要重启udev服务，需要申请停机吗

解决方案

这个不需要重启，找到新添加的盘，添加到ASM就可以。

问题24：OGG使用add extract exta, SourceisTable初始化后，如何接着做增量同步

解决方案

如果源端是Oracle init的时候可以指定SCN，table user.*, SQLPREDICATE 'AS OF SCN 1909670000'，初始化之后再从这个SCN开始应用队列文件.

问题25：如何知道开发前台使用update语句更新的值

解决方案
SQL TRACE 现在可能叫做 SQL monitor 或者直接10046event， 追踪 Session 。事后分析，可以用logminer挖redo，也可以针对特定SQL类型和表启用审计。SQL TRACE 现在可能叫做 SQL monitor 或者直接10046event， 追踪 Session 。

问题26：物化视图可以基于scn开始同步么

解决方案

1、通过物化视图进行增量同步，平时有用，效果不错；
2、自带的初始化方式效率，这个看你自己的环境，这个不好说，关键看网络；
3、物化视图增量刷新一般结合主键或者rowid实现的，scn 估计不太好弄.

问题27：表空间的文件设置的自动扩大，文件32G无法自动新增数据文件

解决方案

可以给表空间多加几个数据文件，让这几个数据文件都自动增长。

问题28：关于多节点RAC中VIP漂移的疑问

解决方案

VIP漂移的规律对于连接数据库，没有关系。节点故障，导致VIP漂移后，你连接哪个节点和VIP漂移到哪个节点没有任何关系

问题29：关于B树索引的问题

解决方案

1、B*Tree的顶层节点称为root节点，即根节点；
2、B*Tree的层数最大不是3，而是看实际需要，root节点在索引变大时会发生split，有时不止一次；
3、B*Tree的leaf节点间有双向指针，而branch节点间没有

问题30：控制文件丢失如何恢复

解决方案

只是控制文件坏了,不代表数据丢失,直接重建就可以了.
使用”recover database using backup controlfile until cancel;“进行恢复数据库，告诉Oracle把数据库恢复到数据文件头记录的最大SCN，同时也会把控制文件恢复到最新，最后用resetlogs打开数据库。

问题31：Oracle的自动收集计划是什么量级的收集

解决方案

收集是全库范围内的，但并不是读取库中所有的数据，也不是对所有修改过的表都收集，是对修改数据量达到某个阈值的表进行收集，而且也不都是全表读，有时是按照一定的比例收集。

问题32：expdp怎么导出分区表的某些分区

解决方案

–Excluding Table Partitions’ Data
EXCLUDE=TABLE_DATA:”IN (select partition_name from dba_tab_partitions where table_name in (‘TABLE_NAME1′,’TABLE_NAME2′…) and partition_name like ‘%%%%_P%%%%’ and substr(partition_name,-4,4) < to_char(sysdate,’YYYY’) and partition_name not like ‘%%%%P2013%%%%’)” –P20XX

–Excluding Table

EXCLUDE=TABLE:”IN (‘TABLE_NAME1′,’TABLE_NAME2′…)”

问题33：impdp remap_datafile和 remap_tablespace区别

解决方案

remap_*的作用不是帮你创建表空间和数据文件，而是在源和目标库之间可以改变表存储的数据文件和表空间，比如：表在源库中存储在表空间tbs1上，而目标库中没tbs1，只有tbs2，那么可以用remap_tablespace将表的存储表空间由tbs1改为目标库中的tbs2。

问题34：怎么知道DB用的是CBO还是RBO
解决方案

一般没有修改的话，默认优化模式是choose，如果有分析过表则会走CBO，否则RBO,可以通过强制指定单个SQL的优化类型。

问题35：RAC安装时安装软件如何确定哪台服务器为节点1

解决方案

GI和数据库都先在节点1上跑，我记得是安装GI到运行ROOT.SH这个脚本时决定，也就是，谁先跑ROOT.SH，ASM1就给谁，后跑的就用ASM2。

问题36：oracle adg主库故障怎样启用备库

解决方案

这个就是failover了。
alter database recover managed standby database finish;
alter database commit to switchover to primary;
shutdown immediate;
startup;
不过，这个要谨慎，会毁掉DG配置。

问题37：ocr和voting disk 存放在asm中的问题

解决方案

ocr是集群的注册信息,只要信息不变更可以有很多信镜像,可以同时放在多个磁盘组中；voting disk是用来做权重vote的当然只能是一个.

问题38：oracle Dataguard 主备库可以是不同操作系统么

解决方案

不行，必须字节顺序一致。但是OGG可以跨OS，跨DB，支持不同版本数据库（oracle/DB2/SQLServer等），不同硬件平台OS（linux/AIX/windows）

问题39：拉链表如何分区

拉链表数据量很大，请问怎么按日期分区，主要查询最近一段时间的数据
table_t1(
card_no 账户,
product_no 产品,
product_num 数量,
start_dt 开始日期,
end_dt 结束日期
)；

解决方案

start_dt和end_dt 如果在查询条件中都用到，你就选定一个字段，做时间范围分区;  如果还不够，另一个时间字段，还可以考虑划分子分区

问题40：oracle数据库可以暴力备份吗？

解决方案

实际上os拷贝大小与oracle块大小不一致.
如果你拷贝这个块时正好有写入这个块,这样问题就来了.
oracle通过尾部的tailchk记录前面的一部分scn_base(低2位)的信息,这样就可能出现不一致的情况,这样拷贝这块就有问题.
这也就是为什么热备份模式要在日志中记录1次整个数据块的信息,这样热备份时要选择空闲时进行.
而rman之类的工具自动修复这个错误.
如果拷贝时没有写入,也许没有问题.
实际上还是不建议这样的备份方式.

问题41：如何监控数据库表增长趋势

在巡检的时候发现很多数据库每天整体表空间占用率增长较快
想要通过user_segments 查询哪些表比较大，但是一直查不出结果，可能是由于数据文件比较多的原因。那如何监控segment级别的数据增长呢，有没有其他有效手段可以抓出每天增长比较多的segment对象？

解决方案
找到一个视图 ,DBA_HIST_SEG_STAT

问题42：oracle是通过什么方法来控制实例名

解决方案

先有实例，再有数据库：通过ORACLE_SID找到参数文件，参照文件里记录了要启动的数据库；根据当前操作系统环境变量的ORACLE_SID去ORACLE_HOME/dbs寻找对应ORACLE_SID的spfile或pfile来启动数据库。

问题43：Grid安装时候搜索不到磁盘怎么解决

解决方案
把字符设备更改成块设备即可。

问题44：oracle自增字段做主键是否会比较快

解决方案

自增主键本身就是增序索引，索引块中存储的记录也都是按顺序存在数据块中，此时排序效率肯定要高。

问题45：请问如何修改一张表的主键

解决方案
alter table tabName
drop constraint tabName_key ;
alter table tabName
add constraint tabName_key primary key(a1,b1) ;

问题46：如何创建SPFILE

解决方案

SQL> connect / as sysdba
SQL> select * from v$version;
SQL> create pfile from spfile;
SQL> CREATE SPFILE FROM PFILE='Era9iadmineyglepfileinit.ora';
文件已创建。

SQL> CREATE SPFILE='Era9idatabaseSPFILEEYGLE.ORA' FROM PFILE='Era9iad
mineyglepfileinit.ora';
文件已创建。

问题47：如何测试SQL语句执行所用的时间

解决方案

SQL>set timing on ;
SQL>select * from tablename;

问题48：SQL语句如何插入全年日期

解决方案

create table BSYEAR (d date);
insert into BSYEAR
select to_date('20030101','yyyymmdd')+rownum-1
from all_objects
where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd');

问题49：怎样估算SQL执行的I/O数

解决方案

SQL>SET AUTOTRACE ON ;
SQL>SELECT * FROM TABLE;
OR
SQL>SELECT * FROM v$filestat ;

问题50：如何查看数据文件放置的路径

解决方案

col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

问题51：如何用正则表达式取出指定的方括号的值

解决方案

可以尝试用纯PL/SQL来实现你这个函数，包括按逗号解析、排序、拼接都用PLSQL完成。排序部分没有SQL的ORDER BY那么方便，可以试试用关联数组，即 TABLE OF ... INDEX BY VARCHAR2(100) 这样的，把你要排序的字符串用作数组下标。然后用 FIRST, NEXT来遍历这个稀疏数组，间接完成了排序的动作。

以上就是总结的51个常遇到的开发或运维中的问题，希望对大家加深认识有帮助。