eagle’s home

在10.2版本logical standby环境中，如果primary上执行grant select on sequence to user，这条命令会复制到standby，如果sequence在standby上面不存在，apply process会停止工作。

对于有些部分复制的logical standby来说，需要忽略这部分错误。对于grant select on table to user，oracle已经很聪明的忽略了这类错误，显然grant sequence的问题是设计上的一个bug。

还好我们可以通过dbms_logstdby.skip_error来自动忽略此错误。

首先创建一个procedure用来处理GRANT的DDL 错误

CREATE OR REPLACE PROCEDURE sys.handle_error_ddl (
old_stmt IN VARCHAR2,
stmt_type IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2,
xidusn IN NUMBER,
xidslt IN NUMBER,
xidsqn IN NUMBER,
error IN VARCHAR2,
new_stmt OUT VARCHAR2

) AS
BEGIN
— Ignore any GRANT errors
new_stmt := old_stmt;
IF INSTR(UPPER(old_stmt),’GRANT’) > 0
THEN
new_stmt := NULL;
END IF;
END handle_error_ddl;
/

然后执行dbms_logstdby.skip_error，当遇到任何Non schema DDL的错误时，调用procedure sys.handle_error_ddl 来处理。

begin
DBMS_LOGSTDBY.SKIP_ERROR (’NON_SCHEMA_DDL’, ”,”, ‘SYS.HANDLE_ERROR_DDL’);
end;
/

Eagle Fan Life

Oracle升级时间真的是越来越长,对于我们数百个数据库在限定的outage window内的升级，每一分钟都很珍贵。

之前我有一篇文章尽量缩短oracle upgrade时间讲述了一些缩短升级过程的技巧，不过对于10g到11g的升级，怎么算下来都需要1个多小时（目前我们还在寻找缩短11g升级时间的方法）。11g中含有set echo on的文件有下面6个，都是在$ORACLE_HOME/rdbms/admin下面：

1. catocm.sql
2. prvthtdb.plb
3. prvtaddm.plb
4. prvtpspi.plb
5. utlrdt.sql
6. prvtbog2.plb

这样的话，对于我们2个小时的outage window，如果升级过程中出现问题想做downgrade（downgrade过程大约需要40分钟），极大可能会超出outage window。所以必须找到一种快速downgrade的方法。这里便要提到10g的new feature — flashback database。

经过我的测试，flashback database只用了9秒钟，enable flashback会增加大约1分钟upgrade的时间。flashback area的实际使用大小大约为1.6GB。

具体步骤如下：

1. modify compatible = 10.2.0.4
2. Update log_archive_format = TEST_%s.%t_%r_arc
3. set diagnostic_dest in 11g init.ora
#diagnostic_dest=/oracle/TEST/data14/TEST #enabled when switching to 11g
4. Turn on Flashback
DB_RECOVERY_FILE_DEST_SIZE=4G
DB_RECOVERY_FILE_DEST=’/oracle/TEST/data14/TEST/flashback’

– in 10g environment.

SQL> startup mount

SQL> alter database flashback on;

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.
SQL> shtudown immediate;

– switching to 11g environment

SQL> startup upgrade

SQL> spool upgrade.log
SQL> set time on
SQL> @catupgrd.sql

SQL> startup

SYS@TEST:prod SQL> @?/rdbms/admin/utlu112s
.
Oracle Database 11.2 Post-Upgrade Status Tool 01-28-2010 00:49:30
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.2.0.1.0 00:45:51
Oracle Real Application Clusters
. INVALID 11.2.0.1.0 00:00:02
Gathering Statistics
. 00:12:59
Total Upgrade Time: 00:58:55

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catuppst
SQL> @?/rdbms/admin/utlrp
SQL> shutdown immediate

– Flashback in 11g environment
SYS@TEST:prod SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@TEST:prod SQL> startup mount

SYS@TEST:prod SQL> set time on timing on

00:55:47 SYS@TEST:prod SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
——————
RESTORE POINT ONLY

Elapsed: 00:00:00.01
00:55:54 SYS@TEST:prod SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;

Flashback complete.

Elapsed: 00:00:09.00

Incomplete Recovery applied until change 3565405231814 time 01/27/2010 03:11:22
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT before_upgrade
SQL> shutdown immediate

– switch back to 10g version

SQL> startup mount

SQL> alter database open resetlogs;

Database altered.

SQL> select comp_name,version,status,modified from dba_registry;

SYS@USER10:prod SQL> select comp_name,version,status,modified from dba_registry;

COMP_NAME
————————————————————————————————————————————
VERSION STATUS MODIFIED
—————————— ——————————— ————————–
Oracle Database Catalog Views
10.2.0.4.0 VALID 01-MAY-2009 00:04:13

Oracle Database Packages and Types
10.2.0.4.0 VALID 01-MAY-2009 00:04:13

Oracle Real Application Clusters
10.2.0.4.0 INVALID 01-MAY-2009 00:04:13

Eagle Fan Life

老同学发给我的两张92年报纸上的老照片

你能一眼就找到我吗？

晕，学习轻松…..这也是夸奖吗

Eagle Fan Life

Eagle Fan Life

真是越长越像我啦，哈哈

Eagle Fan Life