月归档：五月 2013

修改dataguard主库redo组数和大小

发表于 2013 年 5 月 14 日由惜分飞

在一个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健壮性.

发表在 Data Guard | 10 条评论

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

发表于 2013 年 5 月 14 日由惜分飞

在某些情况下,我们需要获得数据库用户的创建脚本(包含系统权限,对象权限,配额等相关语句),这些东西如果人工去做绝对是体力活,在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
/

发表在 Oracle | 评论关闭

bbed处理ORA-01200故障

发表于 2013 年 5 月 12 日由惜分飞

一个朋友的测试库出现ORA-01200错误,正好周末比较空闲,随手帮他使用bbed进行了恢复,给广大朋友提供一种解决该问题的方法
数据库启动报错

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:09:11 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL> connect/as sysdba
已连接到空闲例程。
SQL> startup force
ORACLE 例程已经启动。

Total System Global Area 1071333376 bytes
Fixed Size                  1334380 bytes
Variable Size             318768020 bytes
Database Buffers          746586112 bytes
Redo Buffers                4644864 bytes
数据库装载完毕。
ORA-01122: 数据库文件 1 验证失败
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01200: 87946 的实际文件大小小于 88320 块的正确大小

这里的错误很明显是因为file 1的数据文件头记录block大小为88320个block,而该数据文件的实际大小只有87946个block,所以出现该问题.

dbv检测文件

D:\app\Administrator\oradata\orcl>dbv file=SYSTEM01.DBF

DBVERIFY: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:30:29 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - 开始验证: FILE = SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 87040
处理的页总数 (数据): 62870
失败的页总数 (数据): 0
处理的页总数 (索引): 11055
失败的页总数 (索引): 0
处理的页总数 (其它): 2437
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 10678
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 980055 (0.980055)

检查发现该数据文件未发现坏块,减小了该数据文件通过bbed恢复异常的风险,数据库最怕就是system中出现很多坏块

使用bbed修改kccfhfsz
因为win的bbed问题,所以拷贝到我的电脑上进行修改

C:\Users\XIFENFEI\Desktop\temp>bbed filename=system01.dbf blocksize=8192
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sun May 12 23:27:26 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set block 2
        BLOCK#          2
--从一台机器中拷贝到另外的机器，实际中的block可能发生改变，因为含block 0

BBED> map
 File: system01.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0

 ub4 tailchk                                @8188


BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a

--通过ORA-01200错误报出来的文件头记录大小88320实际就是0x0001578a


BBED> set mode edit
        MODE            Edit

BBED> set count 32
        COUNT           32

BBED> d
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
00590100 00200000 01000300 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> m /x 8A570100
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
 8a570100 00200000 01000300 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>
--通过ORA-01200错误报出来的数据文件实际大小,来修改该文件头的kcvfhhdr.kccfhfsz值,也可以通过文件实际大小计算出来


BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a

BBED> sum apply
Check value for File 0, Block 2:
current = 0x0f79, required = 0x0f79

BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 1


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

打开数据库

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;

数据库已更改。

发表在非常规恢复 | 标签为 bbed, ORA-01200 | 评论关闭

17813235971

QQ 咨询

月归档：五月 2013

修改dataguard主库redo组数和大小

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

bbed处理ORA-01200故障

文章分类

最近发表

月归档：五月 2013

修改dataguard主库redo组数和大小

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

bbed处理ORA-01200故障

标签云

文章分类

最近发表