eygle.com   eygle.com
eygle.com eygle
eygle.com  
 
Digest Net: February 2009 Archives

February 2009 Archives

在Oracle 10g之前,merge语句支持匹配更新和不匹配插入2种简单的用法,在10g中Oracle对merge语句做了增强,增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。

 

参考Oracle 的SQL Reference,大家可以看到Merge Statement的语法如下:
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
{ table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

下面我在windows xp 下10.2.0.1版本上做一个测试看看


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
一、创建测试用的表
SQL> create table subs(msid number(9),
2 ms_type char(1),
3 areacode number(3)
4 );

表已创建。

SQL> create table acct(msid number(9),
2 bill_month number(6),
3 areacode number(3),
4 fee number(8,2) default 0.00);

表已创建。

SQL>
SQL> insert into subs values(905310001,0,531);

已创建 1 行。

SQL> insert into subs values(905320001,1,532);

已创建 1 行。

SQL> insert into subs values(905330001,2,533);

已创建 1 行。

SQL> commit;

提交完成。

SQL>
 

二、下面先演示一下merge的基本功能

1) matched 和not matched clauses 同时使用
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode);
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode);

3) 只有matched clause, 也就是只更新不插入
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study

SQL> select * from subs;

MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------

SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when MATCHED then
4 update set a.areacode=b.areacode
5 when NOT MATCHED then
6 insert(msid,bill_month,areacode)
7 values(b.msid,'200702',b.areacode);

Done

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905320001 200702 532 0.00
905330001 200702 533 0.00
905310001 200702 531 0.00

SQL> insert into subs values(905340001,3,534);

1 row inserted

SQL> select * from subs;

MSID MS_TYPE AREACODE
---------- ------- --------
905340001 3 534
905310001 0 531
905320001 1 532
905330001 2 533

SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when NOT MATCHED then
4 insert(msid,bill_month,areacode)
5 values(b.msid,'200702',b.areacode);

Done

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905320001 200702 532 0.00
905330001 200702 533 0.00
905310001 200702 531 0.00
905340001 200702 534 0.00

SQL> update subs set areacode=999;

4 rows updated

SQL> select * from subs;

MSID MS_TYPE AREACODE
---------- ------- --------
905340001 3 999
905310001 0 999
905320001 1 999
905330001 2 999

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905320001 200702 532 0.00
905330001 200702 533 0.00
905310001 200702 531 0.00
905340001 200702 534 0.00

SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when MATCHED then
4 update set a.areacode=b.areacode;

Done

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905320001 200702 999 0.00
905330001 200702 999 0.00
905310001 200702 999 0.00
905340001 200702 999 0.00

SQL>
 
三、10g中增强一:条件操作

1) matched 和not matched clauses 同时使用
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;

3) 只有matched clause, 也就是只更新不插入
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0;


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as study

SQL> select * from subs;

MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------

SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when MATCHED then
4 update set a.areacode=b.areacode
5 where b.ms_type=0
6 when NOT MATCHED then
7 insert(msid,bill_month,areacode)
8 values(b.msid,'200702',b.areacode)
9 where b.ms_type=0;

Done

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905310001 200702 531 0.00

SQL> insert into subs values(905360001,0,536);

1 row inserted

SQL> select * from subs;

MSID MS_TYPE AREACODE
---------- ------- --------
905360001 0 536
905310001 0 531
905320001 1 532
905330001 2 533

SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when NOT MATCHED then
4 insert(msid,bill_month,areacode)
5 values(b.msid,'200702',b.areacode)
6 where b.ms_type=0;

Done

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905310001 200702 531 0.00
905360001 200702 536 0.00

SQL> update subs set areacode=888 where ms_type=0;

2 rows updated

SQL> select * from subs;

MSID MS_TYPE AREACODE
---------- ------- --------
905360001 0 888
905310001 0 888
905320001 1 532
905330001 2 533

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905310001 200702 531 0.00
905360001 200702 536 0.00

SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when MATCHED then
4 update set a.areacode=b.areacode
5 where b.ms_type=0;

Done

SQL> select * from acct;

MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905310001 200702 888 0.00
905360001 200702 888 0.00

SQL>
四、10g中增强二:删除操作
An optional DELETE WHERE clause can be used to clean up after a
merge operation. Only those rows which match both the ON clause
and the DELETE WHERE clause are deleted.

merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
delete where (b.ms_type!=0);

SQL> select * from subs;

MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

SQL> select * from acct;

MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
905320001 1 532
905330001 2 533

SQL>
SQL> merge into acct a
2 using subs b on (a.msid=b.msid)
3 when MATCHED then
4 update set a.areacode=b.areacode
5 delete where (b.ms_type!=0);

Done

SQL> select * from acct;

MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531

SQL>

更为详尽的语法,请参考Oracle SQL Reference手册!

原文链接:http://tomszrp.itpub.net/post/11835/263865

Oracle Event 10434 And DRM

Quote From Metalink:
In combination with event 10429/10, tracing with event 10434 at level 6 is useful for debugging internal message batching issues. Only set this event when instructed by Support or Development.

Example:

*** 2004-02-19 15:13:16.315
kjccfmb: free buff 8cc037bc, queue 8c52f064, pool 200097fc, size 408, qlen 0
*** 2004-02-19 15:13:16.316
kjccfmb: free buff 8cc037bc, queue 8c52f064, pool 200097fc, size 408, qlen 0
*** 2004-02-19 15:13:16.317
kjccfmb: free buff 8cc037bc, queue 8c52f064, pool 200097fc, size 408, qlen 0
kjccbatchstart: bflags 0x10 flag 0x0
kjccgmb_pb[new bat]: bmsg 8cf7227c mb 8cf72238 msg 8cf72298 mlen 332 dest x104 flushsz 65535
kjccbatchflush: flag 0x11 end 1 inc 2.2
kjccbatchflush[flush]: bmsg 8cf7227c blen 360 dest[1.4]
*** 2004-02-19 15:13:16.318
kjccfmb: free buff 8cf72238, queue 8c52f064, pool 200098f4, size 4128, qlen 0

