2007-06-21 Thu
Reading through other programmers’ code I often discover interesting things that sometimes I find hard to explain. For example, consider the following simple PL/SQL block:
DECLARE CURSOR emp_cur IS SELECT first_name FROM employees WHERE employee_id = 100; l_emp_name employees.first_name%TYPE; BEGIN IF emp_cur%ISOPEN THEN CLOSE emp_cur; END IF; OPEN emp_cur; FETCH emp_cur INTO l_emp_name; CLOSE emp_cur; END;
The PL/SQL package I was reviewing had the same exact pattern for every query. Basically, every query was declared as an explicit cursor and code similar to the above was used to open, fetch and close every cursor in the program.
I do not understand two things here: the need for an explicit cursor, and the need for this “IF cur%ISOPEN THEN CLOSE cur; END IF;” before “OPEN cur …. CLOSE cur“. Do you?
I would have coded the above like this:
DECLARE l_emp_name employees.first_name%TYPE; BEGIN SELECT first_name INTO l_emp_name FROM employees WHERE employee_id = 100; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line (‘Not found’); END;
In this example, since the employee_id is the primary key of the employees table, there is no need to code for the TOO_MANY_ROWS exception.
Andrew Clarke has a good post about implicit and explicit cursors in response to Misbah Jalil’s post about the same subject. And here is a virtual book about cursors in Oracle DB 10gR2.
---
Related Articles at Eddie Awad's Blog:
- See How To Hack Oracle Using Dangling Cursor Snarfing
- Producing XML from SQL using cursor expressions
- Oracle REF CURSOR and ColdFusion
在以前的应用中，ASSM表空间已经被广泛的使用，但是，我从来没有真正发现过三级位图块。借这次的存储测试，在测试表装载的时候，随便弄了一个超大的表（873G），但是，遗憾的是，还是没有发现三级位图块。
- SQL> select bytes/1024/1024/1024 "SIZE(G)" from user_segments where segment_name='TEST';
- SIZE(G)
- ----------
- 873.25
- SQL> select file_id,block_id from dba_extents where segment_name='TEST' and extent_id=0;
- FILE_ID BLOCK_ID
- ---------- ----------
- 74 9
- SQL>alter system dump datafile 74 block 74;
打开跟踪文件，可以看到
......
type: 0x23=PAGETABLE SEGMENT HEADER
......
Last Level 1 BMB: 0x251f200c
Last Level II BMB: 0x251d700d
Last Level III BMB: 0x00000000
......
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x12800049
DBA 2: 0x1c00c00d
DBA 3: 0x1c02100d
......
DBA 109: 0x2cdad00d
DBA 110: 0x2cdc200d
DBA 111: 0x2cdd700d
DBA 112: 0x2cdec00d
从以上的信息可以看到，在一个叫PAGETABLE SEGMENT HEADER的块中，已经出现了112个二级位图块，但是，还是没有一个三级位图块。
以上的信息中，可能有人比较疑惑的是，怎么定位这个叫 PAGETABLE SEGMENT HEADER的块。大致方法可以为：
1、从dba_extents中获得的extent_id=0的信息，可以得到file_id=74,block_id=9，这个表示这个段的开始块。
2、dump datafile 74 block 9，可以看到这个是一个FIRST LEVEL BITMAP BLOCK，里面保存了它所管理的block，其中大致为：
......
type: 0x20=FIRST LEVEL BITMAP BLOCK
......
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x12800009 Length: 64 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:Metadata 5:Metadata 6:Metadata 7:Metadata
8:Metadata 9:Metadata 10:Metadata 11:Metadata
......
可以看到，这里有很多类型为Metadata的块，这些都是oracle的管理块，要么是位图块，要么是段头，选择这里面的最后一个Metadata的块，一般就是PAGETABLE SEGMENT HEADER，如我上面的例子，block=74（74=8+Metadata前面的Id）。
3、再dump datafile 74 block 74即可。
我个人猜想位图管理的大致结构应当如下，但是我从来没有证明过，要是有谁发现了三级位图块，或者是有这方面的资料，可以反馈给我，谢谢。
环境： WINDOWS XP单机+Oracle 9.2.0.8
一、 确认主库包含逻辑STANDBY支持的表和数据类型
1、 逻辑STANDBY支持的数据类型：
CHAR
NCHAR
VARCHAR2 and VARCHAR
NVARCHAR2
NUMBER
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
RAW
CLOB
BLOB
2、逻辑STANDBY不支持的数据类型：NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID
3、逻辑STANDBY不支持的表和序列：
Tables and sequences in the SYS schema
Tables with unsupported datatypes
Tables used to support functional indexes
Tables used to support materialized views
Global temporary tables
逻辑STANDBY不支持的表和列可以通过视图dba_logstdby_unsupported来查询：
SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
4、逻辑STANDBY不支持的SQL语句操作：
ALTER DATABASE
ALTER SESSION
ALTER SNAPSHOT
ALTER SNAPSHOT LOG
ALTER SYSTEM SWITCH LOG
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE SCHEMA AUTHORIZATION
CREATE SNAPSHOT
CREATE SNAPSHOT LOG
CREATE SPFILE FROM PFILE
CREATE TABLE AS SELECT FROM A CLUSTER TABLE
DROP DATABASE LINK
DROP SNAPSHOT
DROP SNAPSHOT LOG
EXPLAIN
LOCK TABLE
RENAME
SET CONSTRAINTS
SET ROLE
SET TRANSACTION
二、 确认主库中所有表都具备唯一约束
逻辑STANDBY需要确保主库中每一行每一列的更新都能被日志应用到逻辑备用库里
面，因此最好主库中的表都具备唯一约束。可以通过视图DBA_LOGSTDBY_NOT_UNIQUE来查找主库中不具备唯一约束的表。如果表不具备唯一约束的话，建议创建一个RELY的唯一约束：ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
如果不具备唯一约束的话，那么ORACLE的supplemental logging特性可以自动产生用于标识主库中每一行更改的信息，使得逻辑STANDBY能够同步这些变更。
查看是否启用了supplemental logging特性：
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
NO NO
如果未启用的话，那么开启supplemental logging特性：
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
YES YES
三、 主库其他检查事项
1、 确保主库是归档模式：
Sys@XUE9> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\XUE9\archive
Oldest online log sequence 82
Next log sequence to archive 83
Current log sequence 83
如果主库是非归档的话更改为归档模式。
2、 log_parallelism参数必须设置为1：
Sys@XUE9> show parameter log_parallelism
NAME TYPE VALUE
------------------------------------ ----------- -------
log_parallelism integer 1
3、 创建一个logminer所用的表空间：
创建逻辑STANDBY对象的默认表空间是SYSTEM表空间，为了减少对系统表空间的影响，
我们创建一个专门的表空间用于逻辑STANDBY：
CREATE TABLESPACE logminer DATAFILE 'd:\oracle\oradata\xue9\logminer01.dbf' size 50M;
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logminer');
四、 逻辑STANDBY配置主库端操作：
1、关闭数据库，将主库的数据文件冷备至备用库相应的目录下（也可以采用热备模式，参考METALINK文档：Note:278371.1）
Sys@XUE9> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Sys@XUE9>host copy d:\oracle\oradata\xue9\*.dbf d:\oracle\oradata\standby\
2、MOUNT主库，生成备用库控制文件：
Sys@XUE9> startup mount
ORACLE instance started.
Total System Global Area 80813392 bytes
Fixed Size 453968 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Sys@XUE9> ALTER DATABASE BACKUP CONTROLFILE TO 'd:\oracle\oradata\standby\control01.ctl';
Database altered.
4、 打开主库，执行DBMS_LOGSTDBY.BUILD生成逻辑STANDBY所需LOGMINER字典信息：
Sys@XUE9> alter database open;
Database altered.
Sys@XUE9> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
5、归档当前日志，并记录包含创建逻辑STANDBY字典信息的最新归档日志，将最新归档COPY至STANDBY的相应归档目录：
Sys@XUE9> alter system switch logfile;
System altered.
Sys@XUE9> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES'
2 and STANDBY_DEST='NO';
NAME
---------------------------------------------------------------------
D:\ORACLE\ORADATA\XUE9\ARCHIVE\ARC00094.001
Sys@XUE9>host copy D:\ORACLE\ORADATA\XUE9\ARCHIVE\ARC00094.001
d:\oracle\oradata\standby\ARCHIVE
5、 生成STANDBY初始化参数文件：
Sys@XUE9> create pfile='d:\init.ora' from spfile;
五、 逻辑STANDBY配置：
1、 生成WINDOWS服务
WINNT> oradim -NEW -SID STANDBY -STARTMODE manual
2、 更改初始化参数文件：
注意更改control_files、background_dump_dest等指定的相应目录，这些目录必须预先创建好，注意添加以下几个参数：
standby_archive_dest：指定备用库的归档路径
parallel_max_servers：设置为9，必须设置，因为STANDBY日志恢复里程会启动相应的并行进程：
LOGMINER: Fetch Slave process P003 started with pid=17 OS id=3632
LOGMINER: Apply Slave process P004 started with pid=18 OS id=1888
LOGMINER: Apply Slave process P005 started with pid=19 OS id=2292
LOGMINER: Apply Slave process P006 started with pid=20 OS id=3336
LOGMINER: Apply Slave process P007 started with pid=21 OS id=3824
LOGMINER: Apply Slave process P008 started with pid=22 OS id=1788
instance_name：如果是同机配置STANDBY的话，必须指定和主库不同的实例名
更改后的参数文件如下：
*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\STANDBY\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\STANDBY\control01.ctl','D:\oracle\oradata\STANDBY\control02.ctl','D:\oracle\oradata\STANDBY\control03.ctl'
*.core_dump_dest='D:\oracle\admin\STANDBY\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='XUE9'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='STANDBY'
*.java_pool_size=8388608
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='location=D:\oracle\oradata\STANDBY\archive'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=12582912
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=20971520
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\STANDBY\udump'
*.STANDBY_ARCHIVE_DEST='D:\oracle\oradata\STANDBY\archive'
*.LOG_ARCHIVE_FORMAT=ARC%S.%T
*.lock_name_space=STANDBY
*.standby_file_management='AUTO'
*.parallel_max_servers=9
3、将备用库的控制文件按参数文件中的设置COPY 3份并更名，MOUNT数据库，更改数据文件和日志文件和路径：
idle> startup mount pfile=d:\init.ora
ORACLE instance started.
Total System Global Area 80813392 bytes
Fixed Size 453968 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
idle> alter database rename file 'D:\ORACLE\ORADATA\XUE9\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF' ;
Database altered.
idle> alter database rename file 'D:\ORACLE\ORADATA\XUE9\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF' ;
Database altered.
idle> alter database rename file 'D:\ORACLE\ORADATA\XUE9\LOGMINER01.DBF' to 'D:\ORACLE\ORADATA\STANDBY\LOGMINER01.DBF';
Database altered.
idle> alter database rename file 'D:\ORACLE\ORADATA\XUE9\USERS01.DBF' to 'D:\ORACLE\ORADATA\STANDBY\USERS01.DBF' ;
Database altered.
idle> alter database rename file 'D:\ORACLE\ORADATA\XUE9\redo01.log' to 'D:\ORACLE\ORADATA\STANDBY\redo01.log' ;
Database altered.
idle> alter database rename file 'D:\ORACLE\ORADATA\XUE9\redo02.log' to 'D:\ORACLE\ORADATA\STANDBY\redo02.log' ;
Database altered.
4、启用STANDBY保护模式并打开数据库：
idle> ALTER DATABASE GUARD ALL;
Database altered.
idle> ALTER DATABASE OPEN RESETLOGS;
Database altered.
5、更改数据库名字并启动数据库（这步可做可不做）：
idle> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> startup mount pfile=d:\init.ora
ORACLE instance started.
Total System Global Area 80813392 bytes
Fixed Size 453968 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
C:\Documents and Settings\Roby.Xuexb >set oracle_sid=standby
C:\Documents and Settings\Roby.Xuexb >nid target=sys/admin dbname=standby setname=yes
DBNEWID: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database XUE9 (DBID=1578028243)
Control Files in database:
D:\ORACLE\ORADATA\STANDBY\CONTROL01.CTL
D:\ORACLE\ORADATA\STANDBY\CONTROL02.CTL
D:\ORACLE\ORADATA\STANDBY\CONTROL03.CTL
Change database name of database XUE9 to STANDBY? (Y/[N]) => y
Proceeding with operation
Changing database name from XUE9 to STANDBY
Control File D:\ORACLE\ORADATA\STANDBY\CONTROL01.CTL - modified
Control File D:\ORACLE\ORADATA\STANDBY\CONTROL02.CTL - modified
Control File D:\ORACLE\ORADATA\STANDBY\CONTROL03.CTL - modified
Datafile D:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\STANDBY\LOGMINER01.DBF - wrote new name
Datafile D:\ORACLE\ORADATA\STANDBY\USERS01.DBF - wrote new name
Control File D:\ORACLE\ORADATA\STANDBY\CONTROL01.CTL - wrote new name
Control File D:\ORACLE\ORADATA\STANDBY\CONTROL02.CTL - wrote new name
Control File D:\ORACLE\ORADATA\STANDBY\CONTROL03.CTL - wrote new name
Database name changed to STANDBY.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
idle> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
idle> startup mount pfile=d:\init.ora
ORACLE instance started.
Total System Global Area 80813392 bytes
Fixed Size 453968 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-01103: database name 'STANDBY' in controlfile is not 'XUE9'
idle> startup pfile=d:\init.ora
ORACLE instance started.
Total System Global Area 80813392 bytes
Fixed Size 453968 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-01991: invalid password file 'D:\oracle\9.2\DATABASE\PWDstandby.ORA'
idle> host del D:\oracle\9.2\DATABASE\PWDstandby.ORA
idle> host orapwd file=D:\oracle\9.2\DATABASE\PWDstandby.ORA password=admin entr
ies=5
idle> alter database open;
Database altered.
Elapsed: 00:00:02.04
7、STANDBY创建临时文件，如果v$tempfile有记录主库的临时文件信息，必须先offline drop掉后再创建。这步必须要做，要不STANDBY将无法配置成功。
idle> select * from v$tempfile;
no rows selected
Elapsed: 00:00:00.01
idle> alter tablespace temp add tempfile 'd:\oracle\oradata\standby\temp01.dbf' size 50M reuse;
Tablespace altered.
8、手工注册COPY过来的归档日志文件：
idle> ALTER DATABASE REGISTER LOGICAL LOGFILE 'D:\oracle\oradata\STANDBY\archive
\ARC00094.001';
Database altered.
9、启动STANDBY日志应用模式：
idle> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
Database altered.
六、 配置listener.ora和tnsnames.ora：
Lisener.ora添加STANDBY的配置信息：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\9.2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = XUE9i)
(ORACLE_HOME = D:\oracle\9.2)
(SID_NAME = XUE9i)
)
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = D:\oracle\9.2)
(SID_NAME = standby)
)
(SID_DESC =
(GLOBAL_DBNAME = XUE9)
(ORACLE_HOME = D:\oracle\9.2)
(SID_NAME = XUE9)
)
)
Tnsnames.ora添加STANDBY的连接字：
XUE9 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XUE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XUE9)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XUE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
配置完成后重启监听：lsnrctl reload
七、 主库配置STANDBY的归档信息：
Sys@XUE9> alter system set log_archive_dest_2='service=standby lgwr';
System altered.
Sys@XUE9> alter system set log_archive_dest_state_2=enable;
System altered.
至此，逻辑STANDBY配置完成。
八、 测试日志应用：
主库端操作：
Sys@XUE9> create user roby identified by roby;
User created.
Sys@XUE9> grant dba to roby;
Grant succeeded.
Sys@XUE9> alter user roby default tablespace users;
User altered.
Sys@XUE9> alter system switch logfile;
System altered.
Sys@XUE9> /
System altered.
Sys@XUE9> conn roby/roby
Connected.
Sys@XUE9> create table t(a int);
Table created.
Sys@XUE9> insert into t values(1);
1 row created.
Sys@XUE9> insert into t values(1);
1 row created.
Sys@XUE9> insert into t values(1);
1 row created.
Sys@XUE9> insert into t values(1);
1 row created.
Sys@XUE9> commit;
Commit complete.
Sys@XUE9> alter system switch logfile;
System altered.
Sys@XUE9> alter system switch logfile;
System altered.
Sys@XUE9> alter system switch logfile;
System altered.
Sys@XUE9> select * from t;
A
----------
1
1
1
1
Elapsed: 00:00:00.00
备用库端检查：
Alert log记录的信息：
LOGSTDBY stmt: create user roby identified by VALUES '3937FA8E626D1ADE'
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: grant dba to roby
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: alter user roby default tablespace users
Thu Jun 21 13:58:15 2007
LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: create table t(a int)
Thu Jun 21 14:05:16 2007
LOGSTDBY event: ORA-16204: DDL successfully applied
SQLPLUS登录进去，可以看到创建的表已经成功同步了：
idle> conn roby/roby
Connected.
idle> select * from t;
A
----------
1
1
1
1
./QQWry.Dat 修改为 ${DIR}/QQWry.Dat在公共配置文件中：增加
LoadPlugin="qqhostinfo"输出样例截图： http://www.flickr.com/photos/chedong/578959127/
2007-06-20 Wed
2007-06-19 Tue
AnySQL.net
DBA notes
Oracle & Starcraft
eagle's home
Oraus.net
Oracle Scratchpad
Oracle Life
OracleDBA Blog
Photos from dbanotes
Chanel [K]
xzh2000的博客
Oracle Security Blog
ERN空间
Eddie Awad's Blog
MySQL Performance Blog
The Tom Kyte Blog
del.icio.us/fenng/oracle
AIXpert
O'Reilly Databases
Red Hat Magazine
DBASupport
DB2 Magazine 中文版
Pythian Group Blog
车东[Blog^2]
blue_prince
玉面飞龙的BLOG
此生 今世
人生就是如此
Orange Tiger 木匠 的 web log
生活帮-LifeBang
Fenng's shared items in Google Reader
Hey!! Sky!
dba on unix
Oracle Notes Wiki
Welcome to brotherxiao's Home