oracle recycle bin

在10g后,oracle出了个叫recycle bin的东西,粗浅的理解成”回收站”就可以。
表空间的Recycle Bin 区域只是一个逻辑区域,而不是从表空间上物理的划出一块区域固定用于回收站,因此Recycle Bin是和普通对象共用表空间的存储区域,或者说是Recycle Bin的对象要和普通对象抢夺存储空间。当发生空间不够时,Oracle会按照先入先出的顺序覆盖Recycle Bin中的对象。所以为了避免这类资源的消耗,建议管理员不定期的看看recyle bin中的对象,定时清理。
也可以手动的删除Recycle Bin占用的空间:
1). Purge tablespace tablespace_name : 用于清空表空间的Recycle Bin
2). Purge tablespace tablespace_name user user_name: 清空指定表空间的Recycle Bin中指定用户的对象
3). Purge recyclebin: 删除当前用户的Recycle Bin中的对象
4). Purge dba_recyclebin: 删除所有用户的Recycle Bin中的对象,该命令要sysdba权限
5). Drop table table_name purge: 删除对象并且不放在Recycle Bin中,即永久的删除,不能用Flashback恢复。
6). Purge index recycle_bin_object_name: 当想释放Recycle bin的空间,又想能恢复表时,可以通过释放该对象的index所占用的空间来缓解空间压力。 因为索引是可以重建的

Fine Grained Auditing (FGA) is independant to oracle standard audit

在oracle-base上找到一篇介绍FGA的文章,取了片段引用过来。原文可以参考http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
FGA与oracle standard audit是相对独立的,具体可以参考引用的例子,长久以来错误的以为oracle standard audit是FGA的基础,惭愧啊。
Fine Grained Auditing (FGA)
Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

First, create a test table.
CONN audit_test/password CREATE TABLE emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Tim’, 1); INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Larry’, 50001); COMMIT;
The following policy audits any queries of salaries greater than £50,000.
CONN sys/password AS sysdba BEGIN DBMS_FGA.add_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SALARY_CHK_AUDIT’, audit_condition => ‘SAL > 50000’, audit_column => ‘SAL’); END; /
Querying both employees proves the auditing policy works as expected.
CONN audit_test/password SELECT sal FROM emp WHERE ename = ‘Tim’; SELECT sal FROM emp WHERE ename = ‘Larry’; CONN sys/password AS SYSDBA SELECT sql_text FROM dba_fga_audit_trail; SQL_TEXT —————————————— SELECT sal FROM emp WHERE ename = ‘Larry’ 1 row selected. SQL>
Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:
BEGIN DBMS_FGA.add_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SALARY_CHK_AUDIT’, audit_condition => ‘SAL > 50000’, audit_column => ‘SAL’, handler_schema => ‘AUDIT_TEST’, handler_module => ‘FIRE_CLERK’, enable => TRUE); END; /
The DBMS_FGA package contains the following procedures:
ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY
In Oracle9i fine grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.
— Clear down the audit trail. CONN sys/password AS SYSDBA TRUNCATE TABLE fga_log$; SELECT sql_text FROM dba_fga_audit_trail; no rows selected. — Apply the policy to the SAL column of the EMP table. BEGIN DBMS_FGA.add_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SAL_AUDIT’, audit_condition => NULL, — Equivalent to TRUE audit_column => ‘SAL’, statement_types => ‘SELECT,INSERT,UPDATE,DELETE’); END; / — Test the auditing. CONN audit_test/password SELECT * FROM emp WHERE empno = 9998; INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1); UPDATE emp SET sal = 10 WHERE empno = 9998; DELETE emp WHERE empno = 9998; ROLLBACK; — Check the audit trail. CONN sys/password AS SYSDBA SELECT sql_text FROM dba_fga_audit_trail; SQL_TEXT ————————————– SELECT * FROM emp WHERE empno = 9998 INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1) UPDATE emp SET sal = 10 WHERE empno = 9998 DELETE emp WHERE empno = 9998 4 rows selected. — Drop the policy. CONN sys/password AS SYSDBA BEGIN DBMS_FGA.drop_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SAL_AUDIT’); END; /

ORA-04030 out of memory

客户现场的数据库报错了,一个b/s结构的web系统,在夜间跑一个batch任务的时候报错退出,job失败一般reload一下就行了,对前台的客户倒是影响也不大。
ORA-04030: out of process memory when trying to allocate 24 bytes (top call heap,PGA heap pool)
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn
skgpspawn failed:category = 27143, depinfo = 9261, op = spdcr, loc = skgpspawn