Oracle10g Database Resource Manager

Database Resource Manager (DRM) provides the resource management facilities. In a database instance, which is highly active and concurrently accessed by large numbers of users, control of suitable resource allocation is essential. In the absence of a better resource control, some critical and high priority sessions or tasks may not get required resources in time. Oracle DRM is a framework that provides a mechanism to control the resource allocation.

The DRM helps to allocate a percentage of CPU time to different users, user groups, and applications. It can limit the parallelism of any operation by allowing other competing processes to get their share of resources. It also can create resource pools, such as the 'undo pool' and the 'active session pool', that help control the execution resource availability for a group of sessions.
Oracle10g Components of DRM

There are three main components with which you can define and manage resource allocation. They are:

* Resource Consumer Group - These are the named entities, which are groups of users or sessions combined together, based on their processing and resource needs.
* Resource Plan - Contains the directives that specify how the resources are allocated to the resource consumer groups.
* Resource Plan Directive - These are used to associate resource consumer groups with particular resource plans and allocate resources among the resource consumer groups.

The Oracle package dbms_resource_manager is used to create and maintain the resource plans and manage the resource consumer groups.

Oracle DRM - Dynamic Resource management
When using Real application Clusters (RAC), Each instance has its own SGA and buffer cache. RAC will ensure that these block changes are co-ordinated to maximize performance and to ensure data intergrity. Each copy of the buffer also called as a cache resource has a master which is one of the nodes of the cluster.

In database releases before 10g (10.1.0.2) once a cache resource is mastered on an instance, a re-mastering or a change in the master would take place only during a reconfiguration that would happen automatically during both normal operations like instance startup or instance shutdown or abnormal events like Node eviction by Cluster Manager. So if Node B is the master of a cache resource, this resource will remain mastered on Node B until reconfiguration.

10g introduces a concept of resource remastering via DRM. With DRM a resource can be re-mastered on another node say from Node B to Node A if it is found that the cache resource is accessed more frequently from Node A. A reconfiguration is no longer the only reason for a resource to be re-mastered.

Cache Fusion and Inter Instance Coordination

Recovery Methodology and steps

Oracle performs the following steps to recover:

1. In the initial phase of recovery, GES enqueues are reconfigured and the global resource directory is frozen. All GCS resource requests and writes are temporarily halted.

2. GCS resources are reconfigured among the surviving instances. One of the surviving instances becomes the "recovering instance". The SMON process of the recovering instance starts a first pass of the redo log read of the failed instance's redo thread.

3. Block resources that need to be recovered are identified and the global resource directory is reconstructed. Pending requests or writes are cancelled or replayed.

4. Resources identified in the previous log read phase are defined as recovery resources. Buffer space for recovery is allocated.

5. Assuming that there are past images of blocks to be recovered in other caches in the cluster, source buffers are requested from other instances. The resource buffers are the starting point of recovery for a particular block.

6. All resources and enqueues required for subsequent processing have been acquired and the global resource directory is now unfrozen. Any data blocks that are not in recovery can now be accessed. At this time, the system is partially available.

7.The SMON merges the redo thread order by SCN to ensure that changes are written in an orderly fashion. (This process is important for multiple simultaneous failures. If multiple instances die simultaneously, neither the PI buffers nor the current buffers for a data block can be found in any surviving instance's cache. Then a log merger of the failed instances is performed.)

AIX下卷组 closed/syncd 之原因说明

在AIX下,卷组有时候会显示closed/syncd 和open/syncd 两种状态:
$ lsvg -l oradata
oradata:
LV NAME            TYPE      LPs    PPs    PVs  LV STATE      MOUNT POINT
ora_ocr01_512m      raw        1      1      1    closed/syncd  N/A
ora_ocr02_512m      raw        1      1      1    closed/syncd  N/A
ora_vote01_512m    raw        1      1      1    closed/syncd  N/A
ora_vote02_512m    raw        1      1      1    closed/syncd  N/A
ora_vote03_512m    raw        1      1      1    closed/syncd  N/A
ora_system_10g      raw        20      20      1    closed/syncd  N/A
ora_sysaux_5g      raw        10      10      1    closed/syncd  N/A
ora_undo01_20g      raw        40      40      1    closed/syncd  N/A
ora_undo02_20g      raw        40      40      1    closed/syncd  N/A
ora_temp_5g        raw        10      10      1    closed/syncd  N/A
ora_user_5g        raw        10      10      1    closed/syncd  N/A
portaltbs_20g      raw        40      40      1    closed/syncd  N/A
portaltmp_10g      raw        20      20      1    closed/syncd  N/A
lvoradata          jfs2      80      80      1    open/syncd    /oradata
loglv00            jfs2log    1      1      1    open/syncd    N/A
lvoraarch          jfs2      80      80      1    open/syncd    /oraarch


注意当 lv 作为裸设备(raw device) 使用时,当数据库正在使用时其状态才会是open/syncd,如果是用文件系统,则需要创建文件系统并 mount 文件系统才是 open/syncd 的。closed / syncd 一般不意味着存在问题。

在Oracle Database 10g中引入了DB time指标,其实这并不算是新概念。

先摘录一些官方的说明(Oracle Database Performance Tuning Guide 10g Release 2 (10.2)    B14211-01)

DB Time

Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the time spent on instance background processes such as PMON.

The most important of the time model statistics is DB time. This statistics represents the total time spent in database calls and is a indicator of the total instance workload. It is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions). DB time is measured cumulatively from the time that the instance was started.

