windows Patch list

32-Bit Patches :

Patch

Patch Location Bug Fix List Notes
11.2.0.3.0 Patch 20 Target date End May 2013
11.2.0.3.0 Patch 19 Bug:16656150 Patch:16656150 Note:1114533.1 Requires 11.2.0.3.0
11.2.0.3.0 Bug:10404530 Patch:10404530 ReadMe
11.2.0.2.0 Patch 26 Target date End May 2013
11.2.0.2.0 Patch 25 Bug:16345845 Patch:16345845 Note:1114533.1 Requires 11.2.0.2.0
11.2.0.2.0 Bug:10098816 Patch:10098816 ReadMe
11.2.0.1.0 Patch 15 Bug:13329696 Patch:13329696 Note:1114533.1 Requires 11.2.0.1.0
11.1.0.7.0 Patch 53 Target date Mid July 2013
11.1.0.7.0 Patch 52 Bug:16345861 Patch:16345861 Note:560295.1

Requires 11.1.0.7.0

11.1.0.7.0 Bug:6890831 Patch:6890831 ReadMe
11.1.0.6.0 Patch 18 Bug:8970709 Patch:8970709 Note:560295.1

Requires 11.1.0.6.0

10.2.0.5.0 Patch 22 Target date Mid July 2013
10.2.0.5.0 Patch 21 Bug:16345855 Patch:16345855 Note:342443.1 Requires 10.2.0.5.0
10.2.0.5.0 Bug:8202632 Patch:8202632 ReadMe
10.2.0.4.0 Patch 50 Bug:15834695 Patch:15834695 Note:342443.1

Requires 10.2.0.4.0

10.2.0.4.0 Patch 49 Bug:13928775 Patch:13928775 Note:342443.1

Requires 10.2.0.4.0

10.2.0.4.0 Bug:6810189 Patch:6810189 ReadMe
10.2.0.3.0 Patch 31 Bug:8288852 Patch:8288852 Note:342443.1

Requires 10.2.0.3.0

10.2.0.3.0 Bug:5337014 Patch:5337014 ReadMe
10.2.0.2.0 Patch 18 Bug:7213940 Patch:7213940 Note:342443.1

Requires 10.2.0.2.0

10.2.0.2.0 Bug:4547817 Patch:4547817 ReadMe
10.2.0.1.0 Patch 9 Bug:5695784 Patch:5695784 Note:342443.1
10.1.0.5.0 Patch 45 Not planned

Requires 10.1.0.5.0 Standalone Database Home

10.1.0.5.0 Patch 44 Bug:13413002 Patch:13413002 Note:276548.1

Applicable to Standalone Database Oracle Homes.
These fixes are consumed by later OracleAS Critical Patches, seeNote:1159443.1 for further information.

10.1.0.5.0 Bug:4505133 Patch:4505133 ReadMe
9.2.0.8.0 Patch 32 Not planned
9.2.0.8.0 Patch 31 Bug:14666190 Patch:14666190 Note:211268.1

Requires 9.2.0.8.0

9.2.0.8.0 CFS RAC Bug:5388107 Patch:5388107

Cluster File System / RAC Clusterware bundle

9.2.0.8.0 Bug:4547809 Patch:4547809 ReadMe

 

64-Bit x64 Patches :

Patch

Patch Location Bug Fix List Notes
11.2.0.3.0 Patch 20 Target date End May 2013
11.2.0.3.0 Patch 19 Bug:16656151 Patch:16656151 Note:1114533.1 Requires 11.2.0.3.0
11.2.0.3.0 Bug:10404530 Patch:10404530 ReadMe
11.2.0.2.0 Patch 26 Target date End May 2013
11.2.0.2.0 Patch 25 Bug:16345846 Patch:16345846 Note:1114533.1 Requires 11.2.0.2.0
11.2.0.2.0 Bug:10098816 Patch:10098816 ReadMe
11.2.0.1.0 Patch 16 Bug:13423278 Patch:13423278 Note:1114533.1 Requires 11.2.0.1.0. Patch 16 is only available on x64 Windows due a build issue.
11.1.0.7.0 Patch 53 Target date Mid July 2013
11.1.0.7.0 Patch 52 Bug:16345862 Patch:16345862 Note:560295.1

Requires 11.1.0.7.0

11.1.0.7.0 Bug:6890831 Patch:6890831 ReadMe
11.1.0.6.0 Patch 18 Bug:8970710 Patch:8970710 Note:560295.1

Requires 11.1.0.6.0

10.2.0.5.0 Patch 22 Target date Mid July 2013
10.2.0.5.0 Patch 21 Bug:16345857 Patch:16345857 Note:342443.1 Requires 10.2.0.5.0
10.2.0.5.0 Bug:8202632 Patch:8202632 ReadMe
10.2.0.4.0 Patch 49 Bug:13928776 Patch:13928776 Note:342443.1

Requires 10.2.0.4.0

10.2.0.4.0 Bug:6810189 Patch:6810189 ReadMe
10.2.0.3.0 Patch 31 Bug:8288854 Patch:8288854 Note:342443.1

Requires 10.2.0.3.0