win32 4G ram,oracle 9201,530M的pga,sga_max_size给了1.5G。当然了,9i下面,sga的使用是在各个pool中定义的。sga_max_size是db_cache,share_pool等等能够调节之后,使用的最大值。一般来说,db_cache_size+share pool之后会小于这个值。遇到这个报错多半是os上的资源不足够了。win32bit下面,user process能够使用的最大内存<2G,这里的user process是除了系统进程以外的所有程序进程,可以理解为:除了系统以外,其余非系统进程所能使用的。当然,oracle.exe肯定是其中之一而已。有人说开启os上的PAE,/3G设置能够解决此问题。我觉得仅仅能缓解而已。如果pga确实满足不了app,os上还是会陆续的报错,类似"unable to begin xxx thread"之类的。解决方法?建议更换成64bit的吧。或者缩小pga..试试。

CRS-0184: Cannot communicate with the CRS daemon

环境rhel 4u8+oracle rac+nfs
当你想在oracle用户下面通过crs_start -all来启动所有资源的时候,可能会遇到如下类似的情形。
[oracle@rac2 dbs]$ crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.
这个时候需要去查看一下当前节点上的crs进程是否正常。
[root@rac2 bin]# ./crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
[root@rac2 bin]# ps -ef|grep crs
root 10816 1 0 09:59 ? 00:00:00 /bin/sh /etc/init.d/init.crsd run
root 21639 30579 0 10:32 pts/1 00:00:00 grep crs

可见,这不是正常启动状态下crs的输出信息。
CRS正常情况下的状态输出可以参考如下:
[root@rac1 bin]# ./crsctl check crs
CSS appears healthy

lanmanservershare

windows的共享权限都记录在注册表里面,“lanmanservershare”。如果用户遇到迁移共享文件夹的需求时,可以考虑注册表的导入导出。文件夹目录的ntfs权限应该在copy时能带过去,而共享权限都在注册表导出文件中。

–EOF–

starwind+starport做共享存储

starwind和starport是个不错的组合,如果你想在virtualbox或者vmware上测试基于共享存储的集群方案的话,可以
考虑采用iscsi的starwind服务端+starport客户端的组合。例如,mscs,oracle rac等等。。
之前有客户需求搭建模拟测试环境,在mscs+oracle做primary/standby模式测试,遇到死活找不到仲裁磁盘的错误。。
折腾了两个晚上,在朋友的建议拿starwind和starport来做共享存储,很轻松解决了,很方便。
–EOF–

esxi server to esxi server

客户最近有需求,要将一个环境中的esxi虚拟机迁移至另外一套esxi server中,因为有停机的时间,于是建议客户使用最笨的方法,利用vsphere client的工具,download和upload功能将vm的文件迁移至新的esxi server上,再浏览存储的时候,点击vmx配置文件,“添加到清单”中。方法确实很笨。。。囧。不过倒是解决了客户的需求。我想esxi应该有迁移的更好的、现成的方法,但肯定需要额外的license

create public database link vs create database link

create public database link与create database link的区别就是:public link是数据库中所有数据库用户都可以访问使用的,而普通的database link是私有的link,仅仅对创建它的用户可用。

用户可以通过dblink,和远端数据库的账户A,从本地去访问远端用户A下面的数据。
但从数据管控的角度来讲,存在相对于A用户的远端源头,能够操作A下面用户的风险

pay attention as exporting the object from the oracle 11g

please pay attention when someone is trying to export the data from the oracle 11g and it’s better to use expdp as default export tools,because tables without rows(0 rows) will not be exported by exp which may lead the incorrectness when you import the dmp file

change the password of sys on rac

We can login the rac by ‘sqlplus sys/password@tnsnames as sysdba’ from the remote machine,if you want to change the password on RAC, maybe you need to alter user sys idenfied by ‘passwd’ on each nodes when the ORApwdfile was located in local disk on each nodes instead of shared disk.
for example, you need to do alter user sys on every nodes in the RAC,because the orapwd file was seperated on the nodes.
solution:
1.the easiest way:just do the alter user sys.. on each nodes and make the password the same one.

2.put the orapwdfile as just one file for every nodes on shared disk,as you change the password on one nodes,it can notify the changes to single orapwd file through the shard disk.