Because DB time it is calculated by combining the times from all non-idle user sessions, it is possible that the DB time can exceed the actual time elapsed since the instance started up. For example, a instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is
approximately 120 minutes.
/*记住:如果只有一个cpu,就不可能4个session同时处于no-idle,所以下面这个假设只能在大于4cpu的服务器上成立。

For example, a instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is approximately 120 minutes.
*/

By reducing DB time, the database server is able to support more user requests using the same resources, which increases throughput.
   
   
总结出来就是db time= cpu time + wait time(不包含空闲等待) (非后台进程)
说白了就是db time就是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间


列出下面这两个来做解释:

Report A:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 4610 24-Jul-08 22:00:54 68 19.1
End Snap: 4612 24-Jul-08 23:00:25 17 1.7
Elapsed: 59.51 (mins)
DB Time: 466.37 (mins)

Report B:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 3098 13-Nov-07 21:00:37 39 13.6
End Snap: 3102 13-Nov-07 22:00:15 40 16.4
Elapsed: 59.63 (mins)
DB Time: 19.49 (mins)

服务器是AIX的系统,4个双核cpu,共8个核:
/sbin> bindprocessor -q
The available processors are: 0 1 2 3 4 5 6 7
先说Report A,在snapshot间隔中,总共约60分钟,cpu就共有60*8=480分钟,DB time为466.37分钟,则:
cpu花费了466.37分钟在处理Oralce非空闲等待和运算上(比方逻辑读)
也就是说cpu有 466.37/480*100% 花费在处理Oracle的操作上,这还不包括后台进程

看Report B,总共约60分钟,cpu有 19.49/480*100% 花费在处理Oracle的操作上
很显然,2中服务器的平均负载很低。
从awr report的Elapsed time和DB Time就能大概了解db的负载

引自

来源链接:http://zhang41082.itpub.net/post/7167/464465

因为业务需要,不得不在生产上更新一个千万级的大表,更新过程中产生了4个G左右的日志后发现此更新会导致其他问题,取消更新,然后更正更新的逻辑后,重新对这千万级的数据进行更新,产生6个多G的日志后,更新完成,但是却造成了其他两个逻辑STANDBY的大量延迟。如何加快这些日志的应用,是一个比较严重的问题。

1、首先是加快主库的更新操作,那么首先关闭主库对STANDBY的归档,减少主库写STANDBY库日志对主库产生的压力,这需要在更新开始前在主库设置log_archive_dest_state_N参数为DEFER,然后主库切换日志来激活这个新的参数设置。注意:如果是RAC的主库,则每个库都需要设置此参数,并且每个库都进行一次日志切换操作。

2、主库更新完成后,可以在备用库做一些设置来加速日志应用的更新,参数如下:
APPLY_SERVERS=16--可以设置更多的并行应用的进行来提高应用的速度
PREPARE_SERVERS=8--可以设置更多的准备进程来提高应用速度
MAX_SERVERS=40--设置最大可使用的进程数,要大于所有的APPLY、PREPARE、ANALYZE等进程之和
----设置以上并行参数的时候,要注意PARAMETER中的PARALLEL参数相关的设置也要能支持上面的调整。
_EAGER_SIZE=4001--这个是一个隐含的参数,用来设置大事务和小事务时间的分界线,更新记录行数大于此值的被认为是大事务,提高这个参数的设置对大事务的应用有提高。查询 v$logstdby_stats视图,如果没有很多的bytes paged out的话,说明这个参数还可以设置的更大。
MAX_SGA=2048--LCR可使用的SGA大小,如果过小则会产生很多的PAGE OUT,可以调整此SGA的大小,此SGA是数据SHARED POOL中的一部分的,所以要保证SHARED POOL也足够大
PRESERVE_COMMIT_ORDER = FALSE--设置事务不按照严格的主库上事务发生的顺序来进行应用,也可以提高应用的速度。

3、如果以上招数都不能解决问题,那可能就是碰到了ORACLE的BUG了,参考5327658.8,上面说到如果更新百万级大表的话,可能造成STANDBY的应用非常慢,可以通过升级来解决问题,需要升级到10.2.0.4或者11.1.0.6

4、我这次千万级的更新使用了2的方法后,还是应用很慢,整整一天应用都没有同步过来,后来发现可以把一个表的DML操作SKIP掉,然后重新同步这个表来实现。于是先使用dbms_logstdby.skip把这个表所有的DML操作全部忽略,加速应用,等到积累的日志全部应用完成后,使用 dbms_logstdby.instantiate_table进行表的同步,结果发现这个同步操作也是很慢,半个多小时了还是在进行中,于是取消操作。后来发现dbms_logstdby.instantiate_table基本上就是在STANDBY库先把表删除,然后再使用IMPDP把这个表的数据通过DBLINK导过来。既然这样可以,那就手工做吧。

5、把STANDBY的APPLY停下来,然后查询STANDBY的 V$LOGSTDBY_PROGRESS,得到当前的APPLY_SCN,那么利用主库的FLASHBACK特性,先把STANDBY的表 TRUNCATE掉,然后使用INSERT /*+ APPEND*/ INTO TABLE SELECT * FROM TABLE@DBLINK AS SCN OF XX把数据导过来,其中的XX就是上面查到的APPLY_SCN。这当中其实就是大家很熟悉的导数据操作了,可以使用先删除索引,导入完了再重建,使用APPEND提示等等多种手段来提高导入的速度。

6、经过10多分钟,数据导完,建完索引,整个过程不到半个小时,比dbms_logstdby.instantiate_table还快一些,虽然繁琐,但是过程比较透明,出现问题也容易处理的多。

建议和总结
1、方法2中的很多参数在平时的日志应用中就可是设置好来加速日志应用速度。
2、对于大批量的数据更新,尽量使用分批提交的方式,把大事务拆分成小的事务,而且进行要比_EAGER_SIZE参数设置的要小一些
3、PRESERVE_COMMIT_ORDER参数在日志同步完成后,为了保证事务和生产上的顺序一致,最好把这个参数使用DBMS_LOGSTDBY.APPLY_UNSEGT取消。
4、因为主库的一个DML的操作在STANDBY库会被分解成一个个单独的更新的sql,所以可以合理利用规则来SKIP这些DML,然后再手工同步的方式来进行
5、有可能的话,升级数据库系统