10.2.0.3.0 Bug:5337014 Patch:5337014 ReadMe
10.2.0.2.0 Patch 18 Bug:7213942 Patch:7213942 Note:342443.1

Requires 10.2.0.2.0

10.2.0.2.0 Bug:4547817 Patch:4547817 ReadMe
10.2.0.1.0 Patch 9 Bug:5695786 Patch:5695786 Note:342443.1

 

64-Bit Itanium Patches :

Patch

Patch Location Bug Fix List Notes
10.2.0.5.0 Patch 22 Target date Mid July 2013
10.2.0.5.0 Patch 21 Bug:16345856 Patch:16345856 Note:342443.1 Requires 10.2.0.5.0
10.2.0.5.0 Bug:8202632 Patch:8202632 ReadMe
10.2.0.4.0 Patch 47 Bug:12914909 Patch:12914909 Note:342443.1

Requires 10.2.0.4.0

10.2.0.4.0 Bug:6810189 Patch:6810189 ReadMe
10.2.0.3.0 Patch 32 Bug:8504486 Patch:8504486 Note:342443.1

Requires 10.2.0.3.0

10.2.0.3.0 Bug:5337014 Patch:5337014 ReadMe
10.2.0.2.0 Patch 18 Bug:7213941 Patch:7213941 Note:342443.1

Requires 10.2.0.2.0

10.2.0.2.0 Bug:4547817 Patch:4547817 ReadMe
10.2.0.1.0 Patch 9 Bug:5695785 Patch:5695785 Note:342443.1
10.1.0.5.0 Patch 45 Not planned
10.1.0.5.0 Patch 44 Bug:13413003 Patch:13413003 Note:276548.1

Applicable to Standalone Database Oracle Homes.
These fixes are consumed by later OracleAS Critical Patches, seeNote:1159443.1 for further information.

10.1.0.5.0 Bug:4505133 Patch:4505133 ReadMe
9.2.0.8.0 Patch 31 Not planned
9.2.0.8.0 Patch 30 Bug:9683645 Patch:9683645 Note:211268.1

Requires 9.2.0.8

9.2.0.8.0 CFS RAC Bug:5689499 Patch:5689499
9.2.0.8.0 Bug:4547809 Patch:4547809 ReadMe

 

修改dataguard主库redo组数和大小

在一个dg环境中,配置的是实时同步,需要增加主库的redo大小和组数,本来是一个很简单的问题,解决思路是:先备库增加standby redo删除老standby redo,然后主库增加redo删除老redo,备库增加新redo删除老redo,最后主库增加standby redo。但是在实施过程中,遇到了一些细节性的问题,主要是学习到了log_file_name_convert如果不配置,将导致备库redo 文件不能被删除

standby redo log管理
增加standby redo log