DataGuard Logical standby维护命令手册

1.查看logical standby上当前正在apply的redo log

COLUMN DICT_BEGIN FORMAT A15;
COLUMN FILE_NAME FORMAT A30;
SET NUMF 9999999;
COL FCHANGE# format 9999999999999;
COL NCHANGE# for 999999999999999999999;
SET line 200
SELECT  file_name, sequence# AS seq#, first_change# AS fchange#,
        next_change# AS nchange#, TIMESTAMP, dict_begin AS beg,
        dict_end AS END, thread# AS thr#, applied
    FROM dba_logstdby_log
ORDER BY thread#;

2. 查看logical standby节点apply进程状态
select sid,type,status_code,status from v$logstdby_process;

set linesize 120
col type for a10
col status for a40
col sid for 9999
col high_scn for 9999999999999
select * from v$logstdby_process where status_code<>16116;

查看applyer进程的个数
SELECT COUNT(*) AS APPLIER_COUNT FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';

查看空闲的applyer进程
SELECT COUNT(*) AS IDLE_APPLIER
FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16166;

3. 调整logical standby的apply进程数

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply
EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20); --- 调整apply进程数为20,默认为5个
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

确认logical standby上的空闲APPLIER进程

SELECT COUNT (*) AS idle_applier
  FROM v$logstdby_process
WHERE TYPE = 'APPLIER' AND status_code = 16166;

注:status_code = 16166 表示进程是空闲状态,可以看到"STATS"为"ORA-16116: no work available"

如何根据系统表现调整APPLIER进程的个数,统计transactions的apply状态,

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%';

NAME                        VALUE
-------------------------- ----------------------------
transactions ready          159
transactions applied        159

如果ready(等待apply)和applied(已经apply)的值基本同步,则设置的APPLIER进程合适或偏多。根据IDLE_APPLIER的进程数,可减少APPLIER进程数目.如果transactions ready - transactions applied的差比APPLIER进程数的2倍还多,则需要增加APPLIER进程数目了。

4. 调整PREPARER(调制机)的进程数

logical standby上有很多transactions等待apply, 但是还有空闲的applyer进程,且已经没有idle状态的PREPARER进程,这时需要增加PREPARER(调制机)的进程数。如下:

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply
EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 4); --- 调整PREPARER进程数为4
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

5. 计算logical standby的apply速度

可以通过v$logstdby_stats视图统计logical standby的apply速度,计算公式如下:
apply_rate = bytes of redo processed / (coordinator uptime - seconds system is idle)

比如:

SELECT NAME, VALUE
  FROM v$logstdby_stats
WHERE NAME IN
          ('coordinator uptime',
          'seconds system is idle',
          'bytes of redo processed'
          );

NAME                        VALUE
--------------------------- ---------------------------------
coordinator uptime          78717
bytes of redo processed    7954813012
seconds system is idle      40

logical standby.apply_rate = 7954813012/(78717-40)/1024/1024

整理成1条sql语句计算出apply_rate如下:

SELECT c.VALUE / (a.VALUE - b.VALUE) / 1024 / 1024 AS "APPLY_RATE"
  FROM v$logstdby_stats a, v$logstdby_stats b, v$logstdby_stats c
WHERE a.NAME = 'coordinator uptime'
  AND b.NAME = 'seconds system is idle'
  AND c.NAME = 'bytes of redo processed';


6. 统计logical standby上是否有报错信息

SELECT xidusn, xidslt, xidsqn, status, status_code
  FROM dba_logstdby_events
WHERE event_time = (SELECT MAX (event_time)
                      FROM dba_logstdby_events);

7. 调整MAX_SGA - 防止Pageouts

SQL> select value bytes from v$logstdby_stats where name='bytes paged out';

注:如果以上查询结果在增长,则查到当前MAX_SGA的大小:
SQL> select value from v$logstdby_stats where name = 'maximum SGA for LCR cache';
VALUE
------------------------
30

增大MAX_SGA,备注:10gR2,MAX_SGA可以增大到4095 MB.

SQL> alter database stop logical standby apply;
Database altered.
SQL> execute dbms_logstdby.apply_set('MAX_SGA',1000);
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply immediate;
Database altered.

逻辑备库需要将redo记录解析成LCR,会在shared pool里分配一部分空间来作为LCR Cache,如果cache太小,就会像OS的虚拟内存管理一样,需要做page out,这会严重影响应用日志的性能。默认情况下,LCR Cache为Shared pool的四分之一,最少不少于30M,否则SQL Apply不能启动。如果机器的内存足够,建议将LCR Cache尽量设大一点,当然,同时share pool也要足够大。如果机器内存有限,那可以考虑将buffer cache减少一点来给LCR Cache腾出空间。

8.跳过特定表或Schema的DML或DDL事务
alter database stop logical standby apply;
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'DML',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
alter database start logical standby apply;

9.如何重新初始化数据表
通过以下查询确认当前的skip规则:
select * from dba_logstdby_skip;

建议取消Skip之后,再重新初始化:
alter database stop logical standby apply;
execute dbms_logstdby.unskip('DML','EYGLE','SALES');
exec dbms_logstdby.instantiate_table('EYGLE','SALES','dblink_name');
alter database start logical standby apply;


10. Starting Real-time Apply

To start real-time apply on the logical standby database to immediately recover redo data from the standby redo log files on the logical standby database, include the IMMEDIATE keyword as shown in the following statement:

SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

11.Stopping Log Apply Services on a Logical Standby Database

To stop SQL Apply, issue the following statement on the logical standby database:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

When you issue this statement, SQL Apply waits until it has committed all complete transactions that were in the process of being applied. Thus, this command may not stop the SQL Apply processes immediately.

If you want to stop SQL Apply immediately, issue the following statement:

SQL> ALTER DATABASE ABORT LOGICAL STANDBY APPLY;

Oracle文档参考:

INSTANTIATE_TABLE Procedure

This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink) as an input parameter.

Use the INSTANTIATE_TABLE procedure to:

  • Add a table to a standby database
  • Re-create a table in a standby database

Syntax

DBMS_LOGSTDBY.INSTANTIATE_TABLE (
table_name IN VARCHAR2,
schema_name IN VARCHAR2,
dblink IN VARCHAR2);

Parameters

Table 29-4 describes the parameters for the INSTANTIATE_TABLE procedure.

Table 29-4  DBMS_LOGSTDBY.INSTANTIATE_TABLE Procedure Parameters
Parameter Description

table_name

Name of the table to be created or re-created in the standby database.

schema_name

Name of the schema.

dblink

Name of the database link account that has privileges to read and lock the table in the primary database.

Exceptions

None.

Usage Notes

  • Use this procedure to create and populate a table in a way that keeps the data on the standby database transactionally consistent with the primary database.
  • This procedure assumes that the metadata has been maintained correctly.
  • This table is not safe until the redo log that was current on the primary database at the time of execution is applied to the standby database.

Example

Enter this statement to create and populate a new table on the standby database.

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('myschema', 'mytable', 'mydblink');


使用RMAN恢复Oracle 10g RAC库至异机

出处 ITPUB ,原文:链接
主RAC库:2个节点 rac1(192.168.218.168),rac2(192.168.218.168) 采用ASM+RAW
备机 standby(192.168.218.188)  文件系统


一。在rac库备份:
rac1-> crs_stat -t
Name          Type          Target    State    Host       
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    rac1       
ora....C1.lsnr application    ONLINE    ONLINE    rac1       
ora.rac1.gsd  application    ONLINE    ONLINE    rac1       
ora.rac1.ons  application    ONLINE    ONLINE    rac1       
ora.rac1.vip  application    ONLINE    ONLINE    rac1       
ora....SM2.asm application    ONLINE    ONLINE    rac2       
ora....C2.lsnr application    ONLINE    ONLINE    rac2       
ora.rac2.gsd  application    ONLINE    ONLINE    rac2       
ora.rac2.ons  application    ONLINE    ONLINE    rac2       
ora.rac2.vip  application    ONLINE    ONLINE    rac2       
ora.racdb.db  application    ONLINE    ONLINE    rac1       
ora....b1.inst application    ONLINE    ONLINE    rac1       
ora....b2.inst application    ONLINE    ONLINE    rac2       
rac1->
rac1-> export ORACLE_SID=racdb1
rac1-> rman target / nocatalog
   
RMAN> run{
2>  allocate CHANNEL ch00  type DISK  CONNECT 'sys/oracle@racdb1'; 
3>  allocate CHANNEL ch01  type DISK  CONNECT 'sys/oracle@racdb2'; 
4>  backup database    format '/rmanset/racdb_full_%d%t%s%p'        tag 'fullbackup';
5>  BACKUP FORMAT '/rmanset/%d_arch_%s_%p_%h' ARCHIVELOG ALL;                                         
6>  backup format '/rmanset/cf_%d_%s_%p'
7>          (current controlfile);
8>    RELEASE CHANNEL ch00;                                                         
9>    RELEASE CHANNEL ch01;                                                       
10>  }

allocated channel: ch00
channel ch00: sid=148 instance=racdb1 devtype=DISK

allocated channel: ch01
channel ch01: sid=148 instance=racdb2 devtype=DISK

Starting backup at 02-NOV-08
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
input datafile fno=00001 name=+RACDATA/racdb/datafile/system.259.669487425
input datafile fno=00005 name=+RACDATA/racdb/datafile/users.264.669487561
input datafile fno=00004 name=+RACDATA/racdb/datafile/undotbs2.263.669487539
channel ch00: starting piece 1 at 02-NOV-08
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
input datafile fno=00003 name=+RACDATA/racdb/datafile/sysaux.261.669487497
input datafile fno=00002 name=+RACDATA/racdb/datafile/undotbs1.260.669487479
channel ch01: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769770931 tag=FULLBACKUP comment=NONE
channel ch00: backup set complete, elapsed time: 00:02:19
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
channel ch00: starting piece 1 at 02-NOV-08
channel ch01: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769784941 tag=FULLBACKUP comment=NONE
channel ch01: backup set complete, elapsed time: 00:02:28
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ch01: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769914951 tag=FULLBACKUP comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:11
channel ch01: finished piece 1 at 02-NOV-08
piece handle=/rmanset/racdb_full_RACDB669769933961 tag=FULLBACKUP comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:05
Finished backup at 02-NOV-08