SQL> alter database add standby logfile group 8 ('/data/oradata/wasudb/st_redo08.log') size 200M; 
alter database add standby logfile group 8 ('/data/oradata/wasudb/st_redo08.log') size 200M
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 9 ('/data/oradata/wasudb/st_redo09.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 10 ('/data/oradata/wasudb/st_redo10.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 11 ('/data/oradata/wasudb/st_redo11.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 12 ('/data/oradata/wasudb/st_redo12.log') size 200M; 

Database altered.

删除standby redo log

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/data/oradata/wasudb/st_redo04.log'


SQL> alter database drop logfile group 5;;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

--在主库多次执行switch logfile

SQL>  alter database drop logfile group 4;

Database altered.

主库redo log 管理
增加redo log

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除redo log

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.
--这里涉及到多次switch logfile,需要确定redo是inactive才能够删除

备库redo log管理
增加redo log

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m; 
alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m
*
ERROR at line 1:
ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is automatic.


SQL>  alter system set standby_file_management=manual;

System altered.

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除redo log

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance wasudb (thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL>  ALTER DATABASE CLEAR LOGFILE GROUP 1;
 ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL> show parameter NAME_CONVERT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string

SQL> alter system set log_file_name_convert='/data/oradata/wasudb','/data/oradata/wasudb' scope=spfile;

System altered.

SQL>  alter system set db_file_name_convert='/data/oradata/wasudb','/data/oradata/wasudb' scope=spfile;

System altered.

--重启数据库

SQL> show parameter file_name_convert;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /data/oradata/wasudb, /data/or
                                                 adata/wasudb
log_file_name_convert                string      /data/oradata/wasudb, /data/or
                                                 adata/wasudb

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance wasudb (thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

主库standby redo log管理
增加standby redo

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除standby redo

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

后续工作

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

至此修改dataguard环境中的redo大小和增加redo组数的操作基本完成.在这里主要修正了自己以前对log_file_name_convert的认识,我以前以为如果我的主备库redo对应的目录一致不用配置该参数,今天通过查询MOS发现从10.2开始数据库为了能实现dg的快速切换在mrp启动的时候会去尝试清理备库redo,如果备库没有redo,或者log_file_name_convert配置不正确导致不能正常执行这个清理工作,数据库就会报ORA-19527,特别是在mrp进程启动之时.对于本次出现执行CLEAR LOGFILE命令也出现该问题,确实有点过犹不及了.这里也就是提醒我们:就算redo file,datafile主备位置相同,也建议配置log_file_name_convert和db_file_name_convert参数,提高dg健壮性.

通过脚本获得创建用户语句

在某些情况下,我们需要获得数据库用户的创建脚本(包含系统权限,对象权限,配额等相关语句),这些东西如果人工去做绝对是体力活,在asktom网站上看到相关脚本,做了测试和验证,确实很好

---------------------------------------------
-- ###########################################
---------------------------------------------
create or replace procedure GET_CREATE_USER_DDL
 as
cursor get_username is
select username
from dba_users
--where username IN('CHF','XIFENFEI')
;

begin
for l_user in get_username loop

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case'); 
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)'); 
DBMS_OUTPUT.PUT_LINE('               from   dba_users'); 
DBMS_OUTPUT.PUT_LINE('               where  username = '''||l_user.username||''') > 0)'); 
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_ddl (''USER'', '''||l_user.username||''')'); 
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: User not found!'')'); 
DBMS_OUTPUT.PUT_LINE('        end ) "--Extracted_DDL" from dual'); 
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case'); 
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)'); 
DBMS_OUTPUT.PUT_LINE('               from   dba_ts_quotas'); 
DBMS_OUTPUT.PUT_LINE('               where  username = '''||l_user.username||''') > 0)'); 
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''TABLESPACE_QUOTA'', 
'''||l_user.username||''')'); 
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No TS Quotas found!'')'); 
DBMS_OUTPUT.PUT_LINE('        end )  from dual'); 
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case'); 
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)'); 
DBMS_OUTPUT.PUT_LINE('               from   dba_role_privs'); 
DBMS_OUTPUT.PUT_LINE('               where  grantee = '''||l_user.username||''') > 0)'); 
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''ROLE_GRANT'', 
'''||l_user.username||''')'); 
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No granted roles found!'')'); 
DBMS_OUTPUT.PUT_LINE('        end ) from dual'); 
DBMS_OUTPUT.PUT_LINE('UNION ALL');


DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case'); 
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)'); 
DBMS_OUTPUT.PUT_LINE('               from   dba_sys_privs'); 
DBMS_OUTPUT.PUT_LINE('               where  grantee = '''||l_user.username||''') > 0)'); 
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''SYSTEM_GRANT'', 
'''||l_user.username||''')'); 
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No System Privileges found!'')'); 
DBMS_OUTPUT.PUT_LINE('        end ) from dual'); 
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case'); 
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)'); 
DBMS_OUTPUT.PUT_LINE('               from   dba_tab_privs'); 
DBMS_OUTPUT.PUT_LINE('               where  grantee = '''||l_user.username||''') > 0)'); 
DBMS_OUTPUT.PUT_LINE('        then  dbms_metadata.get_granted_ddl (''OBJECT_GRANT'', 
'''||l_user.username||''')'); 
DBMS_OUTPUT.PUT_LINE('        else  to_clob (''   -- Note: No Object Privileges found!'')'); 
DBMS_OUTPUT.PUT_LINE('        end ) from dual'); 
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------');

end loop;
end;
/


---------------------------------------------
--###########################################
---------------------------------------------
The above proc when called with the foll. will give the SQLs for all users:

---------------------------------------------
-- ###########################################
---------------------------------------------
set pages 50000
set serveroutput on size unlimited
spool /tmp/exec_GET_CREATE_USER_DDL.sql
exec GET_CREATE_USER_DDL 
spool off
---------------------------------------------
-- ###########################################
---------------------------------------------

These SQLs generated can in turn be run as follows to get the master-list of all the grants in the database:

---------------------------------------------
-- ###########################################
---------------------------------------------
spool /tmp/GET_CREATE_USER_DDL.sql
conn / as sysdba
set long 1000000000
set pages 50000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
@/tmp/exec_GET_CREATE_USER_DDL.sql
spool off
---------------------------------------------
-- ###########################################
---------------------------------------------

通过这个脚本,我们可以在游标地方限制我们需要获得脚本的用户,而最终得到的/tmp/GET_CREATE_USER_DDL.sql就是我们需要的创建用户的对应脚本.

如果只需要获得一个用户创建相关脚本,只需要执行如下sql即可

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select (case 
        when ((select count(*)
               from   dba_users
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_ddl ('USER', '&&Username') 
        else  to_clob ('   -- Note: User not found!')
        end ) Extracted_DDL from dual
UNION ALL
select (case 
        when ((select count(*)
               from   dba_ts_quotas
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username') 
        else  to_clob ('   -- Note: No TS Quotas found!')
        end ) from dual
UNION ALL
select (case 
        when ((select count(*)
               from   dba_role_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username') 
        else  to_clob ('   -- Note: No granted Roles found!')
        end ) from dual
UNION ALL
select (case 
        when ((select count(*)
               from   dba_sys_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username') 
        else  to_clob ('   -- Note: No System Privileges found!')
        end ) from dual
UNION ALL
select (case 
        when ((select count(*)
               from   dba_tab_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username') 
        else  to_clob ('   -- Note: No Object Privileges found!')
        end ) from dual
/