Starting backup at 02-NOV-08
current log archived
channel ch00: starting archive log backupset
channel ch00: specifying archive log(s) in backup set
input archive log thread=1 sequence=42 recid=141 stamp=669603048
input archive log thread=1 sequence=44 recid=149 stamp=669640424
input archive log thread=1 sequence=45 recid=151 stamp=669675778
input archive log thread=1 sequence=46 recid=152 stamp=669679426
input archive log thread=1 sequence=47 recid=156 stamp=669690456
channel ch00: starting piece 1 at 02-NOV-08
channel ch01: starting archive log backupset
channel ch01: specifying archive log(s) in backup set
input archive log thread=1 sequence=53 recid=170 stamp=669767346
input archive log thread=1 sequence=54 recid=172 stamp=669769357
input archive log thread=2 sequence=44 recid=165 stamp=669764276
input archive log thread=2 sequence=45 recid=167 stamp=669765472
input archive log thread=2 sequence=46 recid=168 stamp=669765474
input archive log thread=2 sequence=47 recid=169 stamp=669767345
input archive log thread=2 sequence=48 recid=171 stamp=669769352
input archive log thread=2 sequence=49 recid=175 stamp=669769944
channel ch01: starting piece 1 at 02-NOV-08
channel ch01: finished piece 1 at 02-NOV-08
piece handle=/rmanset/RACDB_arch_98_1_2 tag=TAG20081102T231211 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:04
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/RACDB_arch_97_1_1 tag=TAG20081102T231211 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:11
channel ch00: starting archive log backupset
channel ch00: specifying archive log(s) in backup set
input archive log thread=1 sequence=48 recid=158 stamp=669690550
input archive log thread=1 sequence=49 recid=160 stamp=669691146
input archive log thread=1 sequence=50 recid=162 stamp=669755973
input archive log thread=1 sequence=51 recid=163 stamp=669757116
input archive log thread=1 sequence=52 recid=166 stamp=669764278
input archive log thread=1 sequence=55 recid=173 stamp=669769375
input archive log thread=1 sequence=56 recid=174 stamp=669769928
channel ch00: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/RACDB_arch_99_1_1 tag=TAG20081102T231211 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:08
Finished backup at 02-NOV-08

Starting backup at 02-NOV-08
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
channel ch00: starting piece 1 at 02-NOV-08
channel ch00: finished piece 1 at 02-NOV-08
piece handle=/rmanset/cf_RACDB_100_1 tag=TAG20081102T231235 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:07
Finished backup at 02-NOV-08

released channel: ch00

released channel: ch01

2. 在rac库上创建pfile

SQL> create pfile='/rmanset/initracdb.ora' from spfile;


将rac1,rac2上的备份出来的rman文件和pfile  ftp 至 standby 主机   


二:  standby  主机做恢复的过程:
0.修改ftp过来的pfile,主要是rac中一些参数去掉:

#*.cluster_database_instances=2
*.cluster_database=false
#racdb2.instance_number=2
#racdb1.instance_number=1
#racdb2.thread=2
#racdb1.thread=1
#racdb2.undo_tablespace='UNDOTBS2'

#*.db_file_name_convert='+RACDATA/racdb/datafile/','/oradata/racdb/'
#*.fal_client='RACDB'
#*.fal_server='STANDBY'
#*.log_archive_config='DG_CONFIG=(racdb,standby)'
#racdb2.log_archive_dest_1='LOCATION=/racdb2_arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb'
#*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
#*.log_archive_dest_state_2='ENABLE'
#*.log_file_name_convert='+RACDATA/racdb/onlinelog/','/oradata/racdb/'
#*.remote_listener='LISTENERS_RACDB'
#*.standby_file_management='AUTO'

1.数据库启动到nomount
   
2.restore controlfile

3.restore archivelog

4.recover database

5.改变logfile 的位置,重新建redo

6.drop 一个undo tablespace.

7.add a temp tablespace

8. 配tnsnames.ora 和listener.ora


下为操作的过程:
   
   
standby-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Nov 2 14:22:39 2008

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

connected to target database: racdb (not mounted)
--恢复controlfile
RMAN> restore controlfile from '/rmanset/cf_RACDB_100_1';

Starting restore at 02-NOV-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output filename=/oradata/racdb/control01.ctl
Finished restore at 02-NOV-08


---restore datafile and archive log

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> run{
2> set until sequence 57 thread 1;
3> set newname for datafile 1 to '/oradata/racdb/system01.dbf';
4> set newname for datafile 2 to '/oradata/racdb/undotbs01.dbf';
5> set newname for datafile 3 to '/oradata/racdb/sysaux01.dbf';
6> set newname for datafile 4 to '/oradata/racdb/users01.dbf'; 
7> set newname for datafile 5 to '/oradata/racdb/undotbs02.dbf';
8> restore database;
9> switch datafile all;
10> }

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 02-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/racdb/system01.dbf
restoring datafile 00004 to /oradata/racdb/users01.dbf
restoring datafile 00005 to /oradata/racdb/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /rmanset/racdb_full_RACDB669769770931
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/racdb_full_RACDB669769770931 tag=FULLBACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /oradata/racdb/undotbs01.dbf
restoring datafile 00003 to /oradata/racdb/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /rmanset/racdb_full_RACDB669769784941
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/racdb_full_RACDB669769784941 tag=FULLBACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 02-NOV-08

datafile 1 switched to datafile copy
input datafile copy recid=10 stamp=669738296 filename=/oradata/racdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=11 stamp=669738296 filename=/oradata/racdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=12 stamp=669738296 filename=/oradata/racdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=13 stamp=669738297 filename=/oradata/racdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=14 stamp=669738297 filename=/oradata/racdb/undotbs02.dbf

RMAN>

--列出备份的archivelog:
RMAN> list backup of archivelog all;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Size      Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
89      2.44M      DISK        00:00:03    02-NOV-08     
        BP Key: 89  Status: AVAILABLE  Compressed: NO  Tag: TAG20081102T231211
        Piece Name: /rmanset/RACDB_arch_98_1_2

  List of Archived Logs in backup set 89
  Thrd Seq    Low SCN    Low Time  Next SCN  Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    53      356353    02-NOV-08 396535    02-NOV-08
  1    54      396535    02-NOV-08 398232    02-NOV-08
  2    44      350717    02-NOV-08 356350    02-NOV-08
  2    45      356350    02-NOV-08 376487    02-NOV-08
  2    46      376487    02-NOV-08 396536    02-NOV-08
  2    47      396536    02-NOV-08 397820    02-NOV-08
  2    48      397820    02-NOV-08 398228    02-NOV-08
  2    49      398228    02-NOV-08 398963    02-NOV-08

BS Key  Size      Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
90      17.42M    DISK        00:00:07    02-NOV-08     
        BP Key: 90  Status: AVAILABLE  Compressed: NO  Tag: TAG20081102T231211
        Piece Name: /rmanset/RACDB_arch_97_1_1

  List of Archived Logs in backup set 90
  Thrd Seq    Low SCN    Low Time  Next SCN  Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    42      285767    01-NOV-08 285925    01-NOV-08
  1    44      286441    01-NOV-08 286516    01-NOV-08
  1    45      286516    01-NOV-08 308174    01-NOV-08
  1    46      308174    01-NOV-08 312441    01-NOV-08
  1    47      312441    01-NOV-08 324657    02-NOV-08

BS Key  Size      Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
91      9.69M      DISK        00:00:04    02-NOV-08     
        BP Key: 91  Status: AVAILABLE  Compressed: NO  Tag: TAG20081102T231211
        Piece Name: /rmanset/RACDB_arch_99_1_1

  List of Archived Logs in backup set 91
  Thrd Seq    Low SCN    Low Time  Next SCN  Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    48      324657    02-NOV-08 324727    02-NOV-08
  1    49      324727    02-NOV-08 325613    02-NOV-08
  1    50      325613    02-NOV-08 349040    02-NOV-08
  1    51      349040    02-NOV-08 350714    02-NOV-08
  1    52      350714    02-NOV-08 356353    02-NOV-08
  1    55      398232    02-NOV-08 398433    02-NOV-08
  1    56      398433    02-NOV-08 398960    02-NOV-08
 
 
--restore archivelog and recover database. 

RMAN>

RMAN> run{
2> set archivelog destination to '/racdb_arch';
3> restore archivelog from sequence 49 thread 2;
}4>

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 02-NOV-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/racdb_arch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=49
channel ORA_DISK_1: reading from backup piece /rmanset/RACDB_arch_98_1_2
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/RACDB_arch_98_1_2 tag=TAG20081102T231211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 02-NOV-08

RMAN>

RMAN>

RMAN> run{
2> set archivelog destination to '/racdb_arch';
3> restore archivelog from sequence 44 thread 2;
4> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 02-NOV-08
using channel ORA_DISK_1

archive log thread 2 sequence 49 is already on disk as file /racdb_arch/2_49_669487401.dbf
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/racdb_arch
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=44
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=45
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=46
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=47
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=48
channel ORA_DISK_1: reading from backup piece /rmanset/RACDB_arch_98_1_2
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/RACDB_arch_98_1_2 tag=TAG20081102T231211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 02-NOV-08

RMAN> run{
2> set until sequence 57 thread 1;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 02-NOV-08
using channel ORA_DISK_1

starting media recovery

archive log thread 2 sequence 49 is already on disk as file /racdb_arch/2_49_669487401.dbf
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=56
channel ORA_DISK_1: reading from backup piece /rmanset/RACDB_arch_99_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanset/RACDB_arch_99_1_1 tag=TAG20081102T231211
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/racdb_arch/1_56_669487401.dbf thread=1 sequence=56
archive log filename=/racdb_arch/2_49_669487401.dbf thread=2 sequence=49
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-NOV-08


--对redo 作修改:

                                                                                                           
alter database rename file '+RACDATA/racdb/onlinelog/group_1.257.669487411' to '/oradata/racdb/redo1';     
alter database rename file '+RACDATA/racdb/onlinelog/group_2.258.669487419' to '/oradata/racdb/redo2';     
alter database rename file '+RACDATA/racdb/onlinelog/group_3.265.669489319' to '/oradata/racdb/redo3';     
alter database rename file '+RACDATA/racdb/onlinelog/group_4.266.669489327' to '/oradata/racdb/redo4';     


SQL>  select member from v$logfile;                                                                       
                                                                                                         
MEMBER                                                                                                   
--------------------------------------------------------------------------------                         
+RACDATA/racdb/onlinelog/group_1.257.669487411                                                                                                         
+RACDATA/racdb/onlinelog/group_2.258.669487419                                                                                                         
+RACDATA/racdb/onlinelog/group_3.265.669489319                                                                                                         
+RACDATA/racdb/onlinelog/group_4.266.669489327                                                           
                                                                                                         
SQL> alter database rename file '+RACDATA/racdb/onlinelog/group_1.257.669487411' to '/oradata/racdb/redo1';
                                                                                                         
Database altered.                                                                                         
                                                                                                         
SQL> alter database rename file '+RACDATA/racdb/onlinelog/group_2.258.669487419' to '/oradata/racdb/redo2';
alter database rename file '+RACDATA/racdb/onlinelog/group_3.265.669489319' to '/oradata/racdb/redo3';   
alter database rename file '+RACDATA/racdb/onlinelog/group_4.266.669489327' to '/oradata/racdb/redo4';   
                                                                                                         
Database altered.                                                                                         
                                                                                                         
SQL>                                                                                                     
Database altered.                                                                                         
                                                                                                         
SQL>                                                                                                     
Database altered.                                                                                         
                                                                                                         
SQL> alter database open resetlogs;                                                                       
                                                                                                         
Database altered.                                                                                         
                                                                                                         
SQL>                                                                                                     
SQL>                                                                                                     
SQL>                                                                                                     
SQL>  select THREAD#, STATUS, ENABLED from v$thread;                                                     
                                                                                                         
  THREAD# STATUS ENABLED                                                                                 
---------- ------ --------                                                                               
        1 OPEN  PUBLIC                                                                                 
        2 CLOSED PUBLIC                                                                                 
                                                                                                         
                                                                                                   
SQL>                                                                                                     
SQL> select group# from v$log where THREAD#=2;                                                           
                                                                                                         
    GROUP#                                                                                               
----------                                                                                               
        3                                                                                               
        4                                                                                               
                                                                                                         
SQL> alter database disable thread 2;                                                                     
                                                                                                         
Database altered.                                                                                         
                                                                                                         
SQL> alter database drop logfile group 4;                                                                 
alter database drop logfile group 4                                                                       
*                                                                                                         
ERROR at line 1:                                                                                         
ORA-00350: log 4 of instance racdb2 (thread 2) needs to be archived                                       
ORA-00312: online log 4 thread 2: '/oradata/racdb/redo4'                                                 
                                                                                                         
                                                                                                         
SQL> alter database clear unarchived logfile group 3;                                                     
                                                                                                         
Database altered.                                                                                         
                                                                                                         
SQL> alter database drop logfile group 3;                                                                 
                                                                                                         
Database altered.                                                                                         
                                                                                                         
SQL> alter database drop logfile group 4;                                                                 
alter database drop logfile group 4                                                                       
*                                                                                                         
ERROR at line 1:                                                                                         
ORA-00350: log 4 of instance racdb2 (thread 2) needs to be archived                                       
ORA-00312: online log 4 thread 2: '/oradata/racdb/redo4'                                                 
                                                                                                         
                                                                                                         
SQL> alter database clear unarchived logfile group 4;                                                     
                                                                                                         
Database altered.                                                                                         
                                                                                                         
SQL>  alter database drop logfile group 4;                                                               
                                                                                                         
Database altered.                                                                                         
                                                                                                         
                                                                                                     
SQL> select group#,member from v$logfile;                                                                 
                                                                                                         
    GROUP#                                                                                               
----------                                                                                               
MEMBER                                                                                                   
--------------------------------------------------------------------------------                         
        1                                                                                               
/oradata/racdb/redo1                                                                                     
                                                                                                         
        2                                                                                               
/oradata/racdb/redo2                         

SQL> select THREAD#, STATUS, ENABLED from v$thread;                                                           
                                                 
  THREAD# STATUS ENABLED                         
---------- ------ --------                       
        1 OPEN  PUBLIC         
       
       
对undo 的处理:       

SQL> show parameter undo

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                      integer    900
undo_tablespace                      string      UNDOTBS1
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL>  select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1

SQL>                 



对temp的处理:

create temporary tablespace TEMP1  tempfile '/oradata/racdb/temp01.dbf'  size 50M autoextend off;
 
SQL>  select name from v$tempfile;

NAME
---------------------------------------------------------------------------------------------------------------------------------------------
/oradata/racdb/RACDB/datafile/o1_mf_temp_4jtl9yfl_.tmp

SQL>
SQL>
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME
------------------------------
TEMP

SQL>  create temporary tablespace TEMP1
  2    tempfile '/oradata/racdb/temp01.dbf'
  3    size 50M autoextend off;

Tablespace created.

SQL> alter database default temporary tablespace TEMP1;

Database altered.

SQL>  drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL>


再配上tnsnames.ora 及  listener.ora


#####参考metalink  Note:415579.1

EXECUTE IMMEDIATE动态SQL的使用总结

EXECUTE IMMEDIATE 在某些情况下代替了以前Oracle8i中DBMS_SQL package包.它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前,EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能,较之以前它相当容易编码.尽管DBMS_SQL仍然可用,但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。

-- 使用技巧


1. EXECUTE IMMEDIATE将不会提交一个DML事务执行,应该显式提交
如果通过EXECUTE IMMEDIATE处理DML命令,
那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分.
如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据

2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.

3. 当执行SQL语句时,不要用分号,当执行PL/SQL块时,在其尾部用分号.

4. 在Oracle手册中,未详细覆盖这些功能。
下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.

5. 对于Forms开发者,当在PL/SQL 8.0.6.3.版本中,Forms 6i不能使用此功能.


EXECUTE IMMEDIATE -- 用法例子


1. 在PL/SQL运行DDL语句


begin
   execute immediate 'set role all';
end;


2. 给动态语句传值(USING 子句)


declare
   l_depnam varchar2(20) := 'testing';
   l_loc     varchar2(10) := 'Dubai';
   begin
   execute immediate 'insert into dept values   (:1, :2, :3)'
     using 50, l_depnam, l_loc;
   commit;
end;


3. 从动态语句检索值(INTO子句)


declare
   l_cnt     varchar2(20);
begin
   execute immediate 'select count(1) from emp'
     into l_cnt;
   dbms_output.put_line(l_cnt);
end;


4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.
黓认为IN类型,其它类型必须显式指定


declare
   l_routin    varchar2(100) := 'gen2161.get_rowcnt';
   l_tblnam    varchar2(20) := 'emp';
   l_cnt       number;
   l_status    varchar2(200);
begin
   execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
     using in l_tblnam, out l_cnt, in out l_status;

   if l_status != 'OK' then
      dbms_output.put_line('error');
   end if;
end;


5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量


declare
   type empdtlrec is record (empno   number(4),
                            ename   varchar2(20),
                            deptno   number(2));
   empdtl empdtlrec;
begin
   execute immediate 'select empno, ename, deptno ' ||
                    'from emp where empno = 7934'
     into empdtl;
end;


6. 传递并检索值.INTO子句用在USING子句前


declare
   l_dept     pls_integer := 20;
   l_nam      varchar2(20);
   l_loc      varchar2(20);
begin
   execute immediate 'select dname, loc from dept where deptno = :1'
     into l_nam, l_loc
     using l_dept ;
end;


7. 多行查询选项.对此选项用insert语句填充临时表,
用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.

declare
   l_sal    pls_integer := 2000;
begin
   execute immediate 'insert into temp(empno, ename) ' ||
                    '           select empno, ename from emp ' ||
                    '           where   sal > :1'
     using l_sal;
   commit;
end;

Pages

Powered by Movable Type 6.3.2

About this Archive

This page is an archive of entries from February 2009 listed from newest to oldest.

January 2009 is the previous archive.

March 2009 is the next archive.

回到 首页 查看最近文章或者查看所有归档文